MySQL 慢查询日志配置与分析

慢查询日志配置

核心参数配置

参数名	描述	默认值	建议值	说明
`slow_query_log`	启用慢查询日志	OFF	ON	生产环境建议开启
`slow_query_log_file`	慢查询日志文件路径	host_name-slow.log	/var/log/mysql/slow.log	确保目录存在且权限正确
`long_query_time`	慢查询阈值	10	1	单位秒，根据业务需求调整
`min_examined_row_limit`	最小扫描行数	0	100	减少日志量，只记录扫描行数较多的查询
`log_queries_not_using_indexes`	记录未使用索引的查询	OFF	OFF	通常建议关闭，避免日志过大
`log_slow_admin_statements`	记录慢管理语句	OFF	OFF	通常不需要
`log_slow_slave_statements`	记录从库慢查询	OFF	OFF	通常不需要
`log_output`	日志输出方式	FILE	FILE	可选TABLE，但性能较差
`log_timestamps`	日志时间戳格式	UTC	SYSTEM	使用系统时间，便于分析

配置示例

方法1：修改my.cnf配置文件

ini

# 慢查询日志配置
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
min_examined_row_limit = 100
log_queries_not_using_indexes = OFF
log_slow_admin_statements = OFF
log_slow_slave_statements = OFF
log_output = FILE
log_timestamps = SYSTEM

方法2：动态配置（无需重启）

sql

-- 启用慢查询日志
SET GLOBAL slow_query_log = 'ON';

-- 设置慢查询阈值
SET GLOBAL long_query_time = 1;

-- 设置最小扫描行数
SET GLOBAL min_examined_row_limit = 100;

-- 设置日志文件路径
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- 设置时间戳格式
SET GLOBAL log_timestamps = 'SYSTEM';

配置验证

sql

-- 查看慢查询日志配置
SHOW VARIABLES LIKE '%slow%';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'min_examined_row_limit';

-- 查看慢查询日志状态
SHOW GLOBAL STATUS LIKE 'Slow_queries';

慢查询日志格式

标准格式

慢查询日志的标准格式示例：

# Time: 2023-12-01T10:00:00.123456+08:00
# User@Host: root[root] @ localhost []  Id:     1
# Query_time: 2.500000  Lock_time: 0.000000 Rows_sent: 10  Rows_examined: 10000
SET timestamp=1670000000;
SELECT * FROM users WHERE age > 30 ORDER BY created_at DESC LIMIT 10;

格式说明

Time：查询执行时间
User@Host：执行查询的用户和主机
Id：连接ID
Query_time：查询执行时间（秒）
Lock_time：锁等待时间（秒）
Rows_sent：发送到客户端的行数
Rows_examined：扫描的行数
SET timestamp：查询执行的时间戳
SQL语句：实际执行的SQL语句

扩展格式

在MySQL 5.6及以上版本，可以通过设置log_slow_extra参数启用扩展格式，包含更多信息：

ini

# 启用扩展格式
log_slow_extra = ON

慢查询日志分析工具

内置工具

mysqldumpslow

功能：MySQL自带的慢查询日志分析工具，简单易用

使用方法：

bash

# 查看帮助
mysqldumpslow --help

# 分析慢查询日志
mysqldumpslow /var/log/mysql/slow.log

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

# 按执行时间排序
mysqldumpslow -s t /var/log/mysql/slow.log

# 显示前10条
mysqldumpslow -t 10 /var/log/mysql/slow.log

# 过滤特定数据库
mysqldumpslow -d database_name /var/log/mysql/slow.log

输出示例：

Reading mysql slow query log from /var/log/mysql/slow.log
Count: 10  Time=2.50s (25s)  Lock=0.00s (0s)  Rows=10.0 (100 rows sent, 100000 rows examined)
  SELECT * FROM users WHERE age > N ORDER BY created_at DESC LIMIT N;

mysqladmin

功能：MySQL管理工具，可以查看慢查询统计信息

使用方法：

bash

# 查看慢查询数量
mysqladmin extended-status | grep Slow_queries

# 查看慢查询相关状态
mysqladmin extended-status | grep -i slow

第三方工具

pt-query-digest

功能：Percona Toolkit中的慢查询日志分析工具，功能强大，分析详细

安装方法：

bash

# Debian/Ubuntu
apt-get install percona-toolkit

