文章目录

  • MySQL自带数据库
  • 自带数据库介绍
  • 1. mysql
  • 2. information_schema
  • Server层统计信息字典表
  • Server层表级别对象字典表
  • Server层其它信息字典表
  • InnoDB层系统字典表
  • InnoDB层锁、事务、统计信息字典表
  • InnoDB层全文索引字典表
  • InnoDB层压缩相关字典表
  • 3. performance_schema
  • 4. sys


MySQL自带数据库

自带数据库介绍

mysql 有四个自带的数据库：mysql、information_schema、performance_schema、sys

数据库

描述

mysql

存储MySQL服务器正常运行所需要的各种信息 （时区、主从、用户、权限等）

information_schema

提供了访问数据库元数据的各种表和视图，包含数据库、表、字段类型及访问权限等

performance_schema

提供了MySQL服务器运行时状态底层监控功能，主要用于收集数据库服务器性能参数

sys

包含了一系列方便 DBA 和开发人员利用 performance_schema性能数据库进行性能调优和诊断的视图

1. mysql

存储MySQL服务器正常运行所需要的各种信息。包括：

  • 数据字典表（Data Dictionary Tables）
    包括character_sets、collations、columns、events、foreign_keys、indexes、parameters、tables、triggers等。
    这些表包含数据字典（包含有关数据库对象的元数据）。
    数据字典表是不可见的。它们不能用SELECT读取，不在SHOW TABLES的输出中出现，不在INFORMATION_SCHEMA.TABLES表中列出。
    但在大多数情况下，可以查询对应的information_schema表。从概念上讲，information_schema提供了一个视图，MySQL通过该视图公开数据字典元数据。
  • 授权系统表（Grant System Tables）
    包括user、db、tables_priv、columns_priv、procs_priv等。其中包含有关用户帐户及其所拥有权限的信息。
  • 对象信息系统表（Object Information System Tables）
    包含组件、可加载函数和服务器端插件的信息。
  • 日志系统表（Log System Tables）
    包括通用查询日志表（general_log）和慢查询日志表（slow_log）。
  • 服务器端帮助系统表（Server-Side Help System Tables）
    这些系统表包含服务器端帮助信息。
  • 时区系统表（Time Zone System Tables）
    这些系统表包含时区信息。
  • 复制系统表（Replication System Tables）
    包括ndb_binlog_index、slave_master_info、slave_relay_log_info、slave_worker_info等。服务器使用这些系统表来支持复制。
  • 优化器系统表（Optimizer System Tables）
    包括innodb_index_stats、innodb_table_stats、server_cost、engine_cost。这些系统表供优化器使用。
  • 杂项系统表（Miscellaneous System Tables）
    包括firewall_group_allowlist、innodb_dynamic_metadata等。其中记录了自增计数器的值。

2. information_schema

information_schema提供了对数据库元数据、统计信息、以及有关MySQL Server的信息访问（例如：数据库名或表名，字段的数据类型和访问权限等）。该库中保存的信息也可以称为MySQL的数据字典或系统目录。information_schema可作为SHOW语句的替代方案。

information_schema数据库下包含多个只读表(临时表)，所以在磁盘中的数据目录下没有对应的关联文件，且不能对这些表设置触发器。虽然在查询时可以使用USE语句将默认数据库设置为information_schema，但该库下的所有表是只读的，不能执行INSERT、UPDATE、DELETE等数据变更操作。

不同MySQL版本中的information_schema：

  • MySQL 5.6版本：
    总共有59张表，其中10张MyISAM引擎临时表(数据字典表)，49张Memory引擎临时表(保存统计信息和一些临时信息)。
  • MySQL 5.7版本：
    总共有61张表，其中10个InnoDB存储引擎临时表(数据字典表)，51个Memory引擎临时表。
  • MySQL 8.0版本：
    数据字典表(包含部分原memory引擎临时表)都迁移到了mysql schema下，且在mysql schema下这些数据字典表被隐藏，无法直接访问，需要通过information_schema下的同名表进行访问(统计信息表保留在information_schema下且仍然为Memory引擎)

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

按照表的用途，可以把information_schema下的表分成一下几类:

Server层统计信息字典表

表名

作用

描述

COLUMNS

存储表中的列信息

KEY_COLUMN_USAGE

存储索引列存的约束条件

表中的信息包含主键、唯一索引、外键等约束的信息

REFERENTIAL_CONSTRAINTS

存储外键约束的一些信息

