概述¶
在学习 MySQL 数据字典之前，先一起了解一下什么是数据字典和元数据：
-
数据字典就类似于系统编目或花名册，它保存数据库服务器上的元数据信息（数据库的整体属性信息）。
-
元数据信息包括：数据库的属性信息、数据表的属性信息、字段的属性信息、视图的属性信息、用户信息、统计类信息等。
MySQL的元数据存在很多种类，包括 Schema，Table，Index，View，Tablespace 等等，描述了数据库中库，表，索引，视图，文件等属性，在 MySQL 中也被称为数据字典，每种资源都被抽象为 DD object，每个 DD object 有一个 Object_id 标识，MySQL 内部实现也是以简洁的多态和模版方式实现的。
元数据物理存储¶
在 MySQL 8.0 之前，Server 层和存储引擎层会各自保留一份元数据（schema name, table definition 等），不仅在信息存储上有着重复冗余，而且可能存在两者之间存储的元数据不同步的现象。字典数据存储在元数据文件、非事务性表和特定于存储引擎的数据字典中。
以上设计导致原子DDL变得非常困难，在 MySQL 8.0之前，如果DDL期间中发生crash，后期的恢复很容易出现各种问题，导致表无法访问、复制异常等。
在MySQL 8.0及更高版本中，引入了事务性数据字典（Transactional Data Dictionary，简称数据字典）。它完全基于InnoDB存储引擎，并且取代了早期版本中使用的各种非事务性元数据存储方式（如FRM、TRG、PAR等文件）。这意味着所有的系统元数据，包括表结构、列信息、索引、触发器、存储过程和函数等，现在都存储在InnoDB表中，从而确保了元数据操作（如创建表、修改列等）的原子性、一致性和隔离性。
DD实现了Server 层和存储引擎层之间统一的元数据管理，这些元数据都存储在 InnoDB 引擎的表中，Server 层和引擎层共享一份元数据，且支持原子性。
参考官方博客 https://dev.mysql.com/blog-archive/mysql-8-0-data-dictionary-architecture-and-design/
这种变化带来了几个显著的好处：
-
原子性：对元数据的所有更改都是原子的，要么全部成功，要么在失败时全部回滚，从而避免了元数据的不一致状态。
-
一致性：由于InnoDB支持事务，因此数据字典中的信息始终保持一致，即使在并发修改的情况下也是如此。
-
崩溃恢复：在发生故障后，InnoDB的恢复机制可以确保数据字典的完整性。
-
性能：InnoDB的缓存和事务管理特性有助于提高元数据操作的性能。
-
简化管理：不再需要维护多种不同类型的元数据文件和表，所有数据字典信息都集中存储在InnoDB表中，简化了数据库的管理和备份。
-
向后兼容性：尽管内部实现发生了重大变化，但MySQL努力保持了与早期版本的兼容性，以便现有的应用程序和脚本能够继续无缝工作。
这些元数据对应的 InnoDB引擎表我们一般称为**系统表**，其表结构是固定的直接定义在代码类结构中（因此不再需要记录额外的元数据，要不然就套娃了），对应表文件在整个 MySQL 进行初始化时就建立了，有如 tables、columns、indexes、foreign_keys等系统表。
数字字典视图¶
information_schema数据库现在提供了一组视图，这些视图作为访问事务性数据字典的接口。
这些视图提供了关于数据库对象的详细信息，如表、列、索引、触发器、存储过程和函数等。通过查询这些视图，管理员和开发人员可以获取关于数据库结构和属性的实时信息，而无需直接访问底层的InnoDB表。
在MySQL 8中，事务数据字典（Data Dictionary）是一个集中的元数据存储系统，它包含了关于数据库对象的各种信息。这些信息被组织成一系列的“字典表”，但实际上这些字典表并不直接暴露给用户。相反，用户通过information_schema数据库中的视图来访问这些字典表的内容。
以下是一些主要的information_schema视图，它们对应于事务数据字典中的不同部分，并提供了关于数据库对象的详细信息：
-
TABLES (information_schema.TABLES)
- 提供数据库中所有表的信息。
- 包括表名、表类型（BASE TABLE, VIEW等）、创建时间、最后修改时间等。
-
COLUMNS (information_schema.COLUMNS)
- 提供表中所有列的信息。
- 包括列名、数据类型、字符集、列默认值、是否可为NULL等。
-
STATISTICS (information_schema.STATISTICS)
- 提供关于表索引的信息。
- 在MySQL中，索引和键是同一个概念，因此这个视图也包含了主键、外键和唯一键的信息。
- 包括索引名、列名、索引类型（BTREE, HASH等）、是否唯一等。
-
KEY_COLUMN_USAGE (information_schema.KEY_COLUMN_USAGE)
- 描述哪些列被用作表的键（主键、外键等）。
- 提供列名、约束名、引用的表等信息。
-
ROUTINES (information_schema.ROUTINES)
- 提供关于存储过程和函数的信息。
- 包括例程名、例程类型（PROCEDURE, FUNCTION）、创建时间、SQL模式等。
-
TRIGGERS (information_schema.TRIGGERS)
- 提供关于触发器的信息。
- 包括触发器名、关联的表、触发时间（BEFORE, AFTER）、触发事件（INSERT, UPDATE, DELETE）等。
-
REFERENTIAL_CONSTRAINTS (information_schema.REFERENTIAL_CONSTRAINTS)
- 描述外键约束。
- 提供约束名、约束类型（通常是FOREIGN KEY）、涉及的表等。
-
TABLE_CONSTRAINTS (information_schema.TABLE_CONSTRAINTS)
- 提供关于表约束的信息，如主键、唯一键、检查约束等。
- 包括约束名、约束类型、约束状态等。
-
SCHEMATA (information_schema.SCHEMATA)
- 提供关于数据库中所有模式（数据库）的信息。
- 包括模式名、字符集、排序规则等。
-
VIEWS (information_schema.VIEWS)
- 提供关于数据库中所有视图的信息。
- 包括视图名、视图定义、安全类型（DEFINER, INVOKER）等。
-
PARTITIONS (information_schema.PARTITIONS)
- 如果表被分区，这个视图提供关于每个分区的详细信息。
- 包括分区名、分区方法（RANGE, LIST, HASH等）、分区表达式等。
数据字典内部¶
前面讲到，这些数据字典是通过information_schema中的各种来对外暴露查询接口，其实这些数据字典，每种资源都被抽象为 DD object，每个 DD object 有一个 Object_id 标识，MySQL 内部实现也是以简洁的多态和模版方式实现的。
通过将所有DD数据唯一地持久化到InnoDB存储引擎的DD tables，实现了DD的统一管理。为了避免每次访问DD都去存储中读取数据，使DD内存对象能够复用，DD实现了两级缓存的架构，这样在每个线程使用DD client访问DD时可以通过两级缓存来加速对DD的内存访问。
MySQL预留了多张系统的 DD 表来持久化和构建 DD object 信息，在 MySQL 8.0，这些系统的 DD 表存储 Innodb 引擎的共享 DD 系统表空间 (mysql.ibd) 中，是在 MySQL 初始化数据目录就构建好的，这些系统 DD 表在 debug 模式下可以像正常用户表一样访问，如 tables，columns, indexes 等表。
数据字典表是受保护的，只有在 MySQL 的 debug 版本中才能访问。编译 debug 版本参考： Compiling MySQL for Debugging
Data Dictionary Table是系统的根本，同时数据也非常敏感，必须保护这些表不受DDL的影响，同时不受人为的更改。不了解底层实现原理下，一旦更改那就是灾难性的，所以应该保持隐藏。强烈不建议直接修改或写入数据字典表，这可能会导致您的 MySQL 实例无法运行。
DD内部数据结构¶
数据字典的代码位于sql/dd目录，所有数据字典相关的信息都在dd这个命名空间中，各数据字典表本身的定义位于ql/dd/impl/tables目录的代码中，可以理解为数据字典表的元数据在代码中已经定义好了。所有的数据字典表对应的类，最终都是派生自dd::Object_table，便于统一处理。
https://dev.mysql.com/doc/dev/mysql-server/latest/classdd_1_1Object__table.html
https://dev.mysql.com/doc/dev/mysql-server/latest/object__table_8h_source.html
MySQL8在初始化和启动时，会使用Object_table_definition_impl::get_ddl()函数来获取m_target_def中信息所生成的DDL语句，创建出schemata表；使用Object_table_definition_impl::get_dml()获取DML语句，用于初始化表中的数据
dd::tables::Schemata类的继承关系，如图3。所有的数据字典表对应的类，最终都是派生自dd::Object_table，便于统一处理。
data dictionary提供了统一的client API供 Server 层和引擎层使用，包含对元数据访问的 acquire() / drop() / store() / update() 基本操作。
DD数据访问¶
"两级缓存+持久化"结构，整个MySQL 8.0的数据字典实现在数据字典对象分布上呈现这种三级存储的方式。
每个SQL连接中的工作线程需要访问DD时都会通过建立一个DD client（DD系统提供的一套通用的DD访问接口）来访问DD，具体流程为**通过与线程THD绑定的类Dictionary_client，来依次访问一级缓存和二级缓存，如果两级缓存中都没有要访问的DD对象，则会直接去存储在InnoDB的DD tables中去读取**。
DD对象定义的核心代码在dd/cache/object_registry.h.h中，每个 Object_registry由不同元数据类型的 Local_multi_map 组成，用于管理不同类型的对象字典得（比如表、schema、字符集、统计数据、Event 等）缓存。
Dictionary_client的核心代码在dd/cache/dictionary_client.h中，Dictionary_client做为THD的成员，被Server层的代码来使用访问DD。
底层实现了对 InnoDB 引擎存放的数据字典表的读写操作，包含开表(open table)、构造主键、主键查找等过程。client 和底层存储之间通过**两级缓存**来加速对元数据对象的内存访问，两级缓存都是基于 hash map 实现的：
-
一层缓存是 local 的，由每个 client（每个连接线程对应一个 client）独享，用于加速在当前线程中对于相同对象的重复访问；
-
二级缓存是全局 share 的，为所有线程共享的全局缓存(多线程共享的缓存)。
class Dictionary_client {
public:
class Auto_releaser {
};
}
缓存读取过程¶
DD对象主要有两种访问方式，即通过元数据的id，或者name来访问。需要访问DD的数据库工作线程通过DD client，传入元数据的id，name等key去缓存中读取元数据对象。读取的整体过程：一级本地缓存 -> 二级共享缓存 -> 存储引擎。流程图如下：
使用调试支持编译 MySQL 后，使用此 SET 语句使数据字典表对mysql客户端会话可见：
# 设置会话属性
mysql> SET SESSION debug='+d,skip_dd_table_access_check';
# 使用此查询检索数据字典表
mysql> SELECT name, schema_id, hidden, type FROM mysql.tables where schema_id=1 AND hidden='System';
# 用于 SHOW CREATE TABLE 查看数据字典表定义
mysql> SHOW CREATE TABLE mysql.catalogs
SELECT count(*) FROM information_schema.innodb_tables; -- 34
SELECT count(*) FROM information_schema.tables; -- 328
SELECT count(*) FROM mysql.tables; -- 361
SELECT count(*) FROM mysql.tables where schema_id=1 AND hidden='System'; -- 32张内部表
SELECT CONVERT( properties USING utf8mb4) as properties FROM mysql.dd_properties;
mysql>
mysql> SELECT id, name,schema_id , engine ,mysql_version_id , comment , hidden FROM mysql.tables where schema_id=1 AND hidden='System';
+----+------------------------------+-----------+--------+------------------+---------+--------+
| id | name | schema_id | engine | mysql_version_id | comment | hidden |
+----+------------------------------+-----------+--------+------------------+---------+--------+
| 6 | catalogs | 1 | InnoDB | 80039 | | System |
| 7 | character_sets | 1 | InnoDB | 80039 | | System |
| 8 | check_constraints | 1 | InnoDB | 80039 | | System |
| 9 | collations | 1 | InnoDB | 80039 | | System |
| 10 | column_statistics | 1 | InnoDB | 80039 | | System |
| 11 | column_type_elements | 1 | InnoDB | 80039 | | System |
| 12 | columns | 1 | InnoDB | 80039 | | System |
| 1 | dd_properties | 1 | InnoDB | 80039 | | System |
| 13 | events | 1 | InnoDB | 80039 | | System |
| 14 | foreign_key_column_usage | 1 | InnoDB | 80039 | | System |
| 15 | foreign_keys | 1 | InnoDB | 80039 | | System |
| 16 | index_column_usage | 1 | InnoDB | 80039 | | System |
| 17 | index_partitions | 1 | InnoDB | 80039 | | System |
| 18 | index_stats | 1 | InnoDB | 80039 | | System |
| 19 | indexes | 1 | InnoDB | 80039 | | System |
| 5 | innodb_ddl_log | 1 | InnoDB | 80039 | | System |
| 2 | innodb_dynamic_metadata | 1 | InnoDB | 80039 | | System |
| 20 | parameter_type_elements | 1 | InnoDB | 80039 | | System |
| 21 | parameters | 1 | InnoDB | 80039 | | System |
| 22 | resource_groups | 1 | InnoDB | 80039 | | System |
| 23 | routines | 1 | InnoDB | 80039 | | System |
| 24 | schemata | 1 | InnoDB | 80039 | | System |
| 25 | st_spatial_reference_systems | 1 | InnoDB | 80039 | | System |
| 26 | table_partition_values | 1 | InnoDB | 80039 | | System |
| 27 | table_partitions | 1 | InnoDB | 80039 | | System |
| 28 | table_stats | 1 | InnoDB | 80039 | | System |
| 29 | tables | 1 | InnoDB | 80039 | | System |
| 30 | tablespace_files | 1 | InnoDB | 80039 | | System |
| 31 | tablespaces | 1 | InnoDB | 80039 | | System |
| 32 | triggers | 1 | InnoDB | 80039 | | System |
| 33 | view_routine_usage | 1 | InnoDB | 80039 | | System |
| 34 | view_table_usage | 1 | InnoDB | 80039 | | System |
+----+------------------------------+-----------+--------+------------------+---------+--------+
32 rows in set (0.01 sec)
mysql>
mysql> select table_name from information_schema.tables where table_schema='mysql' ;
+------------------------------------------------------+
| TABLE_NAME |
+------------------------------------------------------+
| columns_priv |
| component |
| db |
| default_roles |
| engine_cost |
| func |
| general_log |
| global_grants |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| password_history |
| plugin |
| procs_priv |
| proxies_priv |
| replication_asynchronous_connection_failover |
| replication_asynchronous_connection_failover_managed |
| replication_group_configuration_version |
| replication_group_member_actions |
| role_edges |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+------------------------------------------------------+
37 rows in set (0.02 sec)
mysql>
CREATE TABLE `mysql`.`tables` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`schema_id` bigint unsigned NOT NULL,
`name` varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL,
`type` enum('BASE TABLE','VIEW','SYSTEM VIEW') COLLATE utf8mb3_bin NOT NULL,
`engine` varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
`mysql_version_id` int unsigned NOT NULL,
`row_format` enum('Fixed','Dynamic','Compressed','Redundant','Compact','Paged') COLLATE utf8mb3_bin DEFAULT NULL,
`collation_id` bigint unsigned DEFAULT NULL,
`comment` varchar(2048) COLLATE utf8mb3_bin NOT NULL,
`hidden` enum('Visible','System','SE','DDL') COLLATE utf8mb3_bin NOT NULL,
`options` mediumtext COLLATE utf8mb3_bin,
`se_private_data` mediumtext COLLATE utf8mb3_bin,
`se_private_id` bigint unsigned DEFAULT NULL,
`tablespace_id` bigint unsigned DEFAULT NULL,
`partition_type` enum('HASH','KEY_51','KEY_55','LINEAR_HASH','LINEAR_KEY_51','LINEAR_KEY_55','RANGE','LIST','RANGE_COLUMNS','LIST_COLUMNS','AUTO','AUTO_LINEAR') COLLATE utf8mb3_bin DEFAULT NULL,
`partition_expression` varchar(2048) COLLATE utf8mb3_bin DEFAULT NULL,
`partition_expression_utf8` varchar(2048) COLLATE utf8mb3_bin DEFAULT NULL,
`default_partitioning` enum('NO','YES','NUMBER') COLLATE utf8mb3_bin DEFAULT NULL,
`subpartition_type` enum('HASH','KEY_51','KEY_55','LINEAR_HASH','LINEAR_KEY_51','LINEAR_KEY_55') COLLATE utf8mb3_bin DEFAULT NULL,
`subpartition_expression` varchar(2048) COLLATE utf8mb3_bin DEFAULT NULL,
`subpartition_expression_utf8` varchar(2048) COLLATE utf8mb3_bin DEFAULT NULL,
`default_subpartitioning` enum('NO','YES','NUMBER') COLLATE utf8mb3_bin DEFAULT NULL,
`created` timestamp NOT NULL,
`last_altered` timestamp NOT NULL,
`view_definition` longblob,
`view_definition_utf8` longtext COLLATE utf8mb3_bin,
`view_check_option` enum('NONE','LOCAL','CASCADED') COLLATE utf8mb3_bin DEFAULT NULL,
`view_is_updatable` enum('NO','YES') COLLATE utf8mb3_bin DEFAULT NULL,
`view_algorithm` enum('UNDEFINED','TEMPTABLE','MERGE') COLLATE utf8mb3_bin DEFAULT NULL,
`view_security_type` enum('DEFAULT','INVOKER','DEFINER') COLLATE utf8mb3_bin DEFAULT NULL,
`view_definer` varchar(288) COLLATE utf8mb3_bin DEFAULT NULL,
`view_client_collation_id` bigint unsigned DEFAULT NULL,
`view_connection_collation_id` bigint unsigned DEFAULT NULL,
`view_column_names` longtext COLLATE utf8mb3_bin,
`last_checked_for_upgrade_version_id` int unsigned NOT NULL,
`engine_attribute` json DEFAULT NULL,
`secondary_engine_attribute` json DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `schema_id` (`schema_id`,`name`),
UNIQUE KEY `engine` (`engine`,`se_private_id`),
KEY `engine_2` (`engine`),
KEY `collation_id` (`collation_id`),
KEY `tablespace_id` (`tablespace_id`),
KEY `type` (`type`),
KEY `view_client_collation_id` (`view_client_collation_id`),
KEY `view_connection_collation_id` (`view_connection_collation_id`),
KEY `type_2` (`type`,`view_definer`),
CONSTRAINT `tables_ibfk_1` FOREIGN KEY (`schema_id`) REFERENCES `schemata` (`id`),
CONSTRAINT `tables_ibfk_2` FOREIGN KEY (`collation_id`) REFERENCES `collations` (`id`),
CONSTRAINT `tables_ibfk_3` FOREIGN KEY (`tablespace_id`) REFERENCES `tablespaces` (`id`),
CONSTRAINT `tables_ibfk_4` FOREIGN KEY (`view_client_collation_id`) REFERENCES `collations` (`id`),
CONSTRAINT `tables_ibfk_5` FOREIGN KEY (`view_connection_collation_id`) REFERENCES `collations` (`id`)
) /*!50100 TABLESPACE `mysql` */ ENGINE=InnoDB AUTO_INCREMENT=363 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin STATS_PERSISTENT=0 ROW_FORMAT=DYNAMIC
ysql> SELECT name, schema_id, hidden, type FROM mysql.tables;
+------------------------------------------------------+-----------+---------+-------------+
| name | schema_id | hidden | type |
+------------------------------------------------------+-----------+---------+-------------+
| dd_properties | 1 | System | BASE TABLE |
| innodb_dynamic_metadata | 1 | System | BASE TABLE |
| innodb_table_stats | 1 | Visible | BASE TABLE |
| innodb_index_stats | 1 | Visible | BASE TABLE |
| innodb_ddl_log | 1 | System | BASE TABLE |
| catalogs | 1 | System | BASE TABLE |
| character_sets | 1 | System | BASE TABLE |
| check_constraints | 1 | System | BASE TABLE |
| collations | 1 | System | BASE TABLE |
| column_statistics | 1 | System | BASE TABLE |
| column_type_elements | 1 | System | BASE TABLE |
| columns | 1 | System | BASE TABLE |
| events | 1 | System | BASE TABLE |
| foreign_key_column_usage | 1 | System | BASE TABLE |
| foreign_keys | 1 | System | BASE TABLE |
| index_column_usage | 1 | System | BASE TABLE |
| index_partitions | 1 | System | BASE TABLE |
| index_stats | 1 | System | BASE TABLE |
| indexes | 1 | System | BASE TABLE |
| parameter_type_elements | 1 | System | BASE TABLE |
| parameters | 1 | System | BASE TABLE |
| resource_groups | 1 | System | BASE TABLE |
| routines | 1 | System | BASE TABLE |
| schemata | 1 | System | BASE TABLE |
| st_spatial_reference_systems | 1 | System | BASE TABLE |
| table_partition_values | 1 | System | BASE TABLE |
| table_partitions | 1 | System | BASE TABLE |
| table_stats | 1 | System | BASE TABLE |
| tables | 1 | System | BASE TABLE |
| tablespace_files | 1 | System | BASE TABLE |
| tablespaces | 1 | System | BASE TABLE |
| triggers | 1 | System | BASE TABLE |
| view_routine_usage | 1 | System | BASE TABLE |
| view_table_usage | 1 | System | BASE TABLE |
| CHARACTER_SETS | 2 | Visible | SYSTEM VIEW |
| CHECK_CONSTRAINTS | 2 | Visible | SYSTEM VIEW |
| COLLATIONS | 2 | Visible | SYSTEM VIEW |
| COLLATION_CHARACTER_SET_APPLICABILITY | 2 | Visible | SYSTEM VIEW |
| COLUMNS | 2 | Visible | SYSTEM VIEW |
| COLUMNS_EXTENSIONS | 2 | Visible | SYSTEM VIEW |
| COLUMN_STATISTICS | 2 | Visible | SYSTEM VIEW |
| EVENTS | 2 | Visible | SYSTEM VIEW |
| FILES | 2 | Visible | SYSTEM VIEW |
| INNODB_DATAFILES | 2 | Visible | SYSTEM VIEW |
| INNODB_FOREIGN | 2 | Visible | SYSTEM VIEW |
| INNODB_FOREIGN_COLS | 2 | Visible | SYSTEM VIEW |
| INNODB_FIELDS | 2 | Visible | SYSTEM VIEW |
| INNODB_TABLESPACES_BRIEF | 2 | Visible | SYSTEM VIEW |
| KEY_COLUMN_USAGE | 2 | Visible | SYSTEM VIEW |
| KEYWORDS | 2 | Visible | SYSTEM VIEW |
| PARAMETERS | 2 | Visible | SYSTEM VIEW |
| PARTITIONS | 2 | Visible | SYSTEM VIEW |
| REFERENTIAL_CONSTRAINTS | 2 | Visible | SYSTEM VIEW |
| RESOURCE_GROUPS | 2 | Visible | SYSTEM VIEW |
| ROUTINES | 2 | Visible | SYSTEM VIEW |
| SCHEMATA | 2 | Visible | SYSTEM VIEW |
| SCHEMATA_EXTENSIONS | 2 | Visible | SYSTEM VIEW |
| SHOW_STATISTICS | 2 | System | SYSTEM VIEW |
| ST_SPATIAL_REFERENCE_SYSTEMS | 2 | Visible | SYSTEM VIEW |
| ST_UNITS_OF_MEASURE | 2 | Visible | SYSTEM VIEW |
| ST_GEOMETRY_COLUMNS | 2 | Visible | SYSTEM VIEW |
| STATISTICS | 2 | Visible | SYSTEM VIEW |
| TABLE_CONSTRAINTS | 2 | Visible | SYSTEM VIEW |
| TABLE_CONSTRAINTS_EXTENSIONS | 2 | Visible | SYSTEM VIEW |
| TABLES | 2 | Visible | SYSTEM VIEW |
| TABLES_EXTENSIONS | 2 | Visible | SYSTEM VIEW |
| TABLESPACES_EXTENSIONS | 2 | Visible | SYSTEM VIEW |
| TRIGGERS | 2 | Visible | SYSTEM VIEW |
| VIEW_ROUTINE_USAGE | 2 | Visible | SYSTEM VIEW |
| VIEW_TABLE_USAGE | 2 | Visible | SYSTEM VIEW |
| VIEWS | 2 | Visible | SYSTEM VIEW |
| COLUMN_PRIVILEGES | 2 | Visible | SYSTEM VIEW |
| ENGINES | 2 | Visible | SYSTEM VIEW |
| OPTIMIZER_TRACE | 2 | Visible | SYSTEM VIEW |
| PLUGINS | 2 | Visible | SYSTEM VIEW |
| PROCESSLIST | 2 | Visible | SYSTEM VIEW |
| PROFILING | 2 | Visible | SYSTEM VIEW |
| SCHEMA_PRIVILEGES | 2 | Visible | SYSTEM VIEW |
| TABLESPACES | 2 | Visible | SYSTEM VIEW |
| TABLE_PRIVILEGES | 2 | Visible | SYSTEM VIEW |
| USER_PRIVILEGES | 2 | Visible | SYSTEM VIEW |
| cond_instances | 3 | Visible | BASE TABLE |
| error_log | 3 | Visible | BASE TABLE |
| events_waits_current | 3 | Visible | BASE TABLE |
| events_waits_history | 3 | Visible | BASE TABLE |
| events_waits_history_long | 3 | Visible | BASE TABLE |
| events_waits_summary_by_host_by_event_name | 3 | Visible | BASE TABLE |
| events_waits_summary_by_instance | 3 | Visible | BASE TABLE |
| events_waits_summary_by_thread_by_event_name | 3 | Visible | BASE TABLE |
| events_waits_summary_by_user_by_event_name | 3 | Visible | BASE TABLE |
| events_waits_summary_by_account_by_event_name | 3 | Visible | BASE TABLE |
| events_waits_summary_global_by_event_name | 3 | Visible | BASE TABLE |
| file_instances | 3 | Visible | BASE TABLE |
| file_summary_by_event_name | 3 | Visible | BASE TABLE |
| file_summary_by_instance | 3 | Visible | BASE TABLE |
| host_cache | 3 | Visible | BASE TABLE |
| mutex_instances | 3 | Visible | BASE TABLE |
| objects_summary_global_by_type | 3 | Visible | BASE TABLE |
| performance_timers | 3 | Visible | BASE TABLE |
| processlist | 3 | Visible | BASE TABLE |
| rwlock_instances | 3 | Visible | BASE TABLE |
| setup_actors | 3 | Visible | BASE TABLE |
| setup_consumers | 3 | Visible | BASE TABLE |
| setup_instruments | 3 | Visible | BASE TABLE |
| setup_objects | 3 | Visible | BASE TABLE |
| setup_threads | 3 | Visible | BASE TABLE |
| table_io_waits_summary_by_index_usage | 3 | Visible | BASE TABLE |
| table_io_waits_summary_by_table | 3 | Visible | BASE TABLE |
| table_lock_waits_summary_by_table | 3 | Visible | BASE TABLE |
| threads | 3 | Visible | BASE TABLE |
| events_stages_current | 3 | Visible | BASE TABLE |
| events_stages_history | 3 | Visible | BASE TABLE |
| events_stages_history_long | 3 | Visible | BASE TABLE |
| events_stages_summary_by_thread_by_event_name | 3 | Visible | BASE TABLE |
| events_stages_summary_by_account_by_event_name | 3 | Visible | BASE TABLE |
| events_stages_summary_by_user_by_event_name | 3 | Visible | BASE TABLE |
| events_stages_summary_by_host_by_event_name | 3 | Visible | BASE TABLE |
| events_stages_summary_global_by_event_name | 3 | Visible | BASE TABLE |
| events_statements_current | 3 | Visible | BASE TABLE |
| events_statements_history | 3 | Visible | BASE TABLE |
| events_statements_history_long | 3 | Visible | BASE TABLE |
| events_statements_summary_by_thread_by_event_name | 3 | Visible | BASE TABLE |
| events_statements_summary_by_account_by_event_name | 3 | Visible | BASE TABLE |
| events_statements_summary_by_user_by_event_name | 3 | Visible | BASE TABLE |
| events_statements_summary_by_host_by_event_name | 3 | Visible | BASE TABLE |
| events_statements_summary_global_by_event_name | 3 | Visible | BASE TABLE |
| events_statements_summary_by_digest | 3 | Visible | BASE TABLE |
| events_statements_summary_by_program | 3 | Visible | BASE TABLE |
| events_statements_histogram_global | 3 | Visible | BASE TABLE |
| events_statements_histogram_by_digest | 3 | Visible | BASE TABLE |
| events_transactions_current | 3 | Visible | BASE TABLE |
| events_transactions_history | 3 | Visible | BASE TABLE |
| events_transactions_history_long | 3 | Visible | BASE TABLE |
| events_transactions_summary_by_thread_by_event_name | 3 | Visible | BASE TABLE |
| events_transactions_summary_by_account_by_event_name | 3 | Visible | BASE TABLE |
| events_transactions_summary_by_user_by_event_name | 3 | Visible | BASE TABLE |
| events_transactions_summary_by_host_by_event_name | 3 | Visible | BASE TABLE |
| events_transactions_summary_global_by_event_name | 3 | Visible | BASE TABLE |
| events_errors_summary_by_user_by_error | 3 | Visible | BASE TABLE |
| events_errors_summary_by_host_by_error | 3 | Visible | BASE TABLE |
| events_errors_summary_by_account_by_error | 3 | Visible | BASE TABLE |
| events_errors_summary_by_thread_by_error | 3 | Visible | BASE TABLE |
| events_errors_summary_global_by_error | 3 | Visible | BASE TABLE |
| users | 3 | Visible | BASE TABLE |
| accounts | 3 | Visible | BASE TABLE |
| hosts | 3 | Visible | BASE TABLE |
| socket_instances | 3 | Visible | BASE TABLE |
| socket_summary_by_instance | 3 | Visible | BASE TABLE |
| socket_summary_by_event_name | 3 | Visible | BASE TABLE |
| session_connect_attrs | 3 | Visible | BASE TABLE |
| session_account_connect_attrs | 3 | Visible | BASE TABLE |
| keyring_keys | 3 | Visible | BASE TABLE |
| memory_summary_global_by_event_name | 3 | Visible | BASE TABLE |
| memory_summary_by_account_by_event_name | 3 | Visible | BASE TABLE |
| memory_summary_by_host_by_event_name | 3 | Visible | BASE TABLE |
| memory_summary_by_thread_by_event_name | 3 | Visible | BASE TABLE |
| memory_summary_by_user_by_event_name | 3 | Visible | BASE TABLE |
| table_handles | 3 | Visible | BASE TABLE |
| metadata_locks | 3 | Visible | BASE TABLE |
| data_locks | 3 | Visible | BASE TABLE |
| data_lock_waits | 3 | Visible | BASE TABLE |
| replication_connection_configuration | 3 | Visible | BASE TABLE |
| replication_group_members | 3 | Visible | BASE TABLE |
| replication_connection_status | 3 | Visible | BASE TABLE |
| replication_applier_configuration | 3 | Visible | BASE TABLE |
| replication_applier_status | 3 | Visible | BASE TABLE |
| replication_applier_status_by_coordinator | 3 | Visible | BASE TABLE |
| replication_applier_status_by_worker | 3 | Visible | BASE TABLE |
| replication_group_member_stats | 3 | Visible | BASE TABLE |
| replication_applier_filters | 3 | Visible | BASE TABLE |
| replication_applier_global_filters | 3 | Visible | BASE TABLE |
| replication_asynchronous_connection_failover | 3 | Visible | BASE TABLE |
| replication_asynchronous_connection_failover_managed | 3 | Visible | BASE TABLE |
| log_status | 3 | Visible | BASE TABLE |
| prepared_statements_instances | 3 | Visible | BASE TABLE |
| user_variables_by_thread | 3 | Visible | BASE TABLE |
| status_by_account | 3 | Visible | BASE TABLE |
| status_by_host | 3 | Visible | BASE TABLE |
| status_by_thread | 3 | Visible | BASE TABLE |
| status_by_user | 3 | Visible | BASE TABLE |
| global_status | 3 | Visible | BASE TABLE |
| session_status | 3 | Visible | BASE TABLE |
| variables_by_thread | 3 | Visible | BASE TABLE |
| global_variables | 3 | Visible | BASE TABLE |
| session_variables | 3 | Visible | BASE TABLE |
| variables_info | 3 | Visible | BASE TABLE |
| persisted_variables | 3 | Visible | BASE TABLE |
| user_defined_functions | 3 | Visible | BASE TABLE |
| binary_log_transaction_compression_stats | 3 | Visible | BASE TABLE |
| tls_channel_status | 3 | Visible | BASE TABLE |
| keyring_component_status | 3 | Visible | BASE TABLE |
| db | 1 | Visible | BASE TABLE |
| user | 1 | Visible | BASE TABLE |
| default_roles | 1 | Visible | BASE TABLE |
| role_edges | 1 | Visible | BASE TABLE |
| global_grants | 1 | Visible | BASE TABLE |
| password_history | 1 | Visible | BASE TABLE |
| func | 1 | Visible | BASE TABLE |
| plugin | 1 | Visible | BASE TABLE |
| help_topic | 1 | Visible | BASE TABLE |
| help_category | 1 | Visible | BASE TABLE |
| help_relation | 1 | Visible | BASE TABLE |
| servers | 1 | Visible | BASE TABLE |
| tables_priv | 1 | Visible | BASE TABLE |
| columns_priv | 1 | Visible | BASE TABLE |
| help_keyword | 1 | Visible | BASE TABLE |
| time_zone_name | 1 | Visible | BASE TABLE |
| time_zone | 1 | Visible | BASE TABLE |
| time_zone_transition | 1 | Visible | BASE TABLE |
| time_zone_transition_type | 1 | Visible | BASE TABLE |
| time_zone_leap_second | 1 | Visible | BASE TABLE |
| procs_priv | 1 | Visible | BASE TABLE |
| general_log | 1 | Visible | BASE TABLE |
| slow_log | 1 | Visible | BASE TABLE |
| component | 1 | Visible | BASE TABLE |
| slave_relay_log_info | 1 | Visible | BASE TABLE |
| slave_master_info | 1 | Visible | BASE TABLE |
| slave_worker_info | 1 | Visible | BASE TABLE |
| gtid_executed | 1 | Visible | BASE TABLE |
| replication_asynchronous_connection_failover | 1 | Visible | BASE TABLE |
| replication_asynchronous_connection_failover_managed | 1 | Visible | BASE TABLE |
| replication_group_member_actions | 1 | Visible | BASE TABLE |
| replication_group_configuration_version | 1 | Visible | BASE TABLE |
| server_cost | 1 | Visible | BASE TABLE |
| engine_cost | 1 | Visible | BASE TABLE |
| proxies_priv | 1 | Visible | BASE TABLE |
| version | 4 | Visible | VIEW |
| sys_config | 4 | Visible | BASE TABLE |
| innodb_buffer_stats_by_schema | 4 | Visible | VIEW |
| x$innodb_buffer_stats_by_schema | 4 | Visible | VIEW |
| innodb_buffer_stats_by_table | 4 | Visible | VIEW |
| x$innodb_buffer_stats_by_table | 4 | Visible | VIEW |
| schema_object_overview | 4 | Visible | VIEW |
| schema_auto_increment_columns | 4 | Visible | VIEW |
| x$schema_flattened_keys | 4 | Visible | VIEW |
| schema_redundant_indexes | 4 | Visible | VIEW |
| ps_check_lost_instrumentation | 4 | Visible | VIEW |
| latest_file_io | 4 | Visible | VIEW |
| x$latest_file_io | 4 | Visible | VIEW |
| io_by_thread_by_latency | 4 | Visible | VIEW |
| x$io_by_thread_by_latency | 4 | Visible | VIEW |
| io_global_by_file_by_bytes | 4 | Visible | VIEW |
| x$io_global_by_file_by_bytes | 4 | Visible | VIEW |
| io_global_by_file_by_latency | 4 | Visible | VIEW |
| x$io_global_by_file_by_latency | 4 | Visible | VIEW |
| io_global_by_wait_by_bytes | 4 | Visible | VIEW |
| x$io_global_by_wait_by_bytes | 4 | Visible | VIEW |
| io_global_by_wait_by_latency | 4 | Visible | VIEW |
| x$io_global_by_wait_by_latency | 4 | Visible | VIEW |
| innodb_lock_waits | 4 | Visible | VIEW |
| x$innodb_lock_waits | 4 | Visible | VIEW |
| memory_by_user_by_current_bytes | 4 | Visible | VIEW |
| x$memory_by_user_by_current_bytes | 4 | Visible | VIEW |
| memory_by_host_by_current_bytes | 4 | Visible | VIEW |
| x$memory_by_host_by_current_bytes | 4 | Visible | VIEW |
| memory_by_thread_by_current_bytes | 4 | Visible | VIEW |
| x$memory_by_thread_by_current_bytes | 4 | Visible | VIEW |
| memory_global_by_current_bytes | 4 | Visible | VIEW |
| x$memory_global_by_current_bytes | 4 | Visible | VIEW |
| memory_global_total | 4 | Visible | VIEW |
| x$memory_global_total | 4 | Visible | VIEW |
| schema_index_statistics | 4 | Visible | VIEW |
| x$schema_index_statistics | 4 | Visible | VIEW |
| x$ps_schema_table_statistics_io | 4 | Visible | VIEW |
| schema_table_statistics | 4 | Visible | VIEW |
| x$schema_table_statistics | 4 | Visible | VIEW |
| schema_table_statistics_with_buffer | 4 | Visible | VIEW |
| x$schema_table_statistics_with_buffer | 4 | Visible | VIEW |
| schema_tables_with_full_table_scans | 4 | Visible | VIEW |
| x$schema_tables_with_full_table_scans | 4 | Visible | VIEW |
| schema_unused_indexes | 4 | Visible | VIEW |
| schema_table_lock_waits | 4 | Visible | VIEW |
| x$schema_table_lock_waits | 4 | Visible | VIEW |
| statement_analysis | 4 | Visible | VIEW |
| x$statement_analysis | 4 | Visible | VIEW |
| statements_with_errors_or_warnings | 4 | Visible | VIEW |
| x$statements_with_errors_or_warnings | 4 | Visible | VIEW |
| statements_with_full_table_scans | 4 | Visible | VIEW |
| x$statements_with_full_table_scans | 4 | Visible | VIEW |
| x$ps_digest_avg_latency_distribution | 4 | Visible | VIEW |
| x$ps_digest_95th_percentile_by_avg_us | 4 | Visible | VIEW |
| statements_with_runtimes_in_95th_percentile | 4 | Visible | VIEW |
| x$statements_with_runtimes_in_95th_percentile | 4 | Visible | VIEW |
| statements_with_sorting | 4 | Visible | VIEW |
| x$statements_with_sorting | 4 | Visible | VIEW |
| statements_with_temp_tables | 4 | Visible | VIEW |
| x$statements_with_temp_tables | 4 | Visible | VIEW |
| user_summary_by_file_io_type | 4 | Visible | VIEW |
| x$user_summary_by_file_io_type | 4 | Visible | VIEW |
| user_summary_by_file_io | 4 | Visible | VIEW |
| x$user_summary_by_file_io | 4 | Visible | VIEW |
| user_summary_by_statement_type | 4 | Visible | VIEW |
| x$user_summary_by_statement_type | 4 | Visible | VIEW |
| user_summary_by_statement_latency | 4 | Visible | VIEW |
| x$user_summary_by_statement_latency | 4 | Visible | VIEW |
| user_summary_by_stages | 4 | Visible | VIEW |
| x$user_summary_by_stages | 4 | Visible | VIEW |
| user_summary | 4 | Visible | VIEW |
| x$user_summary | 4 | Visible | VIEW |
| host_summary_by_file_io_type | 4 | Visible | VIEW |
| x$host_summary_by_file_io_type | 4 | Visible | VIEW |
| host_summary_by_file_io | 4 | Visible | VIEW |
| x$host_summary_by_file_io | 4 | Visible | VIEW |
| host_summary_by_statement_type | 4 | Visible | VIEW |
| x$host_summary_by_statement_type | 4 | Visible | VIEW |
| host_summary_by_statement_latency | 4 | Visible | VIEW |
| x$host_summary_by_statement_latency | 4 | Visible | VIEW |
| host_summary_by_stages | 4 | Visible | VIEW |
| x$host_summary_by_stages | 4 | Visible | VIEW |
| host_summary | 4 | Visible | VIEW |
| x$host_summary | 4 | Visible | VIEW |
| wait_classes_global_by_avg_latency | 4 | Visible | VIEW |
| x$wait_classes_global_by_avg_latency | 4 | Visible | VIEW |
| wait_classes_global_by_latency | 4 | Visible | VIEW |
| x$wait_classes_global_by_latency | 4 | Visible | VIEW |
| waits_by_user_by_latency | 4 | Visible | VIEW |
| x$waits_by_user_by_latency | 4 | Visible | VIEW |
| waits_by_host_by_latency | 4 | Visible | VIEW |
| x$waits_by_host_by_latency | 4 | Visible | VIEW |
| waits_global_by_latency | 4 | Visible | VIEW |
| x$waits_global_by_latency | 4 | Visible | VIEW |
| metrics | 4 | Visible | VIEW |
| processlist | 4 | Visible | VIEW |
| x$processlist | 4 | Visible | VIEW |
| session | 4 | Visible | VIEW |
| x$session | 4 | Visible | VIEW |
| session_ssl_status | 4 | Visible | VIEW |
| ENABLED_ROLES | 2 | Visible | SYSTEM VIEW |
| APPLICABLE_ROLES | 2 | Visible | SYSTEM VIEW |
| ADMINISTRABLE_ROLE_AUTHORIZATIONS | 2 | Visible | SYSTEM VIEW |
| ROLE_COLUMN_GRANTS | 2 | Visible | SYSTEM VIEW |
| ROLE_ROUTINE_GRANTS | 2 | Visible | SYSTEM VIEW |
| ROLE_TABLE_GRANTS | 2 | Visible | SYSTEM VIEW |
| USER_ATTRIBUTES | 2 | Visible | SYSTEM VIEW |
| INNODB_SESSION_TEMP_TABLESPACES | 2 | Visible | SYSTEM VIEW |
| INNODB_VIRTUAL | 2 | Visible | SYSTEM VIEW |
| INNODB_BUFFER_POOL_STATS | 2 | Visible | SYSTEM VIEW |
| INNODB_BUFFER_PAGE | 2 | Visible | SYSTEM VIEW |
| INNODB_CMPMEM_RESET | 2 | Visible | SYSTEM VIEW |
| INNODB_CMPMEM | 2 | Visible | SYSTEM VIEW |
| INNODB_TRX | 2 | Visible | SYSTEM VIEW |
| INNODB_CMP_PER_INDEX_RESET | 2 | Visible | SYSTEM VIEW |
| INNODB_CMP_RESET | 2 | Visible | SYSTEM VIEW |
| INNODB_FT_DEFAULT_STOPWORD | 2 | Visible | SYSTEM VIEW |
| INNODB_METRICS | 2 | Visible | SYSTEM VIEW |
| INNODB_TEMP_TABLE_INFO | 2 | Visible | SYSTEM VIEW |
| INNODB_FT_DELETED | 2 | Visible | SYSTEM VIEW |
| INNODB_TABLESTATS | 2 | Visible | SYSTEM VIEW |
| INNODB_CMP | 2 | Visible | SYSTEM VIEW |
| INNODB_TABLES | 2 | Visible | SYSTEM VIEW |
| INNODB_FT_BEING_DELETED | 2 | Visible | SYSTEM VIEW |
| INNODB_BUFFER_PAGE_LRU | 2 | Visible | SYSTEM VIEW |
| INNODB_CMP_PER_INDEX | 2 | Visible | SYSTEM VIEW |
| INNODB_FT_CONFIG | 2 | Visible | SYSTEM VIEW |
| INNODB_CACHED_INDEXES | 2 | Visible | SYSTEM VIEW |
| INNODB_FT_INDEX_TABLE | 2 | Visible | SYSTEM VIEW |
| INNODB_COLUMNS | 2 | Visible | SYSTEM VIEW |
| INNODB_FT_INDEX_CACHE | 2 | Visible | SYSTEM VIEW |
| INNODB_INDEXES | 2 | Visible | SYSTEM VIEW |
| INNODB_TABLESPACES | 2 | Visible | SYSTEM VIEW |
| innodb_redo_log_files | 3 | Visible | BASE TABLE |
+------------------------------------------------------+-----------+---------+-------------+
361 rows in set (0.02 sec)
mysql>
| DD 表名 | 说明 |
|---|---|
| catalogs | 目录信息。 |
| character_sets | 可用字符集的信息。 |
| check_constraints | 关于表上定义的CHECK约束的信息。 |
| collations | 关于每个字符集的排序规则的信息 。 |
| column_statistics | 列值的直方图统计信息。 |
| column_type_elements | 关于列使用的类型的信息。 |
| columns | 关于表中列的信息。 |
| dd_properties | 标识数据字典属性的表，例如它的版本。服务器使用它来确定是否必须将数据字典升级到较新的版本。 |
| events | 关于事件调度器事件的信息。 |
| foreign_keys, foreign_key_column_usage | 关于外键的信息。 |
| index_column_usage | 索引使用的列的信息。 |
| index_partitions | 关于索引使用的分区的信息。 |
| index_stats | 用于存储在执行ANALYZE TABLE时生成的动态索引统计信息。 |
| indexes | 关于表索引的信息。 |
| innodb_ddl_log | 存储DDL操作的崩溃安全日志。 |
| parameter_type_elements | 关于存储过程和函数参数的信息，以及关于存储函数返回值的信息。 |
| parameters | 存储过程和函数的相关信息。 |
| resource_groups | 资源组信息。 |
| schemata | 关于schemata的信息。 |
| st_spatial_reference_systems | 空间数据的可用空间参考系统的信息。 |
| table_partition_values | 表分区使用的值的信息。 |
| table_partitions | 关于表使用的分区的信息。 |
| table_stats | 执行ANALYZE table时生成的动态表统计信息。 |
| tables | 关于数据库中表的信息。 |
| tablespace_files | 表空间使用的文件信息。 |
| tablespaces | 活动表空间的信息。 |
| triggers | 触发器信息。 |
| view_routine_usage | 关于视图和视图使用的存储函数之间的依赖关系的信息。 |
| view_table_usage | 用于跟踪视图及其底层表之间的依赖关系。 |
参考
http://mysql.taobao.org/monthly/2021/08/02/ http://mysql.taobao.org/monthly/2023/10/03/ http://mysql.taobao.org/monthly/2022/01/04/