月份：2024年1月

InnoDB监视器
InnoDB监视器提供有关InnoDB内部状态的信息。此信息对性能调优很有用。

InnoDB监视器类型
InnoDB监视器有两种类型:
.标准的InnoDB Monitor显示以下类型的信息:
.主后台线程完成的工作
.线程的信号量等待
.关于最近的外键和死锁错误的数据
.锁等待事务
.由活动事务持有的表和记录锁
.待处理I/O操作及相关统计
.插入缓冲区和自适应哈希索引统计信息
.重做日志数据
.缓冲池统计信息
.行运算数据

.InnoDB锁监视器打印额外的锁信息作为标准InnoDB监视器输出的一部分。

启用InnoDB监视器
当InnoDB监控器为周期性输出启用时，InnoDB将输出写入mysqld服务器标准错误输出(stderr)。InnoDB将诊断输出发送到stderr，而不是stdout或固定大小的内存缓冲区，以避免潜在的缓冲区溢出。

在Windows上，stderr被定向到默认日志文件，除非另有配置。如果您希望将输出定向到控制台窗口而不是错误日志，请使用–console选项从控制台窗口的命令提示符启动服务器。

在Unix和类Unix系统上，stderr通常被定向到终端，除非另有配置。

当启用时，InnoDB监控大约每15秒打印一次数据。这些数据在性能调优中很有用。作为副作用，SHOW ENGINE INNODB STATUS的输出每15秒写入MySQL数据目录中的状态文件。文件名为innodb_status.pid，其中pid是服务器进程ID。InnoDB在服务器正常关闭时删除文件。如果发生了异常关机，则可能存在这些状态文件的实例，必须手动删除。在删除文件之前，检查它们是否包含有关异常关机原因的有用信息。一个innodb_status.pid只有启用了innodb-status-file配置选项，才会创建。默认关闭。

InnoDB监控器应该只在你真正想要查看监控器信息时才启用，因为输出的生成会导致一些性能下降。此外，如果监视器输出指向错误日志，如果您稍后忘记禁用监视器，则日志可能会变得相当大。

注意:
为了辅助故障诊断，在某些条件下，InnoDB临时启用了标准的InnoDB监视器输出。

InnoDB监视器输出以一个包含时间戳和监视器名称的报头开始。例如:

=====================================
2024-01-15 16:50:33 0x7f3e8c201700 INNODB MONITOR OUTPUT
=====================================

标准InnoDB监控器的首部(InnoDB监控器输出)也用于锁监控器，因为后者产生了相同的输出，但添加了额外的锁信息。

innodb_status_output和innodb_status_output_locks系统变量用于启用标准的InnoDB监视器和InnoDB锁监视器。

启用或禁用InnoDB监视器需要PROCESS特权。

启用标准InnoDB监视器
通过将innodb_status_output系统变量设置为ON来启用标准InnoDB监视器。

mysql> SET GLOBAL innodb_status_output=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%innodb_status_output%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_status_output       | ON    |
| innodb_status_output_locks | OFF   |
+----------------------------+-------+
2 rows in set (0.01 sec)

关闭标准的InnoDB Monitor，将innodb_status_output设置为OFF。

当您关闭服务器时，innodb_status_output变量被设置为默认的OFF值。

按需获取标准的InnoDB监控器输出
作为启用标准InnoDB监控器周期性输出的替代方案，您可以使用SHOW ENGINE INNODB STATUS SQL语句按需获取标准InnoDB监控器输出，该语句将输出获取到您的客户端程序。如果你使用的是mysql交互式客户端，将通常的分号语句结束符替换为\G，输出的可读性会更好:

mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2024-01-15 16:50:33 0x7f3e8c201700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 8 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 27 srv_active, 0 srv_shutdown, 427925 srv_idle
srv_master_thread log flush and writes: 427940
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 15736
OS WAIT ARRAY INFO: signal count 1770
RW-shared spins 0, rounds 18523, OS waits 12422
RW-excl spins 0, rounds 32549, OS waits 1086
RW-sx spins 1768, rounds 50702, OS waits 1607
Spin rounds per wait: 18523.00 RW-shared, 32549.00 RW-excl, 28.68 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 5697073
Purge done for trx's n:o < 5697071 undo n:o < 0 state: running but idle
History list length 70
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421390708152144, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
141 OS file reads, 4062 OS file writes, 3289 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 1714, seg size 1716, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 2365241, node heap has 0 buffer(s)
Hash table size 2365241, node heap has 0 buffer(s)
Hash table size 2365241, node heap has 0 buffer(s)
Hash table size 2365241, node heap has 0 buffer(s)
Hash table size 2365241, node heap has 0 buffer(s)
Hash table size 2365241, node heap has 0 buffer(s)
Hash table size 2365241, node heap has 0 buffer(s)
Hash table size 2365241, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 89430260246
Log flushed up to   89430260246
Pages flushed up to 89430260246
Last checkpoint at  89430260237
0 pending log flushes, 0 pending chkp writes
2662 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 8795455488
Dictionary memory allocated 234968
Buffer pool size   524224
Free buffers       523545
Database pages     685
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 96, created 589, written 893
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 685, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size   65528
Free buffers       65456
Database pages     78
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 31, created 47, written 131
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 78, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size   65528
Free buffers       65391
Database pages     137
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 11, created 126, written 367
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 137, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size   65528
Free buffers       65413
Database pages     115
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 8, created 107, written 166
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 115, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size   65528
Free buffers       65406
Database pages     122
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 13, created 109, written 182
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 122, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size   65528
Free buffers       65463
Database pages     65
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 8, created 57, written 16
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 65, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size   65528
Free buffers       65469
Database pages     59
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 9, created 50, written 11
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 59, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size   65528
Free buffers       65470
Database pages     58
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 9, created 49, written 10
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 58, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size   65528
Free buffers       65477
Database pages     51
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 7, created 44, written 10
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 51, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=10759, Main thread ID=139906706892544, state: sleeping
Number of rows inserted 5990, updated 0, deleted 0, read 1794460
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.00 sec)

如果启用了InnoDB锁监视器, SHOW ENGINE INNODB STATUS输出还包括InnoDB锁监视器数据。

启用InnoDB锁监视器
InnoDB锁监控器数据与InnoDB标准监控器输出一起打印。InnoDB标准监控器和InnoDB锁监控器都必须启用，才能定期打印InnoDB锁监控器数据。

开启InnoDB锁监控，设置innodb_status_output_locks系统变量为ON。InnoDB标准监控器和InnoDB锁监控器都必须启用，以便定期打印InnoDB锁监控器数据:

mysql> SET GLOBAL innodb_status_output=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL innodb_status_output_locks=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%innodb_status_output%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_status_output       | ON    |
| innodb_status_output_locks | ON    |
+----------------------------+-------+
2 rows in set (0.00 sec)

要禁用InnoDB锁监控，将innodb_status_output_locks设置为OFF。将innodb_status_output设置为OFF也禁用InnoDB标准监视器。

当你关闭服务器时，innodb_status_output和innodb_status_output_locks变量被设置为默认的OFF值。

注意:
为SHOW ENGINE INNODB STATUS输出启用InnoDB锁监视器，只需要启用innodb_status_output_locks。

InnoDB标准监视器和锁监视器输出
锁监视器与标准监视器相同，只是它包含了额外的锁信息。

为周期性输出启用任一监视器都会打开相同的输出流，但如果启用了Lock monitor，则该流将包含额外的信息。例如，如果您启用了标准监控和锁定监视器，它打开单个输出流。在禁用锁监视器之前，流包含额外的锁信息。

当使用SHOW ENGINE INNODB STATUS语句生成时，标准Monitor输出限制为1MB。此限制不适用于写入tserver标准错误输出(stderr)的输出。

标准监视器输出示例:

mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2024-01-15 17:08:03 0x7f3e8c201700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 18 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 27 srv_active, 0 srv_shutdown, 428975 srv_idle
srv_master_thread log flush and writes: 428990
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 15736
OS WAIT ARRAY INFO: signal count 1770
RW-shared spins 0, rounds 18523, OS waits 12422
RW-excl spins 0, rounds 32549, OS waits 1086
RW-sx spins 1768, rounds 50702, OS waits 1607
Spin rounds per wait: 18523.00 RW-shared, 32549.00 RW-excl, 28.68 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 5697073
Purge done for trx's n:o < 5697071 undo n:o < 0 state: running but idle
History list length 70
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421390708152144, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
141 OS file reads, 4062 OS file writes, 3289 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 1714, seg size 1716, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 2365241, node heap has 0 buffer(s)
Hash table size 2365241, node heap has 0 buffer(s)
Hash table size 2365241, node heap has 0 buffer(s)
Hash table size 2365241, node heap has 0 buffer(s)
Hash table size 2365241, node heap has 0 buffer(s)
Hash table size 2365241, node heap has 0 buffer(s)
Hash table size 2365241, node heap has 0 buffer(s)
Hash table size 2365241, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 89430260246
Log flushed up to   89430260246
Pages flushed up to 89430260246
Last checkpoint at  89430260237
0 pending log flushes, 0 pending chkp writes
2662 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 8795455488
Dictionary memory allocated 234968
Buffer pool size   524224
Free buffers       523545
Database pages     685
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 96, created 589, written 893
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 685, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size   65528
Free buffers       65456
Database pages     78
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 31, created 47, written 131
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 78, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size   65528
Free buffers       65391
Database pages     137
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 11, created 126, written 367
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 137, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size   65528
Free buffers       65413
Database pages     115
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 8, created 107, written 166
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 115, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size   65528
Free buffers       65406
Database pages     122
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 13, created 109, written 182
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 122, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size   65528
Free buffers       65463
Database pages     65
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 8, created 57, written 16
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 65, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size   65528
Free buffers       65469
Database pages     59
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 9, created 50, written 11
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 59, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size   65528
Free buffers       65470
Database pages     58
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 9, created 49, written 10
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 58, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size   65528
Free buffers       65477
Database pages     51
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 7, created 44, written 10
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 51, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=10759, Main thread ID=139906706892544, state: sleeping
Number of rows inserted 5990, updated 0, deleted 0, read 1794460
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.00 sec)

标准监视器输出部分
.Status(状态)
本节显示时间戳、监视器名称和每秒平均值所基于的秒数。秒数是当前时间和最后一次打印InnoDB Monitor输出之间经过的时间。

.BACKGROUND THREAD(后台线程)
srv_master_thread行显示了主后台线程完成的工作。

.SEMAPHORES(信号量)
这部分是报告等待信号量的线程，以及线程需要旋转或等待互斥量或rw-lock信号量的统计数据。大量线程等待信号量可能是磁盘I/O的结果，或者是InnoDB内部的争用问题。竞争可能是由于大量的并行查询或操作系统线程调度问题造成的。在这种情况下，将innodb_thread_concurrency系统变量设置为小于默认值可能会有所帮助。Spin rounds per wait line显示了每个操作系统等待互斥量的旋转轮数。

互斥参数由SHOW ENGINE INNODB MUTEX报告。

mysql> SHOW ENGINE INNODB MUTEX\G
*************************** 1. row ***************************
  Type: InnoDB
  Name: rwlock: dict0dict.cc:2737
Status: waits=35
*************************** 2. row ***************************
  Type: InnoDB
  Name: rwlock: dict0dict.cc:1183
Status: waits=77
*************************** 3. row ***************************
  Type: InnoDB
  Name: rwlock: log0log.cc:838
Status: waits=32
*************************** 4. row ***************************
  Type: InnoDB
  Name: sum rwlock: buf0buf.cc:1460
Status: waits=13399
4 rows in set (0.04 sec)

.LATEST FOREIGN KEY ERROR(最新的外键错误)
本节提供有关最近的外键约束错误的信息。如果没有发生此类错误，则不存在该错误。内容包括失败的语句以及有关失败的约束以及被引用表和引用表的信息。

.LATEST DETECTED DEADLOCK(最近检测到的死锁)
本节提供有关最近死锁的信息。如果没有发生死锁，则不存在。内容显示了涉及到哪些事务，每个试图执行的语句，它们拥有和需要的锁，以及InnoDB决定回滚哪个事务以打破死锁。

.TRANSACTIONS(事务)
如果本节报告锁等待，则您的应用程序可能存在锁争用。输出还可以帮助跟踪事务死锁的原因。

.FILE I/O(文件IO)
本节提供了InnoDB用于执行各种类型I/O的线程的信息。前几个是专用于一般InnoDB处理的。内容还显示挂起的I/O操作信息和I/O性能统计信息。

这些线程的数量由innodb_read_io_threads和innodb_write_io_threads参数控制。

.INSERT BUFFER AND ADAPTIVE HASH INDEX
本节展示了InnoDB插入缓冲区(也称为更改缓冲区)和自适应哈希索引的状态。

.LOG
介绍InnoDB日志的相关信息。内容包括当前日志序列号，日志被刷新到磁盘的距离，以及InnoDB最后一次执行检查点的位置。该部分还显示有关挂起写和写性能统计信息的信息。

.BUFFER POOL AND MEMORY
本节提供了读取和写入页面的统计信息。您可以从这些数字中计算出查询当前正在执行多少数据文件I/O操作

.ROW OPERATIONS
本节显示主线程正在执行的操作，包括每种类型的行操作的数量和性能比率。

InnoDB集成MySQL性能模式

你可以使用MySQL Performance Schema特性来分析某些内部InnoDB操作。这种类型的调优主要针对那些评估优化策略以克服性能瓶颈的专家用户。dba还可以使用此功能进行容量规划，以查看他们的典型工作负载在特定的CPU、RAM和磁盘存储组合上是否遇到任何性能瓶颈;如果是，则判断是否可以通过增加系统某些部分的容量来提高性能。

使用这个特性来检查InnoDB的性能:
.您必须熟悉如何使用Performance Schema特性。例如，您应该知道如何启用仪器和消费者，以及如何查询performance_schema表以检索数据。

.您应该熟悉用于InnoDB的性能模式工具。要查看与innodb相关的仪器，可以查询setup_instruments表中包含’innodb’的仪器名称。