STATISTICS

存储索引的统计信息

一个索引对应一行记录

TABLE_CONSTRAINTS

存储表相关的约束信息

FILES

存储数据表空间文件相关的信息

包含InnoDB存储引擎和NDB存储引擎相关的数据文件信息

ENGINES

存储MySQL Server支持的引擎相关的信息

TABLESPACES

存储活跃表空间的相关信息

该表不提供有关InnoDB存储引擎的表空间的信息。 对于InnoDB表空间元数据信息，请查询INNODB_SYS_TABLESPACES和INNODB_SYS_DATAFILES表。另外，从MySQL 5.7.8开始，INFORMATION_SCHEMA.FILES表也提供查询InnoDB表空间的元数据信息

SCHEMATA

存储MySQL Server中的数据库列表信息

一个schema就代表一个database

Server层表级别对象字典表

表名

作用

描述

VIEWS

存储视图相关的信息

查询该表的帐号需要拥有show view权限

TRIGGERS

存储触发器相关的信息

查询该表的账户必须要有trigger权限

TABLES

存储表相关的基本信息

ROUTINES

存储存储过程和存储函数的信息

不包括用户自定义函数UDF，该表中的信息与“mysql.proc”中记录的信息相对应

PARTITIONS

存储分区表的信息

EVENTS

存储计划任务事件相关的信息

PARAMETERS

存储存储过程和存储函数的参数信息

Server层其它信息字典表

表名

作用

描述

GLOBAL_STATUS

存储全局状态变量信息

GLOBAL_VARIABLES

存储全局系统变量信息

SESSION_STATUS

存储会话级状态变量信息

SESSION_VARIABLES

存储会话级系统变量信息

OPTIMIZER_TRACE

存储优化程序跟踪功能产生的信息

跟踪功能默认关闭，使用optimizer_trace系统变量启用跟踪功能。如果开启该功能，则每个会话只能跟踪他自己执行的语句，不能看到其他会话执行的语句，且每个会话只能记录最后一个跟踪的SQL语句

PLUGINS

存储MySQL Server中支持的插件信息

PROCESSLIST

存储线程运行过程中的状态信息

PROFILING

存储语句性能分析的信息

其记录内容对应于SHOW PROFILES和SHOW PROFILE语句产生的信息。该表需要在会话变量 profiling=1时才会记录语句性能分析信息，否则该表不记录。


从MySQL 5.7.2开始，此表不再推荐使用，在未来的MySQL版本中删除。改用performance_schema代替

CHARACTER_SETS

存储MySQL Server支持的可用字符集信息

COLLATIONS

存储MySQL Server支持的可用校对规则信息

COLLATION_CHARACTER_SET_APPLICABILITY

存储MySQL Server字符集适用于的校对规则消息

查询结果集相当于从SHOW COLLATION获得的结果集中的前两个字段值。

SCHEMA_PRIVILEGES

存储库的权限信息

表中的信息来自mysql.db表

TABLE_PRIVILEGES

存储表的权限信息

表中的内容来自mysql.tables_priv表

COLUMN_PRIVILEGES

存储列的权限信息

表中的内容来自mysql.column_priv表

USER_PRIVILEGES

存储用户的权限信息

表中的信息来自mysql.user表

InnoDB层系统字典表

表名

作用

描述

INNODB_SYS_DATAFILES

存储InnoDB file-per-table和常规表空间数据文件的路径信息

等同于InnoDB数据字典中SYS_DATAFILES表中的信息

表中的信息包含InnoDB所有表空间类型的元数据，包括：独立表空间、常规表空间、系统表空间、临时表空间和undo表空间（如果开启了独立表空间的话）

查询该表的用户需要有process权限

INNODB_SYS_VIRTUAL

存储InnoDB虚拟生成列和与之关联的列的元数据信息

等同于InnoDB数据字典内部SYS_VIRTUAL表中的信息。INNODB_SYS_VIRTUAL表中展示的行信息是虚拟生成列相关联列的每个列的信息。

查询该表的用户需要有process权限

INNODB_SYS_INDEXES

存储InnoDB索引的元数据信息

等同于InnoDB数据字典内部SYS_INDEXES表中的信息

查询该表的用户需要有process权限

INNODB_SYS_TABLES

存储InnoDB表的元数据

等同于InnoDB数据字典内部SYS_TABLES表的信息。

查询该表的用户需要有process权限

INNODB_SYS_FIELDS

存储InnoDB索引键列的元数据信息

