慢sql治理规范

慢SQL治理规范

慢查询发现、分析与优化

---

慢查询发现

【Requirement】生产库必须开启慢查询日志，阈值设为 1 秒；定时采集并分析慢查询日志，建立慢 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;  -- 未走索引的查询也记录
 
-- 永久生效（my.cnf）
[mysqld]
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = ON

1.2 慢查询日志分析工具

# mysqldumpslow：MySQL 自带
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
# -s t：按执行时间排序，-t 10：取前 10 条
 
# pt-query-digest：Percona 工具，功能更强
pt-query-digest /var/log/mysql/slow.log > slow_report.txt
pt-query-digest --filter '$event->{fingerprint} =~ m/select/i' /var/log/mysql/slow.log

1.3 慢 SQL 台账

每条慢 SQL 必须登记台账，包含：

字段	说明
SQL 指纹	归一化后的 SQL 模板
数据库/表	涉及的库表
执行次数	统计周期内执行次数
平均耗时	平均执行时间
最大耗时	最大执行时间
扫描行数	Rows_examined
返回行数	Rows_sent
处理人	负责人
状态	待分析 / 优化中 / 已优化 / 无需处理
优化方案	具体优化措施

---

执行计划分析

【Standard】优化慢 SQL 前必须使用 EXPLAIN 分析执行计划，重点关注 type、key、rows、Extra 字段。

2.1 EXPLAIN 关键字段

字段	含义	关注重点
type	访问类型	效率：system > const > eq_ref > ref > range > index > ALL
key	实际使用的索引	是否为 NULL（未走索引）
key_len	索引使用长度	判断联合索引命中了几列
rows	预估扫描行数	越小越好
Extra	额外信息	是否有 Using filesort / Using temporary

2.2 type 级别说明

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge
> unique_subquery > index_subquery > range > index > ALL

• ALL：全表扫描，必须优化
• index：全索引扫描，需优化
• range：范围扫描，可接受
• ref / eq_ref：索引查找，良好
• const / system：常量级，最优

2.3 EXPLAIN 示例

EXPLAIN SELECT id, order_no FROM order_order WHERE user_id = 123 ORDER BY create_time DESC;
 
-- 输出示例
+----+-------------+-------------+------+------------------+------------------+---------+-------+------+-------------+
| id | select_type | table       | type | possible_keys    | key              | key_len | ref   | rows | Extra       |
+----+-------------+-------------+------+------------------+------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | order_order | ref  | idx_user_status  | idx_user_status  | 8       | const |  150 | Using where |
+----+-------------+-------------+------+------------------+------------------+---------+-------+------+-------------+

2.4 重点关注的 Extra

Extra	含义	处理方式
Using where	索引查找后再过滤	正常
Using index	覆盖索引，无需回表	最优
Using filesort	额外排序	加索引匹配 ORDER BY
Using temporary	使用临时表	优化 GROUP BY / DISTINCT
Using join buffer	关联未走索引	加关联字段索引

---

优化方法

【Standard】慢 SQL 优化按「索引优化 → SQL 改写 → 表结构 → 架构」顺序逐步推进，禁止直接上缓存掩盖问题。

3.1 索引优化（首选）

-- 慢 SQL：无索引全表扫描
SELECT id, order_no FROM order_order WHERE user_id = ? AND order_status = ?;
 
-- 优化：加联合索引
ALTER TABLE order_order ADD INDEX idx_user_status (user_id, order_status);

3.2 SQL 改写

-- 慢：OR 导致索引失效
SELECT id FROM order_order WHERE user_id = 1 OR merchant_id = 2;
 
-- 优化：UNION ALL
SELECT id FROM order_order WHERE user_id = 1
UNION ALL
SELECT id FROM order_order WHERE merchant_id = 2;

-- 慢：深分页
SELECT id FROM order_order ORDER BY id LIMIT 100000, 20;
 
-- 优化：游标分页
SELECT id FROM order_order WHERE id > ?lastId ORDER BY id LIMIT 20;

-- 慢：索引字段使用函数
SELECT id FROM sys_user WHERE DATE(create_time) = '2024-01-01';
 
-- 优化：范围查询
SELECT id FROM sys_user WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02';

