如何使用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 文件中。

NOTE：通过SET动态设置变量并放入conf文件的语法略有不同，例如 `slow_query_log` 对比 `慢查询日志`。 查看 MySQL' 的 动态系统变量 页面了解不同的语法。 Option-File Format 是 conf 文件的格式，System Variable Name 是动态设置变量的变量名。

生成查询配置文件数据

现在已经概述了 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 命令的参数允许一些复杂的表达式匹配，这有助于深入了解日志中的各种查询。

还有 3rd 方日志分析工具可提供不同的数据视图，一种流行的工具是 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=#;

样本输出：

序列	状态	期间
1	开始	0.000046
2	检查权限	0.000005
3	开桌	0.000036
...	...	...

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 之类的工具，监控和评估慢查询日志变得易于管理，并且可以轻松地融入到开发过程中。 现在已经确定了有问题的查询，下一步是调整查询以获得最佳性能。

文章提交者：Jesse Cascio