MySQL 索引优化实战：从原理到实践

一个真实的性能问题

上周遇到一个查询，执行时间从 200ms 逐渐涨到 8 秒：

SELECT * FROM orders 
WHERE user_id = 12345 
  AND status = 'PAID' 
  AND created_at > '2026-01-01'
ORDER BY created_at DESC
LIMIT 20;

表结构：

• orders 表：500 万行数据
• 已有索引：idx_user_id (user_id)

问题：为什么有索引还这么慢？

理解 B+ 树索引

索引的本质

索引不是魔法，它是一个数据结构，用于快速定位数据。

MySQL InnoDB 使用 B+ 树：

        [10, 20, 30]       ← 根节点（索引页）
       /    |    |    \
  [1-9] [11-19] [21-29] [31-50]  ← 叶子节点（数据页）

B+ 树的特点：

• 所有数据都在叶子节点
• 叶子节点之间有指针连接（范围查询快）
• 树高度通常为 3-4 层（1000 万数据也在 4 层内）

聚簇索引 vs 二级索引

聚簇索引（主键索引）：

• 叶子节点存储整行数据
• 一张表只有一个
• 通常是主键

二级索引（辅助索引）：

• 叶子节点存储主键值
• 查询时需要回表（再查一次主键索引）

二级索引：key → 主键值
聚簇索引：主键值 → 整行数据

回表的代价：

• 随机 I/O
• 可能从内存跌到磁盘
• 大量回表时，索引可能不如全表扫描

EXPLAIN：诊断查询的 X 光机

回到开头的问题，用 EXPLAIN 分析：

EXPLAIN SELECT * FROM orders 
WHERE user_id = 12345 
  AND status = 'PAID' 
  AND created_at > '2026-01-01'
ORDER BY created_at DESC
LIMIT 20;

结果：

id | select_type | table  | type  | key         | rows  | Extra
---|-------------|--------|-------|-------------|-------|----------------
 1 | SIMPLE      | orders | ref   | idx_user_id | 50000 | Using where; Using filesort

关键信息：

• type = ref：使用了索引，但非唯一
• rows = 50000：扫描了 5 万行
• Using filesort：文件排序，说明索引没覆盖 ORDER BY

为什么 filesort 慢？

1. MySQL 先用 idx_user_id 找到所有 user_id = 12345 的记录（5 万条）
2. 再过滤 status = 'PAID' 和 created_at > '2026-01-01'
3. 最后在内存/临时文件中排序 created_at
4. 取前 20 条

问题根源：索引只覆盖了 user_id，其他条件都需要额外处理。

索引优化策略

策略一：创建复合索引

ALTER TABLE orders ADD INDEX idx_user_status_created (user_id, status, created_at);

再次 EXPLAIN：

id | select_type | table  | type | key                      | rows | Extra
---|-------------|--------|------|--------------------------|------|---------------------
 1 | SIMPLE      | orders | ref  | idx_user_status_created  | 100  | Using where

改进：

• rows 从 50000 降到 100
• Using filesort 消失了
• 执行时间从 8 秒降到 50ms

策略二：理解最左前缀原则

复合索引 (user_id, status, created_at) 遵循最左前缀原则：

查询条件	能否使用索引	使用部分
user_id = ?	✅	user_id
user_id = ? AND status = ?	✅	user_id, status
user_id = ? AND status = ? AND created_at > ?	✅	全部
status = ?	❌	无（跳过 user_id）
status = ? AND created_at > ?	❌	无
created_at > ?	❌	无

经验法则：

• 等值条件放前面
• 范围条件放后面
• 高频查询条件优先

策略三：覆盖索引

如果查询只需要索引中的字段，可以避免回表：

-- 原查询（需要回表）
SELECT * FROM orders WHERE user_id = 12345;

-- 优化后（覆盖索引）
SELECT id, user_id, status FROM orders WHERE user_id = 12345;

覆盖索引的优势：

• 不需要回表
• 减少 I/O
• 查询更快

实战技巧：