# RHEL/CentOS
yum install percona-toolkit

使用方法：

bash

# 基本分析
pt-query-digest /var/log/mysql/slow.log

# 按执行时间排序，显示前20条
pt-query-digest --limit=20 /var/log/mysql/slow.log

# 分析特定时间范围的日志
pt-query-digest --since='2023-12-01 00:00:00' --until='2023-12-01 23:59:59' /var/log/mysql/slow.log

# 按数据库分组分析
pt-query-digest --group-by=db /var/log/mysql/slow.log

# 输出到文件
pt-query-digest /var/log/mysql/slow.log > /var/log/mysql/slow_analysis.log

输出示例：

# 25s user time, 0s system time, 28 total queries, 10 unique
#  sorted by 'Query time' descending
#  Args: /var/log/mysql/slow.log
#  Current date: 2023-12-01
#  Hostname: server1
#  Files: /var/log/mysql/slow.log
#  Overall: 28 total, 10 unique, 0.01 QPS, 0.09x concurrency _____________
#  Time range: 2023-12-01 00:00:00 to 2023-12-01 23:59:59
#  Attribute          total     min     max     avg     95%  stddev  median
#  ============     ======= ======= ======= ======= ======= ======= =======
#  Exec time           25s   1.2s    3.5s    2.5s    3.2s    0.5s    2.4s
#  Lock time            0s     0s      0s      0s      0s      0s      0s
#  Rows sent          280      10      10      10      10      0       10
#  Rows examine    280000  10000  10000  10000  10000      0   10000
#  Query size        2800     100     100     100     100      0      100

# Profile
# Rank Query ID                            Response time Calls R/Call V/M   Item
# ==== =================================== ============= ===== ====== ===== ==========
#    1 0xF1A2B3C4D5E6F7890                   25.0s     10   2.5s   0.00 SELECT users

# Query 1: 10 QPS, 25.0s total, 2.5s avg, 10 calls
# ID: 0xF1A2B3C4D5E6F7890
# Scores: V/M = 0.00
# Time range: 2023-12-01 00:00:00 to 2023-12-01 23:59:59
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ========== ==== ======= ======= ======= ======= ======= ======= =======
# Count         36      10
# Exec time     100     25s   1.2s    3.5s    2.5s    3.2s    0.5s    2.4s
# Lock time      0       0s     0s      0s      0s      0s      0s      0s
# Rows sent      36     100      10      10      10      10      0       10
# Rows examine   36  100000  10000  10000  10000  10000      0   10000
# Query size     36    1000     100     100     100     100      0      100
# String:
# Databases    test
# Hosts        localhost
# Users        root
# Query_time distribution
#   1s以内    0%
#   1-5s     100%
#   5-10s    0%
#  10-100s   0%
#  100s+     0%
# Tables
#    Show table info
#    Server: localhost:3306
#    Database: test
#    Table: users
#    Size: About 1000000 rows, 100MB
# Explain
#    ID  select_type table type possible_keys key  key_len ref  rows  Extra
#     1  SIMPLE      users ALL  NULL          NULL NULL    NULL 10000 Using where; Using filesort

mysqlsla

功能：另一个强大的慢查询日志分析工具，支持多种格式输出

安装方法：

bash

# 从GitHub下载
wget https://github.com/daniel-nichter/mysqlsla/archive/master.zip
unzip master.zip
cd mysqlsla-master
perl Makefile.PL
make
make install

使用方法：

bash

# 基本分析
mysqlsla -lt slow /var/log/mysql/slow.log

# 按执行时间排序
mysqlsla -lt slow -sf +t /var/log/mysql/slow.log

# 输出为HTML格式
mysqlsla -lt slow -o html /var/log/mysql/slow.log > /var/log/mysql/slow_analysis.html

可视化分析工具

Percona Monitoring and Management (PMM)

功能：开源监控解决方案，包含慢查询分析功能

特点：

实时监控慢查询
可视化分析界面
历史趋势分析
集成其他MySQL监控指标

MySQL Enterprise Monitor

功能：MySQL官方监控工具，提供慢查询分析功能

特点：

自动分析慢查询
提供优化建议
集成告警功能
企业级支持

VividCortex (SolarWinds Database Performance Monitor)

功能：第三方数据库性能监控工具

特点：

实时查询分析
智能告警
历史性能分析
多维度可视化

慢查询日志分析方法

