InnoDB Monitor

╰半夏微凉° 2022-08-18 00:55 158阅读 0赞

# **1. InnoDB Monitor 类型**    #

有四类InnoDB monitor：Standard Monitor、Lock Monitor、Tablespace Monitor、Table Monitor。其中Tablespace Monitor和Table Monitor将在后续版本（MySQL5.7中移除，对应的信息可从information\_schema的表中获取）

*  **Standard Monitor：**监视活动事务持有的表锁、行锁；事务锁等待；线程信号量等待；文件IO请求；buffer pool统计信息；InnoDB主线程purge和change buffer merge活动。
 *  **Lock Monitor：**提供额外的锁信息。
 *  **Tablespace Monitor：**显示共享表空间中的文件段以及表空间数据结构配置验证。
 *  **Table Monitor：**显示内部数据字典的内容。

# 2. InnoDB Monitor 开启/关闭 #

仅在必要时开启，因为会造成性能开销，观察结束后切记关闭监控。若监控期间服务器重新启动，则监控不会自动开启，需删除原来的表并重建相关表，或者重新设置相关变量。表结构不重要，重要的是名字和需为InnoDB引擎。

## 2.1 Standard Monitor ##

开启：  
CREATE TABLE innodb\_monitor (a INT) ENGINE=INNODB;  
  
关闭：  
DROP TABLE innodb\_monitor;  
  
MySQL5.6.16后的推荐方法：  
set GLOBAL innodb\_status\_output=ON/OFF;  
关闭服务器下次重启后该变量恢复OFF默认值  
  
以上三种方法均会将监控结果输出到数据目录下的MySQL错误日志中，每隔15秒产生一次输出，而不是输出到与Server交互的mysql客户端。  
若只需在需要时将Standard监控结果输出，且是输出在mysql客户端中，则可通过：SHOW ENGINE INNODB STATUS\\G获取

## 2.2 Lock Monitor ##

开启：  
CREATE TABLE innodb\_lock\_monitor (a INT) ENGINE=INNODB;  
  
关闭：  
DROP TABLE innodb\_lock\_monitor;  
  
MySQL5.6.16后的推荐方法：  
set GLOBAL innodb\_status\_output=ON;  
set GLOBAL innodb\_status\_output\_locks=ON;  
关闭服务器下次重启后该变量恢复OFF默认值  
需要注意的是，通过该方式开启Lock Monitor必须首先set GLOBAL innodb\_status\_output=ON;而要关闭Lock Monitor仅set GLOBAL innodb\_status\_output\_locks=OFF即可，要是set GLOBAL innodb\_status\_output=OFF;也会关闭Standard监控。  
  
以上方法是将监控结果输出到数据目录的MySQL错误日志中，每隔15秒产生一次输出。  
若是通过SHOW ENGINE INNODB STATUS 仅在必要时输出Standard监控结果到交互式mysql客户端，且还要显示Lock Monitor的话，只需开启innodb\_status\_output\_locks参数即可，innodb\_status\_output开不开无所谓。

## 2.3 Tablespace Monitor ##

开启：  
CREATE TABLE innodb\_tablespace\_monitor (a INT) ENGINE=INNODB;  
  
关闭：  
DROP TABLE innodb\_tablespace\_monitor;  
  
该监控将在MySQL5.6后续版本中移除，需要从 INFORMATION\_SCHEMA查询

## 2.4 Table Monitor ##

开启：  
CREATE TABLE innodb\_table\_monitor (a INT) ENGINE=INNODB;  
  
关闭：  
DROP TABLE innodb\_table\_monitor;  
  
该监控将在MySQL5.6后续版本中移除，需要从 INFORMATION\_SCHEMA查询。

# 3. InnoDB各类监控输出结果 #

## 3.1 InnoDB Standard Monitor and Lock Monitor Output ##

mysql>set GLOBAL innodb\_status\_output\_locks=ON;  
  
一个典型结果（注意此种方式的监控输出限制在1MB，而输出到错误日志中的内容大小不受限制）：

