MYSQL进阶-查询优化- 实战 STATUS

- 日理万妓 2023-10-14 16:38 11阅读 0赞

回城传送–》[《100天精通MYSQL从入门到就业》][100_MYSQL]

**文末有送书活动，可以参加！**

#### 文章目录 ####

*  一、练习题目
 *  二、SQL思路
 *   *  SQL进阶-查询优化- SHOW STATUS
     *   *  初始化数据
         *  解法
         *   *  SHOW STATUS是什么
             *  实战经验：常用的mysql状态查询
             *   *  1、QPS(每秒处理的请求数量)
                 *   *  计算思路：
                     *  Questions和 Queries的区别
                 *  2、TPS(每秒处理的事务数量)
                 *   *  计算思路：
                 *  3、key Buffer 命中率
                 *  4、InnoDB Buffer命中率
                 *  5、query cache命中率
                 *  6 、table\_cache（table\_open\_cache） 命中率
                 *  6、thread cache命中率
                 *   *  优化
                 *  7、tmp table相关状况分析
                 *  8、binlog cache
                 *  9、innodb\_log\_waits
                 *  10、锁状态
 *  三、总结
 *  四、参考
 *  五、粉丝福利
 *   *  福利0
     *  福利1
     *  福利2

## 一、练习题目 ##

<table> 
 <thead> 
  <tr> 
   <th>题目链接</th> 
   <th>难度</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><a href="https://edu.csdn.net/skill/practice/mysql-3574b2e5c9ca475789d9d582d7726906/29551?language=mysql02&amp;materialId=80923" rel="nofollow">SQL进阶-查询优化- SHOW STATUS</a></td> 
   <td>★★★☆☆</td> 
  </tr> 
 </tbody> 
</table>

## 二、SQL思路 ##

### SQL进阶-查询优化- SHOW STATUS ###

![在这里插入图片描述][736279e6ef72463793eefccad7a66f2b.png]

#### 初始化数据 ####

这里写入初始化表结构，初始化数据的sql

#### 解法 ####

##### SHOW STATUS是什么 #####

**SHOW STATUS** 能获取mysql服务的一些状态,这些状态是mysql服务的性能参数！  
语法：

SHOW [SESSION | GLOBAL] STATUS

SESSION表示获取当前会话级别的性能参数，GLOBAL表示获取全局级别的性能参数，并且SESSION和GLOBAL可以省略，如果省略不写，默认为SESSION。  
![b][]  
![在这里插入图片描述][667225e950dc4a05b7d9ae1cb0b1eb19.png]  
参数很多，所以在查找指定参数时，可用以下语法：

SHOW [SESSION | GLOBAL] STATUS LIKE 'status_name';

其中**status\_name** 为状态的参数名称。

##### 实战经验：常用的mysql状态查询 #####

###### 1、QPS(每秒处理的请求数量) ######

QPS是mysql每秒所执行的sql数量，但不仅限于select 、instert、update和delete语句。

QPS = Questions(or Queries) / seconds

show global status like 'Question%';

![在这里插入图片描述][fa101a6a768348f582a9d7f9660f43e9.png]

show global status like 'uptime_since_flush_status';

![在这里插入图片描述][63cf5bf34249407b8f3181168799d9c5.png]

###### 计算思路： ######

先从**show global status like ‘Question%’;** 得到Question1的数据，经过一段时间后，再计算**show global status like ‘Question%’;** ，得到Question2的数据。

QPS = （Question2-Question1）/（uptime\_since\_flush\_status2-uptime\_since\_flush\_status1）

###### Questions和 Queries的区别 ######

![在这里插入图片描述][a11fd1f49bb14b339b3802fd92253df4.png]  
因为 Queries 计数统计的更多，所以理论上 Queries 计数总是大于等于 Questions 计数。

> Queries会比Questions 多统计存储过程语句和预准备语句。

###### 2、TPS(每秒处理的事务数量) ######

TPS指的是每秒处理的事务数量，不包含select语句。

TPS = (Com\_commit + Com\_rollback) / seconds

