MySQL系统库之information_schema_mysql5.7 的schema是多少

information_schema介绍

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

在每个MySQL 实例中都有一个独立的information_schema，用来存储MySQL
实例中所有其他数据库的基本信息。information_schema库下包含多个只读表（非

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

针对information_schema下的表的查询操作可以替代一些 SHOW查询语句
（例如：SHOW DATABASES、SHOW TABLES 等）。

mysql> use test;

Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| dept           |
+----------------+
1 row in set (0.01 sec)

mysql> select * from information_schema.tables where table_schema='test'\G;
*************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: test
     TABLE_NAME: dept
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: 0
 AVG_ROW_LENGTH: 0
    DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 0
      DATA_FREE: 0
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2021-05-09 21:05:40
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: latin1_swedish_ci
       CHECKSUM: NULL
 CREATE_OPTIONS: 
  TABLE_COMMENT: 
1 row in set (0.00 sec)

mysql> select * from schemata;
+--------------+--------------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME        | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+--------------------+----------------------------+------------------------+----------+
| def          | information_schema | utf8                       | utf8_general_ci        | NULL     |
| def          | mysql              | latin1                     | latin1_swedish_ci      | NULL     |
| def          | performance_schema | utf8                       | utf8_general_ci        | NULL     |
| def          | sys                | utf8                       | utf8_general_ci        | NULL     |
| def          | test               | latin1                     | latin1_swedish_ci      | NULL     |
+--------------+--------------------+----------------------------+------------------------+----------+
5 rows in set (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

注意：根据MySQL版本的不同，表的个数和存放是有所不同的。在MySQL5.6
版本中总共有59个表，在MySQL5.7版本中，该schema下总共有61个表，
在MySQL 8.0版本中，该schema下的数据字典表（包含部分原Memory引
擎临时表）都迁移到了mysql schema下，且在mysql schema下这些数据字典表
被隐藏，无法直接访问，需要通过information_schema下的同名表进行访问。

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

information_schema表分类

Server层的统计信息字典表

• COLUMNS：提供查询表中的列（字段）信息。

• KEY_COLUMN_USAGE ：提供查询哪些索引列存在约束条件。
  该表中的信息包含主键、唯一索引、外键等约束信息，例如：所在的库
  表列名、引用的库表列名等。该表中的信息与TABLE_CONSTRAINTS表中记录的
  信息有些类似，但TABLE_CONSTRAINTS表中没有记录约束引用的库表列信息，
  而KEY_COLUMN_USAGE表中却记录了TABLE_CONSTRAINTS表中所没有的约束类
  型。

• REFERENTIAL_CONSTRAINTS：提供查询关于外键约束的一些信息。

• STATISTICS：提供查询关于索引的一些统计信息，一个索引对应一行记录。

• TABLE_CONSTRAINTS：提供查询与表相关的约束信息。

• FILES：提供查询与MySQL的数据表空间文件相关的信息。

• ENGINES：提供查询MySQL Server支持的引擎相关信息。

• TABLESPACES：提供查询关于活跃表空间的相关信息（主要记录的是NDB存储引擎的表
  空间信息）。
  注意：该表不提供有关InnoDB存储引擎的表空间信息。对于InnoDB表
  空间的元数据信息，请查询INNODB_SYS_TABLESPACES 表和
  INNODB_SYS_DATAFILES表。另外，从MySQL 5.7.8开始，
  INFORMATION_SCHEMA.FILES表也提供查询InnoDB表空间的元数据信息。

• SCHEMATA
  ：提供查询MySQL Server中的数据库列表信息，一个schema就代表一个
  数据库。

Server层的表级别对象字典表

• VIEWS：提供查询数据库中的视图相关信息。查询该表的账户需要拥有show view
  权限。

• TRIGGERS：提供查询关于某个数据库下的触发器相关信息。

• TABLES：提供查询与数据库内的表相关的基本信息。

• ROUTINES：提供查询关于存储过程和存储函数的信息（不包括用户自定义函数）。
  该表中的信息与mysql.proc中记录的信息相对应（如果该表中有值的话）。

• PARTITIONS：提供查询关于分区表的信息。

• EVENTS：提供查询与计划任务事件相关的信息。

• PARAMETERS：提供有关存储过程和函数的参数信息，以及有关存储函数的返回值信息。
  这些参数信息与 mysql.proc 表中的 param_list 列记录的内容类似。

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获得的结果集的前两个字段值。目前并没有发现该表
  有太大的作用。

• COLUMN_PRIVILEGES：提供查询关于列（字段）的权限信息，表中的内容来自 mysql.column_priv
  列权限表（需要针对一个表的列单独授权之后才会有内容）。

• SCHEMA_PRIVILEGES：提供查询关于库级别的权限信息，每种类型的库级别权限记录一行信息，
  该表中的信息来自mysql.db表。

• TABLE_PRIVILEGES：
  提供查询关于表级别的权限信息，该表中的内容来自mysql.tables_priv
  表。

• USER_PRIVILEGES：提供查询全局权限的信息，该表中的信息来自mysql.user表。

InnoDB 层的系统字典表

• INNODB_SYS_DATAFILES：提供查询InnoDB所有表空间类型文件的元数据（内部使用的表空间ID
  和表空间文件的路径信息），包括独立表空间、常规表空间、系统表空间、临时
  表空间和undo空间（如果开启了独立undo空间的话）。
  该表中的信息等同于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
  内部也无对应的系统表。

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

• INNODB_LOCKS：提供查询InnoDB引擎中事务正在请求的且同时被其他事务阻塞的锁信
  息（即没有发生不同事务之间锁等待的锁信息，在这里是查看不到的。例如，当
  只有一个事务时，无法查看到该事务所加的锁信息）。该表中的内容可用于诊断
  高并发下的锁争用信息。

• INNODB_TRX
  ：提供查询当前在InnoDB引擎中执行的每个事务（不包括只读事务）的信
  息，包括事务是否正在等待锁、事务什么时间点开始，以及事务正在执行的 SQL
  语句文本信息等（如果有 SQL 语句的话）。

• INNODB_BUFFER_PAGE_LRU
  ：提供查询缓冲池中的页面信息。与INNODB_BUFFER_PAGE表不同，
  INNODB_BUFFER_PAGE_LRU表保存有关InnoDB缓冲池中的页如何进入LRU链表，
  以及在缓冲池不够用时确定需要从中逐出哪些页的信息。

• INNODB_LOCK_WAITS：提供查询InnoDB事务的锁等待信息。如果查询该表为空，则表示无锁等
  待信息；如果查询该表中有记录，则说明存在锁等待，表中的每一行记录表示一
  个锁等待关系。在一个锁等待关系中包含：一个等待锁（即，正在请求获得锁）
  的事务及其正在等待的锁等信息、一个持有锁（这里指的是发生锁等待事务正在
  请求的锁）的事务及其所持有的锁等信息。

• INNODB_TEMP_TABLE_INFO
  ：提供查询有关在InnoDB实例中当前处于活动状态的用户（只对已建立连
  接的用户有效，断开的用户连接对应的临时表会被自动删除）创建的InnoDB临
  表的信息。它不提供查询优化器使用的内部InnoDB临时表的信息。该表在首
  次查询时创建。

• INNODB_BUFFER_PAGE：提供查询关于缓冲池中的页相关信息。

• INNODB_METRICS：提供查询InnoDB更为详细的性能信息，是对InnoDB的
  performance_schema的补充。通过对该表的查询，可用于检查InnoDB的整体健
  康状况，也可用于诊断性能瓶颈、资源短缺和应用程序的问题等。

• INNODB_BUFFER_POOL_STATS：提供查询一些InnoDB缓冲池中的状态信息，该表中记录的信息与SHOW
  ENGINEINNODB STATUS语句输出的缓冲池统计部分信息类似。另外，InnoDB缓
  冲池的一些状态变量也提供了部分相同的值。

InnoDB 层的全文索引字典表

• INNODB_FT_CONFIG

• INNODB_FT_BEING_DELETED

• INNODB_FT_DELETED

• INNODB_FT_DEFAULT_STOPWORD

• INNODB_FT_INDEX_TABLE

InnoDB 层的压缩相关字典表

• INNODB_CMP和INNODB_CMP_RESET：这两个表中的数据包含了与压缩的InnoDB表页有关的操作状态信息。表
  中记录的数据为测量数据库中的InnoDB表压缩的有效性提供参考。

• INNODB_CMP_PER_INDEX和INNODB_CMP_PER_INDEX_RESET：这两个表中记录了与InnoDB压缩表数据和索引相关的操作状态信息，对
  数据库、表、索引的每个组合使用不同的统计信息，以便为评估特定表的压缩性
  能和实用性提供参考数据。

• INNODB_CMPMEM和INNODB_CMPMEM_RESET：这两个表中记录了InnoDB缓冲池中压缩页的状态信息，为测量数据库中
  InnoDB表压缩的有效性提供参考。

information_schema 应用

查看索引列的信息

这里需要用到下面三个表：

• INNODB_SYS_FIELDS：提供查询有关InnoDB索引列（字段）的元数据信息。

• INNODB_SYS_INDEXES：提供查询有关InnoDB索引的元数据信息。

• INNODB_SYS_TABLES：提供查询有关InnoDB表的元数据信息。

假设需要查询test库下的InnoDB表t_order的索引列名称、组成和
索引列顺序等相关信息，
则可以使用如下 SQL 语句进行查询

mysql> select t.name as d_t_name,i.name as i_name ,i.type as i_type,i.N_FIELDS as i_column_numbers,f.name as i_column_name,f.pos as i_position from INNODB_SYS_TABLES as t join INNODB_SYS_INDEXES as i on t.TABLE_ID=i.TABLE_ID left join INNODB_SYS_FIELDS as f on i.INDEX_ID=f.INDEX_ID where t.name='test/t_order';
+--------------+------------------------------+--------+------------------+---------------+------------+
| d_t_name     | i_name                       | i_type | i_column_numbers | i_column_name | i_position |
+--------------+------------------------------+--------+------------------+---------------+------------+
| test/t_order | PRIMARY                      |      3 |                1 | id            |          0 |
| test/t_order | idx_create_time_order_status |      0 |                2 | create_time   |          0 |
| test/t_order | idx_create_time_order_status |      0 |                2 | order_status  |          1 |
+--------------+------------------------------+--------+------------------+---------------+------------+
3 rows in set (0.01 sec)
1
2
3
4
5
6
7
8
9

结果中的列都很好理解，唯一需要解释的是
i_type(INNODB_SYS_INDEXES.type)，它是表示索引类型的数字ID，

• 0：二级索引、

• 1：集群索引

• 2：唯一索引

• 3：主键索引

• 32：全文索引

• 64：空间索引

• 128：包含虚拟生成列的二级索引

使用下面的语句更方便的查询索引信息：

mysql> show index from test.t_order;
+---------+------------+------------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name                     | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+------------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t_order |          0 | PRIMARY                      |            1 | id           | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| t_order |          1 | idx_create_time_order_status |            1 | create_time  | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| t_order |          1 | idx_create_time_order_status |            2 | order_status | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+------------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9