MySQL之性能分析（一）

对于工作中MySQL的数据量一旦达到300w左右就需要进行一些优化策略了，比如增加索引，参数调优，但是有的时候即便有索引SQL查询还是很慢，这个时候我们应该怎么去排查问题呢？到底是索引失效还是别的原因，如果是索引失效我们应该怎么排查索引失效的原因？如果不是索引失效，并且使用到了索引但SQL还是很慢那又应该如何排查？如何解决？

下面我们先来看下大致的流程。

观察MySQL是否存在周期性的卡顿或者请求量时多时少？如果是我们尝试在业务端加上缓存，如果加上缓存无法解决，那我们开启SQL慢查询记录，将执行速度慢的SQL记录下来，然后使用执行计划命令explain分析，根据explain的结果查看是否有使用到索引，是否索引失效，如果有那我们应该加上索引或者解决索引失效的问题，是否多表连接inner join过多，如果是多表连接过多，我们可以尝试将一次查询拆分开成多次查询，然后在业务端进行合并，如果是有explain分析发现SQL等待的时间太长，我们可以尝试MySQL的参数调优，比如将buffer pool加大，如果还没解决那么是不是数据压力过大，达到了MySQL的性能瓶颈，那我们可以考虑架构上的一些问题，比如做MySQL集群多节点，主从复制，读写分离，分库分表（水平拆分，垂直拆分）。如下图

查看系统参数的命令是show [global | session] status like 'param' ， 一些常用的性能参数如下：

1. Connections：连接MySQL服务器的次数。
2. Uptime：MySQL服务器的上线时间。
3. Slow_queries：慢查询的次数。
4. Innodb_rows_read：Select查询返回的行数 。
5. Innodb_rows_inserted：执行INSERT操作插入的行数 。
6. Innodb_rows_updated：执行UPDATE操作更新的行数 。
7. Innodb_rows_deleted：执行DELETE操作删除的行数 。
8. Com_select：查询操作的次数。
9. Com_insert：插入操作的次数。对于批量插入的 INSERT 操作，只累加一次。
10. Com_update：更新操作的次数。 • Com_delete：删除操作的次数。

1.统计SQL的查询成本

下面我们先来看下SQL的执行成本，参数是last_query_cost,这个参数记录的是最后一条SQL执行占用的资源（内存页）。 当前有student表数据500w，起始id：5000000，执行如下SQL：

select * from student where id = 6001000；

然后查询 SQL查询成本show status like 'last_query_cost'，如下图：

接下来我们在执行如下SQL查询1001条记录： select * from student where id between 6000000 and 6001000；

但是实际上我们可以看到执行时间基本上没有差别，但是在SQL执行成本上确多了400倍，也就是查询一条记录只用了一个数据页，而1001条记录使用了400页，此类命令适合比较不同SQL的执行成本，尤其是在不同SQL相同结果然后选择哪一个SQL的情况下可以使用此命令查看查询成本来选择SQL。

2.定位执行慢的SQL

1.开启慢查询日志

首先查看MySQL是否开启慢查询show variables like 'slow_query_log';

如果没开启就使用set global slow_query_log = 1;当前参数是全局的，所以必须加上global， 查看慢查询日志文件位置show variables like 'slow_query_log_file';

查看当前被记录为慢SQL的时间阈值：show variables like 'long_query_time';可以看到当前阈值为1s，如下图。如果想要修改则使用set global long_query_time = 10;这样子就将阈值设置为10s了。

查看当前系统有多少慢查询数量show status like 'slow_queries';

我们使用慢查询日志分析工具mysqldumpslow，查看下帮助文档mysqldumpslow --help

翻译过来就是：

1. -s: 是表示按照何种方式排序：
   1. c: 访问次数
   2. l: 锁定时间
   3. r: 返回记录
   4. t: 查询时间
   5. al: 平均锁定时间
   6. ar: 平均返回记录数
   7. at: 平均查询时间 （默认方式）
   8. ac: 平均查询次数
2. -t: 即为返回前面多少条的数据；
3. -g: 后边搭配一个正则匹配模式，大小写不敏感的；

我们主要是查询时间，所以使用mysqldumpslow -s -t 5 /data/mysql/mysql-slow.log,工具查询时间查看前五条的慢SQL。

工作中常用的命令类型（仅供参考）：

#得到返回记录集最多的10个SQL

mysqldumpslow -s r -t 10 /data/mysql/mysql-slow.log

#得到访问次数最多的10个SQL

mysqldumpslow -s c -t 10 /data/mysql/mysql-slow.log

#得到按照时间排序的前10条里面含有左连接的查询语句

mysqldumpslow -s t -t 10 -g "left join" /data/mysql/mysql-slow.log

#另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现爆屏情况

mysqldumpslow -s r -t 10 /data/mysql/mysql-slow.log | more

关闭慢查询日志记录，建议平时关闭慢查询日志，因为在开启的时候会监听我们的SQL执行，占用MySQL服务器的资源 使用set global slow_query_log = 0临时性关闭，永久关闭可以去mysql的配置文件将slow_query_log=OFF注释。

作者：别给我加香菜
链接：https://juejin.cn/post/7083023192612143112