介绍
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配置文件中设置或通过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文件中。
注 ：通过设置动态设置变量，并将它们放置在配置文件的语法略有不同，例如`slow_query_log`与`慢查询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 …
正在显示的数据：
- 计数 - 已记录查询的次数
- 时间 - ()中的平均时间和总时间
- 锁 - 表锁定时间
- Rows - 返回的行数
该命令抽象数字和字符串，因此具有不同WHERE子句的相同查询将被计为同一查询（注意page_namespace = N）。 拥有像mysqldumpslow这样的工具可以防止持续观察慢查询日志，而允许定期或自动检查。 mysqldumpslow命令的参数允许一些复杂的表达式匹配，帮助深入到日志中的各种查询。
也有提供不同的数据视图，最常见的一个是可用的第三方日志分析工具PT-查询摘要 。
查询细目
要知道的最后一个分析工具是允许查询的复杂分解的工具。 该工具的一个很好的用例是从慢查询日志中获取有问题的查询，并直接在MySQL中运行它。 首先必须打开profiling，然后查询被运行：
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=#;
样品输出：
| SEQ | 州 | DURATION |
|---|---|---|
| 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或更高。
重要提示：这是不使用的分析工具，设置分析= 1，也没有记录所有的查询，即general_log变量，在生产，工作量大的环境是一个好主意 。
结论
缓慢的查询日志非常有助于挑出有问题的查询和分析整体查询性能。 当使用慢查询日志进行查询分析时，开发人员可以深入了解应用程序的MySQL查询的执行情况。 使用诸如mysqldumpslow之类的工具，监视和评估慢查询日志变得易于管理，并且可以轻松地并入开发过程。 现在已经识别出有问题的查询，下一步是调整查询以获得最佳性能。