等同于InnoDB数据字典内部SYS_FIELDS表的信息

查询该表的用户需要有process权限

INNODB_SYS_TABLESPACES

存储InnoDB独立表空间和普通表空间的元数据信息（也包含了全文索引表空间）

等同于InnoDB数据字典内部SYS_TABLESPACES表中的信息

查询该表的用户需要有process权限

INNODB_SYS_FOREIGN_COLS

存储InnoDB外键列的状态信息

等同于InnoDB数据字典内部SYS_FOREIGN_COLS表的信息

查询该表的用户需要有process权限

INNODB_SYS_COLUMNS

存储InnoDB表列的元数据信息

等同于InnoDB数据字典内部SYS_COLUMNS表的信息

查询该表的用户需要具有process权限

INNODB_SYS_FOREIGN

存储InnoDB外键的元数据信息

等同于InnoDB数据字典内部SYS_FOREIGN表的信息

查询该表的用户需要具有process权限

INNODB_SYS_TABLESTATS

存储InnoDB表的较低级别的状态信息

MySQL优化器会使用这些统计信息数据来计算并确定在查询InnoDB表时要使用哪个索引。这些信息保存在内存中的数据结构中，与存储在磁盘上的数据无对应关系。InnoDB内部也无对应的系统表。

查询该表的用户需要具有process权限

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

表名

作用

描述

INNODB_LOCKS

存储InnoDB引擎事务中正在请求的且并未获得的且同时阻塞了其他事务的锁信息

即没有发生不同事务之间的锁等待的锁信息，在这里是查看不到的。该表中的内容可以用于诊断高并发下的锁争用信息。

查询该表的用户需要具有process权限

INNODB_TRX

存储InnoDB引擎中执行的每个事务（不包括只读事务）的信息

包括事务是否正在等待锁、事务什么时间点开始、以及事务正在执行的SQL语句文本信息等信息

查询该表的用户需要具有process权限

INNODB_BUFFER_PAGE

存储InnoDB buffer pool中的页相关的信息

查询该表的用户需要具有process权限

INNODB_BUFFER_PAGE_LRU

存储缓冲池中的页面信息

与INNODB_BUFFER_PAGE表不同，INNODB_BUFFER_PAGE_LRU表保存有关innodb buffer pool中的页如何进入LRU链表以及在buffer pool不够用时确定需要从缓冲池中逐出哪些页

INNODB_LOCK_WAITS

存储每个被阻塞的InnoDB事务的锁等待记录信息

包括发生锁等带事务所请求的锁和阻止该锁请求被授予的锁

查询该表的用户需要具有process权限

INNODB_TEMP_TABLE_INFO

存储InnoDB实例中当前处于活动状态的用户创建的InnoDB临时表的信息

只包含已建立连接的用户的临时表信息（断开的用户连接对应的临时表会被自动删除）。它不提供查询优化器使用的内部InnoDB临时表的信息查询。INNODB_TEMP_TABLE_INFO表在首次查询时创建。

查询该表的用户需要具有process权限

INNODB_METRICS

存储InnoDB更为详细细致的性能信息

对InnoDB的PERFORMANCE_SCHEMA的补充。通过对该表的查询，可用于检查innodb的整体健康状况。也可用于诊断性能瓶颈、资源短缺和应用程序的问题等。

查询该表的用户需要具有process权限

INNODB_BUFFER_POOL_STATS

存储InnoDB buffer pool中的状态信息

该表中记录的信息与SHOW ENGINE INNODB STATUS输出的信息类似相同，另外，innodb buffer pool的一些状态变量也提供了部分相同的值

查询该表的用户需要具有process权限

InnoDB层全文索引字典表

表名

作用

描述

INNODB_FT_CONFIG

存储InnoDB表的FULLTEXT索引和关联的元数据信息


查询该表的用户需要具有process权限

INNODB_FT_BEING_DELETED

存储INNODB_FT_DELETED表的快照数据

该表仅在OPTIMIZE TABLE语句执行维护操作期间作为INNODB_FT_DELETED表的快照数据存放使用。

运行OPTIMIZE TABLE语句时，会先清空INNODB_FT_BEING_DELETED表中的数据，保存INNODB_FT_DELETED表中的快照数据到INNODB_FT_BEING_DELETED表，并从INNODB_FT_DELETED表中删除DOC_ID。由于INNODB_FT_BEING_DELETED表中的内容通常生命周期较短，因此该表中的数据对于监控或者调试来说用处并不大