-- 如果只需要计数
SELECT COUNT(*) FROM orders WHERE user_id = 12345;
-- COUNT(*) 可以直接用索引统计，无需回表

策略四：避免索引失效

常见陷阱：

1. 对索引列做函数操作

   -- ❌ 索引失效
   WHERE DATE(created_at) = '2026-03-18'
   
   -- ✅ 保持索引有效
   WHERE created_at >= '2026-03-18 00:00:00' 
     AND created_at < '2026-03-19 00:00:00'

2. 隐式类型转换

   -- ❌ 字符串字段用数字查询
   WHERE phone = 13800138000
   
   -- ✅ 保持类型一致
   WHERE phone = '13800138000'

3. LIKE 以通配符开头

   -- ❌ 索引失效
   WHERE name LIKE '%张%'
   
   -- ✅ 索引有效
   WHERE name LIKE '张%'

4. OR 条件使用不当

   -- ❌ 如果 status 没有索引，整个索引失效
   WHERE user_id = 12345 OR status = 'PAID'
   
   -- ✅ 用 UNION 替代
   SELECT * FROM orders WHERE user_id = 12345
   UNION
   SELECT * FROM orders WHERE status = 'PAID'

索引不是越多越好

索引的代价

1. 写入性能下降

   • 每次 INSERT/UPDATE/DELETE 都要更新索引
   • 索引越多，写入越慢

2. 占用磁盘空间

   • 索引也是数据，需要存储
   • 大表索引可能占用几十 GB

3. 维护成本

   • 索引需要定期优化（OPTIMIZE TABLE）
   • 统计信息需要更新（ANALYZE TABLE）

什么时候不需要索引？

1. 小表（< 1000 行）：全表扫描更快
2. 低基数字段（如性别）：区分度太低
3. 很少查询的字段
4. 频繁更新的字段

实战案例总结

案例一：分页查询优化

问题：

SELECT * FROM articles ORDER BY created_at DESC LIMIT 100000, 20;

深度分页，执行时间 5 秒+

优化：

-- 方案 1：延迟关联
SELECT a.* FROM articles a
INNER JOIN (
    SELECT id FROM articles ORDER BY created_at DESC LIMIT 100000, 20
) b ON a.id = b.id;

-- 方案 2：记录上次查询的最大 ID
SELECT * FROM articles 
WHERE created_at < {last_created_at} 
ORDER BY created_at DESC LIMIT 20;

案例二：GROUP BY 优化

问题：

SELECT user_id, COUNT(*) as cnt 
FROM orders 
WHERE created_at > '2026-01-01'
GROUP BY user_id;

优化：

-- 创建索引
ALTER TABLE orders ADD INDEX idx_created_user (created_at, user_id);

-- 利用索引覆盖，避免临时表

案例三：JOIN 优化

原则：

• 小表驱动大表
• JOIN 字段必须有索引
• 类型必须一致

-- 确保关联字段有索引
ALTER TABLE orders ADD INDEX idx_user_id (user_id);
ALTER TABLE users ADD INDEX idx_id (id);

-- 类型一致（都是 INT 或都是 BIGINT）

监控与调优

慢查询日志

# my.cnf 配置
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1  # 超过 1 秒的查询
log_queries_not_using_indexes = 1

性能分析工具

-- 查看索引使用情况
SHOW INDEX FROM orders;

-- 查看表统计信息
SHOW TABLE STATUS LIKE 'orders';

-- 分析表
ANALYZE TABLE orders;

-- 优化表（重建索引）
OPTIMIZE TABLE orders;

总结

索引优化的核心思路：

1. 理解查询模式：先搞清楚查询怎么写的
2. EXPLAIN 分析：不要猜，用数据说话
3. 创建合适索引：复合索引 + 最左前缀原则
4. 避免索引失效：函数、类型转换、通配符
5. 权衡利弊：索引不是免费的

最后提醒：

• 生产环境改索引前，先在测试环境验证
• 大表加索引要考虑锁表时间
• 定期清理无用索引

---

性能优化是永无止境的，但 80% 的问题都可以通过合理的索引解决。