在上一篇《InnoDB 層全文索引字典表|全方位認識 information_schema》中，我們詳細介紹了InnoDB層的全文索引字典表，本期我們將為大家帶來系列第八篇《InnoDB 層壓縮相關字典表|全方位認識 information_schema》，下面請跟隨我們一起開始 information_schema 系統庫的系統學習之旅吧。

| INNODB_CMP和INNODB_CMP_RESET

這兩個表中的資料包含了與壓縮的InnoDB表頁有關的操作的狀態資訊。表中記錄的資料為測量資料庫中的InnoDB表壓縮的有效性提供參考。

查詢該表的使用者必須具有PROCESS許可權，該表為Memory引擎臨時表

下面是該表中儲存的資訊內容

# 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)

欄位含義如下：

PAGE_SIZE：表示壓縮的頁有效塊大小（以位元組為單位），例如：預設page size值為16K，則可壓縮的塊大小為16K、8K、4K、2K、1K，注意：這裡的塊大小指的是建表選項中指定的block size
COMPRESS_OPS：表示PAGE_SIZE欄位值大小的BTREE頁被壓縮的次數。當建立一個空白頁或者非壓縮頁的修改日誌空間用完，頁都會被壓縮
COMPRESS_OPS_OK：表示PAGE_SIZE欄位值大小的BTREE頁成功壓縮的次數。此計數不應超過COMPRESS_OPS欄位值
COMPRESS_TIME：嘗試壓縮大小為PAGE_SIZE欄位值的BTREE頁所花費的總時間（以秒為單位）
UNCOMPRESS_OPS：表示PAGE_SIZE欄位值大小的BTREE頁被解壓縮的次數。 B-tree頁面在壓縮失敗時或在首次訪問一個壓縮頁時在緩衝池中不存時被解壓
UNCOMPRESS_TIME：解壓縮PAGE_SIZE欄位值大小的BTREE頁所用的總時間（單位秒）

| INNODB_CMP_PER_INDEX和INNODB_CMP_PER_INDEX_RESET

這兩個表中記錄著InnoDB壓縮表資料和索引相關的操作狀態資訊，對資料庫、表、索引的每個組合使用不同的統計資訊，以便為評估特定表的壓縮效能和實用性提供參考資料

對於InnoDB壓縮表，會對錶中的資料和所有二級索引都進行壓縮。此時表中的資料被視為另一個索引(包含所有資料列的聚集索引）
注意：由於為每個索引收集單獨的度量值會導致效能大幅度降低，因此預設情況下不收集INNODB_CMP_PER_INDEX和INNODB_CMP_PER_INDEX_RESET表統計資訊。如果確有需要，啟用系統配置引數innodb_cmp_per_index_enabled即可（該配置引數為動態變數，預設為OFF）。
查詢該表的賬戶需要有PROCESS許可權，該表為Memory引擎臨時表

下面是該表中儲存的資訊內容

# 需要先啟用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)

欄位含義如下：

DATABASE_NAME：包含壓縮表的資料庫名稱
TABLE_NAME：被監視壓縮狀態的表名稱
INDEX_NAME：被監視壓縮狀態的索引名稱
COMPRESS_OPS：嘗試壓縮操作的次數。當建立一個空白頁或者非壓縮頁的修改日誌的空間用完時，頁都會被壓縮
COMPRESS_OPS_OK：成功壓縮操作的次數
COMPRESS_TIME：表示用於壓縮INDEX_NAME列表示的索引中的資料時的CPU開銷時間總量（以秒為單位）
UNCOMPRESS_OPS：執行解壓縮操作的次數。InnoDB頁在壓縮失敗時，或者第一次訪問的壓縮頁在緩衝池不存在時，則將進行解壓縮操作
UNCOMPRESS_TIME：表示用於解壓縮INDEX_NAME列表示的索引中的資料時的CPU開銷時間總量（以秒為單位）

| INNODB_CMPMEM和INNODB_CMPMEM_RESET

這兩個表中記錄著InnoDB緩衝池中壓縮頁上的狀態資訊，為測量資料庫中InnoDB表壓縮的有效性提供參考

查詢該表的賬戶需要有PROCESS許可權，該表為Memory引擎臨時表

下面是該表中儲存的資訊內容

# 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)

欄位含義如下：

PAGE_SIZE：表示innodb的塊大小（即表中可以使用的有效的壓縮塊大小，以位元組為單位）。該表中的每個記錄都描述了有效的可用塊大小（每個buffer pool instance在該表中都記錄了完整的可壓縮的有效塊大小值，例如：預設page size值為16K，則可壓縮的塊大小為16K、8K、4K、2K、1K，注意：這裡的塊大小指的是使用建表選項指定的block size）
BUFFER_POOL_INSTANCE：buffer pool instance例項的唯一識別符號
PAGES_USED：表示對應每行記錄中的PAGE_SIZE塊大小的塊數(頁數)
PAGES_FREE：表示每行記錄對應的PAGE_SIZE塊大小的當前可分配的塊數。此列顯示記憶體池中的外部碎片。理想情況下，該列值不應該超過1
RELOCATION_OPS：表示PAGE_SIZE列值對應的塊大小的塊被重新設定的次數。當夥伴系統試圖建立一個大塊的空閒區域時，夥伴系統可以重新分配鄰接頁釋放的空間。從表INNODB_CMPMEM_RESET中讀同名列值取重置INNODB_CMPMEM表中該列的計數
RELOCATION_TIME：重新設定PAGE_SIZE列值大小的塊大小的塊所用的總時間（以微秒為單位）。從表INNODB_CMPMEM_RESET讀取相同列值來重置INNODB_CMPMEM表中的該列計數

本期內容就介紹到這裡，本期內容參考連結如下：

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

| 作者簡介

羅小波·沃趣科技高階資料庫技術專家

IT從業多年，主要負責MySQL 產品的資料庫支撐與售後二線支撐。曾參與版本釋出系統、輕量級監控系統、運維管理平臺、資料庫管理平臺的設計與編寫，熟悉MySQL體系結構，Innodb儲存引擎，喜好專研開源技術，多次在公開場合做過線下線上資料庫專題分享，發表過多篇資料庫相關的研究文章。

InnoDB 層壓縮相關字典表 | 全方位認識 information_schema

相關文章