在上一篇《InnoDB 层全文索引字典表|全方位认识 information_schema》中,我们详细介绍了InnoDB层的全文索引字典表,本期我们将为大家带来系列第八篇《InnoDB 层压缩相关字典表|全方位认识 information_schema》,下面请跟随我们一起开始 information_schema 系统库的系统学习之旅吧。
| INNODB_CMP和INNODB_CMP_RESET
这两个表中的数据包含了与压缩的InnoDB表页有关的操作的状态信息。表中记录的数据为测量数据库中的InnoDB表压缩的有效性提供参考。
下面是该表中存储的信息内容
# INNODB_CMP表
root@localhost : information_schema 11:02:28> select * from INNODB_CMP;
+-----------+--------------+-----------------+---------------+----------------+-----------------+
| page_size | compress_ops | compress_ops_ok | compress_time | uncompress_ops | uncompress_time |
+-----------+--------------+-----------------+---------------+----------------+-----------------+
| 1024 | 0 | 0 | 0 | 0 | 0 |
| 2048 | 0 | 0 | 0 | 0 | 0 |
| 4096 | 0 | 0 | 0 | 0 | 0 |
| 8192 | 0 | 0 | 0 | 0 | 0 |
| 16384 | 0 | 0 | 0 | 0 | 0 |
+-----------+--------------+-----------------+---------------+----------------+-----------------+
5 rows in set (0.00 sec)
# INNODB_CMP_RESET表
root@localhost : information_schema 11:33:00> select * from INNODB_CMP_RESET;
+-----------+--------------+-----------------+---------------+----------------+-----------------+
| page_size | compress_ops | compress_ops_ok | compress_time | uncompress_ops | uncompress_time |
+-----------+--------------+-----------------+---------------+----------------+-----------------+
| 1024 | 0 | 0 | 0 | 0 | 0 |
| 2048 | 0 | 0 | 0 | 0 | 0 |
| 4096 | 0 | 0 | 0 | 0 | 0 |
| 8192 | 0 | 0 | 0 | 0 | 0 |
| 16384 | 0 | 0 | 0 | 0 | 0 |
+-----------+--------------+-----------------+---------------+----------------+-----------------+
5 rows in set (0.00 sec)
字段含义如下:
| INNODB_CMP_PER_INDEX和INNODB_CMP_PER_INDEX_RESET
这两个表中记录着InnoDB压缩表数据和索引相关的操作状态信息,对数据库、表、索引的每个组合使用不同的统计信息,以便为评估特定表的压缩性能和实用性提供参考数据
下面是该表中存储的信息内容
# 需要先启用innodb_cmp_per_index_enabled系统参数为ON,然后在innodb表中使用建表选项key_block_size指定一个小于默认的page size的块大小时才会有数据
## INNODB_CMP_PER_INDEX表
root@localhost : test 12:38:08> select * from information_schema.INNODB_CMP_PER_INDEX;
+---------------+------------+------------+--------------+-----------------+---------------+----------------+-----------------+
| database_name | table_name | index_name | compress_ops | compress_ops_ok | compress_time | uncompress_ops | uncompress_time |
+---------------+------------+------------+--------------+-----------------+---------------+----------------+-----------------+
| test | test | i_id | 4 | 4 | 0 | 0 | 0 |
+---------------+------------+------------+--------------+-----------------+---------------+----------------+-----------------+
1 row in set (0.00 sec)
## INNODB_CMP_PER_INDEX_RESET表
root@localhost : test 12:38:11> select * from information_schema.INNODB_CMP_PER_INDEX_RESET;
+---------------+------------+------------+--------------+-----------------+---------------+----------------+-----------------+
| database_name | table_name | index_name | compress_ops | compress_ops_ok | compress_time | uncompress_ops | uncompress_time |
+---------------+------------+------------+--------------+-----------------+---------------+----------------+-----------------+
| test | test | i_id | 4 | 4 | 0 | 0 | 0 |
+---------------+------------+------------+--------------+-----------------+---------------+----------------+-----------------+
1 row in set (0.00 sec)
字段含义如下:
| INNODB_CMPMEM和INNODB_CMPMEM_RESET
这两个表中记录着InnoDB缓冲池中压缩页上的状态信息,为测量数据库中InnoDB表压缩的有效性提供参考
下面是该表中存储的信息内容
# INNODB_CMPMEM表
root@localhost : information_schema 11:33:15> select * from INNODB_CMPMEM;
+-----------+----------------------+------------+------------+----------------+-----------------+
| page_size | buffer_pool_instance | pages_used | pages_free | relocation_ops | relocation_time |
+-----------+----------------------+------------+------------+----------------+-----------------+
| 1024 | 0 | 0 | 0 | 0 | 0 |
| 2048 | 0 | 0 | 0 | 0 | 0 |
| 4096 | 0 | 0 | 0 | 0 | 0 |
| 8192 | 0 | 0 | 0 | 0 | 0 |
| 16384 | 0 | 0 | 0 | 0 | 0 |
| 1024 | 1 | 0 | 0 | 0 | 0 |
| 2048 | 1 | 0 | 0 | 0 | 0 |
| 4096 | 1 | 0 | 0 | 0 | 0 |
| 8192 | 1 | 0 | 0 | 0 | 0 |
| 16384 | 1 | 0 | 0 | 0 | 0 |
| 1024 | 2 | 0 | 0 | 0 | 0 |
| 2048 | 2 | 0 | 0 | 0 | 0 |
| 4096 | 2 | 0 | 0 | 0 | 0 |
| 8192 | 2 | 0 | 0 | 0 | 0 |
| 16384 | 2 | 0 | 0 | 0 | 0 |
| 1024 | 3 | 0 | 0 | 0 | 0 |
| 2048 | 3 | 0 | 0 | 0 | 0 |
| 4096 | 3 | 0 | 0 | 0 | 0 |
| 8192 | 3 | 0 | 0 | 0 | 0 |
| 16384 | 3 | 0 | 0 | 0 | 0 |
+-----------+----------------------+------------+------------+----------------+-----------------+
20 rows in set (0.00 sec)
# INNODB_CMPMEM_RESET表
root@localhost : information_schema 11:44:01> select * from INNODB_CMPMEM_RESET;
+-----------+----------------------+------------+------------+----------------+-----------------+
| page_size | buffer_pool_instance | pages_used | pages_free | relocation_ops | relocation_time |
+-----------+----------------------+------------+------------+----------------+-----------------+
| 1024 | 0 | 0 | 0 | 0 | 0 |
| 2048 | 0 | 0 | 0 | 0 | 0 |
| 4096 | 0 | 0 | 0 | 0 | 0 |
| 8192 | 0 | 0 | 0 | 0 | 0 |
| 16384 | 0 | 0 | 0 | 0 | 0 |
| 1024 | 1 | 0 | 0 | 0 | 0 |
| 2048 | 1 | 0 | 0 | 0 | 0 |
| 4096 | 1 | 0 | 0 | 0 | 0 |
| 8192 | 1 | 0 | 0 | 0 | 0 |
| 16384 | 1 | 0 | 0 | 0 | 0 |
| 1024 | 2 | 0 | 0 | 0 | 0 |
| 2048 | 2 | 0 | 0 | 0 | 0 |
| 4096 | 2 | 0 | 0 | 0 | 0 |
| 8192 | 2 | 0 | 0 | 0 | 0 |
| 16384 | 2 | 0 | 0 | 0 | 0 |
| 1024 | 3 | 0 | 0 | 0 | 0 |
| 2048 | 3 | 0 | 0 | 0 | 0 |
| 4096 | 3 | 0 | 0 | 0 | 0 |
| 8192 | 3 | 0 | 0 | 0 | 0 |
| 16384 | 3 | 0 | 0 | 0 | 0 |
+-----------+----------------------+------------+------------+----------------+-----------------+
20 rows in set (0.00 sec)
字段含义如下:
本期内容就介绍到这里,本期内容参考链接如下:
https://dev.mysql.com/doc/refman/5.7/en/innodb-cmp-table.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-cmp-per-index-table.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-cmpmem-table.html
| 作者简介
《千金良方——MySQL性能优化金字塔法则》、《数据生态:MySQL复制技术与生产实践》作者之一。熟悉MySQL体系结构,擅长数据库的整体调优,喜好专研开源技术,并热衷于开源技术的推广,在线上线下做过多次公开的数据库专题分享,发表过近100篇数据库相关的研究文章。
全文完。