MySQL 索引优化实战：从慢查询到毫秒级响应

接手的电商后台日活 50 万，订单查询接口平均 2.3 秒，最慢的冲到 8 秒。三个月后同一个接口稳定在 15ms 以内。这篇记录我在这过程中摸到的索引原则和排查方法。

第一课：先找到凶手，别凭感觉优化

没跑 EXPLAIN 之前别碰索引。我见过太多人上来就给搜索字段加索引——加完该慢还是慢。

-- 打开慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 1;

跑了一天业务，慢查询日志里冒出这条：

SELECT o.id, o.order_no, o.amount, o.status, u.name
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE o.created_at > '2024-01-01'
  AND o.status IN (1, 3, 5)
ORDER BY o.amount DESC
LIMIT 20;

EXPLAIN 输出： type: ALL（全表扫描），rows: 487,632，Extra: Using filesort。

全表扫了 48 万行，还走了文件排序。这就是元凶。

复合索引：最左前缀法则的实战理解

第一个直觉是给 created_at 加索引。加完 EXPLAIN 一看——type: range，rows 降到 8 万，还行，但 Extra 依然显示 Using filesort。

原因很简单：ORDER BY amount 用不上单列索引的排序。

关键认知：MySQL 的 B+Tree 索引本身有序，但排序字段必须是索引的一部分，且遵循最左前缀。

我加了复合索引：

ALTER TABLE orders ADD INDEX idx_status_amount (`status`, `amount`);

等等——不对。WHERE 里还有 created_at 的范围查询。

这里藏着一个深坑：范围查询（>、<、BETWEEN）之后的索引列无法用于后续的排序和过滤。

-- ❌ 错误：created_at 范围查询导致 amount 无法用索引排序
INDEX(created_at, status, amount)
-- 在 created_at > '2024-01-01' 之后，amount 的排序依赖满足不了

-- ✅ 正确：把范围查询的字段放到最后
INDEX(status, amount, created_at)

最终索引设计：

ALTER TABLE orders ADD INDEX idx_status_amount_date (`status`, `amount`, `created_at`);

EXPLAIN 再次输出：type: ref，rows: 1,247，Extra: Using index condition。

查询从 2.3 秒降到了 180ms。

覆盖索引：一次 10 倍加速的意外发现

优化完上面那条，我顺手看了另一个慢查询：

SELECT o.id, o.order_no, o.amount, o.status, o.created_at
FROM orders o
WHERE o.status = 2
ORDER BY o.created_at DESC
LIMIT 100;

注意——这条 SQL 只查了 orders 表自身的字段，没有 JOIN。那就没必要回表。

-- 覆盖索引：查询所需的所有字段都在索引里
ALTER TABLE orders ADD INDEX idx_status_created_id (`status`, `created_at`, `id`, `order_no`, `amount`);

Extra 从 Using where; Using filesort 变成了 Using where; Using index。

查询耗时：120ms → 11ms。

注意：覆盖索引不是万能药。索引本身占磁盘，每多一个索引，写入就多一分开销。我一般只在查询频率极高且返回字段有限的场景才用。

分页深翻页：一个被忽视的索引杀手

运营后台要导出一批历史订单：

SELECT * FROM orders
WHERE status = 5
ORDER BY id
LIMIT 200000, 50;

这个查询跑了 11 秒。原因很简单：LIMIT 200000, 50 不是只查 50 条，而是查 200050 条，再扔掉前 20 万条。

EXPLAIN 显示 type: ref，rows 只扫了 1000 多——但 Extra 有 Using index condition; Using filesort。

等等，ORDER BY id 为什么还 filesort？因为 WHERE 条件是 status = 5，MySQL 走的是 status 索引，拿到的行按 status 排序，不是按 id 排序的。

解决方案：延迟关联 + 强制走主键排序。

SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders
    WHERE status = 5
    ORDER BY id
    LIMIT 200000, 50
) AS tmp ON o.id = tmp.id;

11 秒 → 0.5 秒。

原理：子查询只在索引上操作。进一步优化——如果 status 区分度低，可以直接用 WHERE id > 上一页最后一条 的方式做游标分页，彻底消灭 OFFSET。

区分度：最常见的「加了索引反而更慢」

有一次我把 orders 的 is_deleted 字段（值只有 0 和 1）加了索引。结果某条查询从 500ms 变成了 3 秒。

原因：区分度太低。MySQL 优化器估算后发现走这个索引要扫超过 30% 的行，不如直接全表扫描快——但扫描方式变了导致排序策略改变，反而更慢。

查看区分度：

SELECT COUNT(DISTINCT `is_deleted`) / COUNT(*) AS selectivity
FROM orders;
-- 结果：0.000002

经验法则：区分度低于 20% 的字段，除非覆盖索引场景，否则单列索引通常是负优化。

血的教训：联表查询的索引陷阱

SELECT o.*, oi.sku_name, oi.quantity, u.phone
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN users u ON o.user_id = u.id
WHERE o.status = 3 AND oi.sku_id = 12345
LIMIT 20;

我以为关联字段都有索引就万事大吉了。实际 EXPLAIN 一看：

• orders：type: ref，用了 idx_status，扫描 3000 行
• order_items：type: ref，用了 idx_order_id，挺好
• users：type: ALL，全表 50 万行

为什么 users 全表？ MySQL Nested Loop Join 的执行顺序是：先扫 orders（3000 行），对每一行去联 order_items 和 users。users 表的 JOIN 条件是 o.user_id = u.id，但 user_id 在 users 表是主键（默认有唯一索引）——这里 EXPLAIN 为何显示 ALL？

仔细看：o.user_id 是 int，u.id 是 varchar。

隐式类型转换导致索引失效。

-- 修复后
WHERE o.status = 3 AND oi.sku_id = 12345
-- 以及确保 user_id 类型一致

改完类型匹配后，users 表变回 type: eq_ref，查询从 4.2 秒降到 30ms。

监控闭环：优化后的验证手段

每次改完索引，我必跑三条验证：

-- 1. EXPLAIN 确认 type、rows、Extra
EXPLAIN SELECT ...;

-- 2. 实际执行时间（多跑几次，排除缓存影响）
SET profiling = 1;
-- 执行你的查询
SHOW PROFILES;

-- 3. 索引使用统计
SELECT * FROM sys.schema_unused_indexes;

用 sys.schema_unused_indexes 定期清理半年没被用过的索引，减少写入开销。

写在最后

三个月下来，我最深的体会是：索引优化不是技术问题，而是信息问题。 EXPLAIN 已经把答案写在纸上了，大多数时候是我们没去读。

还有一件事让我挺意外的——优化完所有慢查询后，数据库 CPU 使用率从 85% 降到了 30%。索引优化的收益不只是查询快了，是整个系统的负载都降了。这就引出了另一个话题：当索引优化到头了，下一步该怎么做（分库分表、读写分离还是缓存层）？那是另一个故事了。