1. sys系统库使用基础环境
在使用sys系统库之前，你需要确保你的数据库环境满足如下条件：
1）sys系统库支持MySQL 5.6或更高版本，5.5.x及其以下版本不支持；
2）因为sys系统库提供了一些代替直接访问performance_schema的视图，所以必须启用performance_schema(performance_schema系统参数设置为ON)之后sys系统库的大部分功能才能正常使用；
3）要完全访问sys系统库，用户必须具有以下权限：
*
对所有sys表和视图具有SELECT权限
*
对所有sys存储过程和函数具有EXECUTE权限
*
对sys_config表具有INSERT、UPDATE权限
*
对某些特定的sys系统库存储过程和函数需要额外权限，如，ps_setup_save()存储过程，需要临时表相关的权限
4）还有sys系统库执行访问的对象相关的权限：
*
任何被sys系统库访问的performance_schema表需要有SELECT权限，如果要使用sys系统库对performance_schema相关表执行更新，则需要performance_schema相关表的UPDATE权限
*
INFORMATION_SCHEMA.INNODB_BUFFER_PAGE表的PROCESS
5）如果要充分使用sys系统库的功能，则必须启用某些performance_schema的instruments和consumers，如下：
*
所有wait instruments
*
所有stage instruments
*
所有statement instruments
*
对于所启用的类型事件的instruments，还需要启用对应类型的consumers(xxx_current和xxx_history_long)，要了解某存储过程具体做了什么事情可能通过show
create procedure procedure_name;语句查看
您可以使用sys系统库本身来启用所有需要的instruments和consumers：
* 启用所有wait instruments：
CALLsys.ps_setup_enable_instrument('wait');
* 启用所有stage instruments：
CALLsys.ps_setup_enable_instrument('stage');
* 启用所有statement instruments：
CALLsys.ps_setup_enable_instrument('statement');
* 启用所有事件类型的current表：
CALLsys.ps_setup_enable_consumer('current');
* 启用所有事件类型的history_long表：
CALLsys.ps_setup_enable_consumer('history_long');
* 注意：performance_schema的默认配置就可以满足sys系统库的大部分数据收集功能。启用上述所提及的所有instruments和consumers会对性能产生一定影响，因此最好仅启用所需的配置。如果你在启用了一些默认配置之外的配置，则可以使用存储过程：CALLsys.ps_setup_reset_to_default(TRUE); 来快速恢复到performance_schema的默认配置
PS：对于以上繁杂的权限要求，通常创建一个具有管理员权限的账号即可，当然如果你有明确的需求，那另当别论，但sys系统库通常都是提供给专业的DBA人员排查一些特定问题使用的，其下所涉及的各项查询或多或少都会对性能有一定影响（主要体现在performance_schema功能实现的性能开销），在不明需求的情况下，不建议开放这些功能来作为常规的监控手段使用。
2. sys系统库初体验
当你使用了use语句切换默认数据库，那么就可以直接使用sys系统库下的视图名称进行查询，就像查询某个库下的表一样操作，如下：
# version视图可以查看sys 系统库和mysql server的版本号
mysql> USE sys;
mysql> SELECT * FROM version;
+ ------------- + ----------------- +
| sys_version | mysql_version |
+ ------------- + ----------------- +
| 1.5.0 | 5.7.9-debug-log |
+ ------------- + ----------------- +
也可以使用db_name.view_name、db_name.procedure_name、db_name.func_name等方式在不指定默认数据库的情况下访问sys 系统库中的对象(这叫做名称限定对象引用)，如下：
mysql> SELECT * FROM sys.version;
+ ------------- + ----------------- +
| sys_version | mysql_version |
+ ------------- + ----------------- +
| 1.5.0 | 5.7.9-debug-log |
+ ------------- + ----------------- +
PS：下文中的示例中，对于sys 系统库的访问都是假定指定了默认数据库为sys 系统库。
sys 系统库下包含许多视图，它们以各种方式对performance_schema表进行聚合计算展示。这些视图中大部分都是成对出现，两个视图名称相同，但有一个视图是带'x$'字符前缀的，例如：host_summary_by_file_io和x$host_summary_by_file_io，代表按照主机进行汇总统计的文件I/O性能数据，两个视图访问数据源是相同的，但是创建视图的语句中，不带x$的视图是把相关数值数据经过单位换算再显示的(显示为毫秒、秒、分钟、小时、天等)，带x$前缀的视图显示的是原始的数据(皮秒)，如下：
# x$host_summary_by_file_io视图汇总数据，显示未格式化的皮秒单位延迟时间，没有x$前缀字符的视图输出的信息经过单位换算之后可读性更高
mysql> SELECT * FROM host_summary_by_file_io;
+------------+-------+------------+
| host | ios | io_latency |
+------------+-------+------------+
| localhost | 67570 | 5.38 s |
| background | 3468 | 4.18 s |
+------------+-------+------------+
# 对于带x$的视图显示原始的皮秒单位数值，对于程序或工具获取使用更易于数据处理
mysql> SELECT * FROM x$host_summary_by_file_io;
+------------+-------+---------------+
| host | ios | io_latency |
+------------+-------+---------------+
| localhost | 67574 | 5380678125144 |
| background | 3474 | 4758696829416 |
+------------+-------+---------------+
要查看sys 系统库对象定义语句，可以使用适当的SHOW语句或INFORMATION_SCHEMA库查询。例如，要查看session视图和format_bytes()函数的定义，可以使用如下语句：
mysql> SHOW CREATE VIEW session;
mysql> SHOW CREATE FUNCTION format_bytes;
然而，这些语句文本是经过格式化的，可读性比较差。要查看更易读的格式对象定义语句，可以访问sys 系统库开发网站https://github.com/mysql/mysql-sys上的各个.sql文件，或者使用mysqldump与mysqlpump工具导出sys库，默认情况下，mysqldump和mysqlpump都不会导出sys 系统库。要生成包含sys 系统库的导出文件，可以使用如下命令显式指定sys 系统库（虽然可以导出视图定义，但是与原始的定义语句相比仍然缺失了相当一部分内容，只是可读性比直接show create view要好一些）：
mysqldump --databases --routines sys> sys_dump.sql
mysqlpump sys> sys_dump.sql
如果要重新导入sys 系统库，可以使用如下命令：
mysql < sys_dump.sql
3. sys 系统库的进度报告功能
从MySQL 5.7.9开始，sys 系统库视图提供查看长时间运行的事务的进度报告，通过processlist和session以及x$前缀的视图进行查看，其中processlist包含了后台线程和前台线程当前的事件信息，session不包含后台线程和command为Daemon的线程，如下：
processlist
session
x$processlist
x$session
session视图是直接调用processlist视图过滤了后台线程和command为Daemon的线程（所以两个视图输出结果的字段相同），而processlist线程联结查询了threads、events_waits_current、events_stages_current、events_statements_current、events_transactions_current、sys.x$memory_by_thread_by_current_bytes、session_connect_attrs表，so，需要打开相应的instruments和consumers，否则谁没打开谁对应的信息字段列就为NULL，对于trx_state字段为ACTIVE的线程，progress可以输出百分比进度信息(只有支持进度的事件才会被统计并打印进来)
查询示例
# 查看当前正在执行的语句进度信息
admin@localhost : sys 06:57:21> select * from session where conn_id!=connection_id() and trx_state='ACTIVE'\G;
*************************** 1. row ***************************
thd_id: 47
conn_id: 5
user: admin@localhost
db: sbtest
command: Query
state: alter table (merge sort)
time: 29
current_statement: alter table sbtest1 add index i_c(c)
statement_latency: 29.34 s
progress: 49.70
lock_latency: 4.34 ms
rows_examined: 0
rows_sent: 0
rows_affected: 0
tmp_tables: 0
tmp_disk_tables: 0
full_scan: NO
last_statement: NULL
last_statement_latency: NULL
current_memory: 4.52 KiB
last_wait: wait/io/file/innodb/innodb_temp_file
last_wait_latency: 369.52 us
source: os0file.ic:470
trx_latency: 29.45 s
trx_state: ACTIVE
trx_autocommit: YES
pid: 4667
program_name: mysql
1 row in set (0.12 sec)
# 查看已经执行完的语句相关统计信息
admin@localhost : sys 07:02:21> select * from session where conn_id!=connection_id() and trx_state='COMMITTED'\G;
*************************** 1. row ***************************
thd_id: 47
conn_id: 5
user: admin@localhost
db: sbtest
command: Sleep
state: NULL
time: 372
current_statement: NULL
statement_latency: NULL
progress: NULL
lock_latency: 4.34 ms
rows_examined: 0
rows_sent: 0
rows_affected: 0
tmp_tables: 0
tmp_disk_tables: 0
full_scan: NO
last_statement: alter table sbtest1 add index i_c(c)
last_statement_latency: 1.61 m
current_memory: 4.52 KiB
last_wait: idle
last_wait_latency: Still Waiting
source: socket_connection.cc:69
trx_latency: 1.61 m
trx_state: COMMITTED
trx_autocommit: YES
pid: 4667
program_name: mysql
1 row in set (0.12 sec)
对于stage事件进度报告要求必须启用events_stages_current consumers，启用需要查看进度相关的instruments。例如：
stage/sql/Copying to tmp table
stage/innodb/alter table (end)
stage/innodb/alter table (flush)
stage/innodb/alter table (insert)
stage/innodb/alter table (log apply index)
stage/innodb/alter table (log apply table)
stage/innodb/alter table (merge sort)
stage/innodb/alter table (read PK and internal sort)
stage/innodb/buffer pool load
对于不支持进度的stage 事件，或者未启用所需的instruments或consumers的stage事件，则对应的进度信息列显示为NULL。
sys 系统库配置
1. sys_config表
该表包含sys系统库的配置选项，每个配置选项一行记录。该表是innodb表，可以通过客户端更新此表来持久化配置，server重启不会丢失。
sys_config表字段含义如下：
variable：配置选项名称
value：配置选项值
set_time：该行配置最近修改时间
set_by：最近一次对改行配置进行修改的帐户名。如果自server安装sys 系统库以来，该行配置从未被更改过，则该列值为NULL
为了减少对sys_config表直接读取的次数，sys 系统库中的视图、存储过程在需要使用到这些配置选项时，会优先检查这些配置选项对应的用户自定义配置选项变量(用户自定义配置选项变量与该表中的配置选项都具有相同的名称，例如：表中的diagnostics.include_raw选项，对应的自定义配置选项变量是@sys.diagnostics.include_raw)。如果用户定义的配置选项变量存在于当前会话作用域中并且是非空的，那么sys 系统库中的函数、存储过程将优先使用该配置选项变量值。否则，该sys 系统库函数和存储过程将使用sys_config表中的配置选项值(从表中读取配置选项值之后，会将sys_config表中的配置选项时同时更新到用户自定义配置选项变量中，以便在同一会话后续对该值的引用时使用变量值，而不必再次从sys_config表中读取)，
示例：statement_truncate_len配置选项控制format_statement()函数返回的语句的最大长度。默认值为64.如果要临时将当前会话的值更改为32，可以设置对应的@sys.statement_truncate_len用户定义的配置选项变量：
# statement_truncate_len配置选项默认是64，直接调用format_statement()函数返回是64字节长度，在未调用任何涉及到该配置选项的函数之前，该自定义变量值为NULL，此时函数需要从表中查询默认值
admin@localhost : sys 11:47:37> select @sys.statement_truncate_len;
+-----------------------------+
| @sys.statement_truncate_len |
+-----------------------------+
| NULL |
+-----------------------------+
1 row in set (0.00 sec)
admin@localhost : sys 11:51:53> SET @stmt = 'SELECT variable, value, set_time, set_by FROM sys_config';
Query OK, 0 rows affected (0.00 sec)
admin@localhost : sys 11:52:04> SELECT format_statement(@stmt);
+----------------------------------------------------------+
| format_statement(@stmt) |
+----------------------------------------------------------+
| SELECT variable, value, set_time, set_by FROM sys_config |
+----------------------------------------------------------+
1 row in set (0.01 sec)
# 调用过一次format_statement()函数之后，表中的默认值会被更新到该自定义配置选项变量中
admin@localhost : sys 11:52:12> select @sys.statement_truncate_len;
+-----------------------------+
| @sys.statement_truncate_len |
+-----------------------------+
| 64 |
+-----------------------------+
1 row in set (0.00 sec)
# 在会话级别中修改为32
admin@localhost : sys 11:52:20> set @sys.statement_truncate_len = 32;
Query OK, 0 rows affected (0.00 sec)
admin@localhost : sys 11:52:34> select @sys.statement_truncate_len;
+-----------------------------+
| @sys.statement_truncate_len |
+-----------------------------+
| 32 |
+-----------------------------+
1 row in set (0.00 sec)
# 再次调用format_statement()函数值，可以发现返回结果中的长度缩短了，说明使用了session级别修改的值32
admin@localhost : sys 11:52:41> SELECT format_statement(@stmt);
+-----------------------------------+
| format_statement(@stmt) |
+-----------------------------------+
| SELECT variabl ... ROM sys_config |
+-----------------------------------+
1 row in set (0.00 sec)
要停止使用用户定义的配置选项变量并恢复使用sys_config表中的值，可以将会话中的配置选项变量设置为NULL，或者结束当前会话（结束会话会使得用户定义的变量被销毁）重新开启一个新的会话：
mysql> SET @sys.statement_truncate_len = NULL;
mysql> SELECT format_statement(@stmt);
+----------------------------------------------------------+
| format_statement(@stmt) |
+----------------------------------------------------------+
| SELECT variable, value, set_time, set_by FROM sys_config |
+----------------------------------------------------------+
注意：如果用户在会话中设置了自定义配置选项变量值，然后再更新了sys_config表中相同名称的配置选项，则对于当前会话，sys_config表中的配置选项值不生效（除非设置自定义配置选项变量值为NULL），只对于新的会话且不存在自定义配置选项变量或者自定义配置选项值为NULL生效（因为此时会从sys_config表中读取）
sys_config表中的选项和相应的用户定义的配置选项变量相关描述如下：
diagnostics.allow_i_s_tables，@sys.diagnostics.allow_i_s_tables：
如果此选项为ON，则diagnostics()存储过程在调用时会扫描INFORMATION_SCHEMA.TABLES表找到所有的基表与STATISTICS表执行联结查询，扫描每个表的统计信息。如果基表非常多，该操作可能比较昂贵。默认为OFF。此选项在MySQL 5.7.9中新增
diagnostics.include_raw，@sys.diagnostics.include_raw：
如果此选项为ON，则diagnostics()存储过程的输出信息中会包括metrics视图中的原始输出信息（该存储过程中会调用metrics视图）。默认为OFF。此选项在MySQL 5.7.9中新增
ps_thread_trx_info.max_length,@sys.ps_thread_trx_info.max_length：
由ps_thread_trx_info()函数生成的JSON输出结果的最大长度。默认值为65535字节。此选项在MySQL 5.7.9中新增
statement_performance_analyzer.limit,@sys.statement_performance_analyzer.limit：
不具有内置限制的视图返回的最大行数。默认值为100（例如，statements_with_runtimes_in_95th_percentile视图具有内置限制，即只返回平均执行时间为占总执行时间分布的95百分位数的语句）。此选项在MySQL 5.7.9中新增
statement_performance_analyzer.view,@sys.statement_performance_analyzer.view：
给statement_performance_analyzer()存储过程当作入参使用的自定义查询或视图名称（statement_performance_analyzer()存储过程由diagnostics()存储过程内部调用）。如果该选项值包含空格，则将其值解释为查询语句。否则解释为视图名称，且这个视图必须是提前创建好的用于查询performance_schema.events_statements_summary_by_digest表的视图。如果statement_performance_analyzer.limit配置选项值大于0，则statement_performance_analyzer.view配置选项指定的查询语句或视图中不能有任何LIMIT子句(因为statement_performance_analyzer.limit选项在statement_performance_analyzer()存储过程中是作为一个条件判断值决定是否要添加一个LIMIT子句，如果你再自行添加一个LIMIT会导致语法错误)。statement_performance_analyzer.view配置选项默认值为NULL。此选项在MySQL 5.7.9中新增
statement_truncate_len，@sys.statement_truncate_len：
控制format_statement()函数返回的语句文本的最大长度。超过该长度的语句文本会被截断，只保留该配置选项定义的长度文本。默认值为64字节
其他选项可以被添加到sys_config表中。例如：如果存在debug配置选项且不为null值，则diagnostics()和execute_prepared_stmt()存储过程调用时会执行检查并做相应的判断，但默认情况下，此选项在sys_config表中不存在，因为debug输出通常只能临时启用，通过会话级别设置自定义配置选项变量实现，如：set @sys.debug='ON';
# 如果所有会话都需要使用，则可以将debug选项insert到sys_config表中
mysql> INSERT INTO sys_config (variable, value) VALUES('debug', 'ON');
# 要更改表中的调试配置选项值，可以使用update语句更新该配置选项值
## 首先，修改表中的值：
mysql> UPDATE sys_config SET value = 'OFF' WHERE variable = 'debug';
## 然后，为了确保当前会话中的存储过程调用时使用表中的更改后的值，需要将相应的用户定义的变量设置为NULL
mysql> SET @sys.debug = NULL;
记录内容示例
admin@localhost : sys 09:48:46> select * from sys_config;
+--------------------------------------+-------+---------------------+--------+
| variable | value | set_time | set_by |
+--------------------------------------+-------+---------------------+--------+
| diagnostics.allow_i_s_tables | OFF | 2017-07-06 12:43:53 | NULL |
| diagnostics.include_raw | OFF | 2017-07-06 12:43:53 | NULL |
| ps_thread_trx_info.max_length | 65535 | 2017-07-06 12:43:53 | NULL |
| statement_performance_analyzer.limit | 100 | 2017-07-06 12:43:53 | NULL |
| statement_performance_analyzer.view | NULL | 2017-07-06 12:43:53 | NULL |
| statement_truncate_len | 64 | 2017-07-06 12:43:53 | NULL |
+--------------------------------------+-------+---------------------+--------+
6 rows in set (0.00 sec)
PS：对sys_config表的insert和update操作会触发sys_config_insert_set_user和sys_config_update_set_user触发器，而该触发器在5.7.x版本中新增了一个用户mysql.sys，且这俩触发器定义时指定了DEFINER=mysql.sys
@localhost
（表示该触发器只能用mysql.sys用户调用），so..该用户必须存在(对MySQL
做安全加固的小朋友要注意了，别直接对mysql.user表做truncate之类的操作，先看一眼表中存在着哪些用户)，否则对sys_config表操作时就算是超级管理员用户也无法修改（报错：ERROR
1449 (HY000): The user specified as a definer ('mysql.sys'@'localhost') does not
exist），如果不小心删除了mysql.sys用户 ，可以使用如下语句重新创建(注意：使用create语句创建用户会失败，报错：ERROR 1396
(HY000): Operation CREATE USER failed for
'mysql.sys'@'localhost'，所以，强烈不建议删除mysql.sys用户，因为grant创建用户的语法即将废弃，当然，如果在不支持grant语句创建用户的MySQL版本中删了mysql.sys用户，也有办法补救，比如：直接insert用户权限表或者drop掉触发器再指定INVOKER=mysql.sys
@localhost
)
grant TRIGGER on sys.* to 'mysql.sys'@'localhost' identified by 'letsg0';
# 注意：mysql.sys用户初始化默认对表sys.sys_config表只有select权限，无法调用sys_config_insert_set_user和sys_config_update_set_user触发器完成更新set_by字段为当前操作用户名，会报错
# ERROR 1143 (42000): UPDATE command denied to user 'mysql.sys'@'localhost' for column 'set_by' in table 'sys_config'，所以要实现这个功能，针对sys.sys_config表还需要添加insert和update权限给mysql.sys用户
grant select,insert,update on sys.sys_config to 'mysql.sys'@'localhost' identified by 'letsg0';
2. sys_config_insert_set_user触发器
当对sys_config表执行INSERT语句添加配置选项行时，sys_config_insert_set_user触发器会将sys_config表的set_by列设置为当前用户名。
注意事项：要使得该触发器生效，有如下三个条件：
* mysql.sys用户必须存在，因为定义语句中DEFINER='mysql.sys'@'localhost' 表示只有该用户才能够调用该触发器，当然，为了方便，你可以删掉这个触发器，然后使用INVOKER='mysql.sys'@'localhost'子句创建
* mysql.sys用户初始化默认对sys.sys_config表只有select权限，无法调用sys_config_insert_set_user和sys_config_update_set_user触发器完成更新set_by字段为当前操作用户名，会报错ERROR 1143 (42000): UPDATE command denied to user 'mysql.sys'@'localhost' for column 'set_by' in table 'sys_config'，所以要实现这个功能，针对sys.sys_config表还需要添加insert和update权限给mysql.sys用户
* @sys.ignore_sys_config_triggers自定义变量必须为0值，任何非0值将导致该触发器不执行更新set_by字段操作
sys_config_insert_set_user触发器定义语句如下：
DROP TRIGGER IF EXISTS sys_config_insert_set_user;
DELIMITER $$
CREATE DEFINER='mysql.sys'@'localhost' TRIGGER sys_config_insert_set_user BEFORE INSERT on sys_config
FOR EACH ROW
BEGIN
IF @sys.ignore_sys_config_triggers != true AND NEW.set_by IS NULL THEN
SET NEW.set_by = USER();
END IF;
END$$
DELIMITER ;
3. sys_config_update_set_user触发器
当对sys_config表执行UPDATE语句添加配置选项行时，sys_config_update_set_user触发器会将sys_config表的set_by列设置为当前用户名
注意事项：同sys_config_insert_set_user触发器注意事项
sys_config_update_set_user触发器定义语句如下：
DROP TRIGGER IF EXISTS sys_config_update_set_user;
DELIMITER $$
CREATE DEFINER='mysql.sys'@'localhost' TRIGGER sys_config_update_set_user BEFORE UPDATE on sys_config
FOR EACH ROW
BEGIN
IF @sys.ignore_sys_config_triggers != true AND NEW.set_by IS NULL THEN
SET NEW.set_by = USER();
END IF;
END$$
DELIMITER ;
03 按 host 分组统计视图
01 host_summary_by_file_io,x$host_summary_by_file_io
按主机（与用户账号组成中的host值相同）分组统计的文件I/O的IO总数和IO延迟时间，默认按照总I/O等待时间降序排序。数据来源：performance_schema.events_waits_summary_by_host_by_event_name表，调用了sys.format_time()自定义函数、sum()聚合函数对查询结果进行求和运算并转换时间单位。
下面我们看看使用该视图查询返回的结果集。
# 从查询的结果中可以看到，延迟时间带有单位秒，对人类来说更易读
mysql> SELECT * FROM host_summary_by_file_io;
+------------+-------+------------+
| host | ios | io_latency |
+------------+-------+------------+
| localhost | 67570 | 5.38 s |
| background | 3468 | 4.18 s |
+------------+-------+------------+
# 带x$前缀的同名视图范围的时间值未经过可读格式装换，单位为皮秒（万亿分之一秒，可读性比较差）
mysql> SELECT * FROM x$host_summary_by_file_io;
+------------+-------+---------------+
| host | ios | io_latency |
+------------+-------+---------------+
| localhost | 67574 | 5380678125144 |
| background | 3474 | 4758696829416 |
+------------+-------+---------------+
视图字段含义如下：
host：客户端连接的主机名或IP。在Performance Schema表中的HOST列为NULL的行在这里假定为后台线程，且在该视图host列显示为background
ios：文件I/O事件总次数，即可以认为就是io总数
io_latency：文件I/O事件的总等待时间（执行时间）
PS：没有x$前缀的视图旨在提供对用户更加友好和更易于阅读的输出格式。而带x$前缀的视图输出的原始格式值更适用于一些工具类的程序使用。没有x$前缀的视图中将会调用如下函数中的一个或者多个进行数值单位转换再输出（后续其他视图的可读格式转换视图相同，下文不再赘述）：
字节值使用format_bytes()函数格式化并转换单位，详见后续章节
时间值使用format_time()函数格式化并转换单位。详见后续章节
使用format_statement()函数将SQL语句文本截断为statement_truncate_len配置选项设置的显示宽度。详见后续章节
路径名称使用format_path()函数截取并替换为相应的系统变量名称。详见后续章节
该视图只统计文件IO等待事件信息("wait/io/file/%")
02 host_summary,x$ host_summary
按照主机分组统计的语句延迟（执行）时间、次数、相关的文件I/O延迟、连接数和内存分配大小等摘要信息，数据来源：performance_schema.accounts、sys.x$host_summary_by_statement_latency、sys.x$host_summary_by_file_io
下面我们看看使用该视图查询返回的结果集。
# 不带x$前缀的视图
root@localhost : sys 12:38:11> select * from host_summary limit 1\G
*************************** 1. row ***************************
host: 192.168.2.122
statements: 9
statement_latency: 13.22 ms
statement_avg_latency: 1.47 ms
table_scans: 0
file_ios: 11
file_io_latency: 53.33 us
current_connections: 1
total_connections: 1
unique_users: 1
current_memory: 0 bytes
total_memory_allocated: 0 bytes
1 row in set (0.01 sec)
# 带x$前缀的视图
root@localhost : sys 12:38:14> select * from x$host_summary limit 1\G
*************************** 1. row ***************************
host: 192.168.2.122
statements: 9
statement_latency: 13218739000
statement_avg_latency: 1468748777.7778
table_scans: 0
file_ios: 11
file_io_latency: 53332848
current_connections: 1
total_connections: 1
unique_users: 1
current_memory: 0
total_memory_allocated: 0
1 row in set (0.01 sec)
视图字段含义如下：
host：客户端连接的主机名或IP。在Performance Schema表中的HOST列为NULL的行在这里假定为后台线程，且在该视图host列显示为background
statements：语句总执行次数
statement_latency：语句总延迟时间（执行时间）
statement_avg_latency：语句的平均延迟时间(执行时间)
table_scans：语句的表扫描总次数
file_ios：文件I/O事件总次数
file_io_latency：文件I/O事件总延迟时间（执行时间）
current_connections：当前连接数
total_connections：总历史连接数
unique_users：不同（去重）用户数量
current_memory：当前内存使用量
total_memory_allocated：总的内存分配量
PS：该视图只统计文件IO等待事件信息("wait/io/file/%")
03 host_summary_by_file_io_type,x$host_summary_by_file_io_type
按照主机和事件名称分组的文件I/O事件次数、延迟统计信息，默认按照主机和总I/O延迟时间降序排序。数据来源：performance_schema.events_waits_summary_by_host_by_event_name，调用了sys.format_time()自定义函数转换时间单位。
下面我们看看使用该视图查询返回的结果集。
# 不带x$前缀的视图
root@localhost : sys 12:39:51> select * from host_summary_by_file_io_type limit 3;
+---------------+--------------------------------------+-------+---------------+-------------+
| host | event_name | total | total_latency | max_latency |
+---------------+--------------------------------------+-------+---------------+-------------+
| 192.168.2.122 | wait/io/file/sql/binlog | 11 | 53.33 us | 24.33 us |
| background | wait/io/file/innodb/innodb_data_file | 1631 | 5.85 s | 35.48 ms |
| background | wait/io/file/sql/FRM | 2151 | 3.89 s | 26.10 ms |
+---------------+--------------------------------------+-------+---------------+-------------+
3 rows in set (0.01 sec)
# 带x$前缀的视图
root@localhost : sys 12:39:54> select * from x$host_summary_by_file_io_type limit 3;
+---------------+--------------------------------------+-------+---------------+-------------+
| host | event_name | total | total_latency | max_latency |
+---------------+--------------------------------------+-------+---------------+-------------+
| 192.168.2.122 | wait/io/file/sql/binlog | 11 | 53332848 | 24334839 |
| background | wait/io/file/innodb/innodb_data_file | 1631 | 5851714703037 | 35476899531 |
| background | wait/io/file/sql/FRM | 2151 | 3894316306089 | 26099526756 |
+---------------+--------------------------------------+-------+---------------+-------------+
3 rows in set (0.00 sec)
视图字段含义如下：
host：客户端连接的主机名或IP。在Performance Schema表中的HOST列为NULL的行在这里假定为后台线程，且在该视图host列显示为background
EVENT_NAME：文件I/O事件名称
total：文件I/O事件发生总次数
total_latency：文件I/O事件的总延迟时间（执行时间）
max_latency：文件I/O事件的单次最大延迟时间（执行时间）
PS：该视图只统计文件IO等待事件信息("wait/io/file/%")
04 host_summary_by_stages,x$host_summary_by_stages
按照主机和事件名称分组的阶段事件总次数、总执行时间、平均执行时间等统计信息，默认按照主机和总的延迟（执行）时间降序排序。数据来源：performance_schema.events_stages_summary_by_host_by_event_name，调用了sys.format_time()自定义函数转换时间单位。
下面我们看看使用该视图查询返回的结果集。
# 不带x$前缀的视图
root@localhost : sys 12:39:57> select * from host_summary_by_stages limit 3;
+------------+-------------------------------+-------+---------------+-------------+
| host | event_name | total | total_latency | avg_latency |
+------------+-------------------------------+-------+---------------+-------------+
| background | stage/innodb/buffer pool load | 1 | 4.68 s | 4.68 s |
+------------+-------------------------------+-------+---------------+-------------+
1 row in set (0.00 sec)
# 带x$前缀的视图
root@localhost : sys 12:40:15> select * from x$host_summary_by_stages limit 3;
+------------+-------------------------------+-------+---------------+---------------+
| host | event_name | total | total_latency | avg_latency |
+------------+-------------------------------+-------+---------------+---------------+
| background | stage/innodb/buffer pool load | 1 | 4678671071000 | 4678671071000 |
+------------+-------------------------------+-------+---------------+---------------+
1 row in set (0.00 sec)
视图字段含义如下：
host：客户端连接的主机名或IP。在Performance Schema表中的HOST列为NULL的行在这里假定为后台线程，且在该视图host列显示为background
EVENT_NAME：阶段事件名称
total：阶段事件总发生次数
total_latency：阶段事件总延迟(执行)时间
avg_latency：阶段事件平均延迟(执行)时间
05 host_summary_by_statement_latency,x$host_summary_by_statement_latency
按照主机和事件名称分组的语句事件总次数、总执行时间、最大执行时间、锁时间以及数据行相关的统计信息，默认按照总延迟（执行）时间降序排序。数据来源：performance_schema.events_statements_summary_by_host_by_event_name
下面我们看看使用该视图查询返回的结果集。
# 不带x$前缀的视图
root@localhost : sys 12:40:19> select * from host_summary_by_statement_latency limit 3;
+---------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| host | total | total_latency | max_latency | lock_latency | rows_sent | rows_examined | rows_affected | full_scans |
+---------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| localhost | 3447 | 539.61 ms | 89.37 ms | 131.90 ms | 3023 | 40772 | 0 | 108 |
| 192.168.2.122 | 9 | 13.22 ms | 12.55 ms | 0 ps | 5 | 0 | 0 | 0 |
| background | 0 | 0 ps | 0 ps | 0 ps | 0 | 0 | 0 | 0 |
+---------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
3 rows in set (0.01 sec)
# 带x$前缀的视图
root@localhost : sys 12:40:36> select * from x$host_summary_by_statement_latency limit 3;
+---------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| host | total | total_latency | max_latency | lock_latency | rows_sent | rows_examined | rows_affected | full_scans |
+---------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| localhost | 3528 | 544883806000 | 89365202000 | 132140000000 | 3026 | 41351 | 0 | 109 |
| 192.168.2.122 | 9 | 13218739000 | 12550251000 | 0 | 5 | 0 | 0 | 0 |
| background | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
+---------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
3 rows in set (0.01 sec)
视图字段含义如下：
host：客户端连接的主机名或IP。在Performance Schema表中的HOST列为NULL的行在这里假定为后台线程，且在该视图host列显示为background
total：语句总执行次数
total_latency：语句总延迟(执行)时间
max_latency：语句单个最大延迟(执行)时间
lock_latency：语句总锁延迟(执行）时间
rows_sent：语句返回给客户端的总数据行数
rows_examined：语句从存储引擎层读取的总数据行数
rows_affected：语句执行时受影响（DML会返回数据发生变更的受影响行数，select等不会产生数据变更的语句执行时不会有受影响行数返回）的总数据行数
full_scans：语句全表扫描总次数
06 host_summary_by_statement_type,x$host_summary_by_statement_type
按照主机和语句分组的当前语句事件总次数、总执行时间、最大执行时间、锁时间以及数据行相关的统计信息（与performance_schema.host_summary_by_statement_latency 视图比起来，该视图只返回执行时间不为0的统计信息，且多了一个statement字段显示语句事件名称层级中的最后一部分字符），数据来源:performance_schema.events_statements_summary_by_host_by_event_name
下面我们看看使用该视图查询返回的结果集。
# 不带x$前缀的视图
root@localhost : sys 12:40:40> select * from host_summary_by_statement_type limit 3;
+---------------+----------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| host | statement | total | total_latency | max_latency | lock_latency | rows_sent | rows_examined | rows_affected | full_scans |
+---------------+----------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| 192.168.2.122 | select | 5 | 12.92 ms | 12.55 ms | 0 ps | 5 | 0 | 0 | 0 |
| 192.168.2.122 | set_option | 3 | 258.22 us | 166.40 us | 0 ps | 0 | 0 | 0 | 0 |
| 192.168.2.122 | Register Slave | 1 | 37.68 us | 37.68 us | 0 ps | 0 | 0 | 0 | 0 |
+---------------+----------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
3 rows in set (0.00 sec)
# 带x$前缀的视图
root@localhost : sys 12:41:00> select * from x$host_summary_by_statement_type limit 3;
+---------------+----------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| host | statement | total | total_latency | max_latency | lock_latency | rows_sent | rows_examined | rows_affected | full_scans |
+---------------+----------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| 192.168.2.122 | select | 5 | 12922834000 | 12550251000 | 0 | 5 | 0 | 0 | 0 |
| 192.168.2.122 | set_option | 3 | 258224000 | 166400000 | 0 | 0 | 0 | 0 | 0 |
| 192.168.2.122 | Register Slave | 1 | 37681000 | 37681000 | 0 | 0 | 0 | 0 | 0 |
+---------------+----------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
3 rows in set (0.01 sec)
视图字段含义如下：
statement：显示语句事件名称层级中的最后一部分字符，如：statement/com/Prepare instruments，在statement字段中就显示Prepare
其他字段含义与performance_schema.host_summary_by_statement_latency 视图字段含义相同
04 按 user 分组统计视图
01 user_summary,x$user_summary
查看活跃连接中按用户分组的总执行时间、平均执行时间、总的IOS、总的内存使用量、表扫描数量等统计信息，默认按照总延迟时间(执行时间)降序排序。数据来源：performance_schema.accounts、sys.x$user_summary_by_statement_latency、sys.x$user_summary_by_file_io、sys.x$memory_by_user_by_current_bytes
下面我们看看使用该视图查询返回的结果。
# 不带x$前缀的视图
admin@localhost : sys 12:54:32> select * from user_summary limit 1\G
*************************** 1. row ***************************
user: admin
statements: 90530
statement_latency: 2.09 h
statement_avg_latency: 83.12 ms
table_scans: 498
file_ios: 60662
file_io_latency: 31.05 s
current_connections: 4
total_connections: 1174
unique_hosts: 2
current_memory: 85.34 MiB
total_memory_allocated: 7.21 GiB
1 row in set (0.04 sec)
# 带x$前缀的视图
admin@localhost : sys 12:55:48> select * from x$user_summary limit 1\G
*************************** 1. row ***************************
user: admin
statements: 90752
statement_latency: 7524792139504000
statement_avg_latency: 82915992369.3583
table_scans: 500
file_ios: 60662
file_io_latency: 31053125849250
current_connections: 4
total_connections: 1174
unique_hosts: 2
current_memory: 89381384
total_memory_allocated: 7755173436
1 row in set (0.02 sec)
视图字段含义如下：
user：客户端访问用户名。如果在performance_schema表中user列为NULL，则假定为后台线程，该字段为'background',如果为前台线程，则该字段对应具体的用户名
statements：对应用户执行的语句总数量
statement_latency：对应用户执行的语句总延迟时间(执行时间)
statement_avg_latency：对应用户执行的语句中，平均每个语句的延迟时间(执行时间)(SUM(stmt.total_latency/SUM(stmt.total))
table_scans：对应用户执行的语句发生表扫描总次数
file_ios：对应用户执行的语句产生的文件I/O事件总次数
file_io_latency：对应用户执行的语句产生的文件I/O事件的总延迟时间(执行时间)
current_connections：对应用户的当前连接数
total_connections：对应用户的历史总连接数
unique_hosts：对应用户来自不同主机(针对主机名去重)连接的数量
current_memory：对应用户的连接当前已使用的内存分配量
total_memory_allocated：对应用户的连接的历史内存分配量
PS：该视图只统计文件IO等待事件信息("wait/io/file/%")
02 user_summary_by_file_io,x$user_summary_by_file_io
按照用户分组的文件I/O延迟时间、IOS统计信息，默认按照总文件I/O时间延迟时间(执行时间)降序排序。数据来源：performance_schema.events_waits_summary_by_user_by_event_name
下面我们看看使用该视图查询返回的结果。
# 不带x$前缀的视图
admin@localhost : sys 12:56:18> select * from user_summary_by_file_io limit 3;
+------------+-------+------------+
| user | ios | io_latency |
+------------+-------+------------+
| admin | 30331 | 15.53 s |
| background | 10119 | 2.49 s |
| qfsys | 281 | 4.69 ms |
+------------+-------+------------+
3 rows in set (0.01 sec)
# 带x$前缀的视图
admin@localhost : sys 12:56:21> select * from x$user_summary_by_file_io limit 3;
+------------+-------+----------------+
| user | ios | io_latency |
+------------+-------+----------------+
| admin | 30331 | 15526562924625 |
| background | 10122 | 2489231563125 |
| qfsys | 281 | 4689150375 |
+------------+-------+----------------+
3 rows in set (0.00 sec)
视图字段含义如下：
user：客户端用户名。如果在performance_schema表中user列为NULL，则假定为后台线程，该字段为'background',如果为前台线程，则该字段对应具体的用户名
ios：对应用户的文件I/O事件总次数
io_latency：对应用户的文件I/O事件的总延迟时间(执行时间)
PS：该视图只统计文件IO等待事件信息("wait/io/file/%")
03 user_summary_by_file_io_type,x$user_summary_by_file_io_type
按照用户和事件类型(事件名称)分组的文件I/O延迟和IOS统计信息，默认情况下按照用户名和总文件I/O时间延迟时间(执行时间)降序排序。数据来源：performance_schema.events_waits_summary_by_user_by_event_name
下面我们看看使用该视图查询返回的结果。
# 不带x$前缀的视图
admin@localhost : sys 12:56:24> select * from user_summary_by_file_io_type limit 3;
+-------+-------------------------------------+-------+---------+-------------+
| user | event_name | total | latency | max_latency |
+-------+-------------------------------------+-------+---------+-------------+
| admin | wait/io/file/sql/io_cache | 27955 | 10.53 s | 67.61 ms |
| admin | wait/io/file/innodb/innodb_log_file | 912 | 2.14 s | 28.22 ms |
| admin | wait/io/file/sql/binlog | 879 | 2.05 s | 31.75 ms |
+-------+-------------------------------------+-------+---------+-------------+
3 rows in set (0.00 sec)
# 带x$前缀的视图
admin@localhost : sys 12:56:48> select * from x$user_summary_by_file_io_type limit 3;
+-------+-------------------------------------+-------+----------------+-------------+
| user | event_name | total | latency | max_latency |
+-------+-------------------------------------+-------+----------------+-------------+
| admin | wait/io/file/sql/io_cache | 27955 | 10534662677625 | 67608294000 |
| admin | wait/io/file/innodb/innodb_log_file | 912 | 2143870695375 | 28216455000 |
| admin | wait/io/file/sql/binlog | 879 | 2054976453000 | 31745275125 |
+-------+-------------------------------------+-------+----------------+-------------+
3 rows in set (0.01 sec)
视图字段含义如下：
user：客户端用户名。如果在performance_schema表中user列为NULL，则假定为后台线程，该字段为'background',如果为前台线程，则该字段对应具体的用户名
EVENT_NAME：文件I/O事件名称
total：对应用户发生的文件I/O事件总次数
latency：对应用户的文件I/O事件的总延迟时间(执行时间)
max_latency：对应用户的单次文件I/O事件的最大延迟时间(执行时间)
PS：该视图只统计文件IO等待事件信息("wait/io/file/%")
04 user_summary_by_stages,x$user_summary_by_stages
按用户分组的阶段事件统计信息，默认情况下按照用户名和阶段事件总延迟时间(执行时间)降序排序。数据来源：performance_schema.events_stages_summary_by_user_by_event_name
下面我们看看使用该视图查询返回的结果。
# 不带x$前缀的视图
admin@localhost : sys 12:56:51> select * from user_summary_by_stages limit 3;
+------------+-------------------------------+-------+---------------+-------------+
| user | event_name | total | total_latency | avg_latency |
+------------+-------------------------------+-------+---------------+-------------+
| background | stage/innodb/buffer pool load | 1 | 12.56 s | 12.56 s |
+------------+-------------------------------+-------+---------------+-------------+
1 row in set (0.01 sec)
# 带x$前缀的视图
admin@localhost : sys 12:57:10> select * from x$user_summary_by_stages limit 3;
+------------+-------------------------------+-------+----------------+----------------+
| user | event_name | total | total_latency | avg_latency |
+------------+-------------------------------+-------+----------------+----------------+
| background | stage/innodb/buffer pool load | 1 | 12561724877000 | 12561724877000 |
+------------+-------------------------------+-------+----------------+----------------+
1 row in set (0.00 sec)
视图字段含义如下：
user：客户端用户名。如果在performance_schema表中user列为NULL，则假定为后台线程，该字段为'background',如果为前台线程，则该字段对应具体的用户名
EVENT_NAME：阶段事件名称
total：对应用户的阶段事件的总次数
total_latency：对应用户的阶段事件的总延迟时间(执行时间)
avg_latency：对应用户的阶段事件的平均延迟时间(执行时间)
05 user_summary_by_statement_latency,
x$user_summary_by_statement_latency
按照用户分组的语句统计信息，默认情况下按照语句总延迟时间(执行时间)降序排序。数据来源：performance_schema.events_statements_summary_by_user_by_event_name
下面我们看看使用该视图查询返回的结果。
# 不带x$前缀的视图
admin@localhost : sys 12:57:13> select * from user_summary_by_statement_latency limit 3;
+------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| user | total | total_latency | max_latency | lock_latency | rows_sent | rows_examined | rows_affected | full_scans |
+------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| admin | 45487 | 1.05 h | 45.66 m | 19.02 s | 6065 | 17578842 | 1544 | 258 |
| qfsys | 9 | 929.43 ms | 928.68 ms | 0 ps | 5 | 0 | 0 | 0 |
| background | 0 | 0 ps | 0 ps | 0 ps | 0 | 0 | 0 | 0 |
+------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
3 rows in set (0.00 sec)
# 带x$前缀的视图
admin@localhost : sys 12:57:34> select * from x$user_summary_by_statement_latency limit 3;
+------------+-------+------------------+------------------+----------------+-----------+---------------+---------------+------------+
| user | total | total_latency | max_latency | lock_latency | rows_sent | rows_examined | rows_affected | full_scans |
+------------+-------+------------------+------------------+----------------+-----------+---------------+---------------+------------+
| admin | 45562 | 3762457232413000 | 2739502018445000 | 19019928000000 | 6068 | 17579421 | 1544 | 259 |
| qfsys | 9 | 929429421000 | 928682487000 | 0 | 5 | 0 | 0 | 0 |
| background | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
+------------+-------+------------------+------------------+----------------+-----------+---------------+---------------+------------+
3 rows in set (0.00 sec)
视图字段含义如下：
user：客户端用户名。如果在performance_schema表中user列为NULL，则假定为后台线程，该字段为'background',如果为前台线程，则该字段对应具体的用户名
total：对应用户执行的语句总数量
total_latency：对应用户执行的语句总延迟时间(执行时间)
max_latency：对应用户执行的语句单次最大延迟时间(执行时间)
lock_latency：对应用户执行的语句锁等待的总时间
rows_sent：对应用户执行的语句返回给客户端的总数据行数
rows_examined：对应用户执行的语句从存储引擎读取的总数据行数
rows_affected：对应用户执行的语句影响的总数据行数
full_scans：对应用户执行的语句的全表扫描总次数
06
user_summary_by_statement_type,
x$user_summary_by_statement_type
按用户和语句事件类型（事件类型名称为语句事件的event_name截取最后一部分字符串，也是语句command类型字符串类似）分组的语句统计信息，默认情况下按照用户名和对应语句的总延迟时间(执行时间)降序排序。数据来源：performance_schema.events_statements_summary_by_user_by_event_name
下面我们看看使用该视图查询返回的结果。
# 不带x$前缀的视图
admin@localhost : sys 12:57:38> select * from user_summary_by_statement_type limit 3;
+-------+-------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| user | statement | total | total_latency | max_latency | lock_latency | rows_sent | rows_examined | rows_affected | full_scans |
+-------+-------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| admin | alter_table | 2 | 56.56 m | 43.62 m | 0 ps | 0 | 0 | 0 | 0 |
| admin | select | 3662 | 5.53 m | 2.02 m | 4.73 s | 6000 | 17532984 | 0 | 148 |
| admin | insert | 1159 | 36.04 s | 337.22 ms | 14.23 s | 0 | 0 | 1159 | 0 |
+-------+-------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
3 rows in set (0.00 sec)
# 带x$前缀的视图
admin@localhost : sys 12:57:50> select * from x$user_summary_by_statement_type limit 3;
+-------+-------------+-------+------------------+------------------+----------------+-----------+---------------+---------------+------------+
| user | statement | total | total_latency | max_latency | lock_latency | rows_sent | rows_examined | rows_affected | full_scans |
+-------+-------------+-------+------------------+------------------+----------------+-----------+---------------+---------------+------------+
| admin | alter_table | 2 | 3393877088372000 | 2617456143674000 | 0 | 0 | 0 | 0 | 0 |
| admin | select | 3663 | 331756087959000 | 121243627173000 | 4733109000000 | 6003 | 17533557 | 0 | 149 |
| admin | insert | 1159 | 36041502943000 | 337218573000 | 14229439000000 | 0 | 0 | 1159 | 0 |
+-------+-------------+-------+------------------+------------------+----------------+-----------+---------------+---------------+------------+
3 rows in set (0.00 sec)
视图字段含义如下：
user：客户端用户名。如果在performance_schema表中user列为NULL，则假定为后台线程，该字段为'background',如果为前台线程，则该字段对应具体的用户名
statement：语句事件名称的最后一部分字符串，与语句的command类型字符串类似
其他字段含义与 user_summary_by_statement_latency,x$user_summary_by_statement_latency 视图的字段含义相同
05 按 file 分组统计视图
01
io_by_thread_by_latency,
x$io_by_thread_by_latency
按照thread ID、processlist ID、用户名分组的 I/O等待时间开销统计信息，默认情况下按照总I/O等待时间降序排序。数据来源：performance_schema.events_waits_summary_by_thread_by_event_name、performance_scgema.threads
下面我们看看使用该视图查询返回的结果。
# 不带x$前缀的视图
root@localhost : sys 12:42:44> select * from io_by_thread_by_latency limit 3;
+-----------------+-------+---------------+-------------+-------------+-------------+-----------+----------------+
| user | total | total_latency | min_latency | avg_latency | max_latency | thread_id | processlist_id |
+-----------------+-------+---------------+-------------+-------------+-------------+-----------+----------------+
| buf_dump_thread | 880 | 4.67 s | 2.94 us | 5.30 ms | 27.33 ms | 40 | NULL |
| main | 2214 | 3.63 s | 409.05 ns | 2.28 ms | 35.48 ms | 1 | NULL |
| root@localhost | 21 | 88.87 ms | 527.22 ns | 2.03 ms | 21.31 ms | 49 | 7 |
+-----------------+-------+---------------+-------------+-------------+-------------+-----------+----------------+
3 rows in set (0.01 sec)
# 带x$前缀的视图
root@localhost : sys 12:43:24> select * from x$io_by_thread_by_latency limit 3;
+-----------------+-------+---------------+-------------+-----------------+-------------+-----------+----------------+
| user | total | total_latency | min_latency | avg_latency | max_latency | thread_id | processlist_id |
+-----------------+-------+---------------+-------------+-----------------+-------------+-----------+----------------+
| buf_dump_thread | 880 | 4667572388808 | 2938797 | 5304059238.0000 | 27331328412 | 40 | NULL |
| main | 2214 | 3626928831147 | 409050 | 2283656763.0000 | 35476899531 | 1 | NULL |
| root@localhost | 21 | 88867469637 | 527220 | 2026334846.2500 | 21312776994 | 49 | 7 |
+-----------------+-------+---------------+-------------+-----------------+-------------+-----------+----------------+
3 rows in set (0.01 sec)
视图字段含义如下：
user：对于前台线程，该列显示与线程关联的account名称（user@host格式），对于后台线程，该列显示后台线程的名称
total：I/O事件总次数
total_latency：I/O事件的总延迟时间（执行时间）
min_latency：I/O事件的单次最小延迟时间（执行时间）
avg_latency：I/O事件的平均延迟时间（执行时间）
max_latency：I/O事件的单次最大延迟时间（执行时间）
thread_id：内部thread ID
processlist_id：对于前台线程，该列显示为processlist ID，对于后台线程，该列显示为NULL
PS：该视图只统计文件IO等待事件信息("wait/io/file/%")
02
io_global_by_file_by_bytes,
x$io_global_by_file_by_bytes
按照文件路径+名称分组的全局I/O读写字节数、读写文件I/O事件数量进行统计，默认情况下按照总I/O(读写字节数)进行降序排序。数据来源：performance_schema.file_summary_by_instance
下面我们看看使用该视图查询返回的结果。
# 不带x$前缀的视图
root@localhost : sys 12:43:27> select * from io_global_by_file_by_bytes limit 3;
+---------------------------------+------------+------------+-----------+-------------+---------------+-----------+-----------+-----------+
| file | count_read | total_read | avg_read | count_write | total_written | avg_write | total | write_pct |
+---------------------------------+------------+------------+-----------+-------------+---------------+-----------+-----------+-----------+
| @@innodb_data_home_dir/ibtmp1 | 0 | 0 bytes | 0 bytes | 2798 | 55.53 MiB | 20.32 KiB | 55.53 MiB | 100.00 |
| @@innodb_undo_directory/undo002 | 874 | 13.66 MiB | 16.00 KiB | 0 | 0 bytes | 0 bytes | 13.66 MiB | 0.00 |
| @@innodb_data_home_dir/ibdata1 | 31 | 2.50 MiB | 82.58 KiB | 3 | 64.00 KiB | 21.33 KiB | 2.56 MiB | 2.44 |
+---------------------------------+------------+------------+-----------+-------------+---------------+-----------+-----------+-----------+
3 rows in set (0.00 sec)
# 带x$前缀的视图
root@localhost : sys 12:43:44> select * from x$io_global_by_file_by_bytes limit 3;
+-----------------------------------------------+------------+------------+------------+-------------+---------------+------------+----------+-----------+
| file | count_read | total_read | avg_read | count_write | total_written | avg_write | total | write_pct |
+-----------------------------------------------+------------+------------+------------+-------------+---------------+------------+----------+-----------+
| /home/mysql/data/mysqldata1/innodb_ts/ibtmp1 | 0 | 0 | 0.0000 | 2798 | 58228736 | 20810.8420 | 58228736 | 100.00 |
| /home/mysql/data/mysqldata1/undo/undo002 | 874 | 14319616 | 16384.0000 | 0 | 0 | 0.0000 | 14319616 | 0.00 |
| /home/mysql/data/mysqldata1/innodb_ts/ibdata1 | 31 | 2621440 | 84562.5806 | 3 | 65536 | 21845.3333 | 2686976 | 2.44 |
+-----------------------------------------------+------------+------------+------------+-------------+---------------+------------+----------+-----------+
3 rows in set (0.00 sec)
视图字段含义如下：
file：文件路径+名称
count_read：读I/O事件总次数
total_read：读I/O事件的总字节数
avg_read：读I/O事件的平均字节数
count_write：写I/O事件总次数
total_written：写I/O事件的总字节数
avg_write：写I/O事件的平均字节数
total：读写I/O事件的总字节数
write_pct：写I/O事件字节数占文件读写I/O事件的总字节数（读和写总字节数）的百分比
03
io_global_by_file_by_latency,
x$io_global_by_file_by_latency
按照文件路径+名称分组的全局I/O事件的时间开销统计信息，默认情况下按照文件总的I/O等待时间(读和写的I/O等待时间)进行降序排序。数据来源：performance_schema.file_summary_by_instance
下面我们看看使用该视图查询返回的结果。
# 不带x$前缀的视图
admin@localhost : sys 09:34:01> admin@localhost : sys 09:34:01> select * from io_global_by_file_by_latency limit 3;
+------------------------------------+-------+---------------+------------+--------------+-------------+---------------+------------+--------------+
| file | total | total_latency | count_read | read_latency | count_write | write_latency | count_misc | misc_latency |
+------------------------------------+-------+---------------+------------+--------------+-------------+---------------+------------+--------------+
| @@basedir/share/english/errmsg.sys | 5 | 268.13 ms | 3 | 119.31 ms | 0 | 0 ps | 2 | 148.82 ms |
| /data/mysqldata1/innodb_ts/ibtmp1 | 51 | 103.21 ms | 0 | 0 ps | 47 | 101.96 ms | 4 | 1.26 ms |
| /data/mysqldata1/undo/undo003 | 139 | 63.41 ms | 132 | 60.72 ms | 1 | 30.11 us | 6 | 2.65 ms |
+------------------------------------+-------+---------------+------------+--------------+-------------+---------------+------------+--------------+
3 rows in set (0.01 sec)
# 带x$前缀的视图
admin@localhost : sys 09:34:07> select * from x$io_global_by_file_by_latency limit 3;
+----------------------------------------------+-------+---------------+------------+--------------+-------------+---------------+------------+--------------+
| file | total | total_latency | count_read | read_latency | count_write | write_latency | count_misc | misc_latency |
+----------------------------------------------+-------+---------------+------------+--------------+-------------+---------------+------------+--------------+
| /home/mysql/program/share/english/errmsg.sys | 5 | 268129329000 | 3 | 119307156000 | 0 | 0 | 2 | 148822173000 |
| /data/mysqldata1/innodb_ts/ibtmp1 | 51 | 103214655750 | 0 | 0 | 47 | 101957648625 | 4 | 1257007125 |
| /data/mysqldata1/undo/undo003 | 139 | 63405483000 | 132 | 60724181625 | 1 | 30110625 | 6 | 2651190750 |
+----------------------------------------------+-------+---------------+------------+--------------+-------------+---------------+------------+--------------+
3 rows in set (0.00 sec)
视图字段含义如下：
file：文件路径+名称
total：I/O事件总次数
total_latency：I/O事件的总延迟时间（执行时间）
count_read：读I/O事件的总次数
read_latency：读I/O事件的总延迟时间（执行时间）
count_write：写I/O事件总次数
write_latency：写I/O事件的总延迟时间（执行时间）
count_misc：其他I/O事件总次数
misc_latency：其他I/O事件的总延迟时间（执行时间）
04
io_global_by_wait_by_bytes,
x$io_global_by_wait_by_bytes
按照文件IO事件名称后缀进行分组的统计信息，默认情况下按照总I/O读写总字节数进行降序排序。数据来源：performance_schema.file_summary_by_event_name
下面我们看看使用该视图查询返回的结果。
# 不带x$前缀的视图
admin@localhost : sys 09:35:20> select * from io_global_by_wait_by_bytes limit 1\G
*************************** 1. row ***************************
event_name: innodb/innodb_data_file
total: 843
total_latency: 439.19 ms
min_latency: 0 ps
avg_latency: 520.99 us
max_latency: 9.52 ms
count_read: 627
total_read: 13.64 MiB
avg_read: 22.28 KiB
count_write: 60
total_written: 12.88 MiB
avg_written: 219.73 KiB
total_requested: 26.52 MiB
1 row in set (0.01 sec)
# 带x$前缀的视图
admin@localhost : sys 09:35:22> select * from x$io_global_by_wait_by_bytes limit 1\G;
*************************** 1. row ***************************
event_name: innodb/innodb_data_file
total: 843
total_latency: 439194939750
min_latency: 0
avg_latency: 520990125
max_latency: 9521262750
count_read: 627
total_read: 14303232
avg_read: 22812.1722
count_write: 60
total_written: 13500416
avg_written: 225006.9333
total_requested: 27803648
1 row in set (0.00 sec)
视图字段含义如下：
EVENT_NAME：文件IO事件全称去掉了'wait/io/file/'前缀的名称字符串
total：读写I/O事件发生的总次数
total_latency：I/O事件的总延迟时间(执行时间)
min_latency：I/O事件单次最短延迟时间（执行时间）
avg_latency：I/O事件的平均延迟时间（执行时间）
max_latency：I/O事件单次最大延迟时间（执行时间）
count_read：读I/O事件的请求次数
total_read：读I/O事件的总字节数
avg_read：读I/O事件的平均字节数
count_write：写I/O事件的请求次数
total_written：写I/O事件的总字节数
avg_written：写I/O事件的平均字节数
total_requested：读与写I/O事件的总字节数
PS：该视图只统计文件IO等待事件信息("wait/io/file/%")
05
io_global_by_wait_by_latency,
x$io_global_by_wait_by_latency
按照事件名称后缀字符串分组、IO延迟时间排序的全局I/O等待时间统计信息，数据来源：performance_schema.file_summary_by_event_name
下面我们看看使用该视图查询返回的结果。
# 不带x$前缀的视图
admin@localhost : sys 09:35:52> select * from io_global_by_wait_by_latency limit 1\G
*************************** 1. row ***************************
event_name: innodb/innodb_data_file
total: 843
total_latency: 439.19 ms
avg_latency: 520.99 us
max_latency: 9.52 ms
read_latency: 317.18 ms
write_latency: 105.05 ms
misc_latency: 16.96 ms
count_read: 627
total_read: 13.64 MiB
avg_read: 22.28 KiB
count_write: 60
total_written: 12.88 MiB
avg_written: 219.73 KiB
1 row in set (0.01 sec)
# 带x$前缀的视图
admin@localhost : sys 09:35:55> select * from x$io_global_by_wait_by_latency limit 1\G;
*************************** 1. row ***************************
event_name: innodb/innodb_data_file
total: 843
total_latency: 439194939750
avg_latency: 520990125
max_latency: 9521262750
read_latency: 317177728125
write_latency: 105052561875
misc_latency: 16964649750
count_read: 627
total_read: 14303232
avg_read: 22812.1722
count_write: 60
total_written: 13500416
avg_written: 225006.9333
1 row in set (0.01 sec)
视图字段含义如下：
EVENT_NAME：文件IO事件全称去掉了'wait/io/file/'前缀的名称字符串
total：I/O事件的发生总次数
total_latency：I/O事件的总延迟时间（执行时间）
avg_latency：I/O事件的平均延迟时间（执行时间）
max_latency：I/O事件单次最大延迟时间（执行时间）
read_latency：读I/O事件的总延迟时间（执行时间）
write_latency：写I/O事件的总延迟时间（执行时间）
misc_latency：其他混杂I/O事件的总延迟时间（执行时间）
count_read：读I/O事件的总请求次数
total_read：读I/O事件的总字节数
avg_read：读I/O事件的平均字节数
count_write：写I/O事件的总请求次数
total_written：写I/O事件的总字节数
avg_written：写I/O事件的平均字节数
PS：该视图只统计文件IO等待事件信息("wait/io/file/%")
06
latest_file_io,
x$latest_file_io
按照文件名称和线程名称分组、文件IO操作开始起始排序的最新的已经执行完成的I/O等待事件信息，数据来源：performance_schema.events_waits_history_long、performance_schema.threads、information_schema.processlist
由于等待事件相关的instruments和consumers默认没有开启，所以该视图需要打开相关的配置之后才能查询到数据，语句如下：
* 打开等待事件的instruments：update setup_instruments set enabled='yes',timed='yes' where name like '%wait/%';
* 打开等待事件的consumers：update setup_consumers set enabled='yes' where name like '%wait%';
下面我们看看使用该视图查询返回的结果。
# 不带x$前缀的视图
admin@localhost : sys 09:50:34> select * from latest_file_io limit 3;
+------------------------+-----------------------------------------+----------+-----------+-----------+
| thread | file | latency | operation | requested |
+------------------------+-----------------------------------------+----------+-----------+-----------+
| admin@localhost:7 | /data/mysqldata1/slowlog/slow-query.log | 69.24 us | write | 251 bytes |
| page_cleaner_thread:29 | /data/mysqldata1/innodb_ts/ibtmp1 | 93.30 us | write | 16.00 KiB |
| page_cleaner_thread:29 | /data/mysqldata1/innodb_ts/ibtmp1 | 16.89 us | write | 16.00 KiB |
+------------------------+-----------------------------------------+----------+-----------+-----------+
3 rows in set (0.02 sec)
# 带x$前缀的视图
admin@localhost : sys 09:50:36> select * from x$latest_file_io limit 3;
+------------------------+-----------------------------------------+----------+-----------+-----------+
| thread | file | latency | operation | requested |
+------------------------+-----------------------------------------+----------+-----------+-----------+
| admin@localhost:7 | /data/mysqldata1/slowlog/slow-query.log | 69240000 | write | 251 |
| page_cleaner_thread:29 | /data/mysqldata1/innodb_ts/ibtmp1 | 93297000 | write | 16384 |
| page_cleaner_thread:29 | /data/mysqldata1/innodb_ts/ibtmp1 | 16891125 | write | 16384 |
+------------------------+-----------------------------------------+----------+-----------+-----------+
3 rows in set (0.01 sec)
视图字段含义如下：
thread：对于前台线程，显示与线程关联的帐户名和processlist id。对于后台线程，显示后台线程名称和内部thread ID
file：文件路径+名称
latency：I/O事件的延迟时间(执行时间)
operation：I/O操作类型
requested：I/O事件请求的数据字节数
PS：该视图只统计文件IO等待事件信息("wait/io/file/%")
06 内存分配统计视图
对innodb buffer pool的统计视图对数据库的性能影响较大（可能会导致性能陡降），它主要是提供给专业DBA人员做问题分析排查使用，一般情况下不要随意使用
对innodb buffer pool的统计视图数据来源于information_schema系统库，考虑到大家可能有MySQL 5.7之前的版本中使用需求，所以本文中特意列出了对innodb buffer pool的统计视图的select语句文本
01
innodb_buffer_stats_by_schema,
x$innodb_buffer_stats_by_schema
按照schema分组的 InnoDB buffer pool统计信息，默认按照分配的buffer size大小降序排序--allocated字段。数据来源：information_schema.innodb_buffer_page
视图select语句文本如下：
# 不带x$前缀的视图select语句文本
SELECT IF(LOCATE('.', ibp.table_name) = 0, 'InnoDB System', REPLACE(SUBSTRING_INDEX(ibp.table_name, '.', 1), '`', '')) AS object_schema,
sys.format_bytes(SUM(IF(ibp.compressed_size = 0, 16384, compressed_size))) AS allocated,
sys.format_bytes(SUM(ibp.data_size)) AS data,
COUNT(ibp.page_number) AS pages,
COUNT(IF(ibp.is_hashed = 'YES', 1, NULL)) AS pages_hashed,
COUNT(IF(ibp.is_old = 'YES', 1, NULL)) AS pages_old,
ROUND(SUM(ibp.number_records)/COUNT(DISTINCT ibp.index_name)) AS rows_cached
FROM information_schema.innodb_buffer_page ibp
WHERE table_name IS NOT NULL
GROUP BY object_schema
ORDER BY SUM(IF(ibp.compressed_size = 0, 16384, compressed_size)) DESC;
# 带x$前缀的视图select语句文本
SELECT IF(LOCATE('.', ibp.table_name) = 0, 'InnoDB System', REPLACE(SUBSTRING_INDEX(ibp.table_name, '.', 1), '`', '')) AS object_schema,
SUM(IF(ibp.compressed_size = 0, 16384, compressed_size)) AS allocated,
SUM(ibp.data_size) AS data,
COUNT(ibp.page_number) AS pages,
COUNT(IF(ibp.is_hashed = 'YES', 1, NULL)) AS pages_hashed,
COUNT(IF(ibp.is_old = 'YES', 1, NULL)) AS pages_old,
ROUND(IFNULL(SUM(ibp.number_records)/NULLIF(COUNT(DISTINCT ibp.index_name), 0), 0)) AS rows_cached
FROM information_schema.innodb_buffer_page ibp
WHERE table_name IS NOT NULL
GROUP BY object_schema
ORDER BY SUM(IF(ibp.compressed_size = 0, 16384, compressed_size)) DESC;
下面我们看看使用该视图查询返回的结果。
# 不带x$前缀的视图
admin@localhost : sys 06:15:41> select * from innodb_buffer_stats_by_schema;
+---------------+------------+-----------+-------+--------------+-----------+-------------+
| object_schema | allocated | data | pages | pages_hashed | pages_old | rows_cached |
+---------------+------------+-----------+-------+--------------+-----------+-------------+
| InnoDB System | 23.73 MiB | 21.76 MiB | 1519 | 0 | 24 | 21474 |
| mysql | 240.00 KiB | 14.57 KiB | 15 | 0 | 15 | 179 |
| xiaoboluo | 128.00 KiB | 38.93 KiB | 8 | 0 | 5 | 982 |
| sys | 16.00 KiB | 354 bytes | 1 | 0 | 1 | 6 |
| 小萝卜 | 16.00 KiB | 135 bytes | 1 | 0 | 1 | 3 |
+---------------+------------+-----------+-------+--------------+-----------+-------------+
5 rows in set (0.43 sec)
# 带x$前缀的视图
admin@localhost : sys 06:15:54> select * from x$innodb_buffer_stats_by_schema;
+---------------+-----------+----------+-------+--------------+-----------+-------------+
| object_schema | allocated | data | pages | pages_hashed | pages_old | rows_cached |
+---------------+-----------+----------+-------+--------------+-----------+-------------+
| InnoDB System | 24887296 | 22809628 | 1519 | 0 | 24 | 21498 |
| mysql | 245760 | 14917 | 15 | 0 | 15 | 179 |
| xiaoboluo | 131072 | 39865 | 8 | 0 | 5 | 982 |
| sys | 16384 | 354 | 1 | 0 | 1 | 6 |
| 小萝卜 | 16384 | 135 | 1 | 0 | 1 | 3 |
+---------------+-----------+----------+-------+--------------+-----------+-------------+
5 rows in set (0.42 sec)
视图字段含义如下：
object_schema：schema级别对象的名称，如果该表属于Innodb存储引擎，则该字段显示为InnoDB System，如果是其他引擎，则该字段显示为每个schema name.
allocated：当前已分配给schema的总内存字节数
data：当前已分配给schema的数据部分使用的内存字节总数
pages：当前已分配给schema内存总页数
pages_hashed：当前已分配给schema的自适应hash索引页总数
pages_old：当前已分配给schema的旧页总数（位于LRU列表中的旧块子列表中的页数）
rows_cached：buffer pool中为schema缓冲的总数据行数
02
innodb_buffer_stats_by_table,
x$innodb_buffer_stats_by_table
按照schema和表分组的 InnoDB buffer pool 统计信息，与sys.innodb_buffer_stats_by_schema视图类似，但是本视图是按照schema name和table name分组。数据来源：information_schema.innodb_buffer_page
视图select语句文本如下：
# 不带x$前缀的视图select语句文本
SELECT IF(LOCATE('.', ibp.table_name) = 0, 'InnoDB System', REPLACE(SUBSTRING_INDEX(ibp.table_name, '.', 1), '`', '')) AS object_schema,
REPLACE(SUBSTRING_INDEX(ibp.table_name, '.', -1), '`', '') AS object_name,
sys.format_bytes(SUM(IF(ibp.compressed_size = 0, 16384, compressed_size))) AS allocated,
sys.format_bytes(SUM(ibp.data_size)) AS data,
COUNT(ibp.page_number) AS pages,
COUNT(IF(ibp.is_hashed = 'YES', 1, NULL)) AS pages_hashed,
COUNT(IF(ibp.is_old = 'YES', 1, NULL)) AS pages_old,
ROUND(SUM(ibp.number_records)/COUNT(DISTINCT ibp.index_name)) AS rows_cached
FROM information_schema.innodb_buffer_page ibp
WHERE table_name IS NOT NULL
GROUP BY object_schema, object_name
ORDER BY SUM(IF(ibp.compressed_size = 0, 16384, compressed_size)) DESC;
# 带x$前缀的视图select语句文本
SELECT IF(LOCATE('.', ibp.table_name) = 0, 'InnoDB System', REPLACE(SUBSTRING_INDEX(ibp.table_name, '.', 1), '`', '')) AS object_schema,
REPLACE(SUBSTRING_INDEX(ibp.table_name, '.', -1), '`', '') AS object_name,
SUM(IF(ibp.compressed_size = 0, 16384, compressed_size)) AS allocated,
SUM(ibp.data_size) AS data,
COUNT(ibp.page_number) AS pages,
COUNT(IF(ibp.is_hashed = 'YES', 1, NULL)) AS pages_hashed,
COUNT(IF(ibp.is_old = 'YES', 1, NULL)) AS pages_old,
ROUND(IFNULL(SUM(ibp.number_records)/NULLIF(COUNT(DISTINCT ibp.index_name), 0), 0)) AS rows_cached
FROM information_schema.innodb_buffer_page ibp
WHERE table_name IS NOT NULL
GROUP BY object_schema, object_name
ORDER BY SUM(IF(ibp.compressed_size = 0, 16384, compressed_size)) DESC;
下面我们看看使用该视图查询返回的结果。
# 不带x$前缀的视图
root@localhost : sys 12:41:25> select * from innodb_buffer_stats_by_table limit 3;
+---------------+-------------+-----------+-----------+-------+--------------+-----------+-------------+
| object_schema | object_name | allocated | data | pages | pages_hashed | pages_old | rows_cached |
+---------------+-------------+-----------+-----------+-------+--------------+-----------+-------------+
| InnoDB System | SYS_TABLES | 11.58 MiB | 10.63 MiB | 741 | 0 | 3 | 36692 |
| luoxiaobo | t_luoxiaobo | 80.00 KiB | 29.21 KiB | 5 | 0 | 0 | 1658 |
| InnoDB System | SYS_COLUMNS | 48.00 KiB | 16.03 KiB | 3 | 0 | 3 | 239 |
+---------------+-------------+-----------+-----------+-------+--------------+-----------+-------------+
3 rows in set (0.12 sec)
# 带x$前缀的视图
root@localhost : sys 12:41:41> select * from x$innodb_buffer_stats_by_table limit 3;
+---------------+-------------+-----------+----------+-------+--------------+-----------+-------------+
| object_schema | object_name | allocated | data | pages | pages_hashed | pages_old | rows_cached |
+---------------+-------------+-----------+----------+-------+--------------+-----------+-------------+
| InnoDB System | SYS_TABLES | 12140544 | 11154757 | 741 | 0 | 3 | 36702 |
| luoxiaobo | t_luoxiaobo | 81920 | 29913 | 5 | 0 | 0 | 1658 |
| InnoDB System | SYS_COLUMNS | 49152 | 16412 | 3 | 0 | 3 | 239 |
+---------------+-------------+-----------+----------+-------+--------------+-----------+-------------+
3 rows in set (0.12 sec)
视图字段含义如下：
object_name：表级别对象名称，通常是表名
其他字段含义与sys.innodb_buffer_stats_by_schema视图字段含义相同，详见 innodb_buffer_stats_by_schema,x$innodb_buffer_stats_by_schema视图解释部分。但这些字段是按照object_name表级别统计的
03
memory_by_host_by_current_bytes,
x$memory_by_host_by_current_bytes
按照客户端主机名分组的内存使用统计信息，默认情况下按照当前内存使用量降序排序，数据来源：performance_schema.memory_summary_by_host_by_event_name
memory类型的事件默认情况下只启用了performance_schema自身的instruments，要监控用户访问，需要单独配置，如下：
* 开启所有的memory类型的instruments：update setup_instruments set enabled='yes' where name like '%memory/%';
下面我们看看使用该视图查询返回的结果。
# 不带x$前缀的视图
admin@localhost : sys 10:01:37> select * from memory_by_host_by_current_bytes limit 3;
+-------------+--------------------+-------------------+-------------------+-------------------+-----------------+
| host | current_count_used | current_allocated | current_avg_alloc | current_max_alloc | total_allocated |
+-------------+--------------------+-------------------+-------------------+-------------------+-----------------+
| 10.10.20.14 | 58256 | 35.83 MiB | 645 bytes | 14.20 MiB | 2.36 GiB |
| localhost | 32 | 903.11 KiB | 28.22 KiB | 819.00 KiB | 7.69 MiB |
| background | 5 | 176 bytes | 35 bytes | 160 bytes | 352.57 KiB |
+-------------+--------------------+-------------------+-------------------+-------------------+-----------------+
3 rows in set (0.01 sec)
# 带x$前缀的视图
admin@localhost : sys 10:02:19> select * from x$memory_by_host_by_current_bytes limit 3;
+-------------+--------------------+-------------------+-------------------+-------------------+-----------------+
| host | current_count_used | current_allocated | current_avg_alloc | current_max_alloc | total_allocated |
+-------------+--------------------+-------------------+-------------------+-------------------+-----------------+
| 10.10.20.14 | 58256 | 37569266 | 644.8995 | 14885584 | 2538394110 |
| localhost | 18 | 891658 | 49536.5556 | 838656 | 9821551 |
| background | 5 | 176 | 35.2000 | 160 | 361068 |
+-------------+--------------------+-------------------+-------------------+-------------------+-----------------+
3 rows in set (0.00 sec)
视图字段含义如下：
host：客户端连接的主机名或IP。在Performance Schema表中的HOST列为NULL的行在这里假定为后台线程，且在该视图host列显示为background
current_count_used：当前已分配的且未释放的内存块对应的内存分配次数（内存事件调用次数，该字段是快捷值，来自：performance_schema.memory_summary_by_host_by_event_name表的内存总分配次数字段COUNT_ALLOC - 内存释放次数COUNT_FREE）
current_allocated：当前已分配的且未释放的内存字节数
current_avg_alloc：当前已分配的且未释放的内存块对应的平均每次内存分配的内存字节数(current_allocated/current_count_used)
current_max_alloc：当前已分配的且未释放的单次最大内存分配字节数
total_allocated：总的已分配内存字节数
04
memory_by_thread_by_current_bytes,
x$memory_by_thread_by_current_bytes
按照thread ID分组的内存使用统计信息（只统计前台线程），默认情况下按照当前内存使用量进行降序排序，数据来源：performance_schema.memory_summary_by_thread_by_event_name、performance_schema.threads
下面我们看看使用该视图查询返回的结果。
# 不带x$前缀的视图
admin@localhost : sys 10:04:07> select * from memory_by_thread_by_current_bytes limit 3;
+-----------+--------------------------+--------------------+-------------------+-------------------+-------------------+-----------------+
| thread_id | user | current_count_used | current_allocated | current_avg_alloc | current_max_alloc | total_allocated |
+-----------+--------------------------+--------------------+-------------------+-------------------+-------------------+-----------------+
| 45 | admin@localhost | 34 | 4.91 MiB | 147.98 KiB | 4.00 MiB | 29.36 MiB |
| 41 | innodb/dict_stats_thread | 5 | 176 bytes | 35 bytes | 160 bytes | 346.31 KiB |
| 47 | admin@localhost | 3 | 112 bytes | 37 bytes | 80 bytes | 8.17 KiB |
+-----------+--------------------------+--------------------+-------------------+-------------------+-------------------+-----------------+
3 rows in set (0.13 sec)
# 带x$前缀的视图
admin@localhost : sys 10:04:58> select * from x$memory_by_thread_by_current_bytes limit 3;
+-----------+--------------------------+--------------------+-------------------+-------------------+-------------------+-----------------+
| thread_id | user | current_count_used | current_allocated | current_avg_alloc | current_max_alloc | total_allocated |
+-----------+--------------------------+--------------------+-------------------+-------------------+-------------------+-----------------+
| 45 | admin@localhost | 19 | 5102538 | 268554.6316 | 4194304 | 44995979 |
| 41 | innodb/dict_stats_thread | 5 | 176 | 35.2000 | 160 | 354620 |
| 47 | admin@localhost | 3 | 112 | 37.3333 | 80 | 8368 |
+-----------+--------------------------+--------------------+-------------------+-------------------+-------------------+-----------------+
3 rows in set (0.12 sec)
视图字段含义如下：
thread_id：内部thread ID
user：对于前台线程，该字段显示为account名称，对于后台线程，该字段显示后台线程名称
其他字段含义与sys.memory_by_host_by_current_bytes视图的字段含义相同，详见 memory_by_host_by_current_bytes,x$memory_by_host_by_current_bytes视图解释部分。但是与该视图不同的是本视图是按照线程分组统计的
05
memory_by_user_by_current_bytes,
x$memory_by_user_by_current_bytes
按照用户分组的内存使用统计信息，默认按照当前内存使用量进行降序排序，数据来源：performance_schema.memory_summary_by_user_by_event_name
下面我们看看使用该视图查询返回的结果。
# 不带x$前缀的视图
admin@localhost : sys 10:05:02> select * from memory_by_user_by_current_bytes limit 3;
+------------+--------------------+-------------------+-------------------+-------------------+-----------------+
| user | current_count_used | current_allocated | current_avg_alloc | current_max_alloc | total_allocated |
+------------+--------------------+-------------------+-------------------+-------------------+-----------------+
| admin | 58291 | 36.71 MiB | 660 bytes | 14.20 MiB | 2.41 GiB |
| background | 5 | 176 bytes | 35 bytes | 160 bytes | 358.17 KiB |
| qfsys | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes |
+------------+--------------------+-------------------+-------------------+-------------------+-----------------+
3 rows in set (0.01 sec)
# 带x$前缀的视图
admin@localhost : sys 10:05:21> select * from x$memory_by_user_by_current_bytes limit 3;
+------------+--------------------+-------------------+-------------------+-------------------+-----------------+
| user | current_count_used | current_allocated | current_avg_alloc | current_max_alloc | total_allocated |
+------------+--------------------+-------------------+-------------------+-------------------+-----------------+
| admin | 58278 | 38460932 | 659.9563 | 14885584 | 2586890836 |
| background | 5 | 176 | 35.2000 | 160 | 366828 |
| qfsys | 0 | 0 | 0.0000 | 0 | 0 |
+------------+--------------------+-------------------+-------------------+-------------------+-----------------+
3 rows in set (0.01 sec)
视图字段含义如下：
user：客户端用户名。对于后台线程，该字段显示为background，对于前台线程，该字段显示user名称(不是account，不包含host部分)
其他字段含义与sys.memory_by_host_by_current_bytes视图的字段含义相同，详见 memory_by_host_by_current_bytes,x$memory_by_host_by_current_bytes视图解释部分。但是与该视图不同的是这里是按照用户名分组统计的
06
memory_global_by_current_bytes,
x$memory_global_by_current_bytes
按照内存分配类型（事件类型）分组的内存使用统计信息，默认情况下按照当前内存使用量进行降序排序，数据来源：performance_schema.memory_summary_global_by_event_name
下面我们看看使用该视图查询返回的结果。
# 不带x$前缀的视图
admin@localhost : sys 10:05:24> select * from memory_global_by_current_bytes limit 3;
+-----------------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+
| event_name | current_count | current_alloc | current_avg_alloc | high_count | high_alloc | high_avg_alloc |
+-----------------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+
| memory/innodb/lock0lock | 9166 | 14.20 MiB | 1.59 KiB | 9166 | 14.20 MiB | 1.59 KiB |
| memory/performance_schema/events_statements_history_long | 1 | 13.66 MiB | 13.66 MiB | 1 | 13.66 MiB | 13.66 MiB |
| memory/performance_schema/events_statements_history_long.tokens | 1 | 9.77 MiB | 9.77 MiB | 1 | 9.77 MiB | 9.77 MiB |
+-----------------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+
3 rows in set (0.01 sec)
# 带x$前缀的视图
admin@localhost : sys 10:07:19> select * from x$memory_global_by_current_bytes limit 3;
+-----------------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+
| event_name | current_count | current_alloc | current_avg_alloc | high_count | high_alloc | high_avg_alloc |
+-----------------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+
| memory/innodb/lock0lock | 9166 | 14885584 | 1624.0000 | 9166 | 14885584 | 1624.0000 |
| memory/performance_schema/events_statements_history_long | 1 | 14320000 | 14320000.0000 | 1 | 14320000 | 14320000.0000 |
| memory/performance_schema/events_statements_history_long.tokens | 1 | 10240000 | 10240000.0000 | 1 | 10240000 | 10240000.0000 |
+-----------------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+
3 rows in set (0.00 sec)
视图字段含义如下：
EVENT_NAME：内存事件名称
CURRENT_COUNT：当前已分配内存且未释放的内存事件发生的总次数(内存分配次数)
current_alloc：当前已分配内存且未释放的内存字节数
current_avg_alloc：当前已分配内存且未释放的内存事件的平均内存字节数(平均每次内存分配的字节数)
high_count：内存事件发生的历史最高位(高水位)次数（来自performance_schema.memory_summary_global_by_event_name表中的HIGH_COUNT_USED字段：如果CURRENT_COUNT_USED增加1是一个新的最高值，则该字段值相应增加 ）
high_alloc：内存分配的历史最高位(高水位)字节数（来自performance_schema.memory_summary_global_by_event_name表中的HIGH_NUMBER_OF_BYTES_USED字段：如果CURRENT_NUMBER_OF_BYTES_USED增加N之后是一个新的最高值，则该字段值相应增加）
high_avg_alloc：内存事件发生的历史最高位(高水位)次数对应的平均每次内存分配的字节数(high_number_of_bytes_used/high_count_used)
07 memory_global_total,x$memory_global_total
当前总内存使用量统计（注意：只包含自memory类型的instruments启用以来被监控到的内存事件，在启用之前的无法监控，so..如果你不是在server启动之前就在配置文件中配置启动memory类型的instruments，那么此值可能并不可靠，当然如果你的server运行时间足够长，那么该值也具有一定参考价值），数据来源：performance_schema.memory_summary_global_by_event_name
下面我们看看使用该视图查询返回的结果。
# 不带x$前缀的视图
admin@localhost : sys 10:07:22> select * from memory_global_total limit 3;
+-----------------+
| total_allocated |
+-----------------+
| 168.91 MiB |
+-----------------+
1 row in set (0.01 sec)
# 带x$前缀的视图
admin@localhost : sys 10:08:24> select * from x$memory_global_total limit 3;
+-----------------+
| total_allocated |
+-----------------+
| 177099388 |
+-----------------+
1 row in set (0.00 sec)
视图字段含义如下：
total_allocated：在server中分配的内存总字节数
07 等待事件统计视图
01
wait_classes_global_by_avg_latency,
x$wait_classes_global_by_avg_latency
按照事件大类(等待事件名称层级中前三层组件组成的名称前缀)分组（如：wait/io/table、wait/io/file、wait/lock/table）的等待事件平均延迟时间（总IO延迟时间/总IOS）等统计信息，默认按照平均延迟时间(执行时间)降序排序。数据来源：events_waits_summary_global_by_event_name
该视图会忽略空闲等待事件(idle事件)信息
下面我们看看使用该视图查询返回的结果。
# 不带x$前缀的视图
admin@localhost : sys 12:58:11> select * from wait_classes_global_by_avg_latency limit 3;
+--------------------+-------+---------------+-------------+-------------+-------------+
| event_class | total | total_latency | min_latency | avg_latency | max_latency |
+--------------------+-------+---------------+-------------+-------------+-------------+
| wait/lock/metadata | 2 | 56.57 m | 12.94 m | 28.28 m | 43.63 m |
| wait/synch/cond | 7980 | 4.37 h | 0 ps | 1.97 s | 5.01 s |
| wait/io/socket | 28988 | 21.02 s | 0 ps | 725.29 us | 103.18 ms |
+--------------------+-------+---------------+-------------+-------------+-------------+
3 rows in set (0.05 sec)
# 带x$前缀的视图
admin@localhost : sys 12:58:22> select * from x$wait_classes_global_by_avg_latency limit 3;
+--------------------+-------+-------------------+-----------------+-----------------------+------------------+
| event_class | total | total_latency | min_latency | avg_latency | max_latency |
+--------------------+-------+-------------------+-----------------+-----------------------+------------------+
| wait/lock/metadata | 2 | 3393932470401750 | 776378395041375 | 1696966235200875.0000 | 2617554075360375 |
| wait/synch/cond | 7980 | 15739342570225500 | 0 | 1972348693010.7143 | 5006888904375 |
| wait/io/socket | 28990 | 21024710924250 | 0 | 725240114.6689 | 103181011500 |
+--------------------+-------+-------------------+-----------------+-----------------------+------------------+
3 rows in set (0.02 sec)
视图字段含义如下：
event_class：事件类别，事件名称层级中前三层组件组成的名称前缀，如'wait/io/file/sql/slow_log'，截取后保留'wait/io/file' 字符串作为事件类别
total：对应事件大类的事件总次数
total_latency：对应事件大类的事件总延迟时间(执行时间)
min_latency：对应事件大类的单次事件最小延迟时间(执行时间)
avg_latency：对应事件大类中，每个事件的平均延迟时间(执行时间)
max_latency：对应事件大类的单次事件在最大延迟时间(执行时间)
02
wait_classes_global_by_latency,
x$wait_classes_global_by_latency
按照事件大类(等待事件名称前三层前缀)分组（如：wait/io/table、wait/io/file、wait/lock/table）的等待事件平均延迟时间等统计信息，默认情况下按照总延迟时间(执行时间)降序排序。数据来源：events_waits_summary_global_by_event_name
下面我们看看使用该视图查询返回的结果。
# 不带x$前缀的视图
admin@localhost : sys 12:58:26> select * from wait_classes_global_by_latency limit 3;
+--------------------+----------+---------------+-------------+-------------+-------------+
| event_class | total | total_latency | min_latency | avg_latency | max_latency |
+--------------------+----------+---------------+-------------+-------------+-------------+
| wait/synch/cond | 7983 | 4.38 h | 0 ps | 1.97 s | 5.01 s |
| wait/lock/metadata | 2 | 56.57 m | 12.94 m | 28.28 m | 43.63 m |
| wait/io/table | 16096791 | 4.59 m | 12.03 us | 17.11 us | 2.02 m |
+--------------------+----------+---------------+-------------+-------------+-------------+
3 rows in set (0.02 sec)
# 带x$前缀的视图
admin@localhost : sys 12:58:40> select * from x$wait_classes_global_by_latency limit 3;
+--------------------+----------+-------------------+-----------------+-----------------------+------------------+
| event_class | total | total_latency | min_latency | avg_latency | max_latency |
+--------------------+----------+-------------------+-----------------+-----------------------+------------------+
| wait/synch/cond | 7984 | 15759344050722375 | 0 | 1973865737815.9287 | 5006888904375 |
| wait/lock/metadata | 2 | 3393932470401750 | 776378395041375 | 1696966235200875.0000 | 2617554075360375 |
| wait/io/table | 16096791 | 275441586767625 | 12026625 | 17111583.7168 | 121243803313125 |
+--------------------+----------+-------------------+-----------------+-----------------------+------------------+
3 rows in set (0.02 sec)
视图字段含义如下：
该视图字段含义和wait_classes_global_by_avg_latency,x$wait_classes_global_by_avg_latency 视图字段含义相同，只是排序字段不同而已
03
waits_by_host_by_latency,
x$waits_by_host_by_latency
按照主机和事件名称分组的等待事件统计信息，默认情况下按照主机名和总的等待事件延迟时间降序排序，数据来源：events_waits_summary_by_host_by_event_name
该视图忽略空闲等待事件（idle事件）信息
下面我们看看使用该视图查询返回的结果。
# 不带x$前缀的视图
admin@localhost : sys 12:58:43> select * from waits_by_host_by_latency limit 3;
+-------------+----------------------------------------------+-------+---------------+-------------+-------------+
| host | event | total | total_latency | avg_latency | max_latency |
+-------------+----------------------------------------------+-------+---------------+-------------+-------------+
| 10.10.20.14 | wait/io/socket/sql/client_connection | 24568 | 20.53 s | 835.48 us | 70.46 ms |
| 10.10.20.14 | wait/synch/mutex/innodb/trx_pool_mutex | 2326 | 14.59 s | 6.27 ms | 215.63 ms |
| 10.10.20.14 | wait/synch/cond/sql/MYSQL_BIN_LOG::COND_done | 1707 | 13.74 s | 8.05 ms | 43.33 ms |
+-------------+----------------------------------------------+-------+---------------+-------------+-------------+
3 rows in set (0.00 sec)
# 带x$前缀的视图
admin@localhost : sys 12:59:04> select * from x$waits_by_host_by_latency limit 3;
+-------------+----------------------------------------------+-------+----------------+-------------+--------------+
| host | event | total | total_latency | avg_latency | max_latency |
+-------------+----------------------------------------------+-------+----------------+-------------+--------------+
| 10.10.20.14 | wait/io/socket/sql/client_connection | 24568 | 20526083640375 | 835480125 | 70457480625 |
| 10.10.20.14 | wait/synch/mutex/innodb/trx_pool_mutex | 2326 | 14586650782125 | 6271131000 | 215632752375 |
| 10.10.20.14 | wait/synch/cond/sql/MYSQL_BIN_LOG::COND_done | 1707 | 13737760876125 | 8047897125 | 43332152250 |
+-------------+----------------------------------------------+-------+----------------+-------------+--------------+
3 rows in set (0.01 sec)
视图字段含义如下：
host：发起连接的主机名
event：等待事件名称
total：对应主机发生的等待事件总次数
total_latency：对应主机的等待事件总延迟时间
avg_latency：对应主机的等待事件的平均延迟时间
max_latency：对应主机的单次等待事件的最大延迟时间
04
waits_by_user_by_latency,
x$waits_by_user_by_latency
按照用户和事件名称分组的等待事件统计信息，默认情况下按照用户名和总的等待事件延迟事件降序排序，数据来源：events_waits_summary_by_user_by_event_name
该视图忽略空闲等待事件（idle事件）信息
下面我们看看使用该视图查询返回的结果。
# 不带x$前缀的视图
admin@localhost : sys 12:59:07> select * from waits_by_user_by_latency limit 3;
+-------+---------------------------------------------------+----------+---------------+-------------+-------------+
| user | event | total | total_latency | avg_latency | max_latency |
+-------+---------------------------------------------------+----------+---------------+-------------+-------------+
| admin | wait/lock/metadata/sql/mdl | 2 | 56.57 m | 28.28 m | 43.63 m |
| admin | wait/synch/cond/sql/MDL_context::COND_wait_status | 3395 | 56.56 m | 999.66 ms | 1.00 s |
| admin | wait/io/table/sql/handler | 16096791 | 4.59 m | 17.11 us | 2.02 m |
+-------+---------------------------------------------------+----------+---------------+-------------+-------------+
3 rows in set (0.01 sec)
# 带x$前缀的视图
admin@localhost : sys 12:59:22> select * from x$waits_by_user_by_latency limit 3;
+-------+---------------------------------------------------+----------+------------------+------------------+------------------+
| user | event | total | total_latency | avg_latency | max_latency |
+-------+---------------------------------------------------+----------+------------------+------------------+------------------+
| admin | wait/lock/metadata/sql/mdl | 2 | 3393932470401750 | 1696966235200875 | 2617554075360375 |
| admin | wait/synch/cond/sql/MDL_context::COND_wait_status | 3395 | 3393839154564375 | 999658071750 | 1004173431750 |
| admin | wait/io/table/sql/handler | 16096791 | 275441586767625 | 17111250 | 121243803313125 |
+-------+---------------------------------------------------+----------+------------------+------------------+------------------+
3 rows in set (0.01 sec)
视图字段含义如下：
user：与该连接关联的用户名
其他字段与waits_by_host_by_latency,x$waits_by_host_by_latency 视图字段含义相同，不同的是waits_by_user_by_latency,x$waits_by_user_by_latency视图是按照用户名和事件名称分组
05
waits_global_by_latency,
x$waits_global_by_latency
按照事件名称分组的等待事件统计信息，默认按照等待事件总延迟时间降序排序。数据来源：events_waits_summary_global_by_event_name
该视图忽略空闲等待事件（idle事件）信息
下面我们看看使用该视图查询返回的结果。
# 不带x$前缀的视图
admin@localhost : sys 12:59:25> select * from waits_global_by_latency limit 3;
+---------------------------------------------------+-------+---------------+-------------+-------------+
| events | total | total_latency | avg_latency | max_latency |
+---------------------------------------------------+-------+---------------+-------------+-------------+
| wait/synch/cond/sql/MYSQL_BIN_LOG::update_cond | 2891 | 3.45 h | 4.29 s | 5.01 s |
| wait/lock/metadata/sql/mdl | 2 | 56.57 m | 28.28 m | 43.63 m |
| wait/synch/cond/sql/MDL_context::COND_wait_status | 3395 | 56.56 m | 999.66 ms | 1.00 s |
+---------------------------------------------------+-------+---------------+-------------+-------------+
3 rows in set (0.02 sec)
# 带x$前缀的视图
admin@localhost : sys 12:59:40> select * from x$waits_global_by_latency limit 3;
+---------------------------------------------------+-------+-------------------+------------------+------------------+
| events | total | total_latency | avg_latency | max_latency |
+---------------------------------------------------+-------+-------------------+------------------+------------------+
| wait/synch/cond/sql/MYSQL_BIN_LOG::update_cond | 2892 | 12411771548807250 | 4291760563125 | 5006888904375 |
| wait/lock/metadata/sql/mdl | 2 | 3393932470401750 | 1696966235200875 | 2617554075360375 |
| wait/synch/cond/sql/MDL_context::COND_wait_status | 3395 | 3393839154564375 | 999658071750 | 1004173431750 |
+---------------------------------------------------+-------+-------------------+------------------+------------------+
3 rows in set (0.02 sec)
视图字段含义如下：
events：等待事件名称
其他字段含义和waits_by_host_by_latency,x$waits_by_host_by_latency 视图字段含义相同，不同的是waits_global_by_latency,x$waits_global_by_latency视图只按照事件名称分组
08 会话和锁信息查询视图
01
innodb_lock_waits,
x$innodb_lock_waits
InnoDB当前锁等待信息，默认按照发生锁等待的开始时间升序排序--wait_started字段即innodb_trx表的trx_wait_started字段。数据来源：information_schema的innodb_trx、innodb_locks、innodb_lock_waits（注：在8.0及其之后的版本中，该视图的信息来源为information_schema的innodb_trx、performance_schema的data_locks和data_lock_waits）
视图查询语句文本
# 不带x$前缀的视图的查询语句
SELECT r.trx_wait_started AS wait_started,
TIMEDIFF(NOW(), r.trx_wait_started) AS wait_age,
TIMESTAMPDIFF(SECOND, r.trx_wait_started, NOW()) AS wait_age_secs,
rl.lock_table AS locked_table,
rl.lock_index AS locked_index,
rl.lock_type AS locked_type,
r.trx_id AS waiting_trx_id,
r.trx_started as waiting_trx_started,
TIMEDIFF(NOW(), r.trx_started) AS waiting_trx_age,
r.trx_rows_locked AS waiting_trx_rows_locked,
r.trx_rows_modified AS waiting_trx_rows_modified,
r.trx_mysql_thread_id AS waiting_pid,
sys.format_statement(r.trx_query) AS waiting_query,
rl.lock_id AS waiting_lock_id,
rl.lock_mode AS waiting_lock_mode,
b.trx_id AS blocking_trx_id,
b.trx_mysql_thread_id AS blocking_pid,
sys.format_statement(b.trx_query) AS blocking_query,
bl.lock_id AS blocking_lock_id,
bl.lock_mode AS blocking_lock_mode,
b.trx_started AS blocking_trx_started,
TIMEDIFF(NOW(), b.trx_started) AS blocking_trx_age,
b.trx_rows_locked AS blocking_trx_rows_locked,
b.trx_rows_modified AS blocking_trx_rows_modified,
CONCAT('KILL QUERY ', b.trx_mysql_thread_id) AS sql_kill_blocking_query,
CONCAT('KILL ', b.trx_mysql_thread_id) AS sql_kill_blocking_connection
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id
INNER JOIN information_schema.innodb_locks bl ON bl.lock_id = w.blocking_lock_id
INNER JOIN information_schema.innodb_locks rl ON rl.lock_id = w.requested_lock_id
ORDER BY r.trx_wait_started;
# x$innodb_lock_waits：在8.0之前的版本，两者无区别
下面我们看看使用该视图查询返回的结果
# 不带x$前缀的视图
root@localhost : sys 12:41:45> select * from innodb_lock_waits\G;
*************************** 1. row ***************************
wait_started: 2017-09-07 00:42:32
wait_age: 00:00:12
wait_age_secs: 12
locked_table: `luoxiaobo`.`test`
locked_index: GEN_CLUST_INDEX
locked_type: RECORD
waiting_trx_id: 66823
waiting_trx_started: 2017-09-07 00:42:32
waiting_trx_age: 00:00:12
waiting_trx_rows_locked: 1
waiting_trx_rows_modified: 0
waiting_pid: 7
waiting_query: select * from test limit 1 for update
waiting_lock_id: 66823:106:3:2
waiting_lock_mode: X
blocking_trx_id: 66822
blocking_pid: 6
blocking_query: NULL
blocking_lock_id: 66822:106:3:2
blocking_lock_mode: X
blocking_trx_started: 2017-09-07 00:42:19
blocking_trx_age: 00:00:25
blocking_trx_rows_locked: 1
blocking_trx_rows_modified: 0
sql_kill_blocking_query: KILL QUERY 6
sql_kill_blocking_connection: KILL 6
1 row in set, 3 warnings (0.00 sec)
视图字段含义如下：
wait_started：发生锁等待的开始时间
wait_age：锁已经等待了多久，该值是一个时间格式值
wait_age_secs：锁已经等待了几秒钟，该值是一个整型值，MySQL 5.7.9中新增
locked_table：锁等待的表名称。此列值格式为：schema_name.table_name
locked_index：锁等待的索引名称
locked_type：锁等待的锁类型
waiting_trx_id：锁等待的事务ID
waiting_trx_started：发生锁等待的事务开始时间
waiting_trx_age：发生锁等待的事务总的锁等待时间，该值是一个时间格式
waiting_trx_rows_locked：发生锁等待的事务已经锁定的行数（如果是复杂事务会累计）
waiting_trx_rows_modified：发生锁等待的事务已经修改的行数（如果是复杂事务会累计）
waiting_pid：发生锁等待的事务的processlist_id
waiting_query：发生锁等待的事务SQL语句文本
waiting_lock_id：发生锁等待的锁ID
waiting_lock_mode：发生锁等待的锁模式
blocking_trx_id：持有锁的事务ID
blocking_pid：持有锁的事务processlist_id
blocking_query：持有锁的事务的SQL语句文本
blocking_lock_id：持有锁的锁ID
blocking_lock_mode：持有锁的锁模式
blocking_trx_started：持有锁的事务的开始时间
blocking_trx_age：持有锁的事务已执行了多长时间，该值为时间格式值
blocking_trx_rows_locked：持有锁的事务的锁定行数
blocking_trx_rows_modified：持有锁的事务需要修改的行数
sql_kill_blocking_query：执行KILL语句来杀死持有锁的查询语句（而不是终止会话）。该列在MySQL 5.7.9中新增
sql_kill_blocking_connection：执行KILL语句以终止持有锁的语句的会话。该列在MySQL 5.7.9中新增
PS：8.0中废弃information_schema.innodb_locks和information_schema.innodb_lock_waits，迁移到performance_schema.data_locks和performance_schema.data_lock_waits，
02
processlist,
x$processlist
包含所有前台和后台线程的processlist信息，默认按照进程等待时间和最近一个语句执行完成的时间降序排序。数据来源：performance_schema的threads、events_waits_current、events_statements_current、events_stages_current 、events_transactions_current 、session_connect_attrs表和 sys 系统库的 x$memory_by_thread_by_current_bytess视图
这些视图列出了进程相关的较为详细的信息，比SHOW PROCESSLIST语句和INFORMATION_SCHEMA PROCESSLIST表更完整，且对该视图的查询是非阻塞的（因为不是从information_schema.processlist表中获取数据的，对processlist表查询是阻塞的）
视图查询语句文本
# 不带x$前缀的视图
SELECT pps.thread_id AS thd_id,
pps.processlist_id AS conn_id,
IF(pps.name = 'thread/sql/one_connection',
CONCAT(pps.processlist_user, '@', pps.processlist_host),
REPLACE(pps.name, 'thread/', '')) user,
pps.processlist_db AS db,
pps.processlist_command AS command,
pps.processlist_state AS state,
pps.processlist_time AS time,
sys.format_statement(pps.processlist_info) AS current_statement,
IF(esc.end_event_id IS NULL,
sys.format_time(esc.timer_wait),
NULL) AS statement_latency,
IF(esc.end_event_id IS NULL,
ROUND(100 * (estc.work_completed / estc.work_estimated), 2),
NULL) AS progress,
sys.format_time(esc.lock_time) AS lock_latency,
esc.rows_examined AS rows_examined,
esc.rows_sent AS rows_sent,
esc.rows_affected AS rows_affected,
esc.created_tmp_tables AS tmp_tables,
esc.created_tmp_disk_tables AS tmp_disk_tables,
IF(esc.no_good_index_used > 0 OR esc.no_index_used > 0, 'YES', 'NO') AS full_scan,
IF(esc.end_event_id IS NOT NULL,
sys.format_statement(esc.sql_text),
NULL) AS last_statement,
IF(esc.end_event_id IS NOT NULL,
sys.format_time(esc.timer_wait),
NULL) AS last_statement_latency,
sys.format_bytes(mem.current_allocated) AS current_memory,
ewc.event_name AS last_wait,
IF(ewc.end_event_id IS NULL AND ewc.event_name IS NOT NULL,
'Still Waiting',
sys.format_time(ewc.timer_wait)) last_wait_latency,
ewc.source,
sys.format_time(etc.timer_wait) AS trx_latency,
etc.state AS trx_state,
etc.autocommit AS trx_autocommit,
conattr_pid.attr_value as pid,
conattr_progname.attr_value as program_name
FROM performance_schema.threads AS pps
LEFT JOIN performance_schema.events_waits_current AS ewc USING (thread_id)
LEFT JOIN performance_schema.events_stages_current AS estc USING (thread_id)
LEFT JOIN performance_schema.events_statements_current AS esc USING (thread_id)
LEFT JOIN performance_schema.events_transactions_current AS etc USING (thread_id)
LEFT JOIN sys.x$memory_by_thread_by_current_bytes AS mem USING (thread_id)
LEFT JOIN performance_schema.session_connect_attrs AS conattr_pid
ON conattr_pid.processlist_id=pps.processlist_id and conattr_pid.attr_name='_pid'
LEFT JOIN performance_schema.session_connect_attrs AS conattr_progname
ON conattr_progname.processlist_id=pps.processlist_id and conattr_progname.attr_name='program_name'
ORDER BY pps.processlist_time DESC, last_wait_latency DESC;
# 带x$前缀的视图查询语句与不带x$前缀的视图查询语句相比，只是少了单位格式化函数
......
下面我们看看使用该视图查询返回的结果
# 不带x$前缀的视图
admin@localhost : sys 04:27:20> select * from processlist where program_name='mysql' and trx_state is not null limit 1\G
*************************** 1. row ***************************
thd_id: 49
conn_id: 7
user: admin@localhost
db: sbtest
command: Sleep
state: NULL
time: 89
current_statement: NULL
statement_latency: NULL
progress: NULL
lock_latency: 157.00 us
rows_examined: 1000
rows_sent: 1000
rows_affected: 0
tmp_tables: 0
tmp_disk_tables: 0
full_scan: YES
last_statement: select * from sbtest1 limit 1000
last_statement_latency: 2.06 ms
current_memory: 0 bytes
last_wait: idle
last_wait_latency: Still Waiting
source: socket_connection.cc:69
trx_latency: 1.49 ms
trx_state: COMMITTED
trx_autocommit: YES
pid: 3927
program_name: mysql
1 row in set (0.13 sec)
# 带x$前缀的视图
admin@localhost : sys 04:27:28> select * from x$processlist where program_name='mysql' and trx_state is not null limit 1\G;
*************************** 1. row ***************************
thd_id: 49
conn_id: 7
user: admin@localhost
db: sbtest
command: Sleep
state: NULL
time: 150
current_statement: NULL
statement_latency: NULL
progress: NULL
lock_latency: 157000000
rows_examined: 1000
rows_sent: 1000
rows_affected: 0
tmp_tables: 0
tmp_disk_tables: 0
full_scan: YES
last_statement: select * from sbtest1 limit 1000
last_statement_latency: 2055762000
current_memory: 0
last_wait: idle
last_wait_latency: Still Waiting
source: socket_connection.cc:69
trx_latency: 1490662000
trx_state: COMMITTED
trx_autocommit: YES
pid: 3927
program_name: mysql
1 row in set (0.14 sec)
视图字段含义如下：
thd_id：内部threqd ID
conn_id：连接ID，即processlist id
user：对于前台线程，该字段值为account名称，对于后台线程，该字段值为后台线程名称
db：线程的默认数据库，如果没有默认数据库，则该字段值为NULL
command：对于前台线程，表示线程正在执行的客户端代码对应的command名称，如果会话处于空闲状态则该字段值为'Sleep ',对于后台超线程，该字段值为NULL
state：表示线程正在做什么：什么事件或状态，与information_schema.processlist表中的state字段值一样
time：表示线程处于当前状态已经持续了多长时间(秒)
current_statement：线程当前正在执行的语句，如果当前没有执行任何语句，该字段值为NULL
statement_latency：线程当前语句已经执行了多长时间，该字段在MySQL 5.7.9中新增
progress：在支持进度报告的阶段事件中统计的工作进度百分比。该字段在MySQL 5.7.9中新增
lock_latency：当前语句的锁等待时间
rows_examined：当前语句从存储引擎检查的数据行数
rows_sent：当前语句返回给客户端的数据行数
rows_affected：受当前语句影响的数据行数（DML语句对数据执行变更才会影响行）
tmp_tables：当前语句创建的内部内存临时表的数量
tmp_disk_tables：当前语句创建的内部磁盘临时表的数量
full_scan：当前语句执行的全表扫描次数
last_statement：如果在performance_schema.threads表中没有找到正在执行的语句或正在等待执行的语句，那么在该字段可以显示线程执行的最后一个语句(在performance_schema.events_statements_current表中查找，该表中会为每一个线程保留最后一条语句执行的事件信息，其他current后缀的事件记录表也类似)
last_statement_latency：线程执行的最近一个语句执行了多长时间
current_memory：当前线程分配的字节数
last_wait：线程最近的等待事件名称
last_wait_latency：线程最近的等待事件的等待时间(执行时间)
source：线程最近的等待事件的instruments所在源文件和行号
trx_latency：线程当前正在执行的事务已经执行了多长时间，该列在MySQL 5.7.9中新增
trx_state：线程当前正在执行的事务的状态，该列在MySQL 5.7.9中新增
trx_autocommit：线程当前正在执行的事务的提交模式，有效值为：'ACTIVE','COMMITTED','ROLLED BACK'，该列在MySQL 5.7.9中新增
pid：客户端进程ID，该列在MySQL 5.7.9中新增
program_name：客户端程序名称，该列在MySQL 5.7.9中新增
03 session,x$session
查看当前用户会话的进程列表信息，与processlist&x$processlist视图类似，但是session视图过滤掉了后台线程，只显示前台(用户)线程相关的统计数据，数据来源：sys.processlist
该视图在MySQL 5.7.9中新增
视图查询语句
# 不带x$的视图查询语句
## 只需要在processlist视图的查询语句上加上如下条件即可
......
[WHERE] conn_id IS NOT NULL AND command != 'Daemon';
# 带x$前缀的视图查询语句与不带x$前缀的视图查询语句相比，只是少了单位格式化函数
......
下面我们看看使用该视图查询返回的结果
# 不带x$前缀的视图
admin@localhost : sys 12:44:22> select * from session where command='query' and conn_id!=connection_id()\G
*************************** 1. row ***************************
thd_id: 48
conn_id: 6
user: admin@localhost
db: xiaoboluo
command: Query
state: Sending data
time: 72
current_statement: select * from test limit 1 for update
statement_latency: 1.20 m
progress: NULL
lock_latency: 169.00 us
rows_examined: 0
rows_sent: 0
rows_affected: 0
tmp_tables: 0
tmp_disk_tables: 0
full_scan: NO
last_statement: NULL
last_statement_latency: NULL
current_memory: 461 bytes
last_wait: wait/io/table/sql/handler
last_wait_latency: Still Waiting
source: handler.cc:3185
trx_latency: NULL
trx_state: NULL
trx_autocommit: NULL
pid: 3788
program_name: mysql
1 row in set (0.15 sec)
# 带x$前缀的视图
admin@localhost : sys 12:45:09> select * from x$session where command='query' and conn_id!=connection_id()\G;
*************************** 1. row ***************************
thd_id: 48
conn_id: 6
user: admin@localhost
db: xiaoboluo
command: Query
state: Sending data
time: 91
current_statement: select * from test limit 1 for update
statement_latency: 91077336919000
progress: NULL
lock_latency: 169000000
rows_examined: 0
rows_sent: 0
rows_affected: 0
tmp_tables: 0
tmp_disk_tables: 0
full_scan: NO
last_statement: NULL
last_statement_latency: NULL
current_memory: 461
last_wait: wait/io/table/sql/handler
last_wait_latency: Still Waiting
source: handler.cc:3185
trx_latency: NULL
trx_state: NULL
trx_autocommit: NULL
pid: 3788
program_name: mysql
1 row in set (0.13 sec)
视图字段含义
与processlist,x$processlist视图相同，但session视图排除了后台线程和Daemon线程，只查询用户连接线程的信息
04
schema_table_lock_waits,
x$schema_table_lock_waits
查看当前链接线程的MDL锁等待信息，显示哪些会话被MDL锁阻塞，是谁阻塞了这些会话，数据来源：performance_schema下的threads、metadata_locks、events_statements_current表
MDL锁的instruments默认没有启用，要使用需要显式开启，如下：
* 启用MDL锁的instruments：update setup_instruments set enabled='yes',timed='yes' where name='wait/lock/metadata/sql/mdl';
* 或者也可以使用sys 系统库下的辅助性视图操作：call sys.ps_setup_enable_instrument('wait/lock/metadata/sql/mdl');该视图在MySQL 5.7.9中新增
视图定义语句文本
# 不带x$的视图查询语句
SELECT g.object_schema AS object_schema,
g.object_name AS object_name,
pt.thread_id AS waiting_thread_id,
pt.processlist_id AS waiting_pid,
sys.ps_thread_account(p.owner_thread_id) AS waiting_account,
p.lock_type AS waiting_lock_type,
p.lock_duration AS waiting_lock_duration,
sys.format_statement(pt.processlist_info) AS waiting_query,
pt.processlist_time AS waiting_query_secs,
ps.rows_affected AS waiting_query_rows_affected,
ps.rows_examined AS waiting_query_rows_examined,
gt.thread_id AS blocking_thread_id,
gt.processlist_id AS blocking_pid,
sys.ps_thread_account(g.owner_thread_id) AS blocking_account,
g.lock_type AS blocking_lock_type,
g.lock_duration AS blocking_lock_duration,
CONCAT('KILL QUERY ', gt.processlist_id) AS sql_kill_blocking_query,
CONCAT('KILL ', gt.processlist_id) AS sql_kill_blocking_connection
FROM performance_schema.metadata_locks g
INNER JOIN performance_schema.metadata_locks p
ON g.object_type = p.object_type
AND g.object_schema = p.object_schema
AND g.object_name = p.object_name
AND g.lock_status = 'GRANTED'
AND p.lock_status = 'PENDING'
INNER JOIN performance_schema.threads gt ON g.owner_thread_id = gt.thread_id
INNER JOIN performance_schema.threads pt ON p.owner_thread_id = pt.thread_id
LEFT JOIN performance_schema.events_statements_current gs ON g.owner_thread_id = gs.thread_id
LEFT JOIN performance_schema.events_statements_current ps ON p.owner_thread_id = ps.thread_id
WHERE g.object_type = 'TABLE';
# 带x$前缀的视图查询语句与不带x$前缀的视图查询语句相比，只是少了单位格式化函数
......
下面我们看看使用该视图查询返回的结果
admin@localhost : sys 11:31:57> select * from schema_table_lock_waits\G;
*************************** 1. row ***************************
object_schema: xiaoboluo
object_name: test
waiting_thread_id: 1217
waiting_pid: 1175
waiting_account: admin@localhost
waiting_lock_type: EXCLUSIVE
waiting_lock_duration: TRANSACTION
waiting_query: alter table test add index i_k(test)
waiting_query_secs: 58
waiting_query_rows_affected: 0
waiting_query_rows_examined: 0
blocking_thread_id: 49
blocking_pid: 7
blocking_account: admin@localhost
blocking_lock_type: SHARED_WRITE
blocking_lock_duration: TRANSACTION
sql_kill_blocking_query: KILL QUERY 7
sql_kill_blocking_connection: KILL 7
*************************** 2. row ***************************
object_schema: xiaoboluo
object_name: test
waiting_thread_id: 1217
waiting_pid: 1175
waiting_account: admin@localhost
waiting_lock_type: EXCLUSIVE
waiting_lock_duration: TRANSACTION
waiting_query: alter table test add index i_k(test)
waiting_query_secs: 58
waiting_query_rows_affected: 0
waiting_query_rows_examined: 0
blocking_thread_id: 1217
blocking_pid: 1175
blocking_account: admin@localhost
blocking_lock_type: SHARED_UPGRADABLE
blocking_lock_duration: TRANSACTION
sql_kill_blocking_query: KILL QUERY 1175
sql_kill_blocking_connection: KILL 1175
2 rows in set (0.00 sec)
视图字段含义如下：
object_schema：发生MDL锁等待的schema名称
OBJECT_NAME：MDL锁等待监控对象的名称
waiting_thread_id：正在等待MDL锁的thread ID
waiting_pid：正在等待MDL锁的processlist ID
waiting_account：正在等待MDL锁的线程关联的account名称
waiting_lock_type：被阻塞的线程正在等待的MDL锁类型
waiting_lock_duration：该字段来自元数据锁子系统中的锁定时间。有效值为：STATEMENT、TRANSACTION、EXPLICIT，STATEMENT和TRANSACTION值分别表示在语句或事务结束时会释放的锁。EXPLICIT值表示可以在语句或事务结束时被会保留，需要显式释放的锁，例如：使用FLUSH TABLES WITH READ LOCK获取的全局锁
waiting_query：正在等待MDL锁的线程对应的语句文本
waiting_query_secs：正在等待MDL锁的语句已经等待了多长时间（秒）
waiting_query_rows_affected：受正在等待MDL锁的语句影响的数据行数（该字段来自performance_schema.events_statement_current表，该表中记录的是语句事件，如果语句是多表联结查询，则该语句可能已经执行了一部分DML语句，所以哪怕该语句当前被其他线程阻塞了，被阻塞线程的这个字段也可能出现大于0的值）
waiting_query_rows_examined：正在等待MDL锁的语句从存储引擎检查的数据行数（同理，该字段来自performance_schema.events_statement_current表）
blocking_thread_id：持有MDL锁的thread ID
blocking_pid：持有MDL锁的processlist ID
blocking_account：持有MDL锁的线程关联的account名称
blocking_lock_type：持有MDL锁的锁类型
blocking_lock_duration：与waiting_lock_duration字段解释相同，只是该值与持有MDL锁的线程相关
sql_kill_blocking_query：生成的KILL掉持有MDL锁的查询的语句
sql_kill_blocking_connection：生成的KILL掉持有MDL锁对应会话的语句
