MySQL索引优化实战：从B+树原理到EXPLAIN排查指南-开发者社区

先去对比一下实际执行计划再说话

MySQL 索引优化这事，网上教程一抓一大把，但多数人看完还是只会背“最左前缀”“不要用函数”这种口诀。真正在线上业务里踩过坑的人都知道，索引能不能生效、该不该建、建几列，每一步都需要结合数据和执行计划来判断。今天我就围绕 MySQL 索引优化，把从原理到实操、从设计到排查这一整套东西串起来讲一遍，希望对正在折腾索引的朋友有点实际帮助。

这篇文章适合三类人：一类是刚接触 MySQL 索引、想系统搞懂原理的新手；另一类是写过简单 SQL 但经常被慢查询折磨、想搞清楚索引为什么失效的开发；还有一类是要接手线上数据库、需要做索引审查和维护的 DBA。内容不会太理论，重点放在“怎么设计索引、怎么验证效果、怎么排查问题”上。

1. 索引提速的核心原理：从 B+ 树存储说起

1.1 为什么 MySQL 偏偏选了 B+ 树

索引的本质是一种数据结构，目的就是让数据库不需要扫描全表就能快速定位数据。MySQL 默认存储引擎 InnoDB 使用的是 B+ 树，这一点几乎所有教程都会提，但很少有人讲清楚它相对于其他结构的优势。

B+ 树有几个关键特征：

所有数据都存放在叶子节点，非叶子节点只存储索引键值和指针，因此单个节点能容纳更多键值，树的高度被压得很低。InnoDB 的默认页大小是 16KB，三层 B+ 树大概能存储千万级数据行，这意味着一次普通查询走主键索引只需要经历三次磁盘 I/O。
叶子节点之间通过双向指针串联，形成了有序的双向链表，对于范围查询、排序操作非常友好。比如你要查询某个时间段内的订单，命中索引后直接顺着叶子节点链往下读就行，不需要反复回溯树结构。
数据是按索引列排序存储的，所以走索引可以避免额外的排序操作，这就是为什么有些查询建立索引后能从“Using filesort”变成“Using index”的原因。

哈希索引虽然单点查询更快，但它不支持范围查询，也不支持排序，所以 InnoDB 虽然有自适应哈希索引功能，但默认的索引结构依然是 B+ 树。

还有一种常见疑问是：为什么不直接用红黑树或二叉查找树？这两种树的查找时间复杂度是 O(logN)，看起来也不差，但问题是树的高度会随数据量线性增长。对于磁盘存储来说，每次树层级的下降都可能触发一次磁盘 I/O，而内存随机访问的速度远比磁盘快，所以 B+ 树通过扁平化结构把树的层级压到极低，本质上是在和磁盘 I/O 做斗争。理解了这个逻辑，你就能明白为什么说“索引不是越多越好，而是越有用越好”——每一条索引都是一棵独立的 B+ 树，而树是有物理存储成本的。

1.2 聚簇索引与二级索引：数据到底存哪里

InnoDB 里主键索引又叫聚簇索引，它的叶子节点直接存放整行数据。也就是说，你建表时指定的主键本身就会生成一棵 B+ 树，而表里的数据就按这棵树的叶子节点顺序物理存放。这就是为什么 InnoDB 表必须要有主键的原因——如果没有显式主键，InnoDB 会选择一个唯一的非空索引作为聚簇索引，实在没有就隐式生成一个 rowid。

二级索引（也就是普通索引）则完全不同，它的叶子节点存放的是索引列的值加上主键值。注意到没有，它不存整行数据。所以当你通过普通索引查询数据时，要走两步：

从二级索引的 B+ 树中找到匹配的主键值。
再通过主键值到聚簇索引的 B+ 树中回表查询完整行数据。

这个“回表”是索引优化中一个非常重要但又容易忽略的细节。回表的次数越多，性能损耗越大。如果一次查询命中了 1000 条记录，每条都要回表，那实际上就是 1000 次随机主键查询，比全表扫描好不到哪去。这也是为什么“覆盖索引”这个概念这么重要，它指的就是二级索引里已经包含了你需要的所有字段，压根不需要回表。

