慢SQL排查与优化实战：从定位到根治
线上告警：接口响应超时。一查，又是慢 SQL。
这种场景太常见了。数据库是后端最常见的性能瓶颈，而慢 SQL 是罪魁祸首。这篇把我排查慢 SQL 的套路整理出来。
SQL 第一步：找到慢SQL
1. 开启慢查询日志
--
SHOW VARIABLES LIKE 'slow_query%'; SHOW VARIABLES LIKE 'long_query_time';
--
SET GLOBAL slow_query_log = 'ON'; SET G #后端LOBAL long_query_time = 1; --
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
永久生效写 my.cnf：
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1 # 没用索引的也记录
2. 分析慢查询日志
# 用mysqldumpslow分析
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
# -s t: 按查询时间排序
# -t 10: 显示前10条
# 输出示例
# Count: 156 Time=3.21s (500s) Lock=0.00s (0s) Rows=1000.0 (156000)
# SELECT * FROM orders WHERE user_id = N AND status = N
SQL 3. 实时查看正在执行的SQL
--
SHOW PROCESSLIST; SHOW FULL PROCESSLIST; -- 显示完整 SQL
--
SELECT *
WHERE command != 'Sleep' ORDER BY time DESC;
--
KILL 进程 ID;
C 4. performance_schema分析
--
--
SELECT DIGEST_TEXT, COUNT_STAR AS exec_count, SUM_TIMER_WAIT/1000000000000 AS total_time_sec, AVG_TIMER_WAIT/1000000000 AS avg_time_ms, SUM_ROWS_EXAMINED AS rows_examined FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;
第二步：分析执行计划
找到慢 SQL 后，用 EXPLAIN 分析。
EXPLAIN基本用法
EXPLAIN SELECT * FROM orders WHERE user_id = 123 AND status = 1;
输出关键字段：
| 字段 | 含义 | 关注点 | | —
| type | 访问类型 | ALL 全表扫描要优化 | | key | 实际用的索引 | NULL 说明没用索引 | | rows | 预估扫描行数 | 越小越好 | | Extra | 额外信息 | Using filesort、Using temporary 要注意 |
type类型（从好到差）
system > const > eq_ref > ref > range > index > ALL
- const: 主键或唯一索引等值查询，最多一条
- eq_ref: 多表join时，被驱动表用主键/唯一索引
- ref: 普通索引等值查询
- range: 索引范围扫描
- index: 全索引扫描（比ALL好，但也不理想）
- ALL: 全表扫描，必须优化
真实案例分析
--
EXPLAIN SELECT *
WHERE create_time > '2024-01-01' AND status = 1 ORDER BY id DESC LIMIT 100;
-- 输出 +----+-------------+--------+------+---------------+------+---------+------+--------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+--------+-----------------------------+ | 1 | SIMPLE | orders | ALL | idx_create | NULL | NULL | NULL | 500000 | Using where; Using filesort | +----+-------------+--------+------+---------------+------+---------+------+--------+-----------------------------+
问题：
- type=ALL ：全表扫描
- key=NULL ：没用索引
- Using filesort ：额外排序
第三步：索引优化
1. 联合索引的最左前缀原则
--
--
WHERE a = 1 WHERE a = 1 AND b = 2 WHERE a = 1 AND b = 2 AND c = 3
--
WHERE b = 2 --
WHERE a = 1 AND c = 3 --
WHERE b = 2 AND c = 3 -- 不能用
2. 覆盖索引
--
SELECT * FROM users WHERE age > 20;
--
SELECT id, name, age FROM users WHERE age > 20;
--
-- Extra 显示 Using index
3. 索引失效的常见缘由
--
SELECT *
SELECT * FROM orders WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01'; -- ✅
--
--
SELECT *
SELECT * FROM users WHERE phone = '13800138000'; -- ✅ 字符串
--
SELECT *
SELECT * FROM users WHERE name LIKE '张%'; -- ✅
--
SELECT * FROM users WHERE id = 1 OR name = '张三'; -- name 没索引则全表
-- 5. 不等于 != 或 <>
SELECT * FROM users WHERE status != 0; -- 可能全表扫描
--
-- 如果 NULL 值许多，优化器可能选择全表扫描
4. 索引设计原则
--
-- 常常出目前 WHERE、JOIN、ORDER BY 的列建索引
--
--
-- 性别区分度低（0.5），手机号区分度高（接近1）
--
CREATE INDEX idx_user ON orders(user_id, status); -- user_id 区分度更高
--
--
-- 可以建 INDEX idx_age_name (age, name)
SQL 第四步：SQL改写优化
C 1. 避免SELECT *
--
SELECT * FROM orders WHERE user_id = 123;
--
SELECT id, order_no, amount, status FROM orders WHERE user_id = 123;
2. 小表驱动大表
-- user表1000条，order表100万条
--
SELECT *
LEFT JOIN users u ON o.user_id = u.id;
--
SELECT *
LEFT JOIN orders o ON u.id = o.user_id;
--
SELECT *
WHERE EXISTS (SELECT 1 FROM users u WHERE u.id = o.user_id AND u.status = 1);
3. 分页优化
--
SELECT *
-- 要扫描100020行，丢弃前100000行
--
SELECT * FROM orders WHERE id > 上一页最后一条的 ID ORDER BY id LIMIT 20;
--
SELECT o.*
INNER JOIN (SELECT id FROM orders ORDER BY id LIMIT 100000, 20) t ON o.id = t.id; -- 子查询只查主键，速度快
4. 避免在循环中查询
// 差：N+1查询
users := getUsers()
for _, user := range users {
orders := getOrdersByUserID(user.ID) // 循环里查询
}
// 好：批量查询 users := getUsers() userIDs := extractUserIDs(users) orders := getOrdersByUserIDs(userIDs) // 一次查出来 orderMap := groupByUserID(orders)
5. UNION ALL 替代 UNION
--
SELECT id FROM orders WHERE status = 1 UNION SELECT id FROM orders WHERE status = 2;
--
SELECT id FROM orders WHERE status = 1 UNION ALL SELECT id FROM orders WHERE status = 2;
第五步：表结构优化
1. 选择合适的数据类型
--
TINYINT --
SMALLINT --
INT --
BIGINT -- 8字节
--
status TINYINT NOT NULL DEFAULT 0
--
-- 但 DATETIME 可读性好，看业务需求
--
amount DECIMAL(10, 2) NOT NULL DEFAULT 0.00
2. 大表拆分
--
--
id, user_id, order_no, amount, status, create_time
--
id, order_id, goods_info, remark
--
orders_2024, orders_2025 orders_0, orders_1, orders_2 ... orders_15 -- 按 user_id 取模
3. 归档历史数据
--
INSERT INTO orders_archive SELECT * FROM orders WHERE create_time < '2023-01-01';
DELETE FROM orders WHERE create_time < '2023-01-01' LIMIT 10000; -- 小批量删除，避免锁表太久
真实案例：一次慢查询排查
现象 ：订单列表接口偶尔超时
排查过程 ：
- 查慢查询日志，找到问题SQL：
SELECT *
WHERE user_id = 123 AND status IN (1, 2, 3) AND create_time > '2024-01-01' ORDER BY create_time DESC LIMIT 20;
- EXPLAIN分析：
type: ref
key: idx_user_id
rows: 50000
Extra: Using where; Using filesort
虽然用了索引，但 rows 很大，还有 filesort。
- 分析：
- 这个用户订单许多（大客户）
- idx_user_id 单列索引，筛选后还要回表过滤status和时间
- ORDER BY create_time和索引顺序不一致，要filesort
- 优化方案：
--
CREATE INDEX idx_user_status_time ON orders(user_id, status, create_time);
--
SELECT *
WHERE user_id = 123 AND status = 1 AND create_time > '2024-01-01' ORDER BY create_time DESC LIMIT 20
UNION ALL
SELECT *
WHERE user_id = 123 AND status = 2 AND create_time > '2024-01-01' ORDER BY create_time DESC LIMIT 20
...
或者接受现状，在应用层做缓存。
- 结果：查询时间从2s降到50ms。
工具推荐
- pt-query-digest ：分析慢查询日志，比mysqldumpslow强劲
- Percona Toolkit ：一套MySQL工具集
- MySQL Workbench ：可视化执行计划
- Explain Analyze （MySQL 8.0+）：真实执行统计
--
EXPLAIN ANALYZE SE
© 版权声明
文章版权归作者所有，未经允许请勿转载。
收藏了，感谢分享