回城传送–》《32天SQL筑基》

零、前言

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

今天的学习内容是：SQL进阶-查询优化- performance_schema

一、练习题目

题目链接	难度
SQL进阶-查询优化- performance_schema	★★★☆☆

二、SQL思路

SQL进阶-查询优化- performance_schema

初始化数据

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

解法

performance_schema是什么

performance_schema数据库是MySQL自带的系统级数据库，记录了MySQL执行过程中的各种事件。
例如SQL语句的执行过程、存储过程和函数的调用、MySQL请求其他资源的等待及执行的SQL语句等信息。分析performance_schema数据库能够了解当前MySQL实例的运行情况，从而更好地优化MySQL数据库。

在MySQL5.7 中， show profile 命令已经开始不推荐使用，MySQL使用performance_schema 中系统表的信息来替代show profile命令。

performance_schema特点

MySQL的performance_schema 用于监控MySQL server在一个较低级别的运行过程中的资源消耗、资源等待等情况，它具有以下特点：

• 1、提供了一种在数据库运行时实时检查server的内部执行情况的方法。performance_schema 数据库中的表使用performance_schema存储引擎。该数据库主要关注数据库运行过程中的性能相关的数据，与information_schema不同，information_schema主要关注server运行过程中的元数据信息 ；
• 2、performance_schema通过监视server的事件来实现监视server内部运行情况， “事件”就是server内部活动中所做的任何事情以及对应的时间消耗，利用这些信息来判断server中的相关资源消耗在了哪里？一般来说，事件可以是函数调用、操作系统的等待、SQL语句执行的阶段（如sql语句执行过程中的parsing 或 sorting阶段）或者整个SQL语句与SQL语句集合。事件的采集可以方便的提供server中的相关存储引擎对磁盘文件、表I/O、表锁等资源的同步调用信息；
• 3、performance_schema中的事件与写入二进制日志中的事件（描述数据修改的events）、事件计划调度程序（这是一种存储程序）的事件不同。performance_schema中的事件记录的是server执行某些活动对某些资源的消耗、耗时、这些活动执行的次数等情况；
• 4、performance_schema中的事件只记录在本地server的performance_schema中，其下的这些表中数据发生变化时不会被写入binlog中，也不会通过复制机制被复制到其他server中；
• 5、当前活跃事件、历史事件和事件摘要相关的表中记录的信息。能提供某个事件的执行次数、使用时长。进而可用于分析某个特定线程、特定对象（如mutex或file）相关联的活动；
• 6、PERFORMANCE_SCHEMA存储引擎使用server源代码中的“检测点”来实现事件数据的收集。对于performance_schema实现机制本身的代码没有相关的单独线程来检测，这与其他功能（如复制或事件计划程序）不同；
• 7、收集的事件数据存储在performance_schema数据库的表中。这些表可以使用SELECT语句查询，也可以使用SQL语句更新performance_schema数据库中的表记录（如动态修改performance_schema的setup_*开头的几个配置表，但要注意：配置表的更改会立即生效，这会影响数据收集）；
• 8、performance_schema的表中的数据不会持久化存储在磁盘中，而是保存在内存中，一旦服务器重启，这些数据会丢失（包括配置表在内的整个performance_schema下的所有数据） ；
• 9、MySQL支持的所有平台中事件监控功能都可用，但不同平台中用于统计事件时间开销的计时器类型可能会有所差异。

performance_schema设计目标

performance_schema实现机制遵循以下设计目标：

