定位并优化慢查询SQL.png
为什么要学习定位并优化慢查询 SQL
日常开发中,在数据量比较小的表中,SQL 的执行效率可能没什么问题,但是随着表数据量的增加,慢 SQL 可能就会慢慢浮现,因此学习如何定位并优化慢 SQL 非常有必要
慢 SQL 是什么
一条 SQL 从提交给 MySQL 到最终执行出结果的耗时超过了我们预先配置的一个时间阀值,那么这条 SQL 就是慢 SQL;简单的说就是,我们预先会在 MySQL 上配置一个超时时间,如果 SQL 执行的时间超过这个值,那么这个 SQL 就是慢 SQL
如何定位慢 SQL
在定位慢 SQL 之前,我们需要先开启我们的慢日志记录
执行 show variables like "%query%";,结果如下图,我们首先需要设置 slow_query_log为 ON,表示开启慢日志记录.
其中long_query_time代表慢查询的时间限制,如果一条SQL执行的时间超过10s,则会被记录到慢日志中。
其中慢日志记录的地址是slow_query_log_file的值。
image.png
配置方式如下.
set global slow_query_log = on;
set long_query_time=1;
执行结果如下:
image.png
准备数据
我这里准备了含有100万条数据的 test 表
表结果如下
image.png
执行一个相对耗时的SQL
select * from test order by c2 desc;
执行耗时1.413秒,已经超过了我们设置的慢 SQL 阀值 1 秒
image.png
查询慢日志
查看````slow_query_log_file```变量对应的地址,查看该日志文件
sudo vim [slow_query_log_file变量对应的值]
慢日志
上图的慢日志解析
从 22 行开始,是我们刚刚执行的那条 SQL 的慢日志
1. 23 行的 # Time: 2020-06-30T14:01:35.231434Z 代表了 SQL 执行完的时间点,从格式来看是 UTC时间
我们可以通过如下命令查询show variables like "%log_timestamps%";查看默认的系统配置,可以发现确实是UTC时间
image.png
2. 24行的# User@Host: root[root] @ localhost [127.0.0.1] Id: 8.
其中
User@Host: root[root]代表了客户端的账户信息,第一个为授权账户,第二个为登陆账户
localhost [127.0.0.1] 代表客户端 IP 地址
Id: 8代表mysql的线程ID
3. 25行的# Query_time: 1.313886 Lock_time: 0.000104 Rows_sent: 1000000 Rows_examined: 2000000
其中Query_time代表查询的耗时,Lock_time代表锁持有时长,
Rows_sent代表返回客户端的行数,Rows_examined代表优化器扫描的行数.我们可以通过减少优化器扫描的行数提升 SQL 性能。
如何优化慢 SQL
通过上面的步骤,我们已经可以定位到慢查询 SQL 了,那如何优化呢?
利用 explain 分析慢 SQL
在分析查询性能的时候,explain非常管用,这个关键字一般放在 select 语句前面,用于描述 MySQL 如何执行查询操作,以及 MySQL 成功返回结果集需要执行的行数,Explain可以帮助我们分析 select语句,让我们知道查询效率低下的原因,从而改进我们的查询,让查询优化器能更好的工作。
那我们就用 explain 分析一下我们刚才的慢 SQL .
explain select * from test order by c2 desc;
执行结果如下图,
extra中出现以下2项意味着MySQL根本不能使用索引,效率会受到重大影响。应尽可能对其优化:
extra 项为Using filesort 时,表示MySQL会对结果使用一个外部索引排序,而不是从表里按索引次序读到相关内容。 可能在内存或磁盘上进行排序。MySQL无法利用索引完成的排序操作称为“文件排序”。【用不到任何索引进行排序】
extra项为Using temporary时,表示MySQL在对查询结果排序时使用临时表。常见于排序order by 和分组查询group by。【将排序的一些结果存储到一张临时表中,方便后面做各种查询使用】,关于 explain执行后的各个指标参数,后续单独在一篇文章中解析
这里只简单介绍一下:
id代表执行编号,select_type代表本行是简单还是复杂查询,table代表的是访问的哪张表,type代表的是访问类型,这里的all是最坏的一种情况,代表全表扫描,possible_keys表示哪一些索引有利于高效的查找,key代表mysql决定采用哪个索引来优化查询,key_len代表显示mysql在索引里使用的字节数,ref代表之前的表在key列记录的索引中查找值所用的列或常量,rows代表为了找到所需的行而需要读取的行数,估算值,不精确,filtered代表按表条件筛选的行的百分比,Extra代表额外信息
image.png
修改 SQL 或者尽量让 SQL 走索引,进而提升它的效率
提升效率的小技巧
我们在使用查询语句时可以在后面加上force index(索引名),来查看其执行时间,从而选择具体的索引。
select count(c2) from test force index(PRIMARY);
select * from test force index(PRIMARY) order by c2 desc ;
总结
配置 MySQL 记录慢日志的方式
1. 配置了 slow_query_log和long_query_time,当 SQL Query_time小于 long_query_time时,该 SQL 的执行就会进入慢日志
2. 当我们设置了系统变量log_queries_not_using_indexes为 ON ,这个系统变量开启后,会将那些未使用索引的 SQL 也被记录到慢查询日志中,另外,full index scan 的 SQL 也会被记录到慢查询日志。所以,当满足这些条件的 SQL ,即使 Query_time 时间小于 long_query_time 的值,也会被记录到慢查询日志