做 MySQL 优化这行我见过太多人看见慢查询就无脑加索引。加完索引发现不生效又开始怀疑 MySQL 版本有问题、数据量太大了、服务器磁盘不行了。其实绝大多数时候问题都不在这些地方而是你根本没搞清楚查询到底是怎么执行的。我自己早期做过一个订单列表功能越查越慢。我当时咔咔给表加了六个索引结果慢的问题没解决INSERT 反而慢了不少。后来老老实实用 EXPLAIN 一分析发现该用的索引压根没被选上建的一堆索引里真正被查询用到的只有两个。从那以后我给自己定了个规矩任何索引的增删都必须先跑一遍 EXPLAIN让查询执行计划给我“签字画押”。这篇文章不聊大而全的优化理论就讲我怎么用 EXPLAIN 来判断一条查询该加什么索引、不该留什么索引。整个过程会带几个真实 SQL 案例每个都有前后对比和数据佐证可以直接搬到你的业务里参考。1. EXPLAIN到底在告诉你什么一张表读懂查询执行计划1.1 EXPLAIN是一条命令不是一个“建议”先摆个基本认知EXPLAIN 不是 MySQL 给你的优化建议而是 MySQL 优化器基于当前表结构和统计信息算出来的“执行计划”预览。你在执行 SELECT 之前优化器已经把表访问顺序、索引选择、连接方式都定好了EXPLAIN 只是把这个计划打印出来给你看。用法就一句话EXPLAIN SELECT user_id, status, created_at FROM orders WHERE user_id 123;在 SELECT 前面加 EXPLAINMySQL 不会真的去拉取结果集而是返回一张表告诉你它打算怎么查。你需要做的是在脑子里把这张表翻译成一句话“它是准备翻全表还是走索引还是先在索引里定位再回表拿数据”。这句话想清楚了优化方向也就清楚了。1.2 输出列逐个拆解别被十几个字段吓到EXPLAIN 的输出列不算少不同 MySQL 版本还略有差异。很多人拿着输出问我“这一大堆是什么意思”其实真正需要反复看的只有五列。先把全貌列出来列名含义是否需要重点看id每个 SELECT 子句的序号单表查询不用太在意select_typeSELECT 类型SIMPLE、PRIMARY、SUBQUERY、DERIVED 等子查询/多表时看执行顺序table访问哪张表多表连接对照 id 看partitions命中的分区分区表才用到type访问类型从 system 到 ALL重点索引好坏的直接体现possible_keys优化器认为可能用到的索引和 key 对比看最有价值key优化器最终选中的索引重点key_len用到的索引前缀字节数判断联合索引实际用了哪几列ref索引等值匹配时参照的列或常量和 key 配合看rows优化器估算需要扫描的行数重点但只是估算值filtered过滤后剩余行数的百分比配合行数判断过滤效果Extra额外的执行信息重点filesort 和临时表在这里暴露多数时候你只需要盯住 type、key、key_len、rows、Extra 这五列就足够判断一条查询的健康度了。1.3 真正要关注的五件事先说 type。type 如果出现 ALL代表全表扫描大表里的 ALL 基本都是慢查询的元凶。但要注意如果表只有几百行全表扫描并不慢反而可能比走索引还快因为索引回表有额外开销。优化永远是看场景不是看单个指标。再说 key。key 是优化器最终选中的索引如果 key 是 NULL说明 MySQL 没选任何索引。即使 possible_keys 罗列了一堆候选索引也没用这时候要么是没索引可用要么是索引被查询条件写失效了。key_len 很容易被忽略但它信息量很大。联合索引 (a, b, c) 到底用了哪几列的有序性看 key_len 就知道。比如 a、b、c 都是 int每个 4 字节如果 key_len 是 8说明 ab 两列被用于定位c 没有参与。这就是判断索引设计是否“用足”的关键证据。rows 是优化器估算的扫描行数虽然是估计值但量级通常是可信的。如果 rows 到了几十万即使 key 不是 NULL也说明索引的选择性不好或者查询条件本身就没法有效过滤数据。Extra 里的标志最直观。出现 Using filesort 说明查询要额外排序Using temporary 说明用了临时表这两个都是危险信号。反过来Using index 表示覆盖索引Using index condition 表示二级索引条件下推都是值得高兴的事。把这五列养成条件反射一张 EXPLAIN 表三秒钟就能读完比看完整份输出高效得多。2. 读懂type索引用得好不好全看这一列2.1 type 从快到慢的完整序列type 列的值不是随便枚举的它是一条完整的“速度阶梯”。从快到慢大致是system const eq_ref ref range index ALL我给每个层级配一个直观解释system表里只有一行极致情况。const主键或唯一索引匹配到一行一次命中。比如 WHERE id 100。eq_ref多表连接时被驱动表通过主键或唯一索引等值匹配每一行只匹配一条。连接查询最理想的内层访问方式。ref使用普通索引做等值匹配可能匹配到多行。比如 WHERE status 0status 不是唯一索引。range索引范围扫描常见于 、、BETWEEN、LIKE abc% 这类条件。index扫描整个索引树比全表扫描快一点因为索引树比数据页小但本质上还是“全扫”。ALL全表扫描从头到尾翻数据页最糟糕的访问方式。2.2 用坐电梯来理解这套速度层级很多人第一次接触 type 觉得很抽象我用一个生活化的类比等电梯。ALL 相当于从 1 楼走楼梯上 30 楼每一层都经过index 相当于坐了一趟每层都停、但不让你出电梯的“慢梯”比走楼梯快但依然费时间range 相当于从 10 层坐到 20 层只停一段ref 相当于你按了目的地楼层中间不耽误const 像专属电梯按一下就到位。这么一想EXPLAIN 里的 type 不再是英文字母而是每天都可能遇到的问题。2.3 实战判断标准什么样的 type 算合格这不是死标准得由表大小和查询特征共同决定。我自己有一套判断习惯单表等值查询能到 ref 以上就算合格const、eq_ref 更优。等值加范围混合的查询能到 range 就接受。如果出现 index 或 ALL但估算扫描行数只有几百行可以先不强求一旦 rows 过万就该认真优化。连接查询里被驱动表的访问类型最低也要 range理想是 ref 或 eq_ref。记住一句话优化不是非要把 type 改成 const 才叫成功而是让 type 和 rows 的组合符合查询特征把无效的大范围扫描干掉。3. 实操案例一慢查询“加了索引还是慢”问题出在哪3.1 真实场景订单查询越查越慢假设有一张订单表 orders表结构大致如下CREATE TABLE orders ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id BIGINT NOT NULL, status TINYINT NOT NULL DEFAULT 0, pay_type TINYINT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL, amount DECIMAL(10,2) NOT NULL, INDEX idx_user (user_id), INDEX idx_status (status) ) ENGINEInnoDB;业务上经常跑这样一条查询取出某个用户、状态为待支付、支付方式为线上的订单列表用于后台自动催付。SELECT * FROM orders WHERE user_id 10086 AND status 0 AND pay_type 1 ORDER BY created_at DESC LIMIT 20;业务方反馈这条 SQL 越来越慢问我是不是该把 pay_type 也加到索引里。我听到这句话就明白了典型的“还没看执行计划就急着加索引”。先跑 EXPLAIN让数据说话。3.2 第一次EXPLAIN问题全部暴露执行EXPLAIN SELECT * FROM orders WHERE user_id 10086 AND status 0 AND pay_type 1 ORDER BY created_at DESC LIMIT 20;关键输出如下typepossible_keyskeykey_lenrowsExtrarefidx_user, idx_statusidx_user845600Using where; Using filesort看到没有possible_keys 里有两个索引最终 key 只选了 idx_user。为什么因为 user_id 是等值条件大概率过滤性比 status 好。但 rows 显示要扫描 45600 行说明这个用户的历史订单量很大MySQL 在 idx_user 上定位到一批订单后还得继续过滤 status 和 pay_type 两个条件最后还要对 created_at 排序。Extra 里有两个关键信号Using where 说明其他过滤条件是在回表后执行的Using filesort 说明排序没走索引要额外做一次文件排序。这两个加在一起就是这条查询慢的根源。3.3 设计联合索引等值条件在前排序字段在后基于上面的执行计划优化目标非常清晰让 user_id 定位之后能直接利用索引继续过滤 status 和 pay_type再让 created_at 的有序性承担排序省掉 filesort。很多人天真地以为把 WHERE 里的字段都塞进索引就行。但列顺序是有讲究的等值条件放前面范围条件放中间排序字段放最后。这里 user_id、status、pay_type 都是等值匹配created_at 负责排序所以合理的设计是ALTER TABLE orders ADD INDEX idx_user_status_pay_created (user_id, status, pay_type, created_at);为什么要让 created_at 放最后因为联合索引本身就是一棵按列顺序排序的 B 树。前面的列都是等值匹配时后面的 created_at 列才能保持全局有序MySQL 才能通过索引直接反向读取来满足 ORDER BY created_at DESC。如果把 created_at 放在中间前面一旦有等值条件它的排序性就被“截断”了排序依然要 filesort。另外 pay_type 和 status 之间的顺序实际影响不大因为它们区分度都低。经验法则等值字段按选择性从高到低排区分度高的放前面这样才能让每层索引树分支尽快收窄。3.4 第二次EXPLAIN索引增加后的前后对比索引建好后再跑一次 EXPLAINEXPLAIN SELECT * FROM orders WHERE user_id 10086 AND status 0 AND pay_type 1 ORDER BY created_at DESC LIMIT 20;优化后的输出typekeykey_lenrowsExtrarefidx_user_status_pay_created10128Using index condition; Backward index scanrows 从 45600 降到 128下降了两个数量级。key_len 是 10正好是 user_idBIGINT 8 字节 statusTINYINT 1 字节 pay_typeTINYINT 1 字节这三列等值条件占用的字节数。created_at 没有计入 key_len因为它不参与定位而是通过索引反向扫描来满足排序。Extra 里的 Backward index scan 是 MySQL 8.0 的新能力表示它反向扫描索引来支持 DESC 排序不再需要 filesort。如果业务上只需要查订单号的某些列还能把查询列都收进索引做覆盖索引连回表都省掉。但覆盖索引会进一步加大索引体积属于费用权衡不是无脑追求。3.5 这个案例告诉我们什么这个案例完全印证了标题那句话不要一味创建索引。建索引之前表上已经有两个单列索引但它们各管各的没法形成合力。真正解决问题的是根据查询重新设计联合索引。更值得注意的是新索引建立之后旧的 idx_user 看起来就有点冗余了。因为 idx_user 只有 user_id 一列而新索引以 user_id 开头并且覆盖了更多列任何能用 idx_user 的查询都能改走新索引。这意味着 idx_user 可以考虑删除。这就是“根据查询增加和删除索引”的完整闭环我在下一个案例详细展开。4. 实操案例二不删无用的索引优化等于白做4.1 索引的代价你加的每个索引都会反噬索引从来不是免费的午餐。每个二级索引都会在写入时同步维护INSERT、UPDATE、DELETE 的代价会随索引数量上升每个索引还占用磁盘空间和 InnoDB 缓冲池的内存。一张 1000 万行的表每多一个二级索引额外占用的磁盘空间经常以 GB 计算。更重要的是索引维护是实时的。你给高频写入的表增加一个索引等于让每一次 INSERT 都多写一棵 B 树。所以“索引越多查询越快”这种认知在写多读少的业务里会变成灾难。尤其是为了某个查询新加了联合索引之后之前专为旧查询建立的单列索引很大概率变成冗余。这时不删掉就是在持续给写入流程上枷锁。4.2 怎么发现冗余索引用信息模式查索引关系我在案例一里建了 idx_user_status_pay_created它是以 user_id 开头的四列联合索引。此时旧的 idx_user(user_id) 就完全冗余因为任何查询如果用 idx_user都可以改走新联合索引而且新索引能过滤更多条件。除了人工分析更系统的方法是直接查系统元数据。看表上所有索引SELECT INDEX_NAME, GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS columns FROM information_schema.STATISTICS WHERE TABLE_SCHEMA mydb AND TABLE_NAME orders GROUP BY INDEX_NAME;输出结果大概是这样的INDEX_NAMEcolumnsPRIMARYididx_useruser_ididx_statusstatusidx_user_status_pay_createduser_id,status,pay_type,created_at一眼就能看出idx_user 的列组合是 idx_user_status_pay_created 的最左前缀属于重复索引。判断规则很简单如果索引 A 的列组合是索引 B 的列组合的连续前缀并且排序方向一致索引 A 就是冗余的。这里 idx_user 只是 user_id正是联合索引的最左前缀可以安全删除。然后执行删除ALTER TABLE orders DROP INDEX idx_user;删除后要回归线上验证。稳妥的做法是先跑一遍所有涉及该索引的业务 SQL 的 EXPLAIN确认 key 列会落到联合索引或者有等价执行路径再在低峰期执行 DDL。4.3 慢查询日志加实际使用度排查用数据找“僵尸索引”如果表上索引组合不直观没法一眼判断谁冗余可以用慢查询日志和 performance_schema 做统计。先把慢查询日志打开SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;然后跑一段时间捞出来哪些 SQL 慢。对每条慢 SQL 执行 EXPLAIN记录它实际使用的 key。一段时间之后你会发现某些索引在 EXPLAIN 里从未被选为 key。这种“永远没出场”的索引要么是设计时拍脑袋建的要么是它对应的查询早已下线可以纳入删除候选。但这里有一个重要提醒EXPLAIN 没走某个索引不代表业务就一定没用到它。优化器在小表上可能故意选择全表扫描因为走索引回表反而更慢。判断时一定要结合表大小和查询特征千万别只凭一次 EXPLAIN 就草率删索引。我的习惯是先把要删的索引定义备份到版本控制删完之后观察线上写入性能和慢查询曲线出了问题马上从备份脚本里捞回定义重建。删索引可以很快重建索引在大表上可能要锁表很久所以预案必须做在前面。4.4 删除索引后的效果写入更轻了我在一张约 600 万行的业务表上做过类似操作删掉两个冗余单列索引后这条业务链路的写入平均响应时间降了 15% 左右。原因很简单少了两个二级索引每次 INSERT 插入索引树的节点操作明显减少同时 InnoDB 缓冲池里也少了两棵索引树占用的内存。查询端因为有覆盖场景更合理的联合索引不但没有变慢部分 SQL 反而更快了。这就是“加减结合”的甜头。5. 索引失效的七个坑EXPLAIN一查一个准5.1 对索引列做函数操作最常见的坑。假设 orders 表有 idx_created(created_at)查询SELECT * FROM orders WHERE DATE(created_at) 2024-03-18;EXPLAIN 通常显示 typeALLkeyNULL。原因很简单索引里存的是原始日期值不是 DATE(created_at) 的结果。对列做函数运算后索引顺序对不上这个条件了。改成范围查询就正常了SELECT * FROM orders WHERE created_at 2024-03-18 AND created_at 2024-03-19;改完 type 会变成 rangerows 大幅下降。这也是业务里常见的错误写法排查时看到 DATE()、MONTH()、YEAR() 这类函数直接想都不想要改写。5.2 隐式类型转换的坑假如 user_id 列是 varchar(32)但业务代码里传了数字SELECT * FROM users WHERE user_id 10086;MySQL 会把字符串列转成数字再比较等于对 user_id 做了隐式函数操作。EXPLAIN 里同样会发现索引失效。解决办法就是让查询参数的类型和列类型保持一致代码里不要用数字接字符串列。这个坑在 ORM 框架里尤其常见因为 ORM 生成的参数类型有时候会跟表结构对不上。5.3 前导模糊查询SELECT * FROM orders WHERE remark LIKE %加急%;前缀不确定索引无法定位起点只能全表扫。反过来SELECT * FROM orders WHERE remark LIKE 加急%;这就是 range 查询可以用索引。业务上如果非要做包含匹配考虑全文索引或者专门检索系统别死磕单表 SQL。5.4 OR 条件把好牌打烂SELECT * FROM orders WHERE user_id 123 OR status 0;如果 user_id 和 status 各有索引优化器可能不选择“分别走索引再合并”而是直接全表扫描。因为两个条件跨列MySQL 要算出两个集合再求并集代价往往比全表扫描还高。稳妥的写法是用 UNION ALL 拆分SELECT * FROM orders WHERE user_id 123 UNION ALL SELECT * FROM orders WHERE status 0;重写后两条子查询各自都能用索引。不过这种改写要先确认业务语义是否允许两个条件取并集。5.5 NOT IN 和 NOT LIKE理论上 MySQL 判断不等于条件时可以先走索引取全集再排除但大多数优化器会直接选全表扫描因为“不等于”的分支太多选择性太差。比如 status 0。如果一个列只有两三个取值你反而可以用 IN 改写让查询变成等值匹配。这种改写要小心业务语义但方向上是对的。5.6 联合索引不满足最左前缀联合索引 (a, b, c) 只能从 a 开始用。查询里如果直接只写 b 1 和 c 2EXPLAIN 往往显示 typeindex 甚至 ALL。虽然索引文件存在但没法用前缀定位。这也是为什么设计联合索引前一定要把高频查询条件的列顺序想清楚。5.7 排序方向和索引顺序不匹配索引 (created_at) 默认升序存储你 ORDER BY created_at DESCMySQL 8.0 可以反向扫描索引Extra 会显示 Backward index scan这不算失效。但如果你在联合索引里有多个排序字段比如 ORDER BY a ASC, b DESC索引按 (a ASC, b ASC) 存储此时两个字段排序方向不一致就会触发 Using filesort。如果必须有这种混排MySQL 8.0 之后可以给 b 列建降序索引来贴合。5.8 失效场景速查表场景失效原因EXPLAIN 常见表现推荐改法函数运算索引列被计算ALL / keyNULL改写为范围查询隐式类型转换列类型和参数类型不一致ALL / keyNULL统一类型LIKE %xx 前导模糊无固定起点ALL改为后缀匹配或全文索引OR 跨列条件合并成本高ALL 或索引合并不稳定UNION ALL 拆分NOT IN / 选择性差ALL语义允许时改用 IN最左前缀违反联合索引从中间列开始用index / ALL调整查询或改索引列顺序排序方向不匹配索引序与需求相反Using filesort单独索引或降序索引排查这些坑的通用方法只有一个对每一条可疑 SQL都带着 EXPLAIN 去看盯着 type 和 Extra 判断方案优劣而不是靠猜。6. EXPLAIN 进阶JSON 格式和 EXPLAIN ANALYZE 怎么用6.1 FORMATJSON看优化器算的成本普通表格输出不够用时可以用 JSON 格式EXPLAIN FORMATJSON SELECT * FROM orders WHERE user_id 10086 AND status 0 AND pay_type 1 ORDER BY created_at DESC LIMIT 20;JSON 输出里会给出每个访问路径的 cost 值最关键的是会在“attached_condition”里显示优化器对每个可走索引的评估。当 possible_keys 里有多个候选索引时JSON 能告诉你优化器为什么选了这个、放弃了另一个。看 cost 不是追求绝对准确而是理解优化器的决策逻辑避免你再建一个它根本不会选的新索引。6.2 EXPLAIN ANALYZE真实执行给你实际行数和耗时MySQL 8.0.18 之后有个更狠的命令EXPLAIN ANALYZE。它不只是给执行计划而是真的执行这条 SQL返回每一步的实际行数、实际耗时和循环次数。EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id 10086 AND status 0 AND pay_type 1 ORDER BY created_at DESC LIMIT 20;输出类似下面这样- Limit: 20 rows (actual time0.4..0.4 rows20 loops1) - Index lookup on orders using idx_user_status_pay_created (user_id10086, status0, pay_type1) (cost2.3 rows128) (actual time0.3..0.3 rows20 loops1)注意看 cost 后面的记录是优化器估算的 rows128而 actual rows 是 20。当 estimated 和 actual 差距巨大时往往是统计信息过期了要跑 ANALYZE TABLE 重新收集统计信息。这是普通 EXPLAIN 永远发现不了的问题。6.3 我的使用经验什么时候用 EXPLAIN ANALYZE线上高并发环境不要随便跑 EXPLAIN ANALYZE因为它真的会执行 SQL一条大扫表查询可能直接打爆数据库。我的习惯是在测试库跑 EXPLAIN ANALYZE线上只跑普通 EXPLAIN 或者 FORMATJSON。如果你非要在线上看真实执行时间建议加 LIMIT 并且挑低峰期比如限制行数的查询影响可控。另外EXPLAIN ANALYZE 的输出非常长不要只看最后一行要顺着执行计划树一层层看。慢的节点往往藏在中间层的“actual time”里而不是最外层。这点和看普通 EXPLAIN 完全不同。7. 建立索引管理的闭环流程从建到删都不拍脑袋7.1 我现在的索引增删五步法这套方法是我踩了不少坑之后固定下来的简单可复制抓慢 SQL。打开慢查询日志或者直接用 performance_schema 查事件统计把 TOP N 慢查询捞出来。逐个 EXPLAIN。对每条慢 SQL 跑 EXPLAIN记录 type、key、rows、Extra重点搞清楚慢点在哪是全表扫、回表过多、filesort还是临时表。逆推索引设计。根据 WHERE 的等值、范围、排序字段设计联合索引列顺序遵循“等值前置、范围中置、排序后置”。如果索引已经存在但没被用上先查失效原因而不是急着再建一个。前后对比验证。建完索引再跑 EXPLAIN 和真实执行时间确认 rows 下降、type 提升、filesort 消失。不能只看 EXPLAIN 满意就完事要在测试库压一遍真实数据量和并发。清理冗余。建立每张表的索引清单和用途清单删除冗余索引和长期未被使用的索引。这一步必须做否则索引会越堆越多。7.2 日常巡检把索引管理变成常规动作我习惯每周跑一次索引健康巡检主要用三条 SQL-- 1. 列出所有索引及对应列 SELECT TABLE_NAME, INDEX_NAME, GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS columns FROM information_schema.STATISTICS WHERE TABLE_SCHEMA mydb GROUP BY TABLE_NAME, INDEX_NAME; -- 2. 对比慢查询日志找出 EXPLAIN 里从未使用过的索引 -- 3. 查看索引占用空间 SELECT TABLE_NAME, INDEX_NAME, ROUND(STAT_VALUE/1024/1024, 2) AS size_mb FROM performance_schema.table_io_waits_statistics_by_table WHERE TABLE_SCHEMA mydb;这套巡检做下来我基本能保证每张表上的每个索引都有“存在的理由”要么是高频查询的执行路径要么是唯一性约束要么是外键约束需要的辅助索引。没有理由的索引一律进入删除候选。回归业务场景再看一遍我处理过的那些慢查询几乎都不需要你疯狂建索引。真正需要的其实是先看懂查询的执行计划再决定一个联合索引怎么建、两个冗余索引怎么删。EXPLAIN 就是把“增加索引”和“删除索引”这两件事变成有据可依的核心工具。我现在写任何优化方案第一页永远是 EXPLAIN 输出对比第二页才是索引变更脚本。这个习惯坚持下来慢查询和索引炸弹都会离你远一点。