返回 筑基・数据元府藏真
索引深度解析：B+树、哈希与覆盖索引
博主
大约 15 分钟
索引深度解析：B+树、哈希与覆盖索引
问题引入：索引失效的困惑
-- 明明有索引，为什么还是全表扫描？
CREATE INDEX idx_user ON user(phone);
SELECT * FROM user WHERE LEFT(phone, 3) = '138'; -- 索引失效！
-- 执行时间：8秒，扫描1000万行
-- 正确的写法
SELECT * FROM user WHERE phone LIKE '138%'; -- 使用索引
-- 执行时间：20ms，扫描1000行
这个案例让我深刻认识到：理解索引原理对于编写高效SQL至关重要。
现象描述：索引使用不当的代价
场景1：索引失效导致全表扫描
-- 创建测试表
CREATE TABLE test_index (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
user_id BIGINT,
status INT,
created_at TIMESTAMP,
INDEX idx_user_status (user_id, status)
);
-- 插入1000万条测试数据
-- ...
-- 查询1：使用索引（最左前缀）
SELECT * FROM test_index WHERE user_id = 100;
-- 执行时间：5ms，扫描10行
-- 查询2：索引失效（跳过最左前缀）
SELECT * FROM test_index WHERE status = 1;
-- 执行时间：5s，扫描1000万行
-- 查询3：索引失效（函数操作）
SELECT * FROM test_index WHERE DATE(created_at) = '2023-01-01';
-- 执行时间：8s，扫描1000万行
场景2：重复索引浪费空间
-- 重复索引示例
CREATE TABLE redundant_index (
id BIGINT PRIMARY KEY,
user_id BIGINT,
order_no VARCHAR(50),
INDEX idx_user_id (user_id),
INDEX idx_user_id_2 (user_id), -- 重复！
INDEX idx_order_no (order_no),
UNIQUE INDEX uk_order_no (order_no) -- 与idx_order_no重复
);
-- 浪费的存储空间
-- 每个重复索引占用约100MB（1000万行数据）
原因分析：索引原理深度剖析
1. B+树索引原理
B+树结构
B+树索引结构示意：
[10, 20, 30]
/ | \
[1,5,8] [12,15,18] [25,28,29]
/ | \ / | \ / | \
叶子节点（包含全部数据，且有序链接）
[1] -> [5] -> [8] -> [10] -> [12] -> [15] -> [18] -> [20] -> [25] -> [28] -> [29] -> [30]
特点：
- 非叶子节点只存储键值，不存储数据
- 叶子节点包含全部数据，且按顺序链接
- 树高通常3-4层，查询稳定
- 支持范围查询和顺序访问
B+树查询过程
-- 查询：SELECT * FROM user WHERE id = 15;
-- 查询过程：
-- 1. 从根节点 [10, 20, 30] 开始
-- 2. 15在10和20之间，进入中间子节点 [12, 15, 18]
-- 3. 在叶子节点找到15，返回数据
-- 4. 总共3次IO操作
-- 范围查询：SELECT * FROM user WHERE id BETWEEN 12 AND 20;
-- 1. 找到12的位置
-- 2. 沿着叶子节点链表顺序读取到20
-- 3. 无需回树查找
B+树 vs B树
| 特性 | B树 | B+树 |
|---|---|---|
| 数据存储 | 所有节点都存储数据 | 只有叶子节点存储数据 |
| 叶子节点 | 不链接 | 有序链表链接 |
| 范围查询 | 需要中序遍历 | 直接顺序扫描叶子节点 |
| 查询稳定性 | 不稳定（可能在非叶子节点找到） | 稳定（一定到叶子节点） |
| 空间利用率 | 较低 | 较高 |
2. 哈希索引原理
哈希索引结构
哈希索引结构示意：
键值：phone = '13800138000'
哈希函数：hash(phone) = 12345
哈希表：
桶地址 | 键值 | 数据指针
-------|------|----------
12345 | 13800138000 | -> 数据行地址
12346 | 13900139000 | -> 数据行地址
...
查询过程：
1. 计算哈希值：hash('13800138000') = 12345
2. 直接定位到桶12345
3. 比较键值，返回数据
-- 时间复杂度：O(1)
哈希索引特点
-- 创建哈希索引（MySQL Memory引擎支持，InnoDB自适应哈希）
CREATE TABLE hash_test (
id INT PRIMARY KEY,
phone VARCHAR(20),
INDEX idx_phone USING HASH (phone)
) ENGINE=MEMORY;
-- 等值查询极快
SELECT * FROM hash_test WHERE phone = '13800138000';
-- 时间复杂度：O(1)
-- 范围查询无法使用哈希索引
SELECT * FROM hash_test WHERE phone > '13800000000';
-- 退化为全表扫描
3. 索引类型对比
| 索引类型 | 存储结构 | 适用场景 | 优点 | 缺点 |
|---|---|---|---|---|
| B+树 | 平衡树 | 范围查询、排序、等值查询 | 支持范围查询，有序 | 写入有开销 |
| 哈希 | 哈希表 | 精确匹配 | 等值查询O(1) | 不支持范围查询 |
| 全文 | 倒排索引 | 文本搜索 | 支持分词搜索 | 占用空间大 |
| 空间 | R树 | 地理数据 | 支持空间查询 | 使用场景有限 |
4. 覆盖索引
什么是覆盖索引
-- 创建复合索引
CREATE INDEX idx_cover ON orders(user_id, status, total_amount);
-- 覆盖索引查询
SELECT user_id, status, total_amount
FROM orders
WHERE user_id = 1 AND status = 'PAID';
-- 执行计划：Extra: Using index
-- 所有需要的字段都在索引中，无需回表查询数据行
覆盖索引的优势
-- 非覆盖索引查询（需要回表）
SELECT * FROM orders WHERE user_id = 1;
-- 1. 在索引idx_user中找到user_id=1的记录
-- 2. 根据指针回表查询完整数据
-- 3. 需要2次IO
-- 覆盖索引查询（无需回表）
SELECT user_id, status FROM orders WHERE user_id = 1;
-- 1. 在索引idx_user_status中找到user_id=1的记录
-- 2. status也在索引中，直接返回
-- 3. 只需1次IO
5. 索引失效场景详解
最左前缀法则
-- 创建复合索引 (a, b, c)
CREATE INDEX idx_abc ON table_name(a, b, c);
-- ✅ 使用索引的情况
SELECT * FROM table_name WHERE a = 1;
SELECT * FROM table_name WHERE a = 1 AND b = 2;
SELECT * FROM table_name WHERE a = 1 AND b = 2 AND c = 3;
SELECT * FROM table_name WHERE a = 1 AND c = 3; -- a使用索引，c不使用
-- ❌ 不使用索引的情况
SELECT * FROM table_name WHERE b = 2; -- 跳过a
SELECT * FROM table_name WHERE c = 3; -- 跳过a和b
SELECT * FROM table_name WHERE b = 2 AND c = 3; -- 跳过a
函数操作导致索引失效
-- ❌ 索引失效：对索引列使用函数
SELECT * FROM user WHERE LEFT(phone, 3) = '138';
SELECT * FROM user WHERE DATE(created_at) = '2023-01-01';
SELECT * FROM user WHERE UPPER(username) = 'ZHANGSAN';
-- ✅ 正确使用索引
SELECT * FROM user WHERE phone LIKE '138%';
SELECT * FROM user WHERE created_at >= '2023-01-01' AND created_at < '2023-01-02';
SELECT * FROM user WHERE username = 'zhangsan'; -- 统一存储大小写
隐式类型转换
-- 表结构
CREATE TABLE test_convert (
id INT PRIMARY KEY,
user_id VARCHAR(20), -- 字符串类型
INDEX idx_user_id (user_id)
);
-- ❌ 索引失效：数字与字符串比较
SELECT * FROM test_convert WHERE user_id = 12345;
-- MySQL会将user_id转换为数字，导致索引失效
-- ✅ 正确使用索引
SELECT * FROM test_convert WHERE user_id = '12345';
范围查询后索引失效
-- 创建复合索引
CREATE INDEX idx_status_created ON orders(status, created_at);
-- ⚠️ 部分使用索引：status使用索引，created_at可能不使用
SELECT * FROM orders
WHERE status > 1 AND created_at > '2023-01-01';
-- 第一个范围条件后的索引列可能失效
-- ✅ 优化方案：将范围条件放在最后
CREATE INDEX idx_created_status ON orders(created_at, status);
-- 或者分开查询
解决方案：索引优化最佳实践
1. 索引设计原则
最左前缀原则
-- 根据查询条件顺序设计索引
-- 常用查询：WHERE a = ? AND b = ? AND c = ?
CREATE INDEX idx_abc ON table_name(a, b, c);
-- 常用查询：WHERE b = ? AND a = ?
-- 考虑索引顺序或创建两个索引
CREATE INDEX idx_ba ON table_name(b, a);
选择性原则
-- 选择性 = 不同值的数量 / 总行数
-- 选择性越高的列越适合作为索引
-- 示例：用户表
-- gender字段：只有2个值，选择性低（不适合索引）
-- phone字段：每个用户不同，选择性高（适合索引）
-- 计算选择性
SELECT
COUNT(DISTINCT gender) / COUNT(*) as gender_selectivity,
COUNT(DISTINCT phone) / COUNT(*) as phone_selectivity
FROM users;
-- gender_selectivity: 0.000002 (2/1000000)
-- phone_selectivity: 1.000000 (1000000/1000000)
2. 索引优化实战
单表索引优化
-- 优化前：多个单列索引
CREATE TABLE bad_index_design (
id BIGINT PRIMARY KEY,
user_id BIGINT,
status INT,
created_at TIMESTAMP,
INDEX idx_user_id (user_id),
INDEX idx_status (status),
INDEX idx_created_at (created_at)
);
-- 优化后：复合索引
CREATE TABLE good_index_design (
id BIGINT PRIMARY KEY,
user_id BIGINT,
status INT,
created_at TIMESTAMP,
-- 复合索引1：用户+状态+时间
INDEX idx_user_status_created (user_id, status, created_at),
-- 复合索引2：时间（用于时间范围查询）
INDEX idx_created_at (created_at)
);
覆盖索引优化
-- 场景：频繁查询用户订单列表（只需要订单ID和金额）
-- 优化前
SELECT order_id, total_amount
FROM orders
WHERE user_id = 100;
-- 需要回表查询
-- 优化后：创建覆盖索引
CREATE INDEX idx_user_cover ON orders(user_id, order_id, total_amount);
-- 所有字段都在索引中，无需回表
3. 索引维护
查看索引使用情况
-- 查看索引统计信息
SHOW INDEX FROM orders;
-- 查看索引 cardinality
SELECT
INDEX_NAME,
CARDINALITY,
TABLE_ROWS,
CARDINALITY / TABLE_ROWS as selectivity
FROM information_schema.STATISTICS
WHERE TABLE_NAME = 'orders';
-- 查看索引使用频率（Performance Schema）
SELECT
OBJECT_SCHEMA,
OBJECT_NAME,
INDEX_NAME,
COUNT_FETCH,
COUNT_INSERT,
COUNT_UPDATE,
COUNT_DELETE
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_NAME = 'orders';
清理无用索引
-- 查找从未使用的索引
SELECT
OBJECT_SCHEMA,
OBJECT_NAME,
INDEX_NAME
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE COUNT_FETCH = 0
AND INDEX_NAME IS NOT NULL;
-- 删除无用索引
DROP INDEX idx_unused ON orders;
实施步骤：索引优化流程
步骤1：收集慢查询
-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
-- 分析慢查询
SELECT * FROM mysql.slow_log
WHERE start_time > DATE_SUB(NOW(), INTERVAL 1 DAY)
ORDER BY query_time DESC
LIMIT 10;
步骤2：分析执行计划
-- 使用EXPLAIN分析
EXPLAIN SELECT * FROM orders WHERE user_id = 100\G
-- 关注指标
-- type: 访问类型，至少达到range
-- key: 实际使用的索引
-- rows: 扫描行数
-- Extra: 避免Using filesort, Using temporary
步骤3：设计优化方案
索引优化检查清单： □ 是否为WHERE条件列创建索引？ □ 是否为JOIN条件列创建索引？ □ 是否为ORDER BY列创建索引？ □ 是否遵循最左前缀原则？ □ 索引选择性是否足够高？ □ 是否可以创建覆盖索引？ □ 是否存在重复索引？ □ 是否存在从未使用的索引？
步骤4：实施与验证
-- 创建索引
CREATE INDEX idx_optimize ON orders(user_id, status, created_at);
-- 验证执行计划
EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 1;
-- 对比优化前后性能
-- 优化前：扫描10000行，耗时500ms
-- 优化后：扫描100行，耗时5ms
效果验证：优化前后对比
案例1：订单查询优化
| 指标 | 优化前 | 优化后 | 提升 |
|---|---|---|---|
| 查询时间 | 2s | 10ms | 99.5% ↓ |
| 扫描行数 | 1000万 | 100 | 99.99% ↓ |
| 使用索引 | 无 | idx_user_status | 新增 |
| CPU使用率 | 80% | 5% | 94% ↓ |
优化措施：
- 添加复合索引
(user_id, status, created_at) - 优化SQL避免函数操作
- 使用覆盖索引减少回表
案例2：索引清理
| 指标 | 优化前 | 优化后 | 效果 |
|---|---|---|---|
| 索引数量 | 15个 | 8个 | 减少47% |
| 索引大小 | 2GB | 1.1GB | 节省45% |
| 写入性能 | 1000 TPS | 1500 TPS | 提升50% |
| 查询性能 | 不变 | 不变 | 无影响 |
优化措施：
- 删除重复索引
- 删除从未使用的索引
- 合并相似索引
经验总结：索引最佳实践
✅ 应该做的
- 为WHERE、JOIN、ORDER BY字段创建索引
- 遵循最左前缀原则设计复合索引
- 选择性高的列放在复合索引前面
- 考虑使用覆盖索引减少回表
- 定期分析索引使用情况
- 删除无用索引减少维护开销
- 大数据量表考虑分区索引
- 使用EXPLAIN验证索引效果
❌ 不应该做的
- 在索引列上使用函数或运算
- 创建过多索引（影响写入性能）
- 为低选择性列创建单独索引
- 忽略隐式类型转换问题
- 创建重复索引
- 从不分析索引使用情况
- 大表操作不关注索引维护成本
- 盲目添加索引不验证效果
深度案例分析：电商系统索引优化
项目背景
某电商平台订单表数据量达到1亿条，查询性能严重下降：
- 订单列表查询平均响应时间3秒
- 高峰期数据库CPU飙升到95%
- 用户投诉搜索功能卡顿
原表索引问题
-- 原表结构
CREATE TABLE old_orders (
order_id BIGINT PRIMARY KEY,
user_id BIGINT,
status INT,
total_amount DECIMAL(10,2),
created_at TIMESTAMP,
-- 索引设计混乱
INDEX idx_user (user_id),
INDEX idx_status (status),
INDEX idx_created (created_at),
INDEX idx_user_status (user_id, status),
INDEX idx_status_user (status, user_id) -- 重复索引
);
-- 问题分析：
-- 1. 多个单列索引，无法同时使用
-- 2. 存在重复索引
-- 3. 缺少覆盖索引
-- 4. 没有考虑查询模式
优化方案
-- 分析查询模式
-- 模式1：用户查询自己的订单列表（最频繁）
-- SELECT * FROM orders WHERE user_id = ? ORDER BY created_at DESC LIMIT 20;
-- 模式2：按状态查询订单
-- SELECT * FROM orders WHERE status = ? AND created_at > ?;
-- 模式3：订单详情查询
-- SELECT * FROM orders WHERE order_id = ?;
-- 优化后索引设计
CREATE TABLE new_orders (
order_id BIGINT PRIMARY KEY,
user_id BIGINT,
status INT,
total_amount DECIMAL(10,2),
created_at TIMESTAMP,
-- 索引1：用户订单列表查询（覆盖索引）
INDEX idx_user_created (user_id, created_at, order_id, status, total_amount),
-- 索引2：状态+时间查询
INDEX idx_status_created (status, created_at),
-- 索引3：订单号查询（主键已满足）
-- 无需额外索引
);
优化效果
| 查询场景 | 优化前 | 优化后 | 提升 |
|---|---|---|---|
| 用户订单列表 | 3s | 20ms | 99.3% ↓ |
| 状态筛选查询 | 5s | 50ms | 99% ↓ |
| 订单详情 | 10ms | 2ms | 80% ↓ |
| 数据库CPU | 95% | 30% | 68% ↓ |
| 索引大小 | 3GB | 1.5GB | 50% ↓ |
索引优化决策树
查询性能问题
↓
是否使用了索引？
↓ 否
检查WHERE条件是否有索引
↓
是否存在函数操作？
↓ 是
改写SQL避免函数操作
↓
是否遵循最左前缀？
↓ 否
调整查询条件顺序或重建索引
↓
索引选择性是否足够？
↓ 否
考虑其他优化方案（如分区）
↓
是否可以覆盖索引？
↓ 是
创建覆盖索引
↓
优化完成
常见误区与避坑指南
❌ 误区1：索引越多越好
问题：过多索引严重影响写入性能，占用大量存储空间。
正确做法：只为必要的查询创建索引，定期清理无用索引。
❌ 误区2：所有字段都建索引
问题：低选择性字段（如性别、状态）单独建索引效果很差。
正确做法：选择性高的字段才适合建索引，低选择性字段应放在复合索引后面。
❌ 误区3：忽略索引维护成本
问题：大表创建索引会锁表，影响线上业务。
正确做法：
- 使用Online DDL（MySQL 5.6+）
- 在低峰期执行
- 使用pt-online-schema-change工具
-- MySQL 8.0 Online DDL
ALTER TABLE orders ADD INDEX idx_new (user_id), ALGORITHM=INPLACE, LOCK=NONE;
❌ 误区4：从不分析索引使用情况
问题：创建了索引但从未使用，浪费资源。
正确做法：定期分析索引使用情况，删除无用索引。
索引评估模板
索引设计评估表
| 评估项 | 权重 | 评分 | 说明 |
|---|---|---|---|
| 是否覆盖主要查询 | 25% | /10 | |
| 是否遵循最左前缀 | 20% | /10 | |
| 索引选择性 | 20% | /10 | |
| 是否存在重复索引 | 15% | /10 | |
| 写入性能影响 | 20% | /10 | |
| 总分 | 100% | /10 |
索引检查清单
□ 是否为所有WHERE条件列创建索引？ □ 是否为JOIN条件列创建索引？ □ 是否为ORDER BY列创建索引？ □ 复合索引是否遵循最左前缀？ □ 索引列选择性是否大于0.1？ □ 是否存在重复索引？ □ 是否存在未使用的索引？ □ 是否使用覆盖索引优化频繁查询？ □ 大表加索引是否使用Online DDL？ □ 是否定期分析索引使用情况？
附录
A. 索引类型速查表
| 索引类型 | 适用场景 | 优点 | 缺点 |
|---|---|---|---|
| PRIMARY KEY | 主键 | 唯一标识，聚簇索引 | 只能有一个 |
| UNIQUE | 唯一约束 | 保证数据唯一性 | 插入时需要检查 |
| INDEX | 普通查询加速 | 提高查询速度 | 占用空间，影响写入 |
| FULLTEXT | 文本搜索 | 支持分词搜索 | 占用空间大 |
| SPATIAL | 空间数据 | 支持地理查询 | 使用场景有限 |
B. EXPLAIN字段速查
| 字段 | 含义 | 优化建议 |
|---|---|---|
| type | 访问类型 | 至少达到range，最好是ref或const |
| possible_keys | 可能使用的索引 | 检查是否包含预期索引 |
| key | 实际使用的索引 | 确保使用了正确的索引 |
| key_len | 索引长度 | 越长表示使用索引列越多 |
| rows | 扫描行数 | 越小越好 |
| Extra | 额外信息 | 避免Using filesort, Using temporary |
C. 索引优化案例库
## 案例1：用户系统索引优化
- 场景：用户登录、信息查询
- 优化前：仅主键索引
- 优化后：
- INDEX idx_username (username)
- INDEX idx_phone (phone)
- INDEX idx_email (email)
- 效果：登录查询从500ms降到5ms
## 案例2：订单系统索引优化
- 场景：订单列表、详情查询
- 优化前：多个单列索引
- 优化后：
- INDEX idx_user_created (user_id, created_at)
- INDEX idx_status_created (status, created_at)
- 效果：订单列表查询从3s降到20ms
## 案例3：日志系统索引优化
- 场景：日志查询、分析
- 优化前：无索引（日志表）
- 优化后：
- INDEX idx_created (created_at)
- 按时间分区
- 效果：日志查询从10s降到100ms
读者练习
-
思考题：分析你项目中的索引设计，找出3个可以优化的地方。
-
实践题：为以下查询设计最优索引：
SELECT * FROM orders
WHERE user_id = 100
AND status IN (1, 2)
AND created_at > '2023-01-01'
ORDER BY total_amount DESC
LIMIT 20;
- 挑战题：实现一个索引使用监控工具，自动发现无用索引。
系列上一篇：高效SQL编写：从基础到高级技巧
系列下一篇：查询优化器揭秘：统计信息与执行计划
知识点测试
读完文章了？来测试一下你对知识点的掌握程度吧！
评论区
使用 GitHub 账号登录后即可发表评论，支持 Markdown 格式。
如果评论系统无法加载，请确保：
- 您的网络可以访问 GitHub
- giscus GitHub App 已安装到仓库
- 仓库已启用 Discussions 功能