写SQL的时候最怕什么不是语法报错而是查询结果是对的但数据库负载一天比一天高。有一次我凌晨被拉起来处理问题一张不到三千万行的订单表查当天数据要跑四十秒接口直接超时。我第一反应不是改业务逻辑而是先跑了EXPLAIN。结果发现查询在扫全表优化器连一个像样的候选索引都没用上。后面调整了查询条件重新设计联合索引接口耗时回到几十毫秒。这篇内容主要围绕MySQL Explain详解与索引优化实战展开会从执行计划的每一列到底是什么意思开始讲到索引命中、失效、联合索引怎么设计再给一个完整慢SQL排查流程。适合正在准备MySQL面试题的后端开发也适合被慢查询折磨过但还没系统梳理过执行计划的DBA或运维。无论你是第一次打开EXPLAIN输出还是已经会看rows但不知道下一步怎么改索引这篇文章都能给你一套可执行的思路。1. 执行计划是什么为什么每个MySQL开发都要懂1.1 没有EXPLAIN时我踩过的那些坑早期我写过不少“看上去没问题”的SQL比如在订单表上判断状态和支付方式再加一个按创建时间倒序的排序。业务规模小的时候一切都好等到单表几百万行之后查询一次比一次慢。当时我的排查方式很原始先给所有可能在WHERE里面出现的字段建单列索引然后一个个试。这种盲目的做法带来了两个问题。一是索引建了一大堆但SQL实际执行时根本没用上。比如我建了status、pay_type、created_at三个独立索引MySQL优化器在单次查询里通常只会选其中一个另外两个索引完全是冗余的。二是排序还是慢因为排序需要把结果集加载到临时文件或者走filesort索引没覆盖到排序字段EXPLAIN里就会出现明显的Using filesort这个标记比全表扫描还让人头疼。后来我养成了习惯任何SQL上线之前先看执行计划。EXPLAIN不是万能的但它能让“猜测”变成“有依据的判断”。尤其是面对慢SQL执行计划会清清楚楚告诉你这条语句是先查了哪张表用了哪个索引大致扫了多少行是否要做临时表和文件排序。知道这些信息后加索引才不是碰运气。1.2 EXPLAIN到底能告诉我们什么EXPLAIN是MySQL提供的一条诊断命令用法很简单在SELECT前加EXPLAIN四个字就行。MySQL 8.0还可以用EXPLAIN FORMATTREE查看更结构化的输出或者用EXPLAIN ANALYZE拿到真实执行时间。我先用最传统的表格输出来说因为它是所有版本通用、也是大家看得最多的格式。执行计划的输出是一张多列的表每一行代表一个执行步骤。关键信息可以分成几组。查询结构相关id、select_type、table解决“这条SQL被拆成了哪些步骤”。访问路径相关type、possible_keys、key、key_len、ref解决“每一步到底怎么从表里取数据”。成本预估相关rows、filtered解决“这一步大概会扫多少行、筛选后还剩多少”。额外行为相关Extra解决“有没有走索引下推、临时表、文件排序、覆盖索引”。举个例子EXPLAIN SELECT id, order_no, total_amount FROM orders WHERE status 1 AND created_at 2024-04-01 ORDER BY created_at DESC LIMIT 20;输出大概长这样------------------------------------------------------------------------------------------------------------ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ------------------------------------------------------------------------------------------------------------ | 1 | SIMPLE | orders | range | idx_status | status | 1 | NULL | 198760 | Using index condition; Using filesort | ------------------------------------------------------------------------------------------------------------这里能看出的问题是虽然用了索引idx_status但rows预估还有20万行并且出现了Using filesort。说明单列索引把status过滤掉了但created_at的范围筛选和排序都没有被索引很好地支撑。看到这个输出下一步优化方向就很明确了要么调整索引让范围条件和排序都能走索引要么改写SQL减少筛选范围。2. 读懂Explain输出从id到Extra逐列拆解2.1 id、select_type查询怎么拆、怎么合并EXPLAIN结果里最左侧的id用来标识查询中每个SELECT的执行顺序。id相同的行MySQL认为它们是同一个查询里的多个表会按照从上到下的顺序进行连接id不同的行通常是子查询或UNIONid越大越先执行。一个常见场景是子查询。比如SELECT id, user_id FROM orders WHERE user_id IN ( SELECT user_id FROM users WHERE level 3 );在执行计划里子查询的id可能是2外层查询是1MySQL会先执行id2的部分把结果集缓存下来再供外层查询使用。需要注意的是MySQL优化器经常会把简单子查询改写成半连接semi-join如果你看到的select_type不是SUBQUERY而是PRIMARY配合DEPENDENT SUBQUERY说明子查询在逐行关联执行这往往是性能瓶颈。select_type的值有很多包括SIMPLE、PRIMARY、SUBQUERY、DERIVED、UNION、UNION RESULT等。这里不用背重点是看到一个查询被拆成了多个步骤后能判断出各步骤的依赖关系。如果出现DEPENDENT SUBQUERY请警惕这种一般意味着每条外层记录都要执行一次子查询代价很高优化空间通常很大。2.2 table、type访问路径的天花板差异table列表示当前步骤从哪张表取数和SQL中的别名对应。真正决定性能高低的是type列它描述的是MySQL如何访问这张表。各种访问方式按性能从高到低排列如下。system表只有一行属于const的特例实际业务中很少见。const通过主键或唯一索引等值匹配最多返回一行。比如WHERE id 10086MySQL可以直接定位。eq_ref被驱动表通过主键或唯一索引做等值关联join查询里最常见的高效访问方式每个驱动表行最多匹配一条被驱动表记录。ref通过普通二级索引等值匹配可能匹配到多行。range通过索引做范围匹配常见于、、BETWEEN、IN等条件。index全索引扫描遍历的不是聚簇索引而是二级索引树。虽然扫描范围比全表小但还是要扫完整棵索引树通常出现在没有其他可用条件的查询里。ALL全表扫描这是最需要避免的类型。如果一个查询的type是ALL不代表这条SQL世界末日了但至少说明执行计划没有用到任何索引来减少读取范围。一个千万级大表加上ALL配合大结果集慢是必然的。我在排查问题时习惯先看type只要type不是ALL且rows不大后面的优化空间一般有限。看到eq_ref和ref时要注意区分。连表查询时驱动表即使扫出来一万行只要被驱动表访问方式是eq_ref也是可以接受的。很多初学者看到一行输出是ALL就急着把索引加在驱动表上其实正确的做法往往是把索引加在被驱动表的关联字段上。2.3 possible_keys、key、key_len索引到底用没用上possible_keys列出的是优化器认为可能用到的索引key是最终实际选中的索引。两者对不上很常见原因一般是候选索引区分度不够或成本估算较高优化器觉得全表扫描更快。key_len是实际使用索引的长度单位是字节。这是容易被忽略但很有用的列。通过key_len可以判断联合索引到底使用了哪几列。比如联合索引是(a, b, c)如果key_len只等于a列的长度说明只用了a如果等于ab的长度说明用了a和bc没被用上。key_len的计算要注意几个细节。int类型占4字节bigint占8字节。varchar类型除了要算字符集字节数还要加上变长字段的2字节长度前缀。字符集不同单字符占的字节也不同utf8mb4下一个中文字符占4字节ascii下只占1字节。允许为NULL的字段key_len还要额外加1字节。例如一个索引列定义为VARCHAR(50) NOT NULLutf8mb4字符集单列长度就是50*42202字节如果字段允许NULL就是203字节。看到key_len之后最好反向验证一下查询是否最多只用了联合索引的前缀。如果建了一个三列联合索引但实际SQL用到的key_len只有第一列的长度那后面的列等于白建此时要考虑调整索引列顺序或者改写SQL让更多列能参与匹配。2.4 ref、rows、filtered预估值里的门道ref列显示当前步骤用索引等值匹配时拿什么和索引列做比较。它可能是const表示用的是常量、某个列名表示和另一张表的列关联也可能为NULL。看到const时通常意味着查询条件里给了固定值优化器可以直接定位。rows是优化器预估要扫描的行数不是精确值更不是最终返回行数。它是一个基于统计信息和索引基数的估算。两张表的JOIN优化器会根据rows和filtered估算驱动顺序rows越小说明优化器认为是更优的驱动方向。filtered是百分比表示经过WHERE条件过滤后预计还有百分之多少的row数能被保留。rows * filtered才是优化器估算的最终结果集大小。这个值与真实情况差别可能很大特别是索引统计不准确的时候。MySQL的优化器统计数据有抽样机制不是实时精确统计所以如果你发现执行计划和实际性能严重不符可以使用ANALYZE TABLE来更新统计信息然后再看执行计划。Extra列是执行计划里细节最丰富的一列。常见的高价值信息有Using index查询通过覆盖索引完成不需要回表效率高。Using index condition走了索引下推InnoDB把索引列上的条件判断下推到存储引擎层完成减少回表次数。Using whereMySQL在拿到记录后还要进行额外的条件过滤通常发生在未使用索引做全部过滤时。Using filesort结果集需要额外的内存或磁盘排序这是排序场景下最常见的优化目标。Using temporary查询过程中用到了临时表经常出现在GROUP BY、DISTINCT、UNION等操作中。3. 索引优化的核心原理与设计要点3.1 索引的本质为什么B树可以避免慢查询索引解决的问题是快速定位。如果把表数据比作一本厚书全表扫描就是从头翻到尾索引则像书的目录先找章节再找页码最后才翻到正文。MySQL默认的InnoDB引擎使用B树组织索引这种结构的特点是所有数据都存储在叶子节点并且叶子节点之间有指针相连非常适合范围查询和排序。在InnoDB里主键索引就是聚簇索引叶子节点直接保存整行数据。也就是说通过主键查找一条记录只要从B树的根节点一路找到叶子节点就能直接拿到数据。而普通索引是二级索引叶子节点保存的是索引列的值和主键值。如果二级索引覆盖了查询需要的所有列MySQL不需要再回表性能最好如果不覆盖就需要根据主键值再查一次聚簇索引这个过程叫回表。回表本身不是坏事但如果一张表有几十万行都命中了二级索引每一行都要回表性能就会明显下降。理解了这一点你就明白了为什么覆盖索引是优化中的“高级武器”。让索引尽量包含查询需要的列能省掉大量随机IO。同样选择主键时尽量用自增、连续的值可以避免B树频繁页分裂写性能也会更稳定。3.2 联合索引与最左前缀原则的真相联合索引是索引优化中最常用也最容易被用错的部分。假设你在订单表上建了联合索引(user_id, status, created_at)从存储结构上看B树先按user_id排序user_id相同的记录再按status排序user_id和status都相同的再按created_at排序。这意味着这个索引天然支持以下查询模式。等值查询user_id 10086等值查询user_id 10086 AND status 1等值查询user_id 10086 AND status 1 AND created_at 2024-04-01排序字段按照user_id、status、created_at依次排列但以下查询却无法充分利用这个索引。status 1 AND created_at 2024-04-01跳过最左边的user_id索引无法定位。user_id 10086 AND created_at 2024-04-01中间跳过了status只有user_id能用到索引过滤。这就是最左前缀原则。设计联合索引时不要一上来就按SQL语句里出现的字段顺序照抄而是要考虑哪些字段适合做等值筛选哪些字段需要控制范围哪些字段需要被索引覆盖以避免回表。一个实用的设计顺序是先放等值查询的字段再放范围查询的字段最后再考虑把排序字段和SELECT列添加进去。常见的反面教材是把区分度低的boolean字段放在联合索引最前面。比如订单表里90%的订单都是有效状态只有10%是异常状态那么把status放在最左侧优化器可能觉得就算用索引扫描也还是要扫一大半数据干脆直接全表扫。这种时候区分度高的字段应该放在左侧比如user_id或者shop_id。3.3 覆盖索引、索引下推、排序优化覆盖索引指的是索引里已经包含了SELECT需要的所有字段查询不需要回表。这个效果会在Extra列里显示为Using index。举个例子一个联合索引为(user_id, status)查询SELECT user_id, status FROM orders WHERE user_id 10086 AND status 1这个查询要的列正好都在索引里直接扫描二级索引树就完成了。但如果在SELECT列表里加了一个不在索引里的total_amountMySQL就必须回表。这时如果能把total_amount也加入索引形成覆盖索引(user_id, status, total_amount)查询会更快。代价是索引本身占的空间变大写入时需要维护更多字段。所以覆盖索引适合用在查询频繁、返回列固定、查询条件明确的场景。索引下推是MySQL 5.6开始引入的优化对应的Extra标记是Using index condition。它做的事情是把WHERE条件中能被索引列判断的部分下推到存储引擎层先过滤掉不满足条件的记录再回表。比如联合索引(user_id, status)WHERE条件里有user_id 10086 AND status 1没有索引下推时MySQL会根据user_id从索引里取出所有记录再回表之后判断status有了索引下推status的判断在存储引擎读索引时就完成了回表次数减少性能提升明显。这个细节在解释为什么某个SQL已经用了联合索引但不需要加回表时特别有用。排序优化是另一个容易踩坑的点。一条ORDER BY created_at DESC的查询如果EXPLAIN里出现了Using filesort说明排序字段没有走索引。走索引排序的前提是排序字段本身是索引的一部分且前面的索引列已经是等值匹配。比如联合索引(user_id, created_at)查询WHERE user_id 10086 ORDER BY created_at DESCcreated_at能复用索引顺序但WHERE status 1 ORDER BY created_at DESC用了同一联合索引也不一定能直接利用索引排序因为status不是最左侧等值条件。4. 从Explain结果到索引调整的实战流程4.1 一个完整的慢SQL排查案例接下来用一个实战案例把流程串起来。假设订单表orders有约1200万行数据结构如下CREATE TABLE orders ( id BIGINT PRIMARY KEY AUTO_INCREMENT, order_no VARCHAR(32) NOT NULL, user_id BIGINT NOT NULL, shop_id BIGINT NOT NULL, status TINYINT NOT NULL COMMENT 1待支付,2已支付,3已发货,4已完成,5已取消, pay_type TINYINT COMMENT 1微信,2支付宝,3银行卡, total_amount DECIMAL(10,2) NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_status (status), KEY idx_created_at (created_at) ) ENGINEInnoDB;慢SQL是这样的SELECT id, order_no, total_amount FROM orders WHERE status 2 AND pay_type 2 AND created_at 2024-03-01 ORDER BY created_at DESC LIMIT 20;第一次EXPLAIN结果type: ALL possible_keys: idx_status, idx_created_at key: NULL rows: 12000000 Extra: Using where; Using filesort看到key为NULLrows预估1200万全表扫。为什么possible_keys里明明有idx_status优化器却不用原因很可能是status2这一条件能过滤的行数比例不高MySQL觉得扫二级索引再回表的成本比直接扫全表还高。这时候直接把WHERE status 2 AND pay_type 2改成WHERE pay_type 2 AND status 2没有任何意义字段顺序和优化器成本估算无关关键是索引设计。我的做法是分两步。第一步先建一个联合索引把等值筛选字段放前面范围字段放后面ALTER TABLE orders ADD INDEX idx_status_pay_type_time (status, pay_type, created_at);再跑EXPLAIN结果变成type: ref key: idx_status_pay_type_time key_len: 2 ref: const, const, NULL rows: 358000 Extra: Using index condition; Using filesort4.2 根据rows变化和真实耗时验证优化效果上面结果明显比之前好usable的过滤条件用上了rows从1200万降到35万行。不过EXPLAIN只是预估还要用真实执行时间说话。我用SET profiling 1开启会话级性能记录再执行一次原SQL最后用SHOW PROFILE FOR QUERY 1看到耗时分到了哪个阶段。对比结果优化前总耗时约32秒Sending data阶段占了大头。优化后总耗时约500毫秒其中大部分时间花在ORDER BY的filesort上。说明剩余瓶颈是排序。这就是最初的Extra里Using filesort在起作用。此时有两个选择一是加一个能把created_at排序也纳入索引结构的联合索引二是改写SQL让LIMIT更早生效。多数情况下ORDER BY字段和范围筛选字段放在同一个联合索引里可能因为范围条件导致索引无法继续支持排序所以需要权衡。这里我采用了更实用的方案把select语句改成只回主表取必要字段并且测试了索引顺序调整为(status, pay_type, created_at, id)。由于二级索引叶子节点已经按status、pay_type、created_at排序当数据量不大时filesort也能接受。更重要的是我确认了分页逻辑是否能用游标代替深分页。业务愿意配合的情况下LIMIT 20 OFFSET 50000改为记录上一页最后一条created_at性能会再上一个台阶。4.3 改索引之后的检查和上线注意事项索引上线不是改一条ALTER TABLE就结束。我最常提醒自己的几点核对冗余索引。新索引(status, pay_type, created_at)已经能覆盖原来单独的idx_status旧索引可以择期删除否则每次写入都要维护多棵B树。加索引的DDL操作会持锁建议用pt-online-schema-change或者MySQL 8.0的INSTANT ALGORITHM。虽然我们这次只在线上几十毫秒完成但大表建索引会锁写必须避开高峰窗口。上线后把EXPLAIN的结果存下来和改之前对比形成一条SQL一条记录的习惯。下次再出现类似慢查询可以直接参考历史结论不用重新排查。不要只看rows。rows只是优化器的估算值统计信息可能滞后定期对大表执行ANALYZE TABLE可以降低错估概率。5. 常见问题速查与避坑经验5.1 让索引失效的六种典型写法很多开发都遇到过“明明建了索引SQL却不用”的情况。我从实际排查里总结出下面几种常见写法一旦看清套路EXPLAIN就能少踩坑。场景问题本质建议方案WHERE函数包裹索引列例如YEAR(created_at)2024索引列参与函数运算B树无法按原值排序改写为created_at BETWEEN 2024-01-01 AND 2024-12-31前导模糊查询例如LIKE %关键词无法根据B树前缀定位改为LIKE 关键词%或考虑全文检索OR连接多个非索引条件优化器无法用一个索引统一处理改写为两个查询UNION ALL或者用UNION索引隐式类型转换varchar列和数字常量比较MySQL会先转成数字再比较索引列被函数化应用层保证类型一致或SQL里显式传字符串对索引列做加减乘除例如amount 5 100同函数问题索引失效改写成amount 95NOT IN / NOT EXISTS的不当使用大部分情况下容易全表扫描按数据分布改写为反连接或LEFT JOIN IS NULL其他会绕路的情况比如在索引列上接NULL判断或者排序字段和WHERE过滤字段顺序互相矛盾都会让优化器放弃索引。遇到索引没生效先别急着加索引看EXPLAIN之前的潜伏条件优先改SQL写法。5.2 高区分度与低区分度联合索引的排序艺术建联合索引时一个常被忽视的坑是列的顺序。下面两条常见设计原则供参考。第一区分度高的列优先放在等值条件的靠前位置。比如user_id的取值比status多得多等值条件user_id10086 AND status2索引放在(user_id, status)比放在(status, user_id)更好因为前者能一开始就缩小B树扫描范围。第二范围条件放后面排序字段放在合适的位置。联合索引(user_id, created_at)能支持user_id10086 ORDER BY created_at DESC但(created_at, user_id)就很难利用同样的排序优化。另一种情况下如果某列本身几乎没有区分度比如订单表里的is_deleted标记99.9%都是0不要把它放在联合索引首位也不要单独建索引。那不是性能优化纯粹是增加写放大。还有一点要特别提醒不要只凭模板背“最左前缀”。最左前缀要结合WHERE和ORDER BY里的真实查询来设计。一个联合索引能否被完整使用关键看查询条件是否满足前缀连续等值。比如联合索引(a,b,c)用b2 AND c3 AND a1也能走到索引因为MySQL优化器会先做等值条件重排关键还是要有a的等值条件存在。5.3 用EXPLAIN做日常SQL巡检的方法我所在的项目组现在有一条硬性规定所有涉及核心表的新SQL提交前必须附上EXPLAIN输出。巡检流程大致是这样的。先打开慢查询日志把long_query_time设置成1秒配合mysqldumpslow或者pt-query-digest找出Top SQL。拿到慢SQL之后逐个跑EXPLAIN重点关注rows、type、Extra三列。rows超过一定阈值比如单表预估扫100万行以上的SQL会被标成待优化。type是ALL或者明显出现Using filesort、Using temporary的也要进一步排查。EXPLAIN虽然输出是预估但MySQL 8.0.18以后的EXPLAIN ANALYZE能给出真实耗时的细节。它会真实执行语句并输出每个步骤实际的行数和耗时这对分析“预估优化器走了A索引实际却卡在B步骤”特别有用。不过EXPLAIN ANALYZE会真的执行SQL所以在生产库跑之前要确认它是只读查询并且注意它会带来额外负载。日常巡检中还要留意统计信息。每次大版本升级或者数据量出现数量级变化后执行计划可能变化原本走索引的查询会变成全表扫描。这时候ANALYZE TABLE orders能帮优化器重新校准。别忽略这个动作很多诡异的性能波动最后都查出来是统计信息太久没更新导致的。我个人比较坚持的一点是慢查询优化不能只盯一个SQL。一条SQL改好了还要看它是否影响了其他查询的索引选择。比如原来的普通二级索引删掉之后有没有其他SQL还依赖这个索引。上线前后把相关表的所有核心查询都跑一遍EXPLAIN是最稳妥的做法。这样坚持几个月你手里会积累一张属于自己业务的执行计划清单哪张表该有哪个索引哪条SQL已经优化过了都会很清晰。最后分享一个我在实际排查中最常用的小技巧遇到和执行计划对不上的诡异慢查询先把EXPLAIN FORMATTREE跑出来看看它比传统表格更容易暴露子查询、JOIN顺序和扫描路径。如果还看不出问题就一步步拆分比如先只跑WHERE条件再单独跑JOIN最后拼起来执行多半能在拆分过程中定位到某一步的意外全表扫描。MySQL的索引优化不是一次完成的它是在一次次Explain和真实执行对比中慢慢磨出来的经验。
MySQL Explain详解与索引优化实战：从执行计划到慢SQL排查
写SQL的时候最怕什么不是语法报错而是查询结果是对的但数据库负载一天比一天高。有一次我凌晨被拉起来处理问题一张不到三千万行的订单表查当天数据要跑四十秒接口直接超时。我第一反应不是改业务逻辑而是先跑了EXPLAIN。结果发现查询在扫全表优化器连一个像样的候选索引都没用上。后面调整了查询条件重新设计联合索引接口耗时回到几十毫秒。这篇内容主要围绕MySQL Explain详解与索引优化实战展开会从执行计划的每一列到底是什么意思开始讲到索引命中、失效、联合索引怎么设计再给一个完整慢SQL排查流程。适合正在准备MySQL面试题的后端开发也适合被慢查询折磨过但还没系统梳理过执行计划的DBA或运维。无论你是第一次打开EXPLAIN输出还是已经会看rows但不知道下一步怎么改索引这篇文章都能给你一套可执行的思路。1. 执行计划是什么为什么每个MySQL开发都要懂1.1 没有EXPLAIN时我踩过的那些坑早期我写过不少“看上去没问题”的SQL比如在订单表上判断状态和支付方式再加一个按创建时间倒序的排序。业务规模小的时候一切都好等到单表几百万行之后查询一次比一次慢。当时我的排查方式很原始先给所有可能在WHERE里面出现的字段建单列索引然后一个个试。这种盲目的做法带来了两个问题。一是索引建了一大堆但SQL实际执行时根本没用上。比如我建了status、pay_type、created_at三个独立索引MySQL优化器在单次查询里通常只会选其中一个另外两个索引完全是冗余的。二是排序还是慢因为排序需要把结果集加载到临时文件或者走filesort索引没覆盖到排序字段EXPLAIN里就会出现明显的Using filesort这个标记比全表扫描还让人头疼。后来我养成了习惯任何SQL上线之前先看执行计划。EXPLAIN不是万能的但它能让“猜测”变成“有依据的判断”。尤其是面对慢SQL执行计划会清清楚楚告诉你这条语句是先查了哪张表用了哪个索引大致扫了多少行是否要做临时表和文件排序。知道这些信息后加索引才不是碰运气。1.2 EXPLAIN到底能告诉我们什么EXPLAIN是MySQL提供的一条诊断命令用法很简单在SELECT前加EXPLAIN四个字就行。MySQL 8.0还可以用EXPLAIN FORMATTREE查看更结构化的输出或者用EXPLAIN ANALYZE拿到真实执行时间。我先用最传统的表格输出来说因为它是所有版本通用、也是大家看得最多的格式。执行计划的输出是一张多列的表每一行代表一个执行步骤。关键信息可以分成几组。查询结构相关id、select_type、table解决“这条SQL被拆成了哪些步骤”。访问路径相关type、possible_keys、key、key_len、ref解决“每一步到底怎么从表里取数据”。成本预估相关rows、filtered解决“这一步大概会扫多少行、筛选后还剩多少”。额外行为相关Extra解决“有没有走索引下推、临时表、文件排序、覆盖索引”。举个例子EXPLAIN SELECT id, order_no, total_amount FROM orders WHERE status 1 AND created_at 2024-04-01 ORDER BY created_at DESC LIMIT 20;输出大概长这样------------------------------------------------------------------------------------------------------------ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ------------------------------------------------------------------------------------------------------------ | 1 | SIMPLE | orders | range | idx_status | status | 1 | NULL | 198760 | Using index condition; Using filesort | ------------------------------------------------------------------------------------------------------------这里能看出的问题是虽然用了索引idx_status但rows预估还有20万行并且出现了Using filesort。说明单列索引把status过滤掉了但created_at的范围筛选和排序都没有被索引很好地支撑。看到这个输出下一步优化方向就很明确了要么调整索引让范围条件和排序都能走索引要么改写SQL减少筛选范围。2. 读懂Explain输出从id到Extra逐列拆解2.1 id、select_type查询怎么拆、怎么合并EXPLAIN结果里最左侧的id用来标识查询中每个SELECT的执行顺序。id相同的行MySQL认为它们是同一个查询里的多个表会按照从上到下的顺序进行连接id不同的行通常是子查询或UNIONid越大越先执行。一个常见场景是子查询。比如SELECT id, user_id FROM orders WHERE user_id IN ( SELECT user_id FROM users WHERE level 3 );在执行计划里子查询的id可能是2外层查询是1MySQL会先执行id2的部分把结果集缓存下来再供外层查询使用。需要注意的是MySQL优化器经常会把简单子查询改写成半连接semi-join如果你看到的select_type不是SUBQUERY而是PRIMARY配合DEPENDENT SUBQUERY说明子查询在逐行关联执行这往往是性能瓶颈。select_type的值有很多包括SIMPLE、PRIMARY、SUBQUERY、DERIVED、UNION、UNION RESULT等。这里不用背重点是看到一个查询被拆成了多个步骤后能判断出各步骤的依赖关系。如果出现DEPENDENT SUBQUERY请警惕这种一般意味着每条外层记录都要执行一次子查询代价很高优化空间通常很大。2.2 table、type访问路径的天花板差异table列表示当前步骤从哪张表取数和SQL中的别名对应。真正决定性能高低的是type列它描述的是MySQL如何访问这张表。各种访问方式按性能从高到低排列如下。system表只有一行属于const的特例实际业务中很少见。const通过主键或唯一索引等值匹配最多返回一行。比如WHERE id 10086MySQL可以直接定位。eq_ref被驱动表通过主键或唯一索引做等值关联join查询里最常见的高效访问方式每个驱动表行最多匹配一条被驱动表记录。ref通过普通二级索引等值匹配可能匹配到多行。range通过索引做范围匹配常见于、、BETWEEN、IN等条件。index全索引扫描遍历的不是聚簇索引而是二级索引树。虽然扫描范围比全表小但还是要扫完整棵索引树通常出现在没有其他可用条件的查询里。ALL全表扫描这是最需要避免的类型。如果一个查询的type是ALL不代表这条SQL世界末日了但至少说明执行计划没有用到任何索引来减少读取范围。一个千万级大表加上ALL配合大结果集慢是必然的。我在排查问题时习惯先看type只要type不是ALL且rows不大后面的优化空间一般有限。看到eq_ref和ref时要注意区分。连表查询时驱动表即使扫出来一万行只要被驱动表访问方式是eq_ref也是可以接受的。很多初学者看到一行输出是ALL就急着把索引加在驱动表上其实正确的做法往往是把索引加在被驱动表的关联字段上。2.3 possible_keys、key、key_len索引到底用没用上possible_keys列出的是优化器认为可能用到的索引key是最终实际选中的索引。两者对不上很常见原因一般是候选索引区分度不够或成本估算较高优化器觉得全表扫描更快。key_len是实际使用索引的长度单位是字节。这是容易被忽略但很有用的列。通过key_len可以判断联合索引到底使用了哪几列。比如联合索引是(a, b, c)如果key_len只等于a列的长度说明只用了a如果等于ab的长度说明用了a和bc没被用上。key_len的计算要注意几个细节。int类型占4字节bigint占8字节。varchar类型除了要算字符集字节数还要加上变长字段的2字节长度前缀。字符集不同单字符占的字节也不同utf8mb4下一个中文字符占4字节ascii下只占1字节。允许为NULL的字段key_len还要额外加1字节。例如一个索引列定义为VARCHAR(50) NOT NULLutf8mb4字符集单列长度就是50*42202字节如果字段允许NULL就是203字节。看到key_len之后最好反向验证一下查询是否最多只用了联合索引的前缀。如果建了一个三列联合索引但实际SQL用到的key_len只有第一列的长度那后面的列等于白建此时要考虑调整索引列顺序或者改写SQL让更多列能参与匹配。2.4 ref、rows、filtered预估值里的门道ref列显示当前步骤用索引等值匹配时拿什么和索引列做比较。它可能是const表示用的是常量、某个列名表示和另一张表的列关联也可能为NULL。看到const时通常意味着查询条件里给了固定值优化器可以直接定位。rows是优化器预估要扫描的行数不是精确值更不是最终返回行数。它是一个基于统计信息和索引基数的估算。两张表的JOIN优化器会根据rows和filtered估算驱动顺序rows越小说明优化器认为是更优的驱动方向。filtered是百分比表示经过WHERE条件过滤后预计还有百分之多少的row数能被保留。rows * filtered才是优化器估算的最终结果集大小。这个值与真实情况差别可能很大特别是索引统计不准确的时候。MySQL的优化器统计数据有抽样机制不是实时精确统计所以如果你发现执行计划和实际性能严重不符可以使用ANALYZE TABLE来更新统计信息然后再看执行计划。Extra列是执行计划里细节最丰富的一列。常见的高价值信息有Using index查询通过覆盖索引完成不需要回表效率高。Using index condition走了索引下推InnoDB把索引列上的条件判断下推到存储引擎层完成减少回表次数。Using whereMySQL在拿到记录后还要进行额外的条件过滤通常发生在未使用索引做全部过滤时。Using filesort结果集需要额外的内存或磁盘排序这是排序场景下最常见的优化目标。Using temporary查询过程中用到了临时表经常出现在GROUP BY、DISTINCT、UNION等操作中。3. 索引优化的核心原理与设计要点3.1 索引的本质为什么B树可以避免慢查询索引解决的问题是快速定位。如果把表数据比作一本厚书全表扫描就是从头翻到尾索引则像书的目录先找章节再找页码最后才翻到正文。MySQL默认的InnoDB引擎使用B树组织索引这种结构的特点是所有数据都存储在叶子节点并且叶子节点之间有指针相连非常适合范围查询和排序。在InnoDB里主键索引就是聚簇索引叶子节点直接保存整行数据。也就是说通过主键查找一条记录只要从B树的根节点一路找到叶子节点就能直接拿到数据。而普通索引是二级索引叶子节点保存的是索引列的值和主键值。如果二级索引覆盖了查询需要的所有列MySQL不需要再回表性能最好如果不覆盖就需要根据主键值再查一次聚簇索引这个过程叫回表。回表本身不是坏事但如果一张表有几十万行都命中了二级索引每一行都要回表性能就会明显下降。理解了这一点你就明白了为什么覆盖索引是优化中的“高级武器”。让索引尽量包含查询需要的列能省掉大量随机IO。同样选择主键时尽量用自增、连续的值可以避免B树频繁页分裂写性能也会更稳定。3.2 联合索引与最左前缀原则的真相联合索引是索引优化中最常用也最容易被用错的部分。假设你在订单表上建了联合索引(user_id, status, created_at)从存储结构上看B树先按user_id排序user_id相同的记录再按status排序user_id和status都相同的再按created_at排序。这意味着这个索引天然支持以下查询模式。等值查询user_id 10086等值查询user_id 10086 AND status 1等值查询user_id 10086 AND status 1 AND created_at 2024-04-01排序字段按照user_id、status、created_at依次排列但以下查询却无法充分利用这个索引。status 1 AND created_at 2024-04-01跳过最左边的user_id索引无法定位。user_id 10086 AND created_at 2024-04-01中间跳过了status只有user_id能用到索引过滤。这就是最左前缀原则。设计联合索引时不要一上来就按SQL语句里出现的字段顺序照抄而是要考虑哪些字段适合做等值筛选哪些字段需要控制范围哪些字段需要被索引覆盖以避免回表。一个实用的设计顺序是先放等值查询的字段再放范围查询的字段最后再考虑把排序字段和SELECT列添加进去。常见的反面教材是把区分度低的boolean字段放在联合索引最前面。比如订单表里90%的订单都是有效状态只有10%是异常状态那么把status放在最左侧优化器可能觉得就算用索引扫描也还是要扫一大半数据干脆直接全表扫。这种时候区分度高的字段应该放在左侧比如user_id或者shop_id。3.3 覆盖索引、索引下推、排序优化覆盖索引指的是索引里已经包含了SELECT需要的所有字段查询不需要回表。这个效果会在Extra列里显示为Using index。举个例子一个联合索引为(user_id, status)查询SELECT user_id, status FROM orders WHERE user_id 10086 AND status 1这个查询要的列正好都在索引里直接扫描二级索引树就完成了。但如果在SELECT列表里加了一个不在索引里的total_amountMySQL就必须回表。这时如果能把total_amount也加入索引形成覆盖索引(user_id, status, total_amount)查询会更快。代价是索引本身占的空间变大写入时需要维护更多字段。所以覆盖索引适合用在查询频繁、返回列固定、查询条件明确的场景。索引下推是MySQL 5.6开始引入的优化对应的Extra标记是Using index condition。它做的事情是把WHERE条件中能被索引列判断的部分下推到存储引擎层先过滤掉不满足条件的记录再回表。比如联合索引(user_id, status)WHERE条件里有user_id 10086 AND status 1没有索引下推时MySQL会根据user_id从索引里取出所有记录再回表之后判断status有了索引下推status的判断在存储引擎读索引时就完成了回表次数减少性能提升明显。这个细节在解释为什么某个SQL已经用了联合索引但不需要加回表时特别有用。排序优化是另一个容易踩坑的点。一条ORDER BY created_at DESC的查询如果EXPLAIN里出现了Using filesort说明排序字段没有走索引。走索引排序的前提是排序字段本身是索引的一部分且前面的索引列已经是等值匹配。比如联合索引(user_id, created_at)查询WHERE user_id 10086 ORDER BY created_at DESCcreated_at能复用索引顺序但WHERE status 1 ORDER BY created_at DESC用了同一联合索引也不一定能直接利用索引排序因为status不是最左侧等值条件。4. 从Explain结果到索引调整的实战流程4.1 一个完整的慢SQL排查案例接下来用一个实战案例把流程串起来。假设订单表orders有约1200万行数据结构如下CREATE TABLE orders ( id BIGINT PRIMARY KEY AUTO_INCREMENT, order_no VARCHAR(32) NOT NULL, user_id BIGINT NOT NULL, shop_id BIGINT NOT NULL, status TINYINT NOT NULL COMMENT 1待支付,2已支付,3已发货,4已完成,5已取消, pay_type TINYINT COMMENT 1微信,2支付宝,3银行卡, total_amount DECIMAL(10,2) NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_status (status), KEY idx_created_at (created_at) ) ENGINEInnoDB;慢SQL是这样的SELECT id, order_no, total_amount FROM orders WHERE status 2 AND pay_type 2 AND created_at 2024-03-01 ORDER BY created_at DESC LIMIT 20;第一次EXPLAIN结果type: ALL possible_keys: idx_status, idx_created_at key: NULL rows: 12000000 Extra: Using where; Using filesort看到key为NULLrows预估1200万全表扫。为什么possible_keys里明明有idx_status优化器却不用原因很可能是status2这一条件能过滤的行数比例不高MySQL觉得扫二级索引再回表的成本比直接扫全表还高。这时候直接把WHERE status 2 AND pay_type 2改成WHERE pay_type 2 AND status 2没有任何意义字段顺序和优化器成本估算无关关键是索引设计。我的做法是分两步。第一步先建一个联合索引把等值筛选字段放前面范围字段放后面ALTER TABLE orders ADD INDEX idx_status_pay_type_time (status, pay_type, created_at);再跑EXPLAIN结果变成type: ref key: idx_status_pay_type_time key_len: 2 ref: const, const, NULL rows: 358000 Extra: Using index condition; Using filesort4.2 根据rows变化和真实耗时验证优化效果上面结果明显比之前好usable的过滤条件用上了rows从1200万降到35万行。不过EXPLAIN只是预估还要用真实执行时间说话。我用SET profiling 1开启会话级性能记录再执行一次原SQL最后用SHOW PROFILE FOR QUERY 1看到耗时分到了哪个阶段。对比结果优化前总耗时约32秒Sending data阶段占了大头。优化后总耗时约500毫秒其中大部分时间花在ORDER BY的filesort上。说明剩余瓶颈是排序。这就是最初的Extra里Using filesort在起作用。此时有两个选择一是加一个能把created_at排序也纳入索引结构的联合索引二是改写SQL让LIMIT更早生效。多数情况下ORDER BY字段和范围筛选字段放在同一个联合索引里可能因为范围条件导致索引无法继续支持排序所以需要权衡。这里我采用了更实用的方案把select语句改成只回主表取必要字段并且测试了索引顺序调整为(status, pay_type, created_at, id)。由于二级索引叶子节点已经按status、pay_type、created_at排序当数据量不大时filesort也能接受。更重要的是我确认了分页逻辑是否能用游标代替深分页。业务愿意配合的情况下LIMIT 20 OFFSET 50000改为记录上一页最后一条created_at性能会再上一个台阶。4.3 改索引之后的检查和上线注意事项索引上线不是改一条ALTER TABLE就结束。我最常提醒自己的几点核对冗余索引。新索引(status, pay_type, created_at)已经能覆盖原来单独的idx_status旧索引可以择期删除否则每次写入都要维护多棵B树。加索引的DDL操作会持锁建议用pt-online-schema-change或者MySQL 8.0的INSTANT ALGORITHM。虽然我们这次只在线上几十毫秒完成但大表建索引会锁写必须避开高峰窗口。上线后把EXPLAIN的结果存下来和改之前对比形成一条SQL一条记录的习惯。下次再出现类似慢查询可以直接参考历史结论不用重新排查。不要只看rows。rows只是优化器的估算值统计信息可能滞后定期对大表执行ANALYZE TABLE可以降低错估概率。5. 常见问题速查与避坑经验5.1 让索引失效的六种典型写法很多开发都遇到过“明明建了索引SQL却不用”的情况。我从实际排查里总结出下面几种常见写法一旦看清套路EXPLAIN就能少踩坑。场景问题本质建议方案WHERE函数包裹索引列例如YEAR(created_at)2024索引列参与函数运算B树无法按原值排序改写为created_at BETWEEN 2024-01-01 AND 2024-12-31前导模糊查询例如LIKE %关键词无法根据B树前缀定位改为LIKE 关键词%或考虑全文检索OR连接多个非索引条件优化器无法用一个索引统一处理改写为两个查询UNION ALL或者用UNION索引隐式类型转换varchar列和数字常量比较MySQL会先转成数字再比较索引列被函数化应用层保证类型一致或SQL里显式传字符串对索引列做加减乘除例如amount 5 100同函数问题索引失效改写成amount 95NOT IN / NOT EXISTS的不当使用大部分情况下容易全表扫描按数据分布改写为反连接或LEFT JOIN IS NULL其他会绕路的情况比如在索引列上接NULL判断或者排序字段和WHERE过滤字段顺序互相矛盾都会让优化器放弃索引。遇到索引没生效先别急着加索引看EXPLAIN之前的潜伏条件优先改SQL写法。5.2 高区分度与低区分度联合索引的排序艺术建联合索引时一个常被忽视的坑是列的顺序。下面两条常见设计原则供参考。第一区分度高的列优先放在等值条件的靠前位置。比如user_id的取值比status多得多等值条件user_id10086 AND status2索引放在(user_id, status)比放在(status, user_id)更好因为前者能一开始就缩小B树扫描范围。第二范围条件放后面排序字段放在合适的位置。联合索引(user_id, created_at)能支持user_id10086 ORDER BY created_at DESC但(created_at, user_id)就很难利用同样的排序优化。另一种情况下如果某列本身几乎没有区分度比如订单表里的is_deleted标记99.9%都是0不要把它放在联合索引首位也不要单独建索引。那不是性能优化纯粹是增加写放大。还有一点要特别提醒不要只凭模板背“最左前缀”。最左前缀要结合WHERE和ORDER BY里的真实查询来设计。一个联合索引能否被完整使用关键看查询条件是否满足前缀连续等值。比如联合索引(a,b,c)用b2 AND c3 AND a1也能走到索引因为MySQL优化器会先做等值条件重排关键还是要有a的等值条件存在。5.3 用EXPLAIN做日常SQL巡检的方法我所在的项目组现在有一条硬性规定所有涉及核心表的新SQL提交前必须附上EXPLAIN输出。巡检流程大致是这样的。先打开慢查询日志把long_query_time设置成1秒配合mysqldumpslow或者pt-query-digest找出Top SQL。拿到慢SQL之后逐个跑EXPLAIN重点关注rows、type、Extra三列。rows超过一定阈值比如单表预估扫100万行以上的SQL会被标成待优化。type是ALL或者明显出现Using filesort、Using temporary的也要进一步排查。EXPLAIN虽然输出是预估但MySQL 8.0.18以后的EXPLAIN ANALYZE能给出真实耗时的细节。它会真实执行语句并输出每个步骤实际的行数和耗时这对分析“预估优化器走了A索引实际却卡在B步骤”特别有用。不过EXPLAIN ANALYZE会真的执行SQL所以在生产库跑之前要确认它是只读查询并且注意它会带来额外负载。日常巡检中还要留意统计信息。每次大版本升级或者数据量出现数量级变化后执行计划可能变化原本走索引的查询会变成全表扫描。这时候ANALYZE TABLE orders能帮优化器重新校准。别忽略这个动作很多诡异的性能波动最后都查出来是统计信息太久没更新导致的。我个人比较坚持的一点是慢查询优化不能只盯一个SQL。一条SQL改好了还要看它是否影响了其他查询的索引选择。比如原来的普通二级索引删掉之后有没有其他SQL还依赖这个索引。上线前后把相关表的所有核心查询都跑一遍EXPLAIN是最稳妥的做法。这样坚持几个月你手里会积累一张属于自己业务的执行计划清单哪张表该有哪个索引哪条SQL已经优化过了都会很清晰。最后分享一个我在实际排查中最常用的小技巧遇到和执行计划对不上的诡异慢查询先把EXPLAIN FORMATTREE跑出来看看它比传统表格更容易暴露子查询、JOIN顺序和扫描路径。如果还看不出问题就一步步拆分比如先只跑WHERE条件再单独跑JOIN最后拼起来执行多半能在拆分过程中定位到某一步的意外全表扫描。MySQL的索引优化不是一次完成的它是在一次次Explain和真实执行对比中慢慢磨出来的经验。
