1: INNODB_SYS_VIRTUAL
表存储的是INNODB表的虚拟列的信息，当然这个还是比较简单的，我们直接通过SHOW CREATE TABLE 或者DESC TABLE就能看得到。
| Column name | Description |
| TABLE_ID | An identifier representing the table associated with the virtual column; the same value as INNODB_SYS_TABLES.TABLE_ID. |
| POS | The position value of the generated virtual column. The value is large because it encodes the column sequence number and ordinal position. The formula used to calculate the value uses a bitwise operation. The formula is ((nth virtual generated column for the InnoDB instance + 1) << 16) + the ordinal position of the generated virtual column. For example, if the first virtual generated column in theInnoDB instance is the third column of the table, the formula is (0 + 1) << 16) + 2. The first generated virtual column in the InnoDB instance is always number 0. As the third column in the table, the ordinal position of the generated virtual column is 2. Ordinal positions are counted from 0. |
| BASE_POS | The ordinal position of the columns upon which a generated virtual column is based. |
在MySQL 5.7中，支持两种Generated Column，即Virtual Generated Column和Stored Generated Column，前者只将Generated Column保存在数据字典中（表的元数据），并不会将这一列数据持久化到磁盘上；后者会将Generated Column持久化到磁盘上，而不是每次读取的时候计算所得。很明显，后者存放了可以通过已有数据计算而得的数据，需要更多的磁盘空间，与Virtual Column相比并没有优势，因此，MySQL 5.7中，不指定Generated Column的类型，默认是Virtual Column。
如果需要Stored Generated Golumn的话，可能在Virtual Generated Column上建立索引更加合适
综上，一般情况下，都使用Virtual Generated Column，这也是MySQL默认的方式
基本语法是：
<type> [ GENERATED ALWAYS ] AS ( <expression> ) [ VIRTUAL|STORED ]
[ UNIQUE [KEY] ] [ [PRIMARY] KEY ] [ NOT NULL ] [ COMMENT <text> ]
例子：
create table user(uid int auto_increment,data json,primary key(uid));
insert into user values (NULL,'{"name":"wang","address":"shenyang"}'); insert into user values (NULL,'{"name":"zhao","address":"riben"}');
alter table user add user_name varchar(20) generated always as (data->'$.name');
不过虚拟列还是要加上索引的。
2：INNODB_SYS_INDEXES
提供相关INNODB表的索引的相关信息，和SYS_INDEXES 这个表存储的信息基本是一样的，只不过后者提供的是所有存储引擎的索引信息，后者只提供INNODB表的索引信息。
看一下官方文档的解释：
| Column name | Description |
| INDEX_ID | An identifier for each index that is unique across all the databases in an instance. |
| NAME | The name of the index. Most indexes created implicitly by InnoDB have consistent names but the index names are not necessarily unique. For example, PRIMARY for a primary key index, GEN_CLUST_INDEX for the index representing a primary key when one is not specified, andID_IND, FOR_IND, and REF_IND for foreign key constraints. |
| TABLE_ID | An identifier representing the table associated with the index; the same value from INNODB_SYS_TABLES.TABLE_ID. |
| TYPE | A numeric identifier signifying the kind of index. 0 = Secondary Index, 1 = Clustered Index, 2 = Unique Index, 3 = Primary Index, 32 = Full-text Index, 64 = Spatial Index, 128 = A secondary index that includes a generated virtual column. |
| N_FIELDS | The number of columns in the index key. For the GEN_CLUST_INDEX indexes, this value is 0 because the index is created using an artificial value rather than a real table column. |
| PAGE_NO | The root page number of the index B-tree. For full-text indexes, the PAGE_NO field is unused and set to -1 (FIL_NULL) because the full-text index is laid out in several B-trees (auxiliary tables). |
| SPACE | An identifier for the tablespace where the index resides. 0 means the InnoDB system tablespace. Any other number represents a table created in file-per-table mode with a separate .ibd file. This identifier stays the same after a TRUNCATE TABLE statement. Because all indexes for a table reside in the same tablespace as the table, this value is not necessarily unique. |
| MERGE_THRESHOLD | The merge threshold value for index pages. If the amount of data in an index page falls below the MERGE_THRESHOLD value when a row is deleted or when a row is shortened by an update operation, InnoDB attempts to merge the index page with the neighboring index page. The default threshold value is 50%. The MERGE_THRESHOLD column was added to INNODB_SYS_INDEXES in MySQL 5.7.6. For more information, see Section 15.6.12, “Configuring the Merge Threshold for Index Pages”. |
3： INNODB_METRICS
提供INNODB的各种的性能指数，是对INFORMATION_SCHEMA的补充，收集的是MySQL的系统统计信息。这些统计信息都是可以手动配置打开还是关闭的。有以下参数都是可以控制的：
innodb_monitor_enable, innodb_monitor_disable, innodb_monitor_reset, innodb_monitor_reset_all,
这些都是mysql的启动参数，给个启动例子：
--innodb_monitor_enable=[counter|module|pattern|all]
| Column name | Description |
| NAME | Unique name for the counter. |
| SUBSYSTEM | The aspect of InnoDB that the metric applies to. See the list following the table for the corresponding module names to use with the SET GLOBAL syntax. |
| COUNT | Value since the counter is enabled. |
| MAX_COUNT | Maximum value since the counter is enabled. |
| MIN_COUNT | Minimum value since the counter is enabled. |
| AVG_COUNT | Average value since the counter is enabled. |
| COUNT_RESET | Counter value since it was last reset. (The _RESET fields act like the lap counter on a stopwatch: you can measure the activity during some time interval, while the cumulative figures are still available in the COUNT, MAX_COUNT, and so on fields.) |
| MAX_COUNT_RESET | Maximum counter value since it was last reset. |
| MIN_COUNT_RESET | Minimum counter value since it was last reset. |
| AVG_COUNT_RESET | Average counter value since it was last reset. |
| TIME_ENABLED | Timestamp of last start. |
| TIME_DISABLED | Timestamp of last stop. |
| TIME_ELAPSED | Elapsed time in seconds since the counter started. |
| TIME_RESET | Timestamp of last stop. |
| STATUS | Whether the counter is still running (enabled) or stopped (disabled). |
| TYPE | Whether the item is a cumulative counter, or measures the current value of some resource. |
| COMMENT | Counter description. |
3： INNODB_TEMP_TABLE_INFO
存储的是INNODB的关于元数据的相关信息，如果要优化临时表的信息的时候就要用到这个表的信息INNODB_TEMP_TABLE_INFO，这个表惠记录所有的INNODB的所有用户使用到的信息，但是只能记录在内存中和没有持久化的信息。看一下官方的解释
| Column name | Description |
| TABLE_ID | The table ID of the active temporary table. |
| NAME | The name of the active temporary table. |
| N_COLS | The number of columns in the temporary table. The number always includes three hidden columns created by InnoDB (DB_ROW_ID, DB_TRX_ID, and DB_ROLL_PTR). |
| SPACE | The tablespace identifier (a numerical value) for the tablespace in which the temporary table resides. As of MySQL 5.7.1, all non-compressedInnoDB temporary tables reside in a shared temporary tablespace, as defined by innodb_temp_data_file_path. By default the shared temporary tablespace is named ibtmp1 and located in the data directory. Compressed temporary tables reside in separate per-table tablespaces located in the temporary file directory, as defined by tmpdir. The SPACE ID is always a non-zero value and is dynamically generated on server restart. |
| PER_TABLE_SPACE | A value of TRUE indicates that the temporary table resides in a separate per-table tablespace. A value of FALSE indicates that the temporary table resides in the shared temporary tablespace. |
| IS_COMPRESSED | A value of TRUE indicates that the temporary table is compressed. |
4：INNODB_BUFFER_PAGE
这个表就比较屌了，存的是buffer里面缓冲的页数据。查询这个表会对性能产生很严重的影响，千万不要再我们自己的生产库上面执行这个语句，除非你能接受服务短暂的停顿。看一下官方的揭示
| Column name | Description |
| POOL_ID | Buffer Pool ID. An identifier to distinguish between multiple buffer pool instances. |
| BLOCK_ID | Buffer Pool Block ID. |
| SPACE | Tablespace ID. Uses the same value as in INNODB_SYS_TABLES.SPACE. |
| PAGE_NUMBER | Page number. |
| PAGE_TYPE | Page type. One of ALLOCATED (Freshly allocated page), INDEX (B-tree node), UNDO_LOG (Undo log page), INODE (Index node),IBUF_FREE_LIST (Insert buffer free list), IBUF_BITMAP (Insert buffer bitmap), SYSTEM (System page), TRX_SYSTEM (Transaction system data), FILE_SPACE_HEADER (File space header), EXTENT_DESCRIPTOR (Extent descriptor page), BLOB (Uncompressed BLOB page), COMPRESSED_BLOB (First compressed BLOB page), COMPRESSED_BLOB2 (Subsequent comp BLOB page), IBUF_INDEX (Insert buffer index), RTREE_INDEX (spatial index), UNKNOWN (unknown). |
| FLUSH_TYPE | Flush type. |
| FIX_COUNT | Number of threads using this block within the buffer pool. When zero, the block is eligible to be evicted. |
| IS_HASHED | Whether hash index has been built on this page. |
| NEWEST_MODIFICATION | Log Sequence Number of the youngest modification. |
| OLDEST_MODIFICATION | Log Sequence Number of the oldest modification. |
| ACCESS_TIME | An abstract number used to judge the first access time of the page. |
| TABLE_NAME | Name of the table the page belongs to. This column is only applicable to pages of type INDEX. |
| INDEX_NAME | Name of the index the page belongs to. It can be the name of a clustered index or a secondary index. This column is only applicable to pages of type INDEX. |
| NUMBER_RECORDS | Number of records within the page. |
| DATA_SIZE | Sum of the sizes of the records. This column is only applicable to pages of type INDEX. |
| COMPRESSED_SIZE | Compressed page size. Null for pages that are not compressed. |
| PAGE_STATE | Page state. A page with valid data has one of the following states: FILE_PAGE (buffers a page of data from a file), MEMORY (buffers a page from an in-memory object), COMPRESSED. Other possible states (managed by InnoDB) are: NULL, READY_FOR_USE, NOT_USED,REMOVE_HASH. |
| IO_FIX | Specifies whether any I/O is pending for this page: IO_NONE = no pending I/O, IO_READ = read pending, IO_WRITE = write pending. |
| IS_OLD | Specifies whether or not the block is in the sublist of old blocks in the LRU list. |
| FREE_PAGE_CLOCK | The value of the freed_page_clock counter when the block was the last placed at the head of the LRU list. Thefreed_page_clock counter tracks the number of blocks removed from the end of the LRU list. |
MySQL的内部机制是很奇怪的，当表，表数据，索引被删除的时候，其实并不会立即删除，而是保存再buffer pool里面，直到这些空间被别的新数据等需要的时候，才会被从buffer pool里面抹除。
5： INNODB_BUFFER_PAGE_LRU
这个表和的所有列是一样的，除了用LRU_POSITION 代替了BLOCK_ID 。首先我们要了解一下LRU是个什么东西。
MySQL的InnoDB引擎设置有索引及数据缓存池，其中用到的LRU算法来维持缓存的命中率
这里用到了顺序表list来作为缓冲池，每个数据节点称为block
该算法采用“中点插入法”：当插入一个新block时，移除表尾最近最少使用的block，在中点插入新block。
这个中点将链表分为两部分：
1.靠近表头的一部分，为young区，这里的block是最近使用的节点
2.靠近表尾的一部分，为old区，这里的block是最近少使用的
该算法通过链表中的block的使用热度来维持各block的位置，其中old区的block为链表满的时候移除的候选区
具体算法如下：
1.链表的3/8被设置为old区
2.中点不是链表的中间点，而是old区的表头节点，即old区与young区的相邻的那个节点
3.当读取的数据不在缓冲池里的时候，读取到的block需要插入到链表中，插入点为中点，但是插入的新节点为old区的节点，如果此时old区满了得话，移除表尾的block（LRU节点）
4.当读取old区的block时，该节点将变成“young”节点：此节点移动到young区的表头（young区的头部那里）
5.在数据库操作中，被访问的节点将移除到young的表头，这样一来，在young区中的未被访问的节点将逐渐往表尾移动，当移动过中点，将变为old区的节点。而old区的节点若被访问到将变为young节点移动到表头，而old区中的为被访问的节点依旧往表尾移动，当表满时，表尾那个block将会被淘汰掉
6：INNODB_BUFFER_POOL_STATS
表提供有关INNODB 的buffer pool相关信息，和show engine innodb status提供的信息是相同的。buffer pool里的数据是young 还是 NOT young取决于是否将数据一到头部或者尾部。young数据会被保存更久的时间，而相反的，其他not young的数据就会被擦除当新数据被写入buffer pool里面的时候。
| Column name | Description |
| POOL_ID | Buffer Pool ID. A unique identifier to distinguish between multiple buffer pool instances. |
| POOL_SIZE | The InnoDB buffer pool size in pages. |
| FREE_BUFFERS | The number of free pages in the InnoDB buffer pool |
| DATABASE_PAGES | The number of pages in the InnoDB buffer pool containing data. The number includes both dirty and clean pages. |
| OLD_DATABASE_PAGES | The number of pages in the old buffer pool sublist. |
| MODIFIED_DATABASE_PAGES | The number of modified (dirty) database pages |
| PENDING_DECOMPRESS | The number of pages pending decompression |
| PENDING_READS | The number of pending reads |
| PENDING_FLUSH_LRU | The number of pages pending flush in the LRU |
| PENDING_FLUSH_LIST | The number of pages pending flush in the flush list |
| PAGES_MADE_YOUNG | The number of pages made young |
| PAGES_NOT_MADE_YOUNG | The number of pages not made young |
| PAGES_MADE_YOUNG_RATE | The number of pages made young per second (pages made young since the last printout / time elapsed) |
| PAGES_MADE_NOT_YOUNG_RATE | The number of pages not made per second (pages not made young since the last printout / time elapsed) |
| NUMBER_PAGES_READ | The number of pages read |
| NUMBER_PAGES_CREATED | The number of pages created |
| NUMBER_PAGES_WRITTEN | The number of pages written |
| PAGES_READ_RATE | The number of pages read per second (pages read since the last printout / time elapsed) |
| PAGES_CREATE_RATE | The number of pages created per second (pages created since the last printout / time elapsed) |
| PAGES_WRITTEN_RATE | The number of pages written per second (pages written since the last printout / time elapsed) |
| NUMBER_PAGES_GET | The number of logical read requests. |
| HIT_RATE | The buffer pool hit rate |
| YOUNG_MAKE_PER_THOUSAND_GETS | The number of pages made young per thousand gets |
| NOT_YOUNG_MAKE_PER_THOUSAND_GETS | The number of pages not made young per thousand gets |
| NUMBER_PAGES_READ_AHEAD | The number of pages read ahead |
| NUMBER_READ_AHEAD_EVICTED | The number of pages read into the InnoDB buffer pool by the read-ahead background thread that were subsequently evicted without having been accessed by queries. |
| READ_AHEAD_RATE | The read ahead rate per second (pages read ahead since the last printout / time elapsed) |
| READ_AHEAD_EVICTED_RATE | The number of read ahead pages evicted without access per second (read ahead pages not accessed since the last printout / time elapsed) |
| LRU_IO_TOTAL | LRU IO total |
| LRU_IO_CURRENT | LRU IO for the current interval |
| UNCOMPRESS_TOTAL | Total number of pages decompressed |
| UNCOMPRESS_CURRENT | The number of pages decompressed in the current interval |
我们看得到PAGES_NOT_MADE_YOUNG这列的数值很大的时候说明buffer pool的命中率是很低的，这就说明系统的性能是可能有问题的，内存压力较大，可以适当调大buffer pool的大小。
要说明LRU那边解释的元文目录：http://www.cnblogs.com/iamsupercp/p/3682659.html
尊重原创。