InnoDB INFORMATION_SCHEMA系统表
你可以使用InnoDB INFORMATION_SCHEMA系统表来提取由InnoDB管理的schema对象的元数据。这些信息来自InnoDB内部的系统表(也称为InnoDB数据字典)，它不能像普通的InnoDB表那样直接查询。传统上，你可以使用InnoDB监视器技术来获取这类信息，设置InnoDB监视器并解析SHOW ENGINE InnoDB STATUS语句的输出。InnoDB INFORMATION_SCHEMA表接口允许你使用SQL查询这些数据。
除了INNODB_SYS_TABLESTATS没有对应的内部系统表外，InnoDB INFORMATION_SCHEMA系统表中的数据都是直接从InnoDB内部系统表中读取的，而不是从内存中缓存的元数据中读取。
InnoDB INFORMATION_SCHEMA系统表包括下面列出的表。INNODB_SYS_DATAFILES和INNODB_SYS_TABLESPACES是在MySQL 5.6.6中添加的，引入了对CREATE TABLE语句中的DATA DIRECTORY=’ DIRECTORY ‘子句的支持，它允许InnoDB file-per-table表空间(在MySQL数据目录之外的位置创建ibd文件。
mysql> SHOW TABLES FROM INFORMATION_SCHEMA LIKE 'INNODB_SYS%'; +--------------------------------------------+ | Tables_in_information_schema (INNODB_SYS%) | +--------------------------------------------+ | INNODB_SYS_DATAFILES | | INNODB_SYS_VIRTUAL | | INNODB_SYS_INDEXES | | INNODB_SYS_TABLES | | INNODB_SYS_FIELDS | | INNODB_SYS_TABLESPACES | | INNODB_SYS_FOREIGN_COLS | | INNODB_SYS_COLUMNS | | INNODB_SYS_FOREIGN | | INNODB_SYS_TABLESTATS | +--------------------------------------------+ 10 rows in set (0.00 sec)
表名表明所提供的数据类型:
.INNODB_SYS_TABLES提供关于InnoDB表的元数据，等价于InnoDB数据字典中的SYS_TABLES表中的信息。
.INNODB_SYS_COLUMNS提供关于InnoDB表列的元数据，等价于InnoDB数据字典中的SYS_COLUMNS表中的信息。
.INNODB_SYS_INDEXES提供有关InnoDB索引的元数据，等价于InnoDB数据字典中的SYS_INDEXES表中的信息。
.INNODB_SYS_FIELDS提供了InnoDB索引中关键列(字段)的元数据，等价于InnoDB数据字典中的SYS_FIELDS表中的信息。
.INNODB_SYS_TABLESTATS提供了关于InnoDB表的底层状态信息的视图，这些状态信息来源于内存中的数据结构。没有对应的内部InnoDB系统表。
.INNODB_SYS_DATAFILES提供了InnoDB file-per-table件和一般表空间的数据文件路径信息，等价于InnoDB数据字典中的SYS_DATAFILES表中的信息。
.INNODB_SYS_TABLESPACES提供了关于InnoDB file-per-table和一般表空间的元数据，等价于InnoDB数据字典中的SYS_TABLESPACES表中的信息。
.INNODB_SYS_FOREIGN提供了定义在InnoDB表上的外键的元数据，等价于InnoDB数据字典中的SYS_FOREIGN表中的信息。
.INNODB_SYS_FOREIGN_COLS提供了定义在InnoDB表中的外键列的元数据，等价于InnoDB数据字典中的SYS_FOREIGN_COLS表中的信息。
InnoDB INFORMATION_SCHEMA系统表可以通过TABLE_ID、INDEX_ID和SPACE等字段连接在一起，让你可以轻松地检索到你想要研究或监控的对象的所有可用数据。
这个例子使用一个简单的表(t1)和一个索引(i1)来展示在InnoDB INFORMATION_SCHEMA系统表中发现的元数据的类型。
1.创建表t1:
mysql> use test;
Database changed
mysql> create table t1 (
-> col1 int,
-> col2 char(10),
-> col3 varchar(10))
-> engine = innodb;
Query OK, 0 rows affected (0.04 sec)
mysql> create index i1 on t1(col1);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
2.创建表t1后，查询innodb_sys_tables来定位test/t1的元数据:
mysql> select * from information_schema.innodb_sys_tables where name='test/t1' \G
*************************** 1. row ***************************
TABLE_ID: 509
NAME: test/t1
FLAG: 33
N_COLS: 6
SPACE: 508
FILE_FORMAT: Barracuda
ROW_FORMAT: Dynamic
ZIP_PAGE_SIZE: 0
SPACE_TYPE: Single
1 row in set (0.01 sec)
表t1的TABLE_ID是509。FLAG字段提供了有关表格式和存储特性的位级别信息。有6列，其中3列是由InnoDB创建的隐藏列(DB_ROW_ID, DB_TRX_ID和DB_ROLL_PTR)。表空间的ID是508(值为0表示表驻留在system表空间中)。FILE_FORMAT是Barracuda格式，ROW_FORMAT是Dynamic格式。ZIP_PAGE_SIZE只适用于行格式压缩的表。
3.使用innodb_sys_tables中的table_id信息，查询innodb_sys_columns表中关于表的列的信息。
mysql> select * from information_schema.innodb_sys_columns where table_id = 509 \G
*************************** 1. row ***************************
TABLE_ID: 509
NAME: col1
POS: 0
MTYPE: 6
PRTYPE: 1027
LEN: 4
*************************** 2. row ***************************
TABLE_ID: 509
NAME: col2
POS: 1
MTYPE: 13
PRTYPE: 2949374
LEN: 40
*************************** 3. row ***************************
TABLE_ID: 509
NAME: col3
POS: 2
MTYPE: 12
PRTYPE: 2949135
LEN: 40
3 rows in set (0.00 sec)
除了TABLE_ID和NAME列之外，INNODB_SYS_COLUMNS还提供了每列的序号位置(POS)(从0开始，顺序递增)，列的MTYPE或“主类型”(6 = INT,13 = CHAR, 12 = VARCHAR)， PRTYPE或“精确类型”(一个二进制值，用比特表示MySQL的数据类型，字符集代码和可空性)，以及列的长度(LEN)。
4.再次使用innodb_sys_tables中的table_id信息，查询innodb_sys_indexes，获取与表t1相关的索引信息。
mysql> select * from information_schema.innodb_sys_indexes where table_id = 509 \G
*************************** 1. row ***************************
INDEX_ID: 758
NAME: GEN_CLUST_INDEX
TABLE_ID: 509
TYPE: 1
N_FIELDS: 0
PAGE_NO: 3
SPACE: 508
MERGE_THRESHOLD: 50
*************************** 2. row ***************************
INDEX_ID: 759
NAME: i1
TABLE_ID: 509
TYPE: 0
N_FIELDS: 1
PAGE_NO: 4
SPACE: 508
MERGE_THRESHOLD: 50
2 rows in set (0.01 sec)
INNODB_SYS_INDEXES返回两个索引的数据第一个索引是GEN_CLUST_INDEX，如果表没有用户定义的聚集索引，它是由InnoDB创建的聚集索引。第二个索引(i1)是用户定义的辅助索引。
INDEX_ID是索引的标识符，在一个实例中的所有数据库中都是唯一的。TABLE_ID标识了索引关联的表。索引TYPE值指示索引类型(1 =集群索引，0 =辅助索引)。n_fields值是组成索引的字段数目。PAGE_NO是索引B-tree的根页号，SPACE是索引所在的表空间ID。非零值表示索引不在系统表空间中。MERGE_THRESHOLD定义索引页中数据量的百分比阈值。当删除一行或者更新操作缩短一行时，如果索引页中的数据量低于这个值(默认值为50%)，InnoDB会尝试将索引页与相邻的索引页合并。
5.使用innodb_sys_indexes中的index_id信息，查询innodb_sys_fields中索引i1的字段信息。
mysql> select * from information_schema.innodb_sys_fields where index_id = 759 \G
*************************** 1. row ***************************
INDEX_ID: 759
NAME: col1
POS: 0
1 row in set (0.01 sec)
INNODB_SYS_FIELDS提供了索引字段的名称及其在索引中的序号位置。如果索引(i1)定义在多个字段上，INNODB_SYS_FIELDS将为每个被索引的字段提供元数据。
6.使用innodb_sys_tables中的space信息，查询innodb_sys_tables]表获得关于表的表空间信息。
mysql> select * from information_schema.innodb_sys_tablespaces where space = 508 \G
*************************** 1. row ***************************
SPACE: 508
NAME: test/t1
FLAG: 33
FILE_FORMAT: Barracuda
ROW_FORMAT: Dynamic
PAGE_SIZE: 16384
ZIP_PAGE_SIZE: 0
SPACE_TYPE: Single
FS_BLOCK_SIZE: 4096
FILE_SIZE: 114688
ALLOCATED_SIZE: 114688
1 row in set (0.05 sec)
除了表空间的SPACE ID和关联表的NAME之外，INNODB_SYS_TABLESPACES还提供了表空间的FLAG数据，这是有关表空间格式和存储特性的位级别信息。还提供了表空间FILE_FORMAT、ROW_FORMAT、PAGE_SIZE和其他几个表空间元数据项。
7.再次使用INNODB_SYS_TABLES中的SPACE信息，查询INNODB_SYS_DATAFILES表空间数据文件的位置。
mysql> select * from information_schema.innodb_sys_datafiles where space = 508 \G *************************** 1. row *************************** SPACE: 508 PATH: ./test/t1.ibd 1 row in set (0.00 sec)
该数据文件位于MySQL的data目录下的test目录中。如果使用CREATE TABLE语句的data directory子句在MySQL数据目录之外的位置创建一个file-per-table表空间，那么表空间路径将是一个完全限定的目录路径。
8.最后一步，在表t1 (TABLE_ID = 509)中插入一行，然后查看INNODB_SYS_TABLESTATS表中的数据。MySQL优化器使用该表中的数据来计算查询InnoDB表时使用的索引。该信息来源于内存中的数据结构。没有对应的内部InnoDB系统表。
mysql> insert into t1 values(5, 'abc', 'def');
Query OK, 1 row affected (0.00 sec)
mysql> select * from information_schema.innodb_sys_tablestats where table_id = 509 \G
*************************** 1. row ***************************
TABLE_ID: 509
NAME: test/t1
STATS_INITIALIZED: Initialized
NUM_ROWS: 1
CLUST_INDEX_SIZE: 1
OTHER_INDEX_SIZE: 1
MODIFIED_COUNTER: 1
AUTOINC: 0
REF_COUNT: 2
1 row in set (0.00 sec)
STATS_INITIALIZED字段表示是否收集了表的统计信息。NUM_ROWS是表中当前估计的行数。cluster_index_size和OTHER_INDEX_SIZE字段分别报告磁盘上为表存储集群索引和辅助索引的页数。MODIFIED_COUNTER值显示了由DML操作和来自外键的级联操作修改的行数。AUTOINC值是任何基于自动递增操作的下一个数字。在表t1中没有定义autoincrement列，因此值为0。REF_COUNT值是一个计数器。当计数器变为0时，表示表元数据可以从表缓存中移除。
外键信息_schema系统表
INNODB_SYS_FOREIGN和INNODB_SYS_FOREIGN_COLS表提供了有关外键关系的数据。这个例子使用具有外键关系的父表和子表来展示在INNODB_SYS_FOREIGN和INNODB_SYS_FOREIGN_COLS表中找到的数据。
1.创建包含父表和子表的测试数据库:
mysql> create table parent (id int not null,
-> primary key (id)) engine=innodb;
Query OK, 0 rows affected (0.03 sec)
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;
Query OK, 0 rows affected (0.01 sec)
2.在创建父表和子表之后，查询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)，它是根据在子表上定义的约束命名的。FOR_NAME是定义外键的子表的名称。REF_NAME是父表(被引用的表)的名称。N_COLS是外键索引中的列数。TYPE是一个数值，表示提供有关外键列的附加信息的位标志。在本例中，TYPE值为1，表示为外键指定了ON DELETE CASCADE选项。有关类型值的更多信息，请参阅INNODB_SYS_FOREIGN表定义。
3.使用外键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
1 row in set (0.00 sec)
FOR_COL_NAME是子表中外键列的名称，REF_COL_NAME是父表中被引用的列的名称。POS值是键字段在外键索引中的顺序位置，从0开始。
连接InnoDB INFORMATION_SCHEMA系统表
这个例子展示了连接三个InnoDB INFORMATION_SCHEMA系统表(INNODB_SYS_TABLES、INNODB_SYS_TABLESPACES和INNODB_SYS_TABLESTATS)来收集employees sample数据库中表的文件格式、行格式、页面大小和索引大小信息。
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 | Barracuda | Dynamic | 16384 | 0 | 0 |
| employees/t | Barracuda | Dynamic | 16384 | 0 | 0 |
| employees/salaries | Barracuda | Dynamic | 16384 | 0 | 0 |
| employees/employees | Barracuda | Dynamic | 16384 | 0 | 0 |
| employees/dept_manager | Barracuda | Dynamic | 16384 | 0 | 0 |
| employees/dept_emp | Barracuda | Dynamic | 16384 | 0 | 0 |
| employees/departments | Barracuda | Dynamic | 16384 | 0 | 0 |
| employees/c | Barracuda | Dynamic | 16384 | 0 | 0 |
+------------------------+-------------+------------+-----------+-------+-----------+
8 rows in set (0.05 sec)
IF()控制流函数用于处理压缩表。如果表被压缩，则使用ZIP_PAGE_SIZE而不是PAGE_SIZE计算索引大小。cluster_index_size和OTHER_INDEX_SIZE以字节为单位，它们除以1024*1024，就得到了以兆字节(MBs)为单位的索引长度。使用ROUND()函数将MB值四舍五入为0个小数空格。