【第22天】MYSQL进阶-查询优化- performance_schema系列四：事件统计（SQL 小虚竹)

青旅半醒 2023-10-07 20:56 8阅读 0赞

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

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

*  零、前言
 *  一、练习题目
 *  二、SQL思路
 *   *  SQL进阶-查询优化- performance\_schema系列四：事件统计
     *   *  等待事件统计表
         *   *  介绍
             *  统计表统计内容查看
         *  阶段事件统计表
         *   *  介绍
             *  统计表统计内容查看
         *  事务事件统计表
         *   *  介绍
             *  统计表统计内容查看
             *  特别说明下事务聚合统计规则
         *  语句事件统计表
         *   *  介绍
             *  统计表统计内容查看
         *  内存事件统计表
         *   *  介绍
             *  统计表统计内容查看
             *  内存统计表允许使用TRUNCATE TABLE语句。使用truncate语句时有如下行为
             *  关于内存事件的行为监控设置与注意事项
             *  当server中的某线程执行了内存分配操作时，按照如下规则进行检测与聚合
             *  对于内存块的释放，按照如下规则进行检测与聚合
 *  总结
 *  四、参考

## 零、前言 ##

今天是学习 **SQL** 打卡的第 **22** 天，每天我会提供一篇文章供群成员阅读（ **不需要订阅付钱** ）。

希望大家先自己思考，如果实在没有想法，再看下面的解题思路，自己再实现一遍。在[小虚竹JAVA社区][JAVA] 中对应的 【打卡贴】打卡，今天的任务就算完成了，养成每天学习打卡的好习惯。

虚竹哥会组织大家一起学习同一篇文章，所以有什么问题都可以在群里问，群里的小伙伴可以迅速地帮到你，一个人可以走得很快，一群人可以走得很远，有一起学习交流的战友，是多么幸运的事情。

我的学习策略很简单，题海策略+ 费曼学习法。如果能把这些题都认认真真自己实现一遍，那意味着 **SQL** 已经筑基成功了。后面的进阶学习，可以继续跟着我，一起走向架构师之路。

今天的学习内容是：**SQL进阶-查询优化- performance\_schema系列四：事件统计**

本文内容有1万多字，内容非常多，建议阅读方式：**记住标题和小模块的介绍** ，内容详解可帮助理解，看不懂也没事，后续章节的实战中也可慢慢体会。  
**本文适用于收藏，需要查阅指定配置时，可快速找到配置的详解。**

## 一、练习题目 ##

## 二、SQL思路 ##

### SQL进阶-查询优化- performance\_schema系列四：事件统计 ###

经过昨天的performance\_schema事件记录表的学习，懂得了什么是事件，但我们并不关心每条事件记录信息的，我们更关心一段时间的事件统计。这就是今天要学习的内容：统计事件表分为5个类别，分别为等待事件、阶段事件、语句事件、事务事件、内存事件。

#### 等待事件统计表 ####

##### 介绍 #####

performance\_schema把等待事件统计表按不同纬度进行聚合，聚合统计数据列包含事件发生次数，总等待时间，最小、最大、平均等待时间。  
等待事件统计表有这么几张表：

use performance_schema;

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

show tables like '%events_waits_summary%';

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

##### 统计表统计内容查看 #####

select * from events_waits_summary_by_account_by_event_name limit 1;
    
    select * from events_waits_summary_by_host_by_event_name limit 1;
    
    select * from events_waits_summary_by_instance limit 1;
    
    select * from events_waits_summary_by_thread_by_event_name limit 1;
    
    select * from events_waits_summary_by_user_by_event_name limit 1;
    
    select * from events_waits_summary_global_by_event_name limit 1;

