印刻数据库

外观

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

慢查询日志概述 ​

什么是慢查询日志 ​

慢查询日志是 MariaDB 记录执行时间超过阈值的 SQL 查询的日志文件，是 DBA 进行性能优化的重要工具。通过分析慢查询日志，可以识别出效率低下的 SQL 语句，找出性能瓶颈，从而进行针对性优化。

慢查询日志的作用 ​

  • 识别执行效率低下的 SQL 语句
  • 分析查询执行计划，找出索引问题
  • 发现数据库设计不合理的地方
  • 监控数据库性能趋势
  • 为容量规划提供依据

慢查询日志配置 ​

配置参数 ​

参数名称默认值描述
slow_query_logOFF是否启用慢查询日志
slow_query_log_file（平台相关）慢查询日志文件路径
long_query_time10.0慢查询阈值，单位秒
log_queries_not_using_indexesOFF是否记录未使用索引的查询
log_slow_admin_statementsOFF是否记录慢管理语句（如 ALTER TABLE）
log_slow_filteradmin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk慢查询过滤条件
log_slow_rate_limit1慢查询日志记录频率限制
log_slow_verbosityquery_plan,explain慢查询日志详细程度
min_examined_row_limit0记录查询的最小扫描行数

配置示例 ​

ini
# my.cnf
[mysqld]
# 启用慢查询日志
slow_query_log = ON

# 慢查询日志文件路径
slow_query_log_file = /var/log/mariadb/mariadb-slow.log

# 慢查询阈值，设置为 1 秒
long_query_time = 1

# 记录未使用索引的查询
log_queries_not_using_indexes = ON

# 记录慢管理语句
log_slow_admin_statements = ON

# 慢查询过滤条件
log_slow_filter = admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk

# 慢查询日志详细程度
log_slow_verbosity = query_plan,explain

# 记录查询的最小扫描行数
min_examined_row_limit = 100

动态调整配置 ​

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

-- 设置慢查询阈值为 2 秒
SET GLOBAL long_query_time = 2;

-- 启用记录未使用索引的查询
SET GLOBAL log_queries_not_using_indexes = ON;

-- 查看当前慢查询日志配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_queries_not_using_indexes';

慢查询日志管理 ​

日志文件位置 ​

操作系统默认慢查询日志路径
Linux (RPM)/var/log/mariadb/mariadb-slow.log
Linux (Debian)/var/log/mysql/mysql-slow.log
Windows数据目录下的 hostname-slow.log
macOS/usr/local/var/log/mariadb-slow.log

日志轮换 ​

使用 logrotate 进行日志轮换 ​

ini
# /etc/logrotate.d/mariadb
/var/log/mariadb/mariadb-slow.log {
    daily
    rotate 7
    missingok
    compress
    delaycompress
    notifempty
    create 640 mysql mysql
    sharedscripts
    postrotate
        # 刷新慢查询日志
        /usr/bin/mysqladmin flush-slow-logs
    endscript
}

手动进行日志轮换 ​

bash
# 重命名当前慢查询日志
mv /var/log/mariadb/mariadb-slow.log /var/log/mariadb/mariadb-slow.log.old

# 刷新慢查询日志，生成新的日志文件
mysqladmin flush-slow-logs

日志清理策略 ​

日志类型保留时间清理方式
生产环境慢查询日志7-30天logrotate 自动清理
测试环境慢查询日志3-7天logrotate 自动清理
归档日志1年压缩存储到归档目录

慢查询日志格式 ​

日志条目格式 ​

