14.16.3 InnoDB INFORMATION_SCHEMA 系统 table
您可以使用InnoDB
INFORMATION_SCHEMA
系统 table 提取有关InnoDB
Management 的架构对象的元数据。此信息来自InnoDB
内部系统 table(也称为InnoDB
数据字典),不能像常规InnoDB
table 那样直接查询。传统上,您将使用第 14.18 节“ InnoDB 监视器”的技术,设置InnoDB
监视器并解析显示引擎的 INNODB 状态语句的输出来获取此类信息。 InnoDB
INFORMATION_SCHEMA
table 接口允许您使用 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提供有关
InnoDB
table 的元数据,等效于InnoDB
数据字典中SYS_TABLES
table 中的信息。 -
INNODB_SYS_COLUMNS提供有关
InnoDB
table 列的元数据,等效于InnoDB
数据字典中SYS_COLUMNS
table 中的信息。 -
INNODB_SYS_INDEXES提供有关
InnoDB
索引的元数据,等效于InnoDB
数据字典中SYS_INDEXES
table 中的信息。 -
INNODB_SYS_FIELDS提供有关
InnoDB
索引的键列(字段)的元数据,等效于InnoDB
数据字典中SYS_FIELDS
table 中的信息。 -
INNODB_SYS_TABLESTATS提供了有关从内存中的数据结构派生的有关
InnoDB
table 的低级状态信息的视图。没有相应的内部InnoDB
系统 table。 -
INNODB_SYS_DATAFILES提供每 table
InnoDB
文件和常规 table 空间的数据文件路径信息,等效于InnoDB
数据字典中SYS_DATAFILES
table 中的信息。 -
INNODB_SYS_TABLESPACES提供有关每个 table
InnoDB
文件和常规 table 空间的元数据,等效于InnoDB
数据字典中SYS_TABLESPACES
table 中的信息。 -
INNODB_SYS_FOREIGN提供有关在
InnoDB
table 上定义的外键的元数据,等效于InnoDB
数据字典中SYS_FOREIGN
table 中的信息。 -
INNODB_SYS_FOREIGN_COLS提供有关在
InnoDB
table 上定义的外键列的元数据,等效于InnoDB
数据字典中SYS_FOREIGN_COLS
table 中的信息。
InnoDB
INFORMATION_SCHEMA
系统 table 可以通过TABLE_ID
,INDEX_ID
和SPACE
之类的字段连接在一起,从而使您可以轻松检索要研究或监视的对象的所有可用数据。
有关每个 table 的列的信息,请参考InnoDB
INFORMATION_SCHEMA文档。
例 14.2 InnoDB INFORMATION_SCHEMA 系统 table
本示例使用具有单个索引(i1
)的简单 table(t1
)来演示在InnoDB
INFORMATION_SCHEMA
系统 table 中找到的元数据的类型。
- 创建一个测试数据库和 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);
- 创建 table
t1
后,查询INNODB_SYS_TABLES以找到test/t1
的元数据:
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
...
tablet1
的TABLE_ID
为 71.FLAG
字段提供有关 table 格式和存储特性的位级别信息。有六列,其中三列是由InnoDB
(DB_ROW_ID
,DB_TRX_ID
和DB_ROLL_PTR
)创建的隐藏列。该 table 的SPACE
的 ID 为 57(值为 0table 示该 table 位于系统 table 空间中)。 FILE_FORMAT
是羚羊,而ROW_FORMAT
是紧凑型。 ZIP_PAGE_SIZE
仅适用于Compressed
行格式的 table。
- 使用INNODB_SYS_TABLES中的
TABLE_ID
信息,查询INNODB_SYS_COLUMNStable 以获取有关 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
)。
- 再次使用来自INNODB_SYS_TABLES的
TABLE_ID
信息,查询INNODB_SYS_INDEXES以获取有关与 tablet1
关联的索引的信息。
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
尝试将索引页与相邻的索引页合并。
- 使用来自INNODB_SYS_INDEXES的
INDEX_ID
信息,查询INNODB_SYS_FIELDS以获取有关索引i1
的字段的信息。
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将为每个索引字段提供元数据。
- 使用INNODB_SYS_TABLES中的
SPACE
信息,查询INNODB_SYS_TABLESPACEStable 以获取有关 table 的 table 空间的信息。
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_FORMAT
,ROW_FORMAT
,PAGE_SIZE
以及其他几个 table 空间元数据项。
- 再次使用来自INNODB_SYS_TABLES的
SPACE
信息,向INNODB_SYS_DATAFILES查询 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
将是标准目录路径。
- 最后一步,在 table
t1
(TABLE_ID = 71
)中插入一行,并查看INNODB_SYS_TABLESTATStable 中的数据。 MySQL 优化程序使用此 table 中的数据来计算查询InnoDB
table 时要使用的索引。此信息来自内存中的数据结构。没有相应的内部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_SIZE
和OTHER_INDEX_SIZE
字段报告磁盘上分别存储 table 的聚集索引和辅助索引的页面数。 MODIFIED_COUNTER
值显示通过 DML 操作和外键的级联操作修改的行数。 AUTOINC
值是针对任何基于自动增量的操作要发出的下一个数字。在 tablet1
上没有定义自动增量列,因此该值为 0.REF_COUNT
的值是一个计数器。当计数器达到 0 时,table 示可以从 table 缓存中逐出 table 元数据。
例 14.3 外键 INFORMATION_SCHEMA 系统 table
INNODB_SYS_FOREIGN和INNODB_SYS_FOREIGN_COLStable 提供有关外键关系的数据。本示例使用具有外键关系的父 table 和子 table 来演示在INNODB_SYS_FOREIGN和INNODB_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/child
和test/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 定义。
- 使用外键
ID
,查询INNODB_SYS_FOREIGN_COLS以查看有关外键列的数据。
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_TABLES,INNODB_SYS_TABLESPACES和INNODB_SYS_TABLESTATS)以收集有关雇员 samples 数据库中 table 的文件格式,行格式,页面大小和索引大小信息。
下 table 名称别名用于缩短查询字符串:
IF()控制流功能用于说明压缩 table。如果 table 被压缩,则使用ZIP_PAGE_SIZE
而不是PAGE_SIZE
计算索引大小。以字节报告的CLUST_INDEX_SIZE
和OTHER_INDEX_SIZE
被1024*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 |
+------------------------+-------------+------------+-----------+-------+-----------+