MySQL运维与监控之：`MySQL`的`Slow Query Log`：如何分析慢查询日志并进行调优。

好的，下面是一篇关于MySQL慢查询日志分析与调优的技术文章，以讲座模式呈现。

MySQL运维与监控之：MySQL的Slow Query Log – 如何分析慢查询日志并进行调优

大家好，今天我们来聊聊MySQL运维中一个非常重要的环节：慢查询日志的分析与优化。慢查询是影响MySQL性能的关键因素之一，定位并优化慢查询是DBA日常工作中必不可少的一部分。本次讲座将从慢查询日志的配置、分析以及优化策略三个方面展开，希望能帮助大家更好地理解和应用慢查询日志。

一、慢查询日志的配置

首先，我们需要开启和配置慢查询日志。慢查询日志默认是关闭的，我们需要手动开启。

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 = 10  # 慢查询阈值，单位秒
  log_output = FILE #日志输出类型，可选FILE, TABLE, NONE

  修改完成后，需要重启MySQL服务使配置生效。

• 动态配置 (无需重启MySQL服务):

  通过MySQL客户端执行SQL命令：

  SET GLOBAL slow_query_log = 'ON';
  SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';
  SET GLOBAL long_query_time = 10;
  SET GLOBAL log_output = 'FILE';

  这种方式的修改只在当前MySQL服务运行期间有效，重启后会失效。 如果需要永久生效，仍然需要修改配置文件。

2. 参数详解:

• slow_query_log: 开启或关闭慢查询日志。1 或 ON 表示开启，0 或 OFF 表示关闭。
• slow_query_log_file: 慢查询日志文件的路径。建议指定一个专门的目录来存放慢查询日志。
• long_query_time: 定义慢查询的阈值，单位为秒。 超过这个时间的查询会被记录到慢查询日志中。 默认值通常为10秒。 根据实际情况，可以调整这个值。例如，对于高并发的系统，可以将其设置为1秒甚至更小。
• log_output: 定义日志输出类型，可选FILE, TABLE, NONE。FILE表示输出到文件，TABLE表示输出到mysql.slow_log表。建议使用FILE，因为将日志写入表会增加数据库的负担。从MySQL 5.6开始，log_output可以设置为FILE,TABLE，同时输出到文件和表。
• log_queries_not_using_indexes: 是否记录没有使用索引的查询。 默认值为OFF。 开启这个选项可能会产生大量的慢查询日志，但对于发现潜在的索引问题很有帮助。开启方法：SET GLOBAL log_queries_not_using_indexes = ON;
• min_examined_row_limit: 只有当查询扫描的行数超过这个值时，才会将查询记录到慢查询日志中。 默认值为0，表示记录所有慢查询。 可以根据实际情况调整这个值，减少慢查询日志的数量。开启方法：SET GLOBAL min_examined_row_limit = 1000; (示例：扫描行数超过1000才记录)

3. 查看配置:

可以使用以下SQL命令查看当前的慢查询日志配置：

SHOW VARIABLES LIKE '%slow_query_log%';
SHOW VARIABLES LIKE '%long_query_time%';
SHOW VARIABLES LIKE '%log_output%';
SHOW VARIABLES LIKE '%log_queries_not_using_indexes%';
SHOW VARIABLES LIKE '%min_examined_row_limit%';

4. 日志轮转:

慢查询日志会不断增长，需要定期进行轮转，避免占用过多的磁盘空间。 可以使用 mysqladmin flush-logs 命令手动轮转日志。 也可以通过配置 logrotate 工具来实现自动轮转。

示例logrotate配置 ( /etc/logrotate.d/mysql-slow ):

/var/log/mysql/mysql-slow.log {
    daily
    rotate 7
    missingok
    notifempty
    compress
    delaycompress
    sharedscripts
    postrotate
        /usr/bin/mysqladmin -u root -p'your_password' flush-logs
    endscript
}

二、慢查询日志的分析

开启慢查询日志后，我们就可以开始分析日志文件，找出需要优化的SQL语句。

1. 日志格式:

