性能实战分析--数据库慢SQL

一、确保数据库的慢查询日志开启状态

1、查询数据库的慢查询日志等开启状态

show variables LIKE 'slow_query_log' 状态是否为ON
show variables LIKE 'long_query_time' 慢查询时间的阈值，例如：1，是指1s以上的查询属于慢查询

如果不是开启状态，需要修改配置文件，例如我使用的容器安装的mysql,在etc/mysql/下面找到 my.cnf就是我的数据库的配置文件，增加/修改2行配置如下：

 slow_query_log=ON
 long_query_time=1

备注：1、有些配置文件名字叫mysql.cnf或者mysqld.cnf，就是多找几个目录，查看下是否有配置信息即可确认

小技巧：有些容器里不带有vim等命令，可以复制出来填写后再复制进去：

docker cp 容器名称:容器地址/文件名 目标地址
docker cp centos7_mysql：/etc/mysql/my.cnf $PWD

修改后，记得重启容器 docker restart centos7_mysql

2、通过数据库工具修改：

SET GLOBAL slow_query_log=ON; 
SET GLOBAL long_query_time=1
SHOW VARIABLES LIKE 'slow%'

3、慢查询日志地址：

show variables LIKE 'slow_query_log_file'

获取到地址为：/var/lib/mysql/文件夹下面

二、性能压测

1、进行性能压测，设置50个并发，阶梯加压

可以看到响应时间非常慢：

tps也是非常小：

三、性能分析

1、首先使用top命令查询下基本指标的情况：

从top命令中分析到：

1）系统负载高，load average ：1分钟前到现在平均负载6.31 ，

2）us用户态占比很高 98.7 ，可能存在 用户程序计算问题，包括密集型计算、资源等待（线程池等）

3）si软中断明显增高，说明cpu抢资源，资源不够用，判断为I/O的问题，需要排查内存 和 I/O;

​ 结合第4、5行的内存信息，初步判断I/O的问题

4）进程中，mysqld占用cpu 178.4%，初步判断主要是数据库I/O问题

2、进一步确认是否I/O问题：vmstat

可以看到：

1）r增高，说明在运行队列中等待的进程数增高

2）io下面的bo相对比较高，读取较高，cs换入换出，说明有大量的磁盘操作

进一步明确cpu使用率高主要是由于I/O的等待造成的，应检查系统中正在进行I/O操作的进程

3、进一步明确是哪个应用，pidstat 1

结论：mysqld进程占用cpu到了100%

最终确认是由于数据库的读取操作导致的响应时间过高和tps不高的原因

4、查看慢查询日志

截取其中部分日志都类似

select account0_.id as id1_0_, account0_.age as age2_0_, account0_.createtime as createti3_0_, account0_.email as email4_0_, account0_.gqid as gqid5_0_, account0_.identity as identity6_0_, account0_.lasttime as lasttime7_0_, account0_.mobile as mobile8_0_, account0_.money as money9_0_, account0_.password as passwor10_0_, account0_.pay_pwd as pay11_0_, account0_.platform as platfor12_0_, account0_.pmoney as pmoney13_0_, account0_.sex as sex14_0_, account0_.token as token15_0_, account0_.username as usernam16_0_ from kyj.cb_account account0_ where account0_.mobile='13899436368';

四、性能优化

分析慢sql：explain + 慢sql

explain select account0_.id as id1_0_, account0_.age as age2_0_, account0_.createtime as createti3_0_, account0_.email as email4_0_, account0_.gqid as gqid5_0_, account0_.identity as identity6_0_, account0_.lasttime as lasttime7_0_, account0_.mobile as mobile8_0_, account0_.money as money9_0_, account0_.password as passwor10_0_, account0_.pay_pwd as pay11_0_, account0_.platform as platfor12_0_, account0_.pmoney as pmoney13_0_, account0_.sex as sex14_0_, account0_.token as token15_0_, account0_.username as usernam16_0_ from kyj.cb_account account0_ where account0_.mobile='13899436368';

发现：

1)type为ALL，全表扫描，是性能效率最低的

2）select_type,查询类型为SIMPLE

3）rows为198913行

可以通过增加一个索引值进行查询，且索引的值选择 account0_.mobile 即可

1）右键设计表，进入索引，栏位选择：mobile

再次性能压测查看结果：

响应时间平均在80ms以下，tps达到最高700左右，比原来提升非常大。