MySQL索引优化实战：从设计到失效排查全攻略-编程实验室

做过几年的 MySQL 开发和 DBA 工作之后，我越来越发现一个现象：很多人写 SQL 很熟练，但只要一遇到慢查询，第一反应就是“加个索引试试”。索引这东西，用好了确实是灵丹妙药，用不好反而会拖垮写性能。所以这篇文章不打算像教科书那样从头到尾讲一遍索引定义，我就直接结合这些年实际踩过的坑，把 MySQL 索引从设计思路、实操落点到失效排查整个链路拆开来讲，力求你看完能直接拿来用。

这篇文章适合这几类人看：刚入门 MySQL、搞不懂为什么明明建了索引查询还是慢的后端开发，被线上慢查询逼到加班、却只会往表上乱加索引的运维同学，以及准备面试、想系统梳理索引知识点的求职者。内容不啰嗦，每一节都是能落地的经验，尤其最后的失效场景排查，基本覆盖了我这几年在真实环境里遇到过的绝大多数情况。

1. 先弄清楚索引到底在解决什么问题

1.1 索引的本质：让数据库走捷径

很多人都知道索引类似于书的目录，这个类比没有错，但我想往深处再走一步。书的目录帮你定位到某一页，数据库的索引则帮你定位到某一行数据所在的物理位置。在没有索引的情况下，MySQL 要做全表扫描——也就是把整张表的每一行都读一遍，然后过滤出符合条件的记录。表小的时候无所谓，但一旦数据量到百万、千万级别，全表扫描的耗时几乎不可接受。

索引的本质，是拿额外的存储空间和维护成本，换取查询时更少的磁盘 I/O 和更快的定位速度。我们常说“空间换时间”，这是最核心的底层逻辑。每次插入、更新、删除数据时，索引结构都要同步维护，所以索引不是免费的午餐，它是有代价的。

1.2 从数据结构和磁盘IO角度理解为什么是B+树

MySQL 的 InnoDB 存储引擎，默认索引结构是 B+ 树。为什么不是二叉树，不是哈希表，不是跳表？这个问题的答案，直接决定了你对索引机制的深度理解。

先说哈希表。哈希索引的查询速度理论上是 O(1)，但它只能做等值查询，范围查询（比如age > 18）就废了。而且哈希表是无序存储，排序也帮不上忙。所以 InnoDB 并没有把哈希作为默认索引，只在自适应哈希索引的场景下作为辅助优化。

再说二叉树。二叉搜索树的查询效率是 O(log n)，但这个复杂度是建立在树高度“矮”的前提下的。如果数据是递增插入的，二叉树会退化成链表，树的高度变得非常深。盘 I/O 是按页读的，树每深一层，就可能多一次 I/O。一个两千万行的表，如果树高度是 20 多层，查询一次就要做 20 多次磁盘 I/O，这在生产环境里是灾难。

B+ 树把多个数据放在一个节点里，每个节点对应一个磁盘页（默认 16KB），一次 I/O 就能读入大量键值。它的核心特点有三个：

非叶子节点只存索引键，不存真实数据，所以一个节点能容纳更多键，树更矮更宽。
叶子节点之间通过双向指针串联，范围查询和排序可以直接顺序遍历。
所有数据都落在叶子节点，查询路径稳定，IO 次数基本等于树的高度。

记住这几个特点，后面理解联合索引、覆盖索引、索引下推都会容易很多。B+ 树的高度一般 3 到 4 层就能支撑千万级数据，也就是说，走索引查询通常只需要 3 到 4 次磁盘 I/O，相比全表扫描，性能差距是指数级的。

2. MySQL索引的几种类型和选型逻辑

2.1 主键索引、唯一索引、普通索引的差异

MySQL 的索引类型很多，但建索引之前，你得先搞懂每种类型的定位。用错了，轻则浪费空间，重则影响写入性能。