show global status like 'Com_commit';
    
    show global status like 'Com_rollback';

字段说明：

> Com\_commit:事务提交数  
> Com\_rollback：事务回滚数

**对于回滚操作非常频繁的数据库，可能意味着应用编写存在问题。**

![在这里插入图片描述][1cef4bf804c1498aa3187d88a75f006c.png]  
![在这里插入图片描述][158799d98cf84aec8e21cf4c2f8b6330.png]

###### 计算思路： ######

先得到\*\* (Com\_commit + Com\_rollback) \*\* 的值commit\_rollback1，然后经过一段时间后，再得到\*\* (Com\_commit + Com\_rollback) \*\* 的值commit\_rollback2。  
TPS = (commit\_rollback2-commit\_rollback1)/（uptime\_since\_flush\_status2-uptime\_since\_flush\_status1）;

###### 3、key Buffer 命中率 ######

key buffer 命中率代表了Myisam类型表的索引cache命中率，命中率的大小直接影响myisam类型表的读写性能。  
命中率过低，说明myisam类型表的读写存在问题。  
key buffer 命中率实际上包括读命中率和写命中率两种，计算公式如下：

> key\_buffer\_read\_hits = (1-key\_reads / key\_read\_requests) \* 100%  
> key\_buffer\_write\_hits = (1-key\_writes / key\_write\_requests) \* 100%

show global status like 'key%';

![在这里插入图片描述][7e8f632de66c41c59a3bc1a3084e357a.png]

###### 4、InnoDB Buffer命中率 ######

innodb buffer 所指的是innodb\_buffer\_pool，也就是用来缓存innodb类型表和索引的内在空间。  
命中率过低，说明innodb类型表的读写存在问题。  
计算公式：  
innodb\_buffer\_read\_hits = (1 - innodb\_buffer\_pool\_reads / innodb\_buffer\_pool\_read\_requests) \* 100%

show global  status like 'innodb_buffer_pool_read%';

![在这里插入图片描述][ab9514d894634b13badb046aee478407.png]

###### 5、query cache命中率 ######

query cache 是mysql的查询cache，在my.cnf配置文件若打开，则可以对查询过的语句结果进行cache。  
对于一些用户数不高或一次性统计平台建议关闭查询缓存。  
若开启query cache，则对query cache 命中率进行监控也是需要的，它可以告诉我们是数据库是否在正确使用query cache。  
计算公式：  
query\_cache\_hits =(Qcache\_hits/(Qcache\_hits+Qcache\_inserts))\* 100%;

show global status like 'Qcache%';

在mysql5中，引入了query-cache的功能,query-cache默认是关闭的。  
**mysql建议我们用第三方的缓存技术，而不是用mysql本身的 query-cache去缓存数据,在mysql8也移除了query-cache**

###### 6 、table\_cache（table\_open\_cache） 命中率 ######

table\_cache是一个非常重要的MySQL性能参数，table\_cache主要用于设置table高速缓存的数量。在**5.1.3**之后的版本中叫做**table\_open\_cache**。  
由于每个客户端连接都会至少访问一个表，因此此参数的值与max\_connections有关。  
当mysql访问某个表时，若表缓存空间还有空间，则将该表就被打开并将数据放入其中，下次访问此表时可以更快的访问表的内容。  
通过查峰值时间的状态值open\_tables 和 opened\_tables可以决定是否需要增加table\_cache值。  
需要注意的是table\_cache设置很太高，可能会造成文件描述符不足，从而造成性能不稳定或是连接失败。

**建议：状态量open\_tables与opened\_tables之间的比率要大于80%**

open\_tables与opened\_tables之间的比率过低，则代表table cache设置过小。

show global status like 'open%_tables';

![在这里插入图片描述][1743a3c04644417cbf3f0fa5f36f80bc.png]  
查看table\_open\_cache的值：

show global variables like 'table_open_cache';

修改**table\_open\_cache** 的值，方式如下：

*  方式一：可以在my.ini或my.cnf里修改参数**table\_open\_cache** 的值。要重启mysql服务。
 *  方式二：在SET GLOBAL table\_open\_cache= 2000;这种方式不需要重启mysql服务。MySQL重启后会失效。

