1、概述

　　MySQL有几个系统数据库，包含了MySQL服务器运行过程中所需的一些信息以及一些运行状态信息。

2、performance_schema

2.1、performance_schema介绍

　　MySQL的 performance_schema 主要关注数据库运行过程中的性能相关数据，采集的内容相对比较底层，比如磁盘文件、表I/O、表锁等。

　　performance_schema 数据库使用 performance_schema 存储引擎

　　performance_schema通过监视Server的事件来实现监视其内部执行情况，”事件”就是在Server内部活动中所做的任何事情以及对应的时间消耗，利用这些信息来判断Server中的相关资源被消耗在哪里。一般来说，事件可以是函数调用、操作系统的等待、SQL语句执行的阶段[如SQL语句执行过程中的parsing（解析）或sorting（排序）阶段]或者整个SQL语句的集合。采集事件可以方便地提供Server中的相关存储引擎对磁盘文件、表I/O、表锁等资源的同步调用信息。

　　performance_schema存储引擎使用Server源代码中的 “检测点” 来实现事件数据的收集。对于performance_schema实现机制本身的代码没有相关的单独线程来检测，这与其他功能（如复制或事件计划程序）不同。

　　收集到的事件数据被存储在performance_schema数据库的表中。这些表可以使用SELECT语句查询，也可以使用SQL语句更新performance_schema数据库中的表记录（比如动态修改performance_schema的以“setup_”开头的配置表，要注意，配置表的更改会立即生效，这会影响数据收集）。

　　performance_schema的表中数据不会持久化存储在磁盘中，而是保存在内存中，一旦服务器重启，这些数据就会丢失（包括配置表在内的整个performance_schema下的所有数据）。

2.2、当前数据库版本是否支持 performance_schema 存储引擎

　　判断当前数据库是否支持 performance_schema，可通过 INFORMATION_SCHEMA.ENGINES表 或 show engines 语句的输出中可以看到它的Support字段值为YES。

select * from information_schema.ENGINES;
show engines;

 　　通过如下语句查看performance_schema启用是否生效（值为ON表示performance_schema已初始化成功且可以使用了；值为OFF表示在启用performance_schema时发生某些错误，可以查看错误日志进行排查）。

show variables like 'performance_schema';

 　　若要显式启用或关闭 performance_schema，则需要使用参数 performance_schema=ON|OFF 来设置，并在 my.cnf 中进行配置。该参数为只读参数，需要在实例启动之前设置才生效。

2.3、performance_schema表的分类

　　performance_schema库下的表可以按照监视的不同维度进行分组，例如：按照不同的数据库对象进行分组、按照不同的事件类型进行分组，或者按照事件类型分组之后，再进一步按照账号、主机、程序、线程、用户等进行细分。

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

　　官网地址：https://dev.mysql.com/doc/refman/5.7/en/performance-schema.html。

3、information_schema

3.1、information_schema介绍

　　information_schema中保存的信息也可以称为MySQL的数据字典或系统目录，提供了对数据库元数据、统计信息以及有关MySQL Server信息的访问，如数据库名或表名、字段的数据类型和访问权限等。

　　每个MySQL 实例中都有一个独立的information_schema，用来存储MySQL实例中所有其他数据库的基本信息。information_schema库下包含多个只读表（非持久表），所以在磁盘中的数据目录下没有对应的关联文件，且不能对这些表设置触发器。

　　在查询时可以使用USE语句将默认数据库设置为information_schema，但该库下的所有表是只读的，不能执行INSERT、UPDATE、DELETE等数据变更操作。

　　information_schema 下的所有表使用的都是Memory和InnoDB存储引擎，且都是临时表，不是持久表，在数据库重启之后这些数据会丢失。在MySQL 的4个系统库中，　　information_schema也是唯一一个在文件系统上没有对应库表的目录和文件的系统库。

3.2、information_schema表分类

3.2.1、Server层的统计信息字典表

3.2.2、Server层的表级别对象字典表

3.2.3、Server层的混杂信息字典表

