|
|
51CTO旗下网站
|
|
移动端

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

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

作者:架构师的修炼之路来源:今日头条|2020-06-05 09:21

一条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 配置的值)。

慢查询日志使用步骤:

使用慢查询日志,一般分为四步:

开启慢查询日志。

设置慢查询阀值。

确定慢查询日志路径。

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

开启慢查询日志(默认是关闭的):

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

设置慢查询时间限制(查询时间只要大于这个值都将记录到慢查询日志中,单位:秒):

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

确定慢查询日志路径:

  1. mysql> show global variables like "datadir"

确定慢查询日志文件名:

  1. 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 运行就可以了。

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

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

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

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

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

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

Explain字段详解(重点关注加粗项):

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

这几列重点解读:

1. select_type重点解读

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

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

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

3. extra重点解读

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

总结

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

本节知识点总结如下:

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

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

【编辑推荐】

  1. MySQL DBA如何利用strace/pstack/gdb来定位问题
  2. MySQL常用函数,程序员真得看看
  3. MySQL中,当update修改数据与原数据相同时会再次执行吗?
  4. MySQL的死锁系列之锁的类型以及加锁原理
  5. MySQL 可重复读,差点就让我背上了一个 P0 事故!
【责任编辑:庞桂玉 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

订阅专栏+更多

实操案例:Jenkins持续交付和持续部署

实操案例:Jenkins持续交付和持续部署

微服务架构下的自动化部署
共18章 | freshman411

142人订阅学习

思科交换网络安全指南

思科交换网络安全指南

安全才能无忧
共5章 | 思科小牛

95人订阅学习

云计算从入门到上瘾

云计算从入门到上瘾

传统IT工程师的转型
共26章 | 51CTO阿森

250人订阅学习

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊

51CTO服务号

51CTO官微