作者 |马听老师
来源 |慕课专栏《一线数据库工程师带你深入理解 MySQL》
本文首先会一起讨论一下 SQL 优化,然后分享如何定位慢查询和如何分析 SQl 执行效率,并用一些简单的例子让大家学会这些分析技巧。
在工作中可能会遇到某个新功能在测试时需要很久才返回结果,这时就应该分析是不是慢查询导致的。如果确实有慢查询,又应该怎么去分析 SQL 执行效率呢?这一篇文章我们就来学习怎么找到慢查询和怎么分析 SQL 执行效率。
1 定位慢 SQL
当我们实际工作中,碰到某个功能或者某个接口需要很久才能返回结果,我们就应该去确定是不是慢查询导致的。定位慢 SQL 有如下两种解决方案:
查看慢查询日志确定已经执行完的慢查询
show processlist 查看正在执行的慢查询
我们一起来了解下这两种方法的使用场景和使用技巧吧!
1.1 通过慢查询日志
如果需要定位到慢查询,一般的方法是通过慢查询日志来查询的,MySQL 的慢查询日志用来记录在 MySQL 中响应时间超过参数 long_query_time(单位秒,默认值 10)设置的值并且扫描记录数不小于 min_examined_row_limit(默认值0)的语句,能够帮我们找到执行完的慢查询,方便我们对这些 SQL 进行优化。
知识扩展:
默认情况下,慢查询日志中不会记录管理语句,可通过设置 log_slow_admin_statements = on 让管理语句中的慢查询也会记录到慢查询日志中。
默认情况下,也不会记录查询时间不超过 long_query_time 但是不使用索引的语句,可通过配置log_queries_not_using_indexes = on 让不使用索引的 SQL 都被记录到慢查询日志中(即使查询时间没超过 long_query_time 配置的值)。
如果需要使用慢查询日志,一般分为四步:开启慢查询日志、设置慢查询阀值、确定慢查询日志路径、确定慢查询日志的文件名。下面我们来学习下:
首先开启慢查询日志,由参数 slow_query_log 决定是否开启,在 MySQL 命令行下输入下面的命令:
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 中 long_query_time 的值如何确定呢?
线上业务一般建议把 long_query_time 设置为 1 秒,如果某个业务的 MySQL 要求比较高的 QPS,可设置慢查询为 0.1 秒。发现慢查询及时优化或者提醒开发改写。
一般测试环境建议 long_query_time 设置的阀值比生产环境的小,比如生产环境是 1 秒,则测试环境建议配置成 0.5 秒。便于在测试环境及时发现一些效率低的 SQL。
甚至某些重要业务测试环境 long_query_time 可以设置为 0,以便记录所有语句。并留意慢查询日志的输出,上线前的功能测试完成后,分析慢查询日志每类语句的输出,重点关注 Rows_examined(语句执行期间从存储引擎读取的行数),提前优化。
确定慢查询日志路径
慢查询日志的路径默认是 MySQL 的数据目录
mysql> show global variables like "datadir";
+---------------+------------------------+
| Vari