如何快速定位对数据库性能消耗高的慢语句

前言：

MySQL中提供了一个慢查询的日志记录功能，可以把查询SQL语句时间大于多少秒的语句写入慢查询日志，日常维护中可以通过慢查询日志的记录信息快速准确地判断问题所在。如果出现数据库cpu消耗高，一般都是慢语句导致，所以第一时间我们一定要拿到数据库实例主机上面的slow_query的日志文件进行定位分析。

开启慢语句配置

/etc/my.cnf 配置 slow query

#开启慢查询
slow_query_log=on   
#超过2秒的查询就视为慢查询
long_query_time=2
#记录日志
slow_query_log_file=/data/mysql/slow.log

分析方法

因为slow_query的日志文件一般比较大，记录数几十万甚至上百万，涉及sql记录至少上万，甚至更多，所以我们需要使用一些技巧进行快速定位。

日志文件涉及敏感信息，不方便贴上来，这里只列出一个例子：

# Time: 2021-11-23T17:31:11.931559+08:00
# User@Host: dbproxy_0ad87[dbproxy_0ad87] @  [***.***.***.***]  Id: 11834321
# Query_time: 24.580089  Lock_time: 0.000143 Rows_sent: 287918  Rows_examined: 863769
SET timestamp=1637659871;
select R.BULLETIN_RCV_OBJ_REL_ID AS R_BULLETIN_RCV_OBJ_REL_ID,
        R.BULLETIN_ID AS R_BULLETIN_ID,
        R.RCV_TYPE AS R_RCV_TYPE,
        R.RCV_ID AS R_RCV_ID,
        R.STATUS_CD AS R_STATUS_CD,
        R.CREATE_STAFF AS R_CREATE_STAFF,
        R.UPDATE_STAFF AS R_UPDATE_STAFF,
        R.CREATE_DATE AS R_CREATE_DATE,
        R.STATUS_DATE AS R_STATUS_DATE,
        R.UPDATE_DATE AS R_UPDATE_DATE,
        R.REMARK AS R_REMARK,
        R.IS_AUTO AS R_IS_AUTO,
        R.BULLETIN_ID AS B_BULLETIN_ID,
        B.BULLETIN_TITLE AS B_BULLETIN_TITLE,
        B.BULLETIN_CONTENT AS B_BULLETIN_CONTENT,
        B.BULLETIN_TYPE AS B_BULLETIN_TYPE,
        BT.BULLETIN_TYPE_NAME AS B_BULLETIN_TYPE_NAME,
        B.BULLETIN_LEVEL AS B_BULLETIN_LEVEL,
        B.IS_TOP AS B_IS_TOP,
        B.LAUNCH_STAFF AS B_LAUNCH_STAFF,
        B.LAUNCH_ORG AS B_LAUNCH_ORG,
        B.EFF_DATE AS B_EFF_DATE,
        B.EXP_DATE AS B_EXP_DATE,
        B.STATUS_CD AS B_STATUS_CD,
        B.CREATE_STAFF AS B_CREATE_STAFF,
        B.UPDATE_STAFF AS B_UPDATE_STAFF,
        B.UPDATE_DATE AS B_UPDATE_DATE,
        B.STATUS_DATE AS B_STATUS_DATE,
        B.CREATE_DATE AS B_CREATE_DATE,
        B.REMARK AS B_REMARK,
        S.STAFF_NAME AS B_STAFF_NAME,
        O.ORG_NAME AS B_ORG_NAME
        
        from bulletin b left join bulletin_type bt on
        bt.bulletin_type = b.bulletin_type
        left join staff s on b.LAUNCH_STAFF = s.staff_id
        left join organization o on b.LAUNCH_ORG = o.org_id
        join
        bulletin_rcv_obj_rel r on b.bulletin_id = r.bulletin_id
        WHERE 1=1
         
         
        and r.rcv_type = '1200'
         
        and r.is_auto = '1'
        and b.STATUS_CD = '1000'
        order by r.status_cd, b.eff_Date desc;

slow_query文件信息解读

#Time: 是sql执行时间
#User@Host:是sql执行所连接过来的客户端IP，telDB展示的是dbproxy的ip
#Query_time: 查询耗时 Lock_time: 锁时间 Rows_sent: 返回客户端行数 Rows_examined: 扫描行数

根据日志里面就可以发现很重要的一个指标Query_time，这个越大，消耗肯定越大。
那能否将所有语句的Query_time拎出来，并且做降序排序，找到数值大的哪些，肯定就能定位出具体sql了。
如果通过notepad++等文本编辑器打开查找慢语句效率比较低，可以通过下面这个脚本来快速找到慢语句的位置。

cat 日志文件 | grep Query_time | awk -F "Query_time: " '{print $2}' | awk -F "  Lock_time" '{print $1+0}' | sort -n

● 将slow_query文件随便上传到一个linux环境，比如上传到某个应用容器中。
● 在上传后的文件目录下执行这个命令
● 立即得到一份按照Query_time进行排序的结果

● 找到最大的值再查询文件中的语句即可
查询时注意，因为sort命令做了四舍五入，所以只需要拿小数点前的整数，前面拼上来进行查询
例如：Query_time: 24，注意Query_time: 后面有空格

上面截图的这条语句便是问题语句。