MySQL 慢 SQL 排查与索引设计实战

"数据库 CPU 打满了""接口突然超时"——大多数时候，问题都出在几条慢 SQL 上。一条查询在数据量小的时候跑得飞快，数据量上来后却能拖垮整个系统。本文梳理从发现慢 SQL 到设计索引的完整实战路径。

一、如何发现慢 SQL

1.1 开启慢查询日志

-- 查看当前配置
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';

-- 动态开启（临时生效）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;   -- 超过 1 秒记录
SET GLOBAL log_queries_not_using_indexes = 'ON';  -- 记录未走索引的查询

生产环境建议将 long_query_time 设为 1 秒，并配合 mysqldumpslow 工具对日志做聚合分析，找出高频慢语句：

# 按执行次数排序
mysqldumpslow -s c /var/log/mysql/slow.log | head -20

1.2 借助 performance_schema

MySQL 5.7+ 可以直接查询 performance_schema 中的语句统计，无需开启日志：

SELECT DIGEST_TEXT, COUNT_STAR, AVG_TIMER_WAIT / 1e12 AS avg_sec,
       SUM_ROWS_EXAMINED / COUNT_STAR AS avg_rows
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;

avg_rows（平均扫描行数）是判断是否走索引的关键指标——扫描行数远超返回行数，几乎可以断定索引缺失。

二、EXPLAIN 执行计划解读

拿到慢 SQL 后，第一步就是用 EXPLAIN 看执行计划：

EXPLAIN SELECT * FROM orders
WHERE user_id = 1024 AND status = 'PAID'
ORDER BY created_at DESC LIMIT 20;

+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+----------------+
|  1 | SIMPLE      | orders | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 523401 |    10.00 | Using filesort  |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+----------------+

重点关注三个字段：

字段	信号	含义
`type`	ALL	全表扫描，最差的情况，必须优化
`rows`	52 万	预计扫描 52 万行，返回却只要 20 行
`Extra`	Using filesort	排序未走索引，额外消耗内存/磁盘

这个执行计划同时暴露了三个问题：没走索引 + 全表扫描 + 文件排序。而优化方向也很明确——建立合适的联合索引。

三、索引设计的核心原则

3.1 最左前缀原则

联合索引 (user_id, status, created_at) 可以命中以下查询：

WHERE user_id = ? ✅
WHERE user_id = ? AND status = ? ✅
WHERE user_id = ? AND status = ? ORDER BY created_at ✅

但跳过最左列的查询用不上该索引：WHERE status = 'PAID' ❌。所以设计索引时，字段顺序必须按"等值条件 → 范围条件 → 排序字段"来排。

3.2 覆盖索引

如果查询只需要索引中的列，就无需回表，性能翻倍。例如：

-- 只需要 user_id 和 status 两列
SELECT user_id, status FROM orders WHERE user_id = 1024;
-- 索引 (user_id, status) 直接覆盖，Extra 显示 Using index

3.3 索引下推（ICP）

MySQL 5.6+ 默认开启：联合索引中，对无法用于索引定位但存在于索引中的列，在索引层先过滤再回表，减少回表次数。这也是联合索引尽量包含更多过滤列的原因之一。

四、实战案例：优化前后对比

4.1 优化前

SELECT id, amount, status FROM orders
WHERE user_id = 1024 AND status = 'PAID'
ORDER BY created_at DESC LIMIT 20;
-- 耗时：1.8s  扫描：52 万行

4.2 建立索引

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

4.3 优化后

+----+-----------+--------+----------+-------+-----------------------+------+----------+-------+
| id | table     | type   | key      | rows  | Extra                 | key_len | ref     | rows  |
+----+-----------+--------+----------+-------+-----------------------+------+----------+-------+
|  1 | orders    | ref    | idx_user_status_created | 36    | Using index condition | 98  | const   |  36   |
+----+-----------+--------+----------+-------+-----------------------+------+----------+-------+
-- 耗时：8ms  扫描：36 行  提升 225 倍

三个变化一目了然：type 从 ALL 变为 ref，rows 从 52 万降到 36，Using filesort 消失（排序直接走索引）。

五、其他常见优化手段

避免 SELECT *：只取需要的列，配合覆盖索引减少回表；
避免对索引列使用函数：WHERE DATE(created_at) = '2026-08-01' 会让索引失效，改为范围查询 created_at >= '2026-08-01' AND created_at < '2026-08-02'；
分页优化：深分页 LIMIT 100000, 20 用延迟关联（先查主键再回表）替代；
控制索引数量：索引不是越多越好，写入成本与存储开销都要权衡，单表建议不超过 5 个联合索引。

六、总结

慢 SQL 优化是典型的"流程化工作"：慢日志/performance_schema 发现 → EXPLAIN 定位 → 按最左前缀设计索引 → 对比验证。掌握这套方法，配合对执行计划的敏感度，绝大多数查询性能问题都能在十分钟内解决。