• 1、启用performance_schema不会导致server的行为发生变化。例如，它不会改变线程调度机制，不会导致查询执行计划（如EXPLAIN）发生变化 ；
• 2、启用performance_schema之后，server会持续不间断地监测，开销很小。不会导致server不可用 ；
• 3、在该实现机制中没有增加新的关键字或语句，解析器不会变化 ；
• 4、即使performance_schema的监测机制在内部对某事件执行监测失败，也不会影响server正常运行 ；
• 5、如果在开始收集事件数据时碰到有其他线程正在针对这些事件信息进行查询，那么查询会优先执行事件数据的收集，因为事件数据的收集是一个持续不断的过程，而检索(查询)这些事件数据仅仅只是在需要查看的时候才进行检索。也可能某些事件数据永远都不会去检索
• 6、需要很容易地添加新的instruments监测点 ；
• 7、instruments(事件采集项)代码版本化：如果instruments的代码发生了变更，旧的instruments代码还可以继续工作；
• 8、注意：MySQL sys schema是一组对象（包括相关的视图、存储过程和函数），可以方便地访问performance_schema收集的数据。同时检索的数据可读性也更高(例如：performance_schema中的时间单位是皮秒，经过sys schema查询时会转换为可读的us,ms,s,min,hour,day等单位)，sys schem在5.7.x版本默认安装 。

MySQL是否支持performance_schema

查看数据库版本：

SELECT VERSION();

在MySQL的information数据库的engines数据表中查看是否支持performance_schema。

SELECT engine,support 
FROM information_schema.engines
WHERE engine='PERFORMANCE_SCHEMA';

结果是yes。
查看是否已经开启performance_schema

SHOW VARIABLES LIKE 'performance_schema';

是已经开启。

注意：

在MySQL 5.7及以上版本中，performance_schema默认是开启的；在MySQL 5.7以下版本中，performance_schema默认是关闭的。

开启/关闭performance_schema

大家还记得开启关闭mysql系统参数的两招吗？再温习下：

• 修改my.ini或my.cnf 配置,重启服务生效;
• 通过命令快速生效,但MySQL重启后会失效

SET SESSION/GLOBAL 系统参数名 = 设置的值;

但performance_schema参数比较特殊，它在mysql中是只读选项，无法通过命令快速生效。
验证下：

SET SESSION performance_schema = OFF;

所以启动/关闭 performance_schema 只能通过修改my.ini或my.cnf 配置,重启服务生效;

验证下：关闭performance_schema
修改my.cnf

vi /etc/my.cnf

重启mysql服务。

systemctl stop mysqld
systemctl start mysqld

要使用这两个命令，需要做一些配置，不了解的可以看这篇，传送门：SQL快速入门-安装MYSQL环境（多环境企业级指导）的linux部分

进入mysql ，查看performance_schema是否关闭。

 SHOW VARIABLES LIKE 'performance_schema';

关闭performance_schema成功。
为了下文的操作，要再开启下，操作不赘述了。

了解performance_schema下存在着哪些表

方式一：使用INFORMATION_SCHEMA.TABLES查询

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA ='performance_schema' and engine='performance_schema';

mysql 8 performance_schema下存在111张表。

方式二：直接在performance_schema库下使用show tables查询

use performance_schema；

show tables from performance_schema;

performance_schema库下表的分类

按照事件类型分组记录性能事件数据的表

语句事件记录表

语句事件记录表，这些表记录了语句事件信息，当前语句事件表events_statements_current、历史语句事件表events_statements_history和长语句历史事件表events_statements_history_long、以及聚合后的摘要表summary，其中，summary表还可以根据帐号(account)，主机(host)，程序(program)，线程(thread)，用户(user)和全局(global)再进行细分)。

show tables like 'events_statement%';

等待事件记录表

等待事件记录表，与语句事件类型的相关记录表类似

show tables like 'events_wait%';

阶段事件记录表

阶段事件记录表，记录语句执行的阶段事件的表，与语句事件类型的相关记录表类似：

show tables like 'events_stage%';

事务事件记录表

事务事件记录表，记录事务相关的事件的表，与语句事件类型的相关记录表类似：

show tables like 'events_transaction%';

监视文件系统层调用的表

show tables like '%file%';

监视内存使用的表

show tables like '%memory%';

动态对performance_schema进行配置的配置表

show tables like '%setup%';

performance_schema的简单配置与使用

了解两个基本概念