![在这里插入图片描述][d2aa1e0b98424fa48d006c0fcbc6a0ba.png]  
![在这里插入图片描述][a7d6587a491f4aa399c266c47013e570.png]  
![在这里插入图片描述][f3a296fa9d074d1cb41124405fe85738.png]  
![在这里插入图片描述][95edf089165f434e80a4b9afcb39ff55.png]  
![在这里插入图片描述][2c1c0cde7f0e4e8682c8e448145415b4.png]  
![在这里插入图片描述][e830378e6e1e4d3f908b7307d4bdbd67.png]  
如上所示，所有表的统计列是如下这些列：

*  COUNT\_STAR：事件被执行的数量。此值包括所有事件的执行次数，需要启用等待事件的instruments
 *  SUM\_TIMER\_WAIT：统计给定计时事件的总等待时间。此值仅针对有计时功能的事件instruments或开启了计时功能事件的instruments，如果某事件的instruments不支持计时或者没有开启计时功能，则该字段为NULL。其他xxx\_TIMER\_WAIT字段值类似
 *  MIN\_TIMER\_WAIT：给定计时事件的最小等待时间
 *  AVG\_TIMER\_WAIT：给定计时事件的平均等待时间
 *  MAX\_TIMER\_WAIT：给定计时事件的最大等待时间
 *  **PS：等待事件统计表允许使用TRUNCATE TABLE语句。**
    
     *  执行truncate语句，不是清空，而是统计列的值置为0 。

**注意：这些表只针对等待事件信息进行统计**

#### 阶段事件统计表 ####

##### 介绍 #####

阶段事件统计表跟等待事件统计表是类似的规则进行聚合，它有这么几张表：

show tables like '%events_stages_summary%';

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

##### 统计表统计内容查看 #####

select * from events_stages_summary_by_account_by_event_name where USER is not null limit 1;
    
     select * from events_stages_summary_by_host_by_event_name where HOST is not null limit 1;
    
    select * from events_stages_summary_by_thread_by_event_name where thread_id is not null limit 1;
    
    select * from events_stages_summary_by_user_by_event_name where user is not null limit 1;
    
    select * from events_stages_summary_global_by_event_name limit 1;

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

![在这里插入图片描述][31faf038a17c406e8e97d4a3cb1d4e86.png]  
![在这里插入图片描述][ed4228cd14964ffca2bab67d82886453.png]  
![在这里插入图片描述][17435b5b2e664be2ad6038dc10989ed4.png]  
![在这里插入图片描述][4e5d6ed415e94920b032dd85816bf468.png]

这些表的字段内容跟等待事件统计表的字段内容是差不多的，就不重复水字数了。哈哈哈～

**注意：这些表只针对阶段事件信息进行统计**

**PS：对这些表使用truncate语句，影响与等待事件类似**

#### 事务事件统计表 ####

##### 介绍 #####

事务事件统计表跟等待事件统计表是类似的规则进行聚合，它有这么几张表：

show tables like '%events_transactions_summary%';

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

##### 统计表统计内容查看 #####

select * from events_transactions_summary_by_account_by_event_name where COUNT_STAR!=0 limit 1;
    
    select * from events_transactions_summary_by_host_by_event_name where COUNT_STAR!=0 limit 1;
    
    select * from events_transactions_summary_by_thread_by_event_name where SUM_TIMER_WAIT!=0;
    
    select * from events_transactions_summary_by_user_by_event_name where SUM_TIMER_WAIT!=0;
    
    select * from events_transactions_summary_global_by_event_name where SUM_TIMER_WAIT!=0;

![在这里插入图片描述][680c82c4d00440db9921a6382ce8cf3a.png]  
![在这里插入图片描述][55659ea7c98f431ab6196fb106da4c49.png]  
![在这里插入图片描述][8aeb9bb5b07b49d9bb6f74351e4adfe0.png]  
![在这里插入图片描述][8becba1dd7a4403392480586c97db510.png]

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

这些表的字段内容跟等待事件统计表的字段内容是差不多的。

对于事务统计事件，针对读写事务和只读事务还单独做了统计(xx\_READ\_WRITE和xx\_READ\_ONLY列，只读事务需要设置只读事务变量transaction\_read\_only=on才会进行统计)。