###### 6、thread cache命中率 ######

在mysql中，为了尽可能提高客户端连接的过程，实现 了一个thread cache池，将空闲的连接线程存放在其中，而不是请求完成后销毁，当有新的连接请求的时候，mysql首先检查thread cache是否存储空闲的连接线程，如果存在则取出来直接使用，如果没有空闲连接线程，才创建新的线程。  
thread cache命中率能直接反应出系统参数thread\_cache\_size设置是否合理。一个合理的read\_cache\_size参数能够节约大量创建新连接时所需要消耗的资源。正常来说，thread cache命中率在**90%** 以上才算合理。

计算公式：

> thread\_cache\_hits = (1- threads\_created/connections) \* 100 %;

show global status like 'Thread%';
    show global status like 'Connections';

![在这里插入图片描述][3a253df4aa784641bf4691d8965f8525.png]  
![在这里插入图片描述][7603ebad9ef24fb2899480edbb1fedc4.png]  
参数说明：

*  threads\_created：表示创建过的线程数，很明显，threads\_created过大，表明mysql服务器一直在创建线程，这也是比较耗资源，说明服务器不健康
 *  Connections：连接MySQL服务器的次数。

###### 优化 ######

如果thread cache命中率低于**90%**  
查看thread\_cache\_size 的大小：

show global variables like 'thread_cache_size';

![在这里插入图片描述][74781046af7f4537b6121a8f89e58dbf.png]  
适当增加配置文件中thread\_cache\_size值，在my.cnf文件中直接加上thread\_cache\_size=64。  
重启Mysql服务，配置生效。

或者执行下面这个命令，这种方式不需要重启mysql服务。MySQL重启后会失效。

set global thread_cache_size=30;

###### 7、tmp table相关状况分析 ######

tmp table 主要用于监控mysql使用临时表的量是否过多，是否有临时表过大而不得不从内存中换出到磁盘文件中

show global status like 'created_tmp%';

![在这里插入图片描述][54533ece39074848b478b69657d74964.png]

参数说明：

Created\_tmp\_disk\_tables:为临时表过大无法在内存中完成，而不得不使用磁盘的次数。  
若create\_tmp\_tables比较多，则可能排序句子过多，或者可能是连接方式不是很优化。  
而如果是create\_tmp\_dis\_table/create\_tmp\_tables比率过高，如超过10%，则需要考虑tmp\_table\_size参数是否需要调整大些。  
**建议tmp\_table\_size与max\_heap\_table\_size需要设置成一样大。**  
在my.cnf文件中直接加上/调整**tmp\_table\_size** 的值。重启Mysql服务，配置生效。

或者执行下面这个命令，这种方式不需要重启mysql服务。MySQL重启后会失效。

set global tmp_table_size=自定义;

查看生效的配置：

show global variables like 'tmp_table_size';
    show global variables like 'max_heap_table_size';

![在这里插入图片描述][1ee561858ea04da4ab05244941be9cd5.png]  
![在这里插入图片描述][7a66b0de1ac94ef6880d3ac908d5abca.png]

###### 8、binlog cache ######

若打开binlog日志功能，则需要考虑binlog cache问题。binlog不是一有数据就写到binlog中，而是先写入到binlog cache中，再写入到binlog中。  
Binlog\_cache\_disk\_use为binlog使用硬盘使用量， Binlog\_cache\_use 为binlog已使用的量。若 Binlog\_cache\_disk\_use大于0，则说明binlog\_cache不够用，需要调大 binlog\_cache\_size大小。

show status like 'binlog_cache%';

![在这里插入图片描述][edf708c95be44e6ebfc30a28524b82f6.png]  
在my.cnf文件中直接加上/调整**binlog\_cache\_size** 的值。重启Mysql服务，配置生效。

或者执行下面这个命令，这种方式不需要重启mysql服务。MySQL重启后会失效。

set global binlog_cache_size=自定义;

查看生效的配置：

show global variables like 'binlog_cache_size';

