创建测试数据库和表 t1

mysql> CREATE DATABASE test;

mysql> USE test;

mysql> CREATE TABLE t1 (
       col1 INT,
       col2 CHAR(10),
       col3 VARCHAR(10))
       ENGINE = InnoDB;

mysql> CREATE INDEX i1 ON t1(col1);

创建表 t1 后，查询 INNODB_TABLES 以找到 test/t1 的元数据

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1' \G
*************************** 1. row ***************************
     TABLE_ID: 71
         NAME: test/t1
         FLAG: 1
       N_COLS: 6
        SPACE: 57
   ROW_FORMAT: Compact
ZIP_PAGE_SIZE: 0
 INSTANT_COLS: 0

表 t1 的 TABLE_ID 为 71。 FLAG 字段提供有关表格式和存储特性的位级信息。共有六列，其中三列是由 InnoDB 创建的隐藏列（DB_ROW_ID、DB_TRX_ID 和 DB_ROLL_PTR）。表的 SPACE ID 为 57（值为 0 表示该表位于系统表空间中）。ROW_FORMAT 为 Compact。ZIP_PAGE_SIZE 仅适用于具有 Compressed 行格式的表。INSTANT_COLS 显示在使用带有 ALGORITHM=INSTANT 的 ALTER TABLE ... ADD COLUMN 添加第一个即时列之前的表中的列数。

使用 INNODB_TABLES 中的 TABLE_ID 信息，查询 INNODB_COLUMNS 表以获取有关表列的信息。

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_COLUMNS where TABLE_ID = 71\G
*************************** 1. row ***************************
     TABLE_ID: 71
         NAME: col1
          POS: 0
        MTYPE: 6
       PRTYPE: 1027
          LEN: 4
  HAS_DEFAULT: 0
DEFAULT_VALUE: NULL
*************************** 2. row ***************************
     TABLE_ID: 71
         NAME: col2
          POS: 1
        MTYPE: 2
       PRTYPE: 524542
          LEN: 10
  HAS_DEFAULT: 0
DEFAULT_VALUE: NULL
*************************** 3. row ***************************
     TABLE_ID: 71
         NAME: col3
          POS: 2
        MTYPE: 1
       PRTYPE: 524303
          LEN: 10
  HAS_DEFAULT: 0
DEFAULT_VALUE: NULL

除了 TABLE_ID 和列 NAME 之外，INNODB_COLUMNS 还提供每个列的序号位置（POS）（从 0 开始并按顺序递增）、列 MTYPE 或““主类型””（6 = INT，2 = CHAR，1 = VARCHAR）、PRTYPE 或““精确类型””（一个二进制值，其位表示 MySQL 数据类型、字符集代码和可空性）以及列长度（LEN）。HAS_DEFAULT 和 DEFAULT_VALUE 列仅适用于使用带有 ALGORITHM=INSTANT 的 ALTER TABLE ... ADD COLUMN 即时添加的列。

再次使用 INNODB_TABLES 中的 TABLE_ID 信息，查询 INNODB_INDEXES 以获取有关与表 t1 关联的索引的信息。

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_INDEXES WHERE TABLE_ID = 71 \G
*************************** 1. row ***************************
       INDEX_ID: 111
           NAME: GEN_CLUST_INDEX
       TABLE_ID: 71
           TYPE: 1
       N_FIELDS: 0
        PAGE_NO: 3
          SPACE: 57
MERGE_THRESHOLD: 50
*************************** 2. row ***************************
       INDEX_ID: 112
           NAME: i1
       TABLE_ID: 71
           TYPE: 0
       N_FIELDS: 1
        PAGE_NO: 4
          SPACE: 57
MERGE_THRESHOLD: 50

INNODB_INDEXES 返回两个索引的数据。第一个索引是 GEN_CLUST_INDEX，它是 InnoDB 在表没有用户定义的聚簇索引时创建的聚簇索引。第二个索引（i1）是用户定义的辅助索引。

INDEX_ID 是索引的标识符，在实例中的所有数据库中都是唯一的。TABLE_ID 标识索引关联的表。索引 TYPE 值指示索引的类型（1 = 聚簇索引，0 = 辅助索引）。N_FILEDS 值是构成索引的字段数。PAGE_NO 是索引 B 树的根页码，SPACE 是索引所在表空间的 ID。非零值表示该索引不在系统表空间中。MERGE_THRESHOLD 定义索引页中数据量的百分比阈值。如果在删除行或更新操作缩短行时，索引页中的数据量低于此值（默认值为 50%），则 InnoDB 会尝试将该索引页与其相邻的索引页合并。

使用 INNODB_INDEXES 中的 INDEX_ID 信息，查询 INNODB_FIELDS 以获取有关索引 i1 字段的信息。

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FIELDS where INDEX_ID = 112 \G
*************************** 1. row ***************************
INDEX_ID: 112
    NAME: col1
     POS: 0