mysql> show engine innodb status\G
    *************************** 1. row ***************************
      Type: InnoDB
      Name:
    #############################################################################################
    #时间戳、监控名称、 per-second averages所基于的秒数（当前时间与上次监控输出时间的秒数差）
    #############################################################################################
    Status:
    =====================================
    2015-11-26 15:10:40 7fcd9e04f700 INNODB MONITOR OUTPUT
    =====================================
    Per second averages calculated from the last 2056 seconds
    
    ############################################################################################
    #主后台线程的所做的工作
    ############################################################################################
    -----------------
    BACKGROUND THREAD
    -----------------
    srv_master_thread loops: 165 srv_active, 0 srv_shutdown, 5784 srv_idle
    srv_master_thread log flush and writes: 460
    
    ############################################################################################
    #线程等待信号量的情况以及线程需要在mutex或rw-lock信号量上spin或者mutex的次数。线程等待信号量
    #值越高意味着磁盘IO较高，或者InnoDB内有很多争用情况。争用源于高并发或者OS的线程调度策略问题。可通过适当设置
    #innodb_thread_concurrency 系统变量来解决问题。Spin rounds per wait一行显示了每次OS wait的spinlock round数。
    #需要注意的是，有关mutex spin waits，rounds和OS waits的信息会在MySQL5.7.8中移除，而在 SHOW ENGINE INNODB MUTEX
    输出中显示。
    ############################################################################################
    ----------
    SEMAPHORES
    ----------
    OS WAIT ARRAY INFO: reservation count 64651
    OS WAIT ARRAY INFO: signal count 77228
    Mutex spin waits 93766, rounds 597449, OS waits 2581
    RW-shared spins 100708, rounds 2316693, OS waits 49424
    RW-excl spins 18852, rounds 504283, OS waits 8663
    Spin rounds per wait: 6.37 mutex, 23.00 RW-shared, 26.75 RW-excl
    
    
    ##############################################################################################
    #最近的外键约束错误，若没有则不显示该部分。内容包含失败的语句、引用及被引用的表和失败的外键约束的相关信息
    ##############################################################################################
    ------------------------
    LATEST FOREIGN KEY ERROR
    ------------------------
    
    ##############################################################################################
    #最近一次的死锁信息，若无则不显示该部分。内容包含死锁涉及到的事务、试图执行的语句、事务持有和需要的锁、
    InnodB决定回滚的事务等信息。
    ##############################################################################################
    ------------------------
    LATEST DETECTED DEADLOCK
    ------------------------
    
    ##############################################################################################
    #当前的事务相关信息，如果这部分报锁等待则说明应用存在锁争用，该部分的输出也可用以排查死锁
    ##############################################################################################
    ------------
    TRANSACTIONS
    ------------
    Trx id counter 932478331
    Purge done for trx's n:o < 932478242 undo n:o < 0 state: running but idle
    History list length 1297
    LIST OF TRANSACTIONS FOR EACH SESSION:
    ---TRANSACTION 0, not started
    MySQL thread id 72, OS thread handle 0x7fcd9e04f700, query id 358704 localhost root init
    show engine innodb status
    ---TRANSACTION 932478196, not started
    MySQL thread id 69, OS thread handle 0x7fcd9e5e5700, query id 358152 10.232.70.77 root cleaning up
    ---TRANSACTION 932478203, not started
    MySQL thread id 70, OS thread handle 0x7fcd9e00e700, query id 358169 10.232.70.77 root cleaning up
    ---TRANSACTION 932478233, not started
    MySQL thread id 71, OS thread handle 0x7fcd9e35b700, query id 358289 10.232.70.77 root cleaning up
    ---TRANSACTION 0, not started
    MySQL thread id 65, OS thread handle 0x7fcd9dfcd700, query id 334645 10.190.137.234 root cleaning up
    ---TRANSACTION 932472722, not started
    MySQL thread id 66, OS thread handle 0x7fcd9df8c700, query id 339358 10.190.137.234 root cleaning up
    ---TRANSACTION 932478186, not started
    MySQL thread id 61, OS thread handle 0x7fcd9e563700, query id 358119 t7.accounttxn.api.dcfservice.com 10.190.137.237 root cleaning up
    ---TRANSACTION 932460023, not started
    MySQL thread id 60, OS thread handle 0x7fcd9e0d1700, query id 291235 10.190.137.234 root cleaning up
    ---TRANSACTION 0, not started
    MySQL thread id 56, OS thread handle 0x7fcd9e1d5700, query id 269842 10.190.137.234 root cleaning up
    ---TRANSACTION 932478180, not started
    MySQL thread id 54, OS thread handle 0x7fcd9e257700, query id 358106 t7.accounttxn.api.dcfservice.com 10.190.137.237 root cleaning up
    ---TRANSACTION 932451765, not started
    MySQL thread id 59, OS thread handle 0x7fcd9e112700, query id 259343 10.232.70.77 root cleaning up
    ---TRANSACTION 932478327, not started
    MySQL thread id 58, OS thread handle 0x7fcd9e153700, query id 358651 10.232.70.77 root cleaning up
    ---TRANSACTION 932454338, not started
    MySQL thread id 57, OS thread handle 0x7fcd9e194700, query id 269171 10.190.137.234 root cleaning up
    ---TRANSACTION 932475593, not started
    MySQL thread id 55, OS thread handle 0x7fcd9e216700, query id 350465 10.190.137.234 root cleaning up
    ---TRANSACTION 932478139, not started
    MySQL thread id 48, OS thread handle 0x7fcd9e6a8700, query id 358006 t7.accounttxn.api.dcfservice.com 10.190.137.237 root cleaning up
    ---TRANSACTION 932477408, not started
    MySQL thread id 42, OS thread handle 0x7fcd9ebbc700, query id 355820 10.190.137.234 root cleaning up
    ---TRANSACTION 932478260, not started
    MySQL thread id 41, OS thread handle 0x7fcd9e31a700, query id 358374 t7.accounttxn.api.dcfservice.com 10.190.137.237 root cleaning up
    ---TRANSACTION 932417383, not started
    MySQL thread id 38, OS thread handle 0x7fcd9e3dd700, query id 358703 10.190.137.234 root cleaning up
    ---TRANSACTION 932478185, not started
    MySQL thread id 37, OS thread handle 0x7fcd9e41e700, query id 358118 t7.accounttxn.api.dcfservice.com 10.190.137.237 root cleaning up
    ---TRANSACTION 932370687, not started
    MySQL thread id 35, OS thread handle 0x7fcd9e4a0700, query id 8695 10.190.137.234 root cleaning up
    ---TRANSACTION 932370593, not started
    MySQL thread id 33, OS thread handle 0x7fcd9e4e1700, query id 8249 10.190.137.234 root cleaning up
    ---TRANSACTION 0, not started
    MySQL thread id 30, OS thread handle 0x7fcd9e5a4700, query id 166346 10.190.137.234 root cleaning up
    ---TRANSACTION 932421709, not started
    MySQL thread id 26, OS thread handle 0x7fcd9e667700, query id 144410 10.190.137.234 root cleaning up
    ---TRANSACTION 932478272, not started
    MySQL thread id 24, OS thread handle 0x7fcd9e6e9700, query id 358410 t7.accounttxn.api.dcfservice.com 10.190.137.237 root cleaning up
    ---TRANSACTION 0, not started
    MySQL thread id 22, OS thread handle 0x7fcd9e76b700, query id 269893 10.190.137.234 root cleaning up
    ---TRANSACTION 932368911, not started
    MySQL thread id 23, OS thread handle 0x7fcd9e72a700, query id 1337 10.190.137.234 root cleaning up
    ---TRANSACTION 932368830, not started
    MySQL thread id 21, OS thread handle 0x7fcd9e7ac700, query id 985 10.190.137.234 root cleaning up
    ---TRANSACTION 0, not started
    MySQL thread id 20, OS thread handle 0x7fcd9e86f700, query id 1520 10.190.137.234 root cleaning up
    ---TRANSACTION 932474399, not started
    MySQL thread id 19, OS thread handle 0x7fcd9e7ed700, query id 345728 10.232.70.77 root cleaning up
    ---TRANSACTION 932476821, not started
    MySQL thread id 18, OS thread handle 0x7fcd9e82e700, query id 354071 t7.accounttxn.api.dcfservice.com 10.190.137.237 root cleaning up
    ---TRANSACTION 0, not started
    MySQL thread id 16, OS thread handle 0x7fcd9e8b0700, query id 899 10.190.137.234 root cleaning up
    ---TRANSACTION 932478329, not started
    MySQL thread id 13, OS thread handle 0x7fcd9e973700, query id 358657 10.232.70.77 root cleaning up
    ---TRANSACTION 932369105, not started
    MySQL thread id 14, OS thread handle 0x7fcd9e932700, query id 355285 t7.accounttxn.api.dcfservice.com 10.190.137.237 root cleaning up
    ---TRANSACTION 932477233, not started
    MySQL thread id 7, OS thread handle 0x7fcd9eab8700, query id 355314 t7.accounttxn.api.dcfservice.com 10.190.137.237 root cleaning up
    ---TRANSACTION 932477046, not started
    MySQL thread id 12, OS thread handle 0x7fcd9e9b4700, query id 354680 10.232.70.77 root cleaning up
    ---TRANSACTION 932478188, not started
    MySQL thread id 11, OS thread handle 0x7fcd9e9f5700, query id 358126 10.232.70.77 root cleaning up
    ---TRANSACTION 932478259, not started
    MySQL thread id 10, OS thread handle 0x7fcd9ea36700, query id 358372 t7.accounttxn.api.dcfservice.com 10.190.137.237 root cleaning up
    ---TRANSACTION 932370737, not started
    MySQL thread id 9, OS thread handle 0x7fcd9eb7b700, query id 13804 10.190.137.234 root cleaning up
    ---TRANSACTION 932427382, not started
    MySQL thread id 6, OS thread handle 0x7fcd9eaf9700, query id 358701 10.190.137.234 root cleaning up
    ---TRANSACTION 932368852, not started
    MySQL thread id 2, OS thread handle 0x7fcd9ebfd700, query id 2495 10.232.70.77 root cleaning up
    ---TRANSACTION 932478330, not started
    MySQL thread id 1, OS thread handle 0x7fcda84c5700, query id 358664 10.232.70.77 root cleaning up
    
    ##############################################################################################
    #InnoDB用于执行各类IO操作的线程相关信息，包含一个insert buffer thread、一个log thread、四个read thread、
    #四个write thread。该部分内容还包含pending IO操作的信息和关于 IO性能统计信息线程的数量由
    #innodb_read_io_threads 和 innodb_write_io_threads 参数控制
    ##############################################################################################
    --------
    FILE I/O
    --------
    I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
    I/O thread 1 state: waiting for completed aio requests (log thread)
    I/O thread 2 state: waiting for completed aio requests (read thread)
    I/O thread 3 state: waiting for completed aio requests (read thread)
    I/O thread 4 state: waiting for completed aio requests (read thread)
    I/O thread 5 state: waiting for completed aio requests (read thread)
    I/O thread 6 state: waiting for completed aio requests (write thread)
    I/O thread 7 state: waiting for completed aio requests (write thread)
    I/O thread 8 state: waiting for completed aio requests (write thread)
    I/O thread 9 state: waiting for completed aio requests (write thread)
    Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
     ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
    Pending flushes (fsync) log: 0; buffer pool: 0
    4359829 OS file reads, 5273 OS file writes, 2209 OS fsyncs
    1119.92 reads/s, 16413 avg bytes/read, 1.13 writes/s, 0.42 fsyncs/s
    
    ##############################################################################################
    #insert buffer（也称change buffer）以及adaptive hash index相关信息。
    #size：change buffer中使用的page数，等于seg size - (1 + free list len)，其中1 +代表change buffer的header page。
    #free list len：change buffer中空闲page数；seg_size:change buffer中的总page数；merges：change buffer merges总数；
    #merged operation -insert：插入的记录merge数 -delete mark：deleted记录merge数 --delete：purge记录merge数；
    #discarded operation --insert：插入合并操作取消的数量 --delete mark：删除合并操作取消的数量 --delete 清理合并操作取消的数量。
    #自适应哈希索引状态信息：Hash table size:自适应哈希索引分配的数组单元总数；自适应哈希索引分配的页数；hash searches/s: 每秒平均hash 索引查找总数
    #non-hash searches/s：每秒非hash索引查找总数
    ##############################################################################################
    -------------------------------------
    INSERT BUFFER AND ADAPTIVE HASH INDEX
    -------------------------------------
    Ibuf: size 1, free list len 3915, seg size 3917, 60 merges
    merged operations:
     insert 114, delete mark 3, delete 0
    discarded operations:
     insert 0, delete mark 0, delete 0
    Hash table size 1106381, node heap has 615 buffer(s)
    1521.00 hash searches/s, 2153.74 non-hash searches/s
    
    ##############################################################################################
    #InnoDB log相关信息。当前的log sequence number，有多少log刷到了磁盘，上一个检查点的位置，
    #以及pending write和write性能统计
    ##############################################################################################
    ---
    LOG
    ---
    Log sequence number 104178900770
    Log flushed up to   104178900770
    Pages flushed up to 104178900770
    Last checkpoint at  104178900770
    0 pending log writes, 0 pending chkp writes
    1117 log i/o's done, 0.22 log i/o's/second
    
    ##############################################################################################
    #InnoDB Buffer Pool中页的读写统计。可通过这些数据计算出当前查询的数据文件IO操作数
    ##############################################################################################
    ----------------------
    BUFFER POOL AND MEMORY
    ----------------------
    Total memory allocated 549453824; in additional pool allocated 0
    Dictionary memory allocated 726989
    Buffer pool size   32767
    Free buffers       1024
    Database pages     31128
    Old database pages 11470
    Modified db pages  0
    Pending reads 0
    Pending writes: LRU 0, flush list 0, single page 0
    Pages made young 2064783, not young 32472025
    47.93 youngs/s, 8568.98 non-youngs/s
    Pages read 4359179, created 1933, written 3574
    1119.88 reads/s, 0.47 creates/s, 0.80 writes/s
    Buffer pool hit rate 936 / 1000, young-making rate 2 / 1000 not 490 / 1000
    Pages read ahead 794.26/s, evicted without access 1.88/s, Random read ahead 0.00/s
    LRU len: 31128, unzip_LRU len: 0
    I/O sum[0]:cur[2300927], unzip sum[0]:cur[0]
    
    ##############################################################################################
    #InnoDB主线程正在做的事情，包括各类行操作的数量和性能等
    ##############################################################################################
    --------------
    ROW OPERATIONS
    --------------
    0 queries inside InnoDB, 0 queries in queue
    0 read views open inside InnoDB
    Main thread process no. 30837, id 140521232324352, state: sleeping
    Number of rows inserted 224, updated 108, deleted 2, read 39335857
    0.07 inserts/s, 0.03 updates/s, 0.00 deletes/s, 10691.56 reads/s
    ----------------------------
    END OF INNODB MONITOR OUTPUT
    ============================