# Time: 2025-12-27T10:15:30.123456Z
# User@Host: user[user] @ localhost []  Id: 12345
# Query_time: 2.500000  Lock_time: 0.100000 Rows_sent: 100  Rows_examined: 10000
# Thread_id: 12345  Schema: mydb  QC_hit: No
# Rows_affected: 0  Bytes_sent: 10240
# Full_scan: Yes  Full_join: No  Tmp_table: Yes  Tmp_table_on_disk: No
# Filesort: Yes  Filesort_on_disk: No  Merge_passes: 0
#   InnoDB_IO_r_ops: 0  InnoDB_IO_r_bytes: 0  InnoDB_IO_w_ops: 0  InnoDB_IO_w_bytes: 0
#   InnoDB_pages_distinct: 1000
use mydb;
SET timestamp=1735325730;
SELECT * FROM mytable WHERE created_at < '2025-01-01' ORDER BY id DESC LIMIT 100;

日志字段说明 ​

字段名称描述
Time查询执行时间
User@Host执行查询的用户和主机
Id连接 ID
Query_time查询执行时间，单位秒
Lock_time锁定时间，单位秒
Rows_sent返回的行数
Rows_examined扫描的行数
Thread_id线程 ID
Schema数据库名称
QC_hit是否命中查询缓存
Rows_affected影响的行数
Bytes_sent发送的数据量
Full_scan是否全表扫描
Full_join是否全连接
Tmp_table是否使用临时表
Tmp_table_on_disk是否使用磁盘临时表
Filesort是否使用文件排序
Filesort_on_disk是否使用磁盘文件排序
Merge_passes合并排序的次数
InnoDB_IO_r_opsInnoDB 读操作次数
InnoDB_IO_r_bytesInnoDB 读字节数
InnoDB_IO_w_opsInnoDB 写操作次数
InnoDB_IO_w_bytesInnoDB 写字节数
InnoDB_pages_distinctInnoDB 访问的不同页面数

慢查询日志分析 ​

常用分析命令 ​

查看慢查询数量 ​

bash
# 统计慢查询数量
wc -l /var/log/mariadb/mariadb-slow.log

# 查看前 10 条慢查询
tail -n 100 /var/log/mariadb/mariadb-slow.log | grep -A 10 "# Query_time"

查找特定类型的慢查询 ​

bash
# 查找全表扫描的慢查询
grep -B 5 -A 10 "Full_scan: Yes" /var/log/mariadb/mariadb-slow.log

# 查找使用磁盘临时表的慢查询
grep -B 5 -A 10 "Tmp_table_on_disk: Yes" /var/log/mariadb/mariadb-slow.log

# 查找使用磁盘文件排序的慢查询
grep -B 5 -A 10 "Filesort_on_disk: Yes" /var/log/mariadb/mariadb-slow.log

分析慢查询执行时间 ​

bash
# 提取慢查询执行时间并排序
grep "Query_time:" /var/log/mariadb/mariadb-slow.log | awk '{print $2}' | sort -nr | head -10

慢查询日志分析工具 ​

mysqldumpslow ​

MariaDB 自带的慢查询日志分析工具：

bash
# 安装位置
which mysqldumpslow

# 分析慢查询日志，按执行次数排序
mysqldumpslow -s c -t 10 /var/log/mariadb/mariadb-slow.log

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

# 按锁定时间排序
mysqldumpslow -s l -t 10 /var/log/mariadb/mariadb-slow.log

# 按返回行数排序
mysqldumpslow -s r -t 10 /var/log/mariadb/mariadb-slow.log

# 过滤特定数据库的慢查询
mysqldumpslow -d mydb -t 10 /var/log/mariadb/mariadb-slow.log

pt-query-digest ​

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

bash
# 安装 Percona Toolkit
sudo apt-get install percona-toolkit

# 基本使用
pt-query-digest /var/log/mariadb/mariadb-slow.log

# 保存分析结果到文件
pt-query-digest /var/log/mariadb/mariadb-slow.log > slow_query_analysis.txt

# 分析特定时间段的慢查询
pt-query-digest --since '2025-12-27 10:00:00' --until '2025-12-27 11:00:00' /var/log/mariadb/mariadb-slow.log

