印刻数据库

外观

MariaDB 慢查询分析 ​

慢查询分析概述 ​

什么是慢查询分析 ​

慢查询分析是通过分析执行时间超过阈值的 SQL 查询，识别性能瓶颈并进行优化的过程。慢查询分析是 MariaDB 性能优化的重要手段，可以帮助 DBA 找出效率低下的 SQL 语句，从而进行针对性优化。

慢查询分析的目的 ​

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

慢查询分析的步骤 ​

  1. 收集慢查询日志：启用慢查询日志，设置合理的阈值
  2. 分析慢查询日志：使用工具分析慢查询日志，识别问题查询
  3. 查看执行计划：对问题查询使用 EXPLAIN 命令查看执行计划
  4. 优化查询：根据分析结果优化查询语句、索引或数据库设计
  5. 验证优化效果：执行优化后的查询，验证性能是否提升
  6. 持续监控：定期分析慢查询日志，确保性能持续优化

慢查询识别 ​

慢查询阈值设置 ​

慢查询阈值的设置应根据业务需求和系统性能调整：

业务类型建议阈值说明
实时交易系统100-500ms核心业务查询要求低延迟
数据分析系统1-5秒复杂查询允许较长时间
批处理系统5-30秒批量操作可以接受较长时间
一般Web应用500ms-1秒平衡用户体验和系统负载

慢查询识别方法 ​

  1. 使用慢查询日志

    • 启用慢查询日志，设置合适的阈值
    • 定期分析慢查询日志，识别问题查询

  2. 使用 Performance Schema

    sql
    -- 查看 Performance Schema 是否启用
SHOW VARIABLES LIKE 'performance_schema';

-- 启用 Performance Schema
SET GLOBAL performance_schema = ON;

-- 查询慢查询
SELECT * FROM performance_schema.events_statements_summary_by_digest 
WHERE SUM_TIMER_WAIT > 1000000000000 -- 1秒
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

  3. 使用 SHOW PROCESSLIST

    sql
    -- 查看当前运行的查询
SHOW FULL PROCESSLIST;

-- 过滤长时间运行的查询
SELECT id, user, host, db, command, time, state, info 
FROM information_schema.processlist 
WHERE time > 60 -- 60秒
ORDER BY time DESC;

  4. 使用 MariaDB Enterprise Monitor

    • 商业版 MariaDB 提供的监控工具
    • 实时监控慢查询，提供可视化分析
    • 自动识别性能问题，提供优化建议

慢查询分析工具 ​

mysqldumpslow ​

MariaDB 自带的慢查询分析工具，简单易用：

bash
# 分析慢查询日志，按执行次数排序
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

MariaDB Query Analyzer ​

商业版 MariaDB 提供的查询分析工具：

  • 实时监控查询性能
  • 提供详细的查询执行计划分析
  • 自动识别性能问题
  • 提供优化建议

执行计划分析 ​

什么是执行计划 ​

执行计划是 MariaDB 查询优化器生成的查询执行方案，包含了查询如何执行的详细信息，如使用的索引、表连接顺序、数据访问方式等。

使用 EXPLAIN 命令 ​

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

-- 显示扩展执行计划
EXPLAIN EXTENDED SELECT * FROM mytable WHERE created_at < '2025-01-01' ORDER BY id DESC LIMIT 100;

-- 显示分区信息
EXPLAIN PARTITIONS SELECT * FROM mytable WHERE created_at < '2025-01-01' ORDER BY id DESC LIMIT 100;

-- 显示 FORMAT=JSON 格式的执行计划
EXPLAIN FORMAT=JSON SELECT * FROM mytable WHERE created_at < '2025-01-01' ORDER BY id DESC LIMIT 100;

执行计划字段说明 ​

字段名称描述
id查询块 ID
select_type查询类型（SIMPLE, PRIMARY, UNION, SUBQUERY 等）
table表名
partitions访问的分区
type访问类型（ALL, index, range, ref, eq_ref, const, system, NULL）
possible_keys可能使用的索引
key实际使用的索引
key_len使用的索引长度
ref与索引比较的列或常量
rows估计需要扫描的行数
filtered过滤后的行数百分比
Extra额外信息（Using index, Using where, Using filesort, Using temporary 等）

