1. 一条慢SQL的拆解Explain命令到底输出什么昨天夜班处理了一个线上问题一条执行了8秒的慢查询表里只有400多万行查询条件是三个字段等值怎么看都不至于慢成这样。我没有急着去改代码而是打开执行计划看了一眼。这一眼就直接锁定了问题type那一栏写着ALLrows估算扫出三百多万行一个索引都没用上。这就是Explain的价值——它把“MySQL到底打算怎么执行这条SQL”摊开给你看瓶颈在哪改完之后到底有没有效果全都能在几秒钟内得到验证。所以这篇内容我打算把Explain和索引优化放在一起讲。Explain是MySQL自带的执行计划分析工具只要在SQL前面加上EXPLAIN关键字就能得到一张执行计划表索引优化则是基于这张表格去调整索引设计、查询写法的一整套实践。两者合在一起就是一条慢SQL从定位、分析到优化、验证的完整闭环。适合刚接触MySQL优化、看到慢查询就一头雾水的开发同学也适合有几年经验但遇到性能问题还是靠猜的同行。1.1 两分钟跑出第一条执行计划使用方式极其简单在SQL开头加EXPLAIN就行EXPLAIN SELECT id, name, age FROM user WHERE name 张三 AND age 30;执行后会返回一张表而不是你查询的结果集。这里有个很多新手会忽略的细节Explain并不真正执行你的SQL它只是让MySQL优化器把执行计划算出来给你看。所以哪怕你Explain的是一条要跑几分钟的SQL它本身也是很快的。想要看真实执行耗时和真实扫描行数需要用MySQL 8.0.16之后提供的EXPLAIN ANALYZE这个后面会专门说到。我建议你第一次接触时直接在你项目里最慢的那条SQL前面加上EXPLAIN跑一遍。不要打开图形化工具先在命令行里看原生的输出因为很多IDE会把Explain的字段重新排版反而不容易看清原始信息。命令行下执行EXPLAIN出来的每一行都对应一个表访问步骤多表查询会有多行逐行看过去就能还原出MySQL的执行路径。1.2 执行计划输出长什么样先建一张演示表后面所有的例子都用它CREATE TABLE user ( id bigint NOT NULL AUTO_INCREMENT, name varchar(50) NOT NULL, age int NOT NULL, email varchar(100) DEFAULT NULL, created_at datetime NOT NULL, PRIMARY KEY (id), KEY idx_name_age (name, age) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;执行前面的Explain语句输出大致是这样id: 1 select_type: SIMPLE table: user type: ref possible_keys: idx_name_age key: idx_name_age key_len: 206 ref: const,const rows: 1 filtered: 100 Extra: Using index这张表里的字段看起来有点多但别慌它们的职责是清楚的id和select_type告诉你这条SQL的查询结构table告诉你在访问哪张表type、possible_keys、key、key_len、ref说明走了哪个索引、怎么走的rows和filtered是优化器估算的成本Extra则专门存放一些“额外行为”的提示。注意Explain输出的rows和filtered是优化器基于统计信息估算出来的不是真实执行结果。尤其是在数据分布严重不均或者统计信息过期时估算值和实际值可能差很远。判断一个优化的真实效果最终要以线上耗时和真实执行计划为准。2. Explain核心字段逐个拆解type和rows最值得盯2.1 字段太多先按用途分组我在实际工作中把Explain的字段分成三组看第一组是定位类包括id、select_type、table、partitions。这一组回答的是“这条SQL从结构上拆成了几个部分先访问谁、后访问谁”。多表查询时id相同的行代表同一个SELECT下的关联表id越大越先执行如果是子查询id还会分层。这部分信息能帮你理清SQL的执行顺序。第二组是路径类包括type、possible_keys、key、key_len、ref。这一组回答的是“MySQL到底用了什么方式访问数据用哪把索引用了索引的多少部分”。这是整个执行计划里信息密度最高的区域也是优化时最先要看的地方。第三组是代价类包括rows、filtered、Extra。这一组回答的是“这一步大概要扫多少行还剩多少比例还有没有额外的排序、临时表、join buffer之类的动作”。它直接决定了这条SQL是秒回还是卡死。分组之后你会发现日常工作里真正常看的就是六个字type、rows、Extra。判断一条慢SQL有没有救先看type是不是能在range以上再看rows是不是和表总行数一个量级最后扫一眼Extra里有没有Using filesort、Using temporary这种危险信号。2.2 type级别的完整排序和代价含义type字段描述的是访问类型。MySQL官方文档给出的完整顺序大致是type含义是否理想system表只有一行属于const的特例极端情况基本遇不到const通过主键或唯一索引等值查询最多返回一行最优eq_ref关联查询时被驱动表通过主键或唯一索引匹配优ref通过非唯一索引等值匹配可能返回多行良好range索引上做范围扫描如, , BETWEEN, LIKE前缀可接受index遍历整个二级索引树相当于“全索引扫描”较差ALL全表扫描数据行全部过一遍最差这里有个容易误会的点index类型虽然带“index”字样但它不等于“用了索引就快”。它只是说MySQL选择去扫一棵二级索引树而不是去扫聚簇索引。二级索引通常比主键索引小所以如果查询列恰好都被索引覆盖index可能比ALL快一些但它本质上还是在遍历没有做到精准定位。真正的目标是把type至少压到range最好是ref、eq_ref、const这一档。rows字段就是优化器估算的扫描行数。一条SQL如果typeALL且rows接近全表行数基本可以断定它在做一次毫无节制的全表扫描。你还要结合filtered看filtered表示经过where条件过滤后剩余多少比例的行返回给上层。比如rows100000、filtered1意思是扫描10万行后最后只返回100行这中间的过滤成本就是巨大的浪费。2.3 Extra里的三个危险信号Extra字段是执行计划的“评论区”很多关键问题都藏在这里。最常见的几个值里有好消息也有坏消息。用前面的例子查询条件命中联合索引idx_name_age而且查询列只有id、name、age输出的Extra是Using index。别高兴太早这里的Using index表示覆盖索引意思是查询所需的列全部在索引树里不需要回表。这是一个非常理想的信号但不是所有Using index都等同于此后面细说。真正要警惕的是这三个第一个是Using filesort。看到它说明MySQL无法利用索引的顺序直接返回数据需要重新排一遍。排序可能发生在内存也可能落到磁盘一旦数据量大磁盘排序就是灾难。一个典型例子WHERE name张三 ORDER BY created_at由于created_at不在联合索引里Order排序没法沿用索引序就会触发filesort。第二个是Using temporary。看到它说明MySQL为了完成GROUP BY、DISTINCT、某些JOIN或子查询需要创建临时表。临时表可能出现在内存也可能溢出到磁盘配合using filesort出现时通常意味着这条SQL存在典型的优化空间比如让GROUP BY的列和索引前缀匹配。第三个是Using join buffer。这个出现在多表关联查询里说明MySQL把驱动表的数据装进join buffer再去和另一个表做匹配而被驱动表没有可用的索引来快速匹配只能逐条对比。这通常是被驱动表连接列缺索引或者连接列的类型、字符集不一致导致索引用不上。提醒一句很多人看到Extra里出现Using where就以为没走索引这是误解。Using where只是说存储引擎返回记录后Server层还需要再做一次条件过滤。它和走了索引并不冲突真正需要担心的是Extra里出现上面三个危险信号以及更靠前的type掉到了ALL。3. 索引优化实战从建索引到验证的全流程3.1 三步定位慢SQL确认优化方向MySQL的索引优化不是闭着眼建索引而是有套路的。我自己的固定流程是这样的第一步先把慢SQL捞出来。线上可以开启慢查询日志配置里写上slow_query_log ONlong_query_time 1再配合log_queries_not_using_indexes ON凡是超过1秒或者没走索引的SQL都会被记录。没有配置权限的话也可以直接查performance_schema里的events_statements_summary_by_digest按平均执行时间倒序排最容易出问题的SQL立刻现形。第二步用Explain查看当前执行计划。这一步要记录三个关键值type是什么、rows估算多少、Extra有没有危险信号。比如typeALL、rows3340000、Extra什么都没有基本可以判断是缺索引或者条件写坏了索引。第三步结合业务判断优化方向。这步很多人会跳过其实是决定成败的。要问自己三个问题这条SQL是不是必须要全字段返回条件里有没有可以缩小的业务范围排序和分页的需求能不能换一种方式满足想清楚再动手否则你只是把慢SQL从一种形态改成了另一种形态。比如一个字段可以缩小时间范围你却为了省事不加过滤条件那索引建得再好也没用。3.2 联合索引的最左前缀法则与字段顺序单列索引没什么好说的难的是联合索引。很多人建联合索引时很随意结果SQL执行计划显示possible_keys里有索引key却是NULL或者走了索引但只用了其中一列。这就是最左前缀法则在起作用。联合索引idx_name_age(name, age)在B树里的排列顺序是先按name排序name相同再按age排序。所以查询条件必须从联合索引的最左列开始连续匹配索引才能被充分利用WHERE name张三可以走索引typeref。WHERE name张三 AND age30可以走索引而且name和age两列都用上。WHERE age30不走索引因为跳过最左列name索引的有序性帮不上忙。很多人记住“最左前缀”这四个字却忽略了“连续”这个词。如果条件是WHERE name张三 AND emailab.com联合索引实际只用到了name列email列不在索引里要回表过滤。这种情况下到底要不要把email也加入索引取决于查询频率和区分度。联合索引字段顺序的设计原则我总结成一句话区分度高的放前面范围条件放后面等值条件优先于范围条件。比如(a, b, c)三个字段如果a区分度极高name查询就能过滤掉大部分数据那b、c放在后面也问题不大如果a是性别这种区分度极低的列它放在最前面会让索引的选择性大打折扣徒增索引维护成本。3.3 索引失效的高频原因与实测对照索引建了SQL条件看着也匹配但Explain就是告诉你没走索引。这类问题我在排查中遇到太多了常见原因就这几类每一条都对应着一个真实踩过的坑。其中最典型的是对索引列做了函数或运算。比如WHERE YEAR(created_at) 2024它让created_at参与了函数计算B树的有序性在函数面前失去意义索引自然用不上。正确的写法是范围查询WHERE created_at 2024-01-01 00:00:00 AND created_at 2025-01-01 00:00:00。同理索引列上做加减乘除也是一样的效果。第二类是隐式类型转换。比如手机号字段phone在表里是varchar查询写成WHERE phone 13800000000MySQL会把varchar列转成数字去比较相当于对索引列做了隐式CAST索引失效。这类问题在日志里往往表现为possible_keys有索引但key为NULL。排查时用SHOW CREATE TABLE核对字段类型再用EXPLAIN验证基本一抓一个准。搜索里那个“mysql将字符串转为日期”也是同一类问题日期字段存成了字符串再和DATE类型比较就会触发隐式转换。第三类是模糊查询的前导通配符WHERE name LIKE %张三%。因为索引的有序性只能保证前缀匹配带着前导%时MySQL没法定位到起始位置只能放弃索引。如果业务确实需要中间匹配建议考虑专门的全文索引或者额外的冗余字段方案而不是硬扛。第四类是OR条件两边不平衡WHERE name张三 OR status1如果status列没有索引优化器评估后很可能会放弃所有索引直接全表扫。两个条件都有索引的话MySQL可能走index_merge但也未必比全表快多少。这类SQL最好拆成两句用UNION ALL或者给两边都建上合适的索引。4. 为什么这些优化有效B树、回表与索引下推4.1 三层B树能装多少数据要理解为什么索引能带来数量级的提升得先理解InnoDB的存储结构。InnoDB的索引是一棵B树数据存在叶子节点非叶子节点只存键值和指向下一层的指针。InnoDB默认页大小是16KB。假设主键是bigint占用8字节指针算6字节那么一个非叶子节点页大约能放16384除以14也就是1100多个键值对。如果叶子节点也有同样的存储能力两层非叶子节点就可以索引到1100乘以1100约一百三十多万个叶子页。假设每个叶子页放几十行用户记录这一棵三层B树就能支撑上千万行数据。这个计算想说明什么当你要通过主键ID或者普通索引定位一条记录时最多只需要从根节点走向叶子节点经历两层三层索引页的磁盘读取就能找到目标。这是全表扫描完全没法比的优势——全表扫描要从第一行翻到最后一个数据页数据量越大成本越高索引查询的成本几乎和表的总行数无关。所以每当我看到一条typeALL的SQL心里想的不是“MySQL在表里找数据”而是“MySQL从1000万个页里逐个翻找”。这种查找方式在数据量小的时候感觉不出来一旦数据放大到百万千万级性能差距就是天壤之别。4.2 回表与覆盖索引的取舍InnoDB里主键索引的叶子节点直接存放整行数据这个索引叫聚簇索引其他索引的叶子节点存放的是主键值和索引字段值这个索引叫二级索引。当你通过二级索引找到一条记录时拿到的是这条记录的主键值还需要带着主键再回聚簇索引里把整行取出来这个过程就叫回表。回表一次两次没感觉但如果一条SQL扫描了10万行二级索引记录每一行都要回表那就在反复进行随机磁盘读取。这是很多SQL明明走了索引却还是很慢的根本原因。如果查询需要返回的所有列都在二级索引里那么MySQL在二级索引的叶子节点上就能拿到全部数据不需要回表。此时Explain输出中的Extra会显示Using index这个就叫覆盖索引。前面例子里的SELECT id, name, age条件name张三联合索引idx_name_age里已经有id、name、age所以Extra是Using index。覆盖索引带来的优化非常实在但它也有代价索引列越多索引树越大每次插入更新需要维护的索引数据就越多。所以设计覆盖索引时要克制只把高频查询里真正需要的列放进去而不是把整张表的字段都塞进索引。我的原则是优先保证where条件走索引其次再考虑查询列是否能被覆盖用空间换查询速度的事要算清楚账。4.3 区分度定生死什么样的列值得建索引索引不是建得越多越好也不是任何列都能建索引。判断一个列值不值得建索引核心指标是区分度COUNT(DISTINCT 列) 除以 COUNT(*)。这个比例越接近1说明这列的值越唯一索引选择效果越好如果比例很低比如性别列只可能有几个不同的值那建单列索引基本没有意义。但区分度低不意味着这个列在联合索引里毫无价值。在实际业务里如果一个低区分度列的查询频率极高你可以把它放到联合索引靠后的位置。这样做的好处是前面高区分度列已经过滤掉大量数据后面的低区分度列只是作为最后的筛选补充同时还能让索引覆盖更多的过滤条件。我处理过的一个例子订单表里有订单状态字段只有五六个值单独建索引完全没用。但当它和用户ID组成联合索引(user_id, status)后一条“查某个用户全部待支付订单”的SQL能直接从用户ID定位到底再在status上做精准筛选。这就是区分度低的列在联合索引中存在的意义。另外区分度判断还有一个隐藏前提统计信息要准。MySQL优化器做成本估算时依赖表统计信息如果统计信息过期它可能不知道区分度已经变化从而做出错误的索引选择。这时候执行ANALYZE TABLE可以重新采集统计信息让优化器重新评估。5. 真实业务场景优化排序、深分页与关联查询5.1 ORDER BY 排序优化的正确姿势ORDER BY慢的根源90%都出在一个地方排序字段没有包含在可用索引里导致MySQL必须把查出来的数据重新排序也就是Extra里的Using filesort。用前面的表举例WHERE name张三 ORDER BY age这个查询可以走idx_name_age联合索引因为name先等值定位age天然已经排好序MySQL按顺序读出来就行不需要额外排序。但如果写成WHERE name张三 ORDER BY created_atcreated_at不在联合索引里索引顺序对created_at没有任何帮助MySQL只能先查出所有符合name张三的记录再在内存或磁盘里按created_at排一遍。优化这类SQL的思路是尽量让ORDER BY字段成为索引的一部分且排序方向和索引的排序方向一致。MySQL 8.0开始支持降序索引可以在创建索引时指定DESC让反向排序也能直接利用索引顺序。实际工作中我建议优先关注高频查询里的排序字段把它们合并进现有联合索引同时反复用Explain确认Extra里不再出现Using filesort。这里还要提醒一个问题ORDER BY和WHERE不是孤立看的。当你把排序字段加入联合索引时要遵循前面说的最左前缀法则。WHERE里面命中的等值条件应该排在前面排序字段放在后面。反过来如果排序字段跳过了某列索引还是帮不上忙。5.2 深分页的三种改造方式分页查询是另一个重灾区。SELECT * FROM user ORDER BY id LIMIT 100000, 20表面上只返回20条但MySQL为了凑够这20条需要从头数到第100000条把中间所有记录都读一遍。如果查询语句还要回表取整行那代价就是十万级的回表操作。我的改造方案通常有三种第一种是延迟关联也就是子查询先只查主键再做表关联。比如SELECT u.* FROM user u JOIN (SELECT id FROM user ORDER BY id LIMIT 100000, 20) tmp ON u.id tmp.id;子查询里只取id这个查询可以走主键索引避免对大量完整行做回表排序和分页都在索引层面完成最后再对20条记录回表取完整数据。这是性能提升最明显、适用面也最广的方式。第二种是键值分页适合那种只需要上下翻页的场景。核心思路是记住上一页最后一条记录的id下一页直接WHERE id 上一个id ORDER BY id LIMIT 20。对于大数据量的分页这种方式的性能几乎是恒定的不会随着页码变大而变差。前提是业务能接受不能跳页。第三种是覆盖索引加回表。如果表结构复杂延迟关联也不好写你可以先确保分页和排序字段在一个覆盖索引里让MySQL只扫索引来完成后续回表。这个方案的普适性不如前两种但可以作为备选。5.3 JOIN 查询的连接字段与驱动表选择多表关联查询的优化Explain里的信息更加丰富。你会发现执行计划有多个行每一行对应一张表的访问方式。第一行通常是被驱动还是驱动这里要看id和table来判断但大多数情况下先执行的是驱动表后执行的是被驱动表。原则只有一个小表驱动大表。驱动表越小遍历的次数越少被驱动表最好有索引这样每拿到驱动表的一条记录就能在索引上快速匹配到对应的行。如果Explain里出现了Using join buffer说明被驱动表的连接列没有可用的索引MySQL只能把一批批数据缓存在内存里慢慢比对这种查询优化空间极大。还有一个非常隐蔽的坑连接列的类型和字符集不一致。比如一张表是utf8另一张表是utf8mb4在JOIN ON条件里做字符集转换会让索引失效。遇到这种现象你用Explain看到的type可能还是ref或eq_ref但实际执行效率却差很多。排查时可以去SHOW CREATE TABLE核对两个连接字段的字符集和排序规则确保它们一致。连接字段如果是varchar另一边可能是数字也会引发隐式转换优化器可能放弃被驱动表的索引。这类问题我在线上见过不止一次外表看起来一切正常慢就慢在被驱动表无法利用索引快速命中。6. 常见问题排查与避坑速查6.1 索引建了却不用统计信息与优化器判断最常见也最让人头疼的现象是索引明明存在条件也符合最左前缀Explain却显示possible_keys里有这个索引key却是NULLtype直接ALL。很多开发者这时候会怀疑是不是MySQL有bug其实绝大多数情况下是优化器在“成本核算”后认为走全表扫描更划算。什么时候优化器会觉得全表更划算一是表的数据量很小比如只有几百行全表扫描的IO成本本身就极低优化器懒得用索引二是索引区分度太低比如某列只有两三个不同值走索引要扫大量重复值再回表成本比全表还高三是统计信息过期实际数据分布已经变了优化器却还拿着旧统计信息做判断。针对统计信息过期的情况执行一下ANALYZE TABLE user;然后重新Explain很可能优化器就换计划了。如果是区分度问题那就别硬走索引要么调整查询方式要么重新设计索引组合。如果业务确实需要强制走某条索引做验证可以临时用FORCE INDEX但我必须强调这是临时验证手段不是长期解决方案。长期方案永远是把索引结构和表数据分布匹配好。6.2 生产环境加索引怎么减少锁影响优化后发现需要新增索引很多人在生产环境直接ALTER TABLE结果在高峰期把线上业务拖死了。这个教训我栽过一次以后每次加索引都格外小心。MySQL 8.0的ADD INDEX虽然支持INPLACE算法但大表执行时仍然可能产生较长的DDL时间期间会对表造成锁影响也可能引起主从延迟明显增大。我的建议是小表可以直接执行大表优先考虑在业务低峰期执行如果表已经超过几百万行建议用pt-osc或gh-ost这类在线DDL工具它们能通过临时表和数据拷贝的方式把锁的影响降到最低。执行前还要用SHOW PROCESSLIST确认当前没有长时间运行的事务因为DDL会等待元数据锁一旦前面有事务卡住后面所有对这个表的访问都会被堵上。这个坑其实非常常见一条慢事务拖住DDLDDL再把所有查询堵死最终形成连环故障。6.3 Explain结果和线上表现不一致的排查思路Explain显示typerange、rows也不大可是线上执行就是慢这种情况怎么查我的排查顺序是这样的。先用EXPLAIN ANALYZE看真实执行计划。EXPLAIN ANALYZE会真正执行SQL并输出每步实际扫描的行数、耗时和循环次数。和普通Explain的估算值对比如果实际行数远大于估算行数基本可以断定是统计信息和真实数据严重脱节。这时候ANALYZE TABLE刷新统计信息后问题往往就缓解了。其次检查SQL文本是否真的和数据库里执行的一致。现在很多应用用了ORM框架或者MyBatis动态拼接SQL很容易出现“你以为传了参数实际传过去的是另一个类型”。变量类型一变隐式转换就来了执行计划可能被改写。遇到这种情况把实际打印出来的SQL拿过来Explain一遍通常能发现问题。还有一种不稳定因素优化器的选择本身就不是一致的。同一条SQL在不同时间点执行因为表数据分布、系统负载的变化优化器可能做出不同选择。碰到这种不稳定执行计划建议先从索引设计上下手把执行计划往稳定方向引导而不是每次都靠运气。6.4 一份可以直接抄的避坑速查表把前面讲的内容浓缩成一张排查对照表遇到慢SQL时可以快速定位问题方向现象优先排查方向typeALLrows接近全表行数查询条件列有没有合适索引是否触发隐式转换或函数运算possible_keys有索引key为NULL统计信息过期执行ANALYZE TABLE检查区分度检查SQL写法Extra出现Using filesort排序字段能否被索引覆盖ORDER BY顺序是否匹配索引Extra出现Using temporary检查GROUP BY/DISTINCT列能否借联合索引实现分组Extra出现Using join buffer被驱动表连接列是否缺索引连接列类型和字符集是否一致执行计划看起来正常但线上慢用EXPLAIN ANALYZE对照真实行数核对统计信息、SQL实际参数这张表不是我凭空列出来的每一条都对应着真实处理过的案例。比如JOIN连接列字符集不一致那次Explain里type还是eq_ref看起来完美实际上慢到让人怀疑人生。所以最后分享一个我自己的习惯拿到一条慢SQL先看Explain但永远不只信Explain。核心还是回到表结构、索引设计、字段类型和字符集这些基础参数上。把基础细节核对清楚大部分性能问题都能在源头解决。