• instruments：生产者，用于采集MySQL中各种操作产生的事件信息，对应配置表中的配置项，我们可以称之为事件采集配置项。以下提及的生产者均统称为instruments。
• consumers：消费者，对应的消费者表用于存储来自instruments采集的数据，对应配置表中的配置项，我们可以称之为消费存储配置项。以下提及的消费者均统称为consumers。

实战：配置监测等待事件数据

数据库刚刚初始化并启动时，并非所有instruments和consumers都启用了，所以默认不会收集所有的事件，可能你需要检测的事件并没有打开，需要进行设置。我们以配置监测等待事件数据为例进行说明。

打开等待事件的采集器配置项开关

需要修改setup_instruments 配置表中对应的采集器配置项

UPDATE setup_instruments SET ENABLED = 'YES', TIMED = 'YES' where name like 'wait%';

打开等待事件的保存表配置开关

修改修改setup_consumers 配置表中对应的配置项

UPDATE setup_consumers SET ENABLED = 'YES' where name like '%wait%';

查看server当前正在做什么

可以通过查询events_waits_current表来得知，该表中每个线程只包含一行数据，用于显示每个线程的最新监视事件（正在做的事情）

SELECT * FROM events_waits_current limit 1;

该事件信息表示线程ID为13的线程正在等待innodb存储引擎的dblwr_mutex锁,等待时间为192290皮秒(ps)(万亿分之一秒)
字段说明：

• *_ID列表示事件来自哪个线程、事件编号是多少；
• EVENT_NAME表示检测到的具体的内容；
• SOURCE表示这个检测代码在哪个源文件中以及行号；
• 计时器字段TIMER_START、TIMER_END、TIMER_WAIT分别表示该事件的开始时间、结束时间、以及总的花费时间，如果该事件正在运行而没有结束，那么TIMER_END和TIMER_WAIT的值显示为NULL。注：计时器统计的值是近似值，并不是完全精确

注意：_current表中每个线程只保留一条记录，一旦线程完成工作，该表中不会再记录该线程的事件信息

查看历史表events_waits_history 中记录

_history表中记录每个线程已经执行完成的事件信息，但每个线程的只事件信息只记录10条，再多就会被覆盖掉，*_history_long表中记录所有线程的事件信息，但总记录数量是10000行，超过会被覆盖掉。

SELECT THREAD_ID,EVENT_ID,EVENT_NAME,TIMER_WAIT FROM events_waits_history ORDER BY THREAD_ID limit 21;

summary表提供所有事件的汇总信息

该组中的表以不同的方式汇总事件数据（如：按用户，按主机，按线程等等）。例如：要查看哪些instruments占用最多的时间，可以通过对events_waits_summary_global_by_event_name表的COUNT_STAR或SUM_TIMER_WAIT列进行查询（这两列是对事件的记录数执行COUNT（*）、事件记录的TIMER_WAIT列执行SUM（TIMER_WAIT）统计而来），如下：

SELECT EVENT_NAME,COUNT_STAR FROM events_waits_summary_global_by_event_name
ORDER BY COUNT_STAR DESC LIMIT 10;

SELECT EVENT_NAME,SUM_TIMER_WAIT FROM events_waits_summary_global_by_event_name
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

instance表记录了哪些类型的对象会被检测

这些对象在被server使用时，在该表中将会产生一条事件记录，例如，file_instances表列出了文件I/O操作及其关联文件名。

SELECT * FROM file_instances limit 20;

三、总结

本篇文章带大家了解了什么是performance_schema，以及performance_schema的特点和设计目标，然后怎么去判断mysql是否支持performance_schema。
接着介绍了如何开启/关闭performance_schema，并了解了performance_schema下存在着哪些表。
然后简单介绍了performance_schema库下的表分类，最后以配置检测等待事件数据为例介绍了如何简单配置和使用performance_schema。
performance_schema的知识点比较多，同时也非常重要，所以会写多篇来介绍，可以系统地学会它。

四、参考

MySQL进阶技能树>服务器优化>performance_schema
初相识｜performance_schema全方位介绍

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