# 按查询类型分析
pt-query-digest --group-by query /var/log/mariadb/mariadb-slow.log

# 按用户分析
pt-query-digest --group-by user /var/log/mariadb/mariadb-slow.log

# 按主机分析
pt-query-digest --group-by host /var/log/mariadb/mariadb-slow.log

MariaDB Enterprise Monitor ​

商业版 MariaDB 提供的监控工具，包含慢查询分析功能：

  • 实时监控慢查询
  • 可视化展示慢查询趋势
  • 自动识别性能问题
  • 提供优化建议

常见慢查询问题分析 ​

全表扫描 ​

现象 ​

日志中显示 Full_scan: Yes，扫描行数远大于返回行数。

原因 ​

  • 没有合适的索引
  • 查询条件使用了函数或表达式，导致索引失效
  • 查询条件使用了 !=、<>、NOT IN 等操作符
  • 查询条件使用了 OR 连接，且没有针对所有条件的联合索引

优化建议 ​

  • 为查询条件添加合适的索引
  • 避免在查询条件中使用函数或表达式
  • 尽量使用 IN 代替 NOT IN
  • 考虑使用 UNION 代替 OR

磁盘临时表 ​

现象 ​

日志中显示 Tmp_table_on_disk: Yes。

原因 ​

  • 查询结果集过大，超过 tmp_table_size 或 max_heap_table_size
  • 查询使用了 BLOB 或 TEXT 类型字段
  • 查询包含 GROUP BY 和 ORDER BY 操作，且字段不同

优化建议 ​

  • 增加 tmp_table_size 和 max_heap_table_size 参数值
  • 避免在临时表中使用 BLOB 或 TEXT 类型
  • 优化 GROUP BY 和 ORDER BY 操作，尽量使用相同的字段
  • 考虑使用索引覆盖查询

磁盘文件排序 ​

现象 ​

日志中显示 Filesort_on_disk: Yes。

原因 ​

  • 查询结果集过大，超过 sort_buffer_size
  • 查询包含复杂的 ORDER BY 操作
  • 查询使用了 DISTINCT 和 ORDER BY 操作

优化建议 ​

  • 增加 sort_buffer_size 参数值
  • 优化 ORDER BY 操作，尽量使用索引排序
  • 避免在查询中同时使用 DISTINCT 和复杂的 ORDER BY
  • 考虑使用索引覆盖查询

长锁定时间 ​

现象 ​

日志中显示 Lock_time 较大。

原因 ​

  • 表级锁争用（如 MyISAM 存储引擎）
  • 行级锁争用（如 InnoDB 存储引擎）
  • 事务持有锁时间过长
  • 死锁导致的等待

优化建议 ​

  • 对于高并发场景，使用 InnoDB 存储引擎
  • 优化事务设计，尽量缩短事务持有锁的时间
  • 避免在事务中执行长时间的操作
  • 优化索引，减少锁冲突

慢查询优化步骤 ​

1. 收集慢查询日志 ​

启用慢查询日志，设置合适的阈值，收集足够的慢查询数据。

2. 分析慢查询日志 ​

使用 pt-query-digest 等工具分析慢查询日志，识别出最需要优化的查询。

3. 查看执行计划 ​

对慢查询语句使用 EXPLAIN 命令查看执行计划，找出问题所在。

sql
EXPLAIN SELECT * FROM mytable WHERE created_at < '2025-01-01' ORDER BY id DESC LIMIT 100;

4. 优化查询语句 ​

根据执行计划，优化查询语句：

  • 简化查询，减少不必要的字段和表连接
  • 优化查询条件，避免全表扫描
  • 合理使用索引
  • 避免在查询中使用函数或表达式

5. 优化数据库结构 ​

  • 优化表结构，合理设计字段类型
  • 优化索引，添加必要的索引，删除冗余索引
  • 考虑分表、分区等方式，分散数据量

6. 优化配置参数 ​