mysql> use performance_schema
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT * FROM setup_instruments WHERE NAME LIKE '%innodb%';
+-------------------------------------------------------+---------+-------+
| NAME                                                  | ENABLED | TIMED |
+-------------------------------------------------------+---------+-------+
| wait/synch/mutex/innodb/commit_cond_mutex             | NO      | NO    |
| wait/synch/mutex/innodb/innobase_share_mutex          | NO      | NO    |
| wait/synch/mutex/innodb/autoinc_mutex                 | NO      | NO    |
..........
| memory/innodb/adaptive hash index                     | NO      | NO    |
| memory/innodb/buf_buf_pool                            | NO      | NO    |
| memory/innodb/dict_stats_bg_recalc_pool_t             | NO      | NO    |
| memory/innodb/dict_stats_index_map_t                  | NO      | NO    |
| memory/innodb/dict_stats_n_diff_on_level              | NO      | NO    |
| memory/innodb/other                                   | NO      | NO    |
| memory/innodb/row_log_buf                             | NO      | NO    |
| memory/innodb/row_merge_sort                          | NO      | NO    |
| memory/innodb/std                                     | NO      | NO    |
| memory/innodb/trx_sys_t::rw_trx_ids                   | NO      | NO    |
| memory/innodb/partitioning                            | NO      | NO    |
| memory/innodb/api0api                                 | NO      | NO    |
| memory/innodb/btr0btr                                 | NO      | NO    |
| memory/innodb/btr0bulk                                | NO      | NO    |
| memory/innodb/btr0cur                                 | NO      | NO    |
| memory/innodb/btr0pcur                                | NO      | NO    |
| memory/innodb/btr0sea                                 | NO      | NO    |
| memory/innodb/buf0buf                                 | NO      | NO    |
| memory/innodb/buf0dblwr                               | NO      | NO    |
| memory/innodb/buf0dump                                | NO      | NO    |
| memory/innodb/buf0flu                                 | NO      | NO    |
| memory/innodb/buf0lru                                 | NO      | NO    |
| memory/innodb/dict0dict                               | NO      | NO    |
| memory/innodb/dict0mem                                | NO      | NO    |
| memory/innodb/dict0stats                              | NO      | NO    |
| memory/innodb/dict0stats_bg                           | NO      | NO    |
| memory/innodb/eval0eval                               | NO      | NO    |
| memory/innodb/fil0fil                                 | NO      | NO    |
| memory/innodb/fsp0file                                | NO      | NO    |
| memory/innodb/fsp0space                               | NO      | NO    |
| memory/innodb/fsp0sysspace                            | NO      | NO    |
| memory/innodb/fts0ast                                 | NO      | NO    |
| memory/innodb/fts0config                              | NO      | NO    |
| memory/innodb/fts0fts                                 | NO      | NO    |
| memory/innodb/fts0opt                                 | NO      | NO    |
| memory/innodb/fts0pars                                | NO      | NO    |
| memory/innodb/fts0que                                 | NO      | NO    |
| memory/innodb/fts0sql                                 | NO      | NO    |
| memory/innodb/gis0sea                                 | NO      | NO    |
| memory/innodb/ha0ha                                   | NO      | NO    |
| memory/innodb/ha_innodb                               | NO      | NO    |
| memory/innodb/handler0alter                           | NO      | NO    |
| memory/innodb/hash0hash                               | NO      | NO    |
| memory/innodb/i_s                                     | NO      | NO    |
| memory/innodb/ibuf0ibuf                               | NO      | NO    |
| memory/innodb/lexyy                                   | NO      | NO    |
| memory/innodb/lock0lock                               | NO      | NO    |
| memory/innodb/log0log                                 | NO      | NO    |
| memory/innodb/log0recv                                | NO      | NO    |
| memory/innodb/mem0mem                                 | NO      | NO    |
| memory/innodb/os0event                                | NO      | NO    |
| memory/innodb/os0file                                 | NO      | NO    |
| memory/innodb/page0cur                                | NO      | NO    |
| memory/innodb/page0zip                                | NO      | NO    |
| memory/innodb/pars0lex                                | NO      | NO    |
| memory/innodb/read0read                               | NO      | NO    |
| memory/innodb/rem0rec                                 | NO      | NO    |
| memory/innodb/row0ftsort                              | NO      | NO    |
| memory/innodb/row0import                              | NO      | NO    |
| memory/innodb/row0log                                 | NO      | NO    |
| memory/innodb/row0merge                               | NO      | NO    |
| memory/innodb/row0mysql                               | NO      | NO    |
| memory/innodb/row0sel                                 | NO      | NO    |
| memory/innodb/row0trunc                               | NO      | NO    |
| memory/innodb/srv0conc                                | NO      | NO    |
| memory/innodb/srv0srv                                 | NO      | NO    |
| memory/innodb/srv0start                               | NO      | NO    |
| memory/innodb/sync0arr                                | NO      | NO    |
| memory/innodb/sync0debug                              | NO      | NO    |
| memory/innodb/sync0rw                                 | NO      | NO    |
| memory/innodb/sync0types                              | NO      | NO    |
| memory/innodb/trx0i_s                                 | NO      | NO    |
| memory/innodb/trx0purge                               | NO      | NO    |
| memory/innodb/trx0roll                                | NO      | NO    |
| memory/innodb/trx0rseg                                | NO      | NO    |
| memory/innodb/trx0sys                                 | NO      | NO    |
| memory/innodb/trx0trx                                 | NO      | NO    |
| memory/innodb/trx0undo                                | NO      | NO    |
| memory/innodb/usr0sess                                | NO      | NO    |
| memory/innodb/ut0list                                 | NO      | NO    |
| memory/innodb/ut0mem                                  | NO      | NO    |
| memory/innodb/ut0mutex                                | NO      | NO    |
| memory/innodb/ut0pool                                 | NO      | NO    |
| memory/innodb/ut0rbt                                  | NO      | NO    |
| memory/innodb/ut0wqueue                               | NO      | NO    |
+-------------------------------------------------------+---------+-------+
167 rows in set (0.00 sec)

关于已仪表化的InnoDB对象的附加信息，您可以查询Performance Schema实例表，它提供了关于已仪表化对象的附加信息。与InnoDB相关的实例表
包括:
.mutex_instances表
.rwlock_instances表
.cond_instances表
.file_instances表

注意:
与InnoDB缓冲池相关的互斥量和读写锁不在本章的讨论范围内;SHOW ENGINE INNODB MUTEX命令的输出结果也是如此。

例如，在执行文件I/O仪表化时，要查看Performance Schema所看到的已仪表化的InnoDB文件对象的信息，您可以发出以下查询:

mysql> SELECT * FROM file_instances WHERE EVENT_NAME LIKE '%innodb%'\G
*************************** 1. row ***************************
 FILE_NAME: /mysqldata/mysql/ibdata1
EVENT_NAME: wait/io/file/innodb/innodb_data_file
OPEN_COUNT: 3
*************************** 2. row ***************************
 FILE_NAME: /mysqldata/mysql/ib_logfile0
EVENT_NAME: wait/io/file/innodb/innodb_log_file
OPEN_COUNT: 2
*************************** 3. row ***************************
 FILE_NAME: /mysqldata/mysql/ib_logfile1
EVENT_NAME: wait/io/file/innodb/innodb_log_file
OPEN_COUNT: 2
......

你应该要熟悉存储InnoDB事件数据的performance_schema表。与innodb相关事件相关的表包括:
.等待事件表，存储等待事件。

.汇总表，提供随时间推移终止的事件的聚合信息。汇总表包括文件I/O汇总表，它聚合了关于I/O操作的信息。

.阶段事件表，存储InnoDB ALTER TABLE和缓冲池load操作事件数据。

使用性能模式监控InnoDB表的ALTER TABLE进度

你可以使用Performance Schema监控InnoDB表的ALTER TABLE进度。

有7个阶段事件表示ALTER TABLE的不同阶段。每个阶段事件报告整个ALTER TABLE操作在其不同阶段进行时的WORK_COMPLETED和WORK_ESTIMATED运行总数。WORK_ESTIMATEDe使用一个公式计算，该公式考虑了ALTER TABLE执行的所有工作，并且可能在ALTER TABLE处理期间进行修改。WORK_COMPLETED和WORK_ESTIMATED值是ALTER TABLE执行的所有工作的抽象表示。

按照发生的顺序，ALTER TABLE阶段的事件包括:
.stage/innodb/alter table (read PK and internal sort):当ALTER TABLE处于read -primary-key阶段时，这个阶段是活动的。它一开始将设置WORK_COMPLETED=0和WORK_ESTIMATED设置为主键中估计的页面数。当这个阶段完成时，WORK_ESTIMATED被更新为主键中的实际页面数。

.stage/innodb/alter table (merge sort):对于通过ALTER TABLE操作添加的每个索引，重复此阶段。

.stage/innodb/alter table (insert):对于通过ALTER TABLE操作添加的每个索引，重复此阶段。

.stage/innodb/alter table (log apply index):这个阶段包括应用运行ALTER TABLE时生成的DML日志。

.stage/innodb/alter table (flush):在此阶段开始之前，根据刷新列表的长度，使用更准确的估计值更新WORK_ESTIMATED。

.stage/innodb/alter table (log apply table):此阶段包括应用在ALTER TABLE运行时生成的并发DML日志。这个阶段的持续时间取决于表变化的程度。如果在表上没有运行并发的DML，则此阶段会瞬间完成。

.stage/innodb/alter table (end):包括在刷新阶段之后出现的任何剩余工作，例如当运行ALTER TABLE时重新应用在表上执行的DML。

注意:
InnoDB ALTER TABLE阶段事件目前不会对添加的空间索引进行计数。

使用Performance Schema(性能模式)监控ALTER TABLE操作

下面的例子演示了如何启用stage/innodb/alter table% stage事件仪器和相关的消费者表来监控alter table的进度。
1.启用stage/innodb/alter%仪器:

mysql> UPDATE setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'stage/innodb/alter%';
Query OK, 7 rows affected (0.00 sec)
Rows matched: 7  Changed: 7  Warnings: 0

2.启用阶段事件消费者表，其中包括events_stages_current、events_stages_history和events_stages_history_long。

mysql> UPDATE setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%stages%';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

3.执行ALTER TABLE操作。在本例中，将middle_name列添加到employees示例数据库的employees表中。

mysql> ALTER TABLE employees.employees ADD COLUMN middle_name varchar(14) AFTER first_name;
Query OK, 0 rows affected (1.80 sec)
Records: 0  Duplicates: 0  Warnings: 0

4.通过查询Performance Schema的events_stages_current表来检查ALTER TABLE操作的进度。所显示的阶段事件取决于当前正在进行的ALTER TABLE阶段。WORK_COMPLETED列显示完成的工作，WORK_ESTIMATED列提供了对剩余工作的估计。

mysql> use performance_schema
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM events_stages_current;
Empty set (0.00 sec)

mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM events_stages_current;
+----------------------------------+----------------+----------------+
| EVENT_NAME                       | WORK_COMPLETED | WORK_ESTIMATED |
+----------------------------------+----------------+----------------+
| stage/innodb/alter table (flush) |            938 |           1793 |
+----------------------------------+----------------+----------------+
1 row in set (0.00 sec)

如果ALTER TABLE操作已经完成，events_stages_current表返回一个空集合。在这种情况下，您可以检查events_stages_history表来查看已完成操作的事件数据。例如:

mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM events_stages_history;
+------------------------------------------------------+----------------+----------------+
| EVENT_NAME                                           | WORK_COMPLETED | WORK_ESTIMATED |
+------------------------------------------------------+----------------+----------------+
| stage/innodb/alter table (read PK and internal sort) |            886 |           1793 |
| stage/innodb/alter table (flush)                     |           1793 |           1793 |
| stage/innodb/alter table (log apply table)           |           2177 |           2177 |
| stage/innodb/alter table (end)                       |           2177 |           2177 |
| stage/innodb/alter table (log apply table)           |           2561 |           2561 |
+------------------------------------------------------+----------------+----------------+
5 rows in set (0.00 sec)

如上所示，在ALTER TABLE处理期间修改了WORK_ESTIMATED的值。初始阶段完成后的预计工作量为1793。当ALTER TABLE处理完成时，WORK_ESTIMATED被设置为实际值，即2561。

使用Performance Schema(性能模式)监控InnoDB互斥锁等待
互斥锁是代码中使用的一种同步机制，用于强制在给定时间内只有一个线程可以访问公共资源。当服务器上执行的两个或多个线程需要访问相同的资源时，线程之间会相互竞争。第一个获得互斥锁的线程会导致其他线程等待，直到锁被释放。

对于仪表化的InnoDB互斥锁，可以使用Performance Schema来监控互斥锁的等待。
例如，在Performance Schema表中收集的等待事件数据可以帮助识别等待时间最长或总等待时间最长的互斥锁。

下面的例子演示了如何启用InnoDB互斥锁等待工具，如何启用关联的消费者，以及如何查询等待事件数据。
1.要查看可用的InnoDB互斥锁等待工具，请查询Performance Schema setup_instruments表，如下所示。所有InnoDB互斥锁等待工具默认是禁用的。

mysql> SELECT * FROM performance_schema.setup_instruments WHERE NAME LIKE '%wait/synch/mutex/innodb%';
+-------------------------------------------------------+---------+-------+
| NAME                                                  | ENABLED | TIMED |
+-------------------------------------------------------+---------+-------+
| wait/synch/mutex/innodb/commit_cond_mutex             | NO      | NO    |
| wait/synch/mutex/innodb/innobase_share_mutex          | NO      | NO    |
| wait/synch/mutex/innodb/autoinc_mutex                 | NO      | NO    |
| wait/synch/mutex/innodb/buf_pool_mutex                | NO      | NO    |
| wait/synch/mutex/innodb/buf_pool_zip_mutex            | NO      | NO    |
| wait/synch/mutex/innodb/cache_last_read_mutex         | NO      | NO    |
| wait/synch/mutex/innodb/dict_foreign_err_mutex        | NO      | NO    |
| wait/synch/mutex/innodb/dict_sys_mutex                | NO      | NO    |
| wait/synch/mutex/innodb/recalc_pool_mutex             | NO      | NO    |
| wait/synch/mutex/innodb/file_format_max_mutex         | NO      | NO    |
| wait/synch/mutex/innodb/fil_system_mutex              | NO      | NO    |
| wait/synch/mutex/innodb/flush_list_mutex              | NO      | NO    |
| wait/synch/mutex/innodb/fts_bg_threads_mutex          | NO      | NO    |
| wait/synch/mutex/innodb/fts_delete_mutex              | NO      | NO    |
| wait/synch/mutex/innodb/fts_optimize_mutex            | NO      | NO    |
| wait/synch/mutex/innodb/fts_doc_id_mutex              | NO      | NO    |
| wait/synch/mutex/innodb/fts_pll_tokenize_mutex        | NO      | NO    |
| wait/synch/mutex/innodb/log_flush_order_mutex         | NO      | NO    |
| wait/synch/mutex/innodb/hash_table_mutex              | NO      | NO    |
| wait/synch/mutex/innodb/ibuf_bitmap_mutex             | NO      | NO    |
| wait/synch/mutex/innodb/ibuf_mutex                    | NO      | NO    |
| wait/synch/mutex/innodb/ibuf_pessimistic_insert_mutex | NO      | NO    |
| wait/synch/mutex/innodb/log_sys_mutex                 | NO      | NO    |
| wait/synch/mutex/innodb/log_sys_write_mutex           | NO      | NO    |
| wait/synch/mutex/innodb/log_cmdq_mutex                | NO      | NO    |
| wait/synch/mutex/innodb/mutex_list_mutex              | NO      | NO    |
| wait/synch/mutex/innodb/page_cleaner_mutex            | NO      | NO    |
| wait/synch/mutex/innodb/page_zip_stat_per_index_mutex | NO      | NO    |
| wait/synch/mutex/innodb/purge_sys_pq_mutex            | NO      | NO    |
| wait/synch/mutex/innodb/recv_sys_mutex                | NO      | NO    |
| wait/synch/mutex/innodb/recv_writer_mutex             | NO      | NO    |
| wait/synch/mutex/innodb/redo_rseg_mutex               | NO      | NO    |
| wait/synch/mutex/innodb/noredo_rseg_mutex             | NO      | NO    |
| wait/synch/mutex/innodb/rw_lock_list_mutex            | NO      | NO    |
| wait/synch/mutex/innodb/rw_lock_mutex                 | NO      | NO    |
| wait/synch/mutex/innodb/srv_dict_tmpfile_mutex        | NO      | NO    |
| wait/synch/mutex/innodb/srv_innodb_monitor_mutex      | NO      | NO    |
| wait/synch/mutex/innodb/srv_misc_tmpfile_mutex        | NO      | NO    |
| wait/synch/mutex/innodb/srv_monitor_file_mutex        | NO      | NO    |
| wait/synch/mutex/innodb/buf_dblwr_mutex               | NO      | NO    |
| wait/synch/mutex/innodb/trx_undo_mutex                | NO      | NO    |
| wait/synch/mutex/innodb/trx_pool_mutex                | NO      | NO    |
| wait/synch/mutex/innodb/trx_pool_manager_mutex        | NO      | NO    |
| wait/synch/mutex/innodb/srv_sys_mutex                 | NO      | NO    |
| wait/synch/mutex/innodb/lock_mutex                    | NO      | NO    |
| wait/synch/mutex/innodb/lock_wait_mutex               | NO      | NO    |
| wait/synch/mutex/innodb/trx_mutex                     | NO      | NO    |
| wait/synch/mutex/innodb/srv_threads_mutex             | NO      | NO    |
| wait/synch/mutex/innodb/rtr_active_mutex              | NO      | NO    |
| wait/synch/mutex/innodb/rtr_match_mutex               | NO      | NO    |
| wait/synch/mutex/innodb/rtr_path_mutex                | NO      | NO    |
| wait/synch/mutex/innodb/rtr_ssn_mutex                 | NO      | NO    |
| wait/synch/mutex/innodb/trx_sys_mutex                 | NO      | NO    |
| wait/synch/mutex/innodb/thread_mutex                  | NO      | NO    |
| wait/synch/mutex/innodb/sync_array_mutex              | NO      | NO    |
| wait/synch/mutex/innodb/zip_pad_mutex                 | NO      | NO    |
| wait/synch/mutex/innodb/row_drop_list_mutex           | NO      | NO    |
| wait/synch/mutex/innodb/master_key_id_mutex           | NO      | NO    |
+-------------------------------------------------------+---------+-------+
58 rows in set (0.00 sec)

