InnoDB 层系统字典表 | 全方位认识 information_schema

在《Server 层混杂信息字典表 | 全方位认识 information_schema》中，我们详细介绍了information_schema下的状态变量、系统变量、进程状态、字符集和校对规则等字典表，本期我们将为大家带来系列第五篇《InnoDB 层系统字典表 | 全方位认识 information_schema》，下面请跟随我们一起开始information_schema 系统库的系统学习之旅吧。

 

1、INNODB_SYS_DATAFILES

该表提供查询InnoDB file-per-table和常规表空间数据文件的路径信息，等同于InnoDB数据字典中SYS_DATAFILES表中的信息

• 该表为memory引擎临时表，查询该表的用户需要有process权限

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

下面是该表中存储的信息内容

admin@localhost : information_schema 06:26:55> select * from INNODB_SYS_DATAFILES limit 5;
+-------+---------------------------+
| SPACE | PATH                      |
+-------+---------------------------+
|    6 | ./mysql/plugin.ibd        |
|    7 | ./mysql/servers.ibd      |
|    8 | ./mysql/help_topic.ibd    |
|    9 | ./mysql/help_category.ibd |
|    10 | ./mysql/help_relation.ibd |
+-------+---------------------------+
5 rows in set (0.00 sec)

字段含义如下：

• SPACE：表空间文件ID

• PATH：表空间数据文件所在路径。如果独立表空间文件在MySQL数据目录中，则该路径为一个相对路径，如果在MySQL数据目录之外，则该路径值为一个绝对路径

 

2、INNODB_SYS_VIRTUAL

该表提供查询有关InnoDB虚拟生成列和与之关联的列的元数据信息，等同于InnoDB数据字典中SYS_VIRTUAL表中的信息

• INNODB_SYS_VIRTUAL表中展示的行信息是虚拟生成列相关联列的每个列的信息

• 该表为memory引擎临时表，查询该表的用户需要有process权限

下面是该表中存储的信息内容

# 建表
root@localhost : (none) 01:03:42> use test
Database changed
root@localhost : test 01:13:25> CREATE TABLE `t1` (
->          `a` int(11) DEFAULT NULL,
->          `b` int(11) DEFAULT NULL,
->          `c` int(11) GENERATED ALWAYS AS (a+b) VIRTUAL,
->          `h` varchar(10) DEFAULT NULL
->        );
Query OK, 0 rows affected (0.10 sec)

# 查询INNODB_SYS_VIRTUAL表
root@localhost : test 01:13:28> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_VIRTUAL
->        WHERE TABLE_ID IN (SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE "test/t1");
+----------+-------+----------+
| TABLE_ID | POS   | BASE_POS |
+----------+-------+----------+
|      487 | 65538 |        0 |
|      487 | 65538 |        1 |
+----------+-------+----------+
2 rows in set (0.00 sec)

# 如果将常量值分配给虚拟生成列（如以下示例中所示），则此时该列不会在INNODB_SYS_VIRTUAL表中记录（虚拟生成列必须是一个表达式在该表中才会进行记录）
root@localhost : test 01:13:42> CREATE TABLE `t2` (
->          `a` int(11) DEFAULT NULL,
->          `b` int(11) DEFAULT NULL,
->          `c` int(11) GENERATED ALWAYS AS (5) VIRTUAL
->        );
Query OK, 0 rows affected (0.04 sec)

root@localhost : test 01:15:56> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_VIRTUAL WHERE TABLE_ID IN (SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE "test/t2");
Empty set (0.00 sec)

# 虽然在innodb_sys_virtual表中未记录，但是此类列信息记录到了innodb_sys_columns表中
root@localhost : (none) 01:22:53> select c.* from information_schema.innodb_sys_tables as t join information_schema.innodb_sys_columns as c on t.table_id=c.table_id where t.name='test/t2';
+----------+------+-------+-------+--------+-----+
| TABLE_ID | NAME | POS   | MTYPE | PRTYPE | LEN |
+----------+------+-------+-------+--------+-----+
|      488 | a    |     0 |     6 |   1027 |   4 |
|      488 | b    |     1 |     6 |   1027 |   4 |
|      488 | c    | 65538 |     6 |   9219 |   4 |
+----------+------+-------+-------+--------+-----+
3 rows in set (0.00 sec)

字段含义如下：

• TABLE_ID：表示与虚拟列关联的表ID，与INNODB_SYS_TABLES.TABLE_ID字段值相同

• POS：虚拟生成列的位置顺序值。该数字较大，因为它同时编码了列序号和序号位置。且用于计算该值的公式使用了位移运算。公式为：((InnoDB实例的第n个虚拟生成列+ 1) << 16）+虚拟生成列的顺序位置值。例如：如果InnoDB实例中的第一个虚拟生成列是该表的第三列，则公式为（0 + 1）<< 16）+ 2

• BASE_POS：虚拟生成列的关联列的顺序位置

 

3、INNODB_SYS_INDEXES

该表提供查询有关InnoDB索引的元数据信息，等同于InnoDB数据字典内部SYS_INDEXES表中的信息

• 该表为memory引擎临时表，查询该表的用户需要具有process权限

下面是该表中存储的信息内容

admin@localhost : information_schema 06:39:20> select * from INNODB_SYS_INDEXES limit 1\G;
*************************** 1. row ***************************
  INDEX_ID: 11
      NAME: ID_IND
  TABLE_ID: 11
      TYPE: 3
  N_FIELDS: 1
    PAGE_NO: 45
      SPACE: 0
MERGE_THRESHOLD: 50
1 row in set (0.00 sec)

字段含义如下：

• INDEX_ID：索引ID，在实例中的所有数据库中唯一

• NAME：索引名称。索引名称可以在创建时指定，如果不指定InnoDB将隐含地创建名称与列名一致的索引，但索引名称在整个实例中不一定是唯一的(但在每个表中需要唯一)。例如：PRIMARY用于主键索引、GEN_CLUST_INDEX用于表示未指定主键时innodb隐式创建的一个主键索引、以及ID_IND，FOR_IND和REF_IND用于外键约束等

• TABLE_ID：表示与索引关联的表ID，该字段值与INNODB_SYS_TABLES.TABLE_ID的字段值相同

• TYPE：表示索引类型的数字ID，0 =二级索引、1 =集群索引、2 =唯一索引、3 &