索引设计与慢查询优化

在基础篇索引原理之上，本章结合 MySQL 的实际行为，给出可落地的索引设计与慢查询治理方法。

EXPLAIN 实战

EXPLAIN FORMAT=JSON
SELECT u.name, o.amount
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'paid'
ORDER BY o.created_at DESC
LIMIT 20;

重点关注：

字段	目标
`type`	尽量避免 `ALL`（全表）与 `index`（全索引扫描）
`key`	确认命中的是预期索引
`rows`	预估扫描行数应接近返回行数
`Extra`	避免 `Using filesort` / `Using temporary`；`Using index` 最优

联合索引设计原则

等值查询列放前面，范围查询列放最后。
区分度高的列优先：区分度 = 不重复值数 / 总行数。
尽量让高频查询走覆盖索引，消除回表。
遵循最左前缀，避免冗余索引。

-- 高频查询：WHERE tenant_id = ? AND status = ? ORDER BY created_at DESC
-- 设计联合索引：等值列在前，排序列在后
ALTER TABLE orders
  ADD INDEX idx_tenant_status_created (tenant_id, status, created_at);

-- 这句查询可完全命中该索引，且无 filesort
SELECT id, amount
FROM orders
WHERE tenant_id = 42 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

区分度评估

-- 值越接近 1 区分度越高
SELECT COUNT(DISTINCT status) / COUNT(*) AS status_sel,
       COUNT(DISTINCT user_id) / COUNT(*) AS user_sel
FROM orders;

Tip

ORDER BY 的方向会与索引顺序耦合。ORDER BY created_at DESC 在 (tenant_id, status, created_at) 索引上可以正序扫描再逆序返回，无需 filesort；但如果排序方向与索引不一致（如 a ASC, b DESC 混合），MySQL 8 才支持降序索引 (a ASC, b DESC)。

慢查询治理流程

开启慢查询日志，设定阈值（如 1s），收集 Top SQL。
用 EXPLAIN 分析问题 SQL 的访问路径。
补/改索引，或改写 SQL（消除函数、优化分页）。
验证效果，观察 rows、耗时与 QPS 变化。
回归与监控，防止索引被误删或数据分布变化导致回退。

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = ON;  -- 记录未走索引的语句

-- 用 mysqldumpslow 汇总慢日志
-- mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

常见反模式对照

反模式	问题	改写
`WHERE DATE(created_at) = '2026-01-01'`	函数使索引失效	用范围 `>= ... AND < ...`
`WHERE phone = 13800000000`（phone 为字符列）	隐式转换	加引号 `'138...'`
`WHERE name LIKE '%张%'`	前导 `%` 无法用索引	前端搜索或全文索引
`ORDER BY RAND()`	全表 + 排序	应用层随机 / 采样
`SELECT *` 大宽表	无法覆盖、传输冗余	只取需要的列
深分页 `LIMIT 1000000, 20`	扫描并丢弃大量行	键集分页

-- 反例
SELECT id, amount FROM orders ORDER BY id LIMIT 1000000, 20;

-- 正例：键集分页，始终走主键范围
SELECT id, amount FROM orders WHERE id > :last_id ORDER BY id LIMIT 20;

Warning

索引不是越多越好：每个索引都会拖慢写入、占用空间，并增加优化器误判的概率。上线新索引前，用 pt-index-usage 或 performance_schema 分析现有索引的实际使用率，定期清理无用索引。

小结

用 EXPLAIN 定位访问类型、命中索引与额外排序/临时表。
联合索引遵循「等值在前、范围在后、高区分度在前」。
慢查询治理是「采集 → 分析 → 改造 → 验证 → 监控」的闭环。
记住常见反模式，改写往往比加索引更有效。

#索引设计与慢查询优化

#EXPLAIN 实战

#联合索引设计原则

#区分度评估

#慢查询治理流程

#常见反模式对照