你的索引为什么救不了慢查询？从B+树结构到优化器决策的完整解析

你花了两小时设计了一套"完美"的索引方案：where条件列建了索引，排序字段建了索引，连join的列都建了索引。上线第一天，监控系统就开始报警——那条"应该很快"的查询，执行时间飙到了30秒。

你打开EXPLAIN一看，type: ALL，全表扫描。索引明明存在，为什么不用？

这不是运气问题，而是B+树结构、选择率计算、I/O成本估算、统计信息精度等多重因素共同作用的结果。要理解索引为什么"失灵"，需要从最底层的数据结构开始。

索引的本质：不是魔法，是权衡

很多人把索引理解为"加速查询的神器"，但更准确的定义是：索引是一种以空间换时间、以写入换读取的数据结构。

B+树是关系数据库最主流的索引实现。它不是什么神秘的算法，而是一种多路平衡查找树，专门为磁盘存储优化。与内存中的二叉树不同，B+树的每个节点可以存储大量键值，树的高度被严格控制在很小的范围内。

为什么这很重要？因为磁盘I/O是数据库性能的决定性因素。一次随机I/O大约需要10毫秒（机械硬盘），而内存访问只需100纳秒——相差十万倍。B+树的设计目标很简单：用最少的I/O次数找到目标数据。

B+树的结构：从根到叶的旅程

一棵典型的InnoDB B+树索引看起来是这样的：

根节点(Level 2)
    │
    ├── 内部节点(Level 1)
    │       ├── 叶节点(Level 0): [记录1, 记录2, ...]
    │       └── 叶节点(Level 0): [记录3, 记录4, ...]
    │
    └── 内部节点(Level 1)
            ├── 叶节点(Level 0): [记录5, 记录6, ...]
            └── 叶节点(Level 0): [记录7, 记录8, ...]

根节点和内部节点只存储键值和子节点指针，不存储实际数据。它们的唯一作用是指路——告诉你下一步该往哪个分支走。

叶节点存储实际的数据。在聚簇索引中，叶节点存储完整的行数据；在二级索引中，叶节点存储索引列的值和主键值。

每个节点（在InnoDB中称为"页"）默认大小是16KB。这个设计很关键：它与操作系统的磁盘块大小对齐，确保一次I/O能读取完整的节点。

查找一条记录需要多少次I/O？

假设一个表有1亿条记录，主键是4字节的INT：

每个叶节点可以存储约400条记录（16KB页，每条记录约40字节）
每个内部节点可以存储约1000个指针（16KB页，每个指针约16字节）

树的高度计算：

Level 0（叶节点）：1亿 / 400 ≈ 25万个页
Level 1：25万 / 1000 = 250个页
Level 2：250 / 1000 = 1个页（根节点）

查找任意一条记录，最多需要3次I/O：读取根节点 → 读取内部节点 → 读取叶节点。这就是B+树的威力。

但问题来了：这只是找到一条记录的成本。如果要找100万条记录呢？

选择率：优化器决策的核心

数据库优化器不是靠"猜"来选择执行计划的，它有一套相对精确的成本模型。这个模型的核心输入之一就是选择率。

选择率 = 符合条件的行数 / 总行数

选择率越低，索引越有效；选择率越高，索引的价值越低。

一个简单的例子

假设一个用户表有100万条记录：

SELECT * FROM users WHERE gender = 'M';

如果gender字段只有’M’和’F’两个值，且分布均匀，选择率就是50%。这意味着索引会返回50万条记录。

优化器的计算逻辑是这样的：

使用索引的成本：

扫描索引树找到50万个符合条件的条目
每个条目对应一个主键值
用这50万个主键值回表查找完整记录
50万次随机I/O

全表扫描的成本：

顺序读取整张表
假设每个页存储100条记录，需要读取1万个页
1万次顺序I/O

在机械硬盘上，一次随机I/O约10毫秒，一次顺序I/O约0.1毫秒。计算结果：

索引方案：500,000 × 10ms = 5,000秒（实际会并行和缓存，但量级差距明显）
全表扫描：10,000 × 0.1ms = 1秒

优化器选择全表扫描是正确的决定。

这就是为什么"索引列有索引但查询不用"的一个核心原因：当索引选择率太低时，使用索引反而比全表扫描更慢。

选择率的临界值

不同数据库有不同的临界值，但经验值在10%-30%之间。MySQL通常在预计返回超过20%-30%的表数据时，会倾向于选择全表扫描。

PostgreSQL的选择更精细：

返回约1%的数据：使用索引扫描
返回约10%的数据：使用位图扫描
返回约30%以上的数据：使用顺序扫描

位图扫描是一种折中方案：先扫描索引收集符合条件的行位置，然后按物理顺序批量访问表，减少随机I/O。

回表：索引查找的隐形代价

理解"回表"是理解索引性能的关键。

