MySQL :: MySQL 8.3 Reference Manual :: 17.15.3 InnoDB INFORMATION

version 8.3

8.0 current
5.7

8.0 Japanese

MySQL 8.3 Reference Manual / ... / InnoDB INFORMATION_SCHEMA Schema Object Tables

17.15.3 InnoDB INFORMATION_SCHEMA模式对象表

您可以使用 InnoDB INFORMATION_SCHEMA 表来提取由 InnoDB 管理的模式对象的元数据。这些信息来自数据字典。传统上，您将使用第 17.17 节，“InnoDB 监控” 中的技术来获取这种类型的信息，设置 InnoDB 监控并解析 SHOW ENGINE INNODB STATUS 语句的输出。 InnoDB INFORMATION_SCHEMA 表接口允许您使用 SQL 查询这些数据。

InnoDB INFORMATION_SCHEMA 模式对象表包括以下表格。

INNODB_DATAFILES
INNODB_TABLESTATS
INNODB_FOREIGN
INNODB_COLUMNS
INNODB_INDEXES
INNODB_FIELDS
INNODB_TABLESPACES
INNODB_TABLESPACES_BRIEF
INNODB_FOREIGN_COLS
INNODB_TABLES

表名表明了提供的数据类型：

INNODB_TABLES 提供了 InnoDB 表的元数据。
INNODB_COLUMNS 提供了 InnoDB 表列的元数据。
INNODB_INDEXES 提供了 InnoDB 索引的元数据。
INNODB_FIELDS 提供了 InnoDB 索引的键列（字段）的元数据。
INNODB_TABLESTATS 提供了从内存数据结构派生的 InnoDB 表的低级状态信息视图。
INNODB_DATAFILES 提供了 InnoDB 文件每表和通用表空间的数据文件路径信息。
INNODB_TABLESPACES 提供了 InnoDB 文件每表、通用和撤销表空间的元数据。
INNODB_TABLESPACES_BRIEF 提供了 InnoDB 表空间的元数据子集。
INNODB_FOREIGN 提供了在 InnoDB 表上定义的外键的元数据。
INNODB_FOREIGN_COLS 提供了在 InnoDB 表上定义的外键列的元数据。

InnoDB INFORMATION_SCHEMA 模式对象表可以通过字段 such as TABLE_ID, INDEX_ID, and SPACE 连接在一起，允许您轻松检索要研究或监控的对象的所有可用数据。

请参阅 InnoDB INFORMATION_SCHEMA 文档，以获取每个表的列信息。

示例 17.2 InnoDB INFORMATION_SCHEMA 模式对象表

这个示例使用一个简单的表 (t1) 和一个索引 (i1) 来演示 InnoDB INFORMATION_SCHEMA 模式对象表中的元数据类型。

创建一个测试数据库和表 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 只适用于具有压缩行格式的表。INSTANT_COLS 显示了在使用 ALTER TABLE ... ADD COLUMN with ALGORITHM=INSTANT 添加第一列之前的列数。

使用 TABLE_ID 信息来自 INNODB_TABLES，查询 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 还提供了每个列的序号位置（从 0 开始，顺序递增），列的 MTYPE 或 “主类型”（6 = INT，2 = CHAR，1 = VARCHAR），PRTYPE 或 “精确类型”（一个二进制值，表示 MySQL 数据类型、字符集代码和可空性），以及列的长度（LEN）。HAS_DEFAULT 和 DEFAULT_VALUE 列仅适用于使用 ALTER TABLE ... ADD COLUMN with ALGORITHM=INSTANT 添加的列。

使用 TABLE_ID 信息来自 INNODB_TABLES，查询 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_FIELDS 值是索引中的字段数。PAGE_NO 是索引 B 树的根页号，SPACE 是索引所在的表空间的 ID。如果该值非零，则表示索引不在系统表空间中。MERGE_THRESHOLD 定义了索引页中的数据百分比阈值。如果索引页中的数据量低于该值（默认为 50%），InnoDB 将尝试将索引页与相邻索引页合并。
使用 INDEX_ID 信息来自 INNODB_INDEXES，查询 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 将提供每个索引字段的元数据。