分析步骤

收集日志：确保慢查询日志已启用并收集足够的样本
工具选择：根据需要选择合适的分析工具
初步分析：生成总体报告，了解慢查询分布情况
深入分析：针对排名靠前的慢查询进行详细分析
制定优化计划：根据分析结果，制定具体的优化方案
实施优化：执行优化方案，如添加索引、重写SQL等
验证效果：对比优化前后的性能差异
持续监控：定期分析慢查询，持续优化

关键指标分析

执行时间（Query_time）

关注点：总执行时间、平均执行时间、最大执行时间
分析方法：按执行时间排序，优先分析执行时间长的查询
优化方向：重写SQL、添加索引、调整查询计划

扫描行数（Rows_examined）

关注点：扫描行数与返回行数的比例
分析方法：如果扫描行数远大于返回行数，通常需要优化
优化方向：添加合适的索引，减少扫描范围

锁等待时间（Lock_time）

关注点：锁等待时间占总执行时间的比例
分析方法：锁等待时间长通常表示存在锁竞争
优化方向：减少事务范围、优化锁顺序、使用更细粒度的锁

临时表使用（Using temporary）

关注点：是否使用临时表，临时表的大小
分析方法：临时表使用频繁会影响性能
优化方向：优化查询，避免使用临时表，或增加tmp_table_size

文件排序（Using filesort）

关注点：是否使用文件排序
分析方法：文件排序会增加I/O开销
优化方向：添加合适的索引，使排序操作使用索引

常见慢查询类型及优化

全表扫描

识别特征：

type = ALL in EXPLAIN output
扫描行数等于表的总行数

优化方法：

添加合适的索引
优化WHERE条件，使查询使用索引
考虑表分区

索引失效

识别特征：

应该使用索引但未使用
索引列上使用了函数或表达式

优化方法：

避免在索引列上使用函数
重写查询，使索引列单独出现在条件中
考虑使用函数索引（MySQL 5.7+）

排序操作

识别特征：

Using filesort in EXPLAIN output
排序字段未包含在索引中

优化方法：

创建包含排序字段的复合索引
调整ORDER BY子句，与索引顺序一致
考虑使用覆盖索引

连接操作

识别特征：

多表连接查询执行时间长
连接条件未使用索引

优化方法：

为连接条件添加索引
优化连接顺序
考虑使用STRAIGHT_JOIN提示

子查询

识别特征：

子查询嵌套层级深
相关子查询执行效率低

优化方法：

重写为JOIN查询
使用临时表存储子查询结果
考虑使用EXISTS替代IN

慢查询日志管理

日志轮转

使用logrotate

配置示例：

创建 /etc/logrotate.d/mysql-slow-log 文件：

txt

/var/log/mysql/slow.log {
    daily
    rotate 7
    missingok
    compress
    delaycompress
    notifempty
    create 640 mysql mysql
    postrotate
        # 刷新日志
        mysql -e "SET GLOBAL slow_query_log = 'OFF'; SET GLOBAL slow_query_log = 'ON';"
    endscript
}

手动轮转

sql

-- 临时关闭慢查询日志
SET GLOBAL slow_query_log = 'OFF';

-- 重命名日志文件
mv /var/log/mysql/slow.log /var/log/mysql/slow.log.old

-- 重新启用慢查询日志
SET GLOBAL slow_query_log = 'ON';

-- 压缩旧日志
gzip /var/log/mysql/slow.log.old

日志清理

定期清理策略

保留期限：根据存储空间和审计需求，设置合理的保留期限
清理频率：每天或每周清理一次过期日志
自动化：使用cron任务自动执行清理

示例脚本：

bash

#!/bin/bash

# 清理30天前的慢查询日志
find /var/log/mysql -name "slow.log.*" -mtime +30 -delete

# 压缩7天前的慢查询日志
find /var/log/mysql -name "slow.log.*" -not -name "*.gz" -mtime +7 -exec gzip {} \;

日志备份

备份策略

备份频率：与系统备份策略保持一致
备份方式：将慢查询日志纳入常规备份
存储位置：备份到异地存储，确保数据安全
保留期限：根据审计需求设置保留期限

慢查询优化最佳实践

配置最佳实践

生产环境配置

ini

# 生产环境慢查询日志配置
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
min_examined_row_limit = 100
log_queries_not_using_indexes = OFF
log_slow_admin_statements = OFF
log_slow_slave_statements = OFF
log_output = FILE
log_timestamps = SYSTEM