2.一些InnoDB互斥锁实例是在服务器启动时创建的，并且只有在服务器启动时启用了相关的工具时才会被检测。为了确保所有InnoDB互斥锁实例都被仪表化和启用，请将以下性能模式仪表规则添加到您的MySQL配置文件:

performance-schema-instrument='wait/synch/mutex/innodb%=ON'

如果您不需要某等待事件的所有InnoDB互斥锁数据，您可以通过在MySQL配置文件中添加额外的性能模式仪表规则来禁用特定的仪表。例如，要禁用与全文搜索相关的InnoDB互斥锁等待事件工具，请添加以下规则:

performance-schema-instrument='wait/synch/mutex/innodb/fts%=OFF'

注意:
前缀较长的规则(如wait/sync /mutex/innodb/fts%)优先于前缀较短的规则(如wait/sync /mutex/innodb/ %)。

将performance-schema-instrument规则添加到配置文件后，重新启动服务器。除了那些与全文搜索相关的其它所有的InnoDB互斥锁都是启用的。要验证这一点，请查询setup_instruments表。对于您启用的仪器，ENABLED和TIMED列应该设置为YES。

[root@localhost mysql]# service mysqld restart
Shutting down MySQL.... SUCCESS!
Starting MySQL... SUCCESS!

mysql> SELECT * FROM performance_schema.setup_instruments WHERE NAME LIKE '%wait/synch/mutex/innodb%';
+-------------------------------------------------------+---------+-------+
| NAME                                                  | ENABLED | TIMED |
+-------------------------------------------------------+---------+-------+
| wait/synch/mutex/innodb/commit_cond_mutex             | YES     | YES   |
| wait/synch/mutex/innodb/innobase_share_mutex          | YES     | YES   |
| wait/synch/mutex/innodb/autoinc_mutex                 | YES     | YES   |
| wait/synch/mutex/innodb/buf_pool_mutex                | YES     | YES   |
| wait/synch/mutex/innodb/buf_pool_zip_mutex            | YES     | YES   |
| wait/synch/mutex/innodb/cache_last_read_mutex         | YES     | YES   |
| wait/synch/mutex/innodb/dict_foreign_err_mutex        | YES     | YES   |
| wait/synch/mutex/innodb/dict_sys_mutex                | YES     | YES   |
| wait/synch/mutex/innodb/recalc_pool_mutex             | YES     | YES   |
| wait/synch/mutex/innodb/file_format_max_mutex         | YES     | YES   |
| wait/synch/mutex/innodb/fil_system_mutex              | YES     | YES   |
| wait/synch/mutex/innodb/flush_list_mutex              | YES     | YES   |
| wait/synch/mutex/innodb/fts_bg_threads_mutex          | NO      | NO    |
| wait/synch/mutex/innodb/fts_delete_mutex              | NO      | NO    |
| wait/synch/mutex/innodb/fts_optimize_mutex            | NO      | NO    |
| wait/synch/mutex/innodb/fts_doc_id_mutex              | NO      | NO    |
| wait/synch/mutex/innodb/fts_pll_tokenize_mutex        | NO      | NO    |
| wait/synch/mutex/innodb/log_flush_order_mutex         | YES     | YES   |
| wait/synch/mutex/innodb/hash_table_mutex              | YES     | YES   |
| wait/synch/mutex/innodb/ibuf_bitmap_mutex             | YES     | YES   |
| wait/synch/mutex/innodb/ibuf_mutex                    | YES     | YES   |
| wait/synch/mutex/innodb/ibuf_pessimistic_insert_mutex | YES     | YES   |
| wait/synch/mutex/innodb/log_sys_mutex                 | YES     | YES   |
| wait/synch/mutex/innodb/log_sys_write_mutex           | YES     | YES   |
| wait/synch/mutex/innodb/log_cmdq_mutex                | YES     | YES   |
| wait/synch/mutex/innodb/mutex_list_mutex              | YES     | YES   |
| wait/synch/mutex/innodb/page_cleaner_mutex            | YES     | YES   |
| wait/synch/mutex/innodb/page_zip_stat_per_index_mutex | YES     | YES   |
| wait/synch/mutex/innodb/purge_sys_pq_mutex            | YES     | YES   |
| wait/synch/mutex/innodb/recv_sys_mutex                | YES     | YES   |
| wait/synch/mutex/innodb/recv_writer_mutex             | YES     | YES   |
| wait/synch/mutex/innodb/redo_rseg_mutex               | YES     | YES   |
| wait/synch/mutex/innodb/noredo_rseg_mutex             | YES     | YES   |
| wait/synch/mutex/innodb/rw_lock_list_mutex            | YES     | YES   |
| wait/synch/mutex/innodb/rw_lock_mutex                 | YES     | YES   |
| wait/synch/mutex/innodb/srv_dict_tmpfile_mutex        | YES     | YES   |
| wait/synch/mutex/innodb/srv_innodb_monitor_mutex      | YES     | YES   |
| wait/synch/mutex/innodb/srv_misc_tmpfile_mutex        | YES     | YES   |
| wait/synch/mutex/innodb/srv_monitor_file_mutex        | YES     | YES   |
| wait/synch/mutex/innodb/buf_dblwr_mutex               | YES     | YES   |
| wait/synch/mutex/innodb/trx_undo_mutex                | YES     | YES   |
| wait/synch/mutex/innodb/trx_pool_mutex                | YES     | YES   |
| wait/synch/mutex/innodb/trx_pool_manager_mutex        | YES     | YES   |
| wait/synch/mutex/innodb/srv_sys_mutex                 | YES     | YES   |
| wait/synch/mutex/innodb/lock_mutex                    | YES     | YES   |
| wait/synch/mutex/innodb/lock_wait_mutex               | YES     | YES   |
| wait/synch/mutex/innodb/trx_mutex                     | YES     | YES   |
| wait/synch/mutex/innodb/srv_threads_mutex             | YES     | YES   |
| wait/synch/mutex/innodb/rtr_active_mutex              | YES     | YES   |
| wait/synch/mutex/innodb/rtr_match_mutex               | YES     | YES   |
| wait/synch/mutex/innodb/rtr_path_mutex                | YES     | YES   |
| wait/synch/mutex/innodb/rtr_ssn_mutex                 | YES     | YES   |
| wait/synch/mutex/innodb/trx_sys_mutex                 | YES     | YES   |
| wait/synch/mutex/innodb/thread_mutex                  | YES     | YES   |
| wait/synch/mutex/innodb/sync_array_mutex              | YES     | YES   |
| wait/synch/mutex/innodb/zip_pad_mutex                 | YES     | YES   |
| wait/synch/mutex/innodb/row_drop_list_mutex           | YES     | YES   |
| wait/synch/mutex/innodb/master_key_id_mutex           | YES     | YES   |
+-------------------------------------------------------+---------+-------+
58 rows in set (0.00 sec)

3.通过更新setup_consumers表来启用等待事件消费者。默认情况下，等待事件消费者是禁用的

mysql> UPDATE performance_schema.setup_consumers SET enabled = 'YES' WHERE name like 'events_waits%';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

可以通过查询setup_consumers表来验证是否启用了等待事件消费者。应该启用events_waits_current、events_waits_history和events_waits_history_long消费者。

mysql> SELECT * FROM performance_schema.setup_consumers;
+----------------------------------+---------+
| NAME                             | ENABLED |
+----------------------------------+---------+
| events_stages_current            | NO      |
| events_stages_history            | NO      |
| events_stages_history_long       | NO      |
| events_statements_current        | YES     |
| events_statements_history        | YES     |
| events_statements_history_long   | NO      |
| events_transactions_current      | NO      |
| events_transactions_history      | NO      |
| events_transactions_history_long | NO      |
| events_waits_current             | YES     |
| events_waits_history             | YES     |
| events_waits_history_long        | YES     |
| global_instrumentation           | YES     |
| thread_instrumentation           | YES     |
| statements_digest                | YES     |
+----------------------------------+---------+
15 rows in set (0.00 sec)

4.启用仪器和使用者后，运行要监视的工作负载。在这个例子中，mysqlslap负载模拟客户端用于模拟工作负载。

[mysql@localhost mysql]$ mysqlslap  -uroot -p123456 mysql  --auto-generate-sql --concurrency=100 --iterations=10 --number-of-queries=1000 --number-char-cols=6 --number-int-cols=6;
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
        Average number of seconds to run all queries: 11.287 seconds
        Minimum number of seconds to run all queries: 11.172 seconds
        Maximum number of seconds to run all queries: 12.000 seconds
        Number of clients running queries: 100
        Average number of queries per client: 10

5.查询wait事件数据。在这个例子中，从events_waits_summary_global_by_event_name表中查询等待事件数据，该表聚合了events_waits_current、events_waits_history和events_waits_history_long表中的数据。数据按事件名称(EVENT_NAME)汇总，这是产生事件的工具的名称。汇总数据包括:
.COUNT_STAR 汇总等待事件的数量。

.SUM_TIMER_WAIT 汇总的定时等待事件的总等待时间。

.MIN_TIMER_WAIT 汇总的定时等待事件的最小等待时间。

.AVG_TIMER_WAIT 汇总的定时等待事件的平均等待时间。

.MAX_TIMER_WAIT 汇总的定时等待事件的最大等待时间。

下面的查询返回工具名称(EVENT_NAME)、等待事件数(COUNT_STAR)和该工具事件的总等待时间(SUM_TIMER_WAIT)。因为等待的时间默认以皮秒(万亿分之一秒)为单位，所以等待时间除以1000000000以毫秒为单位。数据按汇总的等待事件数量降序排列(COUNT_STAR)。可以调整ORDER BY子句，按总等待时间对数据进行排序。

mysql> SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT/1000000000 SUM_TIMER_WAIT_MS
    -> FROM performance_schema.events_waits_summary_global_by_event_name
    -> WHERE SUM_TIMER_WAIT > 0 AND EVENT_NAME LIKE 'wait/synch/mutex/innodb/%'
    -> ORDER BY COUNT_STAR DESC;
+--------------------------------------------------+------------+-------------------+
| EVENT_NAME                                       | COUNT_STAR | SUM_TIMER_WAIT_MS |
+--------------------------------------------------+------------+-------------------+
| wait/synch/mutex/innodb/buf_pool_mutex           |   10169469 |         1019.8992 |
| wait/synch/mutex/innodb/flush_list_mutex         |    6774758 |         1142.6652 |
| wait/synch/mutex/innodb/buf_dblwr_mutex          |    3387219 |          254.6546 |
| wait/synch/mutex/innodb/log_sys_mutex            |    1306588 |          236.5495 |
| wait/synch/mutex/innodb/sync_array_mutex         |     892914 |          214.6077 |
| wait/synch/mutex/innodb/rw_lock_list_mutex       |     524623 |           17.1254 |
| wait/synch/mutex/innodb/fil_system_mutex         |     449955 |          103.0167 |
| wait/synch/mutex/innodb/dict_sys_mutex           |     432432 |           44.9851 |
| wait/synch/mutex/innodb/log_sys_write_mutex      |     426698 |           32.9908 |
| wait/synch/mutex/innodb/log_flush_order_mutex    |     424318 |           76.9258 |
| wait/synch/mutex/innodb/lock_wait_mutex          |     423173 |          154.4097 |
| wait/synch/mutex/innodb/row_drop_list_mutex      |     423165 |          115.7234 |
| wait/synch/mutex/innodb/trx_mutex                |     148083 |           14.0274 |
| wait/synch/mutex/innodb/recalc_pool_mutex        |      42731 |           16.2913 |
| wait/synch/mutex/innodb/redo_rseg_mutex          |      36442 |           72.2952 |
| wait/synch/mutex/innodb/trx_sys_mutex            |      32181 |          595.8351 |
| wait/synch/mutex/innodb/lock_mutex               |      20761 |           49.5238 |
| wait/synch/mutex/innodb/srv_innodb_monitor_mutex |       6939 |            2.3340 |
| wait/synch/mutex/innodb/trx_undo_mutex           |       6550 |            0.8013 |
| wait/synch/mutex/innodb/trx_pool_mutex           |       2272 |        15580.2291 |
| wait/synch/mutex/innodb/innobase_share_mutex     |       2015 |            0.2733 |
| wait/synch/mutex/innodb/trx_pool_manager_mutex   |       1138 |            0.2584 |
| wait/synch/mutex/innodb/file_format_max_mutex    |       1021 |            0.0499 |
| wait/synch/mutex/innodb/page_cleaner_mutex       |        567 |            0.0531 |
| wait/synch/mutex/innodb/purge_sys_pq_mutex       |         70 |            0.0063 |
| wait/synch/mutex/innodb/srv_sys_mutex            |         52 |            0.0071 |
| wait/synch/mutex/innodb/ibuf_mutex               |         29 |            0.0022 |
| wait/synch/mutex/innodb/thread_mutex             |         27 |            0.0045 |
| wait/synch/mutex/innodb/recv_sys_mutex           |         22 |            0.0023 |
| wait/synch/mutex/innodb/buf_pool_zip_mutex       |         22 |            0.0020 |
| wait/synch/mutex/innodb/recv_writer_mutex        |          1 |            0.0002 |
| wait/synch/mutex/innodb/autoinc_mutex            |          1 |            0.0001 |
+--------------------------------------------------+------------+-------------------+
32 rows in set (0.02 sec)

注意:
上述结果集包括启动过程中产生的等待事件数据。要排除这些数据，您可以在启动后和运行工作负载之前立即截断events_waits_summary_global_by_event_name表。但truncate操作本身产生的等待事件数据量可以忽略不计。

mysql> TRUNCATE performance_schema.events_waits_summary_global_by_event_name;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT/1000000000 SUM_TIMER_WAIT_MS
    -> FROM performance_schema.events_waits_summary_global_by_event_name
    -> WHERE SUM_TIMER_WAIT > 0 AND EVENT_NAME LIKE 'wait/synch/mutex/innodb/%'
    -> ORDER BY COUNT_STAR DESC;