理解了聚簇索引和二级索引的差别，很多索引设计问题就迎刃而解了。比如二级索引里放的是主键值，所以主键字段长度越短，二级索引的体积就越小，占用的磁盘空间和内存也就越少。这就是为什么推荐用自增整数做主键，而不是用 UUID 字符串——长字符串主键不仅让聚簇索引体积膨胀，还会让所有二级索引跟着膨胀。从数据结构的角度看，索引优化本质上就是在控制访问路径的长度和存储体积，返回的每一行数据都对应一次磁盘访问，而这些访问路径是由数据结构和索引结构共同决定的。

2. 高效索引的设计思路：先考虑业务查询模式

很多人在建索引时，习惯对着表结构按字段逐个添加，以为这样就能覆盖所有查询。这种思路的问题在于，索引不是字段的堆砌，而是为查询设计的路径。真正高效的索引设计，必须从让 SQL 语句的 WHERE 条件、排序、分组和 Join 字段驱动设计。

2.1 查询需求反推索引字段：先收集慢查询

我在拿到一个新项目或者接手一张新表时，第一件事不是急着写 CREATE INDEX，而是先做三件事：

收集线上慢查询日志，找出最频繁出现的几条慢 SQL。
查看业务方最常用的列表查询和详情查询，确认 WHERE 子句的过滤条件。
分析表的数据分布，看有哪些字段是高区分度、哪些是低区分度，哪些字段会被用于 ORDER BY 和 GROUP BY。

为什么这样做？因为索引优化的目标是让高频查询的代价降到最低。如果业务上根本没人在意某条查询，它再慢也没有太大风险；但如果一张表的详情查询每次都要扫全表，那这就是必须优化的重点对象。所以，索引设计一定是围绕需求驱动的，而不是单纯围绕字段类型。

以电商订单表为例，最频繁的查询往往是“查某个买家最近的订单列表”，这个查询的过滤条件通常是user_id和create_time，排序通常是create_time DESC。那么一条复合索引(user_id, create_time)就是值得优先考虑的方案。而如果你只对user_id建单列索引，查询已经能定位到买家的所有订单，但还要额外做一次排序，效率和覆盖面都不如复合索引。

2.2 组合索引的列顺序：不是随便排列

组合索引的列顺序是整个索引设计里最核心的决策之一，也是最容易出错的地方。MySQL 的组合索引遵循最左前缀原则，具体来说：

假设你创建了一个组合索引(a, b, c)，那么实际生效的查询条件有：

a
a, b
a, b, c

也就是说，查询条件里没有包含最左边的列 a 时，这个索引基本不会被使用（除了 8.0 的索引跳跃扫描能做少量优化）。

真正难的是既然组合索引只能匹配最左前缀，那当多个查询条件并存时，到底哪个字段放最左边？我自己的经验是按照这样几条原则来排：

区分度最高的列放在最前面，因为索引的目的是最快地缩小范围。
经常被用于范围查询的列放在最后面，比如create_time > ?。
经常被用于排序的列尽量按排序方向排列进索引，这能消除文件排序。

拿实际例子说：订单查询有user_id = ?和create_time排序，那么(user_id, create_time)是合理的；但如果是查询“某个时间范围内所有订单”，没有用户ID这个等值条件，那同样这条索引就用不上，反而需要单独建(create_time)索引。所以索引设计必须跟随实际的查询条件，而不是看哪个字段“看起来重要”。

这里我特别强调一个观点：区分度高的列放前面的原则，并不绝对适合所有场景。如果某个区分度高的列是范围查询，比如status字段区分度很低但经常用于 IN 查询，那把它放在最前面反而会让索引扫描成本变大。最怕的是设计索引时只看字段名，不去看 WHERE 条件里到底是等值还是范围，这个坑我踩过不止一次。

2.3 前缀索引与函数索引：解决字段长度和表达式问题

假如表中有一个字段存的是长字符串，比如用户邮箱email字段，值基本唯一，但整个字段长度可能接近100个字符。此时如果直接对整列建索引，索引体积会很大，产生额外磁盘占用和写入开销。更好的方案是只取字符串的前几个字符（比如前8个字符）来建前缀索引，这样能大幅缩小索引体积，同时保持足够的选择性。

创建语法很简单：

ALTER TABLE user ADD INDEX idx_email_prefix (email(8));

但这里有个前提，你必须验证前缀长度是否足够区分。推荐这么测试：

SELECT COUNT(DISTINCT LEFT(email, 4)) / COUNT(*) AS sel4, COUNT(DISTINCT LEFT(email, 8)) / COUNT(*) AS sel8, COUNT(DISTINCT LEFT(email, 12)) / COUNT(*) AS sel12 FROM user;

