如何使用 MySQL 查询分析

介绍

在尝试分析数据库驱动应用程序的整体性能时，MySQL 查询分析是一种有用的技术。在开发中型或大型应用程序时，大型代码库中往往会分布着数百个查询，并且每秒可能会对数据库运行大量查询。如果没有某种查询分析技术，就很难确定瓶颈和应用程序速度变慢的位置和原因。本文将使用 MySQL 服务器内置的工具演示一些有用的查询分析技术。

什么是 MySQL 慢查询日志？

MySQL 慢查询日志是 MySQL 发送慢速、可能有问题的查询的日志。此日志记录功能随 MySQL 一起提供，但默认情况下处于关闭状态。记录哪些查询由可自定义的服务器变量决定，这些变量允许根据应用程序的性能要求进行查询分析。通常，记录的查询是执行时间超过指定时间的查询或未正确命中索引的查询。

设置分析变量

设置 MySQL 慢查询日志的主要服务器变量是：

slow_query_log			G
slow_query_log_file			G
long_query_time			G / S
log_queries_not_using_indexes	G
min_examined_row_limit		G / S

注意：（G）全局变量，（S）会话变量

slow_query_log - 用于打开或关闭慢查询日志的布尔值。

slow_query_log_file - 查询日志文件的绝对路径。该文件的目录应归 mysqld 用户所有，并具有读取和写入的正确权限。mysql 守护进程可能会以“mysql”的身份运行，但要验证，请在 Linux 终端中运行以下命令：

 ps -ef | grep bin/mysqld | cut -d' ' -f1

输出可能会显示当前用户以及 mysqld 用户。设置目录路径 /var/log/mysql 的示例：

cd /var/log
mkdir mysql
chmod 755 mysql
chown mysql:mysql mysql

long_query_time - 检查查询长度的时间（以秒为单位）。如果值为 5，则将记录执行时间超过 5 秒的任何查询。

log_queries_not_using_indexes - 布尔值，表示是否记录未命中索引的查询。在进行查询分析时，记录未命中索引的查询非常重要。

min_examined_row_limit - 设置应检查的行数的下限。值为 1000 时将忽略分析少于 1000 行的任何查询。

MySQL 服务器变量可以在 MySQL conf 文件中设置，也可以通过 MySQL GUI 或 MySQL 命令行动态设置。如果变量是在 conf 文件中设置的，则它们将在服务器重新启动时保留，但也需要服务器重新启动才能生效。MySQL conf 文件通常位于 `/etc 或 /usr`，通常是 `/etc/my.cnf` 或 `/etc/mysql/my.cnf`。要查找 conf 文件（可能需要将搜索范围扩大到更多根目录）：

find /etc -name my.cnf
find /usr -name my.cnf

找到 conf 文件后，只需在 [mysqld] 标题下附加所需的值：

[mysqld]
….
slow-query-log = 1
slow-query-log-file = /var/log/mysql/localhost-slow.log
long_query_time = 1
log-queries-not-using-indexes

再次强调，更改直到服务器重启后才会生效，因此如果需要立即进行更改，则动态设置变量：

mysql> SET GLOBAL slow_query_log = 'ON';
mysql> SET GLOBAL slow_query_log_file = '/var/log/mysql/localhost-slow.log';
mysql> SET GLOBAL log_queries_not_using_indexes = 'ON';
mysql> SET SESSION long_query_time = 1;
mysql> SET SESSION min_examined_row_limit = 100;

检查变量值：

mysql> SHOW GLOBAL VARIABLES LIKE 'slow_query_log';
mysql> SHOW SESSION VARIABLES LIKE 'long_query_time';

动态设置 MySQL 变量的一个缺点是服务器重启后变量会丢失。建议将需要保留的任何重要变量添加到 MySQL conf 文件中。

注意：通过 SET 动态设置变量并将其放入 conf 文件的语法略有不同，例如 `slow_query_log` 与 `slow-query-log`。查看 MySQL 的动态系统变量页面以了解不同的语法。选项文件格式是 conf 文件的格式，系统变量名是动态设置变量的变量名。

生成查询配置文件数据

