MySQL教程FG175-MySQL慢查询日志管理

内容简介：MySQL慢查询日志是性能优化的重要工具，记录了执行时间超过阈值的SQL语句。本文风哥教程参考MySQL官方文档MySQL Server Administration部分，详细介绍MySQL慢查询日志的配置、管理、分析和优化方法，帮助DBA高效管理MySQL慢查询日志系统。学习交流加群风哥QQ113257174

Part01-基础概念与理论知识

1.1 MySQL慢查询日志概述

MySQL慢查询日志用于记录执行时间超过阈值的SQL语句，主要用于：

识别性能瓶颈
优化SQL语句
监控数据库性能
评估索引使用效率

慢查询日志记录的信息包括：

SQL语句执行时间
锁等待时间
扫描行数
返回行数
用户和主机信息
执行时间戳

1.2 慢查询日志参数说明

MySQL提供多个参数用于配置慢查询日志：

slow_query_log：是否开启慢查询日志
slow_query_log_file：慢查询日志文件路径
long_query_time：慢查询阈值（秒）
log_queries_not_using_indexes：是否记录未使用索引的查询
log_slow_admin_statements：是否记录管理语句
log_slow_slave_statements：从服务器是否记录慢查询
min_examined_row_limit：记录慢查询的最小扫描行数

Part02-生产环境规划与建议

2.1 慢查询日志存储规划

在生产环境中，慢查询日志的存储需要合理规划：

独立存储：将慢查询日志存储在与数据文件不同的磁盘上
足够空间：根据业务量和保留策略预留足够空间
定期清理：配置日志轮转，避免日志文件过大
备份策略：重要的慢查询日志需要备份保存

2.2 慢查询阈值设置

慢查询阈值的设置需要根据业务需求和数据库性能来确定：

OLTP系统：建议设置为0.1-1秒
OLAP系统：可以设置为更长时间（如5秒或10秒）
混合系统：根据业务类型设置不同的阈值

风哥提示：慢查询阈值不宜设置过小，否则会产生大量日志影响性能；也不宜设置过大，否则无法及时发现性能问题。

Part03-生产环境项目实施方案

3.1 慢查询日志配置

MySQL慢查询日志配置主要通过my.cnf配置文件或动态修改系统变量实现。

# 配置慢查询日志
# vi /etc/my.cnf
[mysqld]
# 开启慢查询日志
slow_query_log = ON
# 慢查询日志文件路径
slow_query_log_file = /mysql/logs/slow.log
# 慢查询阈值（秒）
long_query_time = 0.5
# 记录未使用索引的查询
log_queries_not_using_indexes = ON
# 记录管理语句
log_slow_admin_statements = ON
# 记录从服务器的慢查询
log_slow_slave_statements = ON
# 记录慢查询的最小扫描行数
min_examined_row_limit = 1000
# 日志输出格式（FILE或TABLE）
log_output = FILE

# 动态修改慢查询日志配置
mysql> SET GLOBAL slow_query_log = ON;
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL long_query_time = 0.5;
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL log_queries_not_using_indexes = ON;
Query OK, 0 rows affected (0.00 sec)

3.2 慢查询日志查看与分析

慢查询日志的查看和分析是性能优化的重要步骤。

# 查看慢查询日志内容
# tail -n 50 /mysql/logs/slow.log
# Time: 2026-04-02T12:00:00.123456+08:00
# User@Host: fgedu_user[192.168.1.100] @ [192.168.1.100]
# Thread_id: 1234 Schema: fgedudb QC_hit: No
# Query_time: 2.500000 Lock_time: 0.000123 Rows_sent: 100 Rows_examined: 100000
# Rows_affected: 0 Bytes_sent: 20000
use fgedudb;
SET timestamp=1712028000;
SELECT * FROM fgedu_orders WHERE customer_id = 100 AND order_date BETWEEN ‘2026-01-01’ AND ‘2026-03-31’;

# Time: 2026-04-02T12:01:00.123456+08:00
# User@Host: fgedu_user[192.168.1.100] @ [192.168.1.100]
# Thread_id: 1235 Schema: fgedudb QC_hit: No
# Query_time: 1.800000 Lock_time: 0.000098 Rows_sent: 1 Rows_examined: 50000
# Rows_affected: 0 Bytes_sent: 500
SET timestamp=1712028060;
SELECT * FROM fgedu_users WHERE username = ‘zhangsan’;

