定位优化慢 SQL
慢 SQL 的危害
一条慢查询的执行时间可能是几百毫秒到几十秒。对于高并发系统，慢 SQL 带来的不仅仅是响应时间变长，更危险的连锁反应包括：
– 占满连接池：后续请求排队等待
– 锁等待：长事务阻塞其他写入
– CPU 飙升：全表扫描和文件排序消耗大量 CPU
– 雪崩：数据库响应变慢 → 应用线程堆积 → 应用 OOM
第一步：开启慢查询日志
配置慢查询
-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
-- 设置慢查询阈值（单位：秒）
SET GLOBAL long_query_time = 1;
-- 输出到文件
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';
-- 记录未使用索引的查询
SET GLOBAL log_queries_not_using_indexes = ON;
生产环境建议
long_query_time设为 0.5~1 秒（具体取决于业务容忍度）- 利用
pt-query-digest定期分析慢查询日志 - 慢查询日志文件要做日志轮转，避免撑爆磁盘
第二步：用 EXPLAIN 分析执行计划
拿到慢 SQL 后，用 EXPLAIN 看看 MySQL 是怎么执行它的：
EXPLAIN SELECT * FROM orders
WHERE user_id = 12345 AND create_time > '2024-01-01'
ORDER BY amount DESC LIMIT 10;
重点关注 EXPLAIN 的输出
| 字段 | 重点关注 |
|---|---|
| type | 从好到差依次为 const → ref → range → index → ALL（ALL = 全表扫描） |
| key | 实际使用的索引 NULL 表示无索引 |
| rows | 扫描行数估算值，越大越慢 |
| Extra | Using filesort（文件排序）、Using temporary（临时表）是需要优化的信号 |
| possible_keys | 可能用到的索引与实际使用的索引对比 |
常见危险信号
type: ALL → 没有用索引
Extra: Using filesort → 排序没有用到索引
Extra: Using temporary → 查询产生了临时表
rows: > 百万 → 扫描行数过多
key: NULL → 没有命中索引
第三步：常见优化手段
1. 索引优化
-- 添加合适的索引
ALTER TABLE orders ADD INDEX idx_user_time (user_id, create_time);
- 分析 WHERE 条件、ORDER BY、JOIN 的字段
- 创建复合索引覆盖多个字段
- 注意索引列的顺序（最左前缀原则）
2. SQL 改写
- 避免
SELECT *，只取需要的列 LIKE '%xxx'会导致索引失效- 拆分复杂的 JOIN 为多次简单查询
OR条件可以改写为UNION ALL
3. 查询拆分
-- 原 SQL：大范围查询 + 排序
SELECT * FROM orders WHERE create_time > '2024-01-01' ORDER BY id LIMIT 10;
-- 改造：先取 ID，再回表
SELECT id FROM orders WHERE create_time > '2024-01-01' ORDER BY id LIMIT 10;
SELECT * FROM orders WHERE id IN (刚才取到的 ID 列表);
4. 使用索引覆盖
-- 查询所有字段需要回表
SELECT * FROM orders WHERE user_id = 12345;
-- 只查需要字段，覆盖索引直接返回
SELECT id, order_no, amount FROM orders WHERE user_id = 12345;
第四步：监控与持续优化
推荐工具
| 工具 | 用途 |
|---|---|
| pt-query-digest | 分析慢查询日志，找出最频繁、最慢的 SQL |
| Performance Schema | MySQL 内置性能监控 |
| sys schema | 方便查询的 Performance Schema 视图 |
| MySQLTuner | 提供 MySQL 配置优化建议 |
典型日常巡检
-- 查看当前正在运行的查询
SHOW FULL PROCESSLIST;
-- 查看当前活跃的事务
SELECT * FROM information_schema.INNODB_TRX;
-- 查看最近 10 条慢查询
SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;
总结
定位优化慢 SQL 四步走：
1. 开慢查询日志：找到问题 SQL
2. EXPLAIN 分析：看懂执行计划中的危险信号
3. 针对性优化：索引 + SQL 改写 + 查询拆分
4. 持续监控：不只要消灭慢查询，还要防止新的慢查询出现
关键原则：优化索引优先于改 SQL，改 SQL 优先于加缓存。索引是最便宜的性能提升手段。
© 版权声明
文章版权归作者所有，未经允许请勿转载。
THE END
暂无评论内容