拓冰网站定制 
1. 一次凌晨两点的慢查询报警逼我重学EXPLAIN凌晨1点47分我在回家的地铁上收到一条线上告警orders表某条统计接口的SQL执行时间从几十毫秒直接飙到3秒以上。第一反应不是去看业务代码而是登上从库把那条SQL原样捞出来跑了一遍EXPLAIN。这一跑不要紧直接让我重新审视了过去两年对MySQL索引优化的理解。1.1 那条半夜报警的SQL告警SQL大概长这样SELECT id, order_no, user_id, amount, status FROM orders WHERE user_id 4782103 AND status 1 ORDER BY create_time DESC LIMIT 20;orders表当时有接近五百万行user_id上明明建了索引idx_user_id线上也跑了小半年没出问题。可那两天数据量涨到四百多万后突然就慢得离谱。我在从库上执行EXPLAINEXPLAIN SELECT id, order_no, user_id, amount, status FROM orders WHERE user_id 4782103 AND status 1 ORDER BY create_time DESC LIMIT 20\G输出重点如下possible_keys: idx_user_id key: NULL type: ALL rows: 4890000 Extra: Using where; Using filesort索引出现在possible_keys里但key是 NULL说明优化器认为这个索引不可用直接选了全表扫描。接近五百万行数据过一遍再做一个临时排序不慢才怪。1.2 反直觉的结论索引列被套了壳为什么user_id明明有索引却用不上后来查表结构才发现orders.user_id是varchar(20)而接口代码里传的参数是Integer类型。MySQL在做等值比较时会把两边的类型先做统一转换也就是隐式类型转换。当索引列是字符串、比较值是数字时MySQL通常会把索引列转换成数字去比较等于在索引列外面包了一层隐式函数。一旦索引列上出现函数或表达式优化器就无法走常规B树索引了。当时我先临时用引号把常量改成字符串让SQL先止血WHERE user_id 4782103重新跑EXPLAINtype立刻从ALL变成refrows掉到几百行接口耗时回落到15ms左右。这个差异让我深刻意识到一个问题很多慢SQL不是天生就慢而是我们自己写的代码让索引失效了。1.3 我整理出的慢SQL排查流程从那次故障之后我给自己定了一套固定的排查流程在后面的多个项目里一直沿用从慢查询日志把问题SQL完整捞出来别只看监控面板上的SQL片段对问题SQL执行EXPLAIN重点看type、key、rows、Extra四列对照表结构确认索引设计是否和查询条件匹配优先判断是否存在隐式类型转换、函数包裹索引列等低级但致命的场景用加粗的后缀尽量独立分析索引本身设计再决定是改SQL还是改索引优化后必须再跑一次EXPLAIN确认变化有条件的话做AB对比压测。这套流程看起来简单但现实中很多团队连第一步都做不好。很多时候告警都出来了开发同学还在代码里打日志而不是第一时间看执行计划。如果你也遇到过类似情况建议把这套流程直接固化到团队的排查规范里能省掉大量无意义的加班。2. 读懂EXPLAIN输出前先搞清12个字段在说什么很多人一提EXPLAIN就说看是否有走索引这样太粗糙。EXPLAIN的每一列都有自己的意义组合起来才能还原优化器的决策路径。我建议至少把常用字段的语义弄清再谈优化。2.1 一张表说清12个字段以MySQL 8.0为例标准的EXPLAIN输出通常包含下面这些列字段含义重点关注度idSELECT标识符多表连接时用于区分顺序一般select_type查询类型如SIMPLE、PRIMARY、SUBQUERY、UNION等有时table本次访问的表名或别名一般partitions命中的分区低频type访问级别ALL到system的顺序决定扫描代价高possible_keys优化器认为可能用到的索引中key真正选中的索引高key_len实际用到的索引字节长度高ref与索引等值匹配的列或常量中rows优化器估算需要扫描的行数高filtered经过表条件过滤后剩余的百分比中Extra额外信息常见的有Using index、Using filesort等高partitions对分区表才有效filtered在5.7版本之后会结合表统计信息给出更贴近实际的值。平时排查SQL我基本把精力集中在type、key、key_len、rows、Extra上。2.2 key_len被忽略的索引使用程度key_len是很多人不会看的列但它能精确告诉我们联合索引到底用到了哪几个列。比如有个联合索引idx(a, b)a是varchar(100) NOT NULLb是int NOT NULL字符集是utf8mb4。在utf8mb4下varchar(100)理论最大占用100 * 4 400字节由于是变长字段MySQL还会额外记录2字节的长度信息因此a列的key_len 400 2 402int列占4字节。如果EXPLAIN结果显示key_len 402说明优化器只用了联合索引的第一列a如果显示key_len 406说明a和b都用上了。这是一个非常高效的判断方式比盯着possible_keys猜可靠得多。key_len还有一个作用通过不同索引命中的长度判断优化器是否选了一条性价比不高的索引路径。2.3 rows与filtered估算成本的两条腿rows是优化器根据索引统计信息估算出的扫描行数注意它是估算值不是实际值。统计信息基于采样不准确很正常。filtered表示从存储引擎返回的数据中经过WHERE条件过滤后的行数比例。例如rows 10000filtered 10%表示最终参与后续操作的预估行数约为1000行。在分析连接查询时rows和filtered的乘积会作为优化器选择驱动表、选择执行顺序的重要依据。如果你发现某个SQL的rows和实际情况偏差非常大通常是因为表的统计信息太旧可以执行ANALYZE TABLE 表名重新收集统计信息再回头跑EXPLAIN。2.4 ref列匹配用的对象是什么ref列对应的是一种引用关系当前查询的索引列是在和什么值做比较。可能是常量const也可能是某个表的字段比如t2.user_id。如果ref是一个具体字段说明该查询可能通过连接条件与另一张表关联这时候看连接顺序id和table会很有帮助。在单表简单查询中ref通常是const代表直接用常量匹配索引列这是最理想的状态之一。3. type从ALL到ref访问级别才是执行计划里的硬指标type列是整个EXPLAIN输出里最有分量的一个指标它直接反映优化器选择的访问方式。很多面试题喜欢让你把type的等级背出来但实际工作中更需要的是看到某个type时能瞬间判断出当前SQL性能处于什么水平。3.1 type的完整序列MySQL官方文档把type按性能由好到差大致排列为system const eq_ref ref range index ALL我在工作中习惯把它们分成三档优秀档system、const、eq_ref。一般对应主键或唯一索引等值查询、单行表访问。可接受档ref、range。非唯一索引等值匹配或者索引范围扫描。危险档index、ALL。index表示全索引扫描虽然不会回表但仍然遍历了整棵索引树ALL是全表扫描通常意味着大批量数据的读盘操作。我通常要求业务核心链路至少到ref或range事后再考虑能否优化到ref。如果查出ALL一定要给出明确的解释否则上线评审这一关就过不去。3.2 从ALL到range为什么是质变举个直观的数字一张500万行的表假设每行数据在磁盘上分散存放全表扫描大概要扫几万个数据页。而B树索引的高度通常只有3到4层走索引定位数据时可能只需要读几个索引页再读取少量数据页。两者的磁盘IO差距往往在两三个数量级这就是为什么type从ALL变成range后SQL耗时常常从秒级降到毫秒级的原因。常见能走到range的场景包括BETWEEN、、、IN等范围条件。如果范围条件所在的列正好是联合索引中的某一列并且前面有等值条件那么范围扫描会基于等值条件定位后再做范围遍历效率会更高。3.3 明明有索引却走了ALL的常见原因根据我排查过的典型SQLtypeALL的原因通常集中在下面几类隐式类型转换字符串列和数字比较或者日期列和字符串比较索引列被函数包裹比如LEFT(name, 3) abc、DATE_FORMAT(create_time, %Y-%m-%d) 2025-01-01前导模糊查询LIKE %关键字这种查询无法用B树索引加速OR条件中包含了非索引列优化器可能选择放弃索引优化器判断走索引还要回表大量行反而不如全表扫描快表统计信息严重滞后导致优化器选错执行路径。遇到这些情况先排除前四条再考虑是不是统计信息问题。很多时候不是索引不存在而是SQL写法把路堵死了。3.4 逐级优化的检查清单看到ALL的时候我脑子里有一套固定的检查顺序看possible_keys是否为空。如果空说明WHERE条件里根本没有能匹配的索引列。如果有索引但没选中先查隐式类型转换和函数包裹问题。排除上面问题后尝试ANALYZE TABLE刷新统计信息。最后才是考虑新增索引或调整索引结构。如果数据量实在太大且查询需要大范围扫描则要考虑业务侧拆分或改成本地缓存方案。这套顺序帮我避免了很多盲目加索引的迷惑行为。4. 真正决定性能的Extra信号filesort和temporary背后的代价Extra列隐藏着优化器在索引扫描之外额外做了什么工作。两个最常见的成本信号是Using filesort和Using temporary。它们不直接体现在type里但往往才是SQL变慢的主要元凶。4.1 Using filesort排序不一定在磁盘但肯定不便宜Using filesort的意思是MySQL需要额外对结果集进行排序。注意它并不代表一定写磁盘了如果数据量在sort_buffer_size范围内过程在内存里完成数据量大时才会在磁盘上走归并排序流程。无论哪种情况这都意味着查询在索引扫描之外又叠加了一个排序阶段。优化排序的通用思路是让索引顺序和ORDER BY顺序保持一致。例如SELECT id, order_no FROM orders WHERE user_id 4782103 ORDER BY create_time DESC;如果存在联合索引(user_id, create_time)那么优化器定位到user_id之后直接沿着索引的create_time顺序读取即可不再需要额外排序。这里要特别提醒MySQL 8.0支持降序索引如果你的业务经常需要ORDER BY create_time DESC可以在建索引时显式声明create_time DESC让索引顺序和查询方向完全匹配。4.2 Using temporary临时表可能比排序更危险Using temporary常见于GROUP BY、DISTINCT、子查询等场景。优化器可能需要创建临时表来处理中间结果临时表还分内存临时表和磁盘临时表。一旦数据量超过tmp_table_size或内存临时表的限制临时表会落盘性能会断崖式下降。优化临时表的思路通常有两条一是让分组或去重使用的列被索引覆盖这样数据本身有序无需临时表排序分组二是重构SQL比如把复杂子查询改成JOIN或者把多阶段GROUP BY拆成两个查询在业务层合并结果。4.3 Using index与Using index condition的差别Using index是覆盖索引标志表示所需列都能直接从索引树中获得不需要回表。这是最理想的情况。Using index condition对应的是索引下推Index Condition PushdownMySQL 5.6之后才出现。它表示存储引擎在遍历索引时先用索引中已有的字段做一次过滤减少回表次数。比如联合索引(name, age)查询条件是name LIKE 张% AND age 20在ICP引入前需要把满足name条件的所有记录都回表再过滤age有了ICP可以在引擎层直接对索引中的age做判断减少回表的数据量。很多人看到Using index condition就以为快实际上它已经做了优化但不算最优。如果能把age做等值匹配的列放到联合索引更靠前的位置甚至做成完全覆盖查询列的索引结果会更好。4.4 关注信号组合Using filesort和typeALL同时出现时基本意味着灾难比如第1章的报警SQL就是全表扫描外加排序。Using temporary伴随连接查询出现时一定要看看驱动表的规模必要时调整关联字段的索引。Using where隔离出现时说明部分过滤条件被放到了存储引擎返回结果之后执行这时候可以观察filtered值判断还有多少行在server层被过滤掉。5. 索引设计实战B树、最左前缀和回表的取舍逻辑很多优化问题到最后都要回到索引设计本身。索引不是越多越好也不是随便把WHERE条件里的列都建一遍就算完。理解InnoDB底层的数据结构才能解释为什么联合索引默认要遵循最左前缀为什么覆盖索引能省掉回表。5.1 为什么选B树而不是跳表或哈希哈希索引适合等值查询但范围查询无能为力。跳表能做范围查询但局部性不如B树好并且InnoDB是按页读写的B树的页节点天然适合磁盘IO。B树的叶子节点用双向链表串联范围扫描时从一个叶子节点开始顺着链表往下读就可以了。二级索引的叶子节点存的是主键值而不是整行记录。这意味着通过二级索引查询时如果需要的列不在索引中就必须根据叶子节点里的主键再到聚簇索引中取回完整记录这就是回表。5.2 最左前缀原则是物理结构决定的我见过太多人把最左前缀当作面试概念背但实际设计索引时照犯错误。联合索引(a, b, c)在B树里的排序规则是先按a排a相同的按b排a、b都相同的再按c排。所以查询条件只有b时数据整体并不按b有序索引自然用不上条件只有a和时间范围时a可以用等值定位时间范围可以继续沿索引顺序扫描。还有一点要记住范围条件之后的索引列会失效。比如WHERE a 1 AND b 10 AND c 2优化器能用a做等值定位用b做范围扫描但c就没法走索引了。5.3 回表和覆盖索引的账回表意味着一次主键随机访问。当二级索引命中大量行时回表成本会急剧增加甚至让优化器干脆放弃二级索引。解决之道是覆盖索引把查询需要的字段全部塞进同一个索引。比如常见查询SELECT order_no, amount, status FROM orders WHERE user_id 4782103 AND status 1;如果存在联合索引(user_id, status, order_no, amount)查询只需要从索引树里直接读取这三列根本不需要回表。Extra会显示Using index。但这个方案的代价是索引占用的空间变大插入和更新的成本也会上升所以要平衡业务查询频率和维护开销。5.4 联合索引列顺序的实用公式我比较喜欢用下面这个顺序来设计联合索引等值条件列 → 排序字段 → 范围条件列等值条件列适合放最前面因为可以直接定位排序字段放在等值条件之后能让数据天然有序消除filesort范围条件列尽量放最后避免它使得后续列的索引失效。另外注意不要盲目添加冗余索引。(a, b, c)已经能覆盖(a)单独作为索引的场景不需要额外再建(a)。每次给表加索引都要评估写入性能的损失尤其在大表上索引数量多了之后写入放大和锁竞争都会飙升。6. 三个典型慢SQL的重构实录每一行都要有依据理论说再多不如看几个完整案例。下面三个案例都来自我实际经手的项目出于脱敏考虑字段名和表结构调整过但问题逻辑完全一致。6.1 案例一隐式类型转换让500万行全表扫描问题SQLSELECT id, order_no, amount FROM orders WHERE user_id 4782103 ORDER BY create_time DESC;表结构里user_id是varchar(20)查询条件是整数常量。EXPLAIN输出type: ALL key: NULL rows: 4850000 Extra: Using where; Using filesort优化方式是把常量改成字符串写法WHERE user_id 4782103改完立刻重跑EXPLAINtype: ref key: idx_user_id rows: 412 Extra: Using filesort全表扫描消失行数估算从485万降到412行filesort还在但排序数据量已经天差地别。这个案例的关键启示是先查字段类型再谈索引优化。很多问题改SQL写法就够了不需要动索引。6.2 案例二ORDER BY触发的filesort靠联合索引消除接上面案例虽然type变成ref但Extra里仍有Using filesort。原因是idx_user_id只包含user_id这一列对create_time排序只能额外操作。优化方案是新增联合索引ALTER TABLE orders ADD INDEX idx_user_create_time (user_id, create_time DESC);优化后EXPLAINtype: ref key: idx_user_create_time key_len: 32 rows: 412 Extra: (空)Using filesort消失索引顺序直接提供了create_time DESC的排好序的结果。这个案例说明当WHERE和ORDER BY同时存在时不要各看各的要用一个联合索引同时覆盖两者。6.3 案例三SELECT *把覆盖索引浪费了某次在订单明细表order_items上排查慢查询SQL是SELECT * FROM order_items WHERE order_id 202501010001 AND product_id IN (1001, 1002, 1003);表有索引(order_id, product_id)EXPLAIN显示type: rangerows也不大但接口耗时就是降不下来。原因是业务表有40多个字段SQL用了SELECT *十几个字段都要回表读取造成大量随机IO。优化分两步一是业务侧只保留真正需要的字段把SELECT *改成SELECT order_id, product_id, price, quantity FROM order_items WHERE order_id 202501010001 AND product_id IN (1001, 1002, 1003);二是把索引扩展为覆盖索引ALTER TABLE order_items ADD INDEX idx_query (order_id, product_id, price, quantity);优化后Extra显示Using index零回表。这个案例告诉我们覆盖索引不仅帮助定位数据还决定了查询是否要回表。你把该覆盖的列覆盖全了性能自然不一样。6.4 优化前后的效果对比案例优化前关键信息优化后关键信息耗时变化隐式类型转换typeALL, rows485万typeref, rows4123.2s → 20msORDER BY排序typeref, Extrafilesorttyperef, Extra为空25ms → 3msSELECT *回表typerange, 大量回表typerange, ExtraUsing index180ms → 18ms三个案例说明EXPLAIN的每一列都不是孤立的。type解决的是怎么找Extra解决的是找到之后还要干什么而索引结构决定了这两步是否都能高效完成。7. 日常巡检与索引维护经验让EXPLAIN成为常态工具优化不是救火而是日常。我现在的做法是把慢查询分析做成持续机制而不是等人报警。7.1 先把慢查询日志打开如果线上环境还没开启慢查询日志以下配置建议立刻做起来SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL log_queries_not_using_indexes ON;long_query_time 1表示超过1秒的SQL都会被记进日志log_queries_not_using_indexes可以额外记下那些全表扫描的隐藏炸弹。当然慢日志本身对性能有一点开销大促前记得评估是否临时关闭。日志捞出来之后我习惯用pt-query-digest做聚合分析找出出现次数多、总耗时高的SQL模板再逐条EXPLAIN。7.2 别把EXPLAIN当圣旨EXPLAIN给出的是基于统计信息的预估rows不精确是常态。如果你想知道真实执行数据MySQL 8.0.18之后的版本提供了EXPLAIN ANALYZE可以直接输出每步操作的实际耗时、实际行数和循环次数。例如EXPLAIN ANALYZE SELECT id, order_no, amount FROM orders WHERE user_id 4782103;输出会包含类似(actual time0.23...0.35 rows412 loops1)的信息。在压测或线上大促前用EXPLAIN ANALYZE做校准很有必要但注意它是真正执行SQL的线上大查询就不要随意跑。7.3 索引是资产也是负债每多一个索引写入时就要多维护一棵B树大表上INSERT、UPDATE、DELETE的代价都会上升还可能引入更多锁竞争。因此我建议定期做索引审查找出从未被key列使用过的索引考虑删除判断是否存在冗余索引比如已经有了(a, b, c)又单独建了(a)关注索引列区分度区分度低的列单独建索引往往没有意义针对高频更新表减少不必要索引保留真正承载业务查询的索引。删索引之前先观察一段时间保留对应查询的调用量和耗时证据不要凭感觉动。7.4 我后期常用的心法清单每一条SQL落库前都先跑EXPLAIN形成习惯而不是评审时才查联合索引设计遵循等值列 → 排序列 → 范围列的顺序遇到typeALL先怀疑隐式类型转换和函数包裹遇到Using filesort优先看成是缺少合适联合索引的信号能用覆盖索引解决的问题别让业务层做多余回表优化一段周期后重新压测并记录对比数据而不是凭感觉说变快了。这套心法不是什么高深理论但帮我解决了不少团队里的玄学慢查询。说真的绝大多数SQL性能问题都轮不到去调参数EXPLAIN看完答案通常就摆在那里。你在日常排查里也可以试着把这些习惯落地让每个慢SQL都有据可查不再靠猜。 MySQL慢查询优化：用EXPLAIN看懂索引失效与执行计划
1. 一次凌晨两点的慢查询报警逼我重学EXPLAIN凌晨1点47分我在回家的地铁上收到一条线上告警orders表某条统计接口的SQL执行时间从几十毫秒直接飙到3秒以上。第一反应不是去看业务代码而是登上从库把那条SQL原样捞出来跑了一遍EXPLAIN。这一跑不要紧直接让我重新审视了过去两年对MySQL索引优化的理解。1.1 那条半夜报警的SQL告警SQL大概长这样SELECT id, order_no, user_id, amount, status FROM orders WHERE user_id 4782103 AND status 1 ORDER BY create_time DESC LIMIT 20;orders表当时有接近五百万行user_id上明明建了索引idx_user_id线上也跑了小半年没出问题。可那两天数据量涨到四百多万后突然就慢得离谱。我在从库上执行EXPLAINEXPLAIN SELECT id, order_no, user_id, amount, status FROM orders WHERE user_id 4782103 AND status 1 ORDER BY create_time DESC LIMIT 20\G输出重点如下possible_keys: idx_user_id key: NULL type: ALL rows: 4890000 Extra: Using where; Using filesort索引出现在possible_keys里但key是 NULL说明优化器认为这个索引不可用直接选了全表扫描。接近五百万行数据过一遍再做一个临时排序不慢才怪。1.2 反直觉的结论索引列被套了壳为什么user_id明明有索引却用不上后来查表结构才发现orders.user_id是varchar(20)而接口代码里传的参数是Integer类型。MySQL在做等值比较时会把两边的类型先做统一转换也就是隐式类型转换。当索引列是字符串、比较值是数字时MySQL通常会把索引列转换成数字去比较等于在索引列外面包了一层隐式函数。一旦索引列上出现函数或表达式优化器就无法走常规B树索引了。当时我先临时用引号把常量改成字符串让SQL先止血WHERE user_id 4782103重新跑EXPLAINtype立刻从ALL变成refrows掉到几百行接口耗时回落到15ms左右。这个差异让我深刻意识到一个问题很多慢SQL不是天生就慢而是我们自己写的代码让索引失效了。1.3 我整理出的慢SQL排查流程从那次故障之后我给自己定了一套固定的排查流程在后面的多个项目里一直沿用从慢查询日志把问题SQL完整捞出来别只看监控面板上的SQL片段对问题SQL执行EXPLAIN重点看type、key、rows、Extra四列对照表结构确认索引设计是否和查询条件匹配优先判断是否存在隐式类型转换、函数包裹索引列等低级但致命的场景用加粗的后缀尽量独立分析索引本身设计再决定是改SQL还是改索引优化后必须再跑一次EXPLAIN确认变化有条件的话做AB对比压测。这套流程看起来简单但现实中很多团队连第一步都做不好。很多时候告警都出来了开发同学还在代码里打日志而不是第一时间看执行计划。如果你也遇到过类似情况建议把这套流程直接固化到团队的排查规范里能省掉大量无意义的加班。2. 读懂EXPLAIN输出前先搞清12个字段在说什么很多人一提EXPLAIN就说看是否有走索引这样太粗糙。EXPLAIN的每一列都有自己的意义组合起来才能还原优化器的决策路径。我建议至少把常用字段的语义弄清再谈优化。2.1 一张表说清12个字段以MySQL 8.0为例标准的EXPLAIN输出通常包含下面这些列字段含义重点关注度idSELECT标识符多表连接时用于区分顺序一般select_type查询类型如SIMPLE、PRIMARY、SUBQUERY、UNION等有时table本次访问的表名或别名一般partitions命中的分区低频type访问级别ALL到system的顺序决定扫描代价高possible_keys优化器认为可能用到的索引中key真正选中的索引高key_len实际用到的索引字节长度高ref与索引等值匹配的列或常量中rows优化器估算需要扫描的行数高filtered经过表条件过滤后剩余的百分比中Extra额外信息常见的有Using index、Using filesort等高partitions对分区表才有效filtered在5.7版本之后会结合表统计信息给出更贴近实际的值。平时排查SQL我基本把精力集中在type、key、key_len、rows、Extra上。2.2 key_len被忽略的索引使用程度key_len是很多人不会看的列但它能精确告诉我们联合索引到底用到了哪几个列。比如有个联合索引idx(a, b)a是varchar(100) NOT NULLb是int NOT NULL字符集是utf8mb4。在utf8mb4下varchar(100)理论最大占用100 * 4 400字节由于是变长字段MySQL还会额外记录2字节的长度信息因此a列的key_len 400 2 402int列占4字节。如果EXPLAIN结果显示key_len 402说明优化器只用了联合索引的第一列a如果显示key_len 406说明a和b都用上了。这是一个非常高效的判断方式比盯着possible_keys猜可靠得多。key_len还有一个作用通过不同索引命中的长度判断优化器是否选了一条性价比不高的索引路径。2.3 rows与filtered估算成本的两条腿rows是优化器根据索引统计信息估算出的扫描行数注意它是估算值不是实际值。统计信息基于采样不准确很正常。filtered表示从存储引擎返回的数据中经过WHERE条件过滤后的行数比例。例如rows 10000filtered 10%表示最终参与后续操作的预估行数约为1000行。在分析连接查询时rows和filtered的乘积会作为优化器选择驱动表、选择执行顺序的重要依据。如果你发现某个SQL的rows和实际情况偏差非常大通常是因为表的统计信息太旧可以执行ANALYZE TABLE 表名重新收集统计信息再回头跑EXPLAIN。2.4 ref列匹配用的对象是什么ref列对应的是一种引用关系当前查询的索引列是在和什么值做比较。可能是常量const也可能是某个表的字段比如t2.user_id。如果ref是一个具体字段说明该查询可能通过连接条件与另一张表关联这时候看连接顺序id和table会很有帮助。在单表简单查询中ref通常是const代表直接用常量匹配索引列这是最理想的状态之一。3. type从ALL到ref访问级别才是执行计划里的硬指标type列是整个EXPLAIN输出里最有分量的一个指标它直接反映优化器选择的访问方式。很多面试题喜欢让你把type的等级背出来但实际工作中更需要的是看到某个type时能瞬间判断出当前SQL性能处于什么水平。3.1 type的完整序列MySQL官方文档把type按性能由好到差大致排列为system const eq_ref ref range index ALL我在工作中习惯把它们分成三档优秀档system、const、eq_ref。一般对应主键或唯一索引等值查询、单行表访问。可接受档ref、range。非唯一索引等值匹配或者索引范围扫描。危险档index、ALL。index表示全索引扫描虽然不会回表但仍然遍历了整棵索引树ALL是全表扫描通常意味着大批量数据的读盘操作。我通常要求业务核心链路至少到ref或range事后再考虑能否优化到ref。如果查出ALL一定要给出明确的解释否则上线评审这一关就过不去。3.2 从ALL到range为什么是质变举个直观的数字一张500万行的表假设每行数据在磁盘上分散存放全表扫描大概要扫几万个数据页。而B树索引的高度通常只有3到4层走索引定位数据时可能只需要读几个索引页再读取少量数据页。两者的磁盘IO差距往往在两三个数量级这就是为什么type从ALL变成range后SQL耗时常常从秒级降到毫秒级的原因。常见能走到range的场景包括BETWEEN、、、IN等范围条件。如果范围条件所在的列正好是联合索引中的某一列并且前面有等值条件那么范围扫描会基于等值条件定位后再做范围遍历效率会更高。3.3 明明有索引却走了ALL的常见原因根据我排查过的典型SQLtypeALL的原因通常集中在下面几类隐式类型转换字符串列和数字比较或者日期列和字符串比较索引列被函数包裹比如LEFT(name, 3) abc、DATE_FORMAT(create_time, %Y-%m-%d) 2025-01-01前导模糊查询LIKE %关键字这种查询无法用B树索引加速OR条件中包含了非索引列优化器可能选择放弃索引优化器判断走索引还要回表大量行反而不如全表扫描快表统计信息严重滞后导致优化器选错执行路径。遇到这些情况先排除前四条再考虑是不是统计信息问题。很多时候不是索引不存在而是SQL写法把路堵死了。3.4 逐级优化的检查清单看到ALL的时候我脑子里有一套固定的检查顺序看possible_keys是否为空。如果空说明WHERE条件里根本没有能匹配的索引列。如果有索引但没选中先查隐式类型转换和函数包裹问题。排除上面问题后尝试ANALYZE TABLE刷新统计信息。最后才是考虑新增索引或调整索引结构。如果数据量实在太大且查询需要大范围扫描则要考虑业务侧拆分或改成本地缓存方案。这套顺序帮我避免了很多盲目加索引的迷惑行为。4. 真正决定性能的Extra信号filesort和temporary背后的代价Extra列隐藏着优化器在索引扫描之外额外做了什么工作。两个最常见的成本信号是Using filesort和Using temporary。它们不直接体现在type里但往往才是SQL变慢的主要元凶。4.1 Using filesort排序不一定在磁盘但肯定不便宜Using filesort的意思是MySQL需要额外对结果集进行排序。注意它并不代表一定写磁盘了如果数据量在sort_buffer_size范围内过程在内存里完成数据量大时才会在磁盘上走归并排序流程。无论哪种情况这都意味着查询在索引扫描之外又叠加了一个排序阶段。优化排序的通用思路是让索引顺序和ORDER BY顺序保持一致。例如SELECT id, order_no FROM orders WHERE user_id 4782103 ORDER BY create_time DESC;如果存在联合索引(user_id, create_time)那么优化器定位到user_id之后直接沿着索引的create_time顺序读取即可不再需要额外排序。这里要特别提醒MySQL 8.0支持降序索引如果你的业务经常需要ORDER BY create_time DESC可以在建索引时显式声明create_time DESC让索引顺序和查询方向完全匹配。4.2 Using temporary临时表可能比排序更危险Using temporary常见于GROUP BY、DISTINCT、子查询等场景。优化器可能需要创建临时表来处理中间结果临时表还分内存临时表和磁盘临时表。一旦数据量超过tmp_table_size或内存临时表的限制临时表会落盘性能会断崖式下降。优化临时表的思路通常有两条一是让分组或去重使用的列被索引覆盖这样数据本身有序无需临时表排序分组二是重构SQL比如把复杂子查询改成JOIN或者把多阶段GROUP BY拆成两个查询在业务层合并结果。4.3 Using index与Using index condition的差别Using index是覆盖索引标志表示所需列都能直接从索引树中获得不需要回表。这是最理想的情况。Using index condition对应的是索引下推Index Condition PushdownMySQL 5.6之后才出现。它表示存储引擎在遍历索引时先用索引中已有的字段做一次过滤减少回表次数。比如联合索引(name, age)查询条件是name LIKE 张% AND age 20在ICP引入前需要把满足name条件的所有记录都回表再过滤age有了ICP可以在引擎层直接对索引中的age做判断减少回表的数据量。很多人看到Using index condition就以为快实际上它已经做了优化但不算最优。如果能把age做等值匹配的列放到联合索引更靠前的位置甚至做成完全覆盖查询列的索引结果会更好。4.4 关注信号组合Using filesort和typeALL同时出现时基本意味着灾难比如第1章的报警SQL就是全表扫描外加排序。Using temporary伴随连接查询出现时一定要看看驱动表的规模必要时调整关联字段的索引。Using where隔离出现时说明部分过滤条件被放到了存储引擎返回结果之后执行这时候可以观察filtered值判断还有多少行在server层被过滤掉。5. 索引设计实战B树、最左前缀和回表的取舍逻辑很多优化问题到最后都要回到索引设计本身。索引不是越多越好也不是随便把WHERE条件里的列都建一遍就算完。理解InnoDB底层的数据结构才能解释为什么联合索引默认要遵循最左前缀为什么覆盖索引能省掉回表。5.1 为什么选B树而不是跳表或哈希哈希索引适合等值查询但范围查询无能为力。跳表能做范围查询但局部性不如B树好并且InnoDB是按页读写的B树的页节点天然适合磁盘IO。B树的叶子节点用双向链表串联范围扫描时从一个叶子节点开始顺着链表往下读就可以了。二级索引的叶子节点存的是主键值而不是整行记录。这意味着通过二级索引查询时如果需要的列不在索引中就必须根据叶子节点里的主键再到聚簇索引中取回完整记录这就是回表。5.2 最左前缀原则是物理结构决定的我见过太多人把最左前缀当作面试概念背但实际设计索引时照犯错误。联合索引(a, b, c)在B树里的排序规则是先按a排a相同的按b排a、b都相同的再按c排。所以查询条件只有b时数据整体并不按b有序索引自然用不上条件只有a和时间范围时a可以用等值定位时间范围可以继续沿索引顺序扫描。还有一点要记住范围条件之后的索引列会失效。比如WHERE a 1 AND b 10 AND c 2优化器能用a做等值定位用b做范围扫描但c就没法走索引了。5.3 回表和覆盖索引的账回表意味着一次主键随机访问。当二级索引命中大量行时回表成本会急剧增加甚至让优化器干脆放弃二级索引。解决之道是覆盖索引把查询需要的字段全部塞进同一个索引。比如常见查询SELECT order_no, amount, status FROM orders WHERE user_id 4782103 AND status 1;如果存在联合索引(user_id, status, order_no, amount)查询只需要从索引树里直接读取这三列根本不需要回表。Extra会显示Using index。但这个方案的代价是索引占用的空间变大插入和更新的成本也会上升所以要平衡业务查询频率和维护开销。5.4 联合索引列顺序的实用公式我比较喜欢用下面这个顺序来设计联合索引等值条件列 → 排序字段 → 范围条件列等值条件列适合放最前面因为可以直接定位排序字段放在等值条件之后能让数据天然有序消除filesort范围条件列尽量放最后避免它使得后续列的索引失效。另外注意不要盲目添加冗余索引。(a, b, c)已经能覆盖(a)单独作为索引的场景不需要额外再建(a)。每次给表加索引都要评估写入性能的损失尤其在大表上索引数量多了之后写入放大和锁竞争都会飙升。6. 三个典型慢SQL的重构实录每一行都要有依据理论说再多不如看几个完整案例。下面三个案例都来自我实际经手的项目出于脱敏考虑字段名和表结构调整过但问题逻辑完全一致。6.1 案例一隐式类型转换让500万行全表扫描问题SQLSELECT id, order_no, amount FROM orders WHERE user_id 4782103 ORDER BY create_time DESC;表结构里user_id是varchar(20)查询条件是整数常量。EXPLAIN输出type: ALL key: NULL rows: 4850000 Extra: Using where; Using filesort优化方式是把常量改成字符串写法WHERE user_id 4782103改完立刻重跑EXPLAINtype: ref key: idx_user_id rows: 412 Extra: Using filesort全表扫描消失行数估算从485万降到412行filesort还在但排序数据量已经天差地别。这个案例的关键启示是先查字段类型再谈索引优化。很多问题改SQL写法就够了不需要动索引。6.2 案例二ORDER BY触发的filesort靠联合索引消除接上面案例虽然type变成ref但Extra里仍有Using filesort。原因是idx_user_id只包含user_id这一列对create_time排序只能额外操作。优化方案是新增联合索引ALTER TABLE orders ADD INDEX idx_user_create_time (user_id, create_time DESC);优化后EXPLAINtype: ref key: idx_user_create_time key_len: 32 rows: 412 Extra: (空)Using filesort消失索引顺序直接提供了create_time DESC的排好序的结果。这个案例说明当WHERE和ORDER BY同时存在时不要各看各的要用一个联合索引同时覆盖两者。6.3 案例三SELECT *把覆盖索引浪费了某次在订单明细表order_items上排查慢查询SQL是SELECT * FROM order_items WHERE order_id 202501010001 AND product_id IN (1001, 1002, 1003);表有索引(order_id, product_id)EXPLAIN显示type: rangerows也不大但接口耗时就是降不下来。原因是业务表有40多个字段SQL用了SELECT *十几个字段都要回表读取造成大量随机IO。优化分两步一是业务侧只保留真正需要的字段把SELECT *改成SELECT order_id, product_id, price, quantity FROM order_items WHERE order_id 202501010001 AND product_id IN (1001, 1002, 1003);二是把索引扩展为覆盖索引ALTER TABLE order_items ADD INDEX idx_query (order_id, product_id, price, quantity);优化后Extra显示Using index零回表。这个案例告诉我们覆盖索引不仅帮助定位数据还决定了查询是否要回表。你把该覆盖的列覆盖全了性能自然不一样。6.4 优化前后的效果对比案例优化前关键信息优化后关键信息耗时变化隐式类型转换typeALL, rows485万typeref, rows4123.2s → 20msORDER BY排序typeref, Extrafilesorttyperef, Extra为空25ms → 3msSELECT *回表typerange, 大量回表typerange, ExtraUsing index180ms → 18ms三个案例说明EXPLAIN的每一列都不是孤立的。type解决的是怎么找Extra解决的是找到之后还要干什么而索引结构决定了这两步是否都能高效完成。7. 日常巡检与索引维护经验让EXPLAIN成为常态工具优化不是救火而是日常。我现在的做法是把慢查询分析做成持续机制而不是等人报警。7.1 先把慢查询日志打开如果线上环境还没开启慢查询日志以下配置建议立刻做起来SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL log_queries_not_using_indexes ON;long_query_time 1表示超过1秒的SQL都会被记进日志log_queries_not_using_indexes可以额外记下那些全表扫描的隐藏炸弹。当然慢日志本身对性能有一点开销大促前记得评估是否临时关闭。日志捞出来之后我习惯用pt-query-digest做聚合分析找出出现次数多、总耗时高的SQL模板再逐条EXPLAIN。7.2 别把EXPLAIN当圣旨EXPLAIN给出的是基于统计信息的预估rows不精确是常态。如果你想知道真实执行数据MySQL 8.0.18之后的版本提供了EXPLAIN ANALYZE可以直接输出每步操作的实际耗时、实际行数和循环次数。例如EXPLAIN ANALYZE SELECT id, order_no, amount FROM orders WHERE user_id 4782103;输出会包含类似(actual time0.23...0.35 rows412 loops1)的信息。在压测或线上大促前用EXPLAIN ANALYZE做校准很有必要但注意它是真正执行SQL的线上大查询就不要随意跑。7.3 索引是资产也是负债每多一个索引写入时就要多维护一棵B树大表上INSERT、UPDATE、DELETE的代价都会上升还可能引入更多锁竞争。因此我建议定期做索引审查找出从未被key列使用过的索引考虑删除判断是否存在冗余索引比如已经有了(a, b, c)又单独建了(a)关注索引列区分度区分度低的列单独建索引往往没有意义针对高频更新表减少不必要索引保留真正承载业务查询的索引。删索引之前先观察一段时间保留对应查询的调用量和耗时证据不要凭感觉动。7.4 我后期常用的心法清单每一条SQL落库前都先跑EXPLAIN形成习惯而不是评审时才查联合索引设计遵循等值列 → 排序列 → 范围列的顺序遇到typeALL先怀疑隐式类型转换和函数包裹遇到Using filesort优先看成是缺少合适联合索引的信号能用覆盖索引解决的问题别让业务层做多余回表优化一段周期后重新压测并记录对比数据而不是凭感觉说变快了。这套心法不是什么高深理论但帮我解决了不少团队里的玄学慢查询。说真的绝大多数SQL性能问题都轮不到去调参数EXPLAIN看完答案通常就摆在那里。你在日常排查里也可以试着把这些习惯落地让每个慢SQL都有据可查不再靠猜。