+-----------------------------------------------+------------+-------------------+
| EVENT_NAME                                    | COUNT_STAR | SUM_TIMER_WAIT_MS |
+-----------------------------------------------+------------+-------------------+
| wait/synch/mutex/innodb/buf_pool_mutex        |        264 |            0.0381 |
| wait/synch/mutex/innodb/flush_list_mutex      |        176 |            0.0316 |
| wait/synch/mutex/innodb/buf_dblwr_mutex       |         88 |            0.0079 |
| wait/synch/mutex/innodb/log_sys_mutex         |         33 |            0.0079 |
| wait/synch/mutex/innodb/sync_array_mutex      |         22 |            0.0042 |
| wait/synch/mutex/innodb/dict_sys_mutex        |         11 |            0.0009 |
| wait/synch/mutex/innodb/fil_system_mutex      |         11 |            0.0022 |
| wait/synch/mutex/innodb/log_flush_order_mutex |         11 |            0.0020 |
| wait/synch/mutex/innodb/log_sys_write_mutex   |         11 |            0.0010 |
| wait/synch/mutex/innodb/lock_wait_mutex       |         11 |            0.0073 |
| wait/synch/mutex/innodb/row_drop_list_mutex   |         11 |            0.0067 |
| wait/synch/mutex/innodb/recalc_pool_mutex     |          1 |            0.0008 |
+-----------------------------------------------+------------+-------------------+
12 rows in set (0.02 sec)

从INFORMATION_SCHEMA.FILES中获取InnoDB表空间元数据
INFORMATION_SCHEMA。FILES表提供了所有InnoDB表空间类型的元数据，包括file-per-table表空间、通用表空间、system表空间、临时表空间和undo表空间(如果存在)。

注意:
INNODB_SYS_TABLESPACES和INNODB_SYS_DATAFILES表也提供了关于InnoDB表空间的元数据，但数据仅限于file-per-table和通用表空间。

该查询从INFORMATION_SCHEMA.FILES表的字段中检索有关InnoDB系统表空间的元数据。与InnoDB无关的字段总是返回NULL，并且被排除在查询之外。

mysql> SELECT FILE_ID, FILE_NAME, FILE_TYPE, TABLESPACE_NAME, FREE_EXTENTS,
    -> TOTAL_EXTENTS, EXTENT_SIZE, INITIAL_SIZE, MAXIMUM_SIZE, AUTOEXTEND_SIZE, DATA_FREE, STATUS ENGINE
    -> FROM INFORMATION_SCHEMA.FILES WHERE TABLESPACE_NAME LIKE 'innodb_system' \G
*************************** 1. row ***************************
        FILE_ID: 0
      FILE_NAME: ./ibdata1
      FILE_TYPE: TABLESPACE
TABLESPACE_NAME: innodb_system
   FREE_EXTENTS: 2
  TOTAL_EXTENTS: 74
    EXTENT_SIZE: 1048576
   INITIAL_SIZE: 77594624
   MAXIMUM_SIZE: NULL
AUTOEXTEND_SIZE: 67108864
      DATA_FREE: 37748736
         ENGINE: NORMAL
1 row in set (0.01 sec)

对于InnoDB file-per-table和通用表空间，下面这个查询检索FILE_ID(等价于space ID)和FILE_NAME(包含路径信息)。file-per-table和通用表空间的文件扩展名是.ibd。

mysql> SELECT FILE_ID, FILE_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_NAME LIKE '%.ibd%' ORDER BY FILE_ID;
+---------+----------------------------------------------------------+
| FILE_ID | FILE_NAME                                                |
+---------+----------------------------------------------------------+
|       5 | ./mysql/plugin.ibd                                       |
|       6 | ./mysql/servers.ibd                                      |
|       7 | ./mysql/help_topic.ibd                                   |
|       8 | ./mysql/help_category.ibd                                |
|       9 | ./mysql/help_relation.ibd                                |
|      10 | ./mysql/help_keyword.ibd                                 |
|      11 | ./mysql/time_zone_name.ibd                               |
|      12 | ./mysql/time_zone.ibd                                    |
|      13 | ./mysql/time_zone_transition.ibd                         |
|      14 | ./mysql/time_zone_transition_type.ibd                    |
|      15 | ./mysql/time_zone_leap_second.ibd                        |
|      16 | ./mysql/innodb_table_stats.ibd                           |
|      17 | ./mysql/innodb_index_stats.ibd                           |
|      18 | ./mysql/slave_relay_log_info.ibd                         |
|      19 | ./mysql/slave_master_info.ibd                            |
|      20 | ./mysql/slave_worker_info.ibd                            |
|      21 | ./mysql/gtid_executed.ibd                                |
|      22 | ./mysql/server_cost.ibd                                  |
|      23 | ./mysql/engine_cost.ibd                                  |
|      24 | ./sys/sys_config.ibd                                     |
|      37 | ./ts1.ibd                                                |
|      38 | ./ts2.ibd                                                |
|      51 | ./test/user.ibd                                          |
|      57 | ./mysql/test_index_1.ibd                                 |
|      58 | ./test/test_index_1.ibd                                  |
|      59 | ./employees/employees.ibd                                |
|      60 | ./employees/departments.ibd                              |
|      61 | ./employees/dept_manager.ibd                             |
|      62 | ./employees/dept_emp.ibd                                 |
|      63 | ./employees/titles.ibd                                   |
|      64 | ./employees/salaries.ibd                                 |
|     262 | ./test/bmsql_config.ibd                                  |
|     272 | ./test/bmsql_warehouse.ibd                               |
|     279 | ./test/bmsql_item.ibd                                    |
|     280 | ./test/bmsql_district.ibd                                |
|     281 | ./test/bmsql_customer.ibd                                |
|     283 | ./test/bmsql_history.ibd                                 |
|     284 | ./test/bmsql_new_order.ibd                               |
|     285 | ./test/bmsql_oorder.ibd                                  |
|     287 | ./test/bmsql_order_line.ibd                              |
|     289 | ./test/bmsql_stock.ibd                                   |
|     293 | ./ts01.ibd                                               |
|     294 | ./ts02.ibd                                               |
|     414 | ./employees/t.ibd                                        |
|     415 | ./employees/c.ibd                                        |
|     420 | ./test/ts03.ibd                                          |
|     423 | ./test/abc.ibd                                           |
|     425 | ./test/t3.ibd                                            |
|     426 | ./test/t4.ibd                                            |
|     427 | ./test/t7.ibd                                            |
|     428 | ./test/t8.ibd                                            |
|     472 | ./undo/opening_lines.ibd                                 |
|     473 | ./undo/FTS_00000000000001d9_BEING_DELETED.ibd            |
|     474 | ./undo/FTS_00000000000001d9_BEING_DELETED_CACHE.ibd      |
|     475 | ./undo/FTS_00000000000001d9_CONFIG.ibd                   |
|     476 | ./undo/FTS_00000000000001d9_DELETED.ibd                  |
|     477 | ./undo/FTS_00000000000001d9_DELETED_CACHE.ibd            |
|     478 | ./undo/FTS_00000000000001d9_00000000000002da_INDEX_1.ibd |
|     479 | ./undo/FTS_00000000000001d9_00000000000002da_INDEX_2.ibd |
|     480 | ./undo/FTS_00000000000001d9_00000000000002da_INDEX_3.ibd |
|     481 | ./undo/FTS_00000000000001d9_00000000000002da_INDEX_4.ibd |
|     482 | ./undo/FTS_00000000000001d9_00000000000002da_INDEX_5.ibd |
|     483 | ./undo/FTS_00000000000001d9_00000000000002da_INDEX_6.ibd |
|     484 | ./undo/t1.ibd                                            |
|     485 | ./ts3.ibd                                                |
|     487 | ./undo/big_table.ibd                                     |
|     489 | ./undo/key_block_size_4.ibd                              |
|     490 | ./ts4.ibd                                                |
|     491 | ./ts5.ibd                                                |
|     492 | ./ts6.ibd                                                |
|     494 | ./undo/t7.ibd                                            |
|     497 | ./undo/t8.ibd                                            |
|     498 | ./undo/employees.ibd                                     |
|     499 | ./undo/t9.ibd                                            |
|     500 | ./undo/t10.ibd                                           |
|     501 | ./undo/t11.ibd                                           |
|     503 | ./undo/t12.ibd                                           |
|     506 | ./undo/t13.ibd                                           |
|     507 | ./undo/t14.ibd                                           |
|     509 | ./test/parent.ibd                                        |
|     510 | ./test/child.ibd                                         |
|     523 | ./test/articles.ibd                                      |
|     524 | ./test/FTS_000000000000020c_BEING_DELETED.ibd            |
|     525 | ./test/FTS_000000000000020c_BEING_DELETED_CACHE.ibd      |
|     526 | ./test/FTS_000000000000020c_CONFIG.ibd                   |
|     527 | ./test/FTS_000000000000020c_DELETED.ibd                  |
|     528 | ./test/FTS_000000000000020c_DELETED_CACHE.ibd            |
|     529 | ./test/FTS_000000000000020c_0000000000000310_INDEX_1.ibd |
|     530 | ./test/FTS_000000000000020c_0000000000000310_INDEX_2.ibd |
|     531 | ./test/FTS_000000000000020c_0000000000000310_INDEX_3.ibd |
|     532 | ./test/FTS_000000000000020c_0000000000000310_INDEX_4.ibd |
|     533 | ./test/FTS_000000000000020c_0000000000000310_INDEX_5.ibd |
|     534 | ./test/FTS_000000000000020c_0000000000000310_INDEX_6.ibd |
|     535 | ./test/t1.ibd                                            |
+---------+----------------------------------------------------------+
94 rows in set (0.00 sec)

下面这个查询获取了InnoDB临时表空间的FILE_ID和FILE_NAME。临时表空间文件名以ibtmp为前缀。

mysql> SELECT FILE_ID, FILE_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_NAME LIKE '%ibtmp%';
+---------+-----------+
| FILE_ID | FILE_NAME |
+---------+-----------+
|     537 | ./ibtmp1  |
+---------+-----------+
1 row in set (0.01 sec)

类似地，InnoDB的undo表空间文件名以undo为前缀。如果配置了单独的undo表空间，下面的查询返回了InnoDB undo表空间的FILE_ID和FILE_NAME。

mysql> SELECT FILE_ID, FILE_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_NAME LIKE '%undo%';
+---------+----------------------------------------------------------+
| FILE_ID | FILE_NAME                                                |
+---------+----------------------------------------------------------+
|       1 | /mysqldata/mysql/undo/undo001                            |
|       2 | /mysqldata/mysql/undo/undo002                            |
|       3 | /mysqldata/mysql/undo/undo003                            |
+---------+----------------------------------------------------------+
3 rows in set (0.00 sec)

InnoDB INFORMATION_SCHEMA临时表信息表

INNODB_TEMP_TABLE_INFO表为用户提供了一个活动的InnoDB临时表的快照。这张表包含了除InnoDB内部使用的优化过的临时表之外的所有用户和系统创建的临时表的元数据，这些临时表在给定的InnoDB实例中是活动的。

mysql> SHOW TABLES FROM INFORMATION_SCHEMA LIKE 'INNODB_TEMP%';
+---------------------------------------------+
| Tables_in_information_schema (INNODB_TEMP%) |
+---------------------------------------------+
| INNODB_TEMP_TABLE_INFO                      |
+---------------------------------------------+
1 row in set (0.00 sec)

下面这个例子将展示了INNODB_TEMP_TABLE_INFO表的特征。
1.创建一个单列的简单InnoDB临时表:

mysql> CREATE TEMPORARY TABLE t1 (c1 INT PRIMARY KEY) ENGINE=INNODB;
Query OK, 0 rows affected (0.01 sec)

2.查询INNODB_TEMP_TABLE_INFO表查看临时表的元数据。

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO\G
*************************** 1. row ***************************
            TABLE_ID: 537
                NAME: #sql1d89_101_0
              N_COLS: 4
               SPACE: 444
PER_TABLE_TABLESPACE: FALSE
       IS_COMPRESSED: FALSE
1 row in set (0.00 sec)

TABLE_ID是临时表的唯一标识符。NAME列显示了系统生成的临时表的名称，前缀是“#sql”。列数(N_COLS)是4而不是1，因为InnoDB总是创建3个隐藏表列(DB_ROW_ID、DB_TRX_ID和DB_ROLL_PTR)。PER_TABLE_TABLESPACE和IS_COMPRESSED仅对压缩的临时表报告为TRUE。

3.创建一个压缩的临时表。在此之前，确保innodb_file_format设置为Barracuda，这是创建压缩行格式的表所必需的。

mysql> SET GLOBAL innodb_file_format="Barracuda";
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TEMPORARY TABLE t2 (c1 INT) ROW_FORMAT=COMPRESSED ENGINE=INNODB;
Query OK, 0 rows affected (0.00 sec)

4.再次查询INNODB_TEMP_TABLE_INFO表。

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO\G
*************************** 1. row ***************************
            TABLE_ID: 538
                NAME: #sql1d89_101_1
              N_COLS: 4
               SPACE: 536
PER_TABLE_TABLESPACE: TRUE
       IS_COMPRESSED: TRUE
*************************** 2. row ***************************
            TABLE_ID: 537
                NAME: #sql1d89_101_0
              N_COLS: 4
               SPACE: 444
PER_TABLE_TABLESPACE: FALSE
       IS_COMPRESSED: FALSE
2 rows in set (0.00 sec)

对于压缩临时表，PER_TABLE_TABLESPACE和IS_COMPRESSED报告为TRUE。压缩临时表的空间ID是不同的，因为压缩临时表是在单独的per-table表空间中创建的。非压缩临时表共享一个表空间(默认情况下是ibtmp1)，并且报告相同的空间ID。

5.重启MySQL并查询INNODB_TEMP_TABLE_INFO表。

[root@localhost mysql]# service mysqld restart
Shutting down MySQL.... SUCCESS!
Starting MySQL... SUCCESS!

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO\G
Empty set (0.00 sec)

返回一个空集合，因为INNODB_TEMP_TABLE_INFO表和其中的数据在服务器关闭时没有持久化到磁盘。

6.创建一个新的临时表。

mysql> CREATE TEMPORARY TABLE t1 (c1 INT PRIMARY KEY) ENGINE=INNODB;
Query OK, 0 rows affected (0.00 sec)

7.查询INNODB_TEMP_TABLE_INFO表查看临时表的元数据。

mysql> CREATE TEMPORARY TABLE t1 (c1 INT PRIMARY KEY) ENGINE=INNODB;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO\G
*************************** 1. row ***************************
            TABLE_ID: 539
                NAME: #sql216d_2_0
              N_COLS: 4
               SPACE: 537
PER_TABLE_TABLESPACE: FALSE
       IS_COMPRESSED: FALSE
1 row in set (0.00 sec)

空间ID是新的，因为它是在服务器重启时动态生成的。

InnoDB INFORMATION_SCHEMA度量表
MySQL 5.6.2中引入了INNODB_METRICS表，它将所有InnoDB性能和资源相关的计数器合并到一个INFORMATION_SCHEMA表中。

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="dml_inserts" \G
*************************** 1. row ***************************
           NAME: dml_inserts
      SUBSYSTEM: dml
          COUNT: 17263004
      MAX_COUNT: 17263004
      MIN_COUNT: NULL
      AVG_COUNT: 0.8178722213173183
    COUNT_RESET: 17263004
MAX_COUNT_RESET: 17263004
MIN_COUNT_RESET: NULL
AVG_COUNT_RESET: NULL
   TIME_ENABLED: 2023-05-10 09:02:54
  TIME_DISABLED: NULL
   TIME_ELAPSED: 21107214
     TIME_RESET: NULL
         STATUS: enabled
           TYPE: status_counter
        COMMENT: Number of rows inserted

启用、禁用和重置计数器
可以使用以下配置选项启用、禁用和重置计数器:
.innodb_monitor_enable:启用一个或多个计数器。

SET GLOBAL innodb_monitor_enable = [counter-name|module_name|pattern|all];

.innodb_monitor_disable:禁用一个或多个计数器。

SET GLOBAL innodb_monitor_disable = [counter-name|module_name|pattern|all];