InnoDB的表数据存储在聚簇索引中——主键索引的叶节点就是完整的行数据。二级索引的叶节点只存储索引列的值和主键值。

当你通过二级索引查找数据时：

1. 扫描二级索引B+树，找到符合条件的索引记录
2. 从索引记录中获取主键值
3. 用主键值扫描聚簇索引B+树，获取完整的行数据

第3步就是回表。每次回表都是一次B+树查找，需要读取多个页（假设树高为3，就是3次I/O）。

回表的代价有多大？

假设一条查询需要返回1万条记录：

如果使用覆盖索引（索引包含所有查询列）：只需要扫描索引，1万次索引页读取
如果需要回表：1万次索引页读取 + 1万次聚簇索引查找（每次查找需要读取多个页）

差距可能是几十倍。

这就是为什么覆盖索引（Covering Index）如此重要。如果你的查询只需要索引列，数据库就不需要回表，性能会有质的飞跃。

-- 需要回表
SELECT * FROM orders WHERE user_id = 123;

-- 覆盖索引，不需要回表（假设索引是(user_id, order_date)）
SELECT user_id, order_date FROM orders WHERE user_id = 123;

索引失效的常见陷阱

即使选择率合适，索引也可能因为各种原因不被使用。以下是最常见的场景：

1. 函数作用于索引列

-- 索引失效
SELECT * FROM users WHERE YEAR(created_at) = 2024;

-- 索引有效
SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

当你在索引列上使用函数时，索引存储的是原始值，而查询条件变成了函数结果。数据库无法直接用索引值匹配函数结果，只能扫描全表计算每行的函数值。

这个规则同样适用于：

-- 索引失效
SELECT * FROM users WHERE name LIKE '%张';
SELECT * FROM users WHERE name LIKE '%张%';

-- 索引有效
SELECT * FROM users WHERE name LIKE '张%';

前缀匹配（LIKE '张%'）可以用索引，因为索引是按前缀排序的。但后缀匹配和中缀匹配需要扫描全部索引值。

2. 隐式类型转换

-- phone字段是VARCHAR类型
-- 索引失效
SELECT * FROM users WHERE phone = 13800138000;

-- 索引有效
SELECT * FROM users WHERE phone = '13800138000';

当查询条件的类型与列类型不匹配时，MySQL会将列值转换为条件类型进行比较。这个转换过程需要对每一行数据执行，导致索引失效。

更隐蔽的是join条件：

-- user_id是BIGINT，order_user_id是VARCHAR
SELECT * FROM orders o JOIN users u ON o.order_user_id = u.user_id;

这种情况下，索引可能在join时失效，导致巨大的性能问题。

3. 联合索引的"最左前缀"规则

联合索引(a, b, c)可以支持以下查询：

WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3

但以下查询无法使用该索引：

WHERE b = 2           -- 缺少a
WHERE c = 3           -- 缺少a和b
WHERE a = 1 AND c = 3 -- 跳过了b（MySQL 8.0+有索引跳跃扫描优化）

这是因为联合索引的排序规则是先按a排序，a相同再按b排序，b相同再按c排序。如果跳过前面的列，索引的有序性就无法利用。

4. OR条件的陷阱

-- 假设name和email各有索引
SELECT * FROM users WHERE name = '张三' OR email = '[email protected]';

早期版本的MySQL很难有效利用索引处理OR条件。MySQL 5.0之后引入了Index Merge优化，可以分别用两个索引扫描，然后合并结果。但这种合并本身有开销，当结果集较大时，优化器可能还是选择全表扫描。

更糟糕的是：

SELECT * FROM users WHERE name = '张三' OR age > 30;

如果age列没有索引，整个条件都无法使用索引。

5. 统计信息不准确

优化器的决策依赖于统计信息：表的行数、每列的基数、值的分布等。这些信息不是实时更新的，而是通过采样的方式定期刷新。

当统计信息过时或不准确时，优化器可能做出错误的决定：

-- 更新统计信息
ANALYZE TABLE users;

生产环境中，大量数据变更后（如批量导入、删除），手动执行ANALYZE TABLE是个好习惯。

深度分页：LIMIT的大坑

分页查询是Web应用的标配，但当用户翻到第1000页时，噩梦开始了。

SELECT * FROM orders ORDER BY id LIMIT 10000, 10;

这条查询看起来只返回10条记录，但数据库实际执行的是：

扫描前10010条记录
丢弃前10000条
返回后10条

I/O开销与偏移量成正比。 当偏移量达到百万级别，查询可能需要几十秒。

延迟关联优化

SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 10000, 10
) t ON o.id = t.id;

子查询只需要扫描索引（比扫描表快得多），获取10个id值，然后只回表查询这10条完整记录。性能提升可能达到10倍以上。

游标分页

如果不需要直接跳转到任意页，可以用游标分页：