使用 SPACE 信息来自 INNODB_TABLES, 查询 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.1.0
 SPACE_VERSION: 1
    ENCRYPTION: N
         STATE: normal

除了表空间的 SPACE ID 和关联表的 NAME 之外，INNODB_TABLESPACES 还提供了表空间的 FLAG 数据，即表空间格式和存储特征的位级信息。此外，还提供了表空间的 ROW_FORMAT、PAGE_SIZE 和其他表空间元数据项目。

再次使用 SPACE 信息来自 INNODB_TABLES, 查询 INNODB_DATAFILES 以获取表空间数据文件的位置。
```
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_DATAFILES WHERE SPACE = 57 \G
*************************** 1. row ***************************
SPACE: 57
 PATH: ./test/t1.ibd
```
数据文件位于 MySQL 的 data 目录下的 test 目录中。如果使用 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 时，表元数据可以从表缓存中删除。

示例 17.3 外键 INFORMATION_SCHEMA 模式对象表

表 INNODB_FOREIGN 和 INNODB_FOREIGN_COLS 提供了关于外键关系的数据。该示例使用父表和子表的外键关系来演示 INNODB_FOREIGN 和 INNODB_FOREIGN_COLS 表中的数据。

创建测试数据库，包括父表和子表：

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 选项。请参阅 INNODB_FOREIGN 表定义，以获取更多关于 TYPE 值的信息。
使用外键 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 值是外键索引中的键字段的序号位置，从零开始。

示例 17.4 连接 InnoDB INFORMATION_SCHEMA 模式对象表

这个示例演示了连接三个 InnoDB INFORMATION_SCHEMA 模式对象表 (INNODB_TABLES, INNODB_TABLESPACES 和 INNODB_TABLESTATS) 以获取 employees 示例数据库中表的文件格式、行格式、页面大小和索引大小信息。

以下表别名用于缩短查询字符串：

使用 IF() 控制流函数来处理压缩表。如果表被压缩，则使用 ZIP_PAGE_SIZE 而不是 PAGE_SIZE 计算索引大小。 CLUST_INDEX_SIZE 和 OTHER_INDEX_SIZE，以字节为单位，除以 1024*1024 以获取兆字节（MB）索引大小。MB 值使用 ROUND() 函数四舍五入到零小数位。

mysql> SELECT a.NAME, a.ROW_FORMAT,
        @page_size :=
         IF(a.ROW_FORMAT='Compressed',
          b.ZIP_PAGE_SIZE, b.PAGE_SIZE)
          AS page_size,
         ROUND((@page_size * c.CLUST_INDEX_SIZE)
          /(1024*1024)) AS pk_mb,
         ROUND((@page_size * c.OTHER_INDEX_SIZE)
          /(1024*1024)) AS secidx_mb
       FROM INFORMATION_SCHEMA.INNODB_TABLES a
       INNER JOIN INFORMATION_SCHEMA.INNODB_TABLESPACES b on a.NAME = b.NAME
       INNER JOIN INFORMATION_SCHEMA.INNODB_TABLESTATS c on b.NAME = c.NAME
       WHERE a.NAME LIKE 'employees/%'
       ORDER BY a.NAME DESC;
+------------------------+------------+-----------+-------+-----------+
| NAME                   | ROW_FORMAT | page_size | pk_mb | secidx_mb |
+------------------------+------------+-----------+-------+-----------+
| employees/titles       | Dynamic    |     16384 |    20 |        11 |
| employees/salaries     | Dynamic    |     16384 |    93 |        34 |
| employees/employees    | Dynamic    |     16384 |    15 |         0 |
| employees/dept_manager | Dynamic    |     16384 |     0 |         0 |
| employees/dept_emp     | Dynamic    |     16384 |    12 |        10 |
| employees/departments  | Dynamic    |     16384 |     0 |         0 |
+------------------------+------------+-----------+-------+-----------+

PREV HOME UP NEXT