通常当选择性比例超过 0.9 时，前缀长度就基本可用了。取太短会导致大量冲突，取太长则失去前缀索引的压缩优势。前缀索引也有个麻烦，它无法用于覆盖索引，因为索引中只存了一部分字段值，查询返回完整值时还是得回表。

MySQL 8.0 还引入了函数索引的功能，语法上是在索引定义里直接使用表达式：

ALTER TABLE user ADD INDEX idx_email_domain ( (SUBSTRING_INDEX(email, '@', -1)) );

这解决了以前“WHERE SUBSTRING_INDEX(email, '@', -1) = 'example.com'”这类查询无法走索引的问题。但在老版本里没有这个功能，只能通过生成列或者改写查询逻辑来绕过，这也是我在历史项目中经常碰到的一个实用性痛点。

2.4 覆盖索引带来的额外收益

前面提过，覆盖索引的二级索引里包含了查询需要的所有列，因此不需要回表。不光如此，由于二级索引通常比聚簇索引体积小，覆盖索引扫描的叶子节点也更少，整体 I/O 成本明显低。尤其在统计类查询中效果显著，比如：

SELECT COUNT(*), status FROM orders GROUP BY status;

如果能建立一个(status)索引，这个查询可以只扫索引而不是全表，几乎任何时候都比全表扫描快一个量级。对于高并发、查询频率极高的场景，建立覆盖索引是比盲目加内存缓存更直接的优化手段。我之前在某个业务里做过一次优化，原本一个统计接口经常触发慢查询，最后仅仅加了一条覆盖索引，耗时就从 800 毫秒降到 80 毫秒，这就是回表被完全消除的威力。

3. 实操：一条索引从创建到验证的全流程

3.1 建索引的常见语句及注意事项

MySQL 里常见的索引类型有普通索引、唯一索引、全文索引、组合索引和前缀索引。日常优化用得最多的是普通索引和组合索引，下面这些语句都是实际开发里高频出现的形式：

-- 创建普通索引 CREATE INDEX idx_user_id ON orders (user_id); -- 创建唯一索引，常用于保证业务字段唯一性 CREATE UNIQUE INDEX idx_order_no ON orders (order_no); -- 创建组合索引 CREATE INDEX idx_user_time ON orders (user_id, create_time); -- ALTER TABLE 方式添加索引 ALTER TABLE orders ADD INDEX idx_status (status); -- 删除索引 DROP INDEX idx_status ON orders;

这里有几个细节要特别提醒：

索引命名要统一规范，比如idx_字段名、uniq_字段名，方便后续排查和维护。生产环境索引非常多时，命名混乱会浪费大量维护时间。
不要在频繁更新的列上建过多索引，每次 UPDATE / INSERT 都会同步维护索引，索引数量越多，写入链路就越重。这就像每加一个索引，就是为这本书多追加一套目录，写新内容时目录同步更新也得很及时。
大表建索引要选业务低峰期操作，因为 InnoDB 在建立索引时会对表加共享锁，数据量一大容易阻塞业务。常规做法是使用在线 DDL（Online DDL）特性，MySQL 5.6 之后已经支持，但依然不建议在高峰期操作。

3.2 EXPLAIN 不只看 type，还要看这几列

建完索引一定要用EXPLAIN验证索引是否真的生效。不少初学者看到EXPLAIN结果里有index关键字就以为万事大吉，这是最大的误区。一条 SQL 能不能高效执行，重点要看下面这几列：

列名	含义	排查重点
type	访问类型	如果出现 all，说明全表扫描，需要重点优化
key	实际使用的索引	为 NULL 说明没用索引，需要检查 WHERE 条件
rows	预估扫描行数	这个值越大说明过滤性越差，需要调整索引
Extra	附加信息	出现 Using filesort / Using temporary 需要优化排序或分组

我通常最关注type和Extra这两列。type 从好到坏大致是：

system：表只有一行，基本不出现。
const：主键或唯一索引等值查询，只需扫描一行。
eq_ref：唯一索引扫描，多表 JOIN 时很常见的优秀级别。
ref：普通索引等值查询。
range：索引范围扫描，比如BETWEEN、>、<等操作。
index：全索引扫描，即遍历整棵索引树，通常只比全表扫描好一点。
ALL：全表扫描，最差情况。

