MySQL慢SQL优化实战：执行计划分析与索引设计完整指南

做后端开发这些年经手的慢 SQL 少说也有上百条很多人一上来就问“这个 SQL 怎么优化”但说实话这个问题 90% 的答案都藏在执行计划和索引里。MySQL 的 SQL 调优本质就一句话搞清楚一条 SQL 是怎么访问数据的然后想尽办法让它少读数据、少做无用功。这篇我把从定位问题、分析执行计划、设计索引到改造 SQL 的完整套路串一遍既适合刚接触数据库优化的人也适合写过不少 SQL 但总觉得优化思路比较散的同学。里面所有经验都来自我接手过的真实业务场景案例表结构做了简化思路可以直接套用。1. 先别急着改 SQL把问题定位清楚再说1.1 一条 SQL 慢到底是慢在哪一层我拿到一条慢 SQL不会立刻去看怎么写而是先拆它到底是慢在哪个环节。一个查询从发起到返回路径大致是客户端到数据库的网络传输、数据库服务端的排队和锁等待、存储引擎的数据访问。网络层的问题通常出现在跨机房或者公网场景内网环境基本可以直接排除。服务层常见的是锁等待和元数据锁一个 DDL 卡在那里后面所有相关查询都会排队。绝大多数慢 SQL 的根源还是在存储引擎这一层要么是扫描行数太多要么是根本没有可用索引导致 InnoDB 一行行扫全表。这个判断很关键因为不同层的问题解法完全不一样。如果是锁等待你优化 SQL 写法没用得先找到谁拿着锁不放如果是全表扫描那核心就是加索引和改写法。所以第一步永远是定位而不是动手改。1.2 用慢查询日志把嫌疑 SQL 捞出来排查问题的标准动作是先打开慢查询日志把那些超过阈值的 SQL 记录下来。我常用的方式是SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL slow_query_log_file /var/log/mysql/slow.log;long_query_time 1表示超过 1 秒的查询都会落日志。注意这里的全局参数只对后续新建的连接生效如果你用已有的连接去执行可能会觉得“明明开了怎么不记”换一个新会话或者直接写进配置文件重启就比较稳妥。除了慢查询日志performance_schema里的events_statements_summary_by_digest表也很好用它会把结构相似的 SQL 聚合成一条统计记录展示总执行次数、总耗时、平均耗时这些指标。我在某个项目里排查时就发现真正拖垮数据库的不是那条单次执行 5 秒的大查询而是一条单次只要 200ms 却被调用了几万次的小查询聚合视图里总耗时排第一的往往才是真凶。1.3 动手之前先回答三个问题在真正碰 SQL 之前我习惯先确认三件事能省掉后面很多弯路这条 SQL 是偶发慢还是持续慢偶发慢大多跟锁、机器负载、网络抖动有关持续慢多半是执行计划或索引设计有问题。它慢在单行查询还是范围查询单行查询慢基本就是索引没建对范围查询慢还要看扫描范围有多大、排序复杂不复杂。表的数据量级是多少增长快不快百万行和千万行是完全不同的优化策略小表上可能加个索引就完事大表还得考虑归档。这个习惯非常重要。直接上来就改 SQL 十次里有八次是在猜而且猜完之后连改了有没有效果都说不清楚。先把问题框定后面每一步才有依据。2. 读懂执行计划SQL 调优就算成功了一大半2.1 EXPLAIN 输出里必须盯住这五个字段MySQL 分析单条 SQL 最直接的手段是EXPLAIN不需要真实执行查询优化器会把它的执行方案告诉你。比如EXPLAIN SELECT u.id, u.name, o.order_no FROM orders o JOIN users u ON o.user_id u.id WHERE o.status 1 ORDER BY o.create_time DESC LIMIT 20;输出结果里有二十多列但第一眼只需要看五列type、possible_keys、key、rows、Extra。type表示访问类型从好到差大致是system const eq_ref ref range index ALL。看到ALL就是全表扫描这是优化空间最大的信号。key是优化器实际选的索引possible_keys只是候选列表别看到有候选就觉得索引用上了必须看key这一列。rows是优化器估算的扫描行数优化前后有没有量级变化主要靠它判断。Extra里藏着很多细节出现了Using filesort或者Using temporary说明排序和分组没用上索引也是常见的优化点。2.2 一个案例从全表扫描到索引点查有个订单表场景特别典型。业务上经常查某个状态的订单在某个时间之后的数据SELECT * FROM orders WHERE status 1 AND create_time 2024-01-01;表建好以后一直没有加辅助索引只有主键索引EXPLAIN 的结果是这样typekeyrowsExtraALLNULL800000Using where80 万行全扫然后逐行用 where 条件过滤。看到这个结果基本不用再想什么 SQL 写法问题缺的就是索引。我给这个表加了一个联合索引ALTER TABLE orders ADD INDEX idx_status_time (status, create_time);再执行一次 EXPLAINtype变成了refkey变成了idx_status_timerows缩小到了几千行Extra也不再是干巴巴的Using where而是Using index condition。这个差异就是索引带来的量级的变化非常直观。2.3 EXPLAIN 的局限rows 只是估算值rows是优化器基于统计信息做的一个估算不代表真实扫描行数。我踩过这个坑某条 SQL 在 EXPLAIN 里看到 rows 只有几万实际执行却要两三秒。原因是那列数据分布严重倾斜优化器按平均分布来估算结果翻车了。遇到 EXPLAIN 和实际耗时对不上的情况先ANALYZE TABLE 表名刷新一下统计信息再重新看执行计划。如果刷新之后还是不行可以用FORCE INDEX临时强制指定某个索引来验证猜想确认是优化器选错索引再考虑改 SQL 或重建索引。2.4 进阶用 EXPLAIN ANALYZE 看真实耗时MySQL 8.0.18 之后有了EXPLAIN ANALYZE它跟EXPLAIN最大的区别是真的会执行这条 SQL并且把每一步的实际耗时、实际返回行数都打印出来。我在验证优化效果时经常用它EXPLAIN ANALYZE SELECT u.id, u.name, o.order_no FROM orders o JOIN users u ON o.user_id u.id WHERE o.status 1 ORDER BY o.create_time DESC LIMIT 20;输出里能看到哪一步最耗时比如是排序慢还是回表慢对比改动前后的输出效果一目了然。不过要注意它是真实执行的生产环境用之前务必确认这条 SQL 是只读查询或者直接放到只读实例上跑。3. 索引设计才是 SQL 调优的核心战场3.1 联合索引怎么设计先想清楚查询模型我见过太多人一门心思优化 SQL 写法却忽略了一个事实很多慢查询的根源是索引压根没建对。联合索引字段的顺序直接决定它能服务哪些查询。我的做法是先把业务里的高频查询的 where 条件、order by、group by 字段列出来然后按照“等值条件优先、范围条件靠后、排序字段尽量进索引”的顺序去排字段。还是拿订单举例高频查询是“查某个用户某段时间的订单”那么联合索引这样建ALTER TABLE orders ADD INDEX idx_user_create (user_id, create_time);user_id是等值条件放在最前面MySQL 可以通过索引精确定位到该用户的所有订单create_time是范围条件放在后面索引接着在分支上做排序和范围过滤。有一条要记住范围查询之后的索引字段没法继续用于等值匹配所以范围字段放最后比较稳。3.2 覆盖索引让查询完全不回表索引里如果已经包含了查询要的所有字段InnoDB 就不需要回主键表去取其他列这个状态在 Extra 里会显示Using index。这就是覆盖索引的价值。比如SELECT user_id, status FROM orders WHERE status 1;如果存在idx_status_user (status, user_id)这个查询完全可以在辅助索引上得出结果连主键都不用碰省的是一大批随机磁盘 IO。覆盖索引的效果在统计类接口上尤其明显我之前把一个高频查询的 select 列都收进联合索引里耗时从几百毫秒降到了几十毫秒改动成本非常低。3.3 常见索引失效场景绕开这些坑这几类索引失效场景几乎在每一个项目里都能见到场景典型写法失效原因对索引列使用函数WHERE DATE(create_time) 2024-01-01索引存的是原始值函数破坏了比较条件隐式类型转换WHERE mobile 123456varchar 列和数字比较发生隐式转换前导模糊匹配WHERE name LIKE %abc%无法利用 B 树的有序性违反最左前缀只有 idx_a_b却用WHERE b 1跳过了联合索引第一个字段OR 连接不同索引列WHERE a 1 OR b 2优化器可能改走全表扫描隐式类型转换是踩坑率最高的一个。手机号字段在表里是 varchar但业务代码里传进来一个整数MySQL 会把字符串列转成数字去做比较索引直接失效。排查的时候把传入参数改成字符串或者程序里统一定义成字符串类型问题立刻消失。提示核心字段尽量设置 NOT NULL 并加默认值。虽然 MySQL 支持在索引里存 NULL但条件写成IS NULL或IS NOT NULL时优化器的选择经常出乎意料省掉 NULL 能让执行计划稳定不少。4. 几个真实场景的 SQL 改造过程4.1 深分页优化LIMIT 大偏移量怎么救后台管理列表最常见的问题就是深分页SELECT * FROM orders ORDER BY create_time DESC LIMIT 200000, 20;LIMIT 的 offset 越大MySQL 扫描后丢弃的行就越多。你只想要 20 条它却可能扫了 20 万行。EXPLAIN 里 rows 会非常大Extra 里有Using filesort。这里最有效的两个方向是延迟关联和游标分页。延迟关联的思路是子查询里只查主键和排序字段利用索引完成排序和偏移再回到原表取详情SELECT o.* FROM orders o JOIN ( SELECT id FROM orders ORDER BY create_time DESC LIMIT 200000, 20 ) t ON o.id t.id;子查询扫描的是索引数据量小很多回表也只在最后 20 条上发生。游标分页则是把LIMIT offset换成WHERE create_time 上一次的最大值 ORDER BY create_time DESC LIMIT 20每次都是固定小查询性能稳定但要业务侧配合改造。面向 C 端的分页我基本都会改成游标形式后台管理系统如果翻页深度不高用延迟关联就足够了。4.2 JOIN 优化让驱动表越小越好JOIN 本身不是性能毒药问题在于连接顺序和连接字段。MySQL 优化器偶尔会选错驱动表需要人为干预。核心原则很简单用小结果集的表做驱动表被驱动表的连接字段必须有索引。比如SELECT c.name, o.order_no FROM customers c JOIN orders o ON o.customer_id c.id WHERE c.level 3;level 3的会员如果只有几百个而 orders 表有几百万行正确的姿势是先过滤 customers 得到小集合然后每一行去 orders 上通过customer_id索引做点查。EXPLAIN 里orders表的访问类型应该是ref或者eq_ref如果是ALL说明驱动表选反了或者连接字段没索引。还有一个极易忽略的坑连接字段类型必须一致。一个 int 一个 varchar即使内容相同MySQL 也会做隐式转换索引照样失效。设计表结构时关联字段类型保持统一能省掉一类很隐蔽的问题。4.3 SELECT * 是性能杀手之一这个建议听起来很简单在大表上的效果却很惊人。SELECT * 意味着每一行都要回表把整行数据捞出来即使那些 text、blob 字段业务根本用不到磁盘 IO 和网络传输也被白白放大。改成明确列名之后配合覆盖索引性能可能从几百毫秒降到几十毫秒。当然我也不是说所有地方都不能用 SELECT *一次性导出数据的内部脚本用 * 确实方便。但线上高频查询列名写清楚是必须的这既是性能要求也是代码规范的一部分。4.4 where 条件里别做无谓的计算业务需求写成这样很常见WHERE TIMESTAMPDIFF(MINUTE, created_at, NOW()) 30这种写法等于在每一行上执行一次函数计算索引必然失效。改成把列独立放在比较符的一侧WHERE created_at NOW() - INTERVAL 30 MINUTE索引才有机会被用上。这个原则可以推广到所有“列参与运算”的场景加减乘除、函数包裹、字符串拼接只要列被包装过索引基本就废了。改写的方向永远是把计算挪到常量一侧。5. 常见问题与排查经验实录5.1 慢查询日志开了为什么看不到 SQL我遇过几次这种怪现象排查下来无非几个原因。第一种是long_query_time设置成全局参数后旧连接没刷新用新会话或者重连才能生效。第二种是慢的其实是事务里前一条语句持有的锁真正执行的那条 SQL 可能并不慢日志里自然看不到。第三种是查询虽然慢但连接被中途杀掉语句没执行完可能不落日志。如果日志里确实查不到但业务又明显卡顿直接查performance_schema里的语句统计表按耗时倒序排列实时抓取正在执行的慢语句。还有一种情况容易让人抓狂慢日志里全是执行 1.5 秒的低危小查询堆在一起把磁盘都写满了。这时候逐条调优效率极低正确做法是把频率最高的几个 SQL 模板挑出来集中治理。5.2 加了索引执行计划还是不走加完索引发现key是 NULL原因通常是这三个统计信息没刷新优化器压根不知道新索引索引列选择性太低优化器觉得走索引还不如全表扫SQL 写法触发了索引失效规则。加完索引之后先ANALYZE TABLE刷新统计信息这个习惯能避免很多“加索引没用”的误会。选择性方面如果一个字段 90% 都是同一个值比如 status 字段大部分都是 1那走索引要回表大量数据优化器选择全扫反而合理这不是错误。这时候用FORCE INDEX强制走一下索引做对比测试如果 FORCE 之后也不变快就别在这个字段上纠结了换个组合方向更实际。5.3 优化后变快了上线一段时间又慢回去这种情况最容易让人头疼。常见原因是数据分布变了新写入的数据改变了某些字段的选择性优化器选的执行计划和最初不一样了也可能是数据量涨到一定级别原来的索引撑不住了。我的建议是给每条重要 SQL 建立性能基线优化完成后把当时的执行计划、实际耗时、扫描行数记录下来定期复查。同时把慢查询日志接到告警系统里超阈值自动推送。这样性能退化时能第一时间发现问题而不是等用户反馈才去翻日志。注意排查初期不要老把锅甩给 MySQL 配置参数。绝大多数慢 SQL 靠索引和写法就能解决buffer pool 这类参数调整属于最后的手段改起来要谨慎评估副作用。5.4 Extra 里出现 filesort 和 temporary 怎么处理Using filesort并不代表真的把文件写到磁盘它只是说排序没能借助索引完成。Using temporary则说明用了临时表常见于 group by 的字段覆盖不了查询的所有分组场景。这两个信号放在一起通常意味着 order by 或 group by 的字段和 where 使用的索引字段不一致。处理方向就是把排序字段尽可能收进联合索引里。比如高频查询是WHERE status 1 ORDER BY create_time那么(status, create_time)这个索引可以同时解决过滤和排序。如果实在绕不开 filesort先确认能不能减少返回列、能不能避免多表排序同时检查sort_buffer_size的配置但注意它是连接级参数起作用的场景有限别把它当成万能药。我自己在实际项目里做 SQL 调优最大的体会是不要靠感觉不要靠运气。每一次改动之前先看执行计划改动之后再看执行计划和实际耗时把这个循环做扎实绝大多数慢查询都能在半小时内解决。还有一个很小的习惯想分享给大家每条慢 SQL 优化完在代码注释里顺手写一句“当时的问题是什么、为什么这样改”三个月后再回来看你会感谢自己的。SQL 调优这件事没有太多玄学无非就是把执行计划看懂把索引建对把不合理的写法改干净剩下的就是耐心和积累。