INNODB_FIELDS 提供索引字段的 NAME 及其在索引中的序号位置。如果索引 (i1) 是在多个字段上定义的，则 INNODB_FIELDS 将提供每个索引字段的元数据。

使用 INNODB_TABLES 中的 SPACE 信息，查询 INNODB_TABLESPACES 表以获取有关表表空间的信息。

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE SPACE = 57 \G
*************************** 1. row ***************************
          SPACE: 57
          NAME: test/t1
          FLAG: 16417
    ROW_FORMAT: Dynamic
     PAGE_SIZE: 16384
 ZIP_PAGE_SIZE: 0
    SPACE_TYPE: Single
 FS_BLOCK_SIZE: 4096
     FILE_SIZE: 114688
ALLOCATED_SIZE: 98304
AUTOEXTEND_SIZE: 0
SERVER_VERSION: 8.4.0
 SPACE_VERSION: 1
    ENCRYPTION: N
         STATE: normal

除了表空间的 SPACE ID 和关联表的 NAME 之外，INNODB_TABLESPACES 还提供表空间 FLAG 数据，它是有关表空间格式和存储特性的位级信息。还提供了表空间 ROW_FORMAT、PAGE_SIZE 以及其他几个表空间元数据项。

再次使用 INNODB_TABLES 中的 SPACE 信息，查询 INNODB_DATAFILES 以获取表空间数据文件的位置。

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_DATAFILES WHERE SPACE = 57 \G
*************************** 1. row ***************************
SPACE: 57
 PATH: ./test/t1.ibd

数据文件位于 MySQL data 目录下的 test 目录中。如果使用 CREATE TABLE 语句的 DATA DIRECTORY 子句在 MySQL 数据目录之外的位置创建了 每个表一个文件 的表空间，则表空间 PATH 将是一个完全限定的目录路径。

最后一步，在表 t1（TABLE_ID = 71）中插入一行并在 INNODB_TABLESTATS 表中查看数据。MySQL 优化器使用此表中的数据来计算在查询 InnoDB 表时使用哪个索引。此信息来自内存数据结构。

mysql> INSERT INTO t1 VALUES(5, 'abc', 'def');
Query OK, 1 row affected (0.06 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLESTATS where TABLE_ID = 71 \G
*************************** 1. row ***************************
         TABLE_ID: 71
             NAME: test/t1
STATS_INITIALIZED: Initialized
         NUM_ROWS: 1
 CLUST_INDEX_SIZE: 1
 OTHER_INDEX_SIZE: 0
 MODIFIED_COUNTER: 1
          AUTOINC: 0
        REF_COUNT: 1

STATS_INITIALIZED 字段指示是否已为该表收集统计信息。NUM_ROWS 是表中当前估计的行数。CLUST_INDEX_SIZE 和 OTHER_INDEX_SIZE 字段分别报告存储表的聚簇索引和辅助索引的磁盘页数。MODIFIED_COUNTER 值显示由 DML 操作和外键级联操作修改的行数。AUTOINC 值是为任何基于自动递增的操作发出的下一个数字。表 t1 上没有定义自动递增列，因此该值为 0。REF_COUNT 值是一个计数器。当计数器达到 0 时，表示可以从表缓存中逐出表元数据。

使用父表和子表创建测试数据库

mysql> CREATE DATABASE test;

mysql> USE test;

mysql> CREATE TABLE parent (id INT NOT NULL,
       PRIMARY KEY (id)) ENGINE=INNODB;

mysql> CREATE TABLE child (id INT, parent_id INT,
    ->     INDEX par_ind (parent_id),
    ->     CONSTRAINT fk1
    ->     FOREIGN KEY (parent_id) REFERENCES parent(id)
    ->     ON DELETE CASCADE) ENGINE=INNODB;

创建父表和子表后，查询 INNODB_FOREIGN 并找到 test/child 和 test/parent 外键关系的外键数据

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN \G
*************************** 1. row ***************************
      ID: test/fk1
FOR_NAME: test/child
REF_NAME: test/parent
  N_COLS: 1
    TYPE: 1

元数据包括外键 ID（fk1），它是为在子表上定义的 CONSTRAINT 命名的。FOR_NAME 是定义外键的子表的名称。REF_NAME 是父表（““被引用”” 表）的名称。N_COLS 是外键索引中的列数。TYPE 是一个数值，表示提供有关外键列的附加信息的位标志。在这种情况下，TYPE 值为 1，这表明已为外键指定了 ON DELETE CASCADE 选项。有关 TYPE 值的更多信息，请参见 INNODB_FOREIGN 表定义。

使用外键 ID，查询 INNODB_FOREIGN_COLS 以查看有关外键列的数据。

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS WHERE ID = 'test/fk1' \G
*************************** 1. row ***************************
          ID: test/fk1
FOR_COL_NAME: parent_id
REF_COL_NAME: id
         POS: 0

FOR_COL_NAME 是子表中外键列的名称，REF_COL_NAME 是父表中被引用列的名称。POS 值是键字段在外键索引中的序号位置，从零开始。