表中默认不记录数据，需要设置系统配置参数innodb_ft_aux_table=string（string表示db_name.tb_name字符串），并创建好全文索引，设置好停用词等

查询该表的用户需要具有process权限

INNODB_FT_DELETED

存储InnoDB表的FULLTEXT索引中删除的行信息

它的存在是为了避免在InnoDB FULLTEXT索引的DML操作期间进行昂贵的索引重组操作，新删除的全文索引中单词的信息将单独存储在该表中，在执行文本搜索时从中过滤出搜索结果，该表中的信息仅在执行OPTIMIZE TABLE语句时清空。

该表中的信息默认不记录，需要使用innodb_ft_aux_table选项(该选项默认值为空串)指定需要记录哪个innodb引擎表的信息，例如：test/test

查询该表的用户需要具有process权限

INNODB_FT_DEFAULT_STOPWORD

存储默认的全文索引停用词信息

提供查询停用词列表值。启用停用词表需要开启参数innodb_ft_enable_stopword=ON，该参数默认为ON，启用停用词功能之后，如果innodb_ft_user_stopword_table选项（针对指定的innodb引擎表中的全文索引生效）自定义了停用词库表名称值，则停用词功能使用innodb_ft_user_stopword_table选项指定的停用词表，如果innodb_ft_user_stopword_table选项未指定，而innodb_ft_server_stopword_table选项（针对所有的innodb引擎表中的全文索引生效）自定义了停用词库表名称值，则同停用词功能使用innodb_ft_server_stopword_table选项指定的停用词表，如果innodb_ft_server_stopword_table选项也未指定，则使用默认的停用词表，即INNODB_FT_DEFAULT_STOPWORD表。

查询该表的用户需要具有process权限

INNODB_FT_INDEX_TABLE

存储InnoDB表全文索引中用于反向文本查找的倒排索引的分词信息


查询该表的用户需要具有process权限

INNODB_FT_INDEX_CACHE

存储包含FULLTEXT索引的innodb存储引擎表中新插入行的全文索引标记信息

它存在的目的是为了避免在DML操作期间进行昂贵的索引重组，新插入的全文索引的单词的信息被单独存储在该表中，直到对表执行OPTIMIZE TABLE语句时、或者关闭服务器时、或者当高速缓存中存放的信息大小超过了innodb_ft_cache_size或innodb_ft_total_cache_size系统配置参数指定的大小才会执行清理。默认不记录数据，需要使用innodb_ft_aux_table系统配置参数指定需要记录哪个表中的新插入行的全文索引数据。

查询该表的用户需要具有process权限

InnoDB层压缩相关字典表

表名

作用

描述

INNODB_CMP、INNODB_CMP_RESET

存储压缩的InnoDB表页有关的操作的状态信息

表中记录的数据为测量数据库中的InnoDb表压缩的有效性提供参考。

查询该表的用户需要具有process权限

INNODB_CMP_PER_INDEX、INNODB_CMP_PER_INDEX_RESET

存储InnoDB压缩表数据和索引相关的操作状态信息

对数据库、表、索引的每个组合使用不同的统计信息，以便为评估特定表的压缩性能和实用性提供参考数据。

对于InnoDB压缩表，会对表中的数据和所有二级索引都进行压缩。此时表中的数据被视为另一个索引(包含所有数据列的聚集索引）。

查询该表的用户需要具有process权限

INNODB_CMPMEM、INNODB_CMPMEM_RESET

存储InnoDB缓冲池中压缩页上的状态信息

为测量数据库中InnoDB表压缩的有效性提供参考

查询该表的用户需要具有process权限

3. performance_schema

performance_schema用于在低级别监视MySQL服务器执行，提供了一种在运行时检查服务器内部执行的方法。

performance_schema中的表是不使用持久磁盘存储的内存表。内容在服务器启动时重新填充，并在服务器关闭时丢弃。

4. sys

sys主要是通过视图的形式把information_schema和performance_schema结合起来，帮助系统管理员和开发人员监控MySQL的技术性能。

可用于典型的调优和诊断用例。此架数据库中的对象包括：

  • 将performance_schema数据汇总为更易于理解的形式的视图（Views）。
  • 执行诸如performance_schema配置和生成诊断报告等操作的存储过程（Stored procedures）。
  • 查询performance_schema配置并提供格式化服务的存储函数（Stored functions）。

参考文章：

https://zhuanlan.zhihu.com/p/50036309