既然已经概述了 MySQL 慢查询日志配置，现在是时候生成一些查询数据进行分析了。此示例是在正在运行的 MySQL 实例上编写的，没有设置任何先前的慢日志配置。示例的查询可以通过 MySQL GUI 或通过 MySQL 命令提示符运行。在监控慢查询日志时，打开两个到服务器的连接窗口很有用：一个连接用于编写 MySQL 语句，一个连接用于查看查询日志。

在 MySQL 控制台选项卡中，以具有 SUPER ADMIN 权限的用户身份登录 MySQL 服务器。首先，创建一个测试数据库和表，添加一些虚拟数据，然后打开慢速查询日志。此示例应在开发环境中运行，最好不要有其他使用 MySQL 的应用程序，以避免在监视查询日志时造成混乱：

$> mysql -u  -p

mysql> CREATE DATABASE profile_sampling;
mysql> USE profile_sampling;
mysql> CREATE TABLE users ( id TINYINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255) );
mysql> INSERT INTO users (name) VALUES ('Walter'),('Skyler'),('Jesse'),('Hank'),('Walter Jr.'),('Marie'),('Saul'),('Gustavo'),('Hector'),('Mike');

mysql> SET GLOBAL slow_query_log = 1;
mysql> SET GLOBAL slow_query_log_file = '/var/log/mysql/localhost-slow.log';
mysql> SET GLOBAL log_queries_not_using_indexes = 1;
mysql> SET long_query_time = 10;
mysql> SET min_examined_row_limit = 0;

现在有一个测试数据库和表，其中包含少量测试数据。慢查询日志已打开，但查询时间故意设置得很高，并且最小行检查标志保持关闭。在用于查看日志的控制台选项卡中：

cd /var/log/mysql
ls -l

文件夹中应该还没有慢查询日志，因为尚未运行任何查询。如果有，则意味着过去已打开并配置了慢查询日志，这可能会歪曲此示例的某些结果。返回 MySQL 选项卡，运行以下 SQL：

mysql> USE profile_sampling;
mysql> SELECT * FROM users WHERE id = 1;

执行的查询是使用表中的主键索引进行的简单选择。此查询速度很快并且使用了索引，因此慢速查询日志中不会有此查询的条目。回顾查询日志目录并验证未创建日志。现在回到 MySQL 窗口运行：

mysql> SELECT * FROM users WHERE name = 'Jesse';

此查询在非索引列（名称）上运行。此时，日志中将出现一个查询，其中包含以下信息（可能不完全相同）：

/var/log/mysql/localhost-slow.log

# Time: 140322 13:54:58
# User@Host: root[root] @ localhost []
# Query_time: 0.000303  Lock_time: 0.000090 Rows_sent: 1  Rows_examined: 10
use profile_sampling;
SET timestamp=1395521698;
SELECT * FROM users WHERE name = 'Jesse';

查询已成功记录。再举一个例子。提高最小检查行限制并运行类似的查询：

mysql> SET min_examined_row_limit = 100;
mysql> SELECT * FROM users WHERE name = 'Walter';

由于未分析至少 100 行，因此不会将任何数据添加到日志中。

注意：如果日志中没有填充任何数据，则需要检查以下几点。首先，检查创建日志的目录的权限。所有者/组应与 mysqld 用户相同（例如，参见上文），并且具有正确的权限，确保 chmod 755。其次，可能存在干扰示例的现有慢查询变量配置。通过从 conf 文件中删除任何慢查询变量并重新启动服务器来重置默认值，或者将全局变量动态设置回其默认值。如果更改是动态进行的，请注销并重新登录 MySQL，以确保全局更新生效。

分析查询配置文件信息

查看上面示例中的查询配置文件数据：

# Time: 140322 13:54:58
# User@Host: root[root] @ localhost []
# Query_time: 0.000303  Lock_time: 0.000090 Rows_sent: 1  Rows_examined: 10
use profile_sampling;
SET timestamp=1395521698;
SELECT * FROM users WHERE name = 'Jesse';

该条目显示：

• 查询运行的时间
• 谁运行了它
• 查询花费了多长时间
• 锁的长度
• 返回了多少行
• 检查了多少行