.innodb_monitor_reset:将一个或多个计数器的计数值重置为零。

SET GLOBAL innodb_monitor_reset = [counter-name|module_name|pattern|all];

.innodb_monitor_reset_all:重置一个或多个计数器的所有值。在使用innodb_monitor_reset_all之前，必须禁用计数器。

SET GLOBAL innodb_monitor_reset_all = [counter-name|module_name|pattern|all];

你也可以在启动时使用MySQL服务器配置文件启用计数器和计数器模块。例如，要启用log模块、metadata_table_handles_opened和metadata_table_handles_closed计数器，在my.cnf配置文件的[mysqld]部分输入以下代码。

[mysqld]
innodb_monitor_enable = module_recovery,metadata_table_handles_opened,metadata_table_handles_closed

当在你的配置文件中启用多个计数器或模块时，你必须指定innodb_monitor_enable配置选项，后面跟着用逗号分隔的计数器和模块名称，如上例所示。只有innodb_monitor_enable选项可以在你的配置文件中使用。禁用和重置配置选项仅支持在命令行上。

注意:
因为每个计数器都会在服务器上增加一定程度的运行时开销，所以通常在试验和基准测试期间在测试和开发服务器上启用更多的计数器，并且仅在生产服务器上启用计数器以诊断已知问题或监视可能成为特定服务器和工作负载瓶颈的方面。

计数器
INNODB_METRICS表中的计数器可能会发生变化，所以要获取最新的计数器列表，可以在运行中的MySQL服务器上查询。

默认启用的计数器与“SHOW ENGINE INNODB STATUS”使用的计数器对应。SHOW ENGINE INNODB STATUS使用的计数器在系统级别上总是“on”的，但是你可以根据需要禁用INNODB_METRICS表的这些计数器。另外，计数器状态不是持久的。除非另外指定，否则计数器在服务器重启时将恢复到默认的启用或禁用状态。

如果你运行的程序会受到INNODB_METRICS表中新增或更改的影响，建议你查看发布说明，并在升级之前查询INNODB_METRICS表中的新版本。

mysql> SELECT name, subsystem, status FROM INFORMATION_SCHEMA.INNODB_METRICS ORDER BY NAME;
+------------------------------------------+---------------------+----------+
| name                                     | subsystem           | status   |
+------------------------------------------+---------------------+----------+
| adaptive_hash_pages_added                | adaptive_hash_index | disabled |
| adaptive_hash_pages_removed              | adaptive_hash_index | disabled |
| adaptive_hash_rows_added                 | adaptive_hash_index | disabled |
| adaptive_hash_rows_deleted_no_hash_entry | adaptive_hash_index | disabled |
| adaptive_hash_rows_removed               | adaptive_hash_index | disabled |
| adaptive_hash_rows_updated               | adaptive_hash_index | disabled |
| adaptive_hash_searches                   | adaptive_hash_index | enabled  |
| adaptive_hash_searches_btree             | adaptive_hash_index | enabled  |
| buffer_data_reads                        | buffer              | enabled  |
| buffer_data_written                      | buffer              | enabled  |
| buffer_flush_adaptive                    | buffer              | disabled |
| buffer_flush_adaptive_avg_pass           | buffer              | disabled |
| buffer_flush_adaptive_avg_time_est       | buffer              | disabled |
| buffer_flush_adaptive_avg_time_slot      | buffer              | disabled |
| buffer_flush_adaptive_avg_time_thread    | buffer              | disabled |
| buffer_flush_adaptive_pages              | buffer              | disabled |
| buffer_flush_adaptive_total_pages        | buffer              | disabled |
| buffer_flush_avg_page_rate               | buffer              | disabled |
| buffer_flush_avg_pass                    | buffer              | disabled |
| buffer_flush_avg_time                    | buffer              | disabled |
| buffer_flush_background                  | buffer              | disabled |
| buffer_flush_background_pages            | buffer              | disabled |
| buffer_flush_background_total_pages      | buffer              | disabled |
| buffer_flush_batches                     | buffer              | disabled |
| buffer_flush_batch_num_scan              | buffer              | disabled |
| buffer_flush_batch_pages                 | buffer              | disabled |
| buffer_flush_batch_scanned               | buffer              | disabled |
| buffer_flush_batch_scanned_per_call      | buffer              | disabled |
| buffer_flush_batch_total_pages           | buffer              | disabled |
| buffer_flush_lsn_avg_rate                | buffer              | disabled |
| buffer_flush_nei***or                    | buffer              | disabled |
| buffer_flush_nei***or_pages              | buffer              | disabled |
| buffer_flush_nei***or_total_pages        | buffer              | disabled |
| buffer_flush_n_to_flush_by_age           | buffer              | disabled |
| buffer_flush_n_to_flush_requested        | buffer              | disabled |
| buffer_flush_pct_for_dirty               | buffer              | disabled |
| buffer_flush_pct_for_lsn                 | buffer              | disabled |
| buffer_flush_sync                        | buffer              | disabled |
| buffer_flush_sync_pages                  | buffer              | disabled |
| buffer_flush_sync_total_pages            | buffer              | disabled |
| buffer_flush_sync_waits                  | buffer              | disabled |
| buffer_LRU_batches_evict                 | buffer              | disabled |
| buffer_LRU_batches_flush                 | buffer              | disabled |
| buffer_LRU_batch_evict_pages             | buffer              | disabled |
| buffer_LRU_batch_evict_total_pages       | buffer              | disabled |
| buffer_LRU_batch_flush_avg_pass          | buffer              | disabled |
| buffer_LRU_batch_flush_avg_time_est      | buffer              | disabled |
| buffer_LRU_batch_flush_avg_time_slot     | buffer              | disabled |
| buffer_LRU_batch_flush_avg_time_thread   | buffer              | disabled |
| buffer_LRU_batch_flush_pages             | buffer              | disabled |
| buffer_LRU_batch_flush_total_pages       | buffer              | disabled |
| buffer_LRU_batch_num_scan                | buffer              | disabled |
| buffer_LRU_batch_scanned                 | buffer              | disabled |
| buffer_LRU_batch_scanned_per_call        | buffer              | disabled |
| buffer_LRU_get_free_loops                | buffer              | disabled |
| buffer_LRU_get_free_search               | Buffer              | disabled |
| buffer_LRU_get_free_waits                | buffer              | disabled |
| buffer_LRU_search_num_scan               | buffer              | disabled |
| buffer_LRU_search_scanned                | buffer              | disabled |
| buffer_LRU_search_scanned_per_call       | buffer              | disabled |
| buffer_LRU_single_flush_failure_count    | Buffer              | disabled |
| buffer_LRU_single_flush_num_scan         | buffer              | disabled |
| buffer_LRU_single_flush_scanned          | buffer              | disabled |
| buffer_LRU_single_flush_scanned_per_call | buffer              | disabled |
| buffer_LRU_unzip_search_num_scan         | buffer              | disabled |
| buffer_LRU_unzip_search_scanned          | buffer              | disabled |
| buffer_LRU_unzip_search_scanned_per_call | buffer              | disabled |
| buffer_pages_created                     | buffer              | enabled  |
| buffer_pages_read                        | buffer              | enabled  |
| buffer_pages_written                     | buffer              | enabled  |
| buffer_page_read_blob                    | buffer_page_io      | disabled |
| buffer_page_read_fsp_hdr                 | buffer_page_io      | disabled |
| buffer_page_read_ibuf_bitmap             | buffer_page_io      | disabled |
| buffer_page_read_ibuf_free_list          | buffer_page_io      | disabled |
| buffer_page_read_index_ibuf_leaf         | buffer_page_io      | disabled |
| buffer_page_read_index_ibuf_non_leaf     | buffer_page_io      | disabled |
| buffer_page_read_index_inode             | buffer_page_io      | disabled |
| buffer_page_read_index_leaf              | buffer_page_io      | disabled |
| buffer_page_read_index_non_leaf          | buffer_page_io      | disabled |
| buffer_page_read_other                   | buffer_page_io      | disabled |
| buffer_page_read_system_page             | buffer_page_io      | disabled |
| buffer_page_read_trx_system              | buffer_page_io      | disabled |
| buffer_page_read_undo_log                | buffer_page_io      | disabled |
| buffer_page_read_xdes                    | buffer_page_io      | disabled |
| buffer_page_read_zblob                   | buffer_page_io      | disabled |
| buffer_page_read_zblob2                  | buffer_page_io      | disabled |
| buffer_page_written_blob                 | buffer_page_io      | disabled |
| buffer_page_written_fsp_hdr              | buffer_page_io      | disabled |
| buffer_page_written_ibuf_bitmap          | buffer_page_io      | disabled |
| buffer_page_written_ibuf_free_list       | buffer_page_io      | disabled |
| buffer_page_written_index_ibuf_leaf      | buffer_page_io      | disabled |
| buffer_page_written_index_ibuf_non_leaf  | buffer_page_io      | disabled |
| buffer_page_written_index_inode          | buffer_page_io      | disabled |
| buffer_page_written_index_leaf           | buffer_page_io      | disabled |
| buffer_page_written_index_non_leaf       | buffer_page_io      | disabled |
| buffer_page_written_other                | buffer_page_io      | disabled |
| buffer_page_written_system_page          | buffer_page_io      | disabled |
| buffer_page_written_trx_system           | buffer_page_io      | disabled |
| buffer_page_written_undo_log             | buffer_page_io      | disabled |
| buffer_page_written_xdes                 | buffer_page_io      | disabled |
| buffer_page_written_zblob                | buffer_page_io      | disabled |
| buffer_page_written_zblob2               | buffer_page_io      | disabled |
| buffer_pool_bytes_data                   | buffer              | enabled  |
| buffer_pool_bytes_dirty                  | buffer              | enabled  |
| buffer_pool_pages_data                   | buffer              | enabled  |
| buffer_pool_pages_dirty                  | buffer              | enabled  |
| buffer_pool_pages_free                   | buffer              | enabled  |
| buffer_pool_pages_misc                   | buffer              | enabled  |
| buffer_pool_pages_total                  | buffer              | enabled  |
| buffer_pool_reads                        | buffer              | enabled  |
| buffer_pool_read_ahead                   | buffer              | enabled  |
| buffer_pool_read_ahead_evicted           | buffer              | enabled  |
| buffer_pool_read_requests                | buffer              | enabled  |
| buffer_pool_size                         | server              | enabled  |
| buffer_pool_wait_free                    | buffer              | enabled  |
| buffer_pool_write_requests               | buffer              | enabled  |
| compression_pad_decrements               | compression         | disabled |
| compression_pad_increments               | compression         | disabled |
| compress_pages_compressed                | compression         | disabled |
| compress_pages_decompressed              | compression         | disabled |
| ddl_background_drop_indexes              | ddl                 | disabled |
| ddl_background_drop_tables               | ddl                 | disabled |
| ddl_log_file_alter_table                 | ddl                 | disabled |
| ddl_online_create_index                  | ddl                 | disabled |
| ddl_pending_alter_table                  | ddl                 | disabled |
| ddl_sort_file_alter_table                | ddl                 | disabled |
| dml_deletes                              | dml                 | enabled  |
| dml_inserts                              | dml                 | enabled  |
| dml_reads                                | dml                 | disabled |
| dml_updates                              | dml                 | enabled  |
| file_num_open_files                      | file_system         | enabled  |
| ibuf_merges                              | change_buffer       | enabled  |
| ibuf_merges_delete                       | change_buffer       | enabled  |
| ibuf_merges_delete_mark                  | change_buffer       | enabled  |
| ibuf_merges_discard_delete               | change_buffer       | enabled  |
| ibuf_merges_discard_delete_mark          | change_buffer       | enabled  |
| ibuf_merges_discard_insert               | change_buffer       | enabled  |
| ibuf_merges_insert                       | change_buffer       | enabled  |
| ibuf_size                                | change_buffer       | enabled  |
| icp_attempts                             | icp                 | disabled |
| icp_match                                | icp                 | disabled |
| icp_no_match                             | icp                 | disabled |
| icp_out_of_range                         | icp                 | disabled |
| index_page_discards                      | index               | disabled |
| index_page_merge_attempts                | index               | disabled |
| index_page_merge_successful              | index               | disabled |
| index_page_reorg_attempts                | index               | disabled |
| index_page_reorg_successful              | index               | disabled |
| index_page_splits                        | index               | disabled |
| innodb_activity_count                    | server              | enabled  |
| innodb_background_drop_table_usec        | server              | disabled |
| innodb_checkpoint_usec                   | server              | disabled |
| innodb_dblwr_pages_written               | server              | enabled  |
| innodb_dblwr_writes                      | server              | enabled  |
| innodb_dict_lru_count                    | server              | disabled |
| innodb_dict_lru_usec                     | server              | disabled |
| innodb_ibuf_merge_usec                   | server              | disabled |
| innodb_log_flush_usec                    | server              | disabled |
| innodb_master_active_loops               | server              | disabled |
| innodb_master_idle_loops                 | server              | disabled |
| innodb_master_purge_usec                 | server              | disabled |
| innodb_master_thread_sleeps              | server              | disabled |
| innodb_mem_validate_usec                 | server              | disabled |
| innodb_page_size                         | server              | enabled  |
| innodb_rwlock_sx_os_waits                | server              | enabled  |
| innodb_rwlock_sx_spin_rounds             | server              | enabled  |
| innodb_rwlock_sx_spin_waits              | server              | enabled  |
| innodb_rwlock_s_os_waits                 | server              | enabled  |
| innodb_rwlock_s_spin_rounds              | server              | enabled  |
| innodb_rwlock_s_spin_waits               | server              | enabled  |
| innodb_rwlock_x_os_waits                 | server              | enabled  |
| innodb_rwlock_x_spin_rounds              | server              | enabled  |
| innodb_rwlock_x_spin_waits               | server              | enabled  |
| lock_deadlocks                           | lock                | enabled  |
| lock_rec_locks                           | lock                | disabled |
| lock_rec_lock_created                    | lock                | disabled |
| lock_rec_lock_removed                    | lock                | disabled |
| lock_rec_lock_requests                   | lock                | disabled |
| lock_rec_lock_waits                      | lock                | disabled |
| lock_row_lock_current_waits              | lock                | enabled  |
| lock_row_lock_time                       | lock                | enabled  |
| lock_row_lock_time_avg                   | lock                | enabled  |
| lock_row_lock_time_max                   | lock                | enabled  |
| lock_row_lock_waits                      | lock                | enabled  |
| lock_table_locks                         | lock                | disabled |
| lock_table_lock_created                  | lock                | disabled |
| lock_table_lock_removed                  | lock                | disabled |
| lock_table_lock_waits                    | lock                | disabled |
| lock_timeouts                            | lock                | enabled  |
| log_checkpoints                          | recovery            | disabled |
| log_lsn_buf_pool_oldest                  | recovery            | disabled |
| log_lsn_checkpoint_age                   | recovery            | disabled |
| log_lsn_current                          | recovery            | disabled |
| log_lsn_last_checkpoint                  | recovery            | disabled |
| log_lsn_last_flush                       | recovery            | disabled |
| log_max_modified_age_async               | recovery            | disabled |
| log_max_modified_age_sync                | recovery            | disabled |
| log_num_log_io                           | recovery            | disabled |
| log_padded                               | recovery            | enabled  |
| log_pending_checkpoint_writes            | recovery            | disabled |
| log_pending_log_flushes                  | recovery            | disabled |
| log_waits                                | recovery            | enabled  |
| log_writes                               | recovery            | enabled  |
| log_write_requests                       | recovery            | enabled  |
| metadata_table_handles_closed            | metadata            | disabled |
| metadata_table_handles_opened            | metadata            | disabled |
| metadata_table_reference_count           | metadata            | disabled |
| os_data_fsyncs                           | os                  | enabled  |
| os_data_reads                            | os                  | enabled  |
| os_data_writes                           | os                  | enabled  |
| os_log_bytes_written                     | os                  | enabled  |
| os_log_fsyncs                            | os                  | enabled  |
| os_log_pending_fsyncs                    | os                  | enabled  |
| os_log_pending_writes                    | os                  | enabled  |
| os_pending_reads                         | os                  | disabled |
| os_pending_writes                        | os                  | disabled |
| purge_del_mark_records                   | purge               | disabled |
| purge_dml_delay_usec                     | purge               | disabled |
| purge_invoked                            | purge               | disabled |
| purge_resume_count                       | purge               | disabled |
| purge_stop_count                         | purge               | disabled |
| purge_undo_log_pages                     | purge               | disabled |
| purge_upd_exist_or_extern_records        | purge               | disabled |
| trx_active_transactions                  | transaction         | disabled |
| trx_commits_insert_update                | transaction         | disabled |
| trx_nl_ro_commits                        | transaction         | disabled |
| trx_rollbacks                            | transaction         | disabled |
| trx_rollbacks_savepoint                  | transaction         | disabled |
| trx_rollback_active                      | transaction         | disabled |
| trx_ro_commits                           | transaction         | disabled |
| trx_rseg_current_size                    | transaction         | disabled |
| trx_rseg_history_len                     | transaction         | enabled  |
| trx_rw_commits                           | transaction         | disabled |
| trx_undo_slots_cached                    | transaction         | disabled |
| trx_undo_slots_used                      | transaction         | disabled |
+------------------------------------------+---------------------+----------+
235 rows in set (0.03 sec)