## 3.2 InnoDB Tablespace Monitor ##

包含共享表空间内的文件段信息，验证表空间分配的数据结构，不包含 innodb\_file\_per\_table系统变量开启后各个独立表空间内的信息。

一个典型的InnoDB Tablespace Monitor输出结果

================================================
    151201  9:53:39 INNODB TABLESPACE MONITOR OUTPUT
    ================================================
    #################################################
    #此部分包含表空间的综合信息：
    #id：表空间ID。0表示共享表空间
    #size：表空间中的page数量
    #free limit：free list尚未初始化的最少page数量
    #free extents: free extents数量
    #not full frag extents, used pages:没有被完全填充的fragment extents量，以及这些extents中已经被分配的pages量
    #full frag extents:被完全填充的fragment extents量
    #first seg id not used：第一个没有被使用的 segment ID
    #################################################
    FILE SPACE INFO: id 0
    size 577664, free limit 571584, free extents 4
    not full frag extents 7: used pages 417, full frag extents 121
    first seg id not used 17118
    
    #######################################################
    #每个独立的段信息
    #id：段ID    space，page：tablespace号和tablespace中segment “inode” 所在的page量，0号表空间意味着共享表空间。InnoDB用innode追踪表空间内的段。
    #剩余的一些字段信息派生自innode信息。
    #res：segment分配的pages量
    #used：segment分配的页中使用的page量
    #full ext：segment分配的被完全使用的extents量
    #fragm pages：已分配给segment的初始pages量
    #free extents：segment分配的完全没有被使用的extents量
    #not full extents：segment分配的部分使用的extents量
    #pages：not full extents中使用的pages量
    #######################################################
    SEGMENT id 1 space 0; page 2; res 96 used 74; full ext 0
    fragm pages 32; free extents 0; not full extents 1: pages 42
    
    SEGMENT id 2 space 0; page 2; res 1 used 1; full ext 0
    fragm pages 1; free extents 0; not full extents 0: pages 0
    SEGMENT id 3 space 0; page 2; res 1 used 1; full ext 0
    fragm pages 1; free extents 0; not full extents 0: pages 0
    SEGMENT id 4 space 0; page 2; res 1 used 1; full ext 0
    fragm pages 1; free extents 0; not full extents 0: pages 0
    SEGMENT id 5 space 0; page 2; res 1 used 1; full ext 0
    fragm pages 1; free extents 0; not full extents 0: pages 0
    ......
    SEGMENT id 1601 space 0; page 89427; res 1 used 1; full ext 0
    fragm pages 1; free extents 0; not full extents 0: pages 0
    SEGMENT id 1602 space 0; page 89427; res 0 used 0; full ext 0
    fragm pages 0; free extents 0; not full extents 0: pages 0
    SEGMENT id 5766 space 0; page 89427; res 1 used 1; full ext 0
    fragm pages 1; free extents 0; not full extents 0: pages 0
    SEGMENT id 3657 space 0; page 89427; res 1 used 1; full ext 0
    fragm pages 1; free extents 0; not full extents 0: pages 0
    SEGMENT id 1605 space 0; page 89427; res 8 used 8; full ext 0
    fragm pages 8; free extents 0; not full extents 0: pages 0
    SEGMENT id 1606 space 0; page 89427; res 5408 used 5353; full ext 83
    fragm pages 32; free extents 0; not full extents 1: pages 9
    SEGMENT id 1607 space 0; page 89427; res 5 used 5; full ext 0
    fragm pages 5; free extents 0; not full extents 0: pages 0
    SEGMENT id 1608 space 0; page 89427; res 1184 used 1138; full ext 17
    fragm pages 32; free extents 0; not full extents 1: pages 18
    SEGMENT id 1609 space 0; page 89427; res 3 used 3; full ext 0
    fragm pages 3; free extents 0; not full extents 0: pages 0
    
    SEGMENT id 1610 space 0; page 89427; res 928 used 801; full ext 11
    fragm pages 32; free extents 0; not full extents 3: pages 65
    
    SEGMENT id 3658 space 0; page 89427; res 0 used 0; full ext 0
    fragm pages 0; free extents 0; not full extents 0: pages 0
    SEGMENT id 3659 space 0; page 89427; res 1 used 1; full ext 0
    fragm pages 1; free extents 0; not full extents 0: pages 0
    SEGMENT id 3660 space 0; page 89427; res 0 used 0; full ext 0
    fragm pages 0; free extents 0; not full extents 0: pages 0
    SEGMENT id 4164 space 0; page 89427; res 1 used 1; full ext 0
    ......
    NUMBER of file segments: 1518
    Validating tablespace
    Validation ok
    ---------------------------------------
    END OF INNODB TABLESPACE MONITOR OUTPUT
    =======================================

