likes
comments
collection
share

深入解析与优化：MySQL 慢查询日志的设置与分析

作者站长头像
站长
· 阅读数 24

概述：

在高性能的数据库管理中，慢查询的优化是一个不可或缺的环节。慢查询不仅会影响用户体验，还可能成为系统瓶颈，限制整体性能的提升。MySQL 作为世界上最流行的开源关系型数据库之一，提供了一个功能强大的慢查询日志，用于帮助我们识别和分析执行时间过长的 SQL 语句。本文将深入探讨 MySQL 慢查询日志的设置与分析，从基本配置到高级分析，优化数据库性能，提升应用响应速度。

设置慢查询日志

  1. 启用慢查询日志: 打开 MySQL 配置文件（通常名为 my.cnf 或 my.ini），在 [mysqld] 部分添加或更新以下配置：

    slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2
log_queries_not_using_indexes = 1
min_examined_row_limit = 0
    • slow_query_log: 设置为 1 或 ON 以启用慢查询日志。
    • slow_query_log_file: 指定慢查询日志的文件路径。
    • long_query_time: 设置查询的最长执行时间（秒）。超过此时间的查询会被记录。
    • log_queries_not_using_indexes: 如果设置为 1 或 ON，那么不使用索引的查询也会被记录，无论执行时间如何。
    • min_examined_row_limit: 设置要检查的最小行数，只有检查的行数超过这个值的查询才会被记录。

  2. 重启 MySQL 服务: 保存配置文件更改并重启 MySQL 服务，使更改生效。

  3. 动态配置（无需重启）: 通过 SQL 语句设置 MySQL 慢查询日志的配置：

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

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

-- 设置记录慢查询的时间阈值（例如，设置为2秒）
SET GLOBAL long_query_time = 2;

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

-- 设置查询必须检查的最小行数才能被记录到慢查询日志中
SET GLOBAL min_examined_row_limit = 0;

获取当前慢查询日志的配置：

-- 获取慢查询日志是否启用
SHOW GLOBAL VARIABLES LIKE 'slow_query_log';

-- 获取慢查询日志文件路径
SHOW GLOBAL VARIABLES LIKE 'slow_query_log_file';

-- 获取记录慢查询的时间阈值
SHOW GLOBAL VARIABLES LIKE 'long_query_time';

-- 获取是否记录未使用索引的查询
SHOW GLOBAL  VARIABLES LIKE 'log_queries_not_using_indexes';

-- 获取查询必须检查的最小行数才能被记录到慢查询日志中
SHOW GLOBAL VARIABLES LIKE 'min_examined_row_limit';

分析慢查询日志

  1. 查看慢查询日志文件: 使用 less、cat 或其他文本查看工具查看慢查询日志文件。例如：

    less /var/log/mysql/mysql-slow.log

  2. 使用 mysqldumpslow 工具: MySQL 提供了 mysqldumpslow 命令行工具，用于解析慢查询日志并汇总信息。以下是一些常用的选项：

    • -s: 按照某个标准排序输出，常见的有 at（平均查询时间）、c（计数）、l（锁定时间）等。
    • -t: 显示前 N 条记录。
    • -g: 使用正则表达式过滤查询。

    示例命令：

    mysqldumpslow -s at -t 10 /var/log/mysql/mysql-slow.log

    这将按平均查询时间排序并显示前10条慢查询。

    1. 手动分析: 你可以手动查看慢查询日志，关注 Query_time、Lock_time、Rows_sent、Rows_examined 等参数，并根据 SQL 语句进行优化。

  3. 使用第三方工具: 有许多第三方工具和服务可以帮助分析慢查询日志，例如 Percona Toolkit 中的 pt-query-digest 工具，或者使用 Grafana 和 Prometheus 结合 MySQL Exporter 进行可视化分析。

慢查询日志的格式

慢查询日志（Slow Query Log）是数据库中用于记录执行时间超过特定阈值的查询的日志。在 MySQL 中，慢查询日志可以帮助你找到性能瓶颈和优化数据库查询。 以下是慢查询日志的一个示例条目和参数详解：