根据慢查询分析结果，调整相关配置参数：

  • tmp_table_size 和 max_heap_table_size
  • sort_buffer_size
  • read_buffer_size 和 read_rnd_buffer_size
  • join_buffer_size

7. 验证优化效果 ​

优化后，再次运行查询，比较执行时间，验证优化效果。

慢查询日志最佳实践 ​

配置最佳实践 ​

  1. 合理设置慢查询阈值

    • 生产环境：根据业务需求设置，一般为 1-5 秒
    • 测试环境：可以设置更小的值，如 0.1 秒，便于发现潜在问题
    • 避免设置过小，导致日志过大；避免设置过大，导致漏报慢查询

  2. 启用合适的过滤条件

    • 根据需要调整 log_slow_filter 参数
    • 记录关键的慢查询信息，如全表扫描、磁盘临时表等

  3. 设置合适的日志详细程度

    • 启用 query_plan 和 explain，便于分析执行计划
    • 根据需要调整 log_slow_verbosity 参数

  4. 定期轮换日志

    • 使用 logrotate 定期轮换日志
    • 压缩归档旧日志，便于后续查询

分析最佳实践 ​

  1. 定期分析慢查询日志

    • 每天或每周分析一次慢查询日志
    • 及时发现和解决性能问题

  2. 建立慢查询基线

    • 记录正常情况下的慢查询数量和类型
    • 当慢查询数量或类型发生明显变化时，及时预警

  3. 结合其他监控数据

    • 将慢查询分析与系统监控、数据库监控结合
    • 全面了解数据库性能状况

  4. 持续优化

    • 建立慢查询优化的持续改进机制
    • 定期回顾和优化已优化的查询

常见问题（FAQ） ​

Q: 如何确定慢查询阈值？ ​

A: 确定慢查询阈值的方法：

  • 参考业务需求，核心业务查询一般要求在 1 秒内完成
  • 分析历史查询性能数据，找出 95% 或 99% 的查询执行时间
  • 根据系统负载调整，高负载时可以适当提高阈值
  • 考虑使用动态阈值，根据不同时间段调整

Q: 慢查询日志过大怎么办？ ​

A: 解决方法：

  • 调整慢查询阈值，减少日志量
  • 优化慢查询，减少慢查询数量
  • 增加日志轮换频率
  • 考虑使用 log_slow_rate_limit 限制日志记录频率
  • 使用集中式日志管理平台，如 ELK Stack

Q: 为什么有些慢查询没有被记录？ ​

A: 可能的原因：

  • 慢查询阈值设置过高，导致这些查询没有达到记录条件
  • 慢查询日志没有启用
  • log_slow_filter 设置不当，过滤掉了这些查询
  • log_slow_rate_limit 设置过小，限制了日志记录
  • 查询执行时间受系统负载影响，有时快有时慢

Q: 如何实时监控慢查询？ ​

A: 实时监控方法：

  • 使用 tail -f 命令实时查看慢查询日志
  • 集成到集中式日志管理平台，设置实时告警
  • 使用 MariaDB Enterprise Monitor 等商业监控工具
  • 编写自定义脚本，定期分析慢查询日志并发送告警

Q: 慢查询优化后性能没有提升怎么办？ ​

A: 排查步骤：

  • 重新分析执行计划，确认优化是否生效
  • 检查是否有其他因素影响性能，如系统负载、磁盘 I/O 等
  • 考虑调整其他相关配置参数
  • 检查是否存在锁争用或死锁问题
  • 考虑优化数据库结构或应用程序设计

总结 ​

慢查询日志是 MariaDB 性能优化的重要工具，通过合理配置和有效分析慢查询日志，可以识别出效率低下的 SQL 语句，找出性能瓶颈，从而进行针对性优化。建议 DBA 建立完善的慢查询日志管理机制，包括合理的配置、定期的分析、持续的优化和自动化的监控，以确保数据库系统的高性能运行。