segment增长时，以一个单独的page开始，InnoDB每次分配一个page，可一直分配到32个pages。之后InnoDB分配整个extents每次可分配到4个extents以确保数据良好的连续性。

## 3.3 InnoDB Table Monitor ##

一个典型的 InnoDB Table Monitor 输出结果

===========================================
    151201 10:56:55 INNODB TABLE MONITOR OUTPUT
    ===========================================
    --------------------------------------
    TABLE: name SYS_FOREIGN, id 11, flags 0, columns 7, indexes 3, appr.rows 11
      COLUMNS: ID: DATA_VARCHAR prtype 524292 len 0; FOR_NAME: DATA_VARCHAR prtype 524292 len 0; REF_NAME: DATA_VARCHAR prtype 524292 len 0; N_COLS: DATA_INT len 4; DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR: DATA_SYS prtype 258 len 7;
      INDEX: name ID_IND, id 11, fields 1/6, uniq 1, type 3
       root page 302, appr.key vals 11, leaf pages 1, size pages 1
       FIELDS:  ID DB_TRX_ID DB_ROLL_PTR FOR_NAME REF_NAME N_COLS
      INDEX: name FOR_IND, id 12, fields 1/2, uniq 2, type 0
       root page 303, appr.key vals 11, leaf pages 1, size pages 1
       FIELDS:  FOR_NAME ID
      INDEX: name REF_IND, id 13, fields 1/2, uniq 2, type 0
       root page 304, appr.key vals 4, leaf pages 1, size pages 1
       FIELDS:  REF_NAME ID
    --------------------------------------
    TABLE: name SYS_FOREIGN_COLS, id 12, flags 0, columns 7, indexes 1, appr.rows 27
      COLUMNS: ID: DATA_VARCHAR prtype 524292 len 0; POS: DATA_INT len 4; FOR_COL_NAME: DATA_VARCHAR prtype 524292 len 0; REF_COL_NAME: DATA_VARCHAR prtype 524292 len 0; DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR: DATA_SYS prtype 258 len 7;
      INDEX: name ID_IND, id 14, fields 2/6, uniq 2, type 3
       root page 305, appr.key vals 27, leaf pages 1, size pages 1
       FIELDS:  ID POS DB_TRX_ID DB_ROLL_PTR FOR_COL_NAME REF_COL_NAME
    --------------------------------------
    ......
    --------------------------------------
    TABLE: name test/t_loan_document, id 286, flags 1, columns 47, indexes 7, appr.rows 21996
      COLUMNS: loan_document_id: DATA_VARMYSQL DATA_NOT_NULL len 108; contract_id: DATA_INT DATA_BINARY_TYPE len 8; applicant_contract_id: DATA_INT DATA_BINARY_TYPE len 8; buyer_id: DATA_VARMYSQL len 60; buyer_name: DATA_VARMYSQL len 150; seller_id: DATA_VARMYSQL len 60; seller_name: DATA_VARMYSQL len 150; loan_document_no: DATA_VARMYSQL len 450; loan_document_type: DATA_INT DATA_BINARY_TYPE len 1; order_content: DATA_VARMYSQL len 765; amount: DATA_FIXBINARY DATA_BINARY_TYPE len 7; buyer_cost: DATA_FIXBINARY DATA_BINARY_TYPE len 7; seller_cost: DATA_FIXBINARY DATA_BINARY_TYPE len 7; apply_amount: DATA_FIXBINARY DATA_BINARY_TYPE len 7; can_apply_amount: DATA_FIXBINARY DATA_BINARY_TYPE len 7; start_time: DATA_INT DATA_UNSIGNED DATA_BINARY_TYPE len 4; end_time: DATA_INT DATA_UNSIGNED DATA_BINARY_TYPE len 4; loan_due_date: DATA_INT DATA_UNSIGNED DATA_BINARY_TYPE len 4; ar_due_date: DATA_INT DATA_UNSIGNED DATA_BINARY_TYPE len 4; buyback_due_date: DATA_INT DATA_UNSIGNED DATA_BINARY_TYPE len 4; lending_date: DATA_INT DATA_UNSIGNED DATA_BINARY_TYPE len 4; lending_amount: DATA_FIXBINARY DATA_BINARY_TYPE len 7; write_off_amount: DATA_FIXBINARY DATA_BINARY_TYPE len 7; write_off_date: DATA_INT DATA_UNSIGNED DATA_BINARY_TYPE len 4; submit_time: DATA_INT DATA_UNSIGNED DATA_BINARY_TYPE len 4; loan_document_state: DATA_INT DATA_BINARY_TYPE len 1; state_change_time: DATA_INT DATA_UNSIGNED DATA_BINARY_TYPE len 4; attachment_count: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4; created_by: DATA_VARMYSQL len 108; create_time: DATA_INT DATA_BINARY_TYPE len 8; update_by: DATA_VARMYSQL len 108; update_time: DATA_INT DATA_BINARY_TYPE len 8; delete_flag: DATA_INT DATA_BINARY_TYPE len 1; digital_sign: DATA_VARMYSQL len 9000; is_locked: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 1; apply_date: DATA_INT DATA_UNSIGNED DATA_BINARY_TYPE len 4; loan_state: DATA_INT DATA_BINARY_TYPE len 1; loan_state_change_time: DATA_INT DATA_UNSIGNED DATA_BINARY_TYPE len 4; pay_state: DATA_INT DATA_BINARY_TYPE len 1; pay_state_change_time: DATA_INT DATA_UNSIGNED DATA_BINARY_TYPE len 4; pay_apply_time: DATA_INT DATA_UNSIGNED DATA_BINARY_TYPE len 4; pay_finish_time: DATA_INT DATA_UNSIGNED DATA_BINARY_TYPE len 4; applied_pay_amount: DATA_FIXBINARY DATA_BINARY_TYPE len 7; debit_balance: DATA_FIXBINARY DATA_BINARY_TYPE len 7; DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR: DATA_SYS prtype 258 len 7;
      INDEX: name PRIMARY, id 506, fields 1/46, uniq 1, type 3
       root page 554277, appr.key vals 21996, leaf pages 3141, size pages 3177
       FIELDS:  loan_document_id DB_TRX_ID DB_ROLL_PTR contract_id applicant_contract_id buyer_id buyer_name seller_id seller_name loan_document_no loan_document_type order_content amount buyer_cost seller_cost apply_amount can_apply_amount start_time end_time loan_due_date ar_due_date buyback_due_date lending_date lending_amount write_off_amount write_off_date submit_time loan_document_state state_change_time attachment_count created_by create_time update_by update_time delete_flag digital_sign is_locked apply_date loan_state loan_state_change_time pay_state pay_state_change_time pay_apply_time pay_finish_time applied_pay_amount debit_balance
      INDEX: name FK_LOAN_BILL_CONTRACT_1_idx, id 507, fields 1/2, uniq 2, type 0
       root page 554278, appr.key vals 1, leaf pages 53, size pages 97
       FIELDS:  contract_id loan_document_id
      INDEX: name IDX_DUE_TIME, id 508, fields 1/2, uniq 2, type 0
       root page 554279, appr.key vals 113, leaf pages 81, size pages 97
       FIELDS:  loan_due_date loan_document_id
      INDEX: name FK_LOAN_BILL_CONTRACT_2_idx, id 509, fields 1/2, uniq 2, type 0
       root page 554280, appr.key vals 1, leaf pages 59, size pages 97
       FIELDS:  applicant_contract_id loan_document_id
      INDEX: name FK_LOAN_BILL_BUYER_1_idx, id 510, fields 1/2, uniq 2, type 0
       root page 554281, appr.key vals 394, leaf pages 67, size pages 97
       FIELDS:  buyer_id loan_document_id
      INDEX: name FK_LOAN_BILL_SELLER_1_idx, id 511, fields 1/2, uniq 2, type 0
       root page 554282, appr.key vals 1, leaf pages 56, size pages 97
       FIELDS:  seller_id loan_document_id
      INDEX: name IDX_CHANGE_TIME, id 512, fields 3/4, uniq 4, type 0
       root page 554283, appr.key vals 968, leaf pages 85, size pages 97
       FIELDS:  state_change_time loan_document_state delete_flag loan_document_id
    --------------------------------------
    TABLE: name test/test, id 287, flags 1, columns 4, indexes 1, appr.rows 2
      COLUMNS: id: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4; DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR: DATA_SYS prtype 258 len 7;
      INDEX: name PRIMARY, id 513, fields 1/3, uniq 1, type 3
       root page 556243, appr.key vals 2, leaf pages 1, size pages 1
       FIELDS:  id DB_TRX_ID DB_ROLL_PTR
    -----------------------------------
    END OF INNODB TABLE MONITOR OUTPUT
    ==================================