开发/测试环境配置

ini

# 开发/测试环境慢查询日志配置
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5  # 更严格的阈值
min_examined_row_limit = 50
log_queries_not_using_indexes = ON  # 开发环境可以开启
log_slow_admin_statements = OFF
log_slow_slave_statements = OFF
log_output = FILE
log_timestamps = SYSTEM

分析最佳实践

定期分析：每周至少分析一次慢查询日志
对比分析：与历史数据对比，识别性能退化
重点关注：优先优化执行频率高、执行时间长的查询
综合分析：结合系统负载、硬件资源等因素综合分析
持续优化：建立慢查询优化的持续改进机制

优化最佳实践

添加合适的索引：根据查询模式添加索引
重写SQL语句：优化查询逻辑，减少不必要的操作
调整数据库结构：合理设计表结构，避免冗余字段
使用缓存：对于频繁查询的结果，考虑使用缓存
分区表：对于大表，考虑使用分区表
调整参数：根据查询特点调整MySQL参数
应用层优化：优化应用代码，减少数据库访问

常见问题（FAQ）

Q1: 如何合理设置慢查询阈值

A1: 慢查询阈值设置应考虑以下因素：

业务场景：不同业务对响应时间的要求不同
系统负载：高负载系统可设置稍高的阈值
硬件配置：高性能硬件可设置更低的阈值
监控目标：根据监控目标调整阈值
经验参考：
- 生产环境：通常设置为1秒
- 开发环境：可设置为0.5秒
- 高并发系统：可设置为0.1秒

Q2: 慢查询日志对系统性能有影响吗

A2: 慢查询日志确实会对系统性能产生一定影响：

影响程度：取决于日志记录的频率和详细程度
优化建议：
- 设置合理的阈值，避免记录过多查询
- 使用min_examined_row_limit减少日志量
- 关闭log_queries_not_using_indexes（生产环境）
- 使用文件输出而非表输出
- 定期轮转日志，避免单个文件过大

Q3: 如何处理大量慢查询日志

A3: 处理大量慢查询日志的方法：

日志轮转：使用logrotate定期轮转日志
压缩存储：对历史日志进行压缩
定期清理：删除过期日志
集中管理：使用ELK Stack等工具集中管理日志
采样分析：对大量日志进行采样分析

Q4: 如何区分真正的性能问题和误报

A4: 区分方法：

执行频率：频繁执行的慢查询更可能是真正的问题
持续时间：长期存在的慢查询需要关注
业务影响：影响核心业务的慢查询优先级更高
执行计划：分析执行计划，确认是否存在优化空间
上下文分析：结合系统负载、硬件资源等因素综合分析

Q5: 除了慢查询日志，还有哪些性能分析工具

A5: 其他性能分析工具：

Performance Schema：MySQL内置的性能监控工具
sys schema：基于Performance Schema的视图集合
SHOW PROFILE：分析单个查询的执行细节
EXPLAIN ANALYZE：MySQL 8.0+提供的执行计划分析工具
第三方工具：
- Percona Monitoring and Management (PMM)
- MySQL Enterprise Monitor
- VividCortex
- pt-query-digest（Percona Toolkit）

案例分析

案例1：电商系统商品列表查询优化

背景

电商系统商品列表页面加载缓慢
慢查询日志中发现商品列表查询执行时间超过2秒
表结构：products表有100万条记录

慢查询分析

sql

-- 原始查询
SELECT id, name, price, category, stock FROM products 
WHERE category = 'electronics' AND stock > 0 
ORDER BY created_at DESC LIMIT 20;

-- 执行计划
EXPLAIN SELECT id, name, price, category, stock FROM products 
WHERE category = 'electronics' AND stock > 0 
ORDER BY created_at DESC LIMIT 20;

-- 执行计划输出
+----+-------------+----------+------+---------------+------+---------+------+---------+-----------------------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows    | Extra                       |
+----+-------------+----------+------+---------------+------+---------+------+---------+-----------------------------+
|  1 | SIMPLE      | products | ALL  | NULL          | NULL | NULL    | NULL | 1000000 | Using where; Using filesort |
+----+-------------+----------+------+---------------+------+---------+------+---------+-----------------------------+

优化方案

添加复合索引：

sql