**注意：这些表只针对事务事件信息进行统计**

##### 特别说明下事务聚合统计规则 #####

*  事务事件的收集不考虑隔离级别，访问模式或自动提交模式
 *  读写事务通常比只读事务占用更多资源，因此事务统计表包含了用于读写和只读事务的单独统计列
 *  事务所占用的资源需求多少也可能会因事务隔离级别有所差异(例如：锁资源)。但是：每个server可能是使用相同的隔离级别，所以不单独提供隔离级别相关的统计列

**PS：对这些表使用truncate语句，影响与等待事件类似。**

#### 语句事件统计表 ####

##### 介绍 #####

语句事件统计表中默认会记录所有的语句事件统计信息，语句事件统计表跟等待事件统计表是类似的规则进行聚合，它有这么几张表：

show tables like '%events_statements_summary%';

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

##### 统计表统计内容查看 #####

select * from events_statements_summary_by_account_by_event_name where COUNT_STAR!=0 limit 1;
    
    select * from events_statements_summary_by_digest limit 1;
    
    select * from events_statements_summary_by_host_by_event_name where COUNT_STAR!=0 limit 1;
    
    select * from events_statements_summary_by_program;
    
    select * from events_statements_summary_by_thread_by_event_name where COUNT_STAR!=0 limit 1;
    
    select * from events_statements_summary_by_user_by_event_name where COUNT_STAR!=0 limit 1;
    
    select * from events_statements_summary_global_by_event_name limit 1;

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

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

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

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

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

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

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

这些表的字段内容跟等待事件统计表的字段内容是差不多的。  
对于语句统计事件，有针对语句对象的额外的统计列:

*  SUM\_xxx：针对events\_statements\_\*事件记录表中相应的xxx列进行统计。例如：语句统计表中的SUM\_LOCK\_TIME和SUM\_ERRORS列对events\_statements\_current事件记录表中LOCK\_TIME和ERRORS列进行统计
 *  events\_statements\_summary\_by\_digest表有自己额外的统计列：FIRST\_SEEN，LAST\_SEEN：显示某给定语句第一次插入 events\_statements\_summary\_by\_digest表和最后一次更新该表的时间戳
 *  events\_statements\_summary\_by\_program表有自己额外的统计列：COUNT\_STATEMENTS，SUM\_STATEMENTS\_WAIT，MIN\_STATEMENTS\_WAIT，AVG\_STATEMENTS\_WAIT，MAX\_STATEMENTS\_WAIT：关于存储程序执行期间调用的嵌套语句的统计信息
 *  prepared\_statements\_instances表有自己额外的统计列：COUNT\_EXECUTE，SUM\_TIMER\_EXECUTE，MIN\_TIMER\_EXECUTE，AVG\_TIMER\_EXECUTE，MAX\_TIMER\_EXECUTE：执行prepare语句对象的统计信息

#### 内存事件统计表 ####

##### 介绍 #####

内存事件统计表跟等待事件统计表是类似的规则进行聚合，  
performance\_schema会记录内存使用情况并聚合内存使用统计信息，如：使用的内存类型（各种缓存，内部缓冲区等）和线程、帐号、用户、主机的相关操作间接执行的内存操作。performance\_schema从使用的内存大小、相关操作数量、高低水位（内存一次操作的最大和最小的相关统计值）。

检测内存工作负载峰值、内存总体的工作负载稳定性、可能的内存泄漏等是至关重要的。

注意：**内存统计表不包含计时信息，因为内存事件不支持时间信息收集。**  
它有这么几张表：

show tables like '%memory%summary%';

![在这里插入图片描述][60c1e5fdf5f344789da30243a7a8341e.png]  
要统计内存事件信息，需要开启内存事件采集器：

update setup_instruments set enabled='yes',timed='yes' where name like 'memory/%';

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

