如何定位并优化慢查询sql(慢查询语句只会是DML,数据操作语言)
这个问题属于开放性的题目,具体场景需要具体分析,这里给个大致思路
- 根据慢日志定位慢查询sql.
- 使用explain等工具分析sql.
- 修改sql或者尽量让sql走索引.
1. 根据慢日志定位慢查询sql
慢日志的作用就是用来记录执行速度比较慢的sql语句.
-
首先我们先看看
慢日志变量
的状态及相关信息- 连接数据库后,输入图中的语句
show VARIABLES LIKE '%quer%'
对变量进行模糊查询,就可以看到慢日志-->slow_query_log,也就是我圈出来的部分.
慢日志默认是关闭的. - 通过图我们可以看到还有一个变量,名为
long_query_time
,其值默认为10秒,这就表示如果执行这个sql语句花费了超过十秒,它就会被记录到慢日志之中.
- 连接数据库后,输入图中的语句
-
然后我们再来看看系统中慢日志的状态,输入语句:SHOW STATUS LIKE '%slow_queries%'进行模糊查询即可看到当前慢查询语句的数量
-
修改慢日志的设置
- 打开慢日志功能: SET GLOBAL slow_query_log = on;
- 去数据库的配置文件
my.ini
中修改慢查询时间为1秒,这样执行超过1秒的语句也会被记录在慢日志中了.
- 打开慢日志功能: SET GLOBAL slow_query_log = on;
2. 使用explain等工具分析sql
explain在分析查询语句很有用,一般放在select关键字前面
explain主要用于描述mysql如何执行查询操作,即mysql的执行计划
.
注意: 添加了explain关键字以后,sql语句并没有真正的执行,只是返回了mysql的执行计划
例如(用的别人的例子):
emmm,我就不去说所有的字段了哈,网上有,我懒得记,等面试问到了我再来看,这里重点说几个字段:
- rows:这一列是mysql估计要读取并检测的行数,
注意这个不是结果集里的行数
- tyoe:它表示mysql找到需要数据行的方式,其性能依次从最优到最差分别为:system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
其中index和ALL表示走的是全表扫描
当我们发现如果某条语句是慢查询语句**或者**type值为index/all
,那么就说明这条语句是可以优化的. - extra:写的是一些额外信息,辅助我们了解该语句的执行,它的字段也非常的多,这里写两个很有可能需要被优化的字段,如图:
3. 修改sql或者尽量让sql走索引
这是当我们出现慢查询语句的时候的两种可选的解决办法,可以给查询的字段加索引或者使用其他索引查询