14.16.3 InnoDB INFORMATION_SCHEMA 系统 table

您可以使用InnoDB INFORMATION_SCHEMA系统 table 提取有关InnoDBManagement 的架构对象的元数据。此信息来自InnoDB内部系统 table(也称为InnoDB数据字典),不能像常规InnoDBtable 那样直接查询。传统上,您将使用第 14.18 节“ InnoDB 监视器”的技术,设置InnoDB监视器并解析显示引擎的 INNODB 状态语句的输出来获取此类信息。 InnoDB INFORMATION_SCHEMAtable 接口允许您使用 SQL 查询此数据。

除了没有相应内部系统 table 的INNODB_SYS_TABLESTATS之外,InnoDB INFORMATION_SCHEMA系统 table 均填充有直接从内部InnoDB系统 table 读取的数据,而不是从缓存在内存中的元数据填充的数据。

InnoDB INFORMATION_SCHEMA系统 table 包括下面列出的 table。

mysql> SHOW TABLES FROM INFORMATION_SCHEMA LIKE 'INNODB_SYS%';
+--------------------------------------------+
| Tables_in_information_schema (INNODB_SYS%) |
+--------------------------------------------+
| INNODB_SYS_DATAFILES                       |
| INNODB_SYS_TABLESTATS                      |
| INNODB_SYS_FOREIGN                         |
| INNODB_SYS_COLUMNS                         |
| INNODB_SYS_INDEXES                         |
| INNODB_SYS_FIELDS                          |
| INNODB_SYS_TABLESPACES                     |
| INNODB_SYS_FOREIGN_COLS                    |
| INNODB_SYS_TABLES                          |
+--------------------------------------------+

table 名指示提供的数据类型:

  • INNODB_SYS_TABLES提供有关InnoDBtable 的元数据,等效于InnoDB数据字典中SYS_TABLEStable 中的信息。

  • INNODB_SYS_COLUMNS提供有关InnoDBtable 列的元数据,等效于InnoDB数据字典中SYS_COLUMNStable 中的信息。

  • INNODB_SYS_INDEXES提供有关InnoDB索引的元数据,等效于InnoDB数据字典中SYS_INDEXEStable 中的信息。

  • INNODB_SYS_FIELDS提供有关InnoDB索引的键列(字段)的元数据,等效于InnoDB数据字典中SYS_FIELDStable 中的信息。

  • INNODB_SYS_TABLESTATS提供了有关从内存中的数据结构派生的有关InnoDBtable 的低级状态信息的视图。没有相应的内部InnoDB系统 table。

  • INNODB_SYS_DATAFILES提供每 tableInnoDB文件和常规 table 空间的数据文件路径信息,等效于InnoDB数据字典中SYS_DATAFILEStable 中的信息。

  • INNODB_SYS_TABLESPACES提供有关每个 tableInnoDB文件和常规 table 空间的元数据,等效于InnoDB数据字典中SYS_TABLESPACEStable 中的信息。

  • INNODB_SYS_FOREIGN提供有关在InnoDBtable 上定义的外键的元数据,等效于InnoDB数据字典中SYS_FOREIGNtable 中的信息。

  • INNODB_SYS_FOREIGN_COLS提供有关在InnoDBtable 上定义的外键列的元数据,等效于InnoDB数据字典中SYS_FOREIGN_COLStable 中的信息。

InnoDB INFORMATION_SCHEMA系统 table 可以通过TABLE_IDINDEX_IDSPACE之类的字段连接在一起,从而使您可以轻松检索要研究或监视的对象的所有可用数据。

有关每个 table 的列的信息,请参考InnoDB INFORMATION_SCHEMA文档。

例 14.2 InnoDB INFORMATION_SCHEMA 系统 table

本示例使用具有单个索引(i1)的简单 table(t1)来演示在InnoDB INFORMATION_SCHEMA系统 table 中找到的元数据的类型。

  • 创建一个测试数据库和 tablet1
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);
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME='test/t1' \G
*************************** 1. row ***************************
     TABLE_ID: 71
         NAME: test/t1
         FLAG: 1
       N_COLS: 6
        SPACE: 57
  FILE_FORMAT: Antelope
   ROW_FORMAT: Compact
ZIP_PAGE_SIZE: 0
...

tablet1TABLE_ID为 71.FLAG字段提供有关 table 格式和存储特性的位级别信息。有六列,其中三列是由InnoDB(DB_ROW_IDDB_TRX_IDDB_ROLL_PTR)创建的隐藏列。该 table 的SPACE的 ID 为 57(值为 0table 示该 table 位于系统 table 空间中)。 FILE_FORMAT是羚羊,而ROW_FORMAT是紧凑型。 ZIP_PAGE_SIZE仅适用于Compressed行格式的 table。

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

除了TABLE_ID和列NAME之外,INNODB_SYS_COLUMNS还提供每列的 Sequences 位置(POS)(从 0 开始并 Sequences 递增),列MTYPE或“主要类型”(6 = INT,2 = CHAR,1 = VARCHAR) ,PRTYPE或“精确类型”(一个二进制值,带有 table 示 MySQL 数据类型,字符集代码和可空性的位),以及列长度(LEN)。

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_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_SYS_INDEXES返回两个索引的数据。第一个索引是GEN_CLUST_INDEX,如果 table 没有用户定义的聚集索引,则它是由InnoDB创建的聚集索引。第二个索引(i1)是用户定义的辅助索引。

