有人把 EXPLAIN 打成 explan我第一次在同事的周报里看到这个拼写还愣了几秒以为是某个内部工具名。后来发现拼错的人和用错的人一样多——大多数人知道慢查询要 explain 一下但点开那张十几列的表之后真正能读出问题的人没几个。MySQL 的 EXPLAIN 是排查查询性能最基础的一把刀。它把一个 SQL 在优化器眼里被拆成了几步、每步用什么方式访问数据、预计扫多少行、有没有走索引、要不要额外排序或者建临时表全部摊开给你看。但它给的是一份推测,不是一份证词——执行计划里的行数是采样估算出来的type 这一列的优先级也不代表绝对的好坏。这个系列的第二篇我就把 EXPLAIN 从字段含义一直拆到真实慢查询的排查链路顺带聊聊不同版本之间那些会让结论翻车的差异。写过 SQL 但没系统学过执行计划的后端开发、刚接手数据库优化工作的同学、以及知道要看 explain 但看完还是不知道该改哪里的人都能对上号。1. EXPLAIN 给出的是优化器的推测而非数据库的证词1.1 执行计划的来源优化器的两轮决策在读懂任何一个字段之前得先知道这张表是谁生成的。MySQL 的优化器分两轮干活逻辑优化负责把 SQL 改写成等价但更容易执行的形式比如子查询展开、外连接转内连接、常量传播、等值传递、把永远为假的条件直接短路掉物理优化则是在改写后的骨架上为每张表选择访问路径——走哪个索引、还是干脆全表扫、多表关联时谁当驱动表、用嵌套循环还是哈希连接。物理优化的核心是代价模型。它估算每种方案要读多少页、要比较多少次挑代价最低的那个。问题在于这个代价基于统计信息而 InnoDB 的索引统计默认是采样出来的——innodb_stats_persistent_sample_pages默认只采 20 个页。拿几百万行的表来说二十个页大概能覆盖几千行然后按比例外推。这就好比做人口普查只抽了二十个小区方向大致对具体数字别当真。所以 EXPLAIN 输出的 rows 从来不是要扫这么多行而是在现有统计信息下优化器认为大概要扫这么多行。统计信息一旦过期或者采样偏差大这个数字就能差出两三个数量级。理解这一点后面很多计划看着不对的困惑会自动消失一半。1.2 看到 type 是 ALL 就急着加索引是最常见的误判我见过太多这样的操作慢查询日志里捞出一条 SQL前面加个 explain看到 type 那列写着 ALLrows 六位数于是断定缺索引顺手在 where 后面的字段建一个。第二天另一个接口变慢了因为新索引打乱了另一条查询的索引选择。索引不是免费的。每建一个二级索引对应的写操作就要多维护一棵 B 树插入时可能触发页分裂更新时要同步改索引项缓冲池里也要多缓存一份数据页。一个写多读少的表上堆五六个索引写入吞吐掉三成是常有的事。更麻烦的是索引越多优化器的选择空间越大选错的概率也越大。还有一种情况是选择性太低。比如订单状态字段取值只有待支付、已支付、已取消那么几种某一状态占了全表七成。这种列建索引查这个状态时优化器大概率还是会选全表扫描因为走索引再回表的随机 IO 成本远高于顺序扫。你在 EXPLAIN 里会看到 possible_keys 列出了这个索引但 key 是 NULL——这就是优化器算完账之后主动放弃的证据。1.3 执行计划回答不了的三个问题把 EXPLAIN 当成万能诊断工具是另一类常见偏差。它有三件事明确回答不了。第一件是真实的 IO 开销。传统格式的 EXPLAIN 不执行 SQL只是让优化器生成计划所以它不知道这次查询到底读了几个磁盘页、命中了几次缓冲池。要拿到实际耗时和实际行数得用 MySQL 8.0.18 之后提供的EXPLAIN ANALYZE它会真的把语句跑一遍输出每个算子的实际执行时间和实际返回行数。第二件是锁与并发。执行计划里完全没有锁等待、间隙锁范围、意向锁冲突这些信息。一条计划完美的 UPDATE 照样可能因为等锁卡上十几秒。第三件是返回给客户端的数据量。EXPLAIN 只看服务端怎么取数不看网络传输。一个select *查出三万行宽表计划再漂亮网络和客户端反序列化也能把它拖成一秒以上。这三件事得靠慢查询日志、performance_schema、以及业务侧的埋点来补。2. 十二列逐列拆解每一列都在回答一个具体问题执行计划的列看起来多其实可以按它回答什么问题分组谁先谁后id、select_type、怎么访问table、partitions、type、用没用索引possible_keys、key、key_len、ref、代价多大rows、filtered、以及有没有额外动作Extra。分组之后读计划就有了固定路径。2.1 id 与 select_type执行顺序与被折叠的查询形态id 这一列的数字大小代表执行优先级数字越大越先执行id 相同的时候从上往下读。所以一个带子查询的 SQL你会看到子查询的 id 是 2外层是 1顺序是先跑子查询。select_type 描述的是这个查询块在整个语句里扮演的角色常见取值如下取值含义需要关注什么SIMPLE不含子查询和 UNION 的简单查询最常见重点看 type 和 ExtraPRIMARY最外层查询与其他块配合看顺序SUBQUERY子查询中不依赖外层的部分只执行一次代价可接受DEPENDENT SUBQUERY子查询依赖外层逐行执行高危通常要改写DERIVED派生表FROM 里的子查询出现说明被物化成临时表UNIONUNION 中的第二个及之后的查询注意是否产生临时表UNION RESULTUNION 的合并结果一般伴随 Using temporaryMATERIALIZED被物化的子查询8.0 里常见于 IN 子查询其中 DEPENDENT SUBQUERY 是最该警惕的一个。它意味着外层每返回一行内层子查询就要重新跑一次。外层一万行内层就是一万次。经典改写方式是把IN (子查询)换成JOIN或者把EXISTS的相关条件挪到 ON 里让优化器有机会做半连接优化。2.2 type 的等级谱系与 index 这个伪安全区type 是访问类型大致按效率从高到低排system、const、eq_ref、ref、fulltext、ref_or_null、index_merge、unique_subquery、index_subquery、range、index、ALL。很多人把它当成绩单越靠前越好但实际判断要结合数据量和过滤条件。system 和 const 是通过主键或唯一索引等值查一行优化器在生成计划阶段就把结果算出来了代价几乎为零。eq_ref 出现在多表关联时被驱动表用主键或唯一索引匹配一进一出很干净。ref 是普通索引等值匹配可能命中多行这是绝大多数业务查询的理想状态。range 是索引范围扫描between、、in这类条件会走到它。index 这个类型最容易被误解。它叫全索引扫描意思是把整棵索引树从叶子节点扫一遍。听起来像走了索引但扫描量级和全表扫描是一个数量级的只是扫的是索引文件而不是数据文件。如果查询字段全部包含在索引里也就是覆盖索引那么扫索引比扫表划算因为索引页更紧凑。但如果扫完索引还得回表那还不如直接全表扫。所以看到 type 是 index先去看 Extra 里有没有Using index没有的话基本可以按 ALL 处理。ALL 就是全表扫描。它本身不是错误小表几百行以内全扫比走索引快优化器很清楚这一点。真正的问题是大表上的 ALL而且 where 条件有可用的高选择性字段。2.3 key_len 里藏着的索引使用真相possible_keys 是理论上能用上的索引key 是实际用上的索引这两列的差异本身就说明了问题如果 possible_keys 有值而 key 是 NULL说明优化器算完账觉得走索引不划算如果两个都是 NULL说明压根没有可用的索引。key_len 是这三个字段里信息量最大的因为它能告诉你索引被用了几列。计算规则是每个索引列的存储长度累加int 占 4 字节bigint 占 8 字节datetime 从 5.6 起占 5 字节timestamp 占 4 字节varchar(N) 在 utf8mb4 下占 N×4 字节再加 2 字节存变长长度允许 NULL 的列额外加 1 字节定长 char(N) 在 utf8mb4 下占 N×4 字节同样允许 NULL 加 1举个例子一个varchar(20) NOT NULL的列在 utf8mb4 库下key_len 显示 8220×42如果建了(user_id, status)的组合索引user_id 是 bigintstatus 是 tinyint那么完整用上两列时 key_len 是 981。如果只显示 8说明 status 那一列没参与索引匹配联合索引只用在了一半。我在排查里经常靠这个数字确认索引到底用到了第几列。因为联合索引最左前缀的规则一旦中间某列用了范围条件、、between后面的列就没法继续用于索引查找了只能退化成回表后的过滤条件。key_len 会诚实地反映这一点。2.4 rows 乘 filtered 才是有效估算rows 是优化器估算的扫描行数filtered 是这一步骤过滤后剩余的百分比。单独看任何一个都没什么意义两个乘起来才是参与下一步的行数。假设驱动表 rows 是 1000filtered 是 10%那么实际参与关联的只有 100 行。如果被驱动表的 rows 是 5000优化器就会认为关联代价是 100×5000 而不是 1000×5000这个差异会直接影响它选谁当驱动表。所以当你觉得驱动表选错了的时候多半是 filtered 的估算出了问题而 filtered 的估算依赖于统计信息的准确性尤其是直方图。MySQL 8.0 引入了列直方图可以用ANALYZE TABLE 表名 UPDATE HISTOGRAM ON 列名 WITH N BUCKETS手动创建。对于数据分布严重倾斜的列比如一个城市字段一线城市占八成、其余几百个城市分剩下两成直方图能显著改善 filtered 的估算精度进而让关联顺序更合理。这个手段在实际项目里用的人不多但对多表关联的性能影响相当直接。2.5 Extra 是整张表里信息密度最高的一列如果只能看一列我会看 Extra。它告诉你在访问数据之外MySQL 还做了哪些额外动作。常见值及含义Extra 内容含义判断Using index覆盖索引无需回表好Using where取到数据后还要在 Server 层过滤视过滤比例而定Using index condition索引下推条件在存储引擎层过滤通常好于 Using whereUsing filesort需要额外排序没走索引顺序数据量大时要查Using temporary需要建临时表常见于 GROUP BY尽量消除Using join buffer (hash join)8.0.18 起的哈希连接大表关联时可接受Using MRR多范围读把随机 IO 合并成顺序读好Using index for group-by松散索引扫描完成分组很好Select tables optimized away优化器直接算出结果最好Using filesort这个名字有历史包袱它不代表一定用磁盘排序。如果待排序的数据量小于sort_buffer_size排序是在内存里做的快排速度很快。真正危险的是排序数据量大到要落磁盘临时文件那就会看到Sort_merge_passes这个状态变量往上涨。判断办法不是看 Extra 有没有 filesort而是看 rows 大不大、SHOW SESSION STATUS LIKE Sort%里Sort_merge_passes有没有增加。Using temporary在 GROUP BY 场景尤其常见。如果分组字段上没有合适的索引MySQL 会先建一张临时表把数据塞进去再分组数据量大时临时表会落到磁盘。消除办法是给分组字段建索引让优化器能用索引有序性直接完成分组这时 Extra 会显示Using index for group-by。3. 一条订单列表查询的完整排查链路光看字段定义容易记不住我把一次真实排查过程完整走一遍。场景是虚构的但每一步的判断逻辑是通用的。业务侧反馈订单列表页打开要三秒多。拿到 SQLSELECT id, user_id, status, amount, created_at FROM t_order WHERE user_id 10086 AND status IN (1, 2) ORDER BY created_at DESC LIMIT 20;表大概八百万行当时只有idx_user(user_id)一个二级索引。执行 explain 得到----------------------------------------------------------------------------------------------------------------------- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | ----------------------------------------------------------------------------------------------------------------------- | 1 | SIMPLE | t_order | ref | idx_user | idx_user | 8 | const | 12640 | 18.50 | Using where; Using filesort | -----------------------------------------------------------------------------------------------------------------------3.1 filesort 是怎么冒出来的先读这张表。type 是 ref走了 idx_userkey_len 是 8说明用上了完整的 bigint 主键部分索引本身没浪费。rows 一万二filtered 18.5%也就是预计最终命中两千三百多行。问题出在 Extra 上Using where; Using filesort。Using where说明 status 的过滤是在存储引擎返回数据之后做的因为 idx_user 里根本没有 status 这一列。Using filesort说明排序没借助索引顺序得先把这两千多行捞出来再按 created_at 排一遍。虽然只取 20 条但排序是全局的MySQL 必须先拿到全部候选行才能确定前 20 名。这里要澄清一个常见的误解很多人以为LIMIT 20会让 MySQL 只扫 20 行就停。只有在ORDER BY的列上有序、并且索引顺序与排序顺序一致时才是这样。一旦排序需要额外处理LIMIT就只能省掉最后的回传省不掉扫描和排序。3.2 联合索引的列序把排序条件一起装进去第一反应是建(user_id, status, created_at)的联合索引。看起来 where 用前两列、order by 用第三列天衣无缝。建完再 explainExtra 变成Using where; Using filesortfilesort 还在。原因在 status 上用了IN。IN (1, 2)等价于 status1 或 status2 两个等值条件。在这两个分支内部created_at 确实是有序的但跨分支不保证有序——status1 的订单 created_at 可能是今天status2 的可能是昨天。MySQL 要做全局的 ORDER BY就必须把两个分支的结果归并起来再排一次。这是个很典型的陷阱。经验上联合索引的列序规则是等值在前、范围在后IN虽然逻辑上是多个等值但在排序语义上它破坏了整体有序性。所以这条 SQL 想彻底消掉 filesort 只有两条路第一条是把 IN 拆成 UNION ALL每个分支单独排序再合并(SELECT ... FROM t_order WHERE user_id 10086 AND status 1 ORDER BY created_at DESC LIMIT 20) UNION ALL (SELECT ... FROM t_order WHERE user_id 10086 AND status 2 ORDER BY created_at DESC LIMIT 20) ORDER BY created_at DESC LIMIT 20;这样每个子查询都能用上(user_id, status, created_at)的完整有序性各自只扫 20 行外层对最多 40 行做一次排序代价可以忽略。代价是 SQL 变长业务代码里拼起来麻烦而且分页逻辑要重做。第二条路是放弃按 status 建索引改用(user_id, created_at)ALTER TABLE t_order ADD INDEX idx_user_ct (user_id, created_at);此时 where 只用 user_idorder by 走 created_at 的索引顺序status 作为回表后的过滤条件。扫描方式变成沿索引从最新的订单往前扫每扫一行回表取 status命中就输出凑够 20 条就停。这条路的成败取决于 status 的命中密度。如果这个用户的订单里绝大多数都是 1 或 2 状态那么扫二三十行就能凑够 20 条极快。如果这个用户的订单里 90% 是已完成的 3 状态那可能要扫几百行才能凑够回表成本就上来了。实际项目中两种状态都有得看具体业务分布。我在实际项目里的选择是默认走第二条路因为大部分用户的订单状态分布集中在少数几个活跃状态上命中率高同时在代码层面对超长历史的用户做额外处理比如限定最近三个月的时间范围避免扫描过深。3.3 用 EXPLAIN ANALYZE 验证真实代价改完之后不能只看计划得看实际执行。MySQL 8.0.18 之后可以用EXPLAIN ANALYZE SELECT id, user_id, status, amount, created_at FROM t_order WHERE user_id 10086 ORDER BY created_at DESC LIMIT 20;输出是一棵树每个节点带actual time... rows... loops...。这三个数字里actual rows和估算 rows 的差距是最有价值的诊断信号。如果估算一万二、实际只命中三百说明统计信息严重偏离优化器基于错误信息做的所有决策都值得怀疑如果估算三百、实际两万说明数据分布有强相关性单列统计信息表达不出来这时候该考虑直方图或者改写查询结构。loops这个数字也常被忽略。它是这个算子被执行的次数在嵌套循环关联里被驱动表算子的 loops 等于驱动表的行数。看到某个算子 actual time 只有 0.05 毫秒但 loops 是十万次总耗时就是五秒这时候优化重点在于减少外层行数而不是优化内层本身。这种视角在传统 EXPLAIN 里是完全看不到的。4. 版本差异与几个看起来对其实错的用法4.1 5.7 时代的经验拿到 8.0 上会翻车网上流传的大量执行计划分析文章写于 MySQL 5.7 时代里面的一些结论在 8.0 上已经反转。最典型的是子查询。5.7 里IN (子查询)经常被物化成临时表EXPLAIN 显示 DERIVED很多人据此得出的结论是IN 子查询一定慢必须改 JOIN。8.0 引入了半连接优化和子查询展开很多IN子查询会被直接改写成半连接或者内连接执行计划和 JOIN 写法几乎一致性能差距消失。盲目改写反而可能引入重复行的问题。再一个是哈希连接。8.0.18 之前 MySQL 只有嵌套循环一种关联算法两张没有索引关联的大表会退化成笛卡尔积级别的扫描。8.0.18 之后等值关联条件在没有可用索引时会自动走哈希连接Extra 显示Using join buffer (hash join)。这意味着一些在 5.7 上必须加索引才能跑的关联查询在 8.0 上不加索引也能跑得动。当然不是说可以不加索引而是说不该再把Using join buffer当成绝对的坏信号。还有索引跳跃扫描Index Skip Scan8.0.13 引入。当联合索引的第一列选择性很低、而查询条件里没带第一列时优化器可以跳过第一列直接按第二列扫描。这是 5.7 完全做不到的事情。如果你在 8.0 上看到 key 用上了组合索引但 type 是 range而 where 里并没有第一列别急着怀疑自己看错了可能就是跳跃扫描在起作用。4.2 隐式类型转换会让索引悄悄失效这类问题在 EXPLAIN 里的表现很有辨识度possible_keys 里明明列出了索引key 却是 NULL或者 key 有值但 key_len 明显偏小。最常见的是字符串列传数字。比如user_id是 varchar 类型代码里传了个 int 参数SQL 变成WHERE user_id 10086。MySQL 会把列值转成数字再比较每行都要做一次转换索引的自然顺序被打乱直接退化成全表扫描。字符集不一致是另一大类。两张表一张 utf8、一张 utf8mb4关联字段上做比较时MySQL 要统一字符集通常是把 utf8 转成 utf8mb4这会导致被转换那侧的索引失效。改表字符集代价大但至少关联字段的字符集必须一致这是个硬要求。还有一种隐蔽情况是索引列上做了运算或者函数。WHERE DATE(created_at) 2024-01-01这种写法created_at 上的索引完全用不上因为 MySQL 算不出DATE(created_at)的结果落在索引的哪一段。改成created_at 2024-01-01 AND created_at 2024-01-02就能用上 range 扫描。这类改写几乎不改变语义收益却是数量级的。4.3 优化器提示是把双刃剑USE INDEX、FORCE INDEX、IGNORE INDEX这三个语法很多人当救命稻草用。它们的实际语义经常被搞混USE INDEX是从这几个索引里挑FORCE INDEX是必须用这个索引IGNORE INDEX是别用这几个。问题在于提示绑定的是索引名字一旦后续有人改了索引结构或者加了新索引提示可能失效甚至把优化器带偏。更要命的是数据分布会随时间变化今天强制的索引选择三个月后可能就是最差的选择。MySQL 8.0 提供了另一种更细粒度的方式用注释形式的优化器提示SELECT /* NO_INDEX(t_order idx_user) */ ... FROM t_order WHERE ...;它作用于单条语句不影响其他查询颗粒度更细。我的做法是只在确认优化器选错、并且能找到明确原因通常是统计信息不准的情况下才用提示同时一定配合ANALYZE TABLE更新统计信息把提示当成临时兜底而不是长期方案。5. 把执行计划纳入日常工程习惯5.1 上线前自查的几个硬动作每条新写的、或者改动过的 SQL在上线前值得过一遍清单在接近线上数据量的环境里跑一次 explain不要在几百行的测试库上看计划那种规模下优化器几乎永远选全表扫描确认 type 不是大表上的 ALL 或 index确认 Extra 里没有大 rows 下的 Using filesort 和 Using temporary检查所有 where 条件列的类型和字符集与索引列保持一致检查索引列上有没有函数、运算、隐式转换检查LIMIT深翻页LIMIT 100000, 20这种写法本质上要扫十万行再丢弃最后一条在实际项目里特别常见。分页参数越翻越大接口耗时线性上涨。改法是记录上一页最后一条记录的主键用WHERE id 上一页最大 id ORDER BY id LIMIT 20的方式翻页。这对有自增主键的表非常友好代价是不能跳页。5.2 索引数量的隐性成本要算清楚一个表上到底该建几个索引没有一个通用数字。我的经验是围绕核心查询建模把最频繁、性能最敏感的十条查询列出来看哪些能共用索引剩下的用覆盖索引或者调整 SQL 结构来解。联合索引的列序设计有一个朴素的判断方法把等值条件列放前面把范围条件和排序列放后面然后在可能的几种排列里分别 explain 一遍选代价最低的那个。这听起来笨但比拍脑袋靠谱得多。尤其是列数超过三个的时候不同排列之间的差异可能达到一个数量级。还有一点容易被忽略索引也会影响缓冲池。每个索引占用的页都要从innodb_buffer_pool_size里分一杯羹索引太多会导致热点数据页被挤出去缓存命中率下降最终所有查询都变慢。所以优化索引不是加,更多时候是减和合并。5.3 统计信息过期是最容易被低估的变量数据量从一百万涨到一千万如果统计信息没及时更新优化器还按一百万的数据分布做决策选出来的计划可能完全失效。InnoDB 默认开启innodb_stats_persistent统计信息会持久化到磁盘同时innodb_stats_auto_recalc默认开启当表数据变化超过 10% 时会自动重算。这个阈值对于大批量导入的场景通常太迟了。大批量写入完成之后手动跑一次ANALYZE TABLE 表名是必要的。但要注意ANALYZE TABLE在 MySQL 8.0 里默认会做一次全表扫描来更新统计信息对大表来说开销不小一般放在业务低峰期做。对于数据分布倾斜的列前面提到的直方图更值得用ANALYZE TABLE t_order UPDATE HISTOGRAM ON status, user_id WITH 128 BUCKETS;128 个桶意味着把这列的值域切成 128 段来记录分布优化器估算 filtered 时会用上这些信息。建完之后可以通过information_schema.COLUMN_STATISTICS查看桶的分布确认是否覆盖到了热点值。我在几个订单类项目里用下来最直接的收益是关联顺序变得稳定不再出现同一条 SQL 今天走 A 表驱动、明天走 B 表驱动的抖动。最后再分享一个小技巧排查那种偶尔慢、复现不了的查询时把执行计划连续打十次看 key 和 rows 有没有变化。如果有基本可以确定是统计信息或者数据分布导致的选择抖动而不是偶发的资源争抢。这个判断能省掉大量无谓的索引调整。 
长河君 · 2026/10/31 3:35:45 · 阅读 1.2k MySQL EXPLAIN执行计划详解：从字段拆解到慢查询索引优化
有人把 EXPLAIN 打成 explan我第一次在同事的周报里看到这个拼写还愣了几秒以为是某个内部工具名。后来发现拼错的人和用错的人一样多——大多数人知道慢查询要 explain 一下但点开那张十几列的表之后真正能读出问题的人没几个。MySQL 的 EXPLAIN 是排查查询性能最基础的一把刀。它把一个 SQL 在优化器眼里被拆成了几步、每步用什么方式访问数据、预计扫多少行、有没有走索引、要不要额外排序或者建临时表全部摊开给你看。但它给的是一份推测,不是一份证词——执行计划里的行数是采样估算出来的type 这一列的优先级也不代表绝对的好坏。这个系列的第二篇我就把 EXPLAIN 从字段含义一直拆到真实慢查询的排查链路顺带聊聊不同版本之间那些会让结论翻车的差异。写过 SQL 但没系统学过执行计划的后端开发、刚接手数据库优化工作的同学、以及知道要看 explain 但看完还是不知道该改哪里的人都能对上号。1. EXPLAIN 给出的是优化器的推测而非数据库的证词1.1 执行计划的来源优化器的两轮决策在读懂任何一个字段之前得先知道这张表是谁生成的。MySQL 的优化器分两轮干活逻辑优化负责把 SQL 改写成等价但更容易执行的形式比如子查询展开、外连接转内连接、常量传播、等值传递、把永远为假的条件直接短路掉物理优化则是在改写后的骨架上为每张表选择访问路径——走哪个索引、还是干脆全表扫、多表关联时谁当驱动表、用嵌套循环还是哈希连接。物理优化的核心是代价模型。它估算每种方案要读多少页、要比较多少次挑代价最低的那个。问题在于这个代价基于统计信息而 InnoDB 的索引统计默认是采样出来的——innodb_stats_persistent_sample_pages默认只采 20 个页。拿几百万行的表来说二十个页大概能覆盖几千行然后按比例外推。这就好比做人口普查只抽了二十个小区方向大致对具体数字别当真。所以 EXPLAIN 输出的 rows 从来不是要扫这么多行而是在现有统计信息下优化器认为大概要扫这么多行。统计信息一旦过期或者采样偏差大这个数字就能差出两三个数量级。理解这一点后面很多计划看着不对的困惑会自动消失一半。1.2 看到 type 是 ALL 就急着加索引是最常见的误判我见过太多这样的操作慢查询日志里捞出一条 SQL前面加个 explain看到 type 那列写着 ALLrows 六位数于是断定缺索引顺手在 where 后面的字段建一个。第二天另一个接口变慢了因为新索引打乱了另一条查询的索引选择。索引不是免费的。每建一个二级索引对应的写操作就要多维护一棵 B 树插入时可能触发页分裂更新时要同步改索引项缓冲池里也要多缓存一份数据页。一个写多读少的表上堆五六个索引写入吞吐掉三成是常有的事。更麻烦的是索引越多优化器的选择空间越大选错的概率也越大。还有一种情况是选择性太低。比如订单状态字段取值只有待支付、已支付、已取消那么几种某一状态占了全表七成。这种列建索引查这个状态时优化器大概率还是会选全表扫描因为走索引再回表的随机 IO 成本远高于顺序扫。你在 EXPLAIN 里会看到 possible_keys 列出了这个索引但 key 是 NULL——这就是优化器算完账之后主动放弃的证据。1.3 执行计划回答不了的三个问题把 EXPLAIN 当成万能诊断工具是另一类常见偏差。它有三件事明确回答不了。第一件是真实的 IO 开销。传统格式的 EXPLAIN 不执行 SQL只是让优化器生成计划所以它不知道这次查询到底读了几个磁盘页、命中了几次缓冲池。要拿到实际耗时和实际行数得用 MySQL 8.0.18 之后提供的EXPLAIN ANALYZE它会真的把语句跑一遍输出每个算子的实际执行时间和实际返回行数。第二件是锁与并发。执行计划里完全没有锁等待、间隙锁范围、意向锁冲突这些信息。一条计划完美的 UPDATE 照样可能因为等锁卡上十几秒。第三件是返回给客户端的数据量。EXPLAIN 只看服务端怎么取数不看网络传输。一个select *查出三万行宽表计划再漂亮网络和客户端反序列化也能把它拖成一秒以上。这三件事得靠慢查询日志、performance_schema、以及业务侧的埋点来补。2. 十二列逐列拆解每一列都在回答一个具体问题执行计划的列看起来多其实可以按它回答什么问题分组谁先谁后id、select_type、怎么访问table、partitions、type、用没用索引possible_keys、key、key_len、ref、代价多大rows、filtered、以及有没有额外动作Extra。分组之后读计划就有了固定路径。2.1 id 与 select_type执行顺序与被折叠的查询形态id 这一列的数字大小代表执行优先级数字越大越先执行id 相同的时候从上往下读。所以一个带子查询的 SQL你会看到子查询的 id 是 2外层是 1顺序是先跑子查询。select_type 描述的是这个查询块在整个语句里扮演的角色常见取值如下取值含义需要关注什么SIMPLE不含子查询和 UNION 的简单查询最常见重点看 type 和 ExtraPRIMARY最外层查询与其他块配合看顺序SUBQUERY子查询中不依赖外层的部分只执行一次代价可接受DEPENDENT SUBQUERY子查询依赖外层逐行执行高危通常要改写DERIVED派生表FROM 里的子查询出现说明被物化成临时表UNIONUNION 中的第二个及之后的查询注意是否产生临时表UNION RESULTUNION 的合并结果一般伴随 Using temporaryMATERIALIZED被物化的子查询8.0 里常见于 IN 子查询其中 DEPENDENT SUBQUERY 是最该警惕的一个。它意味着外层每返回一行内层子查询就要重新跑一次。外层一万行内层就是一万次。经典改写方式是把IN (子查询)换成JOIN或者把EXISTS的相关条件挪到 ON 里让优化器有机会做半连接优化。2.2 type 的等级谱系与 index 这个伪安全区type 是访问类型大致按效率从高到低排system、const、eq_ref、ref、fulltext、ref_or_null、index_merge、unique_subquery、index_subquery、range、index、ALL。很多人把它当成绩单越靠前越好但实际判断要结合数据量和过滤条件。system 和 const 是通过主键或唯一索引等值查一行优化器在生成计划阶段就把结果算出来了代价几乎为零。eq_ref 出现在多表关联时被驱动表用主键或唯一索引匹配一进一出很干净。ref 是普通索引等值匹配可能命中多行这是绝大多数业务查询的理想状态。range 是索引范围扫描between、、in这类条件会走到它。index 这个类型最容易被误解。它叫全索引扫描意思是把整棵索引树从叶子节点扫一遍。听起来像走了索引但扫描量级和全表扫描是一个数量级的只是扫的是索引文件而不是数据文件。如果查询字段全部包含在索引里也就是覆盖索引那么扫索引比扫表划算因为索引页更紧凑。但如果扫完索引还得回表那还不如直接全表扫。所以看到 type 是 index先去看 Extra 里有没有Using index没有的话基本可以按 ALL 处理。ALL 就是全表扫描。它本身不是错误小表几百行以内全扫比走索引快优化器很清楚这一点。真正的问题是大表上的 ALL而且 where 条件有可用的高选择性字段。2.3 key_len 里藏着的索引使用真相possible_keys 是理论上能用上的索引key 是实际用上的索引这两列的差异本身就说明了问题如果 possible_keys 有值而 key 是 NULL说明优化器算完账觉得走索引不划算如果两个都是 NULL说明压根没有可用的索引。key_len 是这三个字段里信息量最大的因为它能告诉你索引被用了几列。计算规则是每个索引列的存储长度累加int 占 4 字节bigint 占 8 字节datetime 从 5.6 起占 5 字节timestamp 占 4 字节varchar(N) 在 utf8mb4 下占 N×4 字节再加 2 字节存变长长度允许 NULL 的列额外加 1 字节定长 char(N) 在 utf8mb4 下占 N×4 字节同样允许 NULL 加 1举个例子一个varchar(20) NOT NULL的列在 utf8mb4 库下key_len 显示 8220×42如果建了(user_id, status)的组合索引user_id 是 bigintstatus 是 tinyint那么完整用上两列时 key_len 是 981。如果只显示 8说明 status 那一列没参与索引匹配联合索引只用在了一半。我在排查里经常靠这个数字确认索引到底用到了第几列。因为联合索引最左前缀的规则一旦中间某列用了范围条件、、between后面的列就没法继续用于索引查找了只能退化成回表后的过滤条件。key_len 会诚实地反映这一点。2.4 rows 乘 filtered 才是有效估算rows 是优化器估算的扫描行数filtered 是这一步骤过滤后剩余的百分比。单独看任何一个都没什么意义两个乘起来才是参与下一步的行数。假设驱动表 rows 是 1000filtered 是 10%那么实际参与关联的只有 100 行。如果被驱动表的 rows 是 5000优化器就会认为关联代价是 100×5000 而不是 1000×5000这个差异会直接影响它选谁当驱动表。所以当你觉得驱动表选错了的时候多半是 filtered 的估算出了问题而 filtered 的估算依赖于统计信息的准确性尤其是直方图。MySQL 8.0 引入了列直方图可以用ANALYZE TABLE 表名 UPDATE HISTOGRAM ON 列名 WITH N BUCKETS手动创建。对于数据分布严重倾斜的列比如一个城市字段一线城市占八成、其余几百个城市分剩下两成直方图能显著改善 filtered 的估算精度进而让关联顺序更合理。这个手段在实际项目里用的人不多但对多表关联的性能影响相当直接。2.5 Extra 是整张表里信息密度最高的一列如果只能看一列我会看 Extra。它告诉你在访问数据之外MySQL 还做了哪些额外动作。常见值及含义Extra 内容含义判断Using index覆盖索引无需回表好Using where取到数据后还要在 Server 层过滤视过滤比例而定Using index condition索引下推条件在存储引擎层过滤通常好于 Using whereUsing filesort需要额外排序没走索引顺序数据量大时要查Using temporary需要建临时表常见于 GROUP BY尽量消除Using join buffer (hash join)8.0.18 起的哈希连接大表关联时可接受Using MRR多范围读把随机 IO 合并成顺序读好Using index for group-by松散索引扫描完成分组很好Select tables optimized away优化器直接算出结果最好Using filesort这个名字有历史包袱它不代表一定用磁盘排序。如果待排序的数据量小于sort_buffer_size排序是在内存里做的快排速度很快。真正危险的是排序数据量大到要落磁盘临时文件那就会看到Sort_merge_passes这个状态变量往上涨。判断办法不是看 Extra 有没有 filesort而是看 rows 大不大、SHOW SESSION STATUS LIKE Sort%里Sort_merge_passes有没有增加。Using temporary在 GROUP BY 场景尤其常见。如果分组字段上没有合适的索引MySQL 会先建一张临时表把数据塞进去再分组数据量大时临时表会落到磁盘。消除办法是给分组字段建索引让优化器能用索引有序性直接完成分组这时 Extra 会显示Using index for group-by。3. 一条订单列表查询的完整排查链路光看字段定义容易记不住我把一次真实排查过程完整走一遍。场景是虚构的但每一步的判断逻辑是通用的。业务侧反馈订单列表页打开要三秒多。拿到 SQLSELECT id, user_id, status, amount, created_at FROM t_order WHERE user_id 10086 AND status IN (1, 2) ORDER BY created_at DESC LIMIT 20;表大概八百万行当时只有idx_user(user_id)一个二级索引。执行 explain 得到----------------------------------------------------------------------------------------------------------------------- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | ----------------------------------------------------------------------------------------------------------------------- | 1 | SIMPLE | t_order | ref | idx_user | idx_user | 8 | const | 12640 | 18.50 | Using where; Using filesort | -----------------------------------------------------------------------------------------------------------------------3.1 filesort 是怎么冒出来的先读这张表。type 是 ref走了 idx_userkey_len 是 8说明用上了完整的 bigint 主键部分索引本身没浪费。rows 一万二filtered 18.5%也就是预计最终命中两千三百多行。问题出在 Extra 上Using where; Using filesort。Using where说明 status 的过滤是在存储引擎返回数据之后做的因为 idx_user 里根本没有 status 这一列。Using filesort说明排序没借助索引顺序得先把这两千多行捞出来再按 created_at 排一遍。虽然只取 20 条但排序是全局的MySQL 必须先拿到全部候选行才能确定前 20 名。这里要澄清一个常见的误解很多人以为LIMIT 20会让 MySQL 只扫 20 行就停。只有在ORDER BY的列上有序、并且索引顺序与排序顺序一致时才是这样。一旦排序需要额外处理LIMIT就只能省掉最后的回传省不掉扫描和排序。3.2 联合索引的列序把排序条件一起装进去第一反应是建(user_id, status, created_at)的联合索引。看起来 where 用前两列、order by 用第三列天衣无缝。建完再 explainExtra 变成Using where; Using filesortfilesort 还在。原因在 status 上用了IN。IN (1, 2)等价于 status1 或 status2 两个等值条件。在这两个分支内部created_at 确实是有序的但跨分支不保证有序——status1 的订单 created_at 可能是今天status2 的可能是昨天。MySQL 要做全局的 ORDER BY就必须把两个分支的结果归并起来再排一次。这是个很典型的陷阱。经验上联合索引的列序规则是等值在前、范围在后IN虽然逻辑上是多个等值但在排序语义上它破坏了整体有序性。所以这条 SQL 想彻底消掉 filesort 只有两条路第一条是把 IN 拆成 UNION ALL每个分支单独排序再合并(SELECT ... FROM t_order WHERE user_id 10086 AND status 1 ORDER BY created_at DESC LIMIT 20) UNION ALL (SELECT ... FROM t_order WHERE user_id 10086 AND status 2 ORDER BY created_at DESC LIMIT 20) ORDER BY created_at DESC LIMIT 20;这样每个子查询都能用上(user_id, status, created_at)的完整有序性各自只扫 20 行外层对最多 40 行做一次排序代价可以忽略。代价是 SQL 变长业务代码里拼起来麻烦而且分页逻辑要重做。第二条路是放弃按 status 建索引改用(user_id, created_at)ALTER TABLE t_order ADD INDEX idx_user_ct (user_id, created_at);此时 where 只用 user_idorder by 走 created_at 的索引顺序status 作为回表后的过滤条件。扫描方式变成沿索引从最新的订单往前扫每扫一行回表取 status命中就输出凑够 20 条就停。这条路的成败取决于 status 的命中密度。如果这个用户的订单里绝大多数都是 1 或 2 状态那么扫二三十行就能凑够 20 条极快。如果这个用户的订单里 90% 是已完成的 3 状态那可能要扫几百行才能凑够回表成本就上来了。实际项目中两种状态都有得看具体业务分布。我在实际项目里的选择是默认走第二条路因为大部分用户的订单状态分布集中在少数几个活跃状态上命中率高同时在代码层面对超长历史的用户做额外处理比如限定最近三个月的时间范围避免扫描过深。3.3 用 EXPLAIN ANALYZE 验证真实代价改完之后不能只看计划得看实际执行。MySQL 8.0.18 之后可以用EXPLAIN ANALYZE SELECT id, user_id, status, amount, created_at FROM t_order WHERE user_id 10086 ORDER BY created_at DESC LIMIT 20;输出是一棵树每个节点带actual time... rows... loops...。这三个数字里actual rows和估算 rows 的差距是最有价值的诊断信号。如果估算一万二、实际只命中三百说明统计信息严重偏离优化器基于错误信息做的所有决策都值得怀疑如果估算三百、实际两万说明数据分布有强相关性单列统计信息表达不出来这时候该考虑直方图或者改写查询结构。loops这个数字也常被忽略。它是这个算子被执行的次数在嵌套循环关联里被驱动表算子的 loops 等于驱动表的行数。看到某个算子 actual time 只有 0.05 毫秒但 loops 是十万次总耗时就是五秒这时候优化重点在于减少外层行数而不是优化内层本身。这种视角在传统 EXPLAIN 里是完全看不到的。4. 版本差异与几个看起来对其实错的用法4.1 5.7 时代的经验拿到 8.0 上会翻车网上流传的大量执行计划分析文章写于 MySQL 5.7 时代里面的一些结论在 8.0 上已经反转。最典型的是子查询。5.7 里IN (子查询)经常被物化成临时表EXPLAIN 显示 DERIVED很多人据此得出的结论是IN 子查询一定慢必须改 JOIN。8.0 引入了半连接优化和子查询展开很多IN子查询会被直接改写成半连接或者内连接执行计划和 JOIN 写法几乎一致性能差距消失。盲目改写反而可能引入重复行的问题。再一个是哈希连接。8.0.18 之前 MySQL 只有嵌套循环一种关联算法两张没有索引关联的大表会退化成笛卡尔积级别的扫描。8.0.18 之后等值关联条件在没有可用索引时会自动走哈希连接Extra 显示Using join buffer (hash join)。这意味着一些在 5.7 上必须加索引才能跑的关联查询在 8.0 上不加索引也能跑得动。当然不是说可以不加索引而是说不该再把Using join buffer当成绝对的坏信号。还有索引跳跃扫描Index Skip Scan8.0.13 引入。当联合索引的第一列选择性很低、而查询条件里没带第一列时优化器可以跳过第一列直接按第二列扫描。这是 5.7 完全做不到的事情。如果你在 8.0 上看到 key 用上了组合索引但 type 是 range而 where 里并没有第一列别急着怀疑自己看错了可能就是跳跃扫描在起作用。4.2 隐式类型转换会让索引悄悄失效这类问题在 EXPLAIN 里的表现很有辨识度possible_keys 里明明列出了索引key 却是 NULL或者 key 有值但 key_len 明显偏小。最常见的是字符串列传数字。比如user_id是 varchar 类型代码里传了个 int 参数SQL 变成WHERE user_id 10086。MySQL 会把列值转成数字再比较每行都要做一次转换索引的自然顺序被打乱直接退化成全表扫描。字符集不一致是另一大类。两张表一张 utf8、一张 utf8mb4关联字段上做比较时MySQL 要统一字符集通常是把 utf8 转成 utf8mb4这会导致被转换那侧的索引失效。改表字符集代价大但至少关联字段的字符集必须一致这是个硬要求。还有一种隐蔽情况是索引列上做了运算或者函数。WHERE DATE(created_at) 2024-01-01这种写法created_at 上的索引完全用不上因为 MySQL 算不出DATE(created_at)的结果落在索引的哪一段。改成created_at 2024-01-01 AND created_at 2024-01-02就能用上 range 扫描。这类改写几乎不改变语义收益却是数量级的。4.3 优化器提示是把双刃剑USE INDEX、FORCE INDEX、IGNORE INDEX这三个语法很多人当救命稻草用。它们的实际语义经常被搞混USE INDEX是从这几个索引里挑FORCE INDEX是必须用这个索引IGNORE INDEX是别用这几个。问题在于提示绑定的是索引名字一旦后续有人改了索引结构或者加了新索引提示可能失效甚至把优化器带偏。更要命的是数据分布会随时间变化今天强制的索引选择三个月后可能就是最差的选择。MySQL 8.0 提供了另一种更细粒度的方式用注释形式的优化器提示SELECT /* NO_INDEX(t_order idx_user) */ ... FROM t_order WHERE ...;它作用于单条语句不影响其他查询颗粒度更细。我的做法是只在确认优化器选错、并且能找到明确原因通常是统计信息不准的情况下才用提示同时一定配合ANALYZE TABLE更新统计信息把提示当成临时兜底而不是长期方案。5. 把执行计划纳入日常工程习惯5.1 上线前自查的几个硬动作每条新写的、或者改动过的 SQL在上线前值得过一遍清单在接近线上数据量的环境里跑一次 explain不要在几百行的测试库上看计划那种规模下优化器几乎永远选全表扫描确认 type 不是大表上的 ALL 或 index确认 Extra 里没有大 rows 下的 Using filesort 和 Using temporary检查所有 where 条件列的类型和字符集与索引列保持一致检查索引列上有没有函数、运算、隐式转换检查LIMIT深翻页LIMIT 100000, 20这种写法本质上要扫十万行再丢弃最后一条在实际项目里特别常见。分页参数越翻越大接口耗时线性上涨。改法是记录上一页最后一条记录的主键用WHERE id 上一页最大 id ORDER BY id LIMIT 20的方式翻页。这对有自增主键的表非常友好代价是不能跳页。5.2 索引数量的隐性成本要算清楚一个表上到底该建几个索引没有一个通用数字。我的经验是围绕核心查询建模把最频繁、性能最敏感的十条查询列出来看哪些能共用索引剩下的用覆盖索引或者调整 SQL 结构来解。联合索引的列序设计有一个朴素的判断方法把等值条件列放前面把范围条件和排序列放后面然后在可能的几种排列里分别 explain 一遍选代价最低的那个。这听起来笨但比拍脑袋靠谱得多。尤其是列数超过三个的时候不同排列之间的差异可能达到一个数量级。还有一点容易被忽略索引也会影响缓冲池。每个索引占用的页都要从innodb_buffer_pool_size里分一杯羹索引太多会导致热点数据页被挤出去缓存命中率下降最终所有查询都变慢。所以优化索引不是加,更多时候是减和合并。5.3 统计信息过期是最容易被低估的变量数据量从一百万涨到一千万如果统计信息没及时更新优化器还按一百万的数据分布做决策选出来的计划可能完全失效。InnoDB 默认开启innodb_stats_persistent统计信息会持久化到磁盘同时innodb_stats_auto_recalc默认开启当表数据变化超过 10% 时会自动重算。这个阈值对于大批量导入的场景通常太迟了。大批量写入完成之后手动跑一次ANALYZE TABLE 表名是必要的。但要注意ANALYZE TABLE在 MySQL 8.0 里默认会做一次全表扫描来更新统计信息对大表来说开销不小一般放在业务低峰期做。对于数据分布倾斜的列前面提到的直方图更值得用ANALYZE TABLE t_order UPDATE HISTOGRAM ON status, user_id WITH 128 BUCKETS;128 个桶意味着把这列的值域切成 128 段来记录分布优化器估算 filtered 时会用上这些信息。建完之后可以通过information_schema.COLUMN_STATISTICS查看桶的分布确认是否覆盖到了热点值。我在几个订单类项目里用下来最直接的收益是关联顺序变得稳定不再出现同一条 SQL 今天走 A 表驱动、明天走 B 表驱动的抖动。最后再分享一个小技巧排查那种偶尔慢、复现不了的查询时把执行计划连续打十次看 key 和 rows 有没有变化。如果有基本可以确定是统计信息或者数据分布导致的选择抖动而不是偶发的资源争抢。这个判断能省掉大量无谓的索引调整。