计数器模块
模块名与INNODB_METRICS表中的SUBSYSTEM列对应，但不完全相同。相对于单独启用、禁用或重置计数器，您可以使用模块名称来快速启用、禁用或重置特定子系统的所有计数器。例如，使用module_dml启用与dml子系统关联的所有计数器。

mysql> SELECT name, subsystem, status FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem ='dml';
+-------------+-----------+----------+
| name        | subsystem | status   |
+-------------+-----------+----------+
| dml_reads   | dml       | disabled |
| dml_inserts | dml       | enabled  |
| dml_deletes | dml       | enabled  |
| dml_updates | dml       | enabled  |
+-------------+-----------+----------+
4 rows in set (0.00 sec)

mysql> SET GLOBAL innodb_monitor_enable = module_dml;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT name, subsystem, status FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem ='dml';
+-------------+-----------+---------+
| name        | subsystem | status  |
+-------------+-----------+---------+
| dml_reads   | dml       | enabled |
| dml_inserts | dml       | enabled |
| dml_deletes | dml       | enabled |
| dml_updates | dml       | enabled |
+-------------+-----------+---------+
4 rows in set (0.00 sec)

下面是innodb_monitor_enable和相关的配置选项可以使用的module_name值以及相应的子系统名称。

.module_adaptive_hash (subsystem = adaptive_hash_index)
.module_buffer (subsystem = buffer)
.module_buffer_page (subsystem = buffer_page_io)
.module_compress (subsystem = compression)
.module_ddl (subsystem = ddl)
.module_dml (subsystem = dml)
.module_file (subsystem = file_system)
.module_ibuf_system (subsystem = change_buffer)
.module_icp (subsystem = icp)
.module_index (subsystem = index)
.module_innodb (subsystem = innodb)
.module_lock (subsystem = lock)
.module_log (subsystem = recovery)
.module_metadata (subsystem = metadata)
.module_os (subsystem = os)
.module_purge (subsystem = purge)
.module_trx (subsystem = transaction)

使用INNODB_METRICS表计数器
下面这个例子演示了启用、禁用、重置计数器，以及查询INNODB_METRICS表中的计数器数据。

1.创建一个简单的InnoDB表;

mysql> use test
Database changed
mysql> CREATE TABLE t1 (c1 INT) ENGINE=INNODB;
Query OK, 0 rows affected (0.01 sec)

2.启用dml_inserts计数器。
先禁用dml_inserts计数器并将所有计数清零

mysql> SET GLOBAL innodb_monitor_disable=dml_inserts;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="dml_inserts" \G
*************************** 1. row ***************************
           NAME: dml_inserts
      SUBSYSTEM: dml
          COUNT: 17263007
      MAX_COUNT: 17263007
      MIN_COUNT: NULL
      AVG_COUNT: 0.817808627174022
    COUNT_RESET: 0
MAX_COUNT_RESET: 0
MIN_COUNT_RESET: NULL
AVG_COUNT_RESET: 0
   TIME_ENABLED: 2023-05-10 09:02:54
  TIME_DISABLED: 2024-01-09 16:37:13
   TIME_ELAPSED: 21108859
     TIME_RESET: NULL
         STATUS: disabled
           TYPE: status_counter
        COMMENT: Number of rows inserted
1 row in set (0.00 sec)