##### 统计表统计内容查看 #####

select * from memory_summary_by_account_by_event_name where COUNT_ALLOC!=0 limit 1;
    
    select * from memory_summary_by_host_by_event_name where COUNT_ALLOC!=0 limit 1;
    
    select * from memory_summary_by_thread_by_event_name where COUNT_ALLOC!=0 limit 1;
    
    select * from memory_summary_by_user_by_event_name where COUNT_ALLOC!=0 limit 1;
    
    select * from memory_summary_global_by_event_name where COUNT_ALLOC!=0 limit 1;

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

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

![在这里插入图片描述][47b6673f371d47919f6866bbb03b1c37.png]  
![在这里插入图片描述][fafd6af383434442bba6d2721366ccf2.png]  
![在这里插入图片描述][606fa88e26ac472bad0cca1267227c43.png]  
这些表的字段内容跟等待事件统计表的字段内容是差不多的。

内存统计表都有如下统计列:

*  COUNT\_ALLOC，COUNT\_FREE：对内存分配和释放内存函数的调用总次数
 *  SUM\_NUMBER\_OF\_BYTES\_ALLOC，SUM\_NUMBER\_OF\_BYTES\_FREE：已分配和已释放的内存块的总字节大小
 *  CURRENT\_COUNT\_USED：这是一个便捷列，等于COUNT\_ALLOC - COUNT\_FREE
 *  CURRENT\_NUMBER\_OF\_BYTES\_USED：当前已分配的内存块但未释放的统计大小。这是一个便捷列，等于SUM\_NUMBER\_OF\_BYTES\_ALLOC - SUM\_NUMBER\_OF\_BYTES\_FREE
 *  LOW\_COUNT\_USED，HIGH\_COUNT\_USED：对应CURRENT\_COUNT\_USED列的低和高水位标记
 *  LOW\_NUMBER\_OF\_BYTES\_USED，HIGH\_NUMBER\_OF\_BYTES\_USED：对应CURRENT\_NUMBER\_OF\_BYTES\_USED列的低和高水位标记

##### 内存统计表允许使用TRUNCATE TABLE语句。使用truncate语句时有如下行为 #####

*  通常，truncate操作会重置统计信息的基准数据（即清空之前的数据），但不会修改当前server的内存分配等状态。也就是说，truncate内存统计表不会释放已分配内存
 *  将COUNT\_ALLOC和COUNT\_FREE列重置，并重新开始计数（等于内存统计信息以重置后的数值作为基准数据）
 *  SUM\_NUMBER\_OF\_BYTES\_ALLOC和SUM\_NUMBER\_OF\_BYTES\_FREE列重置与COUNT\_ALLOC和COUNT\_FREE列重置类似
 *  LOW\_COUNT\_USED和HIGH\_COUNT\_USED将重置为CURRENT\_COUNT\_USED列值
 *  LOW\_NUMBER\_OF\_BYTES\_USED和HIGH\_NUMBER\_OF\_BYTES\_USED将重置为CURRENT\_NUMBER\_OF\_BYTES\_USED列值
 *  此外，按照帐户，主机，用户或线程分类统计的内存统计表或memory\_summary\_global\_by\_event\_name表，如果在对其依赖的accounts、hosts、users表执行truncate时，会隐式对这些内存统计表执行truncate语句

##### 关于内存事件的行为监控设置与注意事项 #####

内存行为监控设置：

*  内存instruments在setup\_instruments表中具有memory/code\_area/instrument\_name格式的名称。但默认情况下大多数instruments都被禁用了，默认只开启了memory/performance\_schema/\*开头的instruments
 *  以前缀memory/performance\_schema命名的instruments可以收集performance\_schema自身消耗的内部缓存区大小等信息。memory/performance\_schema/\* instruments默认启用，无法在启动时或运行时关闭。performance\_schema自身相关的内存统计信息只保存在memory\_summary\_global\_by\_event\_name表中，不会保存在按照帐户，主机，用户或线程分类聚合的内存统计表中
 *  对于memory instruments，setup\_instruments表中的TIMED列无效，因为内存操作不支持时间统计
 *  注意：如果在server启动之后再修改memory instruments，可能会导致由于丢失之前的分配操作数据而导致在释放之后内存统计信息出现负值，所以不建议在运行时反复开关memory instruments，如果有内存事件统计需要，建议在server启动之前就在my.cnf中配置好需要统计的事件采集