主键索引（PRIMARY KEY）：每个 InnoDB 表只能有一个主键，它的特点是不能为空且值唯一。InnoDB 是聚集索引组织表，数据行本身就按主键的 B+ 树排序存储，所以主键索引的叶子节点存的是整行数据。这就是为什么主键查询往往极快的原因——不需要回表，直接拿到全部列。
唯一索引（UNIQUE KEY）：保证列或列组合的值唯一，但允许 NULL 值存在（多个 NULL 不算冲突）。它在业务上的价值是防止重复数据，比如用户表的手机号字段，很适合加唯一索引。
普通索引（INDEX）：只加速查询，不约束唯一性。它的叶子节点存的是主键值，查询时如果需要的列不在索引里，就要根据主键回表查整行。
全文索引（FULLTEXT）：专门做文本匹配用的，在 MySQL 5.7 之后也支持中文分词。日常的LIKE '%关键词%'无法走普通索引，如果业务确实需要全文检索，要么上全文索引，要么引入 ES 之类的搜索引擎。
空间索引（SPATIAL）：处理地理位置数据，平时业务里用得很少，这里就不展开了。

2.2 联合索引设计与最左前缀原则

真实业务里，单列索引往往搞不定复杂查询。比如最常见的订单查询：WHERE user_id = ? AND status = ? ORDER BY create_time DESC。这时候就轮到联合索引出场了。

联合索引，也叫多列索引，本质上是把多个列按顺序拼成一个复合键，存到一棵 B+ 树里。举个例子，联合索引(user_id, status, create_time)，它的排序逻辑是：先按user_id排序，user_id相同的再按status排序，status也相同的继续按create_time排序。

这个排序逻辑直接决定了最左前缀原则：查询条件里必须包含联合索引最左边的列，索引才会被用到。比如：

WHERE user_id = ?：能走索引。
WHERE user_id = ? AND status = ?：能走索引。
WHERE status = ?：不能走索引。
WHERE status = ? AND user_id = ?：在 MySQL 优化器有条件下可能走索引，因为优化器会做等价改写，但这不代表你可以乱写条件顺序。

联合索引的列顺序是设计的关键。通常遵循两个原则：

区分度高的列放前面，比如用户ID、订单号，这类列能快速缩小范围。
经常用于排序、分组的列尽量包含进来，让 B+ 树直接利用索引完成排序，避免 filesort。

这里还要重点说一个很多人忽略的问题：联合索引不是建得越长越好。每多一个列，索引占用的空间就大一圈，插入和更新的维护成本也更高。我见过有人把一张表 20 个字段建了 8 个联合索引，最后写入延迟高得吓人。索引设计一定要克制。

2.3 全文索引和哈希索引的使用边界

聊到哈希索引，最容易踩坑的是把 MySQL 的哈希索引和 InnoDB 的自适应哈希索引混为一谈。InnoDB 的自适应哈希索引是存储引擎内部自动优化的，你感知不到也不需要干预。而真正能手动创建的哈希索引，只存在于 MEMORY 存储引擎里，InnoDB 并不支持直接创建哈希索引。

如果你确实需要等值查询的极致速度，又不想引入 Redis，可以考虑在表上冗余一个字段存哈希值，然后用普通索引去索引这个哈希列。比如 URL 这种内容长又需要精确匹配的字段，做法是加一个url_hash列存 CRC32 或 MD5 值，再对这个列建索引。MySQL 8.0 还支持函数索引，也可以直接对CRC32(url)建索引。不过这个方案只解决等值匹配，范围查询还是得靠 B+ 树。

全文索引现在用得也不少，尤其是对长文本字段做站内搜索。但要注意，全文索引并不是银弹，它对短文本的效率一般，分词器的效果也需要调。真正信息量大的全文搜索，还是建议交给专业的搜索引擎。

3. 添加索引的正确姿势与实操细节

3.1 创建索引的标准语法与命名规范

先给出一套可以直接抄的语法。

-- 创建普通索引 ALTER TABLE `order` ADD INDEX idx_user_id (`user_id`); -- 创建唯一索引 ALTER TABLE `user` ADD UNIQUE KEY uk_mobile (`mobile`); -- 创建联合索引 ALTER TABLE `order` ADD INDEX idx_user_status_time (`user_id`, `status`, `create_time`); -- 删除索引 ALTER TABLE `order` DROP INDEX idx_user_id;

如果你在建表时就确定索引，也可以在CREATE TABLE语句里直接定义：