# Time: 2026-04-02T12:02:00.123456+08:00
# User@Host: fgedu_user[192.168.1.100] @ [192.168.1.100]
# Thread_id: 1236 Schema: fgedudb QC_hit: No
# Query_time: 0.600000 Lock_time: 0.000056 Rows_sent: 0 Rows_examined: 20000
# Rows_affected: 1 Bytes_sent: 45
SET timestamp=1712028120;
UPDATE fgedu_products SET stock = stock – 1 WHERE id = 500;

# 从mysql.slow_log表中查询
mysql> USE mysql;
Database changed

mysql> SELECT * FROM slow_log ORDER BY query_time DESC LIMIT 5;
+—————————-+—————————+———–+———–+———–+——————+————+—————-+———–+———–+—————–+—————–+—————–+———–+——————+——————+——————+—————–+——————-+
| start_time | user_host | thread_id | server_id | schema_name | last_insert_id | insert_id | query_time | lock_time | rows_sent | rows_examined | rows_affected | tmp_tables | tmp_disk_tables | full_scan | full_join | tmp_table_sizes | comment | sql_text |
+—————————-+—————————+———–+———–+———–+——————+————+—————-+———–+———–+—————–+—————–+—————–+——————+——————+——————+——————+——————-+
| 2026-04-02 12:00:00.123456 | fgedu_user[192.168.1.100] | 1234 | 1 | fgedudb | 0 | 0 | 2.500000 | 0.000123 | 100 | 100000 | 0 | 0 | 0 | 1 | 0 | 0 | | SELECT * FROM fgedu_orders WHERE customer_id = 100 AND order_date BETWEEN ‘2026-01-01’ AND ‘2026-03-31’ |
| 2026-04-02 12:01:00.123456 | fgedu_user[192.168.1.100] | 1235 | 1 | fgedudb | 0 | 0 | 1.800000 | 0.000098 | 1 | 50000 | 0 | 0 | 0 | 1 | 0 | 0 | | SELECT * FROM fgedu_users WHERE username = ‘zhangsan’ |
| 2026-04-02 12:02:00.123456 | fgedu_user[192.168.1.100] | 1236 | 1 | fgedudb | 0 | 0 | 0.600000 | 0.000056 | 0 | 20000 | 1 | 0 | 0 | 1 | 0 | 0 | | UPDATE fgedu_products SET stock = stock – 1 WHERE id = 500 |
+—————————-+—————————+———–+———–+———–+——————+————+—————-+———–+———–+—————–+—————–+—————–+——————+——————+——————+——————+——————-+
3 rows in set (0.01 sec)

3.3 慢查询分析工具

使用专业工具分析慢查询日志，提高效率。

# 使用pt-query-digest分析慢查询日志
# pt-query-digest /mysql/logs/slow.log > slow_query_analysis.txt

# 查看分析结果摘要
# head -n 100 slow_query_analysis.txt
# 301ms user time, 10ms system time, 10.00M rss, 40.00M vsz
# Current date: Tue Apr 2 12:10:00 2026
# Hostname: fgedu.net.cn
# Files: /mysql/logs/slow.log
# Overall: 1,234 total, 56 unique, 0.1 QPS, 0.2x concurrency ________________
# Time range: 2026-04-01T00:00:00 to 2026-04-02T12:10:00
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 3456s 0.5s 123s 3s 10s 8s 2s
# Lock time 123s 0 45s 100ms 500ms 500ms 10ms
# Rows sent 456789 0 12345 371 1234 2345 0
# Rows examine 12345678 0 123456 9999 56789 234567 100
# Query size 123456 10 1234 100 500 200 50

