mysql-slow-query-fix-guide

# MySQL 慢查询治理操作手册 ## 一、准备阶段 ### 1. 环境评估 - 确认 MySQL 版本（建议 5.7+ 或 8.0+） - 检查当前慢查询日志状态：`SHOW VARIABLES LIKE 'slow_query_log%';` - 评估服务器资源（CPU、内存、磁盘 I/O） - 确定业务高峰期时段 ### 2. 开启慢查询日志 ```sql -- 临时开启（重启失效） SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 1; -- 设置阈值，单位秒 SET GLOBAL log_output = 'FILE'; -- 输出到文件 -- 永久生效（修改 my.cnf 或 my.ini） [mysqld] slow_query_log = 1 slow_query_log_file = /var/log/mysql/slow.log long_query_time = 1 log_output = FILE ``` ### 3. 工具准备 - 安装分析工具：`pt-query-digest`（Percona Toolkit）或 `mysqldumpslow` - 准备监控工具：如 Prometheus + Grafana、Zabbix 等 - 创建专用分析数据库（可选）：用于存储慢查询统计信息 ### 4. 制定治理目标 - 明确优化目标（如：将平均查询时间降低 50%） - 确定优先级（高频慢查询 > 单次耗时长的查询） - 制定回滚方案 --- ## 二、执行阶段 ### 1. 收集慢查询日志 ```bash # 查看慢查询日志位置 SHOW VARIABLES LIKE 'slow_query_log_file'; # 定期备份日志（避免日志过大） cp /var/log/mysql/slow.log /var/log/mysql/slow_$(date +%Y%m%d).log > /var/log/mysql/slow.log # 清空原日志 ``` ### 2. 分析慢查询 ```bash # 使用 pt-query-digest 分析 pt-query-digest /var/log/mysql/slow.log > slow_analysis_report.txt # 或使用 mysqldumpslow（MySQL 自带） mysqldumpslow -s t -t 10 /var/log/mysql/slow.log # 按时间排序，显示前10条 ``` **分析报告重点关注：** - 查询执行次数（Count） - 总耗时（Query_time_sum） - 平均耗时（Query_time_avg） - 锁等待时间（Lock_time） - 扫描行数（Rows_examined） ### 3. 优化慢查询 **常见优化手段：** #### (1) 添加索引 ```sql -- 分析执行计划 EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 'pending'; -- 添加复合索引 ALTER TABLE orders ADD INDEX idx_user_status (user_id, status); ``` #### (2) 重写 SQL - 避免 SELECT *，只查询必要字段 - 减少子查询，改用 JOIN - 避免在 WHERE 子句中对字段进行函数操作 - 合理使用分页（避免 OFFSET 过大） #### (3) 表结构优化 - 拆分大表（垂直/水平分表） - 字段类型优化（如用 INT 替代 VARCHAR 存储状态） - 添加冗余字段减少 JOIN #### (4) 配置调优 ```ini # my.cnf 调整 innodb_buffer_pool_size = 物理内存的 70-80% query_cache_size = 0 # MySQL 8.0 已移除，5.7 建议关闭 tmp_table_size = 64M max_heap_table_size = 64M ``` ### 4. 验证优化效果 ```sql -- 优化前后对比执行时间 SELECT SQL_NO_CACHE * FROM orders WHERE user_id = 100 AND status = 'pending'; -- 检查索引使用情况 EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE user_id = 100; ``` ---...

存档时间线

版本索引永久保留；内容副本只保留最新 1 份，更早版本仅留索引与哈希（存档时间线的证据链不会因此断裂）。

下载存档内容副本