上一篇（第11期：压缩表）已经了解了压缩表的相关概念、索引页的影响以及简单使用。这篇主要来介绍如何观测压缩表。
一、压缩表的使用场景分类
2. INSERT 业务
3. DELETE 业务
4. UPDATE 业务
二、压缩表监测
mysql> show tables from information_schema like '%cmp%';+--------------------------------------+| Tables_in_information_schema (%CMP%) |+--------------------------------------+| INNODB_CMP || INNODB_CMPMEM || INNODB_CMPMEM_RESET || INNODB_CMP_PER_INDEX || INNODB_CMP_PER_INDEX_RESET || INNODB_CMP_RESET |+--------------------------------------+6 rows in set (0.01 sec)
这些表为内存表，也就是 memory 引擎。对这些表的检索必须具有 process 权限。按照压缩表的操作方式，分为以下三类：
1. INNODB_CMP/INNODB_CMP_RESET
先采集 INNODB_CMP 相关数据；
过一小时再次采集表 INNODB_CMP 相关数据；
完后立刻访问 INNODB_CMP_RESET 表；
初始化表 INNODB_CMP。
相关字段含义为：
mysql> desc information_schema.innodb_cmp;+-----------------+------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-----------------+------+------+-----+---------+-------+| page_size | int | NO | | | || compress_ops | int | NO | | | || compress_ops_ok | int | NO | | | || compress_time | int | NO | | | || uncompress_ops | int | NO | | | || uncompress_time | int | NO | | | |+-----------------+------+------+-----+---------+-------+6 rows in set (0.00 sec)
注意：这两个值的比率（compress_ops_ok/compress_ops）是最直观的数据，可以判断压缩表的健康与否；正常情况下，比率为 0 或者 1 或接近于 1；如果比率长时间不正常，就得考虑压缩表的页大小是否合适或者说压缩表是否应该在这种场景下使用。
2. INNODB_CMPMEM /INNODB_CMPMEM_RESET
这两张表代表在 innodb_buffer_pool 里的压缩表相关访问数据，INNODB_CMPMEM 代表历史数据；INNODB_CMPMEM_RESET 代表当前瞬时数据，只要访问一次，INNODB_CMPMEM 表即被重置。
相关字段含义为：
mysql> desc information_schema.innodb_cmpmem;+----------------------+--------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------------------+--------+------+-----+---------+-------+| page_size | int | NO | | | || buffer_pool_instance | int | NO | | | || pages_used | int | NO | | | || pages_free | int | NO | | | || relocation_ops | bigint | NO | | | || relocation_time | int | NO | | | |+----------------------+--------+------+-----+---------+-------+6 rows in set (0.00 sec)
3. INNODB_CMP_PER_INDEX/INNODB_CMP_PER_INDEX_RESET
_RESET 为历史数据，带 _RESET 为瞬时数据。和前两类表不一样，这类表是针对索引的操作记录数据，开销很大，默认不开启。相关参数为：mysql> select @;+--------------------------------+| @ |+--------------------------------+| 0 |+--------------------------------+1 row in set (0.00 sec)mysql> set persist innodb_cmp_per_index_enabled = 1;Query OK, 0 rows affected (0.01 sec)
相关字段含义为：
mysql> desc information_schema.innodb_cmp_per_index;+-----------------+--------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-----------------+--------------+------+-----+---------+-------+| database_name | varchar(192) | NO | | | || table_name | varchar(192) | NO | | | || index_name | varchar(192) | NO | | | || compress_ops | int | NO | | | || compress_ops_ok | int | NO | | | || compress_time | int | NO | | | || uncompress_ops | int | NO | | | || uncompress_time | int | NO | | | |+-----------------+--------------+------+-----+---------+-------+8 rows in set (0.01 sec)
压缩表监测的实际用例
接下来看看压缩表的监测的实际用例，使用单表空间建立两张表：
t1 ：未压缩表
t2 ：page 为 4K 的压缩表
执行建表
mysql> create table t1(id int, r1 text,r2 text,primary key (id)) row_format=dynamic;Query OK, 0 rows affected (2.35 sec)mysql> create table t2 (id int, r1 text,r2 text, primary key (id)) key_block_size=4;Query OK, 0 rows affected (0.06 sec)
root@ytt-pc:/var/lib/mysql/3305/ytt# ls -shil总用量 2.0G3949029 1.6G -rw-r----- 1 mysql mysql 1.6G 3月 31 21:18 t1.ibd3946045 405M -rw-r----- 1 mysql mysql 404M 3月 31 21:42 t2.ibd
1. 查询速度对比
-- SQL 1mysql> select count(*) from t1;+----------+| count(*) |+----------+| 200000 |+----------+1 row in set (4.02 sec)-- SQL 2mysql> select count(*) from t2;+----------+| count(*) |+----------+| 200000 |+----------+1 row in set (2.69 sec)-- SQL 3mysql> select * from t1 where id = 100;...2 rows in set (6.82 sec)-- SQL 4mysql> select * from t1 where id = 100;...2 rows in set (3.60 sec)
_RESET 的表。mysql> delete from t2 where id = 999999;Query OK, 6 rows affected (3.41 sec)
对应的 compress_ops/compress_ops_ok 为 0
表 INNODB_CMP_PER_INDEX 无数据，因为没有重建索引。可以看出 DELETE 操作对于压缩表很适合。
mysql> select * from innodb_cmp where page_size=4096\G*************************** 1. row ***************************page_size: 4096compress_ops: 0compress_ops_ok: 0compress_time: 0uncompress_ops: 0uncompress_time: 01 row in set (0.00 sec)mysql> select * from innodb_cmp_per_index;Empty set (0.00 sec)
3. 更新少量数据
_RESET 后缀表清空数据update t2 set r1 = '200' where id = 200;Query OK, 2 rows affected (3.41 sec)Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from innodb_cmp where page_size=4096\G*************************** 1. row ***************************page_size: 4096compress_ops: 2compress_ops_ok: 2compress_time: 0uncompress_ops: 0uncompress_time: 01 row in set (0.01 sec)mysql> select * from innodb_cmp_per_index\G*************************** 1. row ***************************database_name: ytttable_name: t2index_name: PRIMARYcompress_ops: 2compress_ops_ok: 2compress_time: 0uncompress_ops: 0uncompress_time: 01 row in set (0.00 sec)
_RESET 的表，清空两张表。update t2 set r1 = '20000' where 1;Query OK, 199996 rows affected (26.59 sec)Rows matched: 199996 Changed: 199996 Warnings: 0
mysql> select * from innodb_cmp where page_size=4096\G*************************** 1. row ***************************page_size: 4096compress_ops: 48789compress_ops_ok: 6251compress_time: 4uncompress_ops: 21269uncompress_time: 01 row in set (0.01 sec)mysql> select * from innodb_cmp_per_index\G*************************** 1. row ***************************database_name: ytttable_name: t2index_name: PRIMARYcompress_ops: 48789compress_ops_ok: 6251compress_time: 4uncompress_ops: 21269uncompress_time: 01 row in set (0.00 sec)
下一篇我详细介绍表统计信息计算。
关于 MySQL 的技术内容，你们还有什么想知道的吗？赶紧留言告诉小编吧！
