Mysql--基础知识点--91.1--慢查询日志

MySQL 慢查询日志是优化数据库性能的核心工具之一，用于记录执行时间超过指定阈值的 SQL 语句。以下是详细使用指南：

---

一、配置慢查询日志

1. 临时启用（重启后失效）

-- 启用慢查询日志（立即生效）
SET GLOBAL slow_query_log = 'ON';

-- 设置阈值（单位：秒，例如 2 秒）
SET GLOBAL long_query_time = 2;

-- 设置日志文件路径（需有写入权限）
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log';

2. 永久配置（修改 my.cnf/my.ini）

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 2
log_queries_not_using_indexes = ON  # 记录未使用索引的查询（即使未超时）

---

二、查看与分析日志

1. 直接查看日志文件

tail -f /var/log/mysql/slow-query.log

2. 使用 mysqldumpslow 工具（MySQL 自带）

# 按总耗时排序（降序）
mysqldumpslow -s t /var/log/mysql/slow-query.log

# 按执行次数排序
mysqldumpslow -s c /var/log/mysql/slow-query.log

# 过滤特定用户（如 user1）
mysqldumpslow -u user1 /var/log/mysql/slow-query.log

3. 日志字段解析

# Time: 2025-05-09T10:00:00.123456Z
# User@Host: user1[user1] @  [192.168.1.10]  Id:     5
# Query_time: 3.256789  Lock_time: 0.000123  Rows_sent: 10  Rows_examined: 1000
USE test;
SELECT * FROM large_table WHERE column = 'value';

• Query_time: 查询总耗时（秒）
• Lock_time: 获取锁的时间（秒）
• Rows_sent/examined: 返回行数与扫描行数

---

三、高级优化工具

1. Percona Toolkit 的 pt-query-digest

pt-query-digest /var/log/mysql/slow-query.log > analysis_report.txt

输出报告包含：

• 查询出现次数（count）
• 平均/最大执行时间（Avg_time, Max_time）
• 占总时间比例（Percent_of_total_time）

2. Performance Schema 分析

-- 查看历史慢查询统计
SELECT * FROM performance_schema.events_statements_summary_by_digest
WHERE AVG_TIMER_WAIT > 2 * 1000000000;  -- 2 秒（单位：皮秒）

---

四、最佳实践

1. 阈值设置：
   根据业务负载调整 long_query_time（生产环境建议 1-5 秒，测试环境可设更短）。

2. 索引优化：
   结合 EXPLAIN 分析未使用索引的查询，添加合适索引。

3. 日志轮转：
   使用 logrotate 定期压缩归档日志，避免磁盘占用过高。

4. 实时监控：
   通过 SHOW FULL PROCESSLIST | SHOW PROFILE配合 State 字段（如 Sending data, Locked）定位阻塞点。

---

五、注意事项

• 权限要求：修改配置文件需 root 权限，动态设置需 SUPER 权限。
• 性能影响：开启日志会带来约 5%-10% 的性能损耗，建议仅在优化阶段启用。
• 日志安全：确保日志文件权限严格（如 640），避免敏感信息泄露。

通过合理配置和分析慢查询日志，可精准定位性能瓶颈，优化 SQL 执行效率。