凌晨两点被电话吵醒，值班同事说线上一个报表查询跑了几十秒没出来，数据库 CPU 直接飙到 90% 多。登录进去一看，一条带三个条件、两个 JOIN 的 SELECT，执行计划里 type 是 ALL，rows 估算一百多万行，典型的连索引都没走。后来加了联合索引、把 SELECT 需要的字段都塞进索引，查询从 40 秒降到 80 毫秒。这种经历干过后端的人多少都遇到过几回，MySQL 索引这个东西，平时不觉得多重要，一出慢 SQL 就恨不得把它原理背下来。
这篇文章不堆砌文档，就按我实际排查问题、做索引优化时的完整思路来写。从 B+ 树为什么能扛住千万级数据，到联合索引怎么设计、EXPLAIN 怎么读懂，再到慢查询定位和索引失效的常见坑，最后用一个真实案例把从 40 秒到 80 毫秒的全过程拆开讲一遍。适合被慢 SQL 折磨过的后端开发、DBA 或者刚接触 MySQL 调优的读者，照着思路去排查自己的库，大概率能少走不少弯路。
1. 索引的本质：先搞清楚它到底在加速什么
1.1 数据库索引为什么默认选择 B+ 树
我刚接触索引的时候也困惑过，为什么不直接用二叉树或者哈希表，非要用 B+ 树这种又高又瘦的结构。后来自己画了几张图，把数据量和磁盘 IO 摊开算了一笔账，才算真正想明白。
先说哈希索引。哈希表的查询复杂度是 O(1)，单条等值查询确实快得离谱，但它有两个硬伤。第一，哈希索引不支持范围查询，because 哈希函数把键值打散之后，相邻的键在存储位置上没有任何关联，你查age BETWEEN 18 AND 30，它只能全扫。第二，哈希索引不支持排序，ORDER BY也走不了。生产环境里纯等值查询的场景太少了，大多数业务都离不开范围和排序，所以哈希索引只能是辅助角色。
再说二叉搜索树。它支持范围查询和排序，但在数据量大的时候会有问题。假设数据插入顺序是递增的，二叉树会退化成一条链表，查询复杂度从 O(log n) 直接变成 O(n)，比全表扫描还慢。用红黑树或 AVL 树能解决退化成链表的问题，但树的高度还是太高了。InnoDB 存储引擎一次 IO 默认读取 16KB 的页，如果每个节点只存一个键值，两千万条数据大概需要二十多层，也就是说每次查询至少要从磁盘读二十多次，这个 IO 成本实在扛不住。
B+ 树把这个问题解决得很巧妙。它每个节点能存很多个键值，一个 16KB 的页可以放下上千个键，三层高度就能支撑千万级数据。更关键的是，B+ 树的数据全部存储在叶子节点，叶子节点之间通过指针相连，形成了一个有序链表。这意味着不仅等值查询快，范围查询也能像翻链表一样顺序扫过去，排序操作在很多时候直接就免了。MySQL 选择 B+ 树作为默认索引结构，本质上是为磁盘 IO 优化的结果，它把查询时的树高度压到三四层，也就是最多三四次磁盘 IO 就能定位到数据，这个代价对绝大多数业务来说都可以接受。
1.2 主键索引、二级索引与回表
明白了 B+ 树，接下来要搞清楚 InnoDB 里索引和数据是怎么组织在一起的。InnoDB 是聚簇索引组织表，表里的数据本身就是一个以主键为索引键的 B+ 树，叶子节点存的是整行数据。所以主键索引也叫聚簇索引，你建表时定的主键，决定了数据在物理磁盘上的组织顺序。
二级索引（也就是我们平时手动创建的普通索引）是另一棵 B+ 树，它的叶子节点不存整行数据，只存索引键和主键值。这个设计有两层意思。第一，如果通过二级索引查数据，先在这棵 B+ 树里找到对应主键，再到主键索引树里回查一次，这个动作就是“回表”。第二，如果二级索引本身已经覆盖了当前查询需要的所有字段，那就不需要再去主键索引树回表了，这就叫“覆盖索引”。
回表这个操作本身有成本，虽然主键查找很快，但每回表一次就是一次额外的 B+ 树检索，如果结果集有一万行，就要回表一万次，这个 IO 开销就很可观了。所以优化 SQL 时有一个很实用的思路：尽量把查询需要的字段都放进索引里，让索引“覆盖”查询。比如你有一个商品表，经常按下单时间区间查订单金额总和，那建一个(order_time, amount)的联合索引，查的时候从索引里就能拿到 amount，完全不需要回表。
注意：主键索引和数据行是绑定在一起的，所以 InnoDB 表设计时要尽量用自增整数或雪花 ID 这类有序值做主键。如果主键是 UUID 这种随机字符串，新插入的数据会随机落在树的中间位置，触发大量的页分裂和页重排，写入性能和新数据占用的物理空间都会变差。
2. 索引设计与创建的实战要点
2.1 建索引前必须想清楚的三件事
建索引之前，先别急着写CREATE INDEX，想清楚三个问题再动手。一是这个查询走索引到底能不能减少数据扫描量。比如一张统计表只有几千行，全表扫描也就几次磁盘读，这时候建索引带来的收益微乎其微，反而白占空间、拖慢写入。一般来说，当表数据量超过十万行，并且查询条件能过滤掉绝大多数行时，索引的性价比才明显。
二是这个索引会不会被高频写入路径拖累。每建一个索引，InnoDB 在插入、更新、删除时都要额外维护一棵 B+ 树的节点变化。索引建得越多，写入放大越严重。业务上读多写少的场景可以适当多建索引，写多读少的场景则要克制。我见过一个账号流水表，因为每个开发都按自己的查询习惯加索引，最后积累了十几个索引，结果一条简单的INSERT都要花十几毫秒，妥妥被索引拖垮。
三是区分度够不够。如果一列只有两个不同值（比如性别），那索引的选择性就很差，优化器算一下发现用索引扫描和全表扫描的代价差不多，干脆不用索引。区分度可以用SELECT COUNT(DISTINCT column) / COUNT(*)来看，值越接近 1 越好。一般低于 0.1 的列，除非和其他列组成联合索引，否则单独建索引意义不大。
2.2 联合索引怎么设计：最左前缀规则的另一面
联合索引是日常优化里最常用也最容易用错的东西。它的底层结构是一棵 B+ 树，排序规则是先按第一列排序，第一列相同再按第二列排序，以此类推。这决定了它最核心的一个规则：查询条件必须从联合索引的最左列开始连续匹配，否则索引就失效。网上管这个叫“最左前缀原则”。
我见过最多的菜鸟错误是建了(user_id, status, create_time)这个联合索引，然后写一条WHERE status = 1 AND create_time > '2024-01-01'的查询，发现压根没走索引，于是跑来问为什么。原因很简单：查询跳过了第一列 user_id，直接咬住索引的第二、三列，B+ 树的排序规则决定了它无法沿着第二列快速定位，只能放弃这个索引。
联合索引的列顺序要怎么排？我总结了一个简单粗暴的优先级：先把等值查询的列放前面，再把范围查询的列放后面。原因是 B+ 树在遇到第一个范围条件时，后面的列就没办法继续用于等值定位了。举个例子，如果你经常查WHERE user_id = 123 AND create_time BETWEEN ...，那(user_id, create_time)就是合理的顺序，而(create_time, user_id)会让 create_time 的范围条件成为第一个范围判断，user_id 反而无法参与后续精确定位。
当然，这个规则不是绝对的。如果某个列区分度特别差，即使它是等值条件，也不一定要放在最前面，因为它过滤不掉多少行。这时候要拿优化器的判断来验证，最简单的方法就是建完索引后执行 EXPLAIN，看 key_len 是否用到了你期望的列。key_len 越长，说明实际使用的索引列越多，联合索引利用率越高。
2.3 索引下推：MySQL 5.6 以后默认开启的性能 buff
提到联合索引，不能不提索引下推（Index Condition Pushdown，简称 ICP）。这个特性从 MySQL 5.6 就开始默认开启了，但很多做了几年开发的人居然不知道，属实可惜。
索引下推解决的是什么问题呢？假设你有一个联合索引(city, age)，执行查询WHERE city = '杭州' AND age > 20。在没有 ICP 的旧版本里，InnoDB 会因为 city 命中了索引，把 city 为杭州的所有主键都回表查出来，然后在服务层再过滤 age > 20。这意味着很多 age 不满足条件的行也被白白回表了一次，多产生了大量随机 IO。
开启 ICP 之后，存储引擎在读取二级索引的时候就顺便把 age > 20 这个条件一起判断了，只有满足条件的索引记录才回表。这相当于把 where 条件提前到索引扫描阶段，大大减少了回表次数。你不需要做什么额外操作，只要确认索引里包含了 where 条件中用到的列即可，MySQL 会自动把能下推的条件都下推下去。
提示：判断一条 SQL 有没有用上索引下推，看 EXPLAIN 输出的 Extra 字段里有没出现
Using index condition。如果出现了，这个索引的查询条件已经不仅仅用在了“定位”上，还用于“过滤”了，整体效率会好很多。
3. 慢 SQL 排查：从 EXPLAIN 到索引失效
3.1 慢查询日志怎么开，怎么高效捞
优化索引的前提是先找到慢 SQL，慢查询日志是排查的第一入口。MySQL 里默认是关闭的，可以在配置文件[mysqld]段下设置：
slow_query_log = ON slow_query_log_file = /var/log/mysql/slow.log long_query_time = 1 log_queries_not_using_indexes = ONlong_query_time的单位是秒，我一般建议线上环境先设成 0.5 或者 1，跑一段时间看看有多少慢查询，有需要再收紧。log_queries_not_using_indexes这个参数会把没有走索引的查询也记到慢日志里，即使它执行时间不长，也值得打开，因为全表扫描就像一个潜伏的地雷，数据量一旦涨上来就会爆。
拿到慢日志之后，用mysqldumpslow工具可以先粗筛一遍，它能按照查询耗时或扫描行数来聚合相似的 SQL。不过这个工具只能做聚合统计，真要分析单条 SQL 的执行计划，还得上 EXPLAIN。
3.2 EXPLAIN 结果怎么看：关键字段逐个拆解
EXPLAIN 是 MySQL 优化器给出的执行计划报告，说白了就是告诉你“它打算怎么执行这条 SQL”。里面字段很多，但真正要重点看的就几个。
type是效率的直观体现，性能从好到差大致是：const、eq_ref、ref、range、index、ALL。ALL就是全表扫描，必须警惕；index意味着虽然扫的是索引树，但要遍历整棵树，代价也不小；range是范围扫描，可以接受；ref、eq_ref、const是走等值索引查询，属于比较理想的状态。
key显示实际用到的索引，key_len表示索引使用的字节数。rows是优化器预估要扫描的行数，这个数字直接反映索引的过滤效果，通常越大越慢。Extra里经常出现的几个值也值得注意：Using filesort表示排序没走索引，MySQL 要额外在内存或磁盘里排序；Using temporary表示使用了临时表，常见于 GROUP BY 或 DISTINCT；Using index是覆盖索引扫描，属于加分项；Using index condition说明用上了索引下推。
在实际排查里，我一般先看type是不是 ALL，再看rows的估算量，然后看Extra里有没有 filesort 或 temporary，最后判断key用到的索引是不是最优。把这几个字段串起来，一条慢 SQL 的病灶基本就能定位了。
3.3 索引失效的八种常见场景
索引失效的问题在开发面试里高频出现，在线上问题里更是家常便饭。我根据这几年踩过的坑，列一份最常见的高危清单。
- 在索引列上做函数运算。比如
WHERE DATE(create_time) = '2024-01-01'，就算 create_time 有索引也用不上。正确写法是WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02'，让索引走范围扫描。 - 隐式类型转换。隐式转换很隐蔽，例如
WHERE phone = 13800138000，而 phone 是 varchar 类型，MySQL 会把字符列转成数字来比较，导致索引失效。记住一点：字段是什么类型，查询参数就传什么类型。 - 模糊查询左匹配。
LIKE '%关键词%'这种前置百分号没法利用索引排序，属于必然全扫；换成LIKE '关键词%'则能走 range 扫描。 - OR 连接非索引条件。
WHERE id = 1 OR age = 18，如果 age 上没有索引，优化器为了合并结果集，可能放弃主键索引走全表扫描。也写过 UNION 语句拆开，通常能救回来。 - 联合索引不满足最左前缀。前面讲过了，查询条件里得从联合索引第一列开始连续命中，跳列或断列都会失效。
NOT IN、<>、!=这些否定操作。这类条件往往让优化器觉得扫描成本比走索引更低，尤其是当否定条件覆盖了大量行时。真要优化，得看业务能不能改写成IN或范围条件。- 在索引列上进行隐式字符集排序规则不匹配。这种情况多出现在多表 JOIN 时，两个表的连接字段字符集不一致，MySQL 必须做转换，索引就废了。
- 优化器认为全表扫描更快。当表数据量很小，或者索引选择性太差（比如性别列），优化器会主动弃用索引。
注意：上面这份列表不是绝对的。比如在索引列上用函数，在某些特定版本和特定场景下也可能走索引，比如前缀索引。所以最可靠的判断标准始终是跑一次 EXPLAIN，看 type 和 key 的实际情况，不要让经验主义代替实测。
4. 优化实战：一个典型订单表查询的调优全过程
4.1 问题现场：生产环境的报表查询为什么会那么慢
今年年初帮一个电商客户排查过一个典型案例，订单表t_order存量大概一千两百万行，结构简化后是这样的：
CREATE TABLE `t_order` ( `id` BIGINT NOT NULL AUTO_INCREMENT, `user_id` BIGINT NOT NULL, `order_no` VARCHAR(64) NOT NULL, `status` TINYINT NOT NULL DEFAULT 0, `channel` VARCHAR(16) DEFAULT NULL, `amount` DECIMAL(10,2) NOT NULL, `create_time` DATETIME NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;报表系统有一条核心查询，逻辑大致是统计某个用户在某段时间内、某个渠道下的订单总金额和单数：
SELECT COUNT(*) AS order_count, IFNULL(SUM(amount), 0) AS total_amount FROM t_order WHERE user_id = 123456 AND channel = 'app' AND create_time >= '2024-01-01' AND create_time < '2024-03-01';这条 SQL 在凌晨跑批时要花 40 秒左右，不仅慢，还把从库 CPU 打满，导致其他业务受影响。我登录数据库先看了慢日志，然后直接对这条 SQL 执行 EXPLAIN，结果 current type 是 ALL，rows 估算一千一百万，Extra 是 Using where。这就意味着它在对整张表做全表扫描，把一千多万行数据逐个丢给 where 条件去过滤，不慢才怪。
4.2 一步步调优：从全表扫描到索引覆盖
第一反应自然是给 user_id、channel、create_time 建一个联合索引。但列的顺序要想清楚，理论上前文说过：等值条件放前面，范围条件放后面。这里 user_id 和 channel 都是等值，create_time 是范围，所以索引设计为：
ALTER TABLE t_order ADD INDEX idx_user_channel_time (user_id, channel, create_time);建完索引后再跑一次 EXPLAIN，type 从 ALL 变成了 range，key 使用到了idx_user_channel_time，rows 从一千一百万降到了几百行。效果立竿见影，查询从 40 秒降到了 200 毫秒左右。按说到这里已经能交差了，但我想再压一压，因为报表这个查询还会按天跑很多次，能省一点是一点。
仔细一看 EXPLAIN 的 Extra，还有Using index condition，说明虽然索引过滤到了几百行，但这几百行还得回表去取 amount 字段做 SUM。回表量不大，可如果有几十个用户同时跑报表，累积 IO 也不少。我顺手把 amount 也加进了索引尾部，做成覆盖索引：
ALTER TABLE t_order ADD INDEX idx_user_channel_time_amount (user_id, channel, create_time, amount);这里覆盖索引的写法，其实就是在联合索引里多塞一个查询需要的列，让二级索引树直接提供 amount 的值，查询不需要回表。改完之后再看 EXPLAIN，type 还是 range，key 换了新索引，Extra 变成了Using index。这就意味着这次查询的全部数据都从索引树里拿，一次回表都不会发生。
4.3 优化结果对比与效果验证
最终结果对比如下：
| 阶段 | type | rows 估算 | 查询耗时 |
|---|---|---|---|
| 优化前（无索引） | ALL | 约 1100 万 | 约 40 秒 |
| 第一次优化（联合索引） | range | 约 300 行 | 约 200 毫秒 |
| 第二次优化（覆盖索引） | range | 约 300 行 | 约 80 毫秒 |
从 40 秒到 80 毫秒，提升了差不多 500 倍，最核心的功臣就是联合索引加覆盖索引。
另外我还做了一件事，把原表里刚才建的idx_user_channel_time直接删掉了。因为新索引idx_user_channel_time_amount的最左前缀和原来的完全一致，老索引就是纯冗余，留着只会增加写放大和占用存储空间。清理完这两个索引之后，再加一层校验，拿一个月的历史数据做抽样回归，确认结果一致才放心。
经验：加索引时，一定检查一下现有索引里有没有某个索引的最左前缀包含了新索引，如果有，旧索引就是多余的。每多一个索引，写入就要多维护一棵 B+ 树，这种冗余在千万级大表上会成倍放大开销。
5. 索引使用中的常见坑与避坑清单
5.1 深分页为什么还是慢：limit offset 的隐藏代价
很多人在后台管理列表页遇到过这样的问题：数据量一大，翻到第 100 页就开始卡顿。页面查询长这样：
SELECT * FROM t_order ORDER BY id LIMIT 100000, 20;从执行计划上看，它确实走了主键索引，type 是 range，rows 也正常，但耗时就是高得离谱。问题出在 LIMIT 的实现方式上：MySQL 会把从第 0 行到第 100019 行全部扫出来，然后丢弃前 100000 行，只把最后 20 行返回给客户端。前面白白扫的那十万行，虽然走索引很快，但依然有额外 IO 成本。
优化思路通常是改成“基于游标的分页”，也就是记录上一页最后一条记录的 id，下一页查询直接用WHERE id > 100000来拿数据：
SELECT * FROM t_order WHERE id > 100000 ORDER BY id LIMIT 20;这样 MySQL 可以直奔目标位置，扫描行数从十万行直接降到 20 行。不过这种方式对业务有一个要求，就是排序的字段必须是唯一的，否则可能会漏数据。如果排序字段不是唯一列，可以退一步用(create_time, id)联合排序，并记录上一页最后一条的(create_time, id)组合值来做游标。
5.2 排序与分组走索引的几个前置条件
ORDER BY和GROUP BY想走索引，也有讲究。首先是排序字段和查询条件的组合必须满足最左前缀规则。比如索引是(user_id, create_time)，那WHERE user_id = 1 ORDER BY create_time就能用到索引排序，不会产生 filesort。但如果你写WHERE create_time > '2024-01-01' ORDER BY user_id，MySQL 就要额外排序了，因为 user_id 在联合索引里是第一列，create_time 是范围条件，order by 的列顺序和索引键顺序已经不一致了。
排序方向也很关键。MySQL 8.0 之前对多个字段排序方向混搭不太友好，比如ORDER BY a ASC, b DESC，就算 a、b 都在索引里，也可能触发 filesort，因为索引本身是全部按升序排的。MySQL 8.0 开始支持降序索引，可以建INDEX idx_a_b (a ASC, b DESC)，但在老版本里几乎无解，只能考虑业务上绕一下或者接受文件排序。
GROUP BY的原理其实是在分组前先排序，所以只要分组字段满足最左前缀并且顺序与索引一致，也能避免临时表。比如GROUP BY user_id, channel，如果索引是(user_id, channel)，那直接从索引顺序扫就能完成分组，Extra 里不会出现Using temporary。
5.3 索引生命周期管理：定期体检比救火更重要
优化完成之后还要有一个长期的维护动作，因为索引不是建完就一劳永逸的。业务在变，查询模式在变，有些索引慢慢就没人用了，而写放大还在持续伤害数据库。我建议每季度做一次索引体检，重点看以下几项。
一是检查是否有冗余索引。可以用sys.schema_redundant_indexes这张系统视图来查，MySQL 官方运维工具包里的工具也能生成分析报告。比如前面提到(user_id, channel, create_time)和(user_id, channel, create_time, amount)同时存在，前者就是后者的子集，可以删掉。
二是看索引的实际使用频率。MySQL 的performance_schema.table_io_waits_summary_by_index_usage表会记录每个索引的读写次数。如果某个索引的读取次数长期为 0，说明它基本没被用上，可以考虑删除。但确定删除前一定要先离线保存一份建表语句，以防业务某个角落还有依赖。
三是关注写入放大。大表加索引和删索引本身都是一种 DDL 操作，在 MySQL 8.0 之前做 DDL 会锁表，线上业务会瞬间卡住。建议用工具去处理在线 DDL，比如使用 Percona Toolkit 里的工具，或者在业务低峰期执行，并设置合理的锁等待超时时间。
最后分享一个我在实际工作中亲测有效的小技巧：每次优化完一条 SQL，不要只记录“改了什么”，要把改之前的执行计划、耗时、改之后的执行计划、耗时都存到团队的 Wiki 里。积累几十个案例之后，你再遇到新问题，基本上扫一眼 SQL 就能猜到问题出在哪，这也是老手和新手之间最大的差距来源。索引优化没有银弹，靠的就是案例积累和每次追根究底的习惯。
