MySQL之性能分析（一）

梦里梦外; 2023-09-29 09:00 9阅读 0赞

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAamF2YeaZtOWkqei_h-WQjg_size_20_color_FFFFFF_t_70_g_se_x_16][]

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

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

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

![format_png][]

![format_png 1][]

查看系统参数的命令是`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'`，如下图：

![format_png 2][]

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

![format_png 3][]

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

#### 2.定位执行慢的SQL ####

1.开启慢查询日志

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

![format_png 4][]

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

![format_png 5][]

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

![format_png 6][]

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

![format_png 7][]

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

![format_png 8][]

翻译过来就是：

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。

![format_png 9][]

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

> \#得到返回记录集最多的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

[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAamF2YeaZtOWkqei_h-WQjg_size_20_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/783cb3fd361946df85cdd0494dc0bb72.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAamF2YeaZtOWkqei_h-WQjg==,size_20,color_FFFFFF,t_70,g_se,x_16
[format_png]: https://img-blog.csdnimg.cn/img_convert/8ed1af7ab49833ff7e37543bdd33a79a.webp?x-oss-process=image/format,png
[format_png 1]: https://img-blog.csdnimg.cn/img_convert/76b16fa88972be1794916ee49ab7ede6.webp?x-oss-process=image/format,png
[format_png 2]: https://img-blog.csdnimg.cn/img_convert/e2a5d241a4fb39f4cd1e8cd2113b25b5.webp?x-oss-process=image/format,png
[format_png 3]: https://img-blog.csdnimg.cn/img_convert/986e410ce72403a0c0eb8e486e0341f5.webp?x-oss-process=image/format,png
[format_png 4]: https://img-blog.csdnimg.cn/img_convert/e45f091a007440e74d05c482e2b0311b.webp?x-oss-process=image/format,png
[format_png 5]: https://img-blog.csdnimg.cn/img_convert/ca972a164c7f3d04c83f1ce7f4255216.webp?x-oss-process=image/format,png
[format_png 6]: https://img-blog.csdnimg.cn/img_convert/48c2b5ffd99001d6d5f0b6bd5fa62fdc.webp?x-oss-process=image/format,png
[format_png 7]: https://img-blog.csdnimg.cn/img_convert/6ae14de8d1d761f6cf949517da592cd7.webp?x-oss-process=image/format,png
[format_png 8]: https://img-blog.csdnimg.cn/img_convert/0510ee0a60f377e2f8d36d72194efcca.webp?x-oss-process=image/format,png
[format_png 9]: https://img-blog.csdnimg.cn/img_convert/0ab47dc5be67fe6356ed937faedb2f67.webp?x-oss-process=image/format,png