慢查询日志是一种文本文件，包含了详细的查询信息。 每条慢查询日志通常包含以下内容：

• 时间戳： 查询执行的时间。
• 用户信息： 执行查询的用户和主机。
• 查询时间： 查询执行的总时间。
• 锁等待时间： 查询等待锁的时间。
• 返回行数： 查询返回的行数。
• 扫描行数： 查询扫描的行数。
• SQL语句： 执行的SQL语句。

一个典型的慢查询日志条目如下：

# Time: 2023-10-27T10:00:00.123456Z
# User@Host: root[root] @ localhost []  Id:     5
# Query_time: 15.234567  Lock_time: 0.000123 Rows_sent: 10  Rows_examined: 1000000
SET timestamp=1698391200;
SELECT * FROM users WHERE name LIKE '%keyword%';

2. 分析工具:

• mysqldumpslow: MySQL自带的慢查询日志分析工具。 可以对慢查询日志进行排序、过滤和统计，生成易于阅读的报告。

  常用命令：

  • mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log: 按照查询时间排序，显示前10条最慢的查询。
  • mysqldumpslow -s c -t 10 /var/log/mysql/mysql-slow.log: 按照查询次数排序，显示前10条执行次数最多的查询。
  • mysqldumpslow -g "keyword" /var/log/mysql/mysql-slow.log: 过滤包含 "keyword" 的查询。
  • mysqldumpslow -a /var/log/mysql/mysql-slow.log: 不将数字和字符串规范化，显示原始SQL语句。

  示例：

  mysqldumpslow -s t -t 5 /var/log/mysql/mysql-slow.log
  
  Top 5 of 10 slow queries:
  Count: 1  Time=15.23s (15s)  Lock=0.00s (0s)  Rows=10 (10), root[root]@localhost
    SELECT * FROM users WHERE name LIKE '%keyword%'

• pt-query-digest: Percona Toolkit 中的慢查询日志分析工具。 功能比 mysqldumpslow 更强大，可以提供更详细的分析报告。

  安装:

  # Debian/Ubuntu
  apt-get install percona-toolkit
  
  # CentOS/RHEL
  yum install percona-toolkit

  常用命令：

  • pt-query-digest /var/log/mysql/mysql-slow.log: 生成慢查询日志的详细分析报告。 报告会包含查询的统计信息、执行计划等。 可以将报告输出到文件：pt-query-digest /var/log/mysql/mysql-slow.log > slow_query_report.txt

  示例：

  pt-query-digest /var/log/mysql/mysql-slow.log
  
  # Overall: 1 total, 1 unique
  # Attribute          total     min     max     avg     95%  stddev  median   
  # ============     ======= ======= ======= ======= ======= ======= =======
  # Exec time           15s      15s      15s      15s      15s      0s      15s
  # Lock time           1ms      1ms      1ms      1ms      1ms      0s      1ms
  # Rows sent            10      10      10      10      10      0s      10
  # Rows examined   1000000 1000000 1000000 1000000 1000000      0s 1000000
  # Query size           62      62      62      62      62      0s      62
  
  # Profile
  # Rank Query ID           Response time Calls R/Call Apdx V/M   Item
  # ==== ================== ============= ===== ====== ==== === === ==========
  #    1 0x4A123...         15.2346 100.0%     1   15.2s  0.0  0.00 SELECT * FROM users
  
  # Query 1: 0.00 QPS, 0.00x concurrency, ID 0x4A123... at byte 397
  # This item is included in the report because it matches --limit.
  # Scores: V/M = 0.00
  # Time range: 2023-10-27 10:00:00 to 2023-10-27 10:00:00
  # Attribute    pct   total     min     max     avg     95%  stddev  median
  # ============ === ======= ======= ======= ======= ======= ======= =======
  # Count       100       1
  # Exec time   100      15s      15s      15s      15s      15s      0s      15s
  # Lock time   100      1ms      1ms      1ms      1ms      1ms      0s      1ms
  # Rows sent   100      10      10      10      10      10      0s      10
  # Rows examined 100 1000000 1000000 1000000 1000000 1000000      0s 1000000
  # Query size  100      62      62      62      62      62      0s      62
  # String:
  # Databases    test
  # Hosts        localhost
  # Users        root
  # Query_time distribution
  #   1us
  #  10us
  # 100us
  #   1ms
  #  10ms
  # 100ms
  #   1s  ################################################################
  #  10s+
  # Tables
  #   SHOW TABLE STATUS FROM `test` LIKE 'users'G
  # EXPLAIN /*!50100 SELECT* FROM `users` WHERE name LIKE '%keyword%' */
  # SELECT * FROM users WHERE name LIKE '%keyword%'

