MySQL优化：定位慢查询的两种方法以及使用explain分析SQL

MySQL优化：定位慢查询的两种方法以及使用explain分析SQL

一条SQL查询语句在经过MySQL查询优化器处理后会生成一个所谓的执行计划，这个执行计划展示了具体执行查询的方式，比如多表连接的顺序是什么，对于每个表采用什么访问方法来具体执行查询等等。

本章的内容就是为了帮助大家看懂EXPLAIN语句的各个输出项都是干嘛使的，从而可以有针对性的提升我们查询语句的性能。

学习步骤

定位慢查询。使用explain分析。

定位慢查询SQL

在平时工作中，我想你肯定遇到过一条sql发出去了，但是等了好久才出现了返回值，这不仅仅影响了测试速度也大大降低了开发效率。所以我们有必要学习sql慢查询定位。

一般定位慢查询会有两种解决方案：

根据慢查询日志定位

使用show processlist定位，查询正在执行的慢查询

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重点解读

总结

今天我分享的关于定位慢 SQL 及使用 explain 分析慢 SQL 到这里就结束了。

本节知识点总结如下：

学习了两种慢查询定位方法。掌握了explain关键列的含义以及使用方法，这也是工作中最常用的方法。在工作中及面试时，SQL 性能优化都是我们经常遇到的问题，要想做好性能优化，我们必须学会使用 SQL 优化时需要的工具，进行定位和分析。

由于篇幅的问题，本小节只介绍了 explain 工具的使用，在下节将补充另外两种分析慢查询的工具：show profile 和 trace。在后面我会再讲解 SQL 优化的一些知识点，相信小伙伴们 SQL 性能优化时一定可以越来越熟练。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。