InnoDB INFORMATION_SCHEMA架构对象表

您可以提取有关InnoDB使用InnoDBINFORMATION_SCHEMA表管理的架构对象的元数据。此信息来自数据字典。传统上，您将使用“ InnoDB监视器”中的技术获取此类信息，设置InnoDB监视器并解析该SHOW ENGINE INNODB STATUS语句的输出。该InnoDBINFORMATION_SCHEMA表界面，您可以使用SQL查询该数据。

InnoDBINFORMATION_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。

InnoDBINFORMATION_SCHEMA方案对象表可以通过领域，如连接在一起TABLE_ID，INDEX_ID和SPACE，让您轻松检索所有可用的数据，要研究或监视的对象。

有关每个表的列的信息，请参阅InnoDB INFORMATION_SCHEMA文档。

例15.2 InnoDB INFORMATION_SCHEMA模式对象表

本示例使用t1带有单个索引（i1）的简单表（）来演示在InnoDBINFORMATION_SCHEMA模式对象表中找到的元数据的类型。

1. 创建一个测试数据库和表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);
   

2. 创建表后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
   

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

3. 使用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提供顺序位置（POS每一列（从0开始并依次递增），柱的）MTYPE或“主要类型”（6 = INT，2 = CHAR，1 = VARCHAR）时，PRTYPE或“精确type ”（一个二进制值，其位表示MySQL数据类型，字符集代码和可空性）和列长度（LEN）。在HAS_DEFAULT与DEFAULT_VALUE列仅适用于使用即时添加的列ALTER TABLE ... ADD COLUMN用ALGORITHM=INSTANT。

4. 再次使用TABLE_ID来自的信息INNODB_TABLES，查询INNODB_INDEXES有关与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）是用户定义的辅助索引。

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

5. 使用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则将为每个索引字段提供元数据。

6. 使用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
   SERVER_VERSION	: 8.0.4
    SPACE_VERSION	: 1
       ENCRYPTION	: N
   

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

7. 再次使用这些SPACE信息INNODB_TABLES，查询INNODB_DATAFILES表空间数据文件的位置。

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

   数据文件位于test MySQL目录下的data目录中。如果使用该语句的子句在MySQL数据目录之外的位置中创建了每个表文件表空间，则该表空间将是标准目录路径。DATA DIRECTORYCREATE TABLEPATH

8. 最后一步，在表格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值是针对任何基于自动增量的操作要发出的下一个数字。在table上没有定义自动增量列t1，因此该值为0。REF_COUNT值是一个计数器。当计数器达到0时，表示可以从表缓存中逐出表元数据。

例15.3外键INFORMATION_SCHEMA模式对象表

在INNODB_FOREIGN和INNODB_FOREIGN_COLS表格提供了有关外键关系数据。本示例使用具有外键关系的父表和子表来演示在INNODB_FOREIGNand INNODB_FOREIGN_COLS表中找到的数据。

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

   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;
   

2. 创建父表和子表后，查询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值的更多信息，请参见表定义。

3. 使用外键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值是外键索引中键字段的顺序位置，从零开始。

例15.4连接InnoDB INFORMATION_SCHEMA模式对象表

本示例演示如何联接三个InnoDBINFORMATION_SCHEMA模式对象表（INNODB_TABLES，INNODB_TABLESPACES和INNODB_TABLESTATS），以收集有关雇员样本数据库中表的文件格式，行格式，页面大小和索引大小信息。

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

• INFORMATION_SCHEMA.INNODB_TABLES：一种
• INFORMATION_SCHEMA.INNODB_TABLESPACES：b
• INFORMATION_SCHEMA.INNODB_TABLESTATS： C

一个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 	|
+------------------------	+------------	+-----------	+-------	+-----------	+