3.2.4　InnoDB层的系统字典表

3.2.5、InnoDB层的锁、事务、统计信息字典表

3.2.6、InnoDB层的压缩相关字典表

3.2.7、InnoDB层的全文索引字典表

　　INNODB_FT_CONFIG、INNODB_FT_BEING_DELETED、INNODB_FT_DELETED、INNODB_FT_DEFAULT_STOPWORD、INNODB_FT_INDEX_TABLE。

4、mysql

4.1、权限系统表

　　在mysql系统库中，MySQL访问权限系统表，放在mysql库中，主要包含如下几个表。

　　若要更改权限表的内容，应该使用账号管理语句（如：REATE USER、GRANT、REVOKE等）来间接修改。

　　grant，revoke语句执行后会变更权限表中相关记录，同时会更新内存中记录用户权限的相关对象。

　　dml语句直接修改权限表只是修改了表中权限信息，需要执行flush privileges;来更新内存中保存用户权限的相关对象。

4.2、日志记录表

　　MySQL的日志系统包含：普通查询日志、慢查询日志、错误日志、二进制日志、中继日志、DDL日志。

　　在MySQL 5.7中只支持写入文件中，在MySQL 8.0中支持写入innodb_ddl_log表中。在MySQL5.7中，只有普通查询日志、慢查询日志支持写入表中（也支持写入文件中）,可以通过log_output=TABLE设置保存到mysql.general_log表和mysql.slow_log表中，其他日志类型在MySQL 5.7中只支持写入文件中。

　　general_log表提供查询普通SQL语句的执行记录信息，用于查看客户端到底在服务器上执行了什么SQL语句。缺省不开启:

show variables like 'general_log';

　　开启 general_log：

set global log_output='TABL,FILE'; -- 'TABLE,FILE'表示同时输出到表和文件 
set global general_log=on;

4.3、InnoDB中的统计数据

4.3.1、统计数据存储方式

　　InnoDB提供了两种存储统计数据的方式：永久性的统计数据、非永久性的统计数据。

　　永久性的统计数据：统计数据存储在磁盘上，即服务器重启之后这些统计数据还存在。

　　非永久性的统计数据：统计数据存储在内存中，当服务器关闭时这些这些统计数据就都被清除掉了，等到服务器重启之后，在某些适当的场景下才会重新收集这些统计数据。

　　MySQL提供了系统变量innodb_stats_persistent来控制到底采用哪种方式去存储统计数据。在MySQL 5.6.6之前，innodb_stats_persistent的值默认是OFF，即 InnoDB的统计数据默认是存储到内存的，之后的版本中innodb_stats_persistent的值默认是ON，即 统计数据默认被存储到磁盘中。

SHOW VARIABLES LIKE 'innodb_stats_persistent';

　　InnoDB默认是以表为单位来收集和存储统计数据的，把某些表的统计数据（以及该表的索引统计数据）存储在磁盘上，把另一些表的统计数据存储在内存中，需要在创建和修改表的时候通过指定STATS_PERSISTENT属性来指明该表的统计数据存储方式：

CREATE TABLE 表名 (...) Engine=InnoDB, STATS_PERSISTENT = (1|0);
ALTER TABLE 表名 Engine=InnoDB, STATS_PERSISTENT = (1|0);

　　当STATS_PERSISTENT=1时，把该表的统计数据永久的存储到磁盘上，当STATS_PERSISTENT=0时，把该表的统计数据临时的存储到内存中。如果我们在创建表时未指定STATS_PERSISTENT属性，那默认采用系统变量innodb_stats_persistent的值作为该属性的值。

4.3.2、基于磁盘的永久性统计数据

　　将某个表以及该表索引的统计数据存放到磁盘上时，实际上是把这些统计数据存储到了如下两个表里：

SHOW TABLES FROM mysql LIKE 'innodb%';

　　可以看到，这两个表都位于mysql系统数据库中，其中：

2.1、innodb_table_stats

　　innodb_table_stats表中的各个列详情：

