MySQL中怎么定位慢查询

本篇文章给大家分享的是有关MySQL中怎么定位慢查询，小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着小编一起来看看吧。

NO.1 慢查询日志定位解析

MySQL 的慢查询日志记录的内容是：在 MySQL 中响应时间超过参数 long_query_time（单位秒，默认值 10）设置的值并且扫描记录数不小于 min_examined_row_limit（默认值0）的语句。

NOTE：默认情况下，慢查询日志中不会记录管理语句，如果需要记录的请做如下设置，设置log_slow_admin_statements  = on 让管理语句中的慢查询也会记录到慢查询日志中。默认情况下，也不会记录查询时间不超过 long_query_time  但是不使用索引的语句，可通过配置
log_queries_not_using_indexes = on 让不使用索引的 SQL 都被记录到慢查询日志中（即使查询时间没超过 long_query_time 配置的值）。

慢查询日志使用步骤：

使用慢查询日志，一般分为四步：

开启慢查询日志。

设置慢查询阀值。

确定慢查询日志路径。

确定慢查询日志的文件名。

开启慢查询日志（默认是关闭的）：

mysql> set global slow_query_log = on;  Query OK, 0 rows affected (0.00 sec)

设置慢查询时间限制（查询时间只要大于这个值都将记录到慢查询日志中，单位：秒）：

mysql> set global long_query_time = 1;  Query OK, 0 rows affected (0.00 sec)

确定慢查询日志路径：

mysql> show global variables like "datadir";

确定慢查询日志文件名：

mysql> show global variables like "slow_query_log_file";

NOTE：慢查询query  time设置小技巧：线上业务一般建议把 long_query_time 设置为 1 秒，如果某个业务的 MySQL 要求比较高的  QPS，可设置慢查询为 0.1 秒。发现慢查询及时优化或者提醒开发改写。一般测试环境建议 long_query_time  设置的阀值比生产环境的小，比如生产环境是 1 秒，则测试环境建议配置成 0.5 秒。便于在测试环境及时发现一些效率低的  SQL。甚至某些重要业务测试环境 long_query_time 可以设置为  0，以便记录所有语句。并留意慢查询日志的输出，上线前的功能测试完成后，分析慢查询日志每类语句的输出，重点关注  Rows_examined（语句执行期间从存储引擎读取的行数），提前优化。

接下来在确定慢查询日志后可以通过：tail -n5
/data/mysql/mysql-slow.log 命令查看

这里对上方的执行结果详细描述一下：

tail -n5：只查看慢查询文件的最后5行

Time：慢查询发生的时间

User@Host：客户端用户和IP

Query_time：查询时间

Lock_time：等待表锁的时间

Rows_sent：语句返回的行数

Rows_examined：语句执行期间从存储引擎扫描的行数

上面这种方式是用系统自带的慢查询日志查看的，如果觉得系统自带的慢查询日志不方便查看，小伙伴们可以使用 pt-query-digest 或者 mysqldumpslow 等工具对慢查询日志进行分析，这不是本节重点，不演示了。

通过 show processlist定位慢查询

有时慢查询正在执行，已经导致数据库负载偏高了，而由于慢查询还没执行完，因此慢查询日志还看不到任何语句。此时可以使用  show processlist 命令判断正在执行的慢查询。show processlist 显示哪些线程正在运行。如果有 PROCESS  权限，则可以看到所有线程。否则，只能看到当前会话的线程。

知识扩展：如果不使用 FULL 关键字，在 info 字段中只显示每个语句的前 100 个字符，如果想看语句的全部内容可以使用 full 修饰（show full processlist）。

这里对上面结果重点参数解释一下：

Time：表示执行时间

Info：表示 SQL 语句

我们这里可以通过它的执行时间（Time）来判断是否是慢 SQL。

EXLPAIN分析慢查询

分析  SQL 执行效率是优化 SQL 的重要手段，通过上面讲的两种方法，定位到慢查询语句后，我们就要开始分析 SQL  执行效率了，子曾经曰过：“工欲善其事，必先利其器”，我们可以通过 explain、show profile 和 trace  等诊断工具来分析慢查询。本节先讲解 explain 的使用，在下节将分享 show profile 和 trace 的使用。

Explain 可以获取 MySQL 中 SQL 语句的执行计划，比如语句是否使用了关联查询、是否使用了索引、扫描行数等。可以帮我们选择更好地索引和写出更优的 SQL 。使用方法：在查询语句前面加上 explain 运行就可以了。

创建一个测试表并且插入部分数据用于测试

在上图表中我们创建了3个索引

PRIMARY KEY (`id`), 聚集索引 KEY `idx_a` (`a`),非聚集索引 KEY `idx_b_c` (`b`,`c`)非聚集索引 d列没有创建索引

执行三个SQL分别得到如下结果

Explain字段详解（重点关注加粗项）：

这几列重点解读：

1. select_type重点解读

2. type重点解读：查询性能从上到下依次是最好到最差

3. extra重点解读

以上就是MySQL中怎么定位慢查询，小编相信有部分知识点可能是我们日常工作会见到或用到的。希望你能通过这篇文章学到更多知识。更多详情敬请关注亿速云行业资讯频道。