什么是慢SQL?
根据字面意思,慢是指这条SQL语句执行时间过长。那么执行时间究竟多长才能定义成慢呢?这就与MySQL中的两个变量有关:slow_query_log、long_query_time。
(1)slow_query_log:这个变量代表是否开启慢查询日志,开启则会将所有慢查询SQL记录到mysql-slow.log文件中,值为1则表示开启,0表示关闭(默认是关闭的,需要手动打开)。
查看slow_query_log的初始值:
通过修改/etc/my.cnf文件开启慢日志记录并指定执行时间超过1s即是慢SQL:
重启mysql服务,再次查看slow_query_log的值:
(2)long_query_time:这个变量代表查询SQL执行时间的“底线”,执行时间超过这个预设值就会定义为慢SQL并被记录到mysql-slow.log文件中(单位是s)。
如何定位慢SQL?
准备好一张数据量足够大的测试表,博主这里准备的数据量是3百万。
(1)查看记录慢SQL的日志文件:cat localhost-slow.log
此时只有一些关于MySQL的基本信息。
(2)使用SQL访问数据库,并用tail命令实时追踪文件的更改:tail -f localhost-slow.log
在左边终端执行了一条查询SQL:select * from user_log where id > 10000,最终执行时间为2.9s。在右边终端可以看到慢日志写入了这条慢SQL的信息,包括执行时间、SQL语句、由哪台主机执行等信息。至此,完成了慢SQL的定位。
如何分析一条SQL的执行计划?
找到了慢SQL语句还不够,我们的最终目的是消除慢SQL语句。这就需要我们对慢SQL语句的执行计划进行分析,因为多数情况下,SQL语句是可以优化的,SQL执行时间过久有可能是因为没有走索引,而是全表扫描;又或者是因为索引列上没有结果需要的数据,要回表查询。
(1)使用explain命令查看慢SQL的执行计划:explain select * from user_log where id > 10000;
(2)我们需要关注的关键字段有:type、key、rows、Extra。
type:索引类型。这里的type为range,意思是在索引列上使用了范围查询(between、in、<、>、<=、>=)。type按照性能由好到坏为:system(只有一条数据的系统表)、const(一次就找到数据的唯一索引)、eq_ref(唯一索引)、ref(非唯一索引)、range、index(扫描索引列的全部数据)、all(全表扫描)。
key:在执行SQL时真正使用到了的索引。这里的key为PRIMARY,表示使用了主键索引(主键索引在创建表时基于主键创建)
rows:MySQL预计找到满足条件的记录所需要读取的行数(越少性能越好)。这里的rows值大概是150万,性能较差。
Extra:关于SQL的额外优化建议。这里的Extra为Using where,表示查询使用了where查询条件,常见的有:Using index(查询使用了索引,需要的数据都在索引列中,无需回表查询)、Using where、Using index condition(查询使用了索引,但需要回表查询数据)、Using filesort(排序没有使用索引排序)。
以上是本文的全部内容,若有错误的地方,请指正。