-- 添加复合索引
CREATE INDEX idx_category_stock_created_at ON products (category, stock, created_at DESC);

优化查询：

sql

-- 优化后的查询（使用覆盖索引）
SELECT id, name, price, category, stock FROM products 
WHERE category = 'electronics' AND stock > 0 
ORDER BY created_at DESC LIMIT 20;

优化效果

执行时间：从2.5秒减少到0.01秒
扫描行数：从100万行减少到20行
执行计划：使用索引，避免全表扫描和文件排序

案例2：用户登录慢查询优化

背景

用户登录过程缓慢
慢查询日志中发现用户验证查询执行时间超过1秒
表结构：users表有50万条记录

慢查询分析

sql

-- 原始查询
SELECT id, username, email, role FROM users 
WHERE username = 'user123' AND password = 'hashed_password';

-- 执行计划
EXPLAIN SELECT id, username, email, role FROM users 
WHERE username = 'user123' AND password = 'hashed_password';

-- 执行计划输出
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | users | ALL  | NULL          | NULL | NULL    | NULL | 500000 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+

优化方案

添加索引：

sql

-- 为username字段添加索引
CREATE INDEX idx_username ON users (username);

优化查询：

sql

-- 优化后的查询（使用索引）
SELECT id, username, email, role FROM users 
WHERE username = 'user123' AND password = 'hashed_password';

优化效果

执行时间：从1.2秒减少到0.001秒
扫描行数：从50万行减少到1行
执行计划：使用索引，避免全表扫描

MySQL 慢查询日志配置与分析 ​

慢查询日志配置 ​

核心参数配置 ​

配置示例 ​

方法1：修改my.cnf配置文件 ​

方法2：动态配置（无需重启） ​

配置验证 ​

慢查询日志格式 ​

标准格式 ​

格式说明 ​

扩展格式 ​

慢查询日志分析工具 ​

内置工具 ​

mysqldumpslow ​

mysqladmin ​

第三方工具 ​

pt-query-digest ​

mysqlsla ​

可视化分析工具 ​

Percona Monitoring and Management (PMM) ​

MySQL Enterprise Monitor ​

VividCortex (SolarWinds Database Performance Monitor) ​

慢查询日志分析方法 ​

分析步骤 ​

关键指标分析 ​

执行时间（Query_time） ​

扫描行数（Rows_examined） ​

锁等待时间（Lock_time） ​

临时表使用（Using temporary） ​

文件排序（Using filesort） ​

常见慢查询类型及优化 ​

全表扫描 ​

索引失效 ​

排序操作 ​

连接操作 ​

子查询 ​

慢查询日志管理 ​

日志轮转 ​

使用logrotate ​

手动轮转 ​

日志清理 ​

定期清理策略 ​

日志备份 ​

备份策略 ​

慢查询优化最佳实践 ​

配置最佳实践 ​

生产环境配置 ​

开发/测试环境配置 ​

分析最佳实践 ​

优化最佳实践 ​

常见问题（FAQ） ​

Q1: 如何合理设置慢查询阈值 ​

Q2: 慢查询日志对系统性能有影响吗 ​

MySQL 慢查询日志配置与分析

慢查询日志配置

核心参数配置

配置示例

方法1：修改my.cnf配置文件

方法2：动态配置（无需重启）

配置验证

慢查询日志格式

标准格式

格式说明

扩展格式

慢查询日志分析工具

内置工具

mysqldumpslow

mysqladmin

第三方工具

pt-query-digest

mysqlsla

可视化分析工具

Percona Monitoring and Management (PMM)

MySQL Enterprise Monitor

VividCortex (SolarWinds Database Performance Monitor)

慢查询日志分析方法

分析步骤

关键指标分析

执行时间（Query_time）

扫描行数（Rows_examined）

锁等待时间（Lock_time）

临时表使用（Using temporary）

文件排序（Using filesort）

常见慢查询类型及优化

全表扫描

索引失效

排序操作

连接操作

子查询

慢查询日志管理

日志轮转

使用logrotate

手动轮转

日志清理

定期清理策略

日志备份

备份策略

慢查询优化最佳实践

配置最佳实践

生产环境配置

开发/测试环境配置

分析最佳实践

优化最佳实践

常见问题（FAQ）

Q1: 如何合理设置慢查询阈值

Q2: 慢查询日志对系统性能有影响吗