![在这里插入图片描述][afa7fa1b63344fb28eba09f83ce0382d.png]

###### 9、innodb\_log\_waits ######

show status like 'innodb_log_waits';

Innodb\_log\_waits值不等于0的话，表明 innodb log buffer 因为空间不足而等待。需要增加innodb\_log\_buffer\_size的值，适当的增加不会造成内存不足的问题。  
![在这里插入图片描述][62623ee0e1584b4ea6e9ec53d4673e90.png]  
在my.cnf文件中直接加上/调整**innodb\_log\_buffer\_size** 的值。重启Mysql服务，配置生效。

或者执行下面这个命令，这种方式不需要重启mysql服务。MySQL重启后会失效。

set global innodb_log_buffer_size=自定义;

查看生效的配置：

show global variables like 'innodb_log_buffer_size';

![在这里插入图片描述][f1122f6eb0b8409db5f224362c011583.png]

###### 10、锁状态 ######

mysql的锁有表锁和行锁，myisam最小锁为表锁，innodb最小锁为行锁，可以通过以下命令获取锁定次数、锁定造成其他线程等待次数，以及锁定等待时间信息。

show status like '%lock%';

Table\_locks\_waited/Table\_locks\_immediate 的比值比较大的话，说明表锁造成的阻塞比较严重。可能需要调整Query语句，或者更改存储引擎，亦或者需要调整业务逻辑。

而Innodb\_row\_lock\_waits较大，则说明Innodb的行锁也比较严重，且影响了其他线程的正常处理。  
造成Innodb行锁严重的原因可能是Query语句所利用的索引不够合理（Innodb行锁是基于索引来锁定的），造成间隙锁过大。

## 三、总结 ##

本文介绍了什么是SHOW STATUS，以及如何使用SHOW STATUS。分享了10个常用的mysql状态查询：

*  QPS(每秒Query量)
 *  TPS(每秒事务数)
 *  key Buffer 命中率
 *  InnoDB Buffer命中率
 *  query cache命中率
 *  table\_cache（table\_open\_cache） 命中率
 *  tmp table相关状况分析
 *  binlog cache
 *  innodb\_log\_waits
 *  锁状态

所以，嗯，这题的答案选。。评论区大声告诉虚竹哥。

## 四、参考 ##

[MySQL进阶技能树>查询优化> SHOW STATUS][MySQL_ SHOW STATUS]  
[如何计算 MySQL 的 QPS/TPS][MySQL _ QPS_TPS]  
[MySQL调试–show status][MySQL_show status]

## 五、粉丝福利 ##

![在这里插入图片描述][35a3d76a9f3246b6a7f9da135dcc6c35.jpeg]  
![在这里插入图片描述][449629ea07cb4419b7ef0e39efdd7e8a.png]

### 福利0 ###

虚竹哥直接送1本好书，点赞数最高的好评~

*  点赞数最高的好评送书：此文章的点赞数最高的好评送一本实体书《Java核心技术》（卷一或卷二 自选一本）
 *  统计截止时间：2023/08/10 19:00:00

### 福利1 ###

虚竹哥直接送2本好书，参与好评送书~

*  参与好评送书：随机抽取2位幸运读者，2位幸运读者各送一本实体书《Java核心技术》（卷一或卷二 自选一本）
 *  统计截止时间：2023/08/10 19:00:00

### 福利2 ###

来虚竹哥送书的朋友圈点赞，点赞送书~  
虚竹哥开放好友位 ：[传送门–》][Link 1]

*  第8个，第88个好友点赞的，2位幸运读者各送一本实体书《Java核心技术》（卷一或卷二 自选一本）
 *  统计截止时间：2023/08/10 19:00:00

**请中奖的读者统计截止时间过后7天内私信虚竹哥收货地址，过期奖励作废哈！**

我是虚竹哥，我们明天见~

