1. 为什么慢查询分析必须从 Explain 入手接手过线上数据库的人应该都有这种经历某个接口突然从 100 毫秒变成 3 秒翻代码找不到问题看慢查询日志发现一条 SQL 出现在列表顶部。这时候最有效的第一步不是去猜哪里慢而是执行一下EXPLAIN让数据库把它的执行计划告诉你。EXPLAIN是 MySQL 提供的一条命令作用是在不真正执行 SQL 的情况下展示 MySQL 优化器打算用哪种方式执行这条查询——比如访问哪张表、走哪个索引、大概扫描多少行、是否要做额外排序。它解决的是“这条 SQL 为什么慢”的问题而性能优化的本质就是先定位病灶再对症下药。任何没看过执行计划就盲目加索引、改 SQL 的行为都跟不看体检报告直接吃药一样危险。这个工具适合谁来用我认为三类人最需要后端开发排查接口慢请求、优化列表查询、处理报表统计类 SQLDBA 和专职性能优化人员做数据库巡检、容量规划、索引体系建设刚入门的同学作为理解索引原理和 MySQL 优化器行为最好的入门教材我用几个实际案例来拆解 Explain 里那些字段到底在说什么以及如何从一份执行计划里读出性能问题的关键线索。2. 执行计划字段逐个拆解不仅要看还要看懂2.1 type 列判断访问方式的五个台阶type列是执行计划里第一个要看的字段。它表示 MySQL 在表里定位数据的访问方式从好到差依次是system、const、eq_ref、ref、range、index、ALL。先说最好的情况const表示用主键或唯一索引做等值查询最多只能命中和条件匹配的一行记录这种速度是最快的。system是const的特例出现在 MySQL 内部系统表的查询中业务 SQL 里基本见不到。然后是eq_ref在联表查询时驱动表使用普通索引关联被驱动表被驱动表的关联列恰好是主键或唯一索引每次只会匹配一行。这里的关键在于联表字段必须有唯一索引否则就会退化到下一档。ref是非唯一索引的等值匹配可能命中多行。range是索引范围扫描比如BETWEEN、 IN这类操作。这两个级别虽然不如前面快但对大多数 OLTP 业务来说已经足够健康。index是遍历二级索引树的扫描方式本质还是全量遍历只是索引树比数据行小很多所以速度快于全表。ALL就是全表扫描数据可以从表的第一行扫到最后一行。一条查询如果 type 到了ALL而且表里数据量已经到百万级别那基本等于宣告这条 SQL 已经生病了。判断执行计划的健康程度可以记住一条经验法则range及以上访问方式通常可接受index级别要警惕遇到ALL必须排查。但这不是绝对的如果业务表本身只有几千条数据全表扫描也完全没问题不要为了优化而优化。ref_or_null、index_merge、unique_subquery、index_subquery这几个变种级别也值得了解。index_merge是 MySQL 在 5.1 之后引入的索引合并能力它可以在一个查询里同时使用多个索引取交集或并集但从我个人经验来说它往往是索引设计不合理的信号——建一个合适的联合索引通常比依赖索引合并更稳定。2.2 key_len 的计算联合索引真正用了哪几列key_len表示查询使用的索引长度单位是字节。它听起来枯燥却能直接告诉你联合索引到底用上了几列是排查索引失效问题的核心字段。计算规则需要掌握整数字段根据类型长度不同TINYINT1 字节SMALLINT2 字节MEDIUMINT3 字节INT4 字节BIGINT8 字节可变长度字符字段字符集字节数乘以声明长度VARCHAR(20)在 utf8mb4 字符集下最大是 80 字节但还要再加 2 字节记录实际长度所以总共算 82 字节允许为 NULL 的列额外占用 1 字节标记位大多数人在索引失效排查时只看 type 和 key但 true 的高手会看 key_len 来确认联合索引的利用率。举个例子假设有一条 SQLSELECT * FROM orders WHERE status PAID AND create_time 2024-01-01;这张表上有联合索引(status, create_time)。如果你看到执行计划里key显示用的是idx_status_time但key_len只有 2 字节说明 MySQL 只用了status这一列来索引create_time没有参与。为什么会这样大概率是status字段定义为CHAR(2)且字符集是 latin1而create_time筛选条件区分度不够优化器认为继续用索引还不如回表成本低。所以每次查看执行计划时看到联合索引出现在key列要习惯性核对key_len是否等于预期的全列长度。不一致时说明索引一部分失效了这时候的下钻方向就是搞清楚优化器为什么放弃后面的列。2.3 Extra 列隐藏的性能地雷都在这里Extra列是执行计划里信息量最大的部分里面藏着的几个关键词直接决定 SQL 是否会在某个环节拖慢整体性能。Using filesort是第一个需要警惕的信号。它不代表磁盘文件排序可能发生在内存中但含义是 MySQL 需要额外对结果做一次排序操作。排序能走索引就不用 filesort比如ORDER BY字段和查询条件组成的联合索引顺序一致时数据天然有序省掉了排序这步。出现 filesort 时优先检查排序字段有没有被索引覆盖、排序顺序是否和索引顺序一致。Using temporary表示 MySQL 需要临时表来完成操作通常出现在GROUP BY、DISTINCT、某些JOIN场景。临时表会占用内存或磁盘当结果集很大时性能会急剧下降。看到一个 SQL 同时出现 filesort 和 temporary就说明这条 SQL 在索引设计上有较大问题改动时要有整体考量。Using index反而是好信号表示查询所需的所有数据都在二级索引树里不需要回表查聚簇索引这叫覆盖索引扫描。这也是为什么很多时候建议把查询列加入联合索引末尾让 select 列和 where 条件列一起构成覆盖索引。Using index condition表示索引条件下推MySQL 5.6 之后的特性。它指的是过滤条件可以在索引层先做一遍只把符合条件的数据回表取出来。整体性能也不错但不如覆盖索引高效可以理解成提前做了个预筛。Using where表示 MySQL 在获取数据后又对存储引擎返回的记录做了进一步过滤。这种不等于有问题但如果typeALL同时还有Using where就说明先做了全表扫描然后再逐行筛数据该考虑优化了。Impossible WHERE和No matching row in partitioned table这类属于优化器静态判断条件恒为假通常是 SQL 逻辑写错了比如WHERE 10。2.4 rows 和 filtered 的联动关系rows表示 MySQL 预估需要读取的行数filtered表示经过 WHERE 条件过滤后剩余行数的百分比。这两个字段连起来看可以判断索引选择是不是合理。比如执行计划显示rows是 10000filtered是 5那么 MySQL 预估最终返回约 500 行。如果实际返回的行数确实在几百行这个量级说明预估还算准确优化器选的执行方式没问题。但如果 rows 是 500000 而 filtered 是 0.1实际结果集却只有几十行说明大量行被 WHERE 条件过滤掉了索引本身没有起到很好的筛选作用。需要明确一点rows是基于统计信息估算出来的不是精确值。MySQL 通过采样和基数统计来估算某个索引能筛掉多少行当表数据频繁更新而统计信息过旧时这个预估可能严重失真。所以行数和实际返回行数差距过大时先执行ANALYZE TABLE 表名让优化器拿到最新的统计信息再重新看执行计划。合理利用 filtered 的方法是观察一条 SQL 的预估行数对比实际 SQL 结果集大小。预估筛选率低于 10% 却还需要扫描数十万行时重点检查 WHERE 条件里的字段是否存在隐式类型转换或是有对索引列做函数运算这些都会让索引失效使优化器只能扫全表再过滤。3. 从执行计划到索引优化三种最典型的翻车场景3.1 联合索引左前缀原则失效联合索引遵循最左前缀原则查询条件从联合索引最左边开始连续匹配才能生效。很多优化新手在理解了这一点后容易过度建索引比如为(a, b, c)建了索引又单独为(a)、(a, b)建索引这是典型的重复索引浪费。但比这个更隐蔽的坑是查询条件没有违反最左前缀原则但中间某列使用了范围查询导致后面的列索引失效。SELECT * FROM orders WHERE user_id 1 AND status PAID AND create_time 2024-01-01;即使有联合索引(user_id, status, create_time)因为statusPAID是等值匹配、create_time 是范围匹配这个查询中三段索引全都用上了key_len 是完整的。但如果把条件换成SELECT * FROM orders WHERE user_id 1 AND create_time 2024-01-01 AND status PAID;同样一个索引(user_id, status, create_time)优化器在遇到user_id等值之后下一个连续的索引列是status但 WHERE 条件里没有直接给出status的等值条件于是create_time 的范围条件就无法继续使用索引。验证方法就是看 key_len只有 user_id 部分的长度后面的全部失效。这种问题在业务不断迭代时尤其常见条件是动态拼接的不同分支拼出来的 WHERE 顺序不一致执行计划也就跟着变。我的建议是维护联合索引时把等值条件字段放前面范围条件字段放后面。并且确保应用的 SQL 逻辑上尽量固定条件顺序。3.2 隐式类型转换导致索引失效执行计划里 type 是 ALLkey 是 NULL但字段本身明明有索引这种情况十次有八次是隐式类型转换导致的。最常见的场景手机号字段定义是VARCHAR(20)但业务代码里传的查询条件是数字类型。SELECT * FROM user WHERE phone 13800138000;字段是VARCHAR传入的参数是数字比较MySQL 会把字段转换成数字再做比较导致索引列上发生了隐式函数运算索引自然走不了。执行计划会显示 key 为 NULL全表扫描。修正方式很简单把参数改成字符串SELECT * FROM user WHERE phone 13800138000;排查时可以注意区分到底谁被转换了如果字段是 varchar传入是数字MySQL 将字符串转换为数字如果字段是数字传入是字符串MySQL 将字符串转换为数字比较时不会对索引列做函数运算。所以通常表现为字符串字段上使用数字参数导致索引失效。反过来还有一种坑字符集不一致。两张表关联时关联字段一个是 utf8mb4 一个是 utf8字段类型长度一致但执行计划显示被驱动表全表扫描。这就是因为两个表的关联列字符集不一致MySQL 在比较时要把其中一端先做字符集转换才能对比函数运算同样会让索引失效。解决方案是统一字符集和排序规则这是建表规划时就要考虑的。3.3 函数运算和模糊查询的索引失效WHERE DATE(create_time) 2024-01-01这种写法非常常见但它对 create_time 列做了函数运算索引失效。类似的还有WHERE MONTH(create_time) 1。这类查询应该改写为范围条件WHERE create_time 2024-01-01 AND create_time 2024-01-02不止能走索引性能差异在几百万行数据上是数量级的前者扫全表后者走 range 扫描。模糊查询LIKE %关键词%因为前导百分号的存在无法使用普通索引。但有一种情况例外如果搜索的字段和查询字段组合形成覆盖索引MySQL 可能选择扫描整个索引树而不是全表此时 type 为 index虽然比不上 range但比 ALL 好。更有效的方法是引入全文索引或者针对业务场景用搜索引擎而不是硬调 SQL。但要注意LIKE 关键词%这种只带后缀的模糊查询是可以走 range 的这也是为什么很多搜索需求要求前缀匹配优先。4. 一个完整的优化实战从慢 SQL 到 Explain 全程指导模拟一个典型的业务场景订单管理后台需要查询某时间段内某个商品分类的支付订单列表打印订单号和支付金额。初版 SQL 长这样SELECT o.order_no, o.amount, o.status FROM orders o LEFT JOIN order_items oi ON o.id oi.order_id WHERE oi.category_id 501 AND o.pay_time 2024-06-01 AND o.pay_time 2024-07-01 ORDER BY o.pay_time DESC LIMIT 50;业务方反馈接口要 2.8 秒。第一步拿到慢查询日志里的原始 SQL执行 ExplainEXPLAIN SELECT o.order_no, o.amount, o.status FROM orders o LEFT JOIN order_items oi ON o.id oi.order_id WHERE oi.category_id 501 AND o.pay_time 2024-06-01 AND o.pay_time 2024-07-01 ORDER BY o.pay_time DESC LIMIT 50;执行计划概览idselect_typetabletypekeykey_lenrowsExtra1SIMPLEoiALLNULLNULL800000Using where; Using temporary; Using filesort1SIMPLEoeq_refPRIMARY81从表访问顺序看优化器先访问了 order_items 表join 顺序是 oi 驱动 o但 oi 表 type 是 ALL扫描行数 80 万。这时候哪怕 o 表用主键 eq_ref 关联整体开销依然巨大。更糟糕的是用到了临时表和 filesort因为驱动表的结果集太大了。接下来看 oi 表为什么全表扫描。在 oi 表上确认索引结构order_items有主键索引、idx_order_id单列索引。SQL 里过滤条件是category_id而order_id只是关联条件不是过滤条件所以idx_order_id在这个场景几乎没派上用场优化器干脆全表扫。这里就发现一个索引设计硬伤关联字段索引无法作为驱动表的过滤条件。正确索引设计应该创建(category_id, order_id)联合索引这样既能让category_id快速过滤商品分类又让order_id覆盖关联查询需求实现索引覆盖的同时避免回表。另一个风险点在这条 SQL 的 JOIN 语义上使用LEFT JOIN查询但 WHERE 条件里却使用了右表字段oi.category_id参与过滤——这本质上已经把 LEFT JOIN 变成了 INNER JOIN。优化器事实上也确实按内连接处理了。与其保留这种误导的写法不如直接改写成 INNER JOIN让语义更清晰对执行计划也有帮助。优化后 SQLSELECT o.order_no, o.amount, o.status FROM orders o INNER JOIN order_items oi ON o.id oi.order_id WHERE oi.category_id 501 AND o.pay_time 2024-06-01 AND o.pay_time 2024-07-01 ORDER BY o.pay_time DESC LIMIT 50;同时给order_items表添加联合索引ALTER TABLE order_items ADD INDEX idx_category_order (category_id, order_id);执行计划变化如下idselect_typetabletypekeykey_lenrowsExtra1SIMPLEoirefidx_category_order4120Using index condition1SIMPLEoeq_refPRIMARY81Using where这里有一个关键优化点虽然联合索引(category_id, order_id)只用了 category_id 这一列做过滤key_len 只有 4但 order_id 在索引中已经存在配合 join 条件o.id oi.order_id可以避免回表操作。注意执行计划里rows从 80 万降到了 120同时临时表和 filesort 从 Extra 里消失了。原因在于这里优化器驱动表变为使用索引过滤的order_items结果集大幅变小那么ORDER BY o.pay_time需要在驱动表很小的情况下排序代价整体可接受不再需要临时表辅助排序。改完之后接口 RT 从 2.8 秒降到 60 毫秒接近 50 倍的提升本质就是执行计划从全表扫描变成了 ref 索引扫描。这个案例给人的最大启发是不要单看一条 SQL 本身长什么样要看清表之间的关联关系、过滤条件落在哪一侧。很多时候慢的根因不在于查询语句的复杂度而是索引设计和驱动表的选取。5. 常见问题速查表执行计划字段里的那些坑把日常排查中常见的问题整理成一张速查表按图索骥的效率会高很多现象含义排查方向typeALL rows 过大全表扫描检查 WHERE 条件列是否有索引、是否发生隐式类型转换keyNULL 但字段有索引索引失效检查索引列是否被函数运算、字段类型和字符集是否匹配key 显示索引但 key_len 偏短联合索引部分失效核对索引列顺序和条件顺序范围条件后的列不生效Extra 出现 Using filesort结果集额外排序检查 ORDER BY 字段是否在索引中、排序方向和索引方向是否一致Extra 出现 Using temporary使用了临时表常见于 GROUP BY 和 DISTINCT检查索引是否覆盖分组字段Extra 出现 Using index覆盖索引扫描正常状态说明查询数据都在索引树里这就是理想情况rows 与实际行数差距大统计信息过旧执行 ANALYZE TABLE 更新表的统计信息再重新看计划filtered 值很小扫描行数多但命中率低条件列区分度不足或该列有大量重复值考虑重新设计索引Using index condition索引条件下推MySQL 5.6 默认支持比回表后过滤好但不如覆盖索引Impossible WHERE条件恒为假检查 SQL 逻辑比如 WHERE 10 这类冲突条件还有一个经常被忽略的问题执行计划里的rows是预估行数不是精确值。优化器基于统计信息做成本估算而统计信息本身存在抽样误差。当表数据暴涨或者大量删除后统计信息容易失真这时的执行计划可能选出次优方案。定期ANALYZE TABLE是维护索引性能很重要的习惯。在 extra 里出现Using index condition时存储引擎会先把符合索引条件的行先筛一遍然后回表取数。但如果 SQL 里筛选出的字段也能放进索引列就成了Using index回表次数降为零。因此设计联合索引时把查询列追加进索引是一种常见的优化手段但也别因此让索引字段过多索引本身也有存储和维护成本。多表 JOIN 的执行计划里还要额外注意驱动表的优先级MySQL 优化器一般会选择小表作为驱动表大表作为被驱动表。如果执行计划里驱动表的过滤条件特别弱比如只有关联条件没有过滤条件就很容易变成全表扫描驱动再逐行关联性能会急剧下降。遇到这种情况需要调整索引策略让驱动表的过滤条件有合适的索引支撑。6. 复盘我在实战中用 Explain 的一些体会Explain 是个听起来很基础的工具但真正拿它来驱动性能优化需要形成一套方法论而不是只会看单个字段。我自己的习惯是先确认 type 不差再看 key_len 是否完整最后检查 Extra 有没有 sorting 或 temporary。这套三步走已经帮我解决过很多起查询性能事故。记得有一次排查一个复杂的报表导出任务导出几万行数据要 20 多分钟业务方已经准备把功能下线了。我去翻 SQL发现 GROUP BY 子句和 SELECT 字段完全没有索引覆盖执行计划里临时表和 filesort 并排站就把 GROUP BY 字段和筛选条件组合起来建了一个大联合索引再次执行时临时表消失导出时间缩短到 40 秒。过程中唯一的小插曲是索引需要的内存变大了一些但权衡收益代价非常值得。还有一些靠 Explain 看不出所以然的情况比如某些表中的数据分布极度不均匀抽样统计的基数无法反映真实分布优化器计算出来的成本就容易跑偏。这时候就要配合看真实的执行追踪或者加提示词强制走某条索引再决定是改 SQL 还是修正统计信息。但对于绝大多数查询优化Explain 已经把答案写在脸上了全扫和索引扫描的区别、排序和临时表的存在、大表和驱动表的顺序都在区区几行输出里。只是很多人只看到 show 出来的表格没看到表格背后想告诉你的故事。