# Time: 190614 10:15:42
# User@Host: root[root] @ localhost []
# Query_time: 0.218000 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1560506142;
SELECT * FROM tbl WHERE col = 'value';

每条日志条目通常包含以下信息：

  • Time: 查询执行的时间戳，格式为 YYMMDD hh:mm:ss。
  • User@Host: 执行查询的用户名和主机名。格式为 user_name[user_host] @ client_host [client_ip]。
  • Query_time: 查询执行的总时间，单位是秒。这是查询实际执行时间，超过 long_query_time 配置的查询会被记录。
  • Schema: SQL执行的schema
  • Last_errno: 错误号。0说明无错误发生。
  • Killed：是否被杀死过，0说明未被杀死过。
  • Query_time：SQL执行时长【单位:秒】
  • Lock_time: 查询在等待表锁的时间，单位是秒。表锁可能会阻塞其他查询。
  • Rows_sent: 查询结果发送给客户端的行数。
  • Rows_examined: 查询检查的行数。这个数字越大，表明查询可能越低效。
  • Rows_affected: 影响行数。
  • SET timestamp: 查询执行时的UNIX时间戳。这个时间戳表示查询实际发生的时间，有助于复现查询环境。
  • Bytes_sent：返回结果大小【单位:Bytes】
  • The actual query: 实际执行的SQL查询语句。

慢查询日志的格式和内容可能因数据库类型（如 MySQL, PostgreSQL 等）和版本的不同而异。在 MySQL 中，你可以通过设置以下参数来配置慢查询日志：

  • slow_query_log: 设置为 1 或 ON 以启用慢查询日志。
  • slow_query_log_file: 指定慢查询日志的文件名。
  • long_query_time: 设置记录慢查询的时间阈值，单位是秒。默认值通常是 10 秒。
  • log_queries_not_using_indexes: 如果设置为 1 或 ON，那么即使它们的执行时间少于 long_query_time，没有使用索引的查询也会被记录。
  • min_examined_row_limit: 设置查询必须检查的最小行数才能被记录到慢查询日志中。

要启用和配置慢查询日志，你通常需要编辑数据库的配置文件（例如，在 MySQL 中是 my.cnf 或 my.ini）并重启数据库服务。

在分析慢查询日志时，应该关注 Query_time、Rows_sent 和 Rows_examined 参数，它们可以帮助你识别低效的查询。通过优化这些查询（比如添加索引、改写查询或调整数据库结构），你可以提高数据库的整体性能。

mysqldumpslow命令实战分析：

要快速分析 MySQL 的慢查询日志文件，你可以使用 MySQL 自带的 mysqldumpslow 命令行工具。此工具可以对慢查询日志进行解析并生成易于阅读的摘要报告。 以下是一些常用的 mysqldumpslow 命令选项及其用法：

  • -s：指定排序的基准。

  • al：平均锁定时间（avg lock time）

  • ar：平均返回记录数（avg rows sent）

  • at：平均查询时间（avg query time）

  • c：计数（count）

  • l：锁定时间（lock time）

  • r：返回记录数（rows sent）

  • t：查询时间（query time）

  • -t：返回前面的 N 条记录。

  • -g：只考虑包含某个字符串的 SQL 语句。

  • -r：反向顺序显示结果。

  • -a：不折叠相似的 SQL 查询。

  • -n：只显示查询时间超过 N 秒的查询。

  • -l：只显示锁定时间超过 N 秒的查询。

  • -i：指定慢查询日志文件的路径。

