SQL调优全链路实战：从慢查询定位到索引优化与执行计划分析

1. 先搞清楚SQL 调优到底在调什么工作里一谈到数据库慢很多人第一反应就是“加索引”“ rewrite 一下 SQL”但真正上手调优之前我建议你先花十分钟想清楚一个问题这条 SQL 慢到底是慢在查询本身还是慢在整体负载还是慢在等你的人太多说个我实际遇到的例子。某次线上订单表查询突然从 50ms 飙到 2.8s开发同学第一时间贴了 SQL 过来让帮忙优化。我看了一眼单表查询、条件简单、索引齐全怎么看都不该慢。后来一查才发现同一时刻有个批量任务在跑 UPDATE锁住了大量行这条 SELECT 一直在等行锁释放。你就算把 SQL 优化到极致锁不解决照样慢。所以 SQL 调优的第一步不是改 SQL而是先确认瓶颈到底在哪个环节。一般来说一条 SQL 从客户端发出来到结果返回会经过网络传输、SQL 解析、优化器生成执行计划、存储引擎执行、返回数据这几个阶段。调优就是逐个环节排查看时间花在了哪里。实操中最常用的定位手段就是慢查询日志配合性能视图先确认“慢”这个事实再谈“怎么调”。另外SQL 调优不只是“加索引”这么简单。它是一条完整的链路定位慢 SQL、分析执行计划、优化索引设计、改写 SQL、调整参数、最后做回归验证。每一环都有对应的工具和方法论。这篇文章我就按这条链路把我平时在项目里实操过的经验和踩过的坑完整梳理一遍。2. 定位慢 SQL没有目标调优就是瞎忙2.1 慢查询日志的正确打开方式MySQL 提供了慢查询日志专门记录执行时间超过阈值的 SQL。这一步是调优的起点但很多人在配置上就出了问题。慢查询日志默认是关闭的需要手动开启。我个人习惯在配置文件里这样设置slow_query_log 1 slow_query_log_file /data/mysql/mysql-slow.log long_query_time 1 log_queries_not_using_indexes 1几个参数说明一下。long_query_time我设置成 1 秒即超过 1 秒的记录。生产环境有专门的 DBA 团队的话可以设成 0.5 秒甚至更低。log_queries_not_using_indexes这个参数很多人会忽略它的作用是即使 SQL 执行很快但只要没用索引也会记录这个对发现隐性问题很有帮助。这里有个坑long_query_time的单位是秒支持小数但很多教程里写的是整数。比如你写long_query_time 0.5MySQL 5.7 及以上版本是能正确识别的但有些老版本只认识整数。设置完记得确认一下生效值SHOW VARIABLES LIKE long_query_time;还有一个细节慢查询日志记录的是“执行时间超过阈值”的语句但所谓“执行时间”是指语句从开始执行到返回结果的耗时不包括等待锁的时间。所以前面说的那种被锁阻塞的慢查询用慢查询日志是抓不到的需要结合SHOW ENGINE INNODB STATUS或者 performance_schema 来排查。2.2 用一个查询快速捞出慢 SQL慢查询日志是文本文件直接打开看往往很长而且夹杂着很多无关信息。我一般用mysqldumpslow这个自带工具做汇总mysqldumpslow -s c -t 10 /data/mysql/mysql-slow.log这句命令的意思是按查询次数排序显示前 10 条最频繁出现的慢 SQL。-s c是按 count 排序也可以改成-s t按总耗时排序、-s at按平均耗时排序。注意mysqldumpslow会把 SQL 里的具体值抽象成N和S方便把同类 SQL 归并到一起统计这是它的核心价值。如果嫌命令行工具不够直观也可以直接在数据库里查。MySQL 5.7 开始支持 performance_schema 中的 events_statements_summary_by_digest 表可以按 SQL 摘要维度查执行次数、平均耗时、扫描行数等SELECT DIGEST_TEXT, COUNT_STAR, AVG_TIMER_WAIT / 1000000000 AS avg_ms, SUM_ROWS_EXAMINED FROM performance_schema.events_statements_summary_by_digest ORDER BY AVG_TIMER_WAIT DESC LIMIT 10;这张表的字段很多里面SUM_ROWS_EXAMINED和SUM_ROWS_SENT的比例特别值得关注。如果扫描行数是返回行数的几十倍甚至几百倍基本可以断定这条 SQL 的访问路径有问题该加索引或者该改写。2.3 顺手排查性能基线除了慢查询日志生产环境排查慢 SQL 时我还会顺手看一下当时的系统状态。很多时候 SQL 本身没有问题是资源占用导致整体性能下降。重点关注三个指标CPU 使用率、磁盘 IO 的iowait、以及 InnoDB 的Buffer Pool命中率。SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read_requests; SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_reads;用这两个值计算命中率(read_requests - reads) / read_requests。正常情况下命中率应该在 99% 以上如果低于 95%就要考虑是不是 Buffer Pool 配得偏小或者有大量全表扫描在刷数据页。这一步做完定位阶段才算完整。事实清楚之后才进入真正核心的环节分析执行计划。3. EXPLAIN 要这样看执行计划里的性能密码3.1 一条 SQL 的执行计划长什么样拿到一条慢 SQL我做的第一件事永远是执行EXPLAIN。最基础也最常用的语法EXPLAIN SELECT o.order_id, o.amount, u.name FROM orders o JOIN users u ON o.user_id u.id WHERE o.status PAID AND o.create_time 2024-01-01;执行后返回的结果集里每一行代表执行计划中的一步关键字段有id、select_type、table、type、possible_keys、key、rows、Extra。很多人只看key有没有索引其实远远不够。真正的信息量在type、rows和Extra这三个字段上。type表示访问类型从上到下性能从好到差排列为type含义性能system系统表只有一行最好const主键或唯一索引等值查询最好eq_ref被驱动表通过主键/唯一索引等值匹配很好ref普通索引等值匹配好range索引范围扫描还可以index全索引扫描差ALL全表扫描最差正常情况下能优化到ref或range是及格线能到eq_ref或const就是满分。如果看到ALL基本可以确定这条 SQL 有问题需要加索引或者改写。3.2 rows 不是精确值但可以参考rows字段估算的是 MySQL 优化器认为需要扫描的行数。注意这是估算值不是实际执行值。它来自统计信息统计信息又不一定准所以有时候rows和实际相差很大。不过作为参考完全够用。看rows的目的是判断是否发生了“扫描行数远大于返回行数”的情况。比如一条 SQL 查询结果是 1 行但rows是 10 万这就说明访问路径选得不好。优化方向很明确把扫描行数降下来。这里补充一个实操技巧EXPLAIN只能看估算值想看实际执行时真实的扫描行数需要用到EXPLAIN ANALYZE。这是 MySQL 8.0.18 引入的功能它会真正执行 SQL并返回每个步骤的实际耗时和行数EXPLAIN ANALYZE SELECT o.order_id, o.amount, u.name FROM orders o JOIN users u ON o.user_id u.id WHERE o.status PAID;输出结果里会显示actual time和rows这才是真实执行数据。MySQL 8.0 以下没有这个功能可以用SET profiling 1加SHOW PROFILES来看执行耗时但拿不到精确到每一步的行数。3.3 Extra 字段里藏着的关键信息Extra字段经常会被忽略而且它常伴随一些“看起来很不妙”的内容。我挑几个高频出现的说下。Using filesort不是磁盘文件排序而是表示无法利用索引顺序需要额外排序。这个一般伴随 ORDER BY 出现优化方式是让排序字段跟查询条件构成联合索引利用索引天然有序的特性消除排序。Using temporary使用了临时表。通常出现 GROUP BY 或 DISTINCT 的场景同样的优化思路尽量让分组字段走索引。Using index这是个好消息表示覆盖索引生效查询只需要扫描索引不用回表。Using where表示在存储引擎返回记录后又加了一层过滤条件。这个本身不算大问题但要结合type一起看。如果type是 ALL 且Using where那就是全表扫描后过滤必须优化。Using index condition这是 Index Condition PushdownICP特性在 MySQL 5.6 之后默认开启。表示存储引擎层先根据索引进行条件过滤再返回表数据。能有效减少回表次数在联合索引的场景下经常出现。我自己看执行计划有一个习惯先把type扫一眼凡是 ALL 的记录标出来再核对rows和返回行数的比例最后看Extra里有没有filesort和temporary。三个维度过一遍SQL 的问题基本就暴露了。4. 索引优化调优的胜负手4.1 索引为什么这么快B 树的逻辑索引之所以能加速查询是因为它底层用 B 树这种数据结构组织数据。B 树的特点是数据都存放在叶子节点且叶子节点之间通过双向指针串联非叶子节点只存索引键值和子节点指针不存实际数据树的高度一般维持在 3 到 4 层这意味着什么一次索引查找只需要经过 3 到 4 次磁盘 IO就能从 2 千万行的表里定位到目标数据。对比全表扫描如果数据量太大可能要做上万次 IO。这就是索引能带来数量级性能提升的原因。另外一个常被忽略的优势B 树的叶子节点有序。所以索引天然支持范围查询和排序ORDER BY 字段如果恰好是索引列就能省掉 filesort。这也是前面说的“用联合索引消除排序”的底层原理。4.2 最左前缀法则联合索引的灵魂联合索引是 SQL 调优里使用频率最高的优化手段但它的使用有铁律最左前缀法则。简单说查询条件里必须包含联合索引最左边的列索引才会被用到。假设表里有联合索引(a, b, c)WHERE a 1命中索引WHERE a 1 AND b 2命中索引WHERE a 1 AND b 2 AND c 3完全命中WHERE b 2无法使用该索引WHERE c 3无法使用该索引这个规则是 B 树的特性决定的联合索引的排序规则是先按第一列排同第一列再按第二列排以此类推。所以没有第一列作为前缀就无法在索引树上定位。实际项目里我见过不少这种情况建了联合索引但查询条件里没用上第一列。比如订单表建了(create_time, status)索引业务方的核心查询却是WHERE status PAID。这个查询用不上索引要么在 status 列单独建索引要么调整查询逻辑让 create_time 参与过滤。判断一条查询能否用到联合索引最靠谱的方法就是执行 EXPLAIN 看key字段而不是靠猜。4.3 覆盖索引与回表的性价比权衡InnoDB 的主键索引聚簇索引的叶子节点保存的是整行数据普通索引二级索引的叶子节点保存的是索引列和主键值。这就导致一个现象通过普通索引查数据时先找到主键值再拿主键去聚簇索引里查整行这个过程叫回表。如果 SQL 要查询的列恰好全都包含在某一个索引中那么连回表都不需要直接在索引上查出结果这就是覆盖索引。覆盖索引能大幅减少磁盘 IO。举个实际例子查询用户表的手机号和昵称SELECT phone, nickname FROM users WHERE phone 13800138000;如果只在 phone 列建了单列索引执行时找到主键后还要回表查 nickname。如果改成联合索引(phone, nickname)那么 phone 和 nickname 都在索引里直接返回不需要回表。虽然多了一个索引但查询速度快很多。不过覆盖索引不是越多越好。索引本身占用空间写入时也要维护加索引会增加插入和更新的开销。我自己判断时主要看两个点一是高频查询的返回列是否稳定二是表写入频率是否很高。如果一秒钟要写几万行那加索引就要谨慎写入性能下降的代价可能超过查询收益。4.4 索引失效的常见场景写在前面怕你踩坑这部分全是经验和教训我直接把最常见的几个坑列一下对索引列使用函数WHERE DATE(create_time) 2024-01-01会失效应改成WHERE create_time 2024-01-01 AND create_time 2024-01-02。隐式类型转换列类型是 varchar查询时用了数字WHERE phone 13800138000索引失效。字符串列上必须带引号。LIKE 模糊匹配左通配WHERE name LIKE %张用不上索引WHERE name LIKE 张%可以。OR 连接非索引列WHERE id 1 OR status 1如果 status 没有索引整个查询可能退化。建议改成 UNION ALL。NOT IN、!、 通常不走索引能改成范围查询的就改。联合索引违反最左前缀前面讲过不复述了。这里单独说下第 2 条。隐式类型转换不只是字符串列查数字的问题还有字符集不一致导致索引失效。表 join 时两边关联字段如果一个是 utf8mb4 一个是 utf8MySQL 会做隐式转换索引大概率失效。建表时期定义关联字段统一字符集是最省事的方案。5. SQL 改写实战同样结果不同写法5.1 深分页优化为什么越翻越慢分页查询是业务系统最常见的慢 SQL 来源尤其是深分页。看这段SELECT * FROM orders ORDER BY create_time DESC LIMIT 100000, 20;这条 SQL 看着简单但执行时 MySQL 需要先扫描出前 100020 行然后丢弃前 10 万行只返回最后 20 行。前面扫描的行全部浪费了。数据量越大翻页越深代价越高。经典优化方案是延迟关联。先只查主键再关联回原表取完整数据SELECT o.* FROM orders o JOIN ( SELECT id FROM orders ORDER BY create_time DESC LIMIT 100000, 20 ) t ON o.id t.id;子查询里只扫描主键和排序字段。如果(create_time, id)上建了联合索引子查询本身就是覆盖索引查询不需要回表扫描代价小得多。外层再按主键关联取完整行最多只查 20 行完整数据。另一个方案是基于游标的分页也就是记住上一次查询最后一条数据的位置SELECT * FROM orders WHERE create_time 2024-01-15 10:00:00 ORDER BY create_time DESC LIMIT 20;详情页、信息流这类应用这个方案更优雅。但有一个限制要求排序字段严格去重且可以比较。如果 create_time 有重复值会出现漏数据的问题需要额外带上 id 条件才能保证稳定。5.2 子查询与 JOIN 的取舍MySQL 5.6 之前对子查询的优化有限很多人养成了“能用 JOIN 就不用子查询”的习惯。5.6 之后引入了子查询优化器部分场景下会自动改写为 JOIN情况改善不少。但实测下来半连接类型的子查询EXISTS、IN在不同 MySQL 版本下执行计划差异很大。比如SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE status 1);在 MySQL 5.7 中优化器通常会把 IN 子查询改写为 semi-join实际执行的其实是类似 JOIN 的操作。一旦改写失败就可能退化成“对每条 orders 都执行子查询”效率惨不忍睹。遇到这种情况手工改成 JOIN 更可控。换成 NOT IN 要特别小心。NOT IN在子查询包含 NULL 值时结果与预期不一致这个属于语义问题。更麻烦的是 NOT IN 在部分 MySQL 版本中不走索引导致全表扫描。建议写成NOT EXISTS配合关联条件或者用 LEFT JOIN 加 IS NULL 的方式。5.3 聚合查询让临时表消失GROUP BY 慢的根源通常在于 MySQL 需要先建立临时表来分组聚合。最理想的做法是让分组字段走索引这样 B 树天然有序优化器可以直接顺序扫描完成分组完全不需要临时表。SELECT user_id, COUNT(*) FROM orders WHERE create_time 2024-01-01 GROUP BY user_id;最优方式是建一个(user_id, create_time)联合索引。这样 WHERE 条件过滤 create_timeGROUP BY user_id 又利用索引顺序一次扫描完成。如果 SQL 里既有 GROUP BY 又有 ORDER BY还要保证排序字段与分组字段一致。不一致时额外加临时表排序性能直接恶化。5.4 利用 LIMIT 提前终止有些带 EXISTS 的关联查询业务上只需要判断“有没有”不需要知道具体数量。这类查询加上LIMIT 1能显著减少扫描量。SELECT 1 FROM users u WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.user_id u.id AND o.amount 1000 LIMIT 1 );MySQL 在找到满足条件的第一行后会提前终止扫描LIMIT 1 让这个终止时机足够早。注意这里子查询里的 SELECT 1 是性能优化习惯生产环境不要用SELECT *没有任何理由需要查出整行数据。6. 优化器与统计信息别让引擎帮你做错误决定6.1 ANALYZE TABLE 的操作价值MySQL 的优化器是基于统计信息选择执行计划的统计信息来自mysql.innodb_table_stats和mysql.innodb_index_stats这两张系统表。统计信息有精度损失更新频率也有限制。数据变更剧烈后统计信息很可能和实际严重偏离优化器选了错误的索引。遇到“明明有合适的索引EXPLAIN 里却不是最优计划”的情况第一步不是改 SQL而是刷新统计信息ANALYZE TABLE orders;执行后重新 EXPLAIN很多情况下问题自动消失。我曾经处理过一个线上案例某张表的统计信息显示一个索引的区分度很低优化器全表扫描。ANALYZE TABLE 之后优化器立刻换成了正确索引查询从 3 秒降到 20ms。这个动作的成本很低值得作为调优的第一步试试。6.2 优化器正确的代价估算逻辑理解优化器为什么“乱选”索引先说下它的估算逻辑。优化器估算执行代价时主要看两个因素扫描行数和回表成本。对于普通索引每扫描一行都要按主键回表查询一次如果回表的成本很高优化器可能放弃索引而选择全表扫描。这就是为什么提升索引选择性的“区分度”这么重要。如果索引列大量重复比如 status 列只有两个值区分度极低。优化器算了算用索引需要大量回表还不如全表顺序扫描来得快。结论是区分度过低的列不适合单独建索引但适合放在联合索引的后面列。6.3 经典查询场景的优化器参数补充MySQL 8.0 之前通过optimizer_switch可以控制优化器部分特性常见的有SET optimizer_switch index_condition_pushdownon; SET optimizer_switch block_nested_loopon; SET optimizer_switch semijoinon;8.0 之后默认配置普遍够用直接改开关的场景少了很多。但有一个参数在高并发场景值得关注optimizer_trace。它能把优化器做决策的完整过程记录下来SET optimizer_traceenabledon; SELECT ...; SELECT * FROM information_schema.OPTIMIZER_TRACE; SET optimizer_traceenabledoff;这个功能展示的信息量非常大包含优化器考虑过的每一条执行路径和成本计算过程。当 EXPLAIN 看不透“为什么没用这个索引”时翻一下 OPTIMIZER_TRACE答案往往就在里面不用瞎猜。7. 常见问题与排查技巧实录把平时项目里高频出现的问题整理成一张速查表方便参照问题现象可能原因排查手段兜底方案SQL 执行慢但 EXPLAIN 显示走了索引回表次数多、扫描行数大查看 rows 与返回行数比改覆盖索引或联合索引有索引却不走统计信息过期ANALYZE TABLE 后重新 EXPLAIN索引列涉及函数/隐式转换需改写 SQL关联查询慢关联字段字符集不一致查看 JOIN 字段的 charset统一为 utf8mb4翻页越来越慢深分页扫描大量无用行查看 LIMIT 偏移量延迟关联或游标分页GROUP BY 慢使用了临时表查看 Extra 字段分组字段建索引ORDER BY 慢filesort查看 Extra 字段排序字段纳入联合索引锁等待导致慢并发 UPDATE 锁冲突SHOW ENGINE INNODB STATUS缩小事务范围、减少持锁时间除了表格里的内容还有三个排查技巧是文档里看不到的属于实战里积累出来的经验。第一个是小结果集直接落内存的验证方法。优化完 SQL 之后用相同条件的查询在低峰期实测观察响应时间和SHOW PROFILE的输出。SHOW PROFILE 能看到每条 SQL 的完整执行耗时分布包括 Sending data、Sorting result、Statistics 这些阶段。每个阶段的耗时占比能告诉你下一步优化该朝哪个方向走。第二个是逐步缩小条件范围定位问题列。当一条多表 JOIN 的 SQL 很慢但 EXPLAIN 看不出来明显问题可以用“分段还原”的方法先只查 A 表再只查 B 表再查 A JOIN B逐步定位慢在哪一层。这个方法效率很高特别是当优化器做了不理想的子查询展开时单看整条 SQL 的执行计划很容易被误导。第三个是利用通用日志做回归对比。优化前和优化后把同一时段内的慢 SQL 数量做对比。MySQL 的log_queries_not_using_indexes参数开启后能持续记录“没用索引的查询”。这个数据能用来评估优化效果也能防止新上的功能悄悄引入新的慢查询。8. 写在最后的体会SQL 调优这件事表面上是在调 SQL实际上是在调“理解”。理解你的数据分布理解优化器的行为逻辑理解 InnoDB 的存储结构理解业务到底需要什么。很多人拿一条 SQL 来问我我反问他业务场景和数据量时他答不上来。不掌握这些信息调优就只能靠猜靠试效率一塌糊涂。我个人的工作习惯是接到一条慢 SQL先看统计信息是否准确再看执行计划再分析索引设计最后才动手改 SQL。绝大多数慢查询在这一步就能解决。真正需要重写 SQL 的反而是少数。顺序反了的话可能你辛苦改完一个写法结果发现换个索引就能解决白费功夫。最后分享一个日常习惯每次调优完把慢 SQL、执行计划、修改内容和优化前后的耗时记录到文档里。时间长了你会发现很多表、很多查询模式是有共性的。这份积累比任何调优工具都值钱。