[100_MYSQL]: https://blog.csdn.net/shi_hong_fei_hei/category_11929488.html
[736279e6ef72463793eefccad7a66f2b.png]: https://img-blog.csdnimg.cn/736279e6ef72463793eefccad7a66f2b.png
[b]: https://img-blog.csdnimg.cn/079f1220fd574cafb0c8b84cda0961ec.png
[667225e950dc4a05b7d9ae1cb0b1eb19.png]: https://img-blog.csdnimg.cn/667225e950dc4a05b7d9ae1cb0b1eb19.png
[fa101a6a768348f582a9d7f9660f43e9.png]: https://img-blog.csdnimg.cn/fa101a6a768348f582a9d7f9660f43e9.png
[63cf5bf34249407b8f3181168799d9c5.png]: https://img-blog.csdnimg.cn/63cf5bf34249407b8f3181168799d9c5.png
[a11fd1f49bb14b339b3802fd92253df4.png]: https://img-blog.csdnimg.cn/a11fd1f49bb14b339b3802fd92253df4.png
[1cef4bf804c1498aa3187d88a75f006c.png]: https://img-blog.csdnimg.cn/1cef4bf804c1498aa3187d88a75f006c.png
[158799d98cf84aec8e21cf4c2f8b6330.png]: https://img-blog.csdnimg.cn/158799d98cf84aec8e21cf4c2f8b6330.png
[7e8f632de66c41c59a3bc1a3084e357a.png]: https://img-blog.csdnimg.cn/7e8f632de66c41c59a3bc1a3084e357a.png
[ab9514d894634b13badb046aee478407.png]: https://img-blog.csdnimg.cn/ab9514d894634b13badb046aee478407.png
[1743a3c04644417cbf3f0fa5f36f80bc.png]: https://img-blog.csdnimg.cn/1743a3c04644417cbf3f0fa5f36f80bc.png
[3a253df4aa784641bf4691d8965f8525.png]: https://img-blog.csdnimg.cn/3a253df4aa784641bf4691d8965f8525.png
[7603ebad9ef24fb2899480edbb1fedc4.png]: https://img-blog.csdnimg.cn/7603ebad9ef24fb2899480edbb1fedc4.png
[74781046af7f4537b6121a8f89e58dbf.png]: https://img-blog.csdnimg.cn/74781046af7f4537b6121a8f89e58dbf.png
[54533ece39074848b478b69657d74964.png]: https://img-blog.csdnimg.cn/54533ece39074848b478b69657d74964.png
[1ee561858ea04da4ab05244941be9cd5.png]: https://img-blog.csdnimg.cn/1ee561858ea04da4ab05244941be9cd5.png
[7a66b0de1ac94ef6880d3ac908d5abca.png]: https://img-blog.csdnimg.cn/7a66b0de1ac94ef6880d3ac908d5abca.png
[edf708c95be44e6ebfc30a28524b82f6.png]: https://img-blog.csdnimg.cn/edf708c95be44e6ebfc30a28524b82f6.png
[afa7fa1b63344fb28eba09f83ce0382d.png]: https://img-blog.csdnimg.cn/afa7fa1b63344fb28eba09f83ce0382d.png
[62623ee0e1584b4ea6e9ec53d4673e90.png]: https://img-blog.csdnimg.cn/62623ee0e1584b4ea6e9ec53d4673e90.png
[f1122f6eb0b8409db5f224362c011583.png]: https://img-blog.csdnimg.cn/f1122f6eb0b8409db5f224362c011583.png
[MySQL_ SHOW STATUS]: https://edu.csdn.net/skill/mysql02/mysql-3574b2e5c9ca475789d9d582d7726906?from=from_parent_mindnote&typeId=80923
[MySQL _ QPS_TPS]: https://blog.csdn.net/ActionTech/article/details/120063367
[MySQL_show status]: https://blog.51cto.com/knifeedge/5138397
[35a3d76a9f3246b6a7f9da135dcc6c35.jpeg]: https://img-blog.csdnimg.cn/35a3d76a9f3246b6a7f9da135dcc6c35.jpeg
[449629ea07cb4419b7ef0e39efdd7e8a.png]: https://img-blog.csdnimg.cn/449629ea07cb4419b7ef0e39efdd7e8a.png
[Link 1]: https://bbs.csdn.net/topics/611175348