• 可视化工具: 一些数据库管理工具提供了慢查询日志的可视化界面，例如 phpMyAdmin、Navicat 等。 这些工具可以更直观地展示慢查询的信息，方便分析。

3. 分析步骤:

1. 收集慢查询日志: 从慢查询日志文件中收集数据。
2. 使用分析工具: 使用 mysqldumpslow 或 pt-query-digest 等工具分析慢查询日志，找出执行时间长、执行次数多的SQL语句。
3. 分析SQL语句: 仔细分析这些SQL语句，找出性能瓶颈。 例如，是否缺少索引、是否使用了不合理的查询方式等。
4. 优化SQL语句: 根据分析结果，对SQL语句进行优化。 例如，添加索引、重写SQL语句等。
5. 验证优化效果: 优化完成后，再次执行SQL语句，检查性能是否有所提升。
6. 重复以上步骤: 持续监控和优化慢查询，保持数据库的良好性能。

三、慢查询的调优策略

找到慢查询后，接下来就是优化它们。 优化策略可以从以下几个方面入手：

1. 索引优化:

• 添加索引: 这是最常见的优化手段。 对于经常出现在 WHERE 子句、JOIN 子句、ORDER BY 子句中的列，应该考虑添加索引。

  示例：

  -- 假设 users 表的 name 列经常用于查询
  CREATE INDEX idx_users_name ON users (name);

• 复合索引: 如果查询条件包含多个列，可以考虑创建复合索引。 复合索引的顺序很重要，应该将选择性最高的列放在最前面。

  示例：

  -- 假设 users 表的 name 和 age 列经常一起用于查询
  CREATE INDEX idx_users_name_age ON users (name, age);

• 前缀索引: 对于 TEXT 或 VARCHAR 类型的列，可以考虑使用前缀索引。 前缀索引只需要索引列的一部分字符，可以减小索引的大小，提高查询效率。

  示例：

  -- 假设 users 表的 address 列很长，只需要索引前10个字符
  CREATE INDEX idx_users_address ON users (address(10));

• 覆盖索引: 如果查询只需要访问索引中的列，而不需要访问表中的数据，就可以使用覆盖索引。 覆盖索引可以减少I/O操作，提高查询效率。

  示例：

  -- 假设经常需要查询 users 表的 name 和 age 列
  CREATE INDEX idx_users_name_age ON users (name, age);
  
  -- 查询语句只需要访问索引中的列
  SELECT name, age FROM users WHERE name = 'John';

• 索引失效: 需要注意一些情况会导致索引失效，例如：

  • 使用 LIKE '%keyword%' 进行模糊查询。
  • 在索引列上进行函数运算。
  • 使用 OR 连接多个查询条件，且不是所有条件都使用索引。
  • 隐式类型转换。

2. SQL语句优化:

• *避免使用 `SELECT `:** 只选择需要的列，减少I/O操作。

• *使用 EXISTS 代替 `COUNT():** 如果只需要判断是否存在满足条件的记录，可以使用EXISTS代替COUNT()。EXISTS在找到满足条件的记录后就会停止查询，而COUNT()` 需要扫描整个表。

  示例：

  -- 使用 EXISTS
  SELECT 1 FROM users WHERE name = 'John'; -- 慢
  
  -- 使用 EXISTS
  SELECT 1 FROM users WHERE EXISTS (SELECT 1 FROM users WHERE name = 'John'); -- 快

