在《Server 层混杂信息字典表 | 全方位认识 information_schema》中,我们详细介绍了information_schema下的状态变量、系统变量、进程状态、字符集和校对规则等字典表,本期我们将为大家带来系列第五篇《InnoDB 层系统字典表 | 全方位认识 information_schema》,下面请跟随我们一起开始information_schema 系统库的系统学习之旅吧。
该表提供查询InnoDB file-per-table和常规表空间数据文件的路径信息,等同于InnoDB数据字典中SYS_DATAFILES表中的信息
下面是该表中存储的信息内容
admin@localhost : information_schema 06:26:55> select * from INNODB_SYS_DATAFILES limit 5;
+-------+---------------------------+
| SPACE | PATH |
+-------+---------------------------+
| 6 | ./mysql/plugin.ibd |
| 7 | ./mysql/servers.ibd |
| 8 | ./mysql/help_topic.ibd |
| 9 | ./mysql/help_category.ibd |
| 10 | ./mysql/help_relation.ibd |
+-------+---------------------------+
5 rows in set (0.00 sec)
字段含义如下:
该表提供查询有关InnoDB虚拟生成列和与之关联的列的元数据信息,等同于InnoDB数据字典中SYS_VIRTUAL表中的信息
下面是该表中存储的信息内容
# 建表
root@localhost : (none) 01:03:42> use test
Database changed
root@localhost : test 01:13:25> CREATE TABLE `t1` (
-> `a` int(11) DEFAULT NULL,
-> `b` int(11) DEFAULT NULL,
-> `c` int(11) GENERATED ALWAYS AS (a+b) VIRTUAL,
-> `h` varchar(10) DEFAULT NULL
-> );
Query OK, 0 rows affected (0.10 sec)
# 查询INNODB_SYS_VIRTUAL表
root@localhost : test 01:13:28> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_VIRTUAL
-> WHERE TABLE_ID IN (SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE "test/t1");
+----------+-------+----------+
| TABLE_ID | POS | BASE_POS |
+----------+-------+----------+
| 487 | 65538 | 0 |
| 487 | 65538 | 1 |
+----------+-------+----------+
2 rows in set (0.00 sec)
# 如果将常量值分配给虚拟生成列(如以下示例中所示),则此时该列不会在INNODB_SYS_VIRTUAL表中记录(虚拟生成列必须是一个表达式在该表中才会进行记录)
root@localhost : test 01:13:42> CREATE TABLE `t2` (
-> `a` int(11) DEFAULT NULL,
-> `b` int(11) DEFAULT NULL,
-> `c` int(11) GENERATED ALWAYS AS (5) VIRTUAL
-> );
Query OK, 0 rows affected (0.04 sec)
root@localhost : test 01:15:56> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_VIRTUAL WHERE TABLE_ID IN (SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE "test/t2");
Empty set (0.00 sec)
# 虽然在innodb_sys_virtual表中未记录,但是此类列信息记录到了innodb_sys_columns表中
root@localhost : (none) 01:22:53> select c.* from information_schema.innodb_sys_tables as t join information_schema.innodb_sys_columns as c on t.table_id=c.table_id where t.name='test/t2';
+----------+------+-------+-------+--------+-----+
| TABLE_ID | NAME | POS | MTYPE | PRTYPE | LEN |
+----------+------+-------+-------+--------+-----+
| 488 | a | 0 | 6 | 1027 | 4 |
| 488 | b | 1 | 6 | 1027 | 4 |
| 488 | c | 65538 | 6 | 9219 | 4 |
+----------+------+-------+-------+--------+-----+
3 rows in set (0.00 sec)
字段含义如下:
该表提供查询有关InnoDB索引的元数据信息,等同于InnoDB数据字典内部SYS_INDEXES表中的信息
下面是该表中存储的信息内容
admin@localhost : information_schema 06:39:20> select * from INNODB_SYS_INDEXES limit 1\G;
*************************** 1. row ***************************
INDEX_ID: 11
NAME: ID_IND
TABLE_ID: 11
TYPE: 3
N_FIELDS: 1
PAGE_NO: 45
SPACE: 0
MERGE_THRESHOLD: 50
1 row in set (0.00 sec)
字段含义如下:
该表提供查询有关InnoDB表的元数据,等同于InnoDB数据字典中SYS_TABLES表的信息
下面是该表中存储的信息内容
root@localhost : (none) 12:53:44> select * from information_schema.INNODB_SYS_TABLES where NAME like 'sbtest%';
+----------+-------------------+------+--------+-------+-------------+------------+---------------+------------+
| TABLE_ID | NAME | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE |
+----------+-------------------+------+--------+-------+-------------+------------+---------------+------------+
| 323 | sbtest/sbtest1 | 33 | 7 | 151 | Barracuda | Dynamic | 0 | Single |
| 318 | sbtest/sbtest2 | 33 | 7 | 146 | Barracuda | Dynamic | 0 | Single |
| 320 | sbtest/sbtest3 | 33 | 7 | 148 | Barracuda | Dynamic | 0 | Single |
| 321 | sbtest/sbtest4 | 33 | 7 | 149 | Barracuda | Dynamic | 0 | Single |
| 325 | sbtest/sbtest5 | 33 | 7 | 153 | Barracuda | Dynamic | 0 | Single |
| 322 | sbtest/sbtest6 | 33 | 7 | 150 | Barracuda | Dynamic | 0 | Single |
| 324 | sbtest/sbtest7 | 33 | 7 | 152 | Barracuda | Dynamic | 0 | Single |
| 319 | sbtest/sbtest8 | 33 | 7 | 147 | Barracuda | Dynamic | 0 | Single |
| 347 | sbtest/test_table | 33 | 4 | 295 | Barracuda | Dynamic | 0 | Single |
+----------+-------------------+------+--------+-------+-------------+------------+---------------+------------+
9 rows in set (0.00 sec)
字段含义如下:
PS:flag列bit位值解释
该表提供查询有关InnoDB索引列(字段)的元数据信息,等同于InnoDB数据字典中SYS_FIELDS表的信息
下面是该表中存储的信息内容
admin@localhost : information_schema 06:41:33> select * from INNODB_SYS_FIELDS limit 10;
+----------+----------+-----+
| INDEX_ID | NAME | POS |
+----------+----------+-----+
| 11 | ID | 0 |
| 12 | FOR_NAME | 0 |
| 13 | REF_NAME | 0 |
| 14 | ID | 0 |
| 14 | POS | 1 |
| 15 | SPACE | 0 |
| 16 | SPACE | 0 |
| 17 | TABLE_ID | 0 |
| 17 | POS | 1 |
| 17 | BASE_POS | 2 |
+----------+----------+-----+
10 rows in set (0.00 sec)
字段含义如下:
该表提供查询有关InnoDB独立表空间和普通表空间的元数据信息(也包含了全文索引表空间),等同于InnoDB数据字典中SYS_TABLESPACES表中的信息
下面是该表中存储的信息内容
admin@localhost : information_schema 06:48:10> select * from INNODB_SYS_TABLESPACES where name like '%sbtest%' limit 10;
+-------+------------------------------------------------------+------+-------------+------------+-----------+---------------+------------+---------------+------------+----------------+
| SPACE | NAME | FLAG | FILE_FORMAT | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE |
+-------+------------------------------------------------------+------+-------------+------------+-----------+---------------+------------+---------------+------------+----------------+
| 28 | sbtest/sbtest6 | 33 | Barracuda | Dynamic | 16384 | 0 | Single | 4096 | 1547698176 | 1547702272 |
| 29 | sbtest/sbtest5 | 33 | Barracuda | Dynamic | 16384 | 0 | Single | 4096 | 1551892480 | 1551896576 |
| 30 | sbtest/sbtest3 | 33 | Barracuda | Dynamic | 16384 | 0 | Single | 4096 | 1551892480 | 1551896576 |
| 31 | sbtest/sbtest7 | 33 | Barracuda | Dynamic | 16384 | 0 | Single | 4096 | 1547698176 | 1547702272 |
| 32 | sbtest/sbtest1 | 33 | Barracuda | Dynamic | 16384 | 0 | Single | 4096 | 2533359616 | 2533363712 |
| 33 | sbtest/sbtest2 | 33 | Barracuda | Dynamic | 16384 | 0 | Single | 4096 | 1547698176 | 1547702272 |
| 34 | sbtest/sbtest4 | 33 | Barracuda | Dynamic | 16384 | 0 | Single | 4096 | 1551892480 | 1551896576 |
| 35 | sbtest/sbtest8 | 33 | Barracuda | Dynamic | 16384 | 0 | Single | 4096 | 1547698176 | 1547702272 |
| 167 | sbtest/xx | 33 | Barracuda | Dynamic | 16384 | 0 | Single | 4096 | 114688 | 114688 |
| 168 | sbtest/FTS_0000000000000076_000000000000008e_INDEX_1 | 33 | Barracuda | Dynamic | 16384 | 0 | Single | 4096 | 98304 | 98304 |
+-------+------------------------------------------------------+------+-------------+------------+-----------+---------------+------------+---------------+------------+----------------+
10 rows in set (0.00 sec)
字段含义如下:
该表提供查询有关InnoDB外键列的状态信息,等同于InnoDB数据字典中SYS_FOREIGN_COLS表的信息
下面是该表中存储的信息内容
root@localhost : (none) 11:44:52> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS;
+-------------------------------+--------------+--------------+-----+
| ID | FOR_COL_NAME | REF_COL_NAME | POS |
+-------------------------------+--------------+--------------+-----+
| employees/dept_emp_ibfk_1 | emp_no | emp_no | 0 |
| employees/dept_emp_ibfk_2 | dept_no | dept_no | 0 |
| employees/dept_manager_ibfk_1 | emp_no | emp_no | 0 |
| employees/dept_manager_ibfk_2 | dept_no | dept_no | 0 |
| employees/salaries_ibfk_1 | emp_no | emp_no | 0 |
| employees/titles_ibfk_1 | emp_no | emp_no | 0 |
+-------------------------------+--------------+--------------+-----+
6 rows in set (0.00 sec)
字段含义如下:
该表提供查询有关InnoDB表列的元数据信息,等同于InnoDB数据字典中SYS_COLUMNS表的信息
下面是该表中存储的信息内容
admin@localhost : information_schema 06:51:17> select * from INNODB_SYS_COLUMNS limit 10;
+----------+--------------+-----+-------+---------+-----+
| TABLE_ID | NAME | POS | MTYPE | PRTYPE | LEN |
+----------+--------------+-----+-------+---------+-----+
| 11 | ID | 0 | 1 | 5439492 | 0 |
| 11 | FOR_NAME | 1 | 1 | 5439492 | 0 |
| 11 | REF_NAME | 2 | 1 | 5439492 | 0 |
| 11 | N_COLS | 3 | 6 | 0 | 4 |
| 12 | ID | 0 | 1 | 5439492 | 0 |
| 12 | POS | 1 | 6 | 0 | 4 |
| 12 | FOR_COL_NAME | 2 | 1 | 5439492 | 0 |
| 12 | REF_COL_NAME | 3 | 1 | 5439492 | 0 |
| 13 | SPACE | 0 | 6 | 0 | 4 |
| 13 | NAME | 1 | 1 | 5439492 | 0 |
+----------+--------------+-----+-------+---------+-----+
10 rows in set (0.00 sec)
字段含义如下:
该表提供查询有关InnoDB外键的元数据信息,等同于InnoDB数据字典中SYS_FOREIGN表的信息
下面是该表中存储的信息内容
root@localhost : (none) 11:44:09> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN;
+-------------------------------+------------------------+-----------------------+--------+------+
| ID | FOR_NAME | REF_NAME | N_COLS | TYPE |
+-------------------------------+------------------------+-----------------------+--------+------+
| employees/dept_emp_ibfk_1 | employees/dept_emp | employees/employees | 1 | 1 |
| employees/dept_emp_ibfk_2 | employees/dept_emp | employees/departments | 1 | 1 |
| employees/dept_manager_ibfk_1 | employees/dept_manager | employees/employees | 1 | 1 |
| employees/dept_manager_ibfk_2 | employees/dept_manager | employees/departments | 1 | 1 |
| employees/salaries_ibfk_1 | employees/salaries | employees/employees | 1 | 1 |
| employees/titles_ibfk_1 | employees/titles | employees/employees | 1 | 1 |
+-------------------------------+------------------------+-----------------------+--------+------+
6 rows in set (0.00 sec)
字段含义如下:
该表提供查询有关InnoDB表的较低级别的状态信息视图。MySQL优化器会使用这些统计信息数据来计算并确定在查询InnoDB表时要使用哪个索引。这些信息保存在内存中的数据结构中,与存储在磁盘上的数据无对应关系。InnoDB内部也无对应的系统表
下面是该表中存储的信息内容
admin@localhost : information_schema 06:52:25> select * from INNODB_SYS_TABLESTATS where name like '%sbtest%' limit 20;
+----------+------------------------------------------------------+-------------------+----------+------------------+------------------+------------------+----------+-----------+
| TABLE_ID | NAME | STATS_INITIALIZED | NUM_ROWS | CLUST_INDEX_SIZE | OTHER_INDEX_SIZE | MODIFIED_COUNTER | AUTOINC | REF_COUNT |
+----------+------------------------------------------------------+-------------------+----------+------------------+------------------+------------------+----------+-----------+
| 119 | sbtest/FTS_0000000000000076_000000000000008e_INDEX_1 | Uninitialized | 0 | 0 | 0 | 0 | 0 | 0 |
| 120 | sbtest/FTS_0000000000000076_000000000000008e_INDEX_2 | Uninitialized | 0 | 0 | 0 | 0 | 0 | 0 |
| 121 | sbtest/FTS_0000000000000076_000000000000008e_INDEX_3 | Uninitialized | 0 | 0 | 0 | 0 | 0 | 0 |
| 122 | sbtest/FTS_0000000000000076_000000000000008e_INDEX_4 | Uninitialized | 0 | 0 | 0 | 0 | 0 | 0 |
| 123 | sbtest/FTS_0000000000000076_000000000000008e_INDEX_5 | Uninitialized | 0 | 0 | 0 | 0 | 0 | 0 |
| 124 | sbtest/FTS_0000000000000076_000000000000008e_INDEX_6 | Uninitialized | 0 | 0 | 0 | 0 | 0 | 0 |
| 125 | sbtest/FTS_0000000000000076_BEING_DELETED | Uninitialized | 0 | 0 | 0 | 0 | 0 | 0 |
| 126 | sbtest/FTS_0000000000000076_BEING_DELETED_CACHE | Uninitialized | 0 | 0 | 0 | 0 | 0 | 0 |
| 127 | sbtest/FTS_0000000000000076_CONFIG | Uninitialized | 0 | 0 | 0 | 0 | 0 | 0 |
| 128 | sbtest/FTS_0000000000000076_DELETED | Uninitialized | 0 | 0 | 0 | 0 | 0 | 0 |
| 129 | sbtest/FTS_0000000000000076_DELETED_CACHE | Uninitialized | 0 | 0 | 0 | 0 | 0 | 0 |
| 45 | sbtest/sbtest1 | Initialized | 4392380 | 62720 | 5929 | 0 | 10730975 | 2 |
| 46 | sbtest/sbtest2 | Initialized | 4707512 | 67264 | 7145 | 0 | 10727715 | 1 |
| 43 | sbtest/sbtest3 | Initialized | 4451936 | 63616 | 6057 | 0 | 10731473 | 1 |
| 47 | sbtest/sbtest4 | Initialized | 4728110 | 67520 | 7337 | 0 | 10730695 | 1 |
| 42 | sbtest/sbtest5 | Initialized | 4372701 | 62464 | 5737 | 0 | 10729813 | 1 |
| 41 | sbtest/sbtest6 | Initialized | 4550283 | 65024 | 6569 | 0 | 10729407 | 1 |
| 44 | sbtest/sbtest7 | Initialized | 4630701 | 66112 | 6889 | 0 | 10729717 | 1 |
| 48 | sbtest/sbtest8 | Initialized | 4596733 | 65664 | 6633 | 0 | 10727951 | 1 |
| 118 | sbtest/xx | Initialized | 2 | 1 | 1 | 0 | 0 | 1 |
+----------+------------------------------------------------------+-------------------+----------+------------------+------------------+------------------+----------+-----------+
20 rows in set (0.00 sec)
字段含义如下:
本期内容就介绍到这里,本期内容参考链接如下:
| 作者简介
《千金良方——MySQL性能优化金字塔法则》、《数据生态:MySQL复制技术与生产实践》作者之一。熟悉MySQL体系结构,擅长数据库的整体调优,喜好专研开源技术,并热衷于开源技术的推广,在线上线下做过多次公开的数据库专题分享,发表过近100篇数据库相关的研究文章。
全文完。