n_rows统计项的收集，统计一个表中有多少行记录：

　　MySQL按照一定算法（并不是纯粹随机的）选取几个叶子节点页面，计算每个页面中主键值记录数量，然后计算平均一个页面中主键值的记录数量乘以全部叶子节点的数量就算是该表的n_rows值。

　　n_rows值精确与否取决于统计时采样的页面数量，MySQL用系统变量innodb_stats_persistent_sample_pages来控制使用永久性的统计数据时，计算统计数据时采样的页面数量。该值设置的越大，统计出的n_rows值越精确，但是统计耗时也就最久；该值设置的越小，统计出的n_rows值越不精确，但是统计耗时特别少。该系统变量的默认值是20。

　　InnoDB默认是以表为单位来收集和存储统计数据的，可设置某个表的采样页面的数量，设置方式就是在创建或修改表的时候通过指定STATS_SAMPLE_PAGES属性来指明该表的统计数据存储方式：

CREATE TABLE 表名 (...) Engine=InnoDB, STATS_SAMPLE_PAGES = 具体的采样页面数量;
ALTER TABLE 表名 Engine=InnoDB, STATS_SAMPLE_PAGES = 具体的采样页面数量;

　　若在创建表的语句中未指定STATS_SAMPLE_PAGES属性的话，将默认使用系统变量innodb_stats_persistent_sample_pages的值作为该属性的值。

2.2、innodb_index_stats

　　innodb_index_stats表中的各个列详情：

desc mysql.innodb_index_stats;

4.3.3、定期更新统计数据

　　随着不断的对表进行增删改操作，表中的数据一直在变化，innodb_table_stats和innodb_index_stats表里的统计数据也在变化。MySQL提供了如下两种更新统计数据的方式：自动更新、手动更新。

　　系统变量innodb_stats_auto_recalc决定着服务器是否自动重新计算统计数据，它的默认值是ON，开启的。每个表都维护了一个变量，该变量记录着对该表进行增删改的记录条数，如果发生变动的记录数量超过了表大小的10%，并且自动重新计算统计数据的功能是打开的，那么服务器会重新进行一次统计数据的计算，并且更新innodb_table_stats和innodb_index_stats表。

　　自动重新计算统计数据的过程是异步发生的，也就是即使表中变动的记录数超过了10%，自动重新计算统计数据也不会立即发生，可能会延迟几秒才会进行计算。

　　InnoDB默认是以表为单位来收集和存储统计数据的，可单独为某个表设置是否自动重新计算统计数的属性，设置方式就是在创建或修改表的时候通过指定STATS_AUTO_RECALC属性来指明该表的统计数据存储方式：

CREATE TABLE 表名 (...) Engine=InnoDB, STATS_AUTO_RECALC = (1|0);
ALTER TABLE 表名 Engine=InnoDB, STATS_AUTO_RECALC = (1|0);

　　当STATS_AUTO_RECALC=1时，该表自动重新计算统计数据，当STATS_AUTO_RECALC=0时，该表不会自动重新计算统计数据。若在创建表时未指定STATS_AUTO_RECALC属性，那默认采用系统变量innodb_stats_auto_recalc的值作为该属性的值。

5、sys

　　sys系统库支持MySQL 5.6或更高版本，不支持MySQL 5.5.x及以下版本。sys系统库提供DBA排查问题使用的，其下所涉及的各项查询或多或少都会对性能有一定的影响。

　　sys系统库提供了一些代替直接访问performance_schema的视图，所以必须启用performance_schema（将performance_schema系统参数设置为ON），sys系统库的大部分功能才能正常使用。

　　若要充分使用sys系统库的功能，则必须启用某些performance_schema的功能。

　　如启用所有的wait instruments：

CALL sys.ps_setup_enable_instrument('wait');

　　启用所有事件类型的current表：

CALL sys.ps_setup_enable_consumer('current');

　　performance_schema的默认配置就可以满足 sys系统库 的大部分数据收集功能。启用所有需要功能会对性能产生一定的影响，因此最好仅启用所需的配置。