# Profile
summed exec time count pct stddev median avg 95% max query
================= ====== ==== ======= ======= =========== =========== =========== =====
3210.0s (93%) 1024 83% 8s 2s 3.1s 10s 123s SELECT * FROM fgedu_orders WHERE customer_id = ? AND order_date BETWEEN ? AND ?
123.0s (4%) 128 10% 2s 1s 0.96s 2s 10s SELECT * FROM fgedu_users WHERE username = ?
45.0s (1%) 64 5% 0.5s 0.3s 0.7s 1s 2s UPDATE fgedu_products SET stock = stock – ? WHERE id = ?
12.0s (0.3%) 18 1% 0.2s 0.1s 0.7s 1s 2s SELECT * FROM fgedu_products WHERE category_id = ? AND status = ?

Part04-生产案例与实战讲解

4.1 慢查询日志诊断案例

使用慢查询日志诊断数据库性能问题。

案例：应用响应缓慢
问题描述：应用程序报告响应缓慢，需要诊断原因
解决方法：分析慢查询日志，找出性能瓶颈

# 分析慢查询日志
# pt-query-digest /mysql/logs/slow.log –limit 10 > top_10_slow_queries.txt

# 查看前10个慢查询
# cat top_10_slow_queries.txt

# 检查表结构和索引
mysql> SHOW CREATE TABLE fgedu_orders;
+————–+————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————+
| Table | Create Table |
+————–+————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————+
| fgedu_orders | CREATE TABLE `fgedu_orders` (
`id` int NOT NULL AUTO_INCREMENT,
`customer_id` int NOT NULL,
`order_date` date NOT NULL,
`total_amount` decimal(10,2) NOT NULL,
`status` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+————–+————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————+
1 row in set (0.01 sec)

分析结果：fgedu_orders表缺少customer_id和order_date的组合索引，导致查询需要全表扫描，影响性能。更多视频教程www.fgedu.net.cn

4.2 基于慢查询的优化实战

根据慢查询日志分析结果进行优化。

# 添加缺失的索引
mysql> CREATE INDEX idx_customer_date ON fgedu_orders(customer_id, order_date);
Query OK, 0 rows affected (0.56 sec)
Records: 0 Duplicates: 0 Warnings: 0

# 优化后验证查询性能
mysql> SELECT * FROM fgedu_orders WHERE customer_id = 100 AND order_date BETWEEN ‘2026-01-01’ AND ‘2026-03-31’;
+—–+————-+————+————–+———–+
| id | customer_id | order_date | total_amount | status |
+—–+————-+————+————–+———–+
| 100 | 100 | 2026-01-15 | 199.99 | completed |
| 200 | 100 | 2026-02-20 | 299.99 | completed |
| 300 | 100 | 2026-03-10 | 399.99 | completed |
+—–+————-+————+————–+———–+
3 rows in set (0.01 sec)

4.3 慢查询监控与告警

建立慢查询监控和告警机制，及时发现性能问题。

#!/bin/bash
# slow_query_monitor.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn

SLOW_LOG=”/mysql/logs/slow.log”
ALERT_FILE=”/tmp/mysql_slow_query_alert.txt”
ALERT_EMAIL=”dba@fgedu.net.cn”

# 定义慢查询阈值（分钟）
TIME_THRESHOLD=60
# 定义慢查询数量阈值
COUNT_THRESHOLD=10

# 检查慢查询日志
check_slow_queries() {
echo “MySQL慢查询日志监控报告 – $(date)” > $ALERT_FILE
echo “==================================” >> $ALERT_FILE

# 获取指定时间范围内的慢查询
time_range=$(date -d “$TIME_THRESHOLD minutes ago” +%Y-%m-%d” “%H:%M:%S)

# 使用pt-query-digest分析指定时间范围内的慢查询
pt-query-digest –since “$time_range” $SLOW_LOG –limit 10 >> $ALERT_FILE

# 统计慢查询数量
slow_count=$(grep -c “Query_time:” $SLOW_LOG | grep -E “$(date +%Y-%m-%d)”)

echo “==================================” >> $ALERT_FILE
echo “总慢查询数量: $slow_count” >> $ALERT_FILE
}

# 发送告警邮件
send_alert_email() {
if [ $(wc -l < $ALERT_FILE) -gt 10 ]; then # 这里可以添加邮件发送逻辑 # mail -s "MySQL慢查询监控告警" $ALERT_EMAIL < $ALERT_FILE echo "检测到MySQL慢查询异常，告警邮件已发送至 $ALERT_EMAIL" echo "告警内容：" cat $ALERT_FILE else echo "MySQL慢查询监控正常" rm $ALERT_FILE fi } # 主函数 main() { check_slow_queries send_alert_email } main

# 运行慢查询监控脚本
# chmod +x /mysql/scripts/slow_query_monitor.sh
# /mysql/scripts/slow_query_monitor.sh
MySQL慢查询日志监控报告 – Tue Apr 2 12:30:00 2026
==================================
# 101ms user time, 5ms system time, 8.00M rss, 32.00M vsz
# Current date: Tue Apr 2 12:30:00 2026
# Hostname: fgedu.net.cn
# Files: /mysql/logs/slow.log
# Overall: 123 total, 12 unique, 0.02 QPS, 0.04x concurrency ________________
# Time range: 2026-04-02T11:30:00 to 2026-04-02T12:30:00
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 345s 0.5s 23s 3s 8s 5s 2s
# Lock time 12s 0 5s 100ms 400ms 400ms 10ms
# Rows sent 45678 0 1234 371 1000 2000 0
# Rows examine 1234567 0 12345 9999 45678 200000 100
# Query size 12345 10 1000 100 400 150 50

# Profile
summed exec time count pct stddev median avg 95% max query
================= ====== ==== ======= ======= =========== =========== =========== =====
310.0s (90%) 100 81% 6s 2s 3.1s 8s 23s SELECT * FROM fgedu_orders WHERE customer_id = ? AND order_date BETWEEN ? AND ?
23.0s (7%) 12 10% 2s 1s 1.9s 3s 5s SELECT * FROM fgedu_users WHERE username = ?
12.0s (3%) 11 9% 0.5s 0.3s 1.1s 2s 3s UPDATE fgedu_products SET stock = stock – ? WHERE id = ?
==================================
总慢查询数量: 123
检测到MySQL慢查询异常，告警邮件已发送至 dba@fgedu.net.cn
告警内容：
MySQL慢查询日志监控报告 – Tue Apr 2 12:30:00 2026
==================================
# 101ms user time, 5ms system time, 8.00M rss, 32.00M vsz
# Current date: Tue Apr 2 12:30:00 2026
# Hostname: fgedu.net.cn
# Files: /mysql/logs/slow.log
# Overall: 123 total, 12 unique, 0.02 QPS, 0.04x concurrency ________________
# Time range: 2026-04-02T11:30:00 to 2026-04-02T12:30:00
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 345s 0.5s 23s 3s 8s 5s 2s
# Lock time 12s 0 5s 100ms 400ms 400ms 10ms
# Rows sent 45678 0 1234 371 1000 2000 0
# Rows examine 1234567 0 12345 9999 45678 200000 100
# Query size 12345 10 1000 100 400 150 50

Part05-风哥经验总结与分享

5.1 慢查询日志管理最佳实践

长期开启慢查询日志：慢查询日志性能影响小，可以长期开启
合理设置阈值：根据业务需求和数据库性能设置合适的阈值
记录未使用索引的查询：有助于发现索引缺失问题
定期分析日志：每周或每月分析慢查询日志，找出性能瓶颈
使用专业工具：使用pt-query-digest等工具提高分析效率
建立监控告警：及时发现和处理慢查询异常
优化后验证：优化后需要验证效果，确保性能提升

5.2 慢查询日志使用技巧

风哥提示：慢查询日志是性能优化的重要工具，但需要合理使用，避免产生过多日志影响性能。

设置合适的阈值：根据业务场景调整long_query_time参数
过滤不重要的慢查询：使用pt-query-digest的–filter选项过滤
关注重点指标：重点关注Query_time、Lock_time、Rows_examined等指标
结合执行计划：使用EXPLAIN分析慢查询的执行计划
定期清理日志：配置日志轮转，避免日志文件过大
记录管理语句：开启log_slow_admin_statements记录管理语句

生产环境建议：建立慢查询日志管理的标准化流程，定期分析和优化慢查询，确保数据库的高性能运行。更多学习教程公众号风哥教程itpux_com

from MySQL:www.itpux.com

GF-MySQL数据库培训文档系列

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html