information_schema库下的innodb相关的表记录了元数据，状态信息，存储引擎相关的统计信息。
1.关于事务以及锁的表
mysql> desc innodb_locks;+-------------+---------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------------+---------------------+------+-----+---------+-------+| lock_id | varchar(81) | NO | | | || lock_trx_id | varchar(18) | NO | | | || lock_mode | varchar(32) | NO | | | || lock_type | varchar(32) | NO | | | || lock_table | varchar(1024) | NO | | | || lock_index | varchar(1024) | YES | | NULL | || lock_space | bigint(21) unsigned | YES | | NULL | || lock_page | bigint(21) unsigned | YES | | NULL | || lock_rec | bigint(21) unsigned | YES | | NULL | || lock_data | varchar(8192) | YES | | NULL | |+-------------+---------------------+------+-----+---------+-------+
mysql> desc innodb_trx;
+----------------------------+---------------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------+---------------------+------+-----+---------------------+-------+
| trx_id | varchar(18) | NO | | | |
| trx_state | varchar(13) | NO | | | |
| trx_started | datetime | NO | | 0000-00-00 00:00:00 | |
| trx_requested_lock_id | varchar(81) | YES | | NULL | |
| trx_wait_started | datetime | YES | | NULL | |
| trx_weight | bigint(21) unsigned | NO | | 0 | |
| trx_mysql_thread_id | bigint(21) unsigned | NO | | 0 | |
| trx_query | varchar(1024) | YES | | NULL | |
| trx_operation_state | varchar(64) | YES | | NULL | |
| trx_tables_in_use | bigint(21) unsigned | NO | | 0 | |
| trx_tables_locked | bigint(21) unsigned | NO | | 0 | |
| trx_lock_structs | bigint(21) unsigned | NO | | 0 | |
| trx_lock_memory_bytes | bigint(21) unsigned | NO | | 0 | |
| trx_rows_locked | bigint(21) unsigned | NO | | 0 | |
| trx_rows_modified | bigint(21) unsigned | NO | | 0 | |
| trx_concurrency_tickets | bigint(21) unsigned | NO | | 0 | |
| trx_isolation_level | varchar(16) | NO | | | |
| trx_unique_checks | int(1) | NO | | 0 | |
| trx_foreign_key_checks | int(1) | NO | | 0 | |
| trx_last_foreign_key_error | varchar(256) | YES | | NULL | |
| trx_adaptive_hash_latched | int(1) | NO | | 0 | |
| trx_adaptive_hash_timeout | bigint(21) unsigned | NO | | 0 | |
| trx_is_read_only | int(1) | NO | | 0 | |
| trx_autocommit_non_locking | int(1) | NO | | 0 | |
+----------------------------+---------------------+------+-----+---------------------+-------+
24 rows in set (0.00 sec)
mysql> desc innodb_lock_waits;
+-------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+-------------+------+-----+---------+-------+
| requesting_trx_id | varchar(18) | NO | | | |
| requested_lock_id | varchar(81) | NO | | | |
| blocking_trx_id | varchar(18) | NO | | | |
| blocking_lock_id | varchar(81) | NO | | | |
+-------------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
要查询这些表，用户需要PROCESS权限
案例
创建一个测试表：
use bbd;
set global autocommit=0;
CREATE TABLE `t2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4
insert into t2 values (0,'a'),(0,'b'),(0,'d');
commit;
mysql> select * from t2;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | d |
+----+------+
3 rows in set (0.00 sec)
假设现在有三个会话：
Session A：
BEGIN;
SELECT id FROM t2 FOR UPDATE;
SELECT SLEEP(100);
Session B：
SELECT id FROM t2 FOR UPDATE;
Session C：
SELECT id FROM t2 FOR UPDATE;
查询sys.innodb_lock_waits
mysql> SELECT
-> waiting_trx_id,
-> waiting_pid,
-> waiting_query,
-> blocking_trx_id,
-> blocking_pid,
-> blocking_query
-> FROM sys.innodb_lock_waits;
+----------------+-------------+------------------------------+-----------------+--------------+------------------------------+
| waiting_trx_id | waiting_pid | waiting_query | blocking_trx_id | blocking_pid | blocking_query |
+----------------+-------------+------------------------------+-----------------+--------------+------------------------------+
| 36110 | 8 | SELECT id FROM t2 FOR UPDATE | 36109 | 7 | SELECT SLEEP(100) |
| 36111 | 6 | SELECT id FROM t2 FOR UPDATE | 36110 | 8 | SELECT id FROM t2 FOR UPDATE |
| 36111 | 6 | SELECT id FROM t2 FOR UPDATE | 36109 | 7 | SELECT SLEEP(100) |
+----------------+-------------+------------------------------+-----------------+--------------+------------------------------+
3 rows in set, 3 warnings (0.00 sec)
由查询结果可知：
36110跟36111都在等待事务36109
36111同时在等待事务36110
2.关于系统相关的表
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)
mysql> select * from innodb_sys_tables;
+----------+---------------------------------+------+--------+-------+-------------+------------+---------------+------------+
| TABLE_ID | NAME | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE |
+----------+---------------------------------+------+--------+-------+-------------+------------+---------------+------------+
| 14 | SYS_DATAFILES | 0 | 5 | 0 | Antelope | Redundant | 0 | System |
| 11 | SYS_FOREIGN | 0 | 7 | 0 | Antelope | Redundant | 0 | System |
| 12 | SYS_FOREIGN_COLS | 0 | 7 | 0 | Antelope | Redundant | 0 | System |
| 13 | SYS_TABLESPACES | 0 | 6 | 0 | Antelope | Redundant | 0 | System |
| 15 | SYS_VIRTUAL | 0 | 6 | 0 | Antelope | Redundant | 0 | System |
| 43 | bbd/T4 | 33 | 4 | 45 | Barracuda | Dynamic | 0 | Single |
| 41 | bbd/t1 | 33 | 4 | 25 | Barracuda | Dynamic | 0 | Single |
| 42 | bbd/t2 | 33 | 5 | 30 | Barracuda | Dynamic | 0 | Single |
| 44 | bbd/t6 | 33 | 5 | 91 | Barracuda | Dynamic | 0 | Single
table_id表明了创建表时的id，flag提供有关表格式和存储特性的位级信息，N_COLS表明了表有几个字段，有三个隐藏字段： (DB_ROW_ID, DB_TRX_ID, DB_ROLL_PTR)。space表明了表的表空间的id,0代表是系统表空间。
mysql> select * from INNODB_SYS_COLUMNS where TABLE_ID = 42 \G;
*************************** 1. row ***************************
TABLE_ID: 42
NAME: id
POS: 0
MTYPE: 6
PRTYPE: 1283
LEN: 4
*************************** 2. row ***************************
TABLE_ID: 42
NAME: name
POS: 1
MTYPE: 12
PRTYPE: 2949135
LEN: 80
2 rows in set (0.00 sec)
POS代表创建时的位置，从0开始依次递增。MTYPE代表数据类型，6 = INT, 2 = CHAR, 1 = VARCHAR，PRTYPE是一个二进制值，其位表示MySQL数据类型、字符集代码和可空性。LEN是字段长度。
mysql> select * from innodb_sys_indexes where table_id=42\G;
*************************** 1. row ***************************
INDEX_ID: 43
NAME: PRIMARY
TABLE_ID: 42
TYPE: 3
N_FIELDS: 1
PAGE_NO: 3
SPACE: 30
MERGE_THRESHOLD: 50
1 row in set (0.00 sec)
TYPE表示索引的类型，1是聚簇索引，0是第二索引。N_FIELDS表示索引在表中的第几列。SPACE表示表空间的ID。
3.关于buffer pool的表
mysql> show tables from information_schema like 'INNODB_BUFFER_%';
+------------------------------------------------+
| Tables_in_information_schema (INNODB_BUFFER_%) |
+------------------------------------------------+
| INNODB_BUFFER_PAGE_LRU |
| INNODB_BUFFER_PAGE |
| INNODB_BUFFER_POOL_STATS |
+------------------------------------------------+
3 rows in set (0.00 sec)
innodb_buffer_page记录了内存中每个页的情况，lru这张表记录的信息与page差不多，只不过把BLOCK_ID字段换成了LRU_POSITION字段。status提供了buffer pool的状态信息，与SHOW ENGINE INNODB STATUS的信息一致。
查询innodb_buffer_page与innodb_buffer_page_lru这两张表将会特别消耗性能。
4.关于临时表的表
mysql> SHOW TABLES FROM INFORMATION_SCHEMA LIKE 'INNODB_TEMP%';
+---------------------------------------------+
| Tables_in_INFORMATION_SCHEMA (INNODB_TEMP%) |
+---------------------------------------------+
| INNODB_TEMP_TABLE_INFO |
+---------------------------------------------+
创建一个临时表
mysql> CREATE TEMPORARY TABLE t1 (c1 INT PRIMARY KEY) ENGINE=INNODB;
mysql> select * from innodb_temp_table_info;
+----------+--------------+--------+-------+----------------------+---------------+
| TABLE_ID | NAME | N_COLS | SPACE | PER_TABLE_TABLESPACE | IS_COMPRESSED |
+----------+--------------+--------+-------+----------------------+---------------+
| 45 | #sql1977_b_0 | 4 | 95 | FALSE | FALSE |
+----------+--------------+--------+-------+----------------------+---------------+
1 row in set (0.01 sec)
NAME表示由系统生成的临时表的名称，通常是以#sql开头，N_COLS表示表中的字段数，会加上三个隐藏列，DB_ROW_ID, DB_TRX_ID, DB_ROLL_PTR。SPACE表示表空间的id。压缩的表空间会生成一个独立的表空间，非压缩的表空间就是ibtmp1。PER_TABLE_TABLESPACE跟IS_COMPRESSED表明了临时表是否压缩，TRUE表示压缩。重启之后临时表不再存在，重新创建的临时表的SPACE也跟之前不同，因为临时表空间重新生成了。
5.关于表空间原始数据的表
mysql> desc files;
+----------------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+---------------------+------+-----+---------+-------+
| FILE_ID | bigint(4) | NO | | 0 | |
| FILE_NAME | varchar(4000) | YES | | NULL | |
| FILE_TYPE | varchar(20) | NO | | | |
| TABLESPACE_NAME | varchar(64) | YES | | NULL | |
| TABLE_CATALOG | varchar(64) | NO | | | |
| TABLE_SCHEMA | varchar(64) | YES | | NULL | |
| TABLE_NAME | varchar(64) | YES | | NULL | |
| LOGFILE_GROUP_NAME | varchar(64) | YES | | NULL | |
| LOGFILE_GROUP_NUMBER | bigint(4) | YES | | NULL | |
| ENGINE | varchar(64) | NO | | | |
| FULLTEXT_KEYS | varchar(64) | YES | | NULL | |
| DELETED_ROWS | bigint(4) | YES | | NULL | |
| UPDATE_COUNT | bigint(4) | YES | | NULL | |
| FREE_EXTENTS | bigint(4) | YES | | NULL | |
| TOTAL_EXTENTS | bigint(4) | YES | | NULL | |
| EXTENT_SIZE | bigint(4) | NO | | 0 | |
| INITIAL_SIZE | bigint(21) unsigned | YES | | NULL | |
| MAXIMUM_SIZE | bigint(21) unsigned | YES | | NULL | |
| AUTOEXTEND_SIZE | bigint(21) unsigned | YES | | NULL | |
| CREATION_TIME | datetime | YES | | NULL | |
| LAST_UPDATE_TIME | datetime | YES | | NULL | |
| LAST_ACCESS_TIME | datetime | YES | | NULL | |
| RECOVER_TIME | bigint(4) | YES | | NULL | |
| TRANSACTION_COUNTER | bigint(4) | YES | | NULL | |
| VERSION | bigint(21) unsigned | YES | | NULL | |
| ROW_FORMAT | varchar(10) | YES | | NULL | |
| TABLE_ROWS | bigint(21) unsigned | YES | | NULL | |
| AVG_ROW_LENGTH | bigint(21) unsigned | YES | | NULL | |
| DATA_LENGTH | bigint(21) unsigned | YES | | NULL | |
| MAX_DATA_LENGTH | bigint(21) unsigned | YES | | NULL | |
| INDEX_LENGTH | bigint(21) unsigned | YES | | NULL | |
| DATA_FREE | bigint(21) unsigned | YES | | NULL | |
| CREATE_TIME | datetime | YES | | NULL | |
| UPDATE_TIME | datetime | YES | | NULL | |
| CHECK_TIME | datetime | YES | | NULL | |
| CHECKSUM | bigint(21) unsigned | YES | | NULL | |
| STATUS | varchar(20) | NO | | | |
| EXTRA | varchar(255) | YES | | NULL | |
+----------------------+---------------------+------+-----+---------+-------+
38 rows in set (0.00 sec)
files表包含了独占表空间，一般表空间，系统表空间，临时表空间，还有undo表空间(如果有)的信息。
mysql> select file_id,file_name,tablespace_name,table_name,total_extents from files order by 1;
+---------+---------------------------------------+--------------------------+------------+---------------+
| file_id | file_name | tablespace_name | table_name | total_extents |
+---------+---------------------------------------+--------------------------+------------+---------------+
| 0 | ./ibdata1 | innodb_system | NULL | 12 |
| 2 | ./mysql/plugin.ibd | innodb_file_per_table_2 | NULL | 0 |
| 3 | ./mysql/servers.ibd | innodb_file_per_table_3 | NULL | 0 |
| 4 | ./mysql/help_topic.ibd | innodb_file_per_table_4 | NULL | 0 |
| 5 | ./mysql/help_category.ibd | innodb_file_per_table_5 | NULL | 0 |
| 6 | ./mysql/help_relation.ibd | innodb_file_per_table_6 | NULL | 0 |
| 7 | ./mysql/help_keyword.ibd | innodb_file_per_table_7 | NULL | 0 |
| 8 | ./mysql/time_zone_name.ibd | innodb_file_per_table_8 | NULL | 0 |
| 9 | ./mysql/time_zone.ibd | innodb_file_per_table_9 | NULL | 0 |
| 10 | ./mysql/time_zone_transition.ibd | innodb_file_per_table_10 | NULL | 0 |
| 11 | ./mysql/time_zone_transition_type.ibd | innodb_file_per_table_11 | NULL | 0 |
| 12 | ./mysql/time_zone_leap_second.ibd | innodb_file_per_table_12 | NULL | 0 |
| 13 | ./mysql/innodb_table_stats.ibd | innodb_file_per_table_13 | NULL | 0 |
| 14 | ./mysql/innodb_index_stats.ibd | innodb_file_per_table_14 | NULL | 0 |
| 15 | ./mysql/slave_relay_log_info.ibd | innodb_file_per_table_15 | NULL | 0 |
| 16 | ./mysql/slave_master_info.ibd | innodb_file_per_table_16 | NULL | 0 |
| 17 | ./mysql/slave_worker_info.ibd | innodb_file_per_table_17 | NULL | 0 |
| 18 | ./mysql/gtid_executed.ibd | innodb_file_per_table_18 | NULL | 0 |
| 19 | ./mysql/server_cost.ibd | innodb_file_per_table_19 | NULL | 0 |
| 20 | ./mysql/engine_cost.ibd | innodb_file_per_table_20 | NULL | 0 |
| 21 | ./sys/sys_config.ibd | innodb_file_per_table_21 | NULL | 0 |
| 25 | ./bbd/t1.ibd | innodb_file_per_table_25 | NULL | 0 |
| 30 | ./bbd/t2.ibd | innodb_file_per_table_30 | NULL | 0 |
| 45 | ./bbd/T4.ibd | innodb_file_per_table_45 | NULL | 0 |
| 91 | ./bbd/t6.ibd | innodb_file_per_table_91 | NULL | 0 |
| 96 | ./ibtmp1 | innodb_temporary | NULL | 12 |
+---------+---------------------------------------+--------------------------+------------+---------------+
26 rows in set (0.01 sec)
fileid就是space id，系统表空间名是innodb_system,临时表空间名是innodb_temporary,独占表空间名是innodb_file_per_table加space id。
若有收获，就点个赞吧
0 人点赞