CREATE TABLE `order` ( `id` bigint NOT NULL AUTO_INCREMENT, `user_id` bigint NOT NULL, `status` tinyint NOT NULL DEFAULT 0, `create_time` datetime NOT NULL, PRIMARY KEY (`id`), KEY idx_user_status_time (`user_id`, `status`, `create_time`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

索引命名这块，团队之间最好统一规范。我常用的规则是：

普通索引用idx_前缀 + 业务字段名。
唯一索引用uk_前缀 + 业务字段名。
联合索引把多个列名缩写后用下划线连接，比如idx_user_status_time。

命名规范看起来是小事，但等你接手一个几十张表的老项目，看到一个索引叫index2或者Index_3，想死的心都有。

3.2 如何通过EXPLAIN判断索引是否生效

索引建好了，它到底有没有被查询走？不能靠猜，要用EXPLAIN看执行计划。

EXPLAIN SELECT user_id, status FROM `order` WHERE user_id = 100 AND status = 1;

执行结果里重点关注这几列：

type：这是访问类型，性能由好到差依次是system>const>eq_ref>ref>range>index>ALL。如果看到ALL，说明是全表扫描，索引基本没起作用。
key：实际用到的索引名。如果为 NULL，说明没走索引。
rows：预估扫描的行数。值越小越好，如果扫描行数接近全表行数，就要反思查询条件了。
Extra：这个列信息量很大。出现Using filesort说明排序没用上索引，出现Using temporary说明用了临时表，出现Using index说明是覆盖索引。

举个例子，假如之前那条 SQL 的type是ALL，rows是 500 万，说明这条 SQL 正在全表扫描 500 万行，这就是慢查询的根源。加了联合索引之后再看，type变成了ref，rows缩减到几十行，这个优化效果肉眼可见。

3.3 一个慢查询优化的完整案例

这里分享一个真实业务里优化过的场景，非常典型。有一张订单流水表，数据量 1500 万左右，线上突然出现一条慢查询，耗时稳定在 3 秒以上。

原 SQL 大概是这样的：

SELECT id, user_id, order_no, amount, status, create_time FROM order_flow WHERE status = 1 AND create_time >= '2024-01-01 00:00:00' ORDER BY create_time DESC LIMIT 10;

表上原有的索引是idx_create_time (create_time)。用EXPLAIN查看后发现，这条 SQL 走了create_time索引，但rows依然高达几十万。原因也很简单：status = 1的数据非常多，即使只查最近半年的数据，也要在索引里扫描大量记录，然后再回表过滤。

当时我给的优化方案是建一个联合索引：

ALTER TABLE order_flow ADD INDEX idx_status_create_time (status, create_time);

这背后的思路是：先通过status快速定位到等于 1 的区间，再在create_time上做范围扫描。因为联合索引已经包含了create_time的排序，ORDER BY create_time DESC直接走索引就完成了，不需要 filesort。优化后这条 SQL 的耗时从 3 秒降到 20 毫秒左右，效果非常明显。

这个案例其实告诉我们一个常见的经验：当单列索引遇到多条件过滤时，往往力不从心，这时候要考虑把过滤条件里的多个列组合成联合索引。但注意，也不能一上来就无脑加联合索引，还需要结合实际业务里查询条件的频率来决定列的顺序。

3.4 索引维护：碎片、统计信息和冗余索引

很多人对索引的认知停留在“建完就完了”，其实索引也需要日常维护。

第一个问题：碎片。InnoDB 在频繁的插入、删除操作后，索引页可能出现碎片，导致扫描效率下降。碎片严重时，可以使用OPTIMIZE TABLE重建表和索引。

OPTIMIZE TABLE order_flow;

但要注意，这个操作会锁表，在线业务要选在低峰期执行，或者用 Percona Toolkit 里的pt-online-schema-change做在线操作。另外，MySQL 8.0 之后OPTIMIZE TABLE对 InnoDB 的支持已经改善，但仍然建议评估好窗口再执行。

第二个问题：统计信息过期。优化器要决定走哪个索引，靠的是表的统计信息。当数据量大变之后，统计信息如果太老，优化器可能选错索引。不需要手动频繁操作，ANALYZE TABLE可以重新收集统计信息：

ANALYZE TABLE order_flow;

第三个问题：冗余索引。这是最容易忽略的。比如你已经建了idx_user_status_time (user_id, status, create_time)，又顺手建了idx_user (user_id)，后者基本是冗余的。因为联合索引最左列就是user_id，单独建一个user_id单列索引完全多余，纯属浪费空间和维护成本。

我常用的查冗余索引的姿势是看information_schema.statistics表，配合sys.schema_redundant_indexes视图（MySQL 5.7 起自带）快速找出冗余索引。

SELECT * FROM sys.schema_redundant_indexes;

4. 索引失效的典型场景和排查技巧

4.1 哪些写法会导致索引失效

这部分是面试高频题，也是日常开发最常踩的坑。我按高频程度排个序。

第一类：在索引列上做函数操作或计算。比如：

WHERE DATE(create_time) = '2024-01-01'

这种写法导致索引失效，是因为 MySQL 对列进行了函数转换，B+ 树里存的原始值和函数结果不在同一个排序空间。正确写法是：

WHERE create_time >= '2024-01-01 00:00:00' AND create_time < '2024-01-02 00:00:00'

第二类：隐式类型转换。如果索引列是字符串类型，但查询条件传的是数字，MySQL 会将字符串转为数字，然后索引就废了。比如：

-- mobile 是 varchar 类型 WHERE mobile = 13800138000

mobile列上明明有索引，但因为类型转换，全表扫描了。正确写法：

WHERE mobile = '13800138000'

第三类：LIKE 以通配符开头。LIKE '%abc'因为无法确定前缀，B+ 树无从定位起点，索引失效。但LIKE 'abc%'是可以走索引的，因为前缀是可确定的。

第四类：OR 条件里存在非索引列。比如：

WHERE user_id = 100 OR status = 1

如果status没有索引，优化器大概率选择全表扫描。正确做法是把status也加上索引，或者用UNION改写。

第五类：联合索引违反最左前缀原则。前面已经专门讲过，这里再强调一下，联合索引(a, b, c)你只查b和c，索引一定失效。

第六类：索引列参与范围查询的另一边。比如WHERE a > 100 AND b = 1，如果联合索引是(a, b)，那b的等值条件很可能用不上索引。原因是 B+ 树先按a排序，a的范围扫描已经锁定了一个区间，这个区间内b不是有序的。

4.2 常见问题速查表

把上面提到的坑整理成一张速查表，方便你排查问题的时候直接对照。

场景	索引是否生效	优化思路
`WHERE id = 100`主键等值	生效	无需处理
`WHERE user_id = 100 AND status = 1`（有联合索引）	生效	确认联合索引列顺序
`WHERE status = 1`（只有联合索引但没有最左列）	失效	补单列索引或调整联合索引
`WHERE DATE(create_time) = '2024-01-01'`	失效	改写成范围查询
`WHERE mobile = 13800138000`（mobile是字符串）	失效	修改参数类型或显式加引号
`WHERE name LIKE '%张三%'`	失效	考虑全文索引或搜索引擎
`WHERE user_id = 100 OR status = 1`（status无索引）	失效	给status加索引或用UNION
`WHERE a > 100 AND b = 1`（联合索引(a,b)）	部分生效	调整列顺序（等值在前，范围在后）
`SELECT ... ORDER BY create_time DESC`（有create_time索引）	生效	索引天然有序，避免filesort

这张表是我平时做慢查询分析时最快能派上用场的参考。建议你把它存下来，写 SQL 之前对照一遍，能省掉很多上线后才发现问题的尴尬。

4.3 几条线下线上排查经验

最后分享几条平时不怎么写进文档、但非常实用的排查经验。

一是升级 MySQL 到 8.0 之后，一定要用EXPLAIN ANALYZE验证实际耗时和行数。MySQL 8.0 的EXPLAIN ANALYZE会真正执行 SQL，并输出实际执行时间和各步骤的实际行数，比传统EXPLAIN的预估值准确得多。

EXPLAIN ANALYZE SELECT id, user_id, status FROM order_flow WHERE user_id = 100 AND status = 1;

输出里会标明每一步耗时，直接能看出来瓶颈在哪一步。

二是有些“失效”其实是优化器的主动选择。比如你查一个小表，表里只有 100 行数据，优化器算了一下觉得全表扫描比走索引更快，于是EXPLAIN结果就是全表扫描。这种情况不算索引失效，不需要强行走索引。除非你用FORCE INDEX强制指定，否则优化器有最终决定权。

三是注意回表次数。联合索引如果只写了两个列，但查询要把整行数据都拿出来，就必须回表。如果业务查询频繁，且需要返回的列不多，可以考虑把需要的列也加进联合索引，形成覆盖索引，让Extra列变成Using index。

四是看索引基数（Cardinality）。你可以通过SHOW INDEX FROM 表名查看Cardinality字段的值。这个值反映索引列的区分度，如果Cardinality很小，说明索引列的重复值很多，走索引的效果就不好。举个例子，一个性别字段gender只有 0 和 1 两个值，那对gender建索引几乎没有意义，优化器大概率也不会走。