MySQL(二)：MySQL中的系统库
1、概述
MySQL有几个系统数据库，包含了MySQL服务器运行过程中所需的一些信息以及一些运行状态信息。
| 系统库 | 作用 |
| performance_schema | MySQL服务器运行过程中的一些状态信息，是对MySQL服务器的一个性能监控，包括最近执行的语句，及花费的时间、内存使用情况等信息 |
| information_schema | MySQL服务器维护的所有其他数据库的信息，描述性信息，称为元数据。如有哪些表、哪些视图、哪些触发器、哪些列、哪些索引等 |
| mysql | 主要存储了MySQL的用户账户和权限信息 |
| sys | 通过视图的形式将information_schema和performance_schema结合起来，使用户更方便的了解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库下的表可以按照监视的不同维度进行分组，例如：按照不同的数据库对象进行分组、按照不同的事件类型进行分组，或者按照事件类型分组之后，再进一步按照账号、主机、程序、线程、用户等进行细分。
如按照事件类型分组记录性能事件数据的表：
|
记录语句事件信息的表
|
以 events_statements 开头
|
show tables like 'events_statement%'
|
|
等待事件记录表
|
以 events_wait 开头
|
show tables like 'events_wait%'
|
|
阶段事件记录表
|
以 events_stage 开头
|
show tables like 'events_stage%'
|
|
事务事件记录表
|
以 events_transaction 开头
|
show tables like 'events_transaction%'
|
|
监视文件系统层调用的表
|
包含 file
|
show tables like '%file%'
|
|
监视内存使用的表
|
包含 memory
|
show tables like '%memory%'
|
|
配置表
|
包含 setup
|
show tables like '%setup%'
|
官网地址：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层的统计信息字典表
|
表名
|
作用
|
|
COLUMNS
|
提供查询表中的列（字段）信息
|
|
KEY_COLUMN_USAGE
|
提供查询哪些索引列存在约束条件
|
|
REFERENTIAL_CONSTRAINTS
|
提供查询关于外键约束的一些信息
|
|
STATISTICS
|
提供查询关于索引的一些统计信息，一个索引对应一行记录
|
|
TABLE_CONSTRAINTS
|
提供查询与表相关的约束信息
|
|
FILES
|
提供查询与MySQL的数据表空间文件相关的信息
|
|
ENGINES
|
提供查询MySQL Server支持的引擎相关信息
|
|
TABLESPACES
|
提供查询关于活跃表空间的相关信息
|
|
SCHEMATA
|
提供查询MySQL Server中的数据库列表信息，一个schema就代表一个数据库
|
3.2.2、Server层的表级别对象字典表
|
表名
|
作用
|
|
VIEWS
|
提供查询数据库中的视图相关信息。查询该表的账户需要拥有show view权限
|
|
TRIGGERS
|
提供查询关于某个数据库下的触发器相关信息
|
|
TABLES
|
提供查询与数据库内的表相关的基本信息
|
|
ROUTINES
|
提供查询关于存储过程和存储函数的信息
|
|
PARTITIONS
|
提供查询关于分区表的信息
|
|
EVENTS
|
提供查询与计划任务事件相关的信息
|
|
PARAMETERS
|
提供有关存储过程和函数的参数信息，以及有关存储函数的返回值信息
|
3.2.3、Server层的混杂信息字典表
|
表名
|
作用
|
|
SESSION_VARIABLES
|
提供查询全局、会话级别的状态变量与系统变量信息
|
|
OPTIMIZER_TRACE
|
提供优化程序跟踪功能产生的信息
跟踪功能默认是关闭的，使用optimizer_trace系统变量启用跟踪功能
|
|
PLUGINS
|
提供查询关于MySQL Server支持哪些插件的信息
|
|
PROCESSLIST
|
提供查询一些关于线程运行过程中的状态信息
|
|
PROFILING
|
提供查询关于语句性能分析的信息
该表只有在会话变量 profiling=1时才会记录语句性能分析信息，否则该表不记录
|
|
CHARACTER_SETS
|
提供查询MySQL Server支持的可用字符集
|
|
COLLATIONS
|
提供查询MySQL Server支持的可用校对规则
|
|
COLLATION_CHARACTER_SET_APPLICABILITY
|
提供查询MySQL Server中哪种字符集适用于什么校对规则
|
|
COLUMN_PRIVILEGES
|
提供查询关于列（字段）的权限信息，表中的内容来自mysql.column_priv列权限表
|
|
SCHEMA_PRIVILEGES
|
提供查询关于库级别的权限信息，每种类型的库级别权限记录一行信息，该表中的信息来自mysql.db表
|
|
TABLE_PRIVILEGES
|
提供查询关于表级别的权限信息，该表中的内容来自mysql.tables_priv表
|
|
USER_PRIVILEGES
|
提供查询全局权限的信息，该表中的信息来自mysql.user表
|
3.2.4 InnoDB层的系统字典表
|
表名
|
作用
|
|
INNODB_SYS_DATAFILES
|
提供查询InnoDB所有表空间类型文件的元数据，内部使用的表空间ID和表空间文件的路径信息
该表中的信息等同于InnoDB数据字典内部 SYS_DATAFILES 表的信息
|
|
INNODB_SYS_VIRTUAL
|
提供查询有关InnoDB虚拟生成列和与之关联的列的元数据信息
等同于InnoDB数据字典内部 SYS_VIRTUAL 表的信息
|
|
INNODB_SYS_INDEXES
|
提供查询有关InnoDB索引的元数据信息
等同于InnoDB数据字典内部 SYS_INDEXES 表中的信息
|
|
INNODB_SYS_TABLES
|
提供查询有关InnoDB表的元数据信息
等同于InnoDB数据字典内部 SYS_TABLES 表的信息
|
|
INNODB_SYS_FIELDS
|
提供查询有关InnoDB索引键列（字段）的元数据信息
等同于InnoDB数据字典内部 SYS_FIELDS 表的信息
|
|
INNODB_SYS_TABLESPACES
|
提供查询有关InnoDB独立表空间和普通表空间的元数据信息
等同于InnoDB数据字典内部 SYS_TABLESPACES 表的信息。
|
|
INNODB_SYS_FOREIGN_COLS
|
提供查询有关InnoDB外键列的状态信息
等同于InnoDB数据字典内部 SYS_FOREIGN_COLS 表的信息
|
|
INNODB_SYS_COLUMNS
|
提供查询有关InnoDB表列的元数据信息
等同于InnoDB数据字典内部 SYS_COLUMNS 表的信息
|
|
INNODB_SYS_FOREIGN
|
提供查询有关InnoDB外键的元数据信息
等同于InnoDB数据字典内部 SYS_FOREIGN 表的信息
|
|
INNODB_SYS_TABLESTATS
|
提供查询有关InnoDB表的较低级别的状态信息视图，MySQL优化器会使用这些统计信息数据来计算并确定在查询InnoDB表时要使用哪个索引
信息保存在内存中的数据结构中，与存储在磁盘上的数据无对应关系，在InnoDB内部也无对应的系统表
|
3.2.5、InnoDB层的锁、事务、统计信息字典表
|
表名
|
作用
|
|
INNODB_LOCKS
|
提供查询InnoDB引擎中事务正在请求的且同时被其他事务阻塞的锁信息
该表中的内容可用于诊断高并发下的锁争用信息
|
|
INNODB_TRX
|
提供查询当前在InnoDB引擎中执行的每个事务（不包括只读事务）的信息
包括 事务是否正在等待锁、事务开始时间点，及事务正在执行的SQL文本信息
|
|
INNODB_BUFFER_PAGE_LRU
|
提供查询缓冲池中的页面信息
保存有关InnoDB缓冲池中的页如何进入LRU链表，以及在缓冲池不够用时确定需要从中逐出哪些页的信息
|
|
INNODB_LOCK_WAITS
|
提供查询InnoDB事务的锁等待信息
若该表为空，则表示无锁等待信息；若该表中有记录，则说明存在锁等待，表中的每一行记录表示一个锁等待关系
|
|
INNODB_TEMP_TABLE_INFO
|
提供查询有关在InnoDB实例中当前处于活动状态的用户（只对已建立连接的用户有效，断开的用户连接对应的临时表会被自动删除）创建的InnoDB临时表的信息
|
|
INNODB_BUFFER_PAGE
|
提供查询关于缓冲池中的页相关信息
|
|
INNODB_METRICS
|
提供查询InnoDB更为详细的性能信息，是对InnoDB的performance_schema的补充
|
|
INNODB_BUFFER_POOL_STATS
|
提供查询一些InnoDB缓冲池中的状态信息
|
3.2.6、InnoDB层的压缩相关字典表
|
表名
|
作用
|
|
INNODB_CMP
|
表中数据包含了与压缩的InnoDB表页有关的操作状态信息
表中记录的数据为测量数据库中的InnoDB表压缩的有效性提供参考。
|
|
INNODB_CMP_RESET
|
|
|
INNODB_CMP_PER_INDEX
|
记录了与InnoDB压缩表数据和索引相关的操作状态信息
对数据库、表、索引的每个组合使用不同的统计信息，以便为评估特定表的压缩性能和实用性提供参考数据
|
|
INNODB_CMP_PER_INDEX_RESET
|
|
|
INNODB_CMPMEM
|
记录了InnoDB缓冲池中压缩页的状态信息
为测量数据库中InnoDB表压缩的有效性提供参考
|
|
INNODB_CMPMEM_RESET
|
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库中，主要包含如下几个表。
|
columns_priv
|
字段级别的权限表
|
|
db
|
数据库级别的权限表
该表中记录的权限信息代表用户是否可以使用这些权限来访问被授予访问的数据库下的所有对象（表或存储程序）
|
|
procs_priv
|
存储过程和函数权限表
|
|
proxies_priv
|
代理用户权限表
|
|
tables_priv
|
表级别的权限表
|
|
user
|
包含用户账户、全局权限和其他非权限列表
|
若要更改权限表的内容，应该使用账号管理语句（如：REATE USER、GRANT、REVOKE等）来间接修改。
grant，revoke语句执行后会变更权限表中相关记录，同时会更新内存中记录用户权限的相关对象。
dml语句直接修改权限表只是修改了表中权限信息，需要执行flush privileges;来更新内存中保存用户权限的相关对象。
4.2、日志记录表
MySQL的日志系统包含：普通查询日志、慢查询日志、错误日志、二进制日志、中继日志、DDL日志。
|
错误日志
|
记录服务器启动时、运行中、停止时的错误信息
|
|
二进制日志
|
记录服务器运行过程中数据变更的逻辑日志
|
|
中继日志
|
记录从库I/O线程从主库获取的主库数据变更日志
|
|
DDL日志
|
记录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系统数据库中，其中：
|
innodb_table_stats
|
存储了关于表的统计数据，每一条记录对应着一个表的统计数据
|
|
innodb_index_stats
|
存储了关于索引的统计数据，每一条记录对应着一个索引的一个统计项的统计数据
|
2.1、innodb_table_stats
innodb_table_stats表中的各个列详情：
|
列名 |
含义 |
|
database_name |
数据库名 |
|
table_name |
表名 |
|
last_update |
本条记录最后更新时间 |
|
n_rows |
表中记录的条数 |
|
clustered_index_size |
表的聚簇索引占用的页面数量 |
|
sum_of_other_index_sizes |
表的其他索引占用的页面数量 |
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;
|
database_name |
数据库名 |
|
table_name |
表名 |
|
index_name |
索引名 |
|
last_update |
本条记录最后更新时间 |
|
stat_name |
统计项的名称 |
|
stat_value |
对应的统计项的值 |
|
sample_size |
为生成统计数据而采样的页面数量 |
|
stat_description |
对应的统计项的描述 |
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系统库 的大部分数据收集功能。启用所有需要功能会对性能产生一定的影响，因此最好仅启用所需的配置。
官网地址：https://dev.mysql.com/doc/refman/5.7/en/sys-schema.html。