Extra 里如果出现Using filesort，说明 MySQL 需要自己排序，而不是顺着索引读出来的天然顺序，这个代价往往很高。如果出现Using temporary，说明查询使用了临时表，常见于 GROUP BY、去重或某些子查询场景，这两个词一出现基本就意味着这条 SQL 还有优化空间。

实际执行 EXPLAIN 的操作如下：

EXPLAIN SELECT id, user_id, amount FROM orders WHERE user_id = 1024 ORDER BY create_time DESC;

假设建了(user_id, create_time)的组合索引，type 应该是 ref，Extra 里不会出现Using filesort，因为组合索引的第二列天生就是按 create_time 排好的。如果只建了(user_id)单列索引，那么 type 虽然还是 ref，但 Extra 里会出现Using filesort，这说明排序没有走索引，需要额外排序。

3.3 覆盖索引和索引下推的实际效果对比

MySQL 5.6 引入了一项优化叫“索引条件下推（ICP）”，它允许 MySQL 在存储引擎层直接过滤掉不符合二级索引条件的更多记录，减少回表次数。举个例子：

假设表 people 上有索引(zipcode, lastname)，执行这样一条查询：

SELECT * FROM people WHERE zipcode = '10001' AND lastname LIKE '%Zhang%';

在 ICP 出现之前，存储引擎只能根据 zipcode 找到所有匹配记录，然后在服务器层对 lastname 做 LIKE 过滤，回表次数很多。启用 ICP 之后，存储引擎直接利用 lastname 的条件在索引内部过滤，只有完全符合的记录才回表，显著降低 I/O 量和回表次数。

像这样的细节如果只看 type 列根本意识不到，需要结合Extra里的Using index condition来确认。这也是我建议所有做索引优化的人，一定要养成看完整 EXPLAIN 结果习惯的原因。本来一个 SQL 就不该只看执行结果正不正确，还要看得快不快，看它走的路径是否是最短的一条。

另外，关于覆盖索引实际做验证的时候，可以用这样的小技巧：把查询字段改成索引字段，看 EXPLAIN 里是否出现Using index。如果出现，说明索引已经覆盖了查询需要的所有列：

EXPLAIN SELECT user_id, create_time FROM orders WHERE user_id = 1024;

这种情况下无需回表，性能自然最好。

4. 索引优化中常见的坑与排查心得

4.1 隐式类型转换让索引失效

这是线上最典型、出现频率最高的问题之一。比如 phone 字段是VARCHAR类型，查询时写了WHERE phone = 13800000000，数字是 INT 类型，MySQL 在比较时会尝试把字符串字段转换为数字，一旦对字段本身做了函数式的隐式转换，索引就无法使用。实际排查时用EXPLAIN一看，type 从 ref 变成了 ALL，小表还好，大表直接刷慢查询。

解决办法有两个层面：第一是 SQL 层面，把参数值写成字符串形式，如WHERE phone = '13800000000'；第二是表结构层面，如果这个字段实质就是数字，干脆从一开始就建成BIGINT类型，这也是字段设计时该考虑清楚的一个点。

4.2 函数表达式包裹字段导致索引失效

同样常见的还有在 WHERE 子句里对索引字段做函数计算：

SELECT * FROM orders WHERE DATE(create_time) = '2024-01-15';

这里对create_time做DATE()函数处理，MySQL 无法直接利用索引。正确做法是把查询条件改写为时间范围：

SELECT * FROM orders WHERE create_time >= '2024-01-15 00:00:00' AND create_time < '2024-01-16 00:00:00';

这种改写不仅能用上索引，语义上也更精准。还有一个常见场景是LEFT(name, 3) = '张'这种函数写法，本质上也是牺牲了索引的使用。如果业务上频繁需要这种查询，在 8.0 里可以考虑函数索引，在低版本里就得考虑生成列或者干脆接受全表扫。

4.3 LIKE 查询如何使用索引

LIKE 查询的索引使用规则很多人记不清，我直接说结论：

LIKE 'abc%'可以用索引，因为前缀是确定的。
LIKE '%abc%'基本不能用索引（除非有全文索引或倒排索引来解决）。
LIKE '%abc'也不能正常走索引。

