Part01-基础概念与理论知识
1.1 MySQL慢查询日志概念
慢查询日志是MySQL数据库的重要性能监控工具，用于记录执行时间超过指定阈值的SQL语句。通过分析慢查询日志，我们可以识别性能瓶颈，优化SQL语句，提高数据库性能。风哥教程参考MySQL官方文档MySQL Server Administration。
1.2 MySQL慢查询日志参数
MySQL慢查询日志相关的主要参数包括：
SHOW VARIABLES LIKE ‘%slow%’;
| Variable_name | Value |
+—————————+——————————-+
| log_slow_admin_statements | OFF |
| log_slow_extra | OFF |
| log_slow_slave_statements | OFF |
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file | /mysql/data/fgedu-slow.log |
| long_query_time | 10.000000 |
+—————————+——————————-+
1.3 MySQL慢查询日志格式
慢查询日志的格式包含以下信息：
- 时间戳
- 用户和主机信息
- 查询执行时间
- 锁定时间
- 发送行数
- 扫描行数
- 使用的索引
- SQL语句
Part02-生产环境规划与建议
2.1 MySQL慢查询日志配置建议
- 在生产环境中，建议开启慢查询日志，但需要合理配置阈值和存储位置
- 慢查询日志文件应存储在与数据文件不同的磁盘上，以避免I/O竞争
- 定期分析慢查询日志，及时发现和优化性能问题
2.2 MySQL慢查询阈值设置
慢查询阈值的设置应根据业务特点和服务器性能来确定。对于OLTP系统，通常设置为1-2秒；对于OLAP系统，可以适当放宽到5-10秒。学习交流加群风哥微信: itpux-com
2.3 MySQL慢查询日志存储管理
慢查询日志文件可能会变得非常大，需要定期清理和归档。建议：
- 设置合理的日志轮转策略
- 使用脚本定期清理旧的日志文件
- 考虑使用第三方工具进行日志分析和存储
Part03-生产环境项目实施方案
3.1 MySQL慢查询日志开启与配置
SET GLOBAL slow_query_log = ‘ON’;
# 设置慢查询阈值（单位：秒）
SET GLOBAL long_query_time = 1;
# 设置慢查询日志文件位置
SET GLOBAL slow_query_log_file = ‘/mysql/data/fgedu-slow.log’;
# 记录未使用索引的查询
SET GLOBAL log_queries_not_using_indexes = ‘ON’;
# 记录管理语句
SET GLOBAL log_slow_admin_statements = ‘ON’;
vi /etc/my.cnf
# 添加以下配置
[mysqld]
slow_query_log = 1
slow_query_log_file = /mysql/data/fgedu-slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1
3.2 MySQL慢查询日志分析工具
常用的慢查询日志分析工具包括：
- mysqldumpslow – MySQL自带的慢查询分析工具
- pt-query-digest – Percona Toolkit中的工具，功能更强大
- MySQL Enterprise Monitor – 企业级监控工具
- 第三方工具如Anemometer、MySQLTuner等
3.3 MySQL慢查询日志监控方案
可以通过以下方式监控慢查询日志：
- 定期运行分析工具，生成报告
- 设置监控系统，当慢查询数量超过阈值时报警
- 集成到持续集成/持续部署流程中
Part04-生产案例与实战讲解
4.1 MySQL慢查询日志分析实战
# mysqldumpslow -s t /mysql/data/fgedu-slow.log
Reading mysql slow query log from /mysql/data/fgedu-slow.log
Count: 10 Time=2.52s (25.2s) Lock=0.00s (0.00s) Rows=1000.0 (10000), fgedu[user]@[192.168.1.100]
SELECT * FROM fgedu_users WHERE age > N
Count: 5 Time=1.87s (9.35s) Lock=0.00s (0.00s) Rows=500.0 (2500), fgedu[user]@[192.168.1.100]
SELECT * FROM fgedu_orders WHERE create_time BETWEEN ‘2023-01-01’ AND ‘2023-01-31’
# pt-query-digest /mysql/data/fgedu-slow.log
# 输出示例
# Profile
# Rank Query ID Response time Calls R/Call V/M Item
# ==== ================== ============= ===== ====== ===== ===================
# 1 0xF123456789ABCDEF 25.200s 10 2.520s 0.00 SELECT * FROM fgedu_users WHERE age > ?
# 2 0xABCDEF1234567890 9.350s 5 1.870s 0.00 SELECT * FROM fgedu_orders WHERE create_time BETWEEN ? AND ?
4.2 MySQL慢查询优化案例
案例1：优化未使用索引的查询
SELECT * FROM fgedu_users WHERE age > 30;
# 优化方案：为age列添加索引
ALTER TABLE fgedu_users ADD INDEX idx_age (age);
# 优化后查询（使用索引）
SELECT * FROM fgedu_users WHERE age > 30;
EXPLAIN SELECT * FROM fgedu_users WHERE age > 30;
# 优化前
+—-+————-+———–+————+——+—————+——+———+——+——–+———-+————-+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+———–+————+——+—————+——+———+——+——–+———-+————-+
| 1 | SIMPLE | fgedu_users | NULL | ALL | NULL | NULL | NULL | NULL | 100000 | 50.00 | Using where |
+—-+————-+———–+————+——+—————+——+———+——+——–+———-+————-+
# 优化后
+—-+————-+———–+————+——-+—————+———+———+——+——-+———-+———————–+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+———–+————+——-+—————+———+———+——+——-+———-+———————–+
| 1 | SIMPLE | fgedu_users | NULL | range | idx_age | idx_age | 4 | NULL | 50000 | 100.00 | Using index condition |
+—-+————-+———–+————+——-+—————+———+———+——+——-+———-+———————–+
案例2：优化范围查询
SELECT * FROM fgedu_orders WHERE create_time BETWEEN ‘2023-01-01’ AND ‘2023-01-31’;
# 优化方案：为create_time列添加索引
ALTER TABLE fgedu_orders ADD INDEX idx_create_time (create_time);
# 优化后查询
SELECT * FROM fgedu_orders WHERE create_time BETWEEN ‘2023-01-01’ AND ‘2023-01-31’;
4.3 MySQL慢查询日志清理与归档
# slow_query_log_rotate.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`
# 慢查询日志文件路径
SLOW_LOG_FILE=”/mysql/data/fgedu-slow.log”
# 归档目录
ARCHIVE_DIR=”/mysql/archive/slow_log”
# 当前日期
DATE=$(date +”%Y%m%d”)
# 创建归档目录
mkdir -p $ARCHIVE_DIR
# 停止慢查询日志
mysql -u root -p”password” -e “SET GLOBAL slow_query_log = ‘OFF’;”
# 归档日志文件
mv $SLOW_LOG_FILE “$ARCHIVE_DIR/fgedu-slow-$DATE.log”
# 重新开启慢查询日志
mysql -u root -p”password” -e “SET GLOBAL slow_query_log = ‘ON’;”
# 压缩归档文件
gzip “$ARCHIVE_DIR/fgedu-slow-$DATE.log”
# 删除30天前的归档文件
find $ARCHIVE_DIR -name “fgedu-slow-*.log.gz” -mtime +30 -delete
Part05-风哥经验总结与分享
5.1 MySQL慢查询优化最佳实践
- 定期分析慢查询日志，至少每周一次
- 关注执行时间长的查询，特别是那些扫描行数多的查询
- 优先优化高频执行的慢查询
- 合理设计索引，避免过度索引
- 使用EXPLAIN分析查询执行计划
- 考虑使用分区表来优化大表查询
- 定期更新表统计信息
5.2 MySQL慢查询监控经验
建立完善的慢查询监控体系：
- 设置合理的慢查询阈值，根据业务特点调整
- 使用监控工具实时监控慢查询数量
- 设置报警机制，当慢查询数量异常时及时通知
- 定期生成慢查询分析报告，跟踪优化效果
学习交流加群风哥QQ113257174
5.3 MySQL慢查询分析工具推荐
- pt-query-digest – Percona Toolkit中的工具，功能强大，支持详细分析
- mysqldumpslow – MySQL自带工具，简单易用
- Anemometer – 基于pt-query-digest的Web界面，可视化分析
- MySQL Enterprise Monitor – 企业级监控工具，提供全面的性能监控
更多视频教程www.fgedu.net.cn
更多学习教程公众号风哥教程itpux_com
from MySQL:www.itpux.com
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