访问类型（type）优先级 ​

从最优到最差的访问类型：

  1. system：表只有一行记录（系统表）
  2. const：通过主键或唯一索引访问，只返回一行记录
  3. eq_ref：表连接时，被连接表使用主键或唯一索引访问
  4. ref：使用非唯一索引访问
  5. range：使用索引范围扫描（如 BETWEEN, IN, >, < 等）
  6. index：全索引扫描
  7. ALL：全表扫描

额外信息（Extra）解读 ​

  1. Using index：使用索引覆盖查询，不需要回表
  2. Using where：使用 WHERE 条件过滤
  3. Using filesort：使用文件排序，需要优化
  4. Using temporary：使用临时表，需要优化
  5. Using join buffer：使用连接缓冲区
  6. Using index condition：使用索引条件下推
  7. Using MRR：使用多范围读取优化
  8. Using index for group-by：使用索引进行分组

慢查询优化技巧 ​

索引优化 ​

  1. 添加合适的索引

    • 为 WHERE 条件中的列添加索引
    • 为 ORDER BY 和 GROUP BY 中的列添加索引
    • 考虑使用联合索引，遵循最左前缀原则

  2. 优化索引结构

    • 避免创建过多索引，影响写入性能
    • 删除冗余索引和未使用的索引
    • 考虑使用前缀索引，减少索引大小

  3. 避免索引失效

    • 避免在查询条件中使用函数或表达式
    • 避免使用 !=, <>, NOT IN 等操作符
    • 避免使用 OR 连接不同的索引列
    • 确保查询条件与索引列类型一致

查询语句优化 ​

  1. 简化查询

    • 只查询需要的列，避免 SELECT *
    • 分解复杂查询为多个简单查询
    • 避免嵌套查询，考虑使用 JOIN

  2. 优化 WHERE 条件

    • 将最严格的条件放在前面
    • 避免使用 LIKE '%value'，考虑使用全文索引
    • 合理使用 IN 和 EXISTS，避免使用 NOT IN

  3. 优化 JOIN 操作

    • 确保连接列有索引
    • 小表驱动大表
    • 避免笛卡尔积
    • 合理使用不同类型的 JOIN

  4. 优化 GROUP BY 和 ORDER BY

    • 确保 GROUP BY 和 ORDER BY 使用相同的列
    • 避免在 GROUP BY 中使用函数
    • 考虑使用索引覆盖查询

数据库设计优化 ​

  1. 表结构优化

    • 选择合适的数据类型
    • 避免使用 TEXT 和 BLOB 类型存储频繁访问的数据
    • 合理设计表分区

  2. 范式和反范式

    • 适当使用反范式，减少 JOIN 操作
    • 考虑使用缓存表和汇总表

  3. 拆分大表

    • 水平拆分：按行拆分，如按时间或 ID 拆分
    • 垂直拆分：按列拆分，将不常用的列拆分到其他表

常见慢查询场景及优化 ​

全表扫描 ​

现象 ​

执行计划中 type 为 ALL，扫描行数远大于返回行数。

原因 ​

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

优化建议 ​

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

磁盘临时表 ​

现象 ​

执行计划中 Extra 包含 Using temporary。

原因 ​

  • 查询结果集过大，超过 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 操作，尽量使用相同的字段
  • 考虑使用索引覆盖查询

磁盘文件排序 ​

现象 ​

执行计划中 Extra 包含 Using filesort。

原因 ​

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

优化建议 ​

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

长锁定时间 ​

现象 ​

慢查询日志中 Lock_time 较大。

原因 ​

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

优化建议 ​

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

慢查询分析最佳实践 ​

  1. 定期分析慢查询日志

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

  2. 建立慢查询基线

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

  3. 结合其他监控数据

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

  4. 持续优化

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

  5. 使用自动化工具

    • 使用 Percona Monitoring and Management (PMM) 等工具自动化分析慢查询
    • 设置慢查询告警，及时发现性能问题

常见问题（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 建立完善的慢查询分析机制，定期分析慢查询日志，结合其他监控数据，持续优化数据库性能。同时，使用自动化工具可以提高慢查询分析的效率，及时发现和解决性能问题。