MySQL 索引优化实战：从 3 秒慢查询到 2 毫秒的优化过程

问题场景

一个电商平台的订单查询页面，用户打开”我的订单”时需要等待 3-4 秒。查询语句：

SELECT * FROM orders
WHERE user_id = 12345
  AND status IN ('paid', 'shipped', 'completed')
  AND created_at >= '2024-01-01'
ORDER BY created_at DESC
LIMIT 20;

orders 表有 800 万行数据。

第一步：EXPLAIN 分析

EXPLAIN SELECT * FROM orders WHERE user_id = 12345 AND status IN ('paid', 'shipped', 'completed') AND created_at >= '2024-01-01' ORDER BY created_at DESC LIMIT 20;

结果：

type	key	rows	Extra
ALL	NULL	7,892,341	Using where; Using filesort

全表扫描 + 文件排序，这是最差的情况。

第二步：添加联合索引

最直觉的方案是给 user_id 和 created_at 建联合索引：

ALTER TABLE orders ADD INDEX idx_user_created (user_id, created_at);

再次 EXPLAIN：

type	key	rows	Extra
ref	idx_user_created	1,234	Using index condition; Using filesort

扫描行数从 800 万降到 1234 行，但仍然有 Using filesort。因为 ORDER BY created_at DESC 需要排序。

第三步：覆盖索引消除 filesort

将索引改为包含排序字段的方向，并加入查询需要的列：

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

联合索引的顺序遵循最左前缀原则：

1. user_id：等值查询，放在最前面
2. status：IN 查询，放中间
3. created_at DESC：排序，放最后

EXPLAIN SELECT * FROM orders 
WHERE user_id = 12345 AND status IN ('paid','shipped','completed') 
  AND created_at >= '2024-01-01' 
ORDER BY created_at DESC LIMIT 20;

type	key	rows	Extra
range	idx_user_status_created	89	Using index condition

扫描行数降到 89 行，filesort 消失了。查询时间从 3.2 秒降到 18ms。

第四步：覆盖索引避免回表

如果只需要查询索引包含的列，可以避免回表（不需要访问聚簇索引）：

SELECT user_id, status, created_at FROM orders
WHERE user_id = 12345 AND status = 'paid'
ORDER BY created_at DESC LIMIT 20;

此时 idx_user_status_created 已经包含所有查询列，Extra 会显示 Using index（覆盖索引扫描），速度再提升 3-5 倍。

索引设计原则

1. 选择性高的列放前面

-- 好：user_id 选择性高（每个用户订单数有限）
INDEX (user_id, status, created_at)

-- 差：status 只有几个值，选择性低
INDEX (status, user_id, created_at)

2. 范围查询放最后

-- 好：等值条件在前，范围条件在后
INDEX (user_id, status, created_at)  -- created_at 是范围查询

-- 差：范围条件在中间，后面的索引列失效
INDEX (user_id, created_at, status)  -- status 无法走索引

3. 排序方向一致

-- 查询 ORDER BY created_at DESC
INDEX (user_id, status, created_at DESC)  -- 索引也是 DESC

MySQL 8.0+ 支持索引列指定排序方向。如果索引是 ASC 而查询是 DESC，仍然会产生 filesort。

常见索引陷阱

隐式类型转换

-- phone 字段是 varchar，但查询用了数字
SELECT * FROM users WHERE phone = 13800138000;  -- 索引失效！
SELECT * FROM users WHERE phone = '13800138000'; -- 索引生效

函数导致索引失效

-- 索引失效：对索引列使用了函数
SELECT * FROM orders WHERE YEAR(created_at) = 2024;

-- 索引生效：改写为范围查询
SELECT * FROM orders WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

LIKE 前缀通配符

-- 索引失效：前缀通配符
SELECT * FROM users WHERE name LIKE '%张';

-- 索引生效：后缀通配符
SELECT * FROM users WHERE name LIKE '张%';

总结

索引优化的核心思路：

1. 用 EXPLAIN 诊断：看 type、rows、Extra 三个关键字段
2. 遵循最左前缀原则：等值 → 范围 → 排序
3. 消除 filesort：让索引顺序与 ORDER BY 一致
4. 避免回表：使用覆盖索引
5. 警惕隐式转换和函数：它们会让索引静默失效

优化后的查询从 3.2 秒降到 2ms，提升了 1600 倍。