##### 当server中的某线程执行了内存分配操作时，按照如下规则进行检测与聚合 #####

*  如果该线程在threads表中没有开启采集功能或者说在setup\_instruments中对应的instruments没有开启，则该线程分配的内存块不会被监控
 *  如果threads表中该线程的采集功能和setup\_instruments表中相应的memory instruments都启用了，则该线程分配的内存块会被监控

##### 对于内存块的释放，按照如下规则进行检测与聚合 #####

*  如果一个线程开启了采集功能，但是内存相关的instruments没有启用，则该内存释放操作不会被监控到，统计数据也不会发生改变
 *  如果一个线程没有开启采集功能，但是内存相关的instruments启用了，则该内存释放的操作会被监控到，统计数据会发生改变，这也是前面提到的为啥反复在运行时修改memory instruments可能导致统计数据为负数的原因

## 总结 ##

本文讲解了performance\_schema中事件统计，并了解了5个类型的统计事件表，分别为等待事件统计表、阶段事件统计表、语句事件统计表、事务事件统计表、内存事件统计表。  
详细介绍了事件统计表的字段说明和一些注意事项。

## 四、参考 ##

[事件统计 | performance\_schema全方位介绍][_ performance_schema]

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

[100_MYSQL]: https://blog.csdn.net/shi_hong_fei_hei/category_11929488.html
[JAVA]: https://bbs.csdn.net/forums/xiaoxuzhu
[7724420879c24fd7888b6aef6685398e.png]: https://img-blog.csdnimg.cn/7724420879c24fd7888b6aef6685398e.png
[68035c912a7542d693e105c42c30b44e.png]: https://img-blog.csdnimg.cn/68035c912a7542d693e105c42c30b44e.png
[d2aa1e0b98424fa48d006c0fcbc6a0ba.png]: https://img-blog.csdnimg.cn/d2aa1e0b98424fa48d006c0fcbc6a0ba.png
[a7d6587a491f4aa399c266c47013e570.png]: https://img-blog.csdnimg.cn/a7d6587a491f4aa399c266c47013e570.png
[f3a296fa9d074d1cb41124405fe85738.png]: https://img-blog.csdnimg.cn/f3a296fa9d074d1cb41124405fe85738.png
[95edf089165f434e80a4b9afcb39ff55.png]: https://img-blog.csdnimg.cn/95edf089165f434e80a4b9afcb39ff55.png
[2c1c0cde7f0e4e8682c8e448145415b4.png]: https://img-blog.csdnimg.cn/2c1c0cde7f0e4e8682c8e448145415b4.png
[e830378e6e1e4d3f908b7307d4bdbd67.png]: https://img-blog.csdnimg.cn/e830378e6e1e4d3f908b7307d4bdbd67.png
[9575da5b1f364dfc8065520ec8d6b6a9.png]: https://img-blog.csdnimg.cn/9575da5b1f364dfc8065520ec8d6b6a9.png
[90e1aac0b5884a52b1e35955fd70f0a9.png]: https://img-blog.csdnimg.cn/90e1aac0b5884a52b1e35955fd70f0a9.png
[31faf038a17c406e8e97d4a3cb1d4e86.png]: https://img-blog.csdnimg.cn/31faf038a17c406e8e97d4a3cb1d4e86.png
[ed4228cd14964ffca2bab67d82886453.png]: https://img-blog.csdnimg.cn/ed4228cd14964ffca2bab67d82886453.png
[17435b5b2e664be2ad6038dc10989ed4.png]: https://img-blog.csdnimg.cn/17435b5b2e664be2ad6038dc10989ed4.png
[4e5d6ed415e94920b032dd85816bf468.png]: https://img-blog.csdnimg.cn/4e5d6ed415e94920b032dd85816bf468.png
[6b9564ebebf14addace395eaa8515380.png]: https://img-blog.csdnimg.cn/6b9564ebebf14addace395eaa8515380.png
[680c82c4d00440db9921a6382ce8cf3a.png]: https://img-blog.csdnimg.cn/680c82c4d00440db9921a6382ce8cf3a.png
[55659ea7c98f431ab6196fb106da4c49.png]: https://img-blog.csdnimg.cn/55659ea7c98f431ab6196fb106da4c49.png
[8aeb9bb5b07b49d9bb6f74351e4adfe0.png]: https://img-blog.csdnimg.cn/8aeb9bb5b07b49d9bb6f74351e4adfe0.png
[8becba1dd7a4403392480586c97db510.png]: https://img-blog.csdnimg.cn/8becba1dd7a4403392480586c97db510.png
[99ad281ff1c54e6db7319af38d85514f.png]: https://img-blog.csdnimg.cn/99ad281ff1c54e6db7319af38d85514f.png
[e6c88ad534e34f1798342157bcec369f.png]: https://img-blog.csdnimg.cn/e6c88ad534e34f1798342157bcec369f.png
[81592a06e8084a9c94ad6afb3cc8d793.png]: https://img-blog.csdnimg.cn/81592a06e8084a9c94ad6afb3cc8d793.png
[f7963d82742144da9b0833fe1c38b67f.png]: https://img-blog.csdnimg.cn/f7963d82742144da9b0833fe1c38b67f.png
[3d4ab0fc048342788a6233c1cdf70b1a.png]: https://img-blog.csdnimg.cn/3d4ab0fc048342788a6233c1cdf70b1a.png
[9e0fb8b80eff41b09dd0f44cd075479a.png]: https://img-blog.csdnimg.cn/9e0fb8b80eff41b09dd0f44cd075479a.png
[9fe9fcde80e14ba598b60a4495c634f0.png]: https://img-blog.csdnimg.cn/9fe9fcde80e14ba598b60a4495c634f0.png
[6590f3bf830d49478bc28a747af349aa.png]: https://img-blog.csdnimg.cn/6590f3bf830d49478bc28a747af349aa.png
[37786e442d0941f892de75fce1446179.png]: https://img-blog.csdnimg.cn/37786e442d0941f892de75fce1446179.png
[60c1e5fdf5f344789da30243a7a8341e.png]: https://img-blog.csdnimg.cn/60c1e5fdf5f344789da30243a7a8341e.png
[21311c3c17524e1d871874014a10b502.png]: https://img-blog.csdnimg.cn/21311c3c17524e1d871874014a10b502.png
[c8e98de490f74bb48aeaa9304d7a39e7.png]: https://img-blog.csdnimg.cn/c8e98de490f74bb48aeaa9304d7a39e7.png
[646e0ea5ccc64aa79342376ae554ce29.png]: https://img-blog.csdnimg.cn/646e0ea5ccc64aa79342376ae554ce29.png
[47b6673f371d47919f6866bbb03b1c37.png]: https://img-blog.csdnimg.cn/47b6673f371d47919f6866bbb03b1c37.png
[fafd6af383434442bba6d2721366ccf2.png]: https://img-blog.csdnimg.cn/fafd6af383434442bba6d2721366ccf2.png
[606fa88e26ac472bad0cca1267227c43.png]: https://img-blog.csdnimg.cn/606fa88e26ac472bad0cca1267227c43.png
[_ performance_schema]: https://mp.weixin.qq.com/s/uQhqVNkA_vQrkHbknzimjA?from=from_parent_mindnote