定位必要优化的SQL ,及怎样优化SQL

[复制链接]
发表于 昨天 22:20 | 显示全部楼层 |阅读模式

马上注册,结交更多好友,享用更多功能,让你轻松玩转社区。

您需要 登录 才可以下载或查看,没有账号?立即注册

×
核心结论与决策建议(BLUF)
在数据库性能治理中,盲目的“玄学调优”往往事倍功半。成熟的高性能架构遵循“指标发现 -> 慢日志日志捕获与指纹归一 -> EXPLAIN 执行计划诊断 -> CBO 代价重构与索引设计”的标准化闭环链路。核心治理铁律:1. 建立基于
  1. slow_query_log
复制代码
的自动化监控监控,设置
  1. long_query_time = 0.5s
复制代码
配合
  1. log_queries_not_using_indexes
复制代码
捕获全量劣质SQL;2. 熟练掌握 EXPLAIN 八大核心指标,严禁生产环境出现
  1. type=ALL
复制代码
、
  1. Extra=Using temporary; Using filesort
复制代码
;3. 深刻理解最左前缀原则、隐式类型转换及索引下推(ICP)机制
。
调优排查阶段核心工具与命令重点关注指标治理目标与行动
第一阶段:全局发现
  1. SHOW PROCESSLIST
复制代码
, Performance Schema
执行时长 State=Sending data, Copy to tmpKill 阻塞连接,恢复数据库可用性
第二阶段:离线聚合
  1. pt-query-digest slow.log
复制代码
总耗时占比、平均耗时、扫描行数比率锁定消耗系统资源 Top 3 的 SQL 指纹
第三阶段:单语句诊断
  1. EXPLAIN FORMAT=TREE
复制代码
/
  1. EXPLAIN ANALYZE
复制代码
type, rows, filtered, cost, actual time分析真实执行开销与物理访问路径
第四阶段:重构落地建立联合索引、覆盖索引、改写分页rows 从百万级收敛至十行以内查询延迟稳定在 1ms ~ 5ms 以内


一、工业级慢查询日志日志标准采集配置
在
  1. my.cnf
复制代码
中进行无感抓取配置:

  1. [mysqld]
  2. slow_query_log = ON
  3. slow_query_log_file = /var/log/mysql/mysql-slow.log
  4. long_query_time = 0.5

  5. -- 记录所有未走索引的全表扫描慢查询
  6. log_queries_not_using_indexes = ON
  7. log_throttle_queries_not_using_indexes = 100

  8. -- 输出更详尽的诊断统计信息
  9. log_slow_verbosity = microsecond,query_cache,innodb
复制代码

使用 Percona Toolkit 的
  1. pt-query-digest
复制代码
工具进行日度自动化报表提炼:

  1. pt-query-digest /var/log/mysql/mysql-slow.log > /tmp/slow_report.txt
复制代码


二、EXPLAIN 执行计划黄金解密法则
获取执行计划:
  1. EXPLAIN SELECT * FROM t_order WHERE ...
复制代码

1. type 列(访问类型由优到劣排序):
-
  1. system
复制代码
>
  1. const
复制代码
(主键/唯一索引等值)>
  1. eq_ref
复制代码
(多表关联唯一主键驱动)>
  1. ref
复制代码
(非唯一二级索引等值)>
  1. range
复制代码
(索引范围扫描)>
  1. index
复制代码
(全量索引树扫描)>
  1. ALL
复制代码
(灾难级全表数据页物理扫描)。
- 生产底线:核心高并发业务必须达到 ref 或 range 以上,坚决杜绝 ALL。

2. rows 与 filtered 列(过滤有效度):
-
  1. rows
复制代码
:优化器预估需要扫描的物理行数。
-
  1. filtered
复制代码
:经过当前条件过滤后留存的有效行百分比。
- 如果
  1. rows = 1000000
复制代码
且
  1. filtered = 0.1%
复制代码
,说明扫描了一百万行仅仅为了获取一千行数据,索引设计严重失准。

3. Extra 列关键告警信号:
- Using index:极佳!命中覆盖索引(Covering Index),直接在二级索引树返回结果,零次回表。
- Using index condition (ICP):优!命中索引下推,存储引擎层过滤不满足的二级索引列,大幅减少回表次数。
- Using filesort:警告!无法利用索引有序性,被迫在内存或磁盘上执行全量文件排序。
- Using temporary:高危!针对 GROUP BY 或 DISTINCT 创建了内部临时表,资源消耗严重。

三、八大导致索引失效的“经典陷阱”
1. 最左前缀匹配违例:建立了联合索引
  1. (a, b, c)
复制代码
,查询条件却跳过 a 直接书写
  1. WHERE b = 1 AND c = 2
复制代码
。
2. 函数计算与表达式操作:
  1. WHERE DATE(create_time) = '2026-03-20'
复制代码
或
  1. WHERE id + 1 = 10
复制代码
,破坏B+树对数寻址。
3. 隐式类型转换:字段为
  1. VARCHAR
复制代码
字符串类型,查询参数却裸传整数:
  1. WHERE phone = 13800000000
复制代码
。MySQL 会隐式调用
  1. CAST()
复制代码
函数导致索引全废。
4. 左模糊匹配:
  1. WHERE name LIKE '%李'
复制代码
。B+树字符串排序基于从左向右的字符编码,前缀通配符导致全索引树失效。
5. OR 关联未全量索引:
  1. WHERE a = 1 OR b = 2
复制代码
,若 a 有索引而 b 无索引,系统只能放弃索引走全表扫描。

常见问题(FAQ)
Q1: 为什么有时候字段明确有索引,MySQL 优化器依然选择走全表扫描?
A1: 这是由基于代价的优化器(CBO)计算得出的结果。若优化器预估通过二级索引查出的数据量超过全表的 20%~30%,由于每次命中索引都需要执行一次随机主键回表(Random I/O),此时顺序扫描聚簇索引连续数据页(Sequential I/O)的总代价反而低于成千上万次离散随机回表,因此优化器会“聪明”地放弃二级索引。

Q2: MySQL 8.0 独有的
  1. EXPLAIN ANALYZE
复制代码
相比传统 EXPLAIN 强在哪里?

A2: 传统的 EXPLAIN 只是优化器在执行前的“纯理论预估”(依靠不一定准确的采样统计信息)。而
  1. EXPLAIN ANALYZE
复制代码
会真正运行该 SQL,输出每一个执行算子真实的物理启动时间(Actual Time)、实际处理行数(Actual Rows)以及循环迭代次数(Loops),让 DBA 精准洞察预估与实际执行的偏差所在。



qidao123.com企服评测-应用市场 编辑提示:本文由企服大模型基于真实系统运维实践重构整理,聚焦技术原理、性能对比与选型避坑。若在实际部署中遇到异常,欢迎在下方跟帖交流;如需企业级技术支持或深度运维解决方案,请访问 qidao123.com 获取专属支持。
回复

使用道具 举报

登录后关闭弹窗

登录参与点评抽奖  加入IT实名职场社区
去登录
快速回复 返回顶部 返回列表