该部分主要包含InnoDB内部数据字典信息，每个表对应一块输出。SYS\_FOREIGN和SYS\_FOREIGN\_COLS两部分为维护foreign key信息的InnoDB内部数据字典表。当然也包含Table Monitor表和用户定义的InnoDB表。  
每个表所属的输出包含表的通用信息以及表列、索引、外键的特定信息。通用信息包含库/表名，ID，列数，索引数，近似的行数。  
COLUMNS部分包含表中的每一列，包括列名、数据类型，InnoDB添加的内部列，如： DB\_ROW\_ID(row ID)、DB\_TRX\_ID(事务ID)、 DB\_ROLL\_PTR(指向rollback/undo数据的指针) 。其中：  
    DATA\_XXX：指示数据类型每个列可能包含多个DATA\_XXX分别用于指明数据类型不同的属性。  
    prtype：更为精确的数据类型，包含列数据类型、字符编码、是否可为NULL、是否为无符号数、是否为binary串等。该字段在 innobase/include/data0type.h有详尽的描述。  
    len以字节为单位的列长度。  
INDEX部分包含表索引的名称和特性信息，其中：  
    name：索引名。 PRIMARY表示主键，GEN\_CLUST\_INDEX表示未明确指定主键且没有非NULL唯一索引的情况下自动创建的聚簇索引。  
    id：索引ID。  
    fields：索引中的fields数目，其中m/n里m表示用户定义的列数，也即可以从索引定义语句中明确看到的列数。n表示索引中总的列数，可能包括内部添加的列。对于聚簇索引，总数包括索引定义中可见的聚簇索引列，外加表定义中的其他列，还包括内部添加的列。对于辅助索引，总列数包括辅助索引定义中的列，外加不属于辅助索引定义中的列的主键列）  
    uniq：足以确定索引值唯一性的最小字段数（从索引列中最左边的字段开始计）  
    type：索引类型。是一个bit字段，1表示聚簇索引、2表示唯一索引、聚簇索引（只包含唯一值）时type为3，0表示非聚簇索引非唯一索引。更详细的描述见：innobase/include/dict0mem.h  
    root page：索引树根所在的page号。  
    appr. key vals：index近似的索引筛选度。  
    leaf pages: index中近似的叶子page数。  
    size pages:index中近似的总pages数。  
    FIELDS: index中的字段。对于自动生成的聚簇索引，字段列表以内部添加的 DB\_ROW\_ID (row ID)字段开始。 DB\_TRX\_ID 和 DB\_ROLL\_PTR字段会始终包含在聚簇索引内，紧跟在组成主键的字段后。对于辅助索引最终的字段除了辅助索引中定义的字段本身外还包括属于主键但不属于辅助索引的字段。  
最后一部分是FOREIGN KEY定义信息，如果表引用了其他表或者被其他表引用了则会显示该部分信息。