mysql> SET GLOBAL innodb_monitor_reset_all=dml_inserts;
Query OK, 0 rows affected (0.05 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="dml_inserts" \G
*************************** 1. row ***************************
           NAME: dml_inserts
      SUBSYSTEM: dml
          COUNT: 0
      MAX_COUNT: NULL
      MIN_COUNT: NULL
      AVG_COUNT: NULL
    COUNT_RESET: 0
MAX_COUNT_RESET: NULL
MIN_COUNT_RESET: NULL
AVG_COUNT_RESET: NULL
   TIME_ENABLED: NULL
  TIME_DISABLED: NULL
   TIME_ELAPSED: NULL
     TIME_RESET: NULL
         STATUS: disabled
           TYPE: status_counter
        COMMENT: Number of rows inserted
1 row in set (0.00 sec)

再启用dml_inserts计数器

mysql> SET GLOBAL innodb_monitor_enable = dml_inserts;
Query OK, 0 rows affected (0.00 sec)

在INNODB_METRICS表的COMMENT列中找到的关于dml_inserts计数器的描述:

mysql> SELECT NAME, COMMENT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="dml_inserts";
+-------------+-------------------------+
| NAME        | COMMENT                 |
+-------------+-------------------------+
| dml_inserts | Number of rows inserted |
+-------------+-------------------------+
1 row in set (0.00 sec)

3.查询INNODB_METRICS表中的dml_inserts计数器数据。因为没有执行DML操作，所以计数器值为零或NULL。TIME_ENABLED和TIME_ELAPSED值表示计数器上次启用的时间，以及从该时间起已经过去了多少秒。

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="dml_inserts" \G
*************************** 1. row ***************************
           NAME: dml_inserts
      SUBSYSTEM: dml
          COUNT: 0
      MAX_COUNT: 0
      MIN_COUNT: NULL
      AVG_COUNT: 0
    COUNT_RESET: 0
MAX_COUNT_RESET: 0
MIN_COUNT_RESET: NULL
AVG_COUNT_RESET: NULL
   TIME_ENABLED: 2024-01-09 16:40:33
  TIME_DISABLED: NULL
   TIME_ELAPSED: 150
     TIME_RESET: NULL
         STATUS: enabled
           TYPE: status_counter
        COMMENT: Number of rows inserted
1 row in set (0.00 sec)

4.向表中插入三行数据。

mysql> INSERT INTO t1 values(1);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t1 values(2);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t1 values(3);
Query OK, 1 row affected (0.00 sec)

5.再次查询INNODB_METRICS表获取dml_inserts计数器数据。现在有一些计数器的值已经递增，包括COUNT、MAX_COUNT、AVG_COUNT和COUNT_RESET。

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="dml_inserts"\G
*************************** 1. row ***************************
           NAME: dml_inserts
      SUBSYSTEM: dml
          COUNT: 3
      MAX_COUNT: 3
      MIN_COUNT: NULL
      AVG_COUNT: 0.00980392156862745
    COUNT_RESET: 3
MAX_COUNT_RESET: 3
MIN_COUNT_RESET: NULL
AVG_COUNT_RESET: NULL
   TIME_ENABLED: 2024-01-09 16:40:33
  TIME_DISABLED: NULL
   TIME_ELAPSED: 306
     TIME_RESET: NULL
         STATUS: enabled
           TYPE: status_counter
        COMMENT: Number of rows inserted
1 row in set (0.00 sec)

6.重置dml_inserts计数器，然后再次在INNODB_METRICS表中查询dml_inserts计数器数据。之前报告的%_RESET值，如COUNT_RESET和MAX_RESET，将重置为零。COUNT、MAX_COUNT和AVG_COUNT等值从计数器启用时开始收集数据，这些值不受重置的影响。

mysql> SET GLOBAL innodb_monitor_disable=dml_inserts;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="dml_inserts"\G
*************************** 1. row ***************************
           NAME: dml_inserts
      SUBSYSTEM: dml
          COUNT: 20
      MAX_COUNT: 20
      MIN_COUNT: NULL
      AVG_COUNT: 0.03424657534246575
    COUNT_RESET: 0
MAX_COUNT_RESET: 0
MIN_COUNT_RESET: NULL
AVG_COUNT_RESET: 0
   TIME_ENABLED: 2024-01-09 16:40:33
  TIME_DISABLED: 2024-01-09 16:50:17
   TIME_ELAPSED: 584
     TIME_RESET: NULL
         STATUS: disabled
           TYPE: status_counter
        COMMENT: Number of rows inserted
1 row in set (0.00 sec)

7.要重置所有计数器值，必须首先禁用计数器。禁用计数器将状态值设置为禁用。

mysql> SET GLOBAL innodb_monitor_disable=dml_inserts;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="dml_inserts"\G
*************************** 1. row ***************************
           NAME: dml_inserts
      SUBSYSTEM: dml
          COUNT: 20
      MAX_COUNT: 20
      MIN_COUNT: NULL
      AVG_COUNT: 0.03076923076923077
    COUNT_RESET: 0
MAX_COUNT_RESET: 0
MIN_COUNT_RESET: NULL
AVG_COUNT_RESET: 0
   TIME_ENABLED: 2024-01-09 16:40:33
  TIME_DISABLED: 2024-01-09 16:51:23
   TIME_ELAPSED: 650
     TIME_RESET: NULL
         STATUS: disabled
           TYPE: status_counter
        COMMENT: Number of rows inserted
1 row in set (0.01 sec)

注意:
计数器和模块名称支持通配符匹配。例如，可以指定dml_i%，而不是指定完整的dml_inserts计数器名称。还可以使用通配符匹配一次启用、禁用或重置多个计数器或模块。例如，指定dml_%来启用、禁用或重置所有以dml_%开头的计数器。

8.当计数器被禁用后，你可以使用innodb_monitor_reset_all选项重置所有计数器的值。所有值都设置为0或NULL。

mysql> SET GLOBAL innodb_monitor_reset_all=dml_inserts;
Query OK, 0 rows affected (0.05 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="dml_inserts"\G
*************************** 1. row ***************************
           NAME: dml_inserts
      SUBSYSTEM: dml
          COUNT: 0
      MAX_COUNT: NULL
      MIN_COUNT: NULL
      AVG_COUNT: NULL
    COUNT_RESET: 0
MAX_COUNT_RESET: NULL
MIN_COUNT_RESET: NULL
AVG_COUNT_RESET: NULL
   TIME_ENABLED: NULL
  TIME_DISABLED: NULL
   TIME_ELAPSED: NULL
     TIME_RESET: NULL
         STATUS: disabled
           TYPE: status_counter
        COMMENT: Number of rows inserted
1 row in set (0.00 sec)

InnoDB INFORMATION_SCHEMA缓冲池表
InnoDB INFORMATION_SCHEMA缓冲池表提供了缓冲池状态信息和InnoDB缓冲池中页的元数据。这些表是在MySQL 5.6.2中引入的，后来移植到MySQL 5.5 (MySQL 5.5.28)和MySQL 5.1 (MySQL 5.1.66)。

InnoDB INFORMATION_SCHEMA缓冲池表包括下面这些表:

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:保存InnoDB缓冲池中每一页的信息。

.INNODB_BUFFER_PAGE_LRU:保存了关于InnoDB缓冲池中页的信息，特别是它们在LRU列表中的排序方式，该列表决定了当缓冲池满时从缓冲池中清除哪些页。INNODB_BUFFER_PAGE_LRU表和INNODB_BUFFER_PAGE表有相同的列，不同的是INNODB_BUFFER_PAGE_LRU表有一个LRU_POSITION列而不是BLOCK_ID列。

.INNODB_BUFFER_POOL_STATS:缓冲池状态信息。大部分相同的信息由SHOW ENGINE INNODB STATUS输出提供，或者可以通过使用INNODB缓冲池服务器状态变量获得。

警示:
查询INNODB_BUFFER_PAGE表或INNODB_BUFFER_PAGE_LRU表会引入显著的性能开销。不要在生产系统上查询这些表，除非您意识到您的查询可能产生的性能影响，并确定它是可接受的。为了避免影响性能，在测试实例上重现您想要调查的问题，并在测试实例上运行您的查询。

查询INNODB_BUFFER_PAGE表中的系统数据
该查询通过排除TABLE_NAME值为NULL或包含表名中有斜杠/或句点.的表名表示用户自定义表，提供了包含系统数据的页面的大致数量。

mysql> select count(*) from information_schema.innodb_buffer_page
    -> where table_name is null or (instr(table_name, '/') = 0 and instr(table_name, '.') = 0);
+----------+
| count(*) |
+----------+
|   451428 |
+----------+
1 row in set (1.80 sec)

该查询返回包含系统数据的大约页数、缓冲池页数和包含系统数据的大约百分比的页数。

mysql> select
    -> (select count(*) from information_schema.innodb_buffer_page
    -> where table_name is null or (instr(table_name, '/') = 0 and instr(table_name, '.') = 0)
    -> ) as system_pages,
    -> (
    -> select count(*)
    -> from information_schema.innodb_buffer_page
    -> ) as total_pages,
    -> (
    -> select round((system_pages/total_pages) * 100)
    -> ) as system_page_percentage;
+--------------+-------------+------------------------+
| system_pages | total_pages | system_page_percentage |
+--------------+-------------+------------------------+
|       451428 |      524224 |                     86 |
+--------------+-------------+------------------------+
1 row in set (3.35 sec)

通过查询PAGE_TYPE的值，可以确定缓冲池中系统数据的类型。例如，下面的查询返回包含系统数据的页的10个不同的PAGE_TYPE值:

mysql> select distinct page_type from information_schema.innodb_buffer_page
    -> where table_name is null or (instr(table_name, '/') = 0 and instr(table_name, '.') = 0);
+-------------------+
| page_type         |
+-------------------+
| SYSTEM            |
| INODE             |
| IBUF_INDEX        |
| INDEX             |
| IBUF_BITMAP       |
| TRX_SYSTEM        |
| FILE_SPACE_HEADER |
| UNKNOWN           |
| UNDO_LOG          |
| EXTENT_DESCRIPTOR |
+-------------------+
10 rows in set (2.02 sec)

查询INNODB_BUFFER_PAGE表中的用户数据
这个查询通过统计TABLE_NAME值不为NULL且不像’%INNODB_SYS_TABLES%’的页面，提供了包含用户数据的页面的近似计数。

mysql> select count(*) from information_schema.innodb_buffer_page
    -> where table_name is not null and table_name not like '%innodb_sys_tables%';
+----------+
| count(*) |
+----------+
|    91067 |
+----------+
1 row in set (1.67 sec)

该查询返回包含用户数据的页面的大致数量、缓冲池页面的总数以及包含用户数据的页面的大致百分比。

mysql> select
    -> (select count(*) from information_schema.innodb_buffer_page
    -> where table_name is not null and (instr(table_name, '/') > 0 or instr(table_name, '.') > 0)
    -> ) as user_pages,
    -> (
    -> select count(*)
    -> from information_schema.innodb_buffer_page
    -> ) as total_pages,
    -> (
    -> select round((user_pages/total_pages) * 100)
    -> ) as user_page_percentage;
+------------+-------------+----------------------+
| user_pages | total_pages | user_page_percentage |
+------------+-------------+----------------------+
|      72796 |      524224 |                   14 |
+------------+-------------+----------------------+
1 row in set (3.31 sec)

这个查询将使用缓冲池中的页的用户定义的表标识出来:

mysql> select distinct table_name from information_schema.innodb_buffer_page
    -> where table_name is not null and (instr(table_name, '/') > 0 or instr(table_name, '.') > 0)
    -> and table_name not like '`mysql`.`innodb_%';
+--------------------------------------------------------+
| table_name                                             |
+--------------------------------------------------------+
| `undo`.`opening_lines`                                 |
| `undo`.`FTS_00000000000001d9_00000000000002da_INDEX_3` |
| `mysql`.`help_category`                                |
| `mysql`.`help_topic`                                   |
| `undo`.`big_table`                                     |
| `undo`.`key_block_size_4`                              |
| `sys`.`sys_config`                                     |
| `test`.`FTS_000000000000020c_DELETED_CACHE`            |
| `undo`.`FTS_00000000000001d9_BEING_DELETED`            |
| `undo`.`FTS_00000000000001d9_00000000000002da_INDEX_4` |
| `mysql`.`help_relation`                                |
| `undo`.`t8`                                            |
| `test`.`FTS_000000000000020c_0000000000000310_INDEX_1` |
| `mysql`.`slave_relay_log_info`                         |
| `undo`.`FTS_00000000000001d9_BEING_DELETED_CACHE`      |
| `undo`.`FTS_00000000000001d9_00000000000002da_INDEX_5` |
| `mysql`.`help_keyword`                                 |
| `undo`.`employees`                                     |
| `undo`.`t13`                                           |
| `test`.`FTS_000000000000020c_0000000000000310_INDEX_2` |
| `mysql`.`time_zone_name`                               |
| `mysql`.`slave_master_info`                            |
| `test`.`t7`                                            |
| `test`.`user`                                          |
| `undo`.`FTS_00000000000001d9_CONFIG`                   |
| `undo`.`FTS_00000000000001d9_00000000000002da_INDEX_6` |
| `undo`.`t9`                                            |
| `undo`.`t14`                                           |
| `test`.`articles`                                      |
| `test`.`FTS_000000000000020c_0000000000000310_INDEX_3` |
| `mysql`.`time_zone`                                    |
| `mysql`.`slave_worker_info`                            |
| `test`.`t8`                                            |
| `undo`.`FTS_00000000000001d9_DELETED`                  |
| `undo`.`t1`                                            |
| `undo`.`t10`                                           |
| `test`.`t1`                                            |
| `test`.`FTS_000000000000020c_BEING_DELETED`            |
| `test`.`FTS_000000000000020c_0000000000000310_INDEX_4` |
| `mysql`.`plugin`                                       |
| `mysql`.`gtid_executed`                                |
| `mysql`.`time_zone_transition`                         |
| `undo`.`FTS_00000000000001d9_DELETED_CACHE`            |
| `undo`.`t2`                                            |
| `undo`.`t11`                                           |
| `test`.`parent`                                        |
| `test`.`FTS_000000000000020c_BEING_DELETED_CACHE`      |
| `test`.`FTS_000000000000020c_0000000000000310_INDEX_5` |
| `mysql`.`server_cost`                                  |
| `mysql`.`time_zone_transition_type`                    |
| `mysql`.`servers`                                      |
| `undo`.`FTS_00000000000001d9_00000000000002da_INDEX_1` |
| `undo`.`t7`                                            |
| `test`.`child`                                         |
| `test`.`FTS_000000000000020c_CONFIG`                   |
| `test`.`FTS_000000000000020c_0000000000000310_INDEX_6` |
| `mysql`.`engine_cost`                                  |
| `mysql`.`time_zone_leap_second`                        |
| `undo`.`FTS_00000000000001d9_00000000000002da_INDEX_2` |
| `undo`.`t12`                                           |
| `test`.`abc`                                           |
| `test`.`FTS_000000000000020c_DELETED`                  |
+--------------------------------------------------------+
62 rows in set (1.81 sec)

查询INNODB_BUFFER_PAGE表中的索引数据
有关索引页的信息，请使用索引的名称查询INDEX_NAME列。例如，下面的查询返回在employees.salaries表上定义的emp_no索引的页数和总数据大小：

mysql> select index_name, count(*) as pages,
    -> round(sum(if(compressed_size = 0, @@global.innodb_page_size, compressed_size))/1024/1024)
    -> as 'total data (mb)'
    -> from information_schema.innodb_buffer_page
    -> where index_name='emp_no' and table_name = '`employees`.`salaries`';
+------------+-------+-----------------+
| index_name | pages | total data (mb) |
+------------+-------+-----------------+
| emp_no     |    44 |               1 |
+------------+-------+-----------------+
1 row in set (1.75 sec)

该查询返回在employees.salaries表上定义的所有索引的页数和总数据大小:

mysql> select index_name, count(*) as pages,
    -> round(sum(if(compressed_size = 0, @@global.innodb_page_size, compressed_size))/1024/1024)
    -> as 'total data (mb)'
    -> from information_schema.innodb_buffer_page
    -> where table_name = '`employees`.`salaries`'
    -> group by index_name;
+------------+-------+-----------------+
| index_name | pages | total data (mb) |
+------------+-------+-----------------+
| emp_no     |    44 |               1 |
| idx_salary |    65 |               1 |
| PRIMARY    |  6086 |              95 |
+------------+-------+-----------------+
3 rows in set (1.73 sec)

查询INNODB_BUFFER_PAGE_LRU表中的LRU_POSITION数据
INNODB_BUFFER_PAGE_LRU表保存了InnoDB缓冲池中页的信息，特别是当缓冲池满时，它们是如何排序的，这决定了哪些页需要从缓冲池中移除。该表的定义与INNODB_BUFFER_PAGE表相同，不同之处在于该表有一个LRU_POSITION列而不是BLOCK_ID列。

该查询计算LRU链表中特定位置上employees.employees表所使用的页数:

mysql> select count(lru_position) from information_schema.innodb_buffer_page_lru
    -> where table_name='`employees`.`employees`' and lru_position < 6570;
+---------------------+
| count(lru_position) |
+---------------------+
|                  13 |
+---------------------+
1 row in set (0.58 sec)

查询INNODB_BUFFER_POOL_STATS表
INNODB_BUFFER_POOL_STATS表提供了类似于SHOW ENGINE INNODB STATUS和INNODB缓冲池状态变量的信息。

mysql> select * from information_schema.innodb_buffer_pool_stats \G
*************************** 1. row ***************************
                         POOL_ID: 0
                       POOL_SIZE: 65528
                    FREE_BUFFERS: 46471
                  DATABASE_PAGES: 17936
              OLD_DATABASE_PAGES: 6640
         MODIFIED_DATABASE_PAGES: 0
              PENDING_DECOMPRESS: 4480
                   PENDING_READS: 0
               PENDING_FLUSH_LRU: 0
              PENDING_FLUSH_LIST: 0
                PAGES_MADE_YOUNG: 16823
            PAGES_NOT_MADE_YOUNG: 19769
           PAGES_MADE_YOUNG_RATE: 0
       PAGES_MADE_NOT_YOUNG_RATE: 0
               NUMBER_PAGES_READ: 857
            NUMBER_PAGES_CREATED: 17087
            NUMBER_PAGES_WRITTEN: 24505
                 PAGES_READ_RATE: 0
               PAGES_CREATE_RATE: 0
              PAGES_WRITTEN_RATE: 0
                NUMBER_PAGES_GET: 3448729
                        HIT_RATE: 0
    YOUNG_MAKE_PER_THOUSAND_GETS: 0
NOT_YOUNG_MAKE_PER_THOUSAND_GETS: 0
         NUMBER_PAGES_READ_AHEAD: 768
       NUMBER_READ_AHEAD_EVICTED: 0
                 READ_AHEAD_RATE: 0
         READ_AHEAD_EVICTED_RATE: 0
                    LRU_IO_TOTAL: 0
                  LRU_IO_CURRENT: 0
                UNCOMPRESS_TOTAL: 0
              UNCOMPRESS_CURRENT: 0
*************************** 2. row ***************************
                         POOL_ID: 1
                       POOL_SIZE: 65528
                    FREE_BUFFERS: 47000
                  DATABASE_PAGES: 17515
              OLD_DATABASE_PAGES: 6485
         MODIFIED_DATABASE_PAGES: 0
              PENDING_DECOMPRESS: 4051
                   PENDING_READS: 0
               PENDING_FLUSH_LRU: 0
              PENDING_FLUSH_LIST: 0
                PAGES_MADE_YOUNG: 0
            PAGES_NOT_MADE_YOUNG: 0
           PAGES_MADE_YOUNG_RATE: 0
       PAGES_MADE_NOT_YOUNG_RATE: 0
               NUMBER_PAGES_READ: 864
            NUMBER_PAGES_CREATED: 16651
            NUMBER_PAGES_WRITTEN: 23771
                 PAGES_READ_RATE: 0
               PAGES_CREATE_RATE: 0
              PAGES_WRITTEN_RATE: 0
                NUMBER_PAGES_GET: 5410983
                        HIT_RATE: 0
    YOUNG_MAKE_PER_THOUSAND_GETS: 0
NOT_YOUNG_MAKE_PER_THOUSAND_GETS: 0
         NUMBER_PAGES_READ_AHEAD: 832
       NUMBER_READ_AHEAD_EVICTED: 0
                 READ_AHEAD_RATE: 0
         READ_AHEAD_EVICTED_RATE: 0
                    LRU_IO_TOTAL: 0
                  LRU_IO_CURRENT: 0
                UNCOMPRESS_TOTAL: 0
              UNCOMPRESS_CURRENT: 0
*************************** 3. row ***************************
                         POOL_ID: 2
                       POOL_SIZE: 65528
                    FREE_BUFFERS: 46659
                  DATABASE_PAGES: 17733
              OLD_DATABASE_PAGES: 6565
         MODIFIED_DATABASE_PAGES: 0
              PENDING_DECOMPRESS: 4544
                   PENDING_READS: 0
               PENDING_FLUSH_LRU: 0
              PENDING_FLUSH_LIST: 0
                PAGES_MADE_YOUNG: 0
            PAGES_NOT_MADE_YOUNG: 0
           PAGES_MADE_YOUNG_RATE: 0
       PAGES_MADE_NOT_YOUNG_RATE: 0
               NUMBER_PAGES_READ: 755
            NUMBER_PAGES_CREATED: 16978
            NUMBER_PAGES_WRITTEN: 23908
                 PAGES_READ_RATE: 0
               PAGES_CREATE_RATE: 0
              PAGES_WRITTEN_RATE: 0
                NUMBER_PAGES_GET: 3462987
                        HIT_RATE: 0
    YOUNG_MAKE_PER_THOUSAND_GETS: 0
NOT_YOUNG_MAKE_PER_THOUSAND_GETS: 0
         NUMBER_PAGES_READ_AHEAD: 640
       NUMBER_READ_AHEAD_EVICTED: 0
                 READ_AHEAD_RATE: 0
         READ_AHEAD_EVICTED_RATE: 0
                    LRU_IO_TOTAL: 0
                  LRU_IO_CURRENT: 0
                UNCOMPRESS_TOTAL: 0
              UNCOMPRESS_CURRENT: 0
*************************** 4. row ***************************
                         POOL_ID: 3
                       POOL_SIZE: 65528
                    FREE_BUFFERS: 46644
                  DATABASE_PAGES: 17751
              OLD_DATABASE_PAGES: 6572
         MODIFIED_DATABASE_PAGES: 0
              PENDING_DECOMPRESS: 4528
                   PENDING_READS: 0
               PENDING_FLUSH_LRU: 0
              PENDING_FLUSH_LIST: 0
                PAGES_MADE_YOUNG: 2251
            PAGES_NOT_MADE_YOUNG: 17746
           PAGES_MADE_YOUNG_RATE: 0
       PAGES_MADE_NOT_YOUNG_RATE: 0
               NUMBER_PAGES_READ: 802
            NUMBER_PAGES_CREATED: 16961
            NUMBER_PAGES_WRITTEN: 23848
                 PAGES_READ_RATE: 0
               PAGES_CREATE_RATE: 0
              PAGES_WRITTEN_RATE: 0
                NUMBER_PAGES_GET: 4188354
                        HIT_RATE: 0
    YOUNG_MAKE_PER_THOUSAND_GETS: 0
NOT_YOUNG_MAKE_PER_THOUSAND_GETS: 0
         NUMBER_PAGES_READ_AHEAD: 768
       NUMBER_READ_AHEAD_EVICTED: 0
                 READ_AHEAD_RATE: 0
         READ_AHEAD_EVICTED_RATE: 0
                    LRU_IO_TOTAL: 0
                  LRU_IO_CURRENT: 0
                UNCOMPRESS_TOTAL: 0
              UNCOMPRESS_CURRENT: 0
*************************** 5. row ***************************
                         POOL_ID: 4
                       POOL_SIZE: 65528
                    FREE_BUFFERS: 46660
                  DATABASE_PAGES: 17729
              OLD_DATABASE_PAGES: 6564
         MODIFIED_DATABASE_PAGES: 0
              PENDING_DECOMPRESS: 4550
                   PENDING_READS: 0
               PENDING_FLUSH_LRU: 0
              PENDING_FLUSH_LIST: 0
                PAGES_MADE_YOUNG: 0
            PAGES_NOT_MADE_YOUNG: 0
           PAGES_MADE_YOUNG_RATE: 0
       PAGES_MADE_NOT_YOUNG_RATE: 0
               NUMBER_PAGES_READ: 792
            NUMBER_PAGES_CREATED: 16937
            NUMBER_PAGES_WRITTEN: 24222
                 PAGES_READ_RATE: 0
               PAGES_CREATE_RATE: 0
              PAGES_WRITTEN_RATE: 0
                NUMBER_PAGES_GET: 4314265
                        HIT_RATE: 0
    YOUNG_MAKE_PER_THOUSAND_GETS: 0
NOT_YOUNG_MAKE_PER_THOUSAND_GETS: 0
         NUMBER_PAGES_READ_AHEAD: 768
       NUMBER_READ_AHEAD_EVICTED: 0
                 READ_AHEAD_RATE: 0
         READ_AHEAD_EVICTED_RATE: 0
                    LRU_IO_TOTAL: 0
                  LRU_IO_CURRENT: 0
                UNCOMPRESS_TOTAL: 0
              UNCOMPRESS_CURRENT: 0
*************************** 6. row ***************************
                         POOL_ID: 5
                       POOL_SIZE: 65528
                    FREE_BUFFERS: 46748
                  DATABASE_PAGES: 17642
              OLD_DATABASE_PAGES: 6532
         MODIFIED_DATABASE_PAGES: 0
              PENDING_DECOMPRESS: 4548
                   PENDING_READS: 0
               PENDING_FLUSH_LRU: 0
              PENDING_FLUSH_LIST: 0
                PAGES_MADE_YOUNG: 0
            PAGES_NOT_MADE_YOUNG: 0
           PAGES_MADE_YOUNG_RATE: 0
       PAGES_MADE_NOT_YOUNG_RATE: 0
               NUMBER_PAGES_READ: 790
            NUMBER_PAGES_CREATED: 16852
            NUMBER_PAGES_WRITTEN: 23741
                 PAGES_READ_RATE: 0
               PAGES_CREATE_RATE: 0
              PAGES_WRITTEN_RATE: 0
                NUMBER_PAGES_GET: 3449085
                        HIT_RATE: 0
    YOUNG_MAKE_PER_THOUSAND_GETS: 0
NOT_YOUNG_MAKE_PER_THOUSAND_GETS: 0
         NUMBER_PAGES_READ_AHEAD: 768
       NUMBER_READ_AHEAD_EVICTED: 0
                 READ_AHEAD_RATE: 0
         READ_AHEAD_EVICTED_RATE: 0
                    LRU_IO_TOTAL: 0
                  LRU_IO_CURRENT: 0
                UNCOMPRESS_TOTAL: 0
              UNCOMPRESS_CURRENT: 0
*************************** 7. row ***************************
                         POOL_ID: 6
                       POOL_SIZE: 65528
                    FREE_BUFFERS: 46821
                  DATABASE_PAGES: 17571
              OLD_DATABASE_PAGES: 6506
         MODIFIED_DATABASE_PAGES: 0
              PENDING_DECOMPRESS: 4544
                   PENDING_READS: 0
               PENDING_FLUSH_LRU: 0
              PENDING_FLUSH_LIST: 0
                PAGES_MADE_YOUNG: 0
            PAGES_NOT_MADE_YOUNG: 0
           PAGES_MADE_YOUNG_RATE: 0
       PAGES_MADE_NOT_YOUNG_RATE: 0
               NUMBER_PAGES_READ: 727
            NUMBER_PAGES_CREATED: 16844
            NUMBER_PAGES_WRITTEN: 24005
                 PAGES_READ_RATE: 0
               PAGES_CREATE_RATE: 0
              PAGES_WRITTEN_RATE: 0
                NUMBER_PAGES_GET: 3852166
                        HIT_RATE: 0
    YOUNG_MAKE_PER_THOUSAND_GETS: 0
NOT_YOUNG_MAKE_PER_THOUSAND_GETS: 0
         NUMBER_PAGES_READ_AHEAD: 704
       NUMBER_READ_AHEAD_EVICTED: 0
                 READ_AHEAD_RATE: 0
         READ_AHEAD_EVICTED_RATE: 0
                    LRU_IO_TOTAL: 0
                  LRU_IO_CURRENT: 0
                UNCOMPRESS_TOTAL: 0
              UNCOMPRESS_CURRENT: 0
*************************** 8. row ***************************
                         POOL_ID: 7
                       POOL_SIZE: 65528
                    FREE_BUFFERS: 46651
                  DATABASE_PAGES: 17749
              OLD_DATABASE_PAGES: 6571
         MODIFIED_DATABASE_PAGES: 0
              PENDING_DECOMPRESS: 4512
                   PENDING_READS: 0
               PENDING_FLUSH_LRU: 0
              PENDING_FLUSH_LIST: 0
                PAGES_MADE_YOUNG: 0
            PAGES_NOT_MADE_YOUNG: 0
           PAGES_MADE_YOUNG_RATE: 0
       PAGES_MADE_NOT_YOUNG_RATE: 0
               NUMBER_PAGES_READ: 804
            NUMBER_PAGES_CREATED: 16945
            NUMBER_PAGES_WRITTEN: 23958
                 PAGES_READ_RATE: 0
               PAGES_CREATE_RATE: 0
              PAGES_WRITTEN_RATE: 0
                NUMBER_PAGES_GET: 3606901
                        HIT_RATE: 0
    YOUNG_MAKE_PER_THOUSAND_GETS: 0
NOT_YOUNG_MAKE_PER_THOUSAND_GETS: 0
         NUMBER_PAGES_READ_AHEAD: 768
       NUMBER_READ_AHEAD_EVICTED: 0
                 READ_AHEAD_RATE: 0
         READ_AHEAD_EVICTED_RATE: 0
                    LRU_IO_TOTAL: 0
                  LRU_IO_CURRENT: 0
                UNCOMPRESS_TOTAL: 0
              UNCOMPRESS_CURRENT: 0
8 rows in set (0.00 sec)

为了比较，SHOW ENGINE INNODB STATUS输出和INNODB缓冲池状态变量输出如下所示，基于相同的数据集。

mysql> show engine innodb status \G
......

----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 8795455488
Dictionary memory allocated 519969
Buffer pool size   524224
Free buffers       373654
Database pages     141626
Old database pages 52435
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 19074, not young 37515
0.00 youngs/s, 0.00 non-youngs/s
Pages read 6391, created 135255, written 191958
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 141626, unzip_LRU len: 35757
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size   65528
Free buffers       46471
Database pages     17936
Old database pages 6640
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 16823, not young 19769
0.00 youngs/s, 0.00 non-youngs/s
Pages read 857, created 17087, written 24505
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 17936, unzip_LRU len: 4480
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size   65528
Free buffers       47000
Database pages     17515
Old database pages 6485
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 864, created 16651, written 23771
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 17515, unzip_LRU len: 4051
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size   65528
Free buffers       46659
Database pages     17733
Old database pages 6565
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 755, created 16978, written 23908
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 17733, unzip_LRU len: 4544
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size   65528
Free buffers       46644
Database pages     17751
Old database pages 6572
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 2251, not young 17746
0.00 youngs/s, 0.00 non-youngs/s
Pages read 802, created 16961, written 23848
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 17751, unzip_LRU len: 4528
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size   65528
Free buffers       46660
Database pages     17729
Old database pages 6564
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 792, created 16937, written 24222
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 17729, unzip_LRU len: 4550
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size   65528
Free buffers       46748
Database pages     17642
Old database pages 6532
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 790, created 16852, written 23741
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 17642, unzip_LRU len: 4548
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size   65528
Free buffers       46821
Database pages     17571
Old database pages 6506
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 727, created 16844, written 24005
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 17571, unzip_LRU len: 4544
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size   65528
Free buffers       46651
Database pages     17749
Old database pages 6571
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 804, created 16945, written 23958
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 17749, unzip_LRU len: 4512
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
......


mysql> show status like 'Innodb_buffer%';
+---------------------------------------+--------------------------------------------------+
| Variable_name                         | Value                                            |
+---------------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_dump_status        | Dumping of buffer pool not started               |
| Innodb_buffer_pool_load_status        | Buffer pool(s) load completed at 230510  9:02:54 |
| Innodb_buffer_pool_resize_status      |                                                  |
| Innodb_buffer_pool_pages_data         | 141626                                           |
| Innodb_buffer_pool_bytes_data         | 2466910208                                       |
| Innodb_buffer_pool_pages_dirty        | 0                                                |
| Innodb_buffer_pool_bytes_dirty        | 0                                                |
| Innodb_buffer_pool_pages_flushed      | 154983                                           |
| Innodb_buffer_pool_pages_free         | 373654                                           |
| Innodb_buffer_pool_pages_misc         | 8944                                             |
| Innodb_buffer_pool_pages_total        | 524224                                           |
| Innodb_buffer_pool_read_ahead_rnd     | 0                                                |
| Innodb_buffer_pool_read_ahead         | 6016                                             |
| Innodb_buffer_pool_read_ahead_evicted | 0                                                |
| Innodb_buffer_pool_read_requests      | 31733470                                         |
| Innodb_buffer_pool_reads              | 376                                              |
| Innodb_buffer_pool_wait_free          | 0                                                |
| Innodb_buffer_pool_write_requests     | 15535407                                         |
+---------------------------------------+--------------------------------------------------+
18 rows in set (0.01 sec)

InnoDB INFORMATION_SCHEMA全文索引表
随着MySQL 5.6.4对InnoDB表的全文索引支持的引入，以下表被添加到INFORMATION_SCHEMA数据库中:

mysql> show tables from information_schema like 'INNODB_FT%';
+-------------------------------------------+
| Tables_in_information_schema (INNODB_FT%) |
+-------------------------------------------+
| INNODB_FT_CONFIG                          |
| INNODB_FT_BEING_DELETED                   |
| INNODB_FT_DELETED                         |
| INNODB_FT_DEFAULT_STOPWORD                |
| INNODB_FT_INDEX_TABLE                     |
| INNODB_FT_INDEX_CACHE                     |
+-------------------------------------------+
6 rows in set (0.00 sec)

.INNODB_FT_CONFIG:显示关于InnoDB表的全文索引和相关处理的元数据。

.INNODB_FT_BEING_DELETED:提供INNODB_FT_DELETED表的快照，仅在使用optimizer table执行维护时使用。当OPTIMIZE TABLE运行时，INNODB_FT_BEING_DELETED表被清空，doc_id从INNODB_FT_DELETED表中移除。因为INNODB_FT_BEING_DELETED的内容的生命周期通常很短，所以该表在监控或调试方面的用途有限。

.INNODB_FT_DELETED:从InnoDB表的全文索引中删除的记录。为了避免在DML操作中对InnoDB全文索引进行昂贵的索引重组，新删除的单词的信息被单独存储，在执行文本搜索时从搜索结果中过滤，并且只有在运行OPTIMIZE TABLE时才从主搜索索引中删除。

.INNODB_FT_DEFAULT_STOPWORD:保存在创建全文索引时默认使用的停用词列表。

.INNODB_FT_INDEX_TABLE:包含用于处理全文索引的文本搜索的倒排索引的数据。

.INNODB_FT_INDEX_CACHE:包含新插入的行在全文索引中的标记信息。为了避免DML操作过程中昂贵的索引重组，新索引词的信息被单独存储，并仅在优化表运行时、服务器关闭时或缓存大小超过innodb_ft_cache_size或innodb_ft_total_cache_size定义的限制时才与主搜索索引结合。

注意:
除了INNODB_FT_DEFAULT_STOPWORD表，你必须将innodb_ft_aux_table配置变量设置为包含全文索引的表名(database_name/table_name)。否则，InnoDB的全文索引INFORMATION_SCHEMA表显示为空。

InnoDB全文索引INFORMATION_SCHEMA表
这个例子使用一个带全文索引的表来演示全文索引INFORMATION_SCHEMA表中包含的数据。
1.创建一个包含全文索引的表并插入一些数据:

mysql> create table articles (
    -> id int unsigned auto_increment not null primary key,
    -> title varchar(200),
    -> body text,
    -> fulltext (title,body)
    -> ) engine=innodb;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into articles (title,body) values
    -> ('MySQL Tutorial','DBMS stands for DataBase ...'),
    -> ('How To Use MySQL Well','After you went through a ...'),
    -> ('Optimizing MySQL','In this tutorial we will show ...'),
    -> ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
    -> ('MySQL vs. YourSQL','In the following database comparison ...'),
    -> ('MySQL Security','When configured properly, MySQL ...');
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

2.设置innodb_ft_aux_table变量为FULLTEXT索引表的名称。如果没有设置这个变量，除了INNODB_FT_DEFAULT_STOPWORD表之外，InnoDB的FULLTEXT INFORMATION_SCHEMA表将显示为空。

mysql> set global innodb_ft_aux_table = 'test/articles';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%aux_table%';
+---------------------+---------------+
| Variable_name       | Value         |
+---------------------+---------------+
| innodb_ft_aux_table | test/articles |
+---------------------+---------------+
1 row in set (0.01 sec)

3.查询INNODB_FT_INDEX_CACHE表，它显示了在全文索引中新插入的行信息。为了避免DML操作期间昂贵的索引重组，新插入行的数据将保留在全文索引缓存中，直到优化表运行(或直到服务器关闭或超过缓存限制)。

mysql> select * from information_schema.innodb_ft_index_cache limit 5;
+------------+--------------+-------------+-----------+--------+----------+
| WORD       | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+------------+--------------+-------------+-----------+--------+----------+
| 1001       |            5 |           5 |         1 |      5 |        0 |
| after      |            3 |           3 |         1 |      3 |       22 |
| comparison |            6 |           6 |         1 |      6 |       44 |
| configured |            7 |           7 |         1 |      7 |       20 |
| database   |            2 |           6 |         2 |      2 |       31 |
+------------+--------------+-------------+-----------+--------+----------+
5 rows in set (0.00 sec)

4.启用innodb_optimize_fulltext_only，在包含全文索引的表上运行OPTIMIZE TABLE。此操作将全文索引缓存的内容刷新到主全文索引。innodb_optimize_fulltext_only改变了optimize_table语句在InnoDB表上的操作方式，并且是在对带有全文索引的InnoDB表进行维护操作时临时启用的。

mysql> SET GLOBAL innodb_optimize_fulltext_only=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'innodb_optimize_fulltext_only';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| innodb_optimize_fulltext_only | ON    |
+-------------------------------+-------+
1 row in set (0.01 sec)


mysql> optimize table articles;
+---------------+----------+----------+----------+
| Table         | Op       | Msg_type | Msg_text |
+---------------+----------+----------+----------+
| test.articles | optimize | status   | OK       |
+---------------+----------+----------+----------+
1 row in set (0.01 sec)

5.查询INNODB_FT_INDEX_TABLE表，查看主要全文索引中的数据信息，包括刚刚从全文索引缓存中刷新的数据信息。

mysql> select * from information_schema.innodb_ft_index_table limit 5;
+------------+--------------+-------------+-----------+--------+----------+
| WORD       | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+------------+--------------+-------------+-----------+--------+----------+
| 1001       |            5 |           5 |         1 |      5 |        0 |
| after      |            3 |           3 |         1 |      3 |       22 |
| comparison |            6 |           6 |         1 |      6 |       44 |
| configured |            7 |           7 |         1 |      7 |       20 |
| database   |            2 |           6 |         2 |      2 |       31 |
+------------+--------------+-------------+-----------+--------+----------+
5 rows in set (0.00 sec)

INNODB_FT_INDEX_CACHE表现在是空的，因为优化表操作刷新了全文索引缓存。

mysql> select * from information_schema.innodb_ft_index_cache limit 5;
Empty set (0.00 sec)

6.从test/articles表中删除一些记录。

mysql> delete from test.articles where id < 4;
Query OK, 3 rows affected (0.01 sec)

7.查询INNODB_FT_DELETED表。该表记录从全文索引中删除的行。为了避免DML操作期间昂贵的索引重组，新删除记录的信息被单独存储，在进行文本搜索时从搜索结果中过滤，在运行OPTIMIZE TABLE时从主搜索索引中删除。

mysql> select * from information_schema.innodb_ft_deleted;
+--------+
| DOC_ID |
+--------+
|      2 |
|      3 |
|      4 |
+--------+
3 rows in set (0.01 sec)

8.运行OPTIMIZE TABLE删除删除的记录。

mysql> optimize table articles;
+---------------+----------+----------+----------+
| Table         | Op       | Msg_type | Msg_text |
+---------------+----------+----------+----------+
| test.articles | optimize | status   | OK       |
+---------------+----------+----------+----------+
1 row in set (0.03 sec)

INNODB_FT_DELETED表现在应该是空的。

mysql> select * from information_schema.innodb_ft_deleted;
Empty set (0.00 sec)

9.查询INNODB_FT_CONFIG表。该表包含关于全文索引和相关处理的元数据:
.optimize_checkpoint_limit是optimize table运行结束的秒数。

.synced_doc_id是下一个要发布的DOC_ID。

.stopword_table_name是用户定义的停止词表的数据库/表名。如果没有用户定义的停用词表，该字段显示为空。

.use_stopword表示是否使用stopword表，stopword表在创建FULLTEXT索引时定义。

mysql> select * from information_schema.innodb_ft_config;
+---------------------------+-------+
| KEY                       | VALUE |
+---------------------------+-------+
| optimize_checkpoint_limit | 180   |
| synced_doc_id             | 8     |
| stopword_table_name       |       |
| use_stopword              | 1     |
+---------------------------+-------+
4 rows in set (0.00 sec)

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个小数空格。