INDEX_ID是索引的标识符,该索引在实例中的所有数据库中都是唯一的。 TABLE_ID标识与索引关联的 table。索引TYPE值指示索引的类型(1 =聚集索引,0 =二级索引)。 N_FILEDS值是组成索引的字段数。 PAGE_NO是索引 B 树的根页号,SPACE是索引所在的 table 空间的 ID。非零值 table 示索引未驻留在系统 table 空间中。 MERGE_THRESHOLD定义索引页中数据量的百分比阈值。如果在删除行或通过更新操作缩短行时索引页中的数据量低于此值(默认值为 50%),则InnoDB尝试将索引页与相邻的索引页合并。

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

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

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES WHERE SPACE = 57 \G
*************************** 1. row ***************************
        SPACE: 57
         NAME: test/t1
         FLAG: 0
  FILE_FORMAT: Antelope
   ROW_FORMAT: Compact or Redundant
    PAGE_SIZE: 16384
ZIP_PAGE_SIZE: 0

除了 table 空间的SPACE ID 和关联 table 的NAME之外,INNODB_SYS_TABLESPACES还提供 table 空间FLAG数据,该数据是有关 table 空间格式和存储特性的位级别信息。还提供了 table 空间FILE_FORMATROW_FORMATPAGE_SIZE以及其他几个 table 空间元数据项。

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

数据文件位于 MySQL 的data目录下的test目录中。如果使用CREATE TABLE语句的DATA DIRECTORY子句在 MySQL 数据目录之外的位置创建了file-per-tabletable 空间,则 table 空间PATH将是标准目录路径。

  • 最后一步,在 tablet1(TABLE_ID = 71)中插入一行,并查看INNODB_SYS_TABLESTATStable 中的数据。 MySQL 优化程序使用此 table 中的数据来计算查询InnoDBtable 时要使用的索引。此信息来自内存中的数据结构。没有相应的内部InnoDB系统 table。
mysql> INSERT INTO t1 VALUES(5, 'abc', 'def');
Query OK, 1 row affected (0.06 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_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字段指示是否已为该 table 收集统计信息。 NUM_ROWS是 table 中当前的估计行数。 CLUST_INDEX_SIZEOTHER_INDEX_SIZE字段报告磁盘上分别存储 table 的聚集索引和辅助索引的页面数。 MODIFIED_COUNTER值显示通过 DML 操作和外键的级联操作修改的行数。 AUTOINC值是针对任何基于自动增量的操作要发出的下一个数字。在 tablet1上没有定义自动增量列,因此该值为 0.REF_COUNT的值是一个计数器。当计数器达到 0 时,table 示可以从 table 缓存中逐出 table 元数据。

例 14.3 外键 INFORMATION_SCHEMA 系统 table

INNODB_SYS_FOREIGNINNODB_SYS_FOREIGN_COLStable 提供有关外键关系的数据。本示例使用具有外键关系的父 table 和子 table 来演示在INNODB_SYS_FOREIGNINNODB_SYS_FOREIGN_COLStable 中找到的数据。

  • 使用父 table 和子 table 创建测试数据库:
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;
  • 创建父 table 和子 table 后,查询INNODB_SYS_FOREIGN并找到test/childtest/parent外键关系的外键数据:
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN \G
*************************** 1. row ***************************
      ID: test/fk1
FOR_NAME: test/child
REF_NAME: test/parent
  N_COLS: 1
    TYPE: 1

元数据包含外键ID(fk1),该外键以在子 table 上定义的CONSTRAINT命名。 FOR_NAME是定义了外键的子 table 的名称。 REF_NAME是父 table(“被引用”table)的名称。 N_COLS是外键索引中的列数。 TYPE是代 table 位标志的数值,这些标志提供有关外键列的其他信息。在这种情况下,TYPE的值为 1,table 示为外键指定了ON DELETE CASCADE选项。有关TYPE值的更多信息,请参见INNODB_SYS_FOREIGNtable 定义。

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_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是子 table 中外键列的名称,而REF_COL_NAME是父 table 中被引用列的名称。 POS值是外键索引中键字段的 Sequences 位置,从零开始。

示例 14.4 加入 InnoDB INFORMATION_SCHEMA 系统 table

此示例演示如何联接三个InnoDB INFORMATION_SCHEMA系统 table(INNODB_SYS_TABLESINNODB_SYS_TABLESPACESINNODB_SYS_TABLESTATS)以收集有关雇员 samples 数据库中 table 的文件格式,行格式,页面大小和索引大小信息。

下 table 名称别名用于缩短查询字符串:

IF()控制流功能用于说明压缩 table。如果 table 被压缩,则使用ZIP_PAGE_SIZE而不是PAGE_SIZE计算索引大小。以字节报告的CLUST_INDEX_SIZEOTHER_INDEX_SIZE1024*1024除以提供兆字节(MB)的索引大小。使用ROUND()函数将 MB 值四舍五入到小数点后零位。

mysql> SELECT a.NAME, a.FILE_FORMAT, 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_SYS_TABLES a
       INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES b on a.NAME = b.NAME
       INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS c on b.NAME = c.NAME
       WHERE a.NAME LIKE 'employees/%'
       ORDER BY a.NAME DESC;
+------------------------+-------------+------------+-----------+-------+-----------+
| NAME                   | FILE_FORMAT | ROW_FORMAT | page_size | pk_mb | secidx_mb |
+------------------------+-------------+------------+-----------+-------+-----------+
| employees/titles       | Antelope    | Compact    |     16384 |    20 |        11 |
| employees/salaries     | Antelope    | Compact    |     16384 |    91 |        33 |
| employees/employees    | Antelope    | Compact    |     16384 |    15 |         0 |
| employees/dept_manager | Antelope    | Compact    |     16384 |     0 |         0 |
| employees/dept_emp     | Antelope    | Compact    |     16384 |    12 |        10 |
| employees/departments  | Antelope    | Compact    |     16384 |     0 |         0 |
+------------------------+-------------+------------+-----------+-------+-----------+