悠悠楠杉
网站页面
MySQL慢查询分析与优化实战指南
08/21
一、慢查询日志的配置与启用
慢查询日志是MySQL性能优化的核心工具。通过修改my.cnf（或my.ini）配置文件启用：
ini
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1 # 超过1秒的查询会被记录
log_queries_not_using_indexes = 1 # 记录未使用索引的查询
重启MySQL服务后，可通过以下命令验证状态：
sql
SHOW VARIABLES LIKE '%slow_query%';
二、慢查询日志分析方法
1. 使用mysqldumpslow工具
MySQL自带的日志分析工具能快速统计高频慢查询：bash
mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log
按总时间排序，显示前10条
2. 使用pt-query-digest
Percona Toolkit提供的更强大分析工具：bash
pt-query-digest --limit=20 /var/log/mysql/mysql-slow.log
生成详细分析报告
3. 关键指标解读
- Query_time distribution：查询时间分布直方图
- Rows_examined：扫描行数（理想值应接近返回行数）
- Lock_time：锁定时间过长可能预示并发问题
三、SQL优化实战技巧
案例1：未使用索引的查询优化
原始SQL：
sql
SELECT * FROM users WHERE status = 'inactive' ORDER BY create_time DESC;
优化步骤：
1. 使用EXPLAIN分析执行计划：
sql
EXPLAIN SELECT * FROM users WHERE status = 'inactive';
2. 添加复合索引：
sql
ALTER TABLE users ADD INDEX idx_status_time (status, create_time);
案例2：分页查询优化
低效写法：
sql
SELECT * FROM large_table LIMIT 1000000, 10;
优化方案：
sql
SELECT * FROM large_table
WHERE id > (SELECT id FROM large_table ORDER BY id LIMIT 1000000, 1)
LIMIT 10;
四、系统级优化建议
参数调优：
innodb_buffer_pool_size：设置为可用内存的70-80%join_buffer_size：适当增大连接缓冲区tmp_table_size：避免磁盘临时表
架构优化：
- 读写分离
- 热点数据缓存（Redis/Memcached）
- 分库分表（当单表超过500万行时考虑）
五、持续监控策略
- 部署Percona PMM或Prometheus+Granafa监控系统
- 设置定期慢查询日志分析任务（如每周自动生成报告）
- 重要业务SQL建立性能基线
通过系统化的慢查询分析流程，我们曾将某电商平台订单查询响应时间从2.3秒降至120毫秒。关键在于：精准定位问题 + 针对性优化 + 持续监控验证。建议每次优化后使用FLUSH STATUS重置计数器，通过对比优化前后的SHOW STATUS数据验证效果。