• 优化 JOIN 查询: 选择合适的 JOIN 类型，并确保 JOIN 列上存在索引。

  • INNER JOIN: 返回两个表中都存在的记录。
  • LEFT JOIN: 返回左表的所有记录，以及右表中与左表匹配的记录。
  • RIGHT JOIN: 返回右表的所有记录，以及左表中与右表匹配的记录。

• 减少子查询: 尽量将子查询转换为 JOIN 查询。

• 批量操作: 将多个小的SQL语句合并成一个大的SQL语句，减少网络开销。 例如，使用 INSERT INTO ... VALUES (...), (...), ... 批量插入数据。

• 避免在 WHERE 子句中使用函数或表达式: 这会导致索引失效。 可以将函数或表达式移动到 SELECT 子句中。

3. 数据库结构优化:

• 范式化: 将数据分解成多个表，减少数据冗余，提高数据一致性。

• 反范式化: 在某些情况下，为了提高查询效率，可以适当增加数据冗余。

• 分区表: 将大表分割成多个小表，提高查询效率。 可以按照时间、范围等进行分区。

  示例：

  -- 按照年份创建分区表
  CREATE TABLE orders (
      id INT PRIMARY KEY,
      order_date DATE,
      amount DECIMAL(10, 2)
  )
  PARTITION BY RANGE (YEAR(order_date)) (
      PARTITION p2020 VALUES LESS THAN (2021),
      PARTITION p2021 VALUES LESS THAN (2022),
      PARTITION p2022 VALUES LESS THAN (2023),
      PARTITION p2023 VALUES LESS THAN (2024)
  );

• 读写分离: 将读操作和写操作分配到不同的数据库服务器上，提高数据库的并发能力。

• 缓存: 使用缓存技术，例如 Memcached、Redis 等，将热点数据缓存起来，减少数据库的访问压力。

4. 硬件优化:

• 增加内存: 增加内存可以提高数据库的缓存命中率，减少I/O操作。
• 使用SSD: 使用SSD可以显著提高I/O性能。
• 优化磁盘I/O: 将数据文件和日志文件放在不同的磁盘上，减少磁盘I/O的竞争。
• 升级CPU: 升级CPU可以提高数据库的计算能力。

5. 其他优化手段:

• 调整MySQL配置参数: 根据实际情况调整MySQL的配置参数，例如 innodb_buffer_pool_size、key_buffer_size 等。
• 使用连接池: 使用连接池可以减少数据库连接的开销。
• 代码层面优化: 检查代码是否存在循环查询，是否有可以批量处理的地方。

优化是一个迭代的过程，需要不断地监控和调整。 每次优化后，都需要验证优化效果，并根据实际情况进行调整。

四、案例分析

假设我们通过慢查询日志发现以下SQL语句执行时间很长：

SELECT * FROM orders WHERE customer_id = 12345 AND order_date BETWEEN '2023-01-01' AND '2023-10-26';

分析：

1. customer_id 和 order_date 都是查询条件，可能缺少索引。
2. SELECT * 选择了所有列，可能存在不必要的I/O操作。

优化：

1. 添加索引:

   CREATE INDEX idx_orders_customer_id_order_date ON orders (customer_id, order_date);

2. 只选择需要的列:

   SELECT order_id, order_date, amount FROM orders WHERE customer_id = 12345 AND order_date BETWEEN '2023-01-01' AND '2023-10-26';

验证：

执行优化后的SQL语句，观察执行时间是否有所缩短。 可以使用 EXPLAIN 命令查看SQL语句的执行计划，确认是否使用了索引。

五、一些总结性的想法

慢查询日志是MySQL性能优化的重要工具，通过配置和分析慢查询日志，我们可以快速定位性能瓶颈，并采取相应的优化措施。 优化策略包括索引优化、SQL语句优化、数据库结构优化、硬件优化等。 优化是一个持续的过程，需要不断地监控和调整。 希望今天的讲座能帮助大家更好地理解和应用慢查询日志，提高MySQL的性能。