3.3 表结构优化

• 大字段（TEXT/BLOB）拆分到扩展表
• 冗余常用字段，减少 JOIN
• 冷热数据分离，历史数据归档

-- 拆分大字段
CREATE TABLE order_order (
  id BIGINT PRIMARY KEY,
  order_no VARCHAR(32),
  ...
);
CREATE TABLE order_order_ext (
  order_id BIGINT PRIMARY KEY,
  detail TEXT COMMENT '订单详情JSON'
);

3.4 架构优化

• 读写分离：读请求走从库
• 分库分表：单表数据量超 1000 万
• 引入 ES：复杂查询、模糊搜索
• 加缓存：热点数据（Redis）

---

索引优化

【Requirement】索引优化必须基于 EXPLAIN 结果和业务查询场景，禁止凭感觉建索引。

4.1 联合索引顺序

区分度高的字段在前，等值查询字段在前，范围查询字段在后：

-- 查询：WHERE user_id = ? AND order_status = ? AND create_time >= ?
-- 正确：等值在前，范围在后
ALTER TABLE order_order ADD INDEX idx_user_status_time (user_id, order_status, create_time);
 
-- 错误：范围在前，后续字段无法命中
ALTER TABLE order_order ADD INDEX idx_time_user_status (create_time, user_id, order_status);

4.2 覆盖索引

查询字段都在索引中，避免回表：

-- 索引包含查询字段
ALTER TABLE order_order ADD INDEX idx_user_no (user_id, order_no);
 
-- 查询命中覆盖索引，Extra 显示 Using index
SELECT order_no FROM order_order WHERE user_id = ?;

4.3 索引使用与失效

索引生效条件：

• 联合索引遵循最左前缀
• 等值查询可跳过中间列（MySQL 5.6+ 索引下推）
• 范围查询后的列无法命中索引

索引失效场景：

• 索引列使用函数、运算、类型转换
• LIKE '%xxx' 左模糊
• OR 连接非索引字段
• !=、NOT IN、IS NOT NULL
• 隐式类型转换（字符串字段传数字）

4.4 索引合并

-- 两个单列索引，MySQL 可能使用 index_merge
ALTER TABLE order_order ADD INDEX idx_user (user_id);
ALTER TABLE order_order ADD INDEX idx_status (order_status);
 
SELECT id FROM order_order WHERE user_id = 1 AND order_status = 1;
-- 可能出现 Using intersect(idx_user, idx_status)
-- 更优：建联合索引 idx_user_status

---

监控

【Standard】建立慢 SQL 监控告警体系，每日生成慢 SQL 报告，P0 级慢 SQL 必须 24 小时内响应。

5.1 监控指标

指标	说明	告警阈值
慢查询数	每分钟慢 SQL 数量	> 10 条/分钟
慢查询耗时	单条 SQL 最大耗时	> 5 秒
连接数	活跃连接数	> 80% max_connections
锁等待	行锁等待时间	> 5 秒
TPS/QPS	事务/查询吞吐	环比下降 > 30%

5.2 监控方式

# 实时查看正在执行的慢 SQL
SHOW PROCESSLIST;
SELECT * FROM information_schema.processlist WHERE time > 1 AND command = 'Query';
 
# 查看锁等待
SELECT * FROM information_schema.innodb_locks;
SELECT * FROM information_schema.innodb_lock_waits;

5.3 应急处理

发现慢 SQL 导致数据库压力过大时：

1. SHOW PROCESSLIST 定位慢 SQL 线程 ID
2. KILL <thread_id> 终止慢 SQL
3. 临时加索引或限流
4. 分析根因，制定长期优化方案

-- 终止慢 SQL
KILL 12345;
 
-- 批量终止超时查询
SELECT CONCAT('KILL ', id, ';') 
FROM information_schema.processlist 
WHERE time > 30 AND command = 'Query';

5.4 持续治理流程

1. 每日采集慢查询日志 → 生成 Top 20 慢 SQL 报告
2. 按业务线分派给对应负责人
3. 负责人 3 个工作日内完成优化或说明原因
4. 优化后验证：EXPLAIN + 线上耗时对比
5. 关闭台账，记录优化效果
6. 每周复盘慢 SQL 治理情况