创建一个测试数据库和表 t1：Create a test database and table 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 的元数据：After creating the table t1, query INNODB_TABLES to locate the metadata for 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 显示在使用 ALTER TABLE ... ADD COLUMN 并带 ALGORITHM=INSTANT 添加第一个即时列之前表中的列数。Table t1 has a TABLE_ID of 71. The FLAG field provides bit level information about table format and storage characteristics. There are six columns, three of which are hidden columns created by InnoDB (DB_ROW_ID, DB_TRX_ID, and DB_ROLL_PTR). The ID of the table's SPACE is 57 (a value of 0 would indicate that the table resides in the system tablespace). The ROW_FORMAT is Compact. ZIP_PAGE_SIZE only applies to tables with a Compressed row format. INSTANT_COLS shows number of columns in the table prior to adding the first instant column using ALTER TABLE ... ADD COLUMN with ALGORITHM=INSTANT.

使用来自 INNODB_TABLES 的 TABLE_ID 信息，查询 INNODB_COLUMNS 表以获取该表列的信息。Using the TABLE_ID information from INNODB_TABLES, query the INNODB_COLUMNS table for information about the table's 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 列仅适用于使用 ALTER TABLE ... ADD COLUMN 并带 ALGORITHM=INSTANT 即时添加的列。In addition to the TABLE_ID and column NAME, INNODB_COLUMNS provides the ordinal position (POS) of each column (starting from 0 and incrementing sequentially), the column MTYPE or “main type” (6 = INT, 2 = CHAR, 1 = VARCHAR), the PRTYPE or “precise type” (a binary value with bits that represent the MySQL data type, character set code, and nullability), and the column length (LEN). The HAS_DEFAULT and DEFAULT_VALUE columns only apply to columns added instantly using ALTER TABLE ... ADD COLUMN with ALGORITHM=INSTANT.

再次使用来自 INNODB_TABLES 的 TABLE_ID 信息，查询 INNODB_INDEXES 以获取与表 t1 相关的索引信息。Using the TABLE_ID information from INNODB_TABLES once again, query INNODB_INDEXES for information about the indexes associated with table 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）是用户定义的辅助索引。INNODB_INDEXES returns data for two indexes. The first index is GEN_CLUST_INDEX, which is a clustered index created by InnoDB if the table does not have a user-defined clustered index. The second index (i1) is the user-defined secondary index.

INDEX_ID 是索引的标识符，在一个实例中的所有数据库中都是唯一的。TABLE_ID 标识索引关联的表。索引的 TYPE 值表示索引的类型（1 = 聚集索引，0 = 次级索引）。N_FILEDS 值表示组成索引的字段数量。PAGE_NO 是索引 B 树的根页号，SPACE 是索引所在表空间的 ID。非零值表示索引不位于系统表空间中。MERGE_THRESHOLD 定义索引页中数据量的百分比阈值。如果在删除行或通过更新操作缩短行时，索引页中的数据量低于此值（默认值为 50%），InnoDB 会尝试将该索引页与相邻的索引页合并。The INDEX_ID is an identifier for the index that is unique across all databases in an instance. The TABLE_ID identifies the table that the index is associated with. The index TYPE value indicates the type of index (1 = Clustered Index, 0 = Secondary index). The N_FILEDS value is the number of fields that comprise the index. PAGE_NO is the root page number of the index B-tree, and SPACE is the ID of the tablespace where the index resides. A nonzero value indicates that the index does not reside in the system tablespace. MERGE_THRESHOLD defines a percentage threshold value for the amount of data in an index page. If the amount of data in an index page falls below the this value (the default is 50%) when a row is deleted or when a row is shortened by an update operation, InnoDB attempts to merge the index page with a neighboring index page.

使用来自 INNODB_INDEXES 的 INDEX_ID 信息，查询 INNODB_FIELDS 获取索引 i1 的字段信息。Using the INDEX_ID information from INNODB_INDEXES, query INNODB_FIELDS for information about the fields of index 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_FIELDS provides the NAME of the indexed field and its ordinal position within the index. If the index (i1) had been defined on multiple fields, INNODB_FIELDS would provide metadata for each of the indexed fields.

使用来自 INNODB_TABLES 的 SPACE 信息，查询 INNODB_TABLESPACES 表以获取关于表的表空间的信息。Using the SPACE information from INNODB_TABLES, query INNODB_TABLESPACES table for information about the table's tablespace.

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 以及其他若干表空间元数据项。In addition to the SPACE ID of the tablespace and the NAME of the associated table, INNODB_TABLESPACES provides tablespace FLAG data, which is bit level information about tablespace format and storage characteristics. Also provided are tablespace ROW_FORMAT, PAGE_SIZE, and several other tablespace metadata items.

再次使用来自 INNODB_TABLES 的 SPACE 信息，查询 INNODB_DATAFILES 获取表空间数据文件的位置。Using the SPACE information from INNODB_TABLES once again, query INNODB_DATAFILES for the location of the tablespace data file.

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 将是完全限定的目录路径。The datafile is located in the test directory under MySQL's data directory. If a file-per-table tablespace were created in a location outside the MySQL data directory using the DATA DIRECTORY clause of the CREATE TABLE statement, the tablespace PATH would be a fully qualified directory path.

作为最后一步，向表 t1（TABLE_ID = 71）插入一行数据，并查看 INNODB_TABLESTATS 表中的数据。该表中的数据被 MySQL 优化器用于计算在查询 InnoDB 表时使用哪个索引。这些信息来源于内存中的数据结构。As a final step, insert a row into table t1 (TABLE_ID = 71) and view the data in the INNODB_TABLESTATS table. The data in this table is used by the MySQL optimizer to calculate which index to use when querying an InnoDB table. This information is derived from in-memory data structures.

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 时，表示表的元数据可以从表缓存中驱逐。The STATS_INITIALIZED field indicates whether or not statistics have been collected for the table. NUM_ROWS is the current estimated number of rows in the table. The CLUST_INDEX_SIZE and OTHER_INDEX_SIZE fields report the number of pages on disk that store clustered and secondary indexes for the table, respectively. The MODIFIED_COUNTER value shows the number of rows modified by DML operations and cascade operations from foreign keys. The AUTOINC value is the next number to be issued for any autoincrement-based operation. There are no autoincrement columns defined on table t1, so the value is 0. The REF_COUNT value is a counter. When the counter reaches 0, it signifies that the table metadata can be evicted from the table cache.

创建包含父表和子表的测试数据库：Create the test database with parent and child tables:

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 外键关系的外键信息：After the parent and child tables are created, query INNODB_FOREIGN and locate the foreign key data for the test/child and test/parent foreign key relationship:

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 表定义。Metadata includes the foreign key ID (fk1), which is named for the CONSTRAINT that was defined on the child table. The FOR_NAME is the name of the child table where the foreign key is defined. REF_NAME is the name of the parent table (the “referenced” table). N_COLS is the number of columns in the foreign key index. TYPE is a numerical value representing bit flags that provide additional information about the foreign key column. In this case, the TYPE value is 1, which indicates that the ON DELETE CASCADE option was specified for the foreign key. See the INNODB_FOREIGN table definition for more information about TYPE values.

使用外键 ID，查询 INNODB_FOREIGN_COLS 以查看外键列的数据。Using the foreign key ID, query INNODB_FOREIGN_COLS to view data about the columns of the foreign key.

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 值是外键索引中键字段的序号位置，从零开始。FOR_COL_NAME is the name of the foreign key column in the child table, and REF_COL_NAME is the name of the referenced column in the parent table. The POS value is the ordinal position of the key field within the foreign key index, starting at zero.