一条SQL从300毫秒变成3秒这种事我遇到过不止一次。遇到这种慢性查询绝大多数人第一反应都是EXPLAIN一把梭但真正把EXPLAIN每个字段都看明白的人却不多。绝大多数情况下我们只看有没有出现ALL、有没有Using filesort其他字段直接忽略。这样用EXPLAIN约等于是靠猜。我写这篇东西的动机很简单把我这些年用EXPLAIN排查慢SQL的经验完整梳理一遍包括每个字段的实际含义、type访问级别的完整阶梯、key_len怎么手算、Extra信息里哪些是坑以及真正的实战排查流程。这篇内容面向的是正在被慢查询折磨的开发者、运维、DBA初学者不管你是什么角色只要能把EXPLAIN吃透定位慢查询的能力会直接上一个台阶。先提醒一句EXPLAIN输出的是执行计划是MySQL基于统计信息和成本模型算出来的预估结果不是真实执行结果。理解这个前提后面所有分析才有意义。1. 一条慢SQL的排查起点EXPLAIN到底在看什么1.1 执行计划是“最优路径预估”不是真实成绩单优化器在选择执行方式时会基于表的统计信息行数、索引基数、数据分布等计算各种执行路径的代价取一个它认为成本最低的方案。这个方案就是EXPLAIN展示的“执行计划”。说白了EXPLAIN就像导航给出的预计到达时间它可能因为地图数据不准确而出现偏差但你行车过程中的任何异常导航都能帮你定位到关键路口。很多人忽略的一点是EXPLAIN不会真正执行查询因此它不会告诉你这个SQL到底跑了多少毫秒也不会告诉你实际扫描了多少行。它只告诉你“优化器觉得该怎么做”。这也是后续第5章要讲的EXPLAIN ANALYZE存在的意义——那是MySQL 8.0提供的真执行、真测量工具。1.2 什么时候必须看执行计划我个人的判断标准是单表查询超过100ms、多表关联超过200ms、接口调用的SQL耗时明显波动、或者线上出现慢查询日志报警时第一件事就是拿EXPLAIN看执行计划。不要先优化代码、不要盲目加缓存先把SQL拿出来单独EXPLAIN一下10秒钟就能知道问题大概在哪。但EXPLAIN也不是万能的它不会主动告诉你“这个索引该建在哪个字段上”你需要通过观察type、key_len、rows这几个字段来反向推理。这就是为什么很多人EXPLAIN完了还是不知道怎么优化——因为他们只会看有没有走索引不会看走了哪个索引、用了索引的哪一部分、预计扫了多少行。1.3 准备一个最小复现环境为了方便后面案例演示我先定义两张表后面所有分析都基于这个环境。CREATE TABLE emu_user ( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, login_name VARCHAR(50) NOT NULL, phone VARCHAR(20) NOT NULL, status TINYINT NOT NULL DEFAULT 1, created_at DATETIME NOT NULL, KEY idx_login_name (login_name), KEY idx_phone_status (phone, status) ) ENGINEInnoDB; CREATE TABLE emu_order ( id BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, order_no VARCHAR(32) NOT NULL, amount DECIMAL(10,2) NOT NULL, status TINYINT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL, KEY idx_user_id (user_id), KEY idx_created_at (created_at) ) ENGINEInnoDB;建完表后插个几十万测试数据然后就可以开始各种EXPLAIN了。2. 核心字段逐个拆解每个数字和单词都有意义2.1 id与select_type看清查询的嵌套结构EXPLAIN输出第一列是id。同一个SELECT语句中的多个表id相同子查询或UNION中的独立SELECTid递增。以前有个说法叫“id越大越先执行”这个说法在大多数场景是对的因为MySQL会先把子查询的结果物化出来再执行外层查询。但注意在JOIN场景下多个id相同的行执行顺序不一定是自上而下优化器可能调整驱动表的顺序。select_type有几种SIMPLE是普通单表查询PRIMARY是最外层SELECTSUBQUERY是出现在WHERE或SELECT列表中的子查询DERIVED是FROM子句中的派生表UNION是UNION中的第二个及后面的SELECTUNION RESULT是从UNION临时表读取结果的阶段。我经常看到有人把DERIVED误认为临时表性能差其实MySQL 8.0已经对派生表做了合并优化很多时候EXPLAIN根本看不到DERIVED。实际排查中如果EXPLAIN结果里有DERIVED和UNION RESULT要多加注意这意味着查询中存在临时表操作。临时表意味着额外的创建和数据拷贝成本特别容易成为慢查询的根源。2.2 type访问类型从ALL到const的完整阶梯type字段是我对一张执行计划的第一判断依据。MySQL官方文档把type分成若干个访问类型从性能差到好大致是这样一条线type值含义代表性场景性能评价ALL全表扫描无索引或优化器认为索引无意义最差必须避免index全索引扫描遍历整个索引树不访问表数据比ALL好点但不代表没问题range索引范围扫描WHERE里用了、、、BETWEEN、IN等可接受ref非唯一索引等值匹配普通索引列 常量良好eq_ref被驱动表通过主键或唯一索引匹配JOIN中驱动表一行只匹配被驱动表一行优秀const主键或唯一索引等值查询WHERE id 1最优system表中只有一行数据系统表极小表最优补充一个容易混淆的点system是const的一种特例只有表中恰好一行时才出现平时基本见不到。还有一个很常见的误判是看到typeindex就以为走了索引万事大吉实际上index代表的是扫描了整个索引树虽然在二级索引上扫描比全表数据扫描要小但如果要回表的话代价依然可能非常高。判断一条SQL是否需要优化的底线range以上range、ref、eq_ref、const通常可以接受index和ALL一定要想办法消除。2.3 possible_keys与key索引的“海选”和“胜出者”possible_keys列显示优化器认为“可能用得上”的索引列表key列显示实际选用的索引。这个组合非常有意思possible_keys为空说明这条SQL的WHERE条件里没有可用的索引字段需要重点关注是不是列类型不匹配、函数包裹列、或者根本没有建索引possible_keys有值但key为空说明优化器遍历了候选索引后认为都不如全表扫描这种情况常见于数据量小或区分度低的场景possible_keys和key都有值但不是同一个说明优化器尝试了多个索引后做了取舍。一个更隐蔽的情况是possible_keys有多个key选了其中一个但实际只用了这个索引的前缀字段。这种情况就要结合第3章的key_len来看了。2.4 rows与filtered优化器心里的“小算盘”rows是优化器预估的需要扫描的行数。注意是预估不是实际值。这个数字受统计信息影响很大如果表长时间没做ANALYZE TABLErows可能是严重偏离的。filtered表示经过WHERE条件过滤后剩余行数占rows的百分比。这两个字段结合起来可以用来估算最终返回的行的规模。我举个直觉化的例子rows100000filtered10说明优化器认为会从10万行中过滤出1万行。如果这个查询还要做GROUP BY或JOIN1万行的中间结果集是非常可观的。反过来看rows很小但filtered很低比如rows10filtered1说明数据过滤能力强大概率走了索引并且索引选择性好。有一个常见的坑rows不准。特别是OR条件、IN列表、函数包裹导致无法准确估算时rows和实际扫描行数可能差出10倍甚至百倍。我在第5章会提到用EXPLAIN ANALYZE校准真实行数。2.5 Extra里藏着的问题Using index、Using where、Using filesort、Using temporaryExtra这一列信息量最大也是很多人忽略的地方。我把日常排查中遇到频率最高的几种列出来并说清楚每个的取舍Using index覆盖索引扫描查询的所有字段都在索引里不需要回表。这是最理想的Extra。看到这个值时可以放心SQL基本没大问题。Using where存储引擎返回数据后服务层还要再做一层WHERE过滤。注意这不代表SQL有问题而是说明有一部分过滤条件无法在索引层面完成。如果索引已经过滤了绝大部分数据Using where是正常的。Using index condition对应索引下推Index Condition PushdownICP。MySQL可以把部分WHERE条件下推到存储引擎层在索引读取时直接过滤。这是5.6以后默认开启的优化InnoDB里很常见是好事。Using filesort文件排序。注意这个“文件”不是真的指磁盘文件只是表示MySQL需要额外的排序操作。它可能是内存排序也可能是磁盘排序。一旦出现这个值意味着你的ORDER BY/GROUP BY没有完全用到索引顺序排序的代价不会小。Using temporary用到了临时表来去重或排序。GROUP BY、DISTINCT、UNION操作经常触发。临时表意味着要创建、写入、读取性能一定会受影响。看到它就值得进一步优化。Using join buffer关联查询中被驱动表没有走索引MySQL只能用join buffer把驱动表的数据缓存起来再逐行匹配。这种情况在业务库中非常典型基本可以断定JOIN字段没有索引或者索引失效了。Extra里如果同时出现Using temporary和Using filesort基本可以断定这条SQL是重灾区必须重写或者补索引。3. type级别与key_len判断索引好坏的硬指标3.1 访问级别的取舍判断我经常跟人讲拿到一条EXPLAIN先看type再看Extra最后看rows和key_len组合。type决定了这条SQL访问数据的方式Extra决定了后处理阶段有没有额外成本。只要type在ref以上且Extra里没有filesort和temporary就算rows看起来有点大通常也不会慢到哪里去。反过来如果type是ALL或者index那么即使Extra看起来很干净你也要警惕。ALL意味着扫描整个表数据量过百万后就是灾难index虽然是在索引树上扫但如果索引树的叶子节点数量庞大而且可能还需要回表性能同样拉胯。3.2 key_len的手动计算方法索引到底吃了“几碗饭”key_len是我想重点展开的字段因为很多优化老手看执行计划主要就看key_len。它表示MySQL实际使用了联合索引中多少个字节的索引长度。通过key_len你能精准判断出这条SQL到底用到了联合索引的第几列。具体计算方法以最常用的utf8mb4字符集为基准列类型允许NULLNOT NULLkey_len计算INT否是4INT是否5BIGINT否是8DATETIME否是5MySQL 8.0.13前8带毫秒时VARCHAR(20)否是20*4282VARCHAR(20)是否20*42183CHAR(20)否是20*480规则其实就几条整型按固定字节数计算INT4BIGINT8DATETIME在8.0里按8字节算更精确的表述是MySQL 5.6以后DATETIME占5字节但如果显式带微秒精度则更多这里我按常见生产库的8字节处理实际值以版本行为为准变长字符串VARCHAR等于字符数乘以字符集最大字节数再加2字节变长长度标记CHAR按声明字符数乘以最大字节数计算任何列允许为NULL字节数加1。举个例子如果要查一张表的联合索引idx_phone_status是(phone VARCHAR(20), status TINYINT)两个字段都不允许NULL那么在utf8mb4下如果EXPLAIN显示key_len84你可以拆解为phone部分20*4282status部分1字节前面有没有多出1字节取决于是否可空这里按不可空就算1合计83实际如果另有1字节说明列定义可能带了额外标记需要结合表结构复核。一般的经验是只要key_len比完整索引长度短就说明只用了联合索引的前缀列key_len等于完整长度说明联合索引用满了。拿适配的场景再说一次idx_phone_status(phone, status)查询条件为WHERE phone123...那么key_len应该是82不含status部分typeref查询条件为WHERE phone123... AND status1key_len就是821可空标记完整长度。通过这个数字的差值你可以判断出联合索引是否被充分利用。3.3 联合索引的最左前缀如何体现在执行计划里联合索引的底层是B树排序规则按索引定义的字段从左到右依次生效。这就意味着只有当WHERE条件中用到了联合索引从最左列开始的连续字段索引才会被完整使用。跳过中间字段直接查后面的字段索引就只能用前缀部分。一个经典的场景联合索引是(a,b,c)查询条件只有WHERE b1 AND c2那么key_len只会显示a字段的索引长度如果a没有出现在条件中甚至可能type直接从ref变成index或ALL。这个现象如果不懂key_len只看type是ref你很可能误以为索引用得很好实际上a列根本没被使用或者优化器做了索引跳跃扫描Index Skip Scan8.0新增能力才能勉强用上后面的列。我一般会建议同事把EXPLAIN里的key_len和一个已知的全长索引做个快速对比差多少字节就能知道哪一列没有被用上。这种方法比肉眼猜SQL要靠谱得多。4. 实战用EXPLAIN揪出三条典型问题SQL4.1 案例A关联查询没走索引全表扫描拖垮接口某次线上有个接口突然变慢SQL长这样SELECT u.login_name, o.order_no, o.amount FROM emu_order o LEFT JOIN emu_user u ON o.user_id u.id WHERE o.created_at 2024-01-01 ORDER BY o.created_at DESC;EXPLAIN结果idselect_typetabletypepossible_keyskeykey_lenrowsExtra1SIMPLEuALLPRIMARYNULLNULL80000NULL1SIMPLEorangeidx_created_atidx_created_at520000Using filesort仔细看驱动表是u居然是全表扫描被驱动表o虽然走了idx_created_at范围查找但最终还有20000行要跟u表匹配。u.id是主键那为什么type不是eq_ref反而是ALL问题出在LEFT JOIN的方向。对LEFT JOIN来说左表是驱动表优化器一开始选择u作为驱动表用o去匹配u时理论上u.id主键是可以被eq_ref命中的但执行计划里u显示为ALL说明优化器因为join buffer或其他统计原因放弃了主键匹配。再往下深挖真正的问题是子表关联字段数据类型不一致或驱动表选择不当。我把查询改成STRAIGHT_JOIN强制u作为被驱动表SELECT u.login_name, o.order_no, o.amount FROM emu_order o STRAIGHT_JOIN emu_user u ON o.user_id u.id WHERE o.created_at 2024-01-01 ORDER BY o.created_at DESC;再次EXPLAINidselect_typetabletypepossible_keyskeykey_lenrowsExtra1SIMPLEorangeidx_created_atidx_created_at520000Using filesort1SIMPLEueq_refPRIMARYPRIMARY41NULL这个结果就健康了o表先范围扫描然后对每一条o记录用主键去u表精确匹配一行回表次数可控类型也变成了eq_ref。通过这个例子我想强调EXPLAIN的关联顺序是对优化器决策的体现如果发现驱动表选错了优先检查字段类型一致性、统计信息是否更新最后再考虑用STRAIGHT_JOIN或者改写SQL来引导执行计划。4.2 案例B索引明明存在却不被使用隐式类型转换有一次排查一个用户查询SQL很简单SELECT * FROM emu_user WHERE phone 13800138000;phone字段上明明有索引idx_phone_status(phone, status)但EXPLAIN显示typeALLpossible_keys是NULLkey也是NULLExtra为Using where。字段有索引却不走十有八九是类型隐式转换。看表结构phone是VARCHAR(20)但查询条件里写的是数字13800138000。MySQL会尝试把字符串类型的列和数字类型做比较发生隐式cast相当于执行了CAST(phone AS SIGNED)。函数包住索引列之后索引的“有序性”就失效了——MySQL没法直接在B树上按函数计算结果定位数据只能全表扫描后逐行做转换和比较。解决方案也简单把SQL改成字符串形式SELECT * FROM emu_user WHERE phone 13800138000;这次EXPLAIN变成typerefpossible_keys和key都是idx_phone_statuskey_len为82Extra里还有一个Using index condition。耗时从数百毫秒降到毫秒级。这个案例提醒我们EXPLAIN看到possible_keys为空不要急着说“没索引”先去核对表结构和SQL里条件字段的数据类型是否匹配。隐式转换是最容易被忽略的索引失效原因没有之一。4.3 案例C排序导致的filesort不仅慢还吃内存再分享一个ORDER BY引发的问题。用户列表接口SQL长这样SELECT id, login_name, phone, status FROM emu_user WHERE status 1 ORDER BY created_at DESC LIMIT 20;EXPLAIN结果如下idselect_typetabletypepossible_keyskeykey_lenrowsExtra1SIMPLEemu_userrefidx_phone_statusidx_phone_status140000Using where; Using filesort表面上看type是ref走了idx_phone_status不算差。但注意还有Using filesort这意味着MySQL要把4万行数据先查出来排完序后再取20行排序的成本远远大于查询本身。问题的根源在于ORDER BY created_at不在索引里而优化器为了使用status过滤牺牲了created_at的排序连续性。什么情况下可以免排序如果创建联合索引(status, created_at)查询条件WHERE status1正好命中status前缀而ORDER BY created_at又是索引的下一列数据在B树中本身就是有序的不需要额外的filesort。改造方案ALTER TABLE emu_user ADD INDEX idx_status_created (status, created_at);再次EXPLAINkey变成idx_status_createdExtra里只剩Using index conditionfilesort消失。一条慢SQL从300ms降到30ms以内这就是执行计划立下的功劳。我在实际项目中反复验证过一个经验凡是看到EXPLAIN结果里带filesort先别急着加排序缓冲区优先想能不能设计一个索引让排序字段天然有序。加缓冲区只是缓解症状索引才是根治。5. EXPLAIN的局限和它的搭档们5.1 为什么rows会和实际扫描行数差很多EXPLAIN的一切结论都是基于优化器的估算而估算依赖表的统计信息。InnoDB的统计信息来自随机采样不是全量精确统计。当数据分布严重不均衡时比如某字段存在大量热点值可能导致rows和filtered的预估失真。一旦发现EXPLAIN的rows与真实扫描行数差异巨大先执行一下ANALYZE TABLE table_name重新搜集统计信息再回头看执行计划。很多“怪异”的慢查询更新完统计信息后自动恢复正常因为这个操作直接影响优化器对执行路径的取舍。5.2 用optimizer trace看优化器的内心戏EXPLAIN只能看到优化器最终选出来的路径但看不到它为什么这么选。MySQL 8.0提供了optimizer trace能输出优化器做决策的全过程包括访问路径候选、成本估算、索引选择原因等。用法很简单SET optimizer_traceenabledon; SELECT * FROM emu_order WHERE user_id 123 AND amount 100; SELECT * FROM information_schema.OPTIMIZER_TRACE; SET optimizer_traceenabledoff;我通常是在EXPLAIN结果看起来“反常”时才用optimizer trace比如优化器明明有更好的索引却选了别的或者因为统计信息偏差导致驱动表选反。它能直接告诉你优化器尝试过哪些索引、各自估算的成本是多少非常上瘾但生产环境慎用enabledon会有额外开销用完立刻关掉。5.3 EXPLAIN ANALYZEMySQL 8.0的真执行、真测量如果你用的是MySQL 8.0.18及以上版本强烈推荐认识EXPLAIN ANALYZE。它和普通EXPLAIN最大的区别是真正把SQL执行了一遍并返回每个步骤的实际耗时、实际扫描行数、实际循环次数。EXPLAIN ANALYZE SELECT u.login_name, o.order_no FROM emu_order o LEFT JOIN emu_user u ON o.user_id u.id WHERE o.created_at 2024-01-01;输出中会包含类似actual time0.5..2.3 rows20000 loops1的信息意思是这个阶段实际耗时2.3毫秒返回2万行只循环了1次。看到actual rows和EXPLAIN预估的rows如果差距很大说明统计信息不准或者优化器的估算模型不适应高基数字段。但要注意EXPLAIN ANALYZE是真的会执行SQL的。在线上大表上对一个复杂查询跑EXPLAIN ANALYZE可能会真实地产生全表扫描、大排序甚至拖垮实例。我的使用习惯是先在测试库用普通EXPLAIN确认大体方案再在低峰期或只读从库上用EXPLAIN ANALYZE校准真实行数和耗时。绝不能拿它在大生产库上乱跑。5.4 profiling与慢查询日志的配合有一类问题是EXPLAIN完全看不出来的比如“锁等待”和“IO抖动”。执行计划再完美锁等100秒也是慢查询。这时候我会用SHOW PROFILE来看整个执行的生命周期或者直接开启慢查询日志配合pt-query-digest做诊断。流程一般是先看慢查询日志找出候选SQL再用EXPLAIN定位执行计划问题然后视情况用optimizer trace或EXPLAIN ANALYZE做深入校准。四者分工不同彼此不能替代。6. 经典问题速查表与避坑心得6.1 常见EXPLAIN结果对照速查下面这个表是我自己整理的基本覆盖了日常慢查询的大部分场景。看到对应的EXPLAIN特征就能直接对应到解决方案。现象可能的根因优先排查方向typeALLpossible_keys为空无索引、隐式类型转换、函数包列核对表结构类型检查SQL条件表达式typeALLpossible_keys有值但key为空索引区分度低或统计信息过期更新统计信息或考虑不建索引改为覆盖索引key_len明显短于完整联合索引长度联合索引前缀命中不足调整索引列顺序或补充查询条件列ExtraUsing temporaryGROUP BY、DISTINCT、UNION等引起临时表检查是否有索引可以覆盖分组排序字段ExtraUsing filesortORDER BY/GROUP BY未走索引顺序建立覆盖排序字段的联合索引ExtraUsing join bufferJOIN字段无索引或字段类型不一致检查关联列索引和数据类型对齐rows很大但typeref非唯一索引返回大量重复值增加筛选条件提高区分度possible_keys和key不一致优化器做了成本对比看统计信息必要时强制指定索引6.2 我踩过的几个坑第一个坑以为走了索引就万事大吉。有一次我优化一条SQLEXPLAIN显示typeref、key_len也正确但是接口依然慢。最后加了一列没有索引的分组字段导致Guo程中途要创建临时表而EXPLAIN里根本没显示出来因为那个临时表是在子查询内部产生的。吃一堑长一智遇到EXPLAIN看不出来的问题就把SQL一层层拆开细看尤其是子查询和派生表部分。第二个坑索引区分度的判断。给状态字段比如status建索引要非常谨慎如果这个字段只有几个离散值1和0区分度极低优化器可能宁愿全表扫描也不走索引。我见过有人给status建了索引但EXPLAIN出来还是ALL于是怀疑索引坏了其实是优化器觉得没价值。判断标准很简单某个值对应的行数占总行数超过15%到20%时索引基本就没意义了。第三个坑关于NULL。MySQL导致索引失效的因素里WHERE phone IS NOT NULL是一种很隐蔽的情况。有些版本的优化器对IS NULL和IS NOT NULL的处理并不一致IS NULL有时候还能用上refIS NOT NULL反而不走索引。如果你的业务允许字段为NULL又需要频繁筛选NULL考虑用默认值替代NULL这也是很多表设计规范里写“不允许NULL”的原因之一。第四个坑EXPLAIN标记为const的查询也可能慢。const代表“仅匹配一行”听起来已经最优了但别忘了还有锁等待、网络传输和连接池排队的时间。EXPLAIN只管执行计划管不了并发环境下的资源竞争。6.3 线上排查EXPLAIN的正确姿势不要在生产环境大表上直接跑EXPLAIN ANALYZE。具体建议是先在从库或测试环境用普通EXPLAIN分析把执行计划优化到typeref/eq_ref级别且无filesort后再在低峰期用EXPLAIN ANALYZE验证一次真实耗时与行数如果仍然无法解释再配合optimizer trace看优化器决策过程或者用performance_schema查锁等待和IO事件。四个工具按这个顺序组合能覆盖95%以上的慢查询场景。我个人在实际操作中还有一个习惯把每次优化前后的EXPLAIN结果截图或记录下来特别是key_len、type、rows前后的变化。这套对比数据积累多了你就有了一本自己的慢查询案例库以后再遇到相似的执行计划一眼就能判断问题在哪。最后再分享一个小技巧当EXPLAIN里出现你不认识的Extra值时去官方文档查一次绝对值得。很多Extra值比如Using index for skip scan是MySQL 8.0才有的新能力不认识它就会把它当成异常情况处理白白浪费排查时间。执行计划这东西认识的越多误判越少。
一条SQL从300毫秒变成3秒这种事我遇到过不止一次。遇到这种慢性查询绝大多数人第一反应都是EXPLAIN一把梭但真正把EXPLAIN每个字段都看明白的人却不多。绝大多数情况下我们只看有没有出现ALL、有没有Using filesort其他字段直接忽略。这样用EXPLAIN约等于是靠猜。我写这篇东西的动机很简单把我这些年用EXPLAIN排查慢SQL的经验完整梳理一遍包括每个字段的实际含义、type访问级别的完整阶梯、key_len怎么手算、Extra信息里哪些是坑以及真正的实战排查流程。这篇内容面向的是正在被慢查询折磨的开发者、运维、DBA初学者不管你是什么角色只要能把EXPLAIN吃透定位慢查询的能力会直接上一个台阶。先提醒一句EXPLAIN输出的是执行计划是MySQL基于统计信息和成本模型算出来的预估结果不是真实执行结果。理解这个前提后面所有分析才有意义。1. 一条慢SQL的排查起点EXPLAIN到底在看什么1.1 执行计划是“最优路径预估”不是真实成绩单优化器在选择执行方式时会基于表的统计信息行数、索引基数、数据分布等计算各种执行路径的代价取一个它认为成本最低的方案。这个方案就是EXPLAIN展示的“执行计划”。说白了EXPLAIN就像导航给出的预计到达时间它可能因为地图数据不准确而出现偏差但你行车过程中的任何异常导航都能帮你定位到关键路口。很多人忽略的一点是EXPLAIN不会真正执行查询因此它不会告诉你这个SQL到底跑了多少毫秒也不会告诉你实际扫描了多少行。它只告诉你“优化器觉得该怎么做”。这也是后续第5章要讲的EXPLAIN ANALYZE存在的意义——那是MySQL 8.0提供的真执行、真测量工具。1.2 什么时候必须看执行计划我个人的判断标准是单表查询超过100ms、多表关联超过200ms、接口调用的SQL耗时明显波动、或者线上出现慢查询日志报警时第一件事就是拿EXPLAIN看执行计划。不要先优化代码、不要盲目加缓存先把SQL拿出来单独EXPLAIN一下10秒钟就能知道问题大概在哪。但EXPLAIN也不是万能的它不会主动告诉你“这个索引该建在哪个字段上”你需要通过观察type、key_len、rows这几个字段来反向推理。这就是为什么很多人EXPLAIN完了还是不知道怎么优化——因为他们只会看有没有走索引不会看走了哪个索引、用了索引的哪一部分、预计扫了多少行。1.3 准备一个最小复现环境为了方便后面案例演示我先定义两张表后面所有分析都基于这个环境。CREATE TABLE emu_user ( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, login_name VARCHAR(50) NOT NULL, phone VARCHAR(20) NOT NULL, status TINYINT NOT NULL DEFAULT 1, created_at DATETIME NOT NULL, KEY idx_login_name (login_name), KEY idx_phone_status (phone, status) ) ENGINEInnoDB; CREATE TABLE emu_order ( id BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, order_no VARCHAR(32) NOT NULL, amount DECIMAL(10,2) NOT NULL, status TINYINT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL, KEY idx_user_id (user_id), KEY idx_created_at (created_at) ) ENGINEInnoDB;建完表后插个几十万测试数据然后就可以开始各种EXPLAIN了。2. 核心字段逐个拆解每个数字和单词都有意义2.1 id与select_type看清查询的嵌套结构EXPLAIN输出第一列是id。同一个SELECT语句中的多个表id相同子查询或UNION中的独立SELECTid递增。以前有个说法叫“id越大越先执行”这个说法在大多数场景是对的因为MySQL会先把子查询的结果物化出来再执行外层查询。但注意在JOIN场景下多个id相同的行执行顺序不一定是自上而下优化器可能调整驱动表的顺序。select_type有几种SIMPLE是普通单表查询PRIMARY是最外层SELECTSUBQUERY是出现在WHERE或SELECT列表中的子查询DERIVED是FROM子句中的派生表UNION是UNION中的第二个及后面的SELECTUNION RESULT是从UNION临时表读取结果的阶段。我经常看到有人把DERIVED误认为临时表性能差其实MySQL 8.0已经对派生表做了合并优化很多时候EXPLAIN根本看不到DERIVED。实际排查中如果EXPLAIN结果里有DERIVED和UNION RESULT要多加注意这意味着查询中存在临时表操作。临时表意味着额外的创建和数据拷贝成本特别容易成为慢查询的根源。2.2 type访问类型从ALL到const的完整阶梯type字段是我对一张执行计划的第一判断依据。MySQL官方文档把type分成若干个访问类型从性能差到好大致是这样一条线type值含义代表性场景性能评价ALL全表扫描无索引或优化器认为索引无意义最差必须避免index全索引扫描遍历整个索引树不访问表数据比ALL好点但不代表没问题range索引范围扫描WHERE里用了、、、BETWEEN、IN等可接受ref非唯一索引等值匹配普通索引列 常量良好eq_ref被驱动表通过主键或唯一索引匹配JOIN中驱动表一行只匹配被驱动表一行优秀const主键或唯一索引等值查询WHERE id 1最优system表中只有一行数据系统表极小表最优补充一个容易混淆的点system是const的一种特例只有表中恰好一行时才出现平时基本见不到。还有一个很常见的误判是看到typeindex就以为走了索引万事大吉实际上index代表的是扫描了整个索引树虽然在二级索引上扫描比全表数据扫描要小但如果要回表的话代价依然可能非常高。判断一条SQL是否需要优化的底线range以上range、ref、eq_ref、const通常可以接受index和ALL一定要想办法消除。2.3 possible_keys与key索引的“海选”和“胜出者”possible_keys列显示优化器认为“可能用得上”的索引列表key列显示实际选用的索引。这个组合非常有意思possible_keys为空说明这条SQL的WHERE条件里没有可用的索引字段需要重点关注是不是列类型不匹配、函数包裹列、或者根本没有建索引possible_keys有值但key为空说明优化器遍历了候选索引后认为都不如全表扫描这种情况常见于数据量小或区分度低的场景possible_keys和key都有值但不是同一个说明优化器尝试了多个索引后做了取舍。一个更隐蔽的情况是possible_keys有多个key选了其中一个但实际只用了这个索引的前缀字段。这种情况就要结合第3章的key_len来看了。2.4 rows与filtered优化器心里的“小算盘”rows是优化器预估的需要扫描的行数。注意是预估不是实际值。这个数字受统计信息影响很大如果表长时间没做ANALYZE TABLErows可能是严重偏离的。filtered表示经过WHERE条件过滤后剩余行数占rows的百分比。这两个字段结合起来可以用来估算最终返回的行的规模。我举个直觉化的例子rows100000filtered10说明优化器认为会从10万行中过滤出1万行。如果这个查询还要做GROUP BY或JOIN1万行的中间结果集是非常可观的。反过来看rows很小但filtered很低比如rows10filtered1说明数据过滤能力强大概率走了索引并且索引选择性好。有一个常见的坑rows不准。特别是OR条件、IN列表、函数包裹导致无法准确估算时rows和实际扫描行数可能差出10倍甚至百倍。我在第5章会提到用EXPLAIN ANALYZE校准真实行数。2.5 Extra里藏着的问题Using index、Using where、Using filesort、Using temporaryExtra这一列信息量最大也是很多人忽略的地方。我把日常排查中遇到频率最高的几种列出来并说清楚每个的取舍Using index覆盖索引扫描查询的所有字段都在索引里不需要回表。这是最理想的Extra。看到这个值时可以放心SQL基本没大问题。Using where存储引擎返回数据后服务层还要再做一层WHERE过滤。注意这不代表SQL有问题而是说明有一部分过滤条件无法在索引层面完成。如果索引已经过滤了绝大部分数据Using where是正常的。Using index condition对应索引下推Index Condition PushdownICP。MySQL可以把部分WHERE条件下推到存储引擎层在索引读取时直接过滤。这是5.6以后默认开启的优化InnoDB里很常见是好事。Using filesort文件排序。注意这个“文件”不是真的指磁盘文件只是表示MySQL需要额外的排序操作。它可能是内存排序也可能是磁盘排序。一旦出现这个值意味着你的ORDER BY/GROUP BY没有完全用到索引顺序排序的代价不会小。Using temporary用到了临时表来去重或排序。GROUP BY、DISTINCT、UNION操作经常触发。临时表意味着要创建、写入、读取性能一定会受影响。看到它就值得进一步优化。Using join buffer关联查询中被驱动表没有走索引MySQL只能用join buffer把驱动表的数据缓存起来再逐行匹配。这种情况在业务库中非常典型基本可以断定JOIN字段没有索引或者索引失效了。Extra里如果同时出现Using temporary和Using filesort基本可以断定这条SQL是重灾区必须重写或者补索引。3. type级别与key_len判断索引好坏的硬指标3.1 访问级别的取舍判断我经常跟人讲拿到一条EXPLAIN先看type再看Extra最后看rows和key_len组合。type决定了这条SQL访问数据的方式Extra决定了后处理阶段有没有额外成本。只要type在ref以上且Extra里没有filesort和temporary就算rows看起来有点大通常也不会慢到哪里去。反过来如果type是ALL或者index那么即使Extra看起来很干净你也要警惕。ALL意味着扫描整个表数据量过百万后就是灾难index虽然是在索引树上扫但如果索引树的叶子节点数量庞大而且可能还需要回表性能同样拉胯。3.2 key_len的手动计算方法索引到底吃了“几碗饭”key_len是我想重点展开的字段因为很多优化老手看执行计划主要就看key_len。它表示MySQL实际使用了联合索引中多少个字节的索引长度。通过key_len你能精准判断出这条SQL到底用到了联合索引的第几列。具体计算方法以最常用的utf8mb4字符集为基准列类型允许NULLNOT NULLkey_len计算INT否是4INT是否5BIGINT否是8DATETIME否是5MySQL 8.0.13前8带毫秒时VARCHAR(20)否是20*4282VARCHAR(20)是否20*42183CHAR(20)否是20*480规则其实就几条整型按固定字节数计算INT4BIGINT8DATETIME在8.0里按8字节算更精确的表述是MySQL 5.6以后DATETIME占5字节但如果显式带微秒精度则更多这里我按常见生产库的8字节处理实际值以版本行为为准变长字符串VARCHAR等于字符数乘以字符集最大字节数再加2字节变长长度标记CHAR按声明字符数乘以最大字节数计算任何列允许为NULL字节数加1。举个例子如果要查一张表的联合索引idx_phone_status是(phone VARCHAR(20), status TINYINT)两个字段都不允许NULL那么在utf8mb4下如果EXPLAIN显示key_len84你可以拆解为phone部分20*4282status部分1字节前面有没有多出1字节取决于是否可空这里按不可空就算1合计83实际如果另有1字节说明列定义可能带了额外标记需要结合表结构复核。一般的经验是只要key_len比完整索引长度短就说明只用了联合索引的前缀列key_len等于完整长度说明联合索引用满了。拿适配的场景再说一次idx_phone_status(phone, status)查询条件为WHERE phone123...那么key_len应该是82不含status部分typeref查询条件为WHERE phone123... AND status1key_len就是821可空标记完整长度。通过这个数字的差值你可以判断出联合索引是否被充分利用。3.3 联合索引的最左前缀如何体现在执行计划里联合索引的底层是B树排序规则按索引定义的字段从左到右依次生效。这就意味着只有当WHERE条件中用到了联合索引从最左列开始的连续字段索引才会被完整使用。跳过中间字段直接查后面的字段索引就只能用前缀部分。一个经典的场景联合索引是(a,b,c)查询条件只有WHERE b1 AND c2那么key_len只会显示a字段的索引长度如果a没有出现在条件中甚至可能type直接从ref变成index或ALL。这个现象如果不懂key_len只看type是ref你很可能误以为索引用得很好实际上a列根本没被使用或者优化器做了索引跳跃扫描Index Skip Scan8.0新增能力才能勉强用上后面的列。我一般会建议同事把EXPLAIN里的key_len和一个已知的全长索引做个快速对比差多少字节就能知道哪一列没有被用上。这种方法比肉眼猜SQL要靠谱得多。4. 实战用EXPLAIN揪出三条典型问题SQL4.1 案例A关联查询没走索引全表扫描拖垮接口某次线上有个接口突然变慢SQL长这样SELECT u.login_name, o.order_no, o.amount FROM emu_order o LEFT JOIN emu_user u ON o.user_id u.id WHERE o.created_at 2024-01-01 ORDER BY o.created_at DESC;EXPLAIN结果idselect_typetabletypepossible_keyskeykey_lenrowsExtra1SIMPLEuALLPRIMARYNULLNULL80000NULL1SIMPLEorangeidx_created_atidx_created_at520000Using filesort仔细看驱动表是u居然是全表扫描被驱动表o虽然走了idx_created_at范围查找但最终还有20000行要跟u表匹配。u.id是主键那为什么type不是eq_ref反而是ALL问题出在LEFT JOIN的方向。对LEFT JOIN来说左表是驱动表优化器一开始选择u作为驱动表用o去匹配u时理论上u.id主键是可以被eq_ref命中的但执行计划里u显示为ALL说明优化器因为join buffer或其他统计原因放弃了主键匹配。再往下深挖真正的问题是子表关联字段数据类型不一致或驱动表选择不当。我把查询改成STRAIGHT_JOIN强制u作为被驱动表SELECT u.login_name, o.order_no, o.amount FROM emu_order o STRAIGHT_JOIN emu_user u ON o.user_id u.id WHERE o.created_at 2024-01-01 ORDER BY o.created_at DESC;再次EXPLAINidselect_typetabletypepossible_keyskeykey_lenrowsExtra1SIMPLEorangeidx_created_atidx_created_at520000Using filesort1SIMPLEueq_refPRIMARYPRIMARY41NULL这个结果就健康了o表先范围扫描然后对每一条o记录用主键去u表精确匹配一行回表次数可控类型也变成了eq_ref。通过这个例子我想强调EXPLAIN的关联顺序是对优化器决策的体现如果发现驱动表选错了优先检查字段类型一致性、统计信息是否更新最后再考虑用STRAIGHT_JOIN或者改写SQL来引导执行计划。4.2 案例B索引明明存在却不被使用隐式类型转换有一次排查一个用户查询SQL很简单SELECT * FROM emu_user WHERE phone 13800138000;phone字段上明明有索引idx_phone_status(phone, status)但EXPLAIN显示typeALLpossible_keys是NULLkey也是NULLExtra为Using where。字段有索引却不走十有八九是类型隐式转换。看表结构phone是VARCHAR(20)但查询条件里写的是数字13800138000。MySQL会尝试把字符串类型的列和数字类型做比较发生隐式cast相当于执行了CAST(phone AS SIGNED)。函数包住索引列之后索引的“有序性”就失效了——MySQL没法直接在B树上按函数计算结果定位数据只能全表扫描后逐行做转换和比较。解决方案也简单把SQL改成字符串形式SELECT * FROM emu_user WHERE phone 13800138000;这次EXPLAIN变成typerefpossible_keys和key都是idx_phone_statuskey_len为82Extra里还有一个Using index condition。耗时从数百毫秒降到毫秒级。这个案例提醒我们EXPLAIN看到possible_keys为空不要急着说“没索引”先去核对表结构和SQL里条件字段的数据类型是否匹配。隐式转换是最容易被忽略的索引失效原因没有之一。4.3 案例C排序导致的filesort不仅慢还吃内存再分享一个ORDER BY引发的问题。用户列表接口SQL长这样SELECT id, login_name, phone, status FROM emu_user WHERE status 1 ORDER BY created_at DESC LIMIT 20;EXPLAIN结果如下idselect_typetabletypepossible_keyskeykey_lenrowsExtra1SIMPLEemu_userrefidx_phone_statusidx_phone_status140000Using where; Using filesort表面上看type是ref走了idx_phone_status不算差。但注意还有Using filesort这意味着MySQL要把4万行数据先查出来排完序后再取20行排序的成本远远大于查询本身。问题的根源在于ORDER BY created_at不在索引里而优化器为了使用status过滤牺牲了created_at的排序连续性。什么情况下可以免排序如果创建联合索引(status, created_at)查询条件WHERE status1正好命中status前缀而ORDER BY created_at又是索引的下一列数据在B树中本身就是有序的不需要额外的filesort。改造方案ALTER TABLE emu_user ADD INDEX idx_status_created (status, created_at);再次EXPLAINkey变成idx_status_createdExtra里只剩Using index conditionfilesort消失。一条慢SQL从300ms降到30ms以内这就是执行计划立下的功劳。我在实际项目中反复验证过一个经验凡是看到EXPLAIN结果里带filesort先别急着加排序缓冲区优先想能不能设计一个索引让排序字段天然有序。加缓冲区只是缓解症状索引才是根治。5. EXPLAIN的局限和它的搭档们5.1 为什么rows会和实际扫描行数差很多EXPLAIN的一切结论都是基于优化器的估算而估算依赖表的统计信息。InnoDB的统计信息来自随机采样不是全量精确统计。当数据分布严重不均衡时比如某字段存在大量热点值可能导致rows和filtered的预估失真。一旦发现EXPLAIN的rows与真实扫描行数差异巨大先执行一下ANALYZE TABLE table_name重新搜集统计信息再回头看执行计划。很多“怪异”的慢查询更新完统计信息后自动恢复正常因为这个操作直接影响优化器对执行路径的取舍。5.2 用optimizer trace看优化器的内心戏EXPLAIN只能看到优化器最终选出来的路径但看不到它为什么这么选。MySQL 8.0提供了optimizer trace能输出优化器做决策的全过程包括访问路径候选、成本估算、索引选择原因等。用法很简单SET optimizer_traceenabledon; SELECT * FROM emu_order WHERE user_id 123 AND amount 100; SELECT * FROM information_schema.OPTIMIZER_TRACE; SET optimizer_traceenabledoff;我通常是在EXPLAIN结果看起来“反常”时才用optimizer trace比如优化器明明有更好的索引却选了别的或者因为统计信息偏差导致驱动表选反。它能直接告诉你优化器尝试过哪些索引、各自估算的成本是多少非常上瘾但生产环境慎用enabledon会有额外开销用完立刻关掉。5.3 EXPLAIN ANALYZEMySQL 8.0的真执行、真测量如果你用的是MySQL 8.0.18及以上版本强烈推荐认识EXPLAIN ANALYZE。它和普通EXPLAIN最大的区别是真正把SQL执行了一遍并返回每个步骤的实际耗时、实际扫描行数、实际循环次数。EXPLAIN ANALYZE SELECT u.login_name, o.order_no FROM emu_order o LEFT JOIN emu_user u ON o.user_id u.id WHERE o.created_at 2024-01-01;输出中会包含类似actual time0.5..2.3 rows20000 loops1的信息意思是这个阶段实际耗时2.3毫秒返回2万行只循环了1次。看到actual rows和EXPLAIN预估的rows如果差距很大说明统计信息不准或者优化器的估算模型不适应高基数字段。但要注意EXPLAIN ANALYZE是真的会执行SQL的。在线上大表上对一个复杂查询跑EXPLAIN ANALYZE可能会真实地产生全表扫描、大排序甚至拖垮实例。我的使用习惯是先在测试库用普通EXPLAIN确认大体方案再在低峰期或只读从库上用EXPLAIN ANALYZE校准真实行数和耗时。绝不能拿它在大生产库上乱跑。5.4 profiling与慢查询日志的配合有一类问题是EXPLAIN完全看不出来的比如“锁等待”和“IO抖动”。执行计划再完美锁等100秒也是慢查询。这时候我会用SHOW PROFILE来看整个执行的生命周期或者直接开启慢查询日志配合pt-query-digest做诊断。流程一般是先看慢查询日志找出候选SQL再用EXPLAIN定位执行计划问题然后视情况用optimizer trace或EXPLAIN ANALYZE做深入校准。四者分工不同彼此不能替代。6. 经典问题速查表与避坑心得6.1 常见EXPLAIN结果对照速查下面这个表是我自己整理的基本覆盖了日常慢查询的大部分场景。看到对应的EXPLAIN特征就能直接对应到解决方案。现象可能的根因优先排查方向typeALLpossible_keys为空无索引、隐式类型转换、函数包列核对表结构类型检查SQL条件表达式typeALLpossible_keys有值但key为空索引区分度低或统计信息过期更新统计信息或考虑不建索引改为覆盖索引key_len明显短于完整联合索引长度联合索引前缀命中不足调整索引列顺序或补充查询条件列ExtraUsing temporaryGROUP BY、DISTINCT、UNION等引起临时表检查是否有索引可以覆盖分组排序字段ExtraUsing filesortORDER BY/GROUP BY未走索引顺序建立覆盖排序字段的联合索引ExtraUsing join bufferJOIN字段无索引或字段类型不一致检查关联列索引和数据类型对齐rows很大但typeref非唯一索引返回大量重复值增加筛选条件提高区分度possible_keys和key不一致优化器做了成本对比看统计信息必要时强制指定索引6.2 我踩过的几个坑第一个坑以为走了索引就万事大吉。有一次我优化一条SQLEXPLAIN显示typeref、key_len也正确但是接口依然慢。最后加了一列没有索引的分组字段导致Guo程中途要创建临时表而EXPLAIN里根本没显示出来因为那个临时表是在子查询内部产生的。吃一堑长一智遇到EXPLAIN看不出来的问题就把SQL一层层拆开细看尤其是子查询和派生表部分。第二个坑索引区分度的判断。给状态字段比如status建索引要非常谨慎如果这个字段只有几个离散值1和0区分度极低优化器可能宁愿全表扫描也不走索引。我见过有人给status建了索引但EXPLAIN出来还是ALL于是怀疑索引坏了其实是优化器觉得没价值。判断标准很简单某个值对应的行数占总行数超过15%到20%时索引基本就没意义了。第三个坑关于NULL。MySQL导致索引失效的因素里WHERE phone IS NOT NULL是一种很隐蔽的情况。有些版本的优化器对IS NULL和IS NOT NULL的处理并不一致IS NULL有时候还能用上refIS NOT NULL反而不走索引。如果你的业务允许字段为NULL又需要频繁筛选NULL考虑用默认值替代NULL这也是很多表设计规范里写“不允许NULL”的原因之一。第四个坑EXPLAIN标记为const的查询也可能慢。const代表“仅匹配一行”听起来已经最优了但别忘了还有锁等待、网络传输和连接池排队的时间。EXPLAIN只管执行计划管不了并发环境下的资源竞争。6.3 线上排查EXPLAIN的正确姿势不要在生产环境大表上直接跑EXPLAIN ANALYZE。具体建议是先在从库或测试环境用普通EXPLAIN分析把执行计划优化到typeref/eq_ref级别且无filesort后再在低峰期用EXPLAIN ANALYZE验证一次真实耗时与行数如果仍然无法解释再配合optimizer trace看优化器决策过程或者用performance_schema查锁等待和IO事件。四个工具按这个顺序组合能覆盖95%以上的慢查询场景。我个人在实际操作中还有一个习惯把每次优化前后的EXPLAIN结果截图或记录下来特别是key_len、type、rows前后的变化。这套对比数据积累多了你就有了一本自己的慢查询案例库以后再遇到相似的执行计划一眼就能判断问题在哪。最后再分享一个小技巧当EXPLAIN里出现你不认识的Extra值时去官方文档查一次绝对值得。很多Extra值比如Using index for skip scan是MySQL 8.0才有的新能力不认识它就会把它当成异常情况处理白白浪费排查时间。执行计划这东西认识的越多误判越少。
