MySQL(66)如何进行慢查询日志分析？

慢查询日志分析是数据库优化的重要步骤，能够帮助我们识别和优化执行时间较长的查询，从而提升数据库的性能。以下是如何进行 MySQL 的慢查询日志分析的详细步骤和代码示例。

1. 启用慢查询日志

首先，我们需要确保 MySQL 的慢查询日志功能是启用的。

1.1 修改 MySQL 配置文件

编辑 MySQL 配置文件 my.cnf 或 my.ini，添加或修改以下配置项：

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1  # 设置慢查询的阈值，单位是秒
log_queries_not_using_indexes = 1  # 记录不使用索引的查询

1.2 重启 MySQL 服务

完成配置后，重启 MySQL 服务以使配置生效：

sudo systemctl restart mysql

1.3 验证慢查询日志配置

进入 MySQL 命令行工具，执行以下命令以检查配置是否生效：

SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'slow_query_log_file';
SHOW VARIABLES LIKE 'long_query_time';

2. 查看慢查询日志

慢查询日志的默认位置通常是 /var/log/mysql/mysql-slow.log。可以使用以下命令查看慢查询日志：

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

3. 使用 mysqldumpslow 工具分析慢查询日志

MySQL 提供了一个名为 mysqldumpslow 的工具，用于分析慢查询日志。mysqldumpslow 可以对日志进行汇总和排序，找出最慢的查询。

3.1 基本用法

以下是一些常用的 mysqldumpslow 命令：

• 显示前 10 个最慢的查询：

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

• 按查询时间排序：

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

• 按查询次数排序：

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

• 按锁定时间排序：

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

• 按返回行数排序：

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

3.2 示例输出

以下是 mysqldumpslow 的示例输出：

Reading mysql slow query log from /var/log/mysql/mysql-slow.log
Count: 5  Time=2.00s (10s)  Lock=0.00s (0s)  Rows=10.0 (50), root[root]@localhost
  SELECT * FROM employees WHERE emp_no = 'S'

Count: 3  Time=1.00s (3s)  Lock=0.00s (0s)  Rows=1.0 (3), user[user]@example.com
  UPDATE salaries SET salary = N WHERE emp_no = N

4. 使用第三方工具进行慢查询分析

除了 mysqldumpslow，还有一些第三方工具可以帮助进行更深入的慢查询日志分析，如 Percona Toolkit 中的 pt-query-digest。

4.1 安装 Percona Toolkit

首先安装 Percona Toolkit：

sudo apt-get install percona-toolkit  # 对于 Debian/Ubuntu 系统
sudo yum install percona-toolkit  # 对于 RedHat/CentOS 系统

4.2 使用 pt-query-digest 分析慢查询日志

使用 pt-query-digest 分析慢查询日志：

pt-query-digest /var/log/mysql/mysql-slow.log > slow_query_report.txt

4.3 示例输出

以下是 pt-query-digest 的示例输出：

# 180ms user time, 60ms system time, 29.00M rss, 2.66G vsz
# Current date: Thu Dec 16 12:34:56 2021
# Hostname: server1
# Files: /var/log/mysql/mysql-slow.log
# Overall: 2 total, 1 unique, 0 QPS, 0x concurrency ________________
# Time range: 2021-12-16 12:34:56 to 2021-12-16 12:34:58
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ ===  =======  =======  =======  =======  =======  =======  =======
# Exec time     99     0.10s    0.05s    0.05s    0.05s    0.05s       0.00ms
# Lock time      0       0s       0s       0s       0s       0s       0.00ms
# Rows sent     50      100       50       50       50       50       0.00
# Rows examine  50      100       50       50       50       50       0.00
# Query size    90     1.00k     1.00k     1.00k     1.00k     1.00k       0.00
# InnoDB:
# IO r bytes    50  100.00k  50.00k  50.00k  50.00k  50.00k       0.00
# IO r wait     50   100.00   50.00   50.00   50.00   50.00       0.00

# Profile
# Rank Query ID           Response time Calls   R/Call V/M Item
# ==== ================== ============= ======= ====== ============
#    1 0x123456789ABCDEF   0.1000       2       0.1000 SELECT employees

5. 优化慢查询

通过慢查询日志分析，确定需要优化的查询。常见的优化方法包括：

• 添加索引：确保查询涉及的列有合适的索引。
• 优化查询语句：重写复杂的查询语句，使其执行效率更高。
• 分区表：对于大表，可以考虑使用表分区。
• 调整数据库参数：优化 MySQL 配置参数，如 innodb_buffer_pool_size 等。

5.1 示例优化

假设我们发现以下查询速度较慢：

SELECT * FROM employees WHERE emp_no = '10001';

此查询没有使用索引，我们可以为 emp_no 列添加索引：

CREATE INDEX idx_emp_no ON employees(emp_no);

小结

通过启用和分析慢查询日志，可以识别和优化性能较差的查询，从而提高数据库整体性能。使用 MySQL 自带的工具 mysqldumpslow 或第三方工具 pt-query-digest，可以更高效地分析慢查询日志，进而进行针对性的优化。