作者 lucy · 2026-04-14
⚠️ 声明：本文相关内容仅供参考，实际效果因场景不同可能差异很大，请结合自身情况判断，谨慎参考。
数据库慢查询是后端系统最常见的性能瓶颈之一。一次糟糕的 SQL，轻则拖慢接口响应，重则把整个数据库拖崩。本文结合一个真实的生产优化案例，梳理从发现到解决的完整思路。
一、问题背景
凌晨 2 点，告警响起：订单列表接口响应时间超过 5 秒。查看监控，发现数据库 CPU 使用率飙升至 90%+，慢查询日志堆积了上百条执行时间超过 10 秒的 SQL。
二、定位根因
1. 开启慢查询日志
-- 查看慢查询配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time%';
-- 开启慢查询日志（临时）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- 超过1秒记录
-- 查看慢查询日志位置
SHOW VARIABLES LIKE 'slow_query_log_file';2. 使用 EXPLAIN 分析执行计划
EXPLAIN SELECT o.id, o.order_no, u.username, o.total_amount, o.status, o.created_at
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending'
AND o.created_at > '2026-01-01'
ORDER BY o.created_at DESC
LIMIT 20;关键字段含义：
| 字段 | 含义 | 优化重点 |
|---|---|---|
| type | 访问类型，ALL 为全表扫描 | 尽量达到 ref/range 以上 |
| key | 实际使用的索引 | 应为非空，否则未命中索引 |
| rows | 扫描行数估算 | 数值越大越差，需结合 limit 评估 |
| Extra | Using filesort / Using temporary | 这两个出现说明排序/临时表开销大 |
三、常见慢查询原因与解决方案
① 全表扫描（type=ALL）
最常见原因：WHERE 条件字段无索引或索引失效。
-- 检查现有索引
SHOW INDEX FROM orders;
-- 为高频查询条件建索引
CREATE INDEX idx_orders_status_created ON orders(status, created_at DESC);
-- 联合索引命中原则：最左前缀匹配
-- 索引 (status, created_at) 可加速以下场景：
-- WHERE status = 'pending'
-- WHERE status = 'pending' AND created_at > '...'
-- 但无法加速 WHERE created_at > '...' （跳过status）② 索引失效：函数/运算导致
-- 错误：WHERE 子句对索引列使用了函数
WHERE DATE(created_at) = '2026-04-14' -- ❌ 索引失效
WHERE created_at >= '2026-04-14 00:00:00' -- ✅ 正确写法
WHERE created_at < '2026-04-15 00:00:00'
-- 错误：隐式类型转换
WHERE order_no = 12345 -- ❌ order_no 是 varchar
WHERE order_no = '12345' -- ✅ 字符串加引号
-- 错误：OR 导致索引断链
WHERE status = 'pending' OR created_at > '2026-04-01' -- ❌
-- 改为 UNION 分别查询或用 IN：
WHERE status = 'pending'
UNION ALL
SELECT ... WHERE created_at > '2026-04-01'③ ORDER BY 引发 filesort
-- 当 ORDER BY 字段不在联合索引中，或排序方向不一致
-- 例：联合索引 (status, created_at DESC)
-- 可直接利用索引顺序，无需额外排序
-- 检查是否产生 filesort
EXPLAIN ... -- Extra: Using filesort = 差，需优化④ 深分页问题（OFFSET 过大）
-- 传统深分页：OFFSET 越大越慢（MySQL 先扫描 OFFSET+N 行再丢弃前 OFFSET 行）
SELECT * FROM orders
ORDER BY id DESC
LIMIT 1000000, 20; -- ❌ 扫描100万行
-- 优化方案1：游标分页（性能最优）
SELECT * FROM orders
WHERE id < #{last_id}
ORDER BY id DESC
LIMIT 20; -- ✅ 只扫描20行
-- 优化方案2：延迟关联
SELECT o.* FROM orders o
INNER JOIN (SELECT id FROM orders ORDER BY id DESC LIMIT 1000000, 20) t
ON o.id = t.id; -- ✅ 先扫索引列，再回表取数据四、优化实战：10秒 → 0.05秒
原始慢查询（耗时 10.3s）：
SELECT o.id, o.order_no, u.username, o.total_amount, o.status, o.created_at
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending'
ORDER BY o.created_at DESC
LIMIT 20;优化步骤：
- EXPLAIN 诊断：type=ALL 全表扫描，Extra=Using filesort
- 建联合索引：`CREATE INDEX idx_status_created ON orders(status, created_at DESC);`
- 覆盖索引：将 SELECT 字段改为 `SELECT o.id, o.order_no, ...` 避免回表
-- 优化后（耗时 0.048s）
SELECT o.id, o.order_no, u.username, o.total_amount, o.status, o.created_at
FROM orders o
USE INDEX (idx_status_created)
LEFT JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending'
ORDER BY o.created_at DESC
LIMIT 20;五、预防机制
-
定期巡检慢查询日志：每周分析一次慢查询日志，重点关注 rows 扫描量大的 SQL
-
上线前强制 EXPLAIN：所有涉及新索引或新 SQL 的上线，必须附上 EXPLAIN 结果
-
控制单表索引数量：索引并非越多越好，频繁更新的表索引过多会影响写入性能，建议单表不超过 5 个索引
-
监控告警：配置 Prometheus + Grafana 监控慢查询数量，设置阈值告警
-- 监控 SQL（每分钟执行一次）
SELECT COUNT(*) AS slow_count
FROM information_schema.PROCESSLIST
WHERE command != 'Sleep'
AND time > 10; -- 运行超过10秒的连接⚠️ 再声明：解决方案仅供参考，不同场景适用性可能差异很大。生产环境修改索引前请务必在测试环境验证，并选择低峰期操作，避免锁表风险。
有问题欢迎留言交流 🚀