-- 第一页
SELECT * FROM orders WHERE id > 0 ORDER BY id LIMIT 10;

-- 下一页（假设上一页最后一条记录的id是12345）
SELECT * FROM orders WHERE id > 12345 ORDER BY id LIMIT 10;

这种方式完全避免了偏移量的开销，无论数据量多大都能保持稳定的性能。

ORDER BY与索引：filesort的真相

当你在EXPLAIN的Extra列看到Using filesort时，不要被名字误导——这不一定意味着排序操作在磁盘上进行。

Filesort是MySQL对结果集进行排序的内部操作，可能在内存中完成，也可能需要临时文件。关键是：当数据量大时，filesort是昂贵的操作。

何时可以避免filesort？

索引本身是有序的。如果你的ORDER BY子句能利用索引的有序性，MySQL就不需要额外排序。

-- 假设有索引(user_id, created_at)
SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at;

这条查询可以：

在索引中定位到user_id = 123的范围
由于索引已经按created_at排序，直接按顺序读取
不需要filesort

但如果查询条件破坏了索引的有序性：

SELECT * FROM orders WHERE user_id > 100 ORDER BY created_at;

范围查询user_id > 100匹配的索引记录在created_at维度是无序的，MySQL必须执行filesort。

优化器也会犯错

成本模型不是万能的。有时优化器会选择"看起来成本低"但实际执行很慢的计划。

常见原因：

统计信息偏差：采样数据不能代表真实分布
成本模型简化：忽略了某些因素（如锁竞争、缓存命中）
索引信息缺失：优化器不知道某些索引的存在（如不可见索引）

当你确信某个索引更好时，可以用强制索引：

SELECT * FROM orders FORCE INDEX(idx_user_id) WHERE user_id = 123;

但这是最后的手段。更好的做法是找出优化器犯错的原因（通常是统计信息问题），从根本上解决。

诊断慢查询的完整流程

遇到慢查询时，按以下步骤排查：

第一步：获取执行计划

EXPLAIN SELECT ...;
-- MySQL 8.0+
EXPLAIN ANALYZE SELECT ...;

重点关注：

type列：ALL（全表扫描）是最差的，const/eq_ref是理想的
key列：实际使用的索引
rows列：预估扫描的行数
Extra列：额外信息，如Using filesort、Using temporary

第二步：检查选择率

SELECT COUNT(*) as total, 
       COUNT(DISTINCT your_column) as distinct_values,
       COUNT(DISTINCT your_column) / COUNT(*) as selectivity
FROM your_table;

如果选择率低于0.1（10%），索引通常是有效的；如果高于0.3（30%），索引可能不会被使用。

第三步：验证索引是否被考虑

检查possible_keys列。如果你的索引不在其中，说明查询条件不匹配索引规则。

第四步：检查是否需要回表

如果查询返回的列不在索引中，必然需要回表。考虑创建覆盖索引。

第五步：更新统计信息

ANALYZE TABLE your_table;

然后重新检查执行计划。

索引设计的黄金法则

高选择性优先：索引列应该有尽可能多的不重复值
覆盖常用查询：让索引包含查询需要的所有列
遵循最左前缀：联合索引的列顺序很重要
避免过度索引：每个索引都有维护成本，影响写入性能
定期维护：重建碎片化严重的索引

-- 查看索引碎片
SELECT table_name, index_name, data_free, data_length,
       data_free / (data_length + data_free) as fragmentation_ratio
FROM information_schema.tables;

-- 重建索引
ALTER TABLE your_table ENGINE=InnoDB;

索引并非万能。理解它的工作原理，才能让它真正发挥作用。下次看到慢查询，别急着加索引——先看看优化器为什么不选择你已有的索引。答案往往藏在选择率、回表成本、统计信息和查询条件这些细节里。

索引的本质：不是魔法，是权衡#

B+树的结构：从根到叶的旅程#

查找一条记录需要多少次I/O？#

选择率：优化器决策的核心#

一个简单的例子#

选择率的临界值#

回表：索引查找的隐形代价#

回表的代价有多大？#

索引失效的常见陷阱#

1. 函数作用于索引列#

2. 隐式类型转换#

3. 联合索引的"最左前缀"规则#

4. OR条件的陷阱#

5. 统计信息不准确#

深度分页：LIMIT的大坑#

延迟关联优化#

游标分页#

ORDER BY与索引：filesort的真相#

何时可以避免filesort？#

优化器也会犯错#

诊断慢查询的完整流程#

索引设计的黄金法则#

更多精彩内容

同一条SQL为何执行计划会突然变化：从参数嗅探到多计划缓存的技术突围

查询优化器的致命误判：为什么数据库有时会选错执行计划

数据库已提交的事务为何会丢失？从fsync到异步提交的持久性权衡

B+树索引的页分裂：从顺序插入的优雅到随机写入的代价

事务隔离级别为何成为数据库最被误解的概念