6.7.8 慢查询治理与SQL审计

本节聚焦慢查询治理与SQL审计，目标是在保障稳定性的前提下快速定位高成本语句、优化执行路径并形成持续改进机制，要求全流程可验证、可回滚、可追溯。

慢查询治理与审计闭环原理草图：

一、慢日志开启与配置（安装与配置示例）
1）确认 MySQL 已安装并可连接：

mysql -uroot -p -e "SELECT VERSION();"

2）在配置文件启用慢日志（路径示例：/etc/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
min_examined_row_limit = 100
log_output = FILE

3）重载配置并验证：

systemctl restart mysqld
mysql -uroot -p -e "SHOW VARIABLES LIKE 'slow_query_log%';"
mysql -uroot -p -e "SHOW VARIABLES LIKE 'long_query_time';"

预期效果：slow_query_log=ON，slow_query_log_file 指向日志路径。

二、慢日志采集与分析（命令示例）
1）使用 mysqldumpslow 快速统计：

mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
# -s t: 按时间排序
# -t 10: 取前10条

2）使用 pt-query-digest 做归一化分析（安装与使用）：

# 安装（CentOS示例）
yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm
yum install -y percona-toolkit

# 分析慢日志
pt-query-digest /var/log/mysql/slow.log > /var/log/mysql/slow_report.txt

预期效果：报告中包含 Query_time、Rows_examined、Rows_sent、95/99分位等统计。

三、SQL优化关键操作（可执行示例）
1）定位执行计划与索引选择：

EXPLAIN SELECT order_id, user_id, amount
FROM orders
WHERE user_id = 10001 AND status = 'PAID'
ORDER BY created_at DESC
LIMIT 10;

2）索引优化示例（覆盖索引）：

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

预期效果：执行计划使用 idx_user_status_created，减少回表与排序。

3）分页优化（避免大 OFFSET）：

-- 不推荐：大 OFFSET
SELECT order_id FROM orders ORDER BY created_at DESC LIMIT 100000, 10;

-- 推荐：基于游标
SELECT order_id FROM orders
WHERE created_at < '2024-01-01 00:00:00'
ORDER BY created_at DESC
LIMIT 10;

四、SQL审计策略与示例（结合代理/日志）
1）审计维度示例（高危DDL、全表扫描等）：
- DROP/ALTER/TRUNCATE
- 未命中索引的扫描
- 访问敏感字段（手机号/证件号）
- 超大结果集（Rows_sent > 1e5）

2）ProxySQL 基础审计示例（规则示意）：

-- 连接到 ProxySQL 管理端
mysql -uadmin -padmin -h127.0.0.1 -P6032

-- 添加审计规则：拦截 DROP
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, error_msg)
VALUES (1001, 1, '^\\s*DROP\\s+', 'DROP is forbidden');

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

预期效果：执行 DROP 时被阻断并返回错误提示。

五、慢查询治理流程（落地清单）
1）设定 SLA：例如 P95 < 200ms，P99 < 500ms
2）建立 TOP SQL 清单与责任人
3）优化前后做基线与回归压测
4）灰度发布与回滚预案
5）定期复盘与规范沉淀

六、排错与常见问题处理
1）慢日志过大影响 IO：

# 分割日志并保留最近7天
logrotate -f /etc/logrotate.d/mysql

2）开启慢日志但无记录：

SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_output';

检查是否写入 FILE 路径权限不足：

ls -l /var/log/mysql/slow.log
chown mysql:mysql /var/log/mysql

3）执行计划不稳定：

ANALYZE TABLE orders;

必要时启用持久化统计或使用 HINT 控制索引（谨慎使用）：

SELECT /*+ SET_VAR(use_index=idx_user_status_created) */
order_id FROM orders WHERE user_id=10001 AND status='PAID';

七、练习与自测
1）在测试库创建表并插入 10 万行，开启慢日志，构造一条全表扫描 SQL，使用 pt-query-digest 找出 TOP SQL。
2）为该 SQL 添加合适索引，比较优化前后 EXPLAIN 输出与执行时间。
3）编写一条审计规则，阻止 TRUNCATE TABLE，并验证报错信息。
4）模拟锁等待：启动事务更新同一行，观察慢日志是否记录锁等待时间。

通过慢查询治理与SQL审计的制度化建设，可以持续降低尾延迟与资源消耗，提升数据库稳定性与可运维性。