实际上在 MySQL 8.0 之前的版本里，%abc%这种模糊搜索想走普通 B+ 树索引几乎不可能，只能用全文索引或外部搜索引擎。MySQL 8.0 之后引入了全文索引的 ngram 解析器，对中文模糊查询有一定改善，但也不是万能药。如果你真的频繁要做包含式模糊匹配，我更建议考虑引入搜索组件或独立搜索引擎，而不是指望普通索引优化出奇迹。

4.4 索引冗余与维护成本

线上库的索引数量往往比想象中多，而冗余索引是慢查询以外又一个容易忽视的问题。比如你已经有一个组合索引(a, b)，再单独建一个(a)单列索引，两者就存在明显冗余。原因是(a, b)已经完全覆盖了以a为前缀的查询路径，单列索引(a)几乎派不上用场。

在 MySQL 5.7 及更早版本里没有直接识别冗余索引的视图，我一般用系统库的统计信息来辅助判断：

SELECT table_name, index_name, column_name, seq_in_index FROM information_schema.statistics WHERE table_schema = 'your_db' ORDER BY table_name, index_name, seq_in_index;

8.0 之后的版本还提供视图sys.schema_redundant_indexes可以直接查看冗余索引。维护索引数量和业务查询频率之间需要平衡，我个人的建议是：黄金法则是每张表索引数量控制在 5 个以内，如果超过这个数一定要逐一审视是否真的被高频查询使用。索引归一化和清理是一个长期持续的工作。

4.5 排序带来的隐藏性能风险

有时候明明查询过滤行的条件很简单，但执行计划里偏偏出现 Using filesort，导致查询变得很慢。原因是 ORDER BY 字段没有和 WHERE 条件共同组成合适的复合索引。比如这个查询：

SELECT user_id, amount FROM orders WHERE status = 'PAID' ORDER BY create_time DESC;

如果只有单列索引(status)，那么 MySQL 得先查出来所有 status='PAID' 的记录，再对这些记录按 create_time 做外部排序。一张千万级的大表，即使过滤后剩 10 万行，排序也会非常吃力。最直接的优化方式就是建组合索引(status, create_time)，这样索引本身就是按 status 过滤后，里面的叶子节点已经按照 create_time 排好序，完全不需要额外排序。

相同的逻辑放到 GROUP BY 也一样，(status, create_time)可以让分组和排序一起受益。很多时候一条复合索引能同时解决过滤、排序、分组三件事，远比你单独为每一个字段各建一个索引高效得多。这一点我强烈建议在索引设计阶段就列出一个业务查询清单，把高频 SQL 的 WHERE 条件和 ORDER BY 字段全部统计出来，然后统一计算覆盖度。

5. 线上索引优化的完整流程与自我复盘

从发现慢查询到完成索引优化，我自己有一套固定的流程，走了一遍又一遍，踩过的坑也积累了不少，现在分享出来大家可以参考：

收集慢查询：开启慢查询日志，设置long_query_time = 1，定期分析慢查询记录。
定位高频慢 SQL：把慢日志按查询次数排序，优先处理频率最高、单次耗时最长的 SQL。
查看表结构：SHOW CREATE TABLE table_name，把现有索引摸清楚，避免建立重复索引。
核对索引使用情况：基于performance_schema或sys.schema_unused_indexes来查看哪些索引从未被使用。
设计索引方案：根据 SQL 的 WHERE、ORDER BY、GROUP BY 字段设计组合索引，并考虑列顺序。
EXPLAIN 验证：执行 EXPLAIN 对比优化前后的 type、rows、Extra。
观察线上效果：上线后监控慢查询数量是否下降，锁等待和 I/O 压力是否改善。

整个过程最重要的是，不要凭感觉给业务加索引，一定要通过真实数据和执行计划来验证。我见过太多人上来就抄网上现成的索引优化矩阵，结果某个字段在业务表里区分度很低，建了索引反而让写入变慢。索引优化永远是个“具体问题具体分析”的活。

最后再分享一个我自己经常用的小经验：优化一个慢查询，不要只看单条 SQL 的 EXPLAIN，还要看它的实际执行频率。一个每天执行几百万次的百万行表查询，即使每次省下 10 毫秒，整体收益也远大于一个每天只执行几次、但单次优化掉 1 秒的查询。索引设计最终服务的是整体业务链路，而高效索引的本质，是用合适的数据结构去匹配真实的数据访问模式。设计索引时多想一步，未来整个业务的数据访问就平稳一分。