这很有用，因为任何违反服务器变量指定的性能要求的查询都会被记录在日志中。这样，开发人员或管理员就可以让 MySQL 在查询性能不如预期时向他们发出警报（而不是通读源代码并试图找到编写不当的查询）。此外，查询分析数据在一段时间内进行分析时也很有用，这有助于确定哪些情况导致了应用程序性能不佳。

使用 mysqldumpslow

在一个更现实的例子中，将在数据库驱动的应用程序上启用分析，提供适度的数据流进行分析。日志将不断被写入，可能比任何人观察的都更频繁。随着日志大小的增长，解析所有数据变得困难，有问题的查询很容易在日志中丢失。MySQL 提供了另一个工具 mysqldumpslow，它通过分解慢查询日志来帮助避免此问题。二进制文件与 MySQL 捆绑在一起（在 Linux 上），因此要使用它，只需运行命令并传入日志路径：

mysqldumpslow -t 5 -s at /var/log/mysql/localhost-slow.log

可以使用各种参数与命令一起帮助自定义输出。在上面的示例中，将显示按平均查询时间排序的前 5 个查询。结果行更易读，并且按查询分组（此输出与示例不同，以展示高值）：

Count: 2  Time=68.34s (136s)  Lock=0.00s (0s)  Rows=39892974.5 (79785949), root[root]@localhost
  SELECT PL.pl_title, P.page_title
  FROM page P
  INNER JOIN pagelinks PL
  ON PL.pl_namespace = P.page_namespace
  WHERE P.page_namespace = N
…

正在显示的数据：

• 计数 - 查询已被记录的次数
• 时间 - （）中的平均时间和总时间
• Lock - 表锁定时间
• 行数 - 返回的行数

该命令抽象了数字和字符串，因此具有不同 WHERE 子句的相同查询将被视为相同查询（请注意 page_namespace = N）。使用 mysqldumpslow 之类的工具可以避免不断查看慢速查询日志，而是允许定期或自动检查。mysqldumpslow 命令的参数允许进行一些复杂的表达式匹配，这有助于深入了解日志中的各种查询。

还有第三方日志分析工具可以提供不同的数据视图，其中流行的一个是pt-query-digest。

查询细分

最后要注意的一个分析工具是允许对查询进行复杂分解的工具。该工具的一个很好的用例是从慢查询日志中抓取有问题的查询并直接在 MySQL 中运行它。首先必须打开分析，然后运行查询：

mysql> SET SESSION profiling = 1;
mysql> USE profile_sampling;
mysql> SELECT * FROM users WHERE name = 'Jesse';
mysql> SHOW PROFILES;

开启分析后，SHOW PROFILES 将显示一个将 Query_ID 链接到 SQL 语句的表。找到与所运行查询对应的 Query_ID，然后运行以下查询（将 # 替换为您的 Query_ID）：

mysql> SELECT * FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=#;

示例输出：

STATE 是执行查询过程中的“步骤”，DURATION 是完成该步骤所用的时间（以秒为单位）。这不是一个非常有用的工具，但它很有趣，可以帮助确定查询执行的哪个部分导致了最大的延迟。

有关各个列的详细概述： http://dev.mysql.com/doc/refman/5.5/en/profiling-table.html

有关各个“步骤”的详细概述： http://dev.mysql.com/doc/refman/5.5/en/general-thread-states.html

注意：此工具不应在生产环境中使用，而应用于分析特定查询。

慢速查询日志性能

最后要解决的问题是慢查询日志将如何影响性能。通常，在生产环境中运行慢查询日志是安全的；CPU 和 I/O 负载都不是问题¹ ²。但是，应该有一些策略来监视日志大小，以确保日志文件大小不会变得太大而无法容纳文件系统。此外，在生产环境中运行慢查询日志时，一个好的经验法则是将 long_query_time 保留为 1s 或更高。

重要提示：在生产、高负载环境中，使用分析工具 SET profiling=1 或记录所有查询（即 general_log 变量）并不是一个好主意。

结论

慢查询日志对于找出有问题的查询和分析整体查询性能非常有帮助。使用慢查询日志进行查询分析时，开发人员可以深入了解应用程序的 MySQL 查询的执行情况。使用 mysqldumpslow 等工具，监控和评估慢查询日志变得易于管理，并且可以轻松纳入开发过程。既然已经确定了有问题的查询，下一步就是调整查询以获得最佳性能。