使用示例：

  1. 显示慢查询日志中所有查询，按平均查询时间排序：

    mysqldumpslow -s at /path/to/slow-query.log

  2. 显示慢查询日志中前 10 条最慢的查询：

    mysqldumpslow -s t -t 10 /path/to/slow-query.log

  3. 显示慢查询日志中，执行时间超过 5 秒的查询：

    mysqldumpslow -s t -t 10 -n 5 /path/to/slow-query.log

  4. 显示慢查询日志中包含特定字符串 "my_table" 的查询：

    mysqldumpslow -s t -g "my_table" /path/to/slow-query.log

  5. 显示慢查询日志中，按照返回记录数排序的查询：

    mysqldumpslow -s r /path/to/slow-query.log

  6. 显示慢查询日志中，按照锁定时间排序的查询，并且不折叠相似的 SQL 查询：

    mysqldumpslow -s l -a /path/to/slow-query.log
特定场景：

  1. 分析特定类型的查询： 如果你想要分析特定类型的查询，比如所有的 SELECT 语句，可以使用 -g 参数进行过滤。

    mysqldumpslow -g '^SELECT' /path/to/slow-query.log

  2. 查找特定数据库的慢查询： 如果你运行多个数据库实例，可能需要针对特定数据库分析慢查询。

    mysqldumpslow -g 'my_database' /path/to/slow-query.log

  3. 查找执行次数最多的慢查询： 有时候，频繁执行的慢查询可能对性能影响更大，即使它们的执行时间不是最长的。

    mysqldumpslow -s c -t 10 /path/to/slow-query.log

  4. 查找锁定时间最长的查询： 锁定时间长的查询可能会导致数据库性能问题，因为它们可能阻塞了其他查询。

    mysqldumpslow -s l -t 10 /path/to/slow-query.log

  5. 查找返回记录行数最多的查询： 返回大量数据的查询可能需要优化，比如添加索引或修改查询逻辑。

    mysqldumpslow -s r -t 10 /path/to/slow-query.log

  6. 组合使用多个参数： 可以组合使用多个参数来获得更精确的分析结果。

    mysqldumpslow -s t -t 5 -n 2 -g 'JOIN' /path/to/slow-query.log

    这个命令会显示执行时间超过 2 秒的前 5 条包含 JOIN 的查询语句。

  7. 查看未折叠的相似查询： 默认情况下，mysqldumpslow 会折叠相似的查询以便于分析。有时候，你可能需要查看所有的查询，而不是折叠后的摘要。

    mysqldumpslow -a /path/to/slow-query.log

  8. 反向排序： 如果你想要看到执行最快或者其他指标最低的查询，可以使用 -r 参数进行反向排序。

    mysqldumpslow -s t -r /path/to/slow-query.log
示例命令：
# 按平均查询时间排序，显示前10条慢查询
mysqldumpslow -s at -t 10 /path/to/your/mysql-slow.log

# 按查询次数排序，显示前10条慢查询
mysqldumpslow -s c -t 10 /path/to/your/mysql-slow.log

# 使用正则表达式过滤查询，这里只显示包含'JOIN'的慢查询
mysqldumpslow -s at -t 10 -g 'JOIN' /path/to/your/mysql-slow.log

mysqldumpslow 会输出查询的归一化形式，即将查询中的常量值替换为占位符，这有助于聚合相似的查询并更好地分析它们的性能。

如果需要更详细的分析，可以使用更高级的工具，如 pt-query-digest，它是 Percona Toolkit 的一部分。pt-query-digest 提供了更多的分析选项和输出格式，可以帮助你深入了解慢查询的性能问题。以下是使用 pt-query-digest 的基本示例：

# 使用 pt-query-digest 分析慢查询日志
pt-query-digest /path/to/your/mysql-slow.log > analyzed_slow_queries.txt

这会将分析结果输出到 analyzed_slow_queries.txt 文件中。你可以通过阅读此文件来了解哪些查询最慢、最频繁以及可能的性能改进点。

记得替换 /path/to/your/mysql-slow.log 为你的慢查询日志文件的实际路径。在使用任何工具之前，请确保你有足够的权限访问慢查询日志文件。

Linux命令实战分析：

在 Linux 系统上，你可以使用一系列标准的命令行工具来进行慢查询日志文件的快速分析。 这些工具包括 grep、awk、sort、uniq、cut 等。以下是一些基本示例，展示了如何使用这些工具来分析慢查询日志。 慢查询日志文件名为 mysql-slow.log。

  1. 计算慢查询的总数:

    grep -c '^# Query_time' mysql-slow.log

  2. 找出执行时间最长的查询:

    grep -A 1 '^# Query_time' mysql-slow.log | grep -v '^# Query_time' | sort -nr | head -n 10

  3. 列出所有执行时间超过特定阈值（例如 5 秒）的查询:

    awk '/^# Query_time: [5-9]/ || /^# Query_time: [1-9][0-9]/ {print $3}' mysql-slow.log

  4. 按执行时间排序查询:

    grep -B 1 '^# Query_time' mysql-slow.log | grep -v '^--' | paste - - | sort -k 3 -nr

  5. 找出执行次数最多的查询:

    awk '/^# User@Host/ {getline; getline; print $0}' mysql-slow.log | sort | uniq -c | sort -nr | head -n 10

  6. 统计每个用户的慢查询次数:

    grep -oP '(?<=# User@Host: ).*(?=\[)' mysql-slow.log | sort | uniq -c | sort -nr

  7. 提取查询时间和锁定时间信息:

    grep '^# Query_time' mysql-slow.log | cut -d' ' -f2-4

  8. 按日期过滤慢查询:

    grep -A 2 '^# Time: 190614' mysql-slow.log

9.查看最近若干行的日志起始时间

 tail -100000 mysql-slow.log |grep Time|head

10.查看某个时间段内的SQL执行情况

tail -100000 mysql-slow.log |grep -A4 '# Time: 2024-06-18T12'

11.对某个时间段内某个特定用户的查询情况按时长排序 AAA 代指

 tail -100000 mysql-slow.log |grep -A4 '# Time: 2024-06-18'|grep -A2 'User@Host: AAA'|grep Query_time|sort -k3rn|head

12.对某个时间段内某个特定用户的查询按返回结果大小排序

AAA 代指

tail -100000 mysql-slow.log |grep -A4 '# Time: 2021-04-22'|grep -A3 'User@Host: AAA'|grep Bytes_sent|sort -k3rn|head
  1. 对某个时间段内某个特定用户的查询情况按返回行数排序 AAA 代指
tail -100000 mysql-slow.log |grep -A4 '# Time: 2021-06-18'|grep -A2 'User@Host: AAA'|grep Query_time|sort -k7rn|head

14.找出最大结果的SQL

tail -1000 mysql-slow.log |grep -B4 -A100 62954

15.找出结果集大小超过8位数字的查询

tail -100000 slow.log |grep -E -A50 -B4 '# Bytes_sent: .{8}'

这些命令提供了不同角度的快速分析方法，但它们都相对简单，不适合复杂或深入的分析。如果你需要进行更详细的分析，可能需要使用专门的分析工具， 如前面提到的 mysqldumpslow 或 pt-query-digest。

请记住，对于大型的慢查询日志文件，这些命令可能需要较长时间才能完成。此外，这些命令的输出可能需要进一步的处理才能得到有用的信息。

总结：

在本文中，系统地介绍了如何有效地设置和利用 MySQL 的慢查询日志来识别数据库中的性能瓶颈。我们探讨了通过 SQL 语句和配置文件启用慢查询日志的步骤，以及如何配置相关参数以捕获关键的性能数据。通过实际示例，我们展示了如何使用 mysqldumpslow 和基本的 Linux 文本处理命令来分析慢查询日志，从而快速定位低效的查询。

最后，值得强调的是，数据库性能优化是一个持续的过程，需要不断监控、分析和调整。慢查询日志是这一过程中的重要工具，它为数据库管理员和开发人员提供了宝贵的信息，帮助他们做出明智的优化决策。通过本文的指导，应该能够更自信地管理和优化 MySQL 数据库，确保它能够在各种负载条件下提供稳定和高效的性能。

转载自:https://juejin.cn/post/7381672213463138355
评论
请登录