上一篇已经解了压缩表的相关概念、索引页的影响以及简单使用。这篇主要来介绍如何观测压缩表。
一、压缩表的使用场景分类
1. SELECT 业务
这类操作不需要对压缩页进行解压，所以非常适合使用压缩表。
2. INSERT 业务
这类操作需要重新对二级索引数据页解压和以及重新压缩，不过 MySQL 对这部分操作放入 change buffer，所以频率相对来说不是很高。
3. DELETE 业务
由于 MySQL 对删除的操作是直接写标记位，然后等待定期的 PURGE 线程清理，这块也适合用压缩表。
4. UPDATE 业务
由于压缩表一般都是对字符串类的数据，比如 TEXT,VARCHAR 等压缩，所以针对这块的数据做更新，很容易就把更改日志（上篇介绍过）打满，继而造成频繁的解压和压缩操作。
总的来说压缩表适合于读密集、只读、或者极少量更新的业务场景。
二、压缩表监测
对压缩表的监控，保存在 Information_schema 内以 INNODB_CMP 开头的字典表。通过这些表可以监控到压缩表是否健康，是否需要调整压缩页，或者说是否适合用压缩表等。
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 压缩表的磁盘访问相关数据，其中 INNODB_CMP 和 INNODB_CMP_RESET 表结构相同，不同的是 INNODB_CMP 代表压缩表历史访问数据，INNODB_CMP_RESET 用来重置压缩表历史数据。比如要监控一小时内的压缩表访问数据，可以执行下面的简单步骤：
- 先采集 INNODB_CMP 相关数据；
- 过一小时再次采集表 INNODB_CMP 相关数据；
- 完后立刻访问 INNODB_CMP_RESET 表；
- 初始化表 INNODB_CMP。
相关字段含义为：
| 字段 | 含义 | 备注 |
|---|---|---|
| page_size | 代表压缩表的对应页（1K/2K/4K/8K/16K），对应压缩表的属性 key_block_size | 不同的是 page_size 单位 Byte，key_block_size 单位 KB |
| compress_ops | 代表对应 page_size 的页被压缩的次数 | 空页创建 +1；修改日志打满 +1 |
| compress_ops_ok | 代表对应 page_size 页被压缩成功的次数 | 压缩成功 +1； |
| compress_time | 代表对应 page_size 页被压缩占用的时间，单位为秒 | |
| uncompress_os | 代表对应 page_size 页被解压缩的次数 | 压缩失败+1；压缩页在innodb buffer pool 里不存在时+1 |
| uncompress_time | 代表对用 page_size 页被解压缩操作的时间 |
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 表即被重置。
相关字段含义为：
| 字段 | 含义 | 备注 |
|---|---|---|
| page_size | 对应页大小 | |
| buffer_pool_instance | innodb buffer pool 的实例 ID | |
| pages_used | 使用的页面个数 | |
| pages_free | 剩余的页面个数，代表页面碎片率 | 正常为 0，理想情况下最多为 1 |
| relocation_ops | 重新分配页面的操作次数 | |
| relocation_time | 重新分配页面的操作时间，单位为微秒 |
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 @@innodb_cmp_per_index_enabled;
+--------------------------------+
| @@innodb_cmp_per_index_enabled |
+--------------------------------+
| 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)相关字段含义为：
| 字段 | 含义 | 备注 |
|---|---|---|
| database_name | 数据库名字 | |
| table_name | 表名字 | |
| index_name | 索引名字 |
其他字段类类似以上说明。
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.0G
3949029 1.6G -rw-r----- 1 mysql mysql 1.6G 3月 31 21:18 t1.ibd
3946045 405M -rw-r----- 1 mysql mysql 404M 3月 31 21:42 t2.ibd 1、查询速度对比
SQL 1 比 SQL 2 慢，SQL 3 比 SQL 4 慢。
压缩表单独查询时优势明显。
-- SQL 1
mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 200000 |
+----------+
1 row in set (4.02 sec)
-- SQL 2
mysql> select count(*) from t2;
+----------+
| count(*) |
+----------+
| 200000 |
+----------+
1 row in set (2.69 sec)
-- SQL 3
mysql> select * from t1 where id = 100;
...
2 rows in set (6.82 sec)
-- SQL 4
mysql> select * from t1 where id = 100;
...
2 rows in set (3.60 sec)2、删除数据
重启 MySQL 实例，对压缩表 t2 进行删除与更新操作；或者清空表 INNODB_CMP 和 INNODB_CMP_PER_INDEX，也就是执行对应后缀为 _RESET 的表**
从表 t2 删除一条记录
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: 4096
compress_ops: 0
compress_ops_ok: 0
compress_time: 0
uncompress_ops: 0
uncompress_time: 0
1 row in set (0.00 sec)
mysql> select * from innodb_cmp_per_index;
Empty set (0.00 sec)3、更新少量数据
同样执行对应 _RESET 后缀表清空数据
mysql> update t2 set r1 = '200' where id = 200;
Query OK, 2 rows affected (3.41 sec)
Rows matched: 2 Changed: 2 Warnings: 0查看对应的监测表数据，compress_ops_ok/compress_ops 为 1，也很健康。
mysql> select * from innodb_cmp where page_size=4096\G
*************************** 1. row ***************************
page_size: 4096
compress_ops: 2
compress_ops_ok: 2
compress_time: 0
uncompress_ops: 0
uncompress_time: 0
1 row in set (0.01 sec)
mysql> select * from innodb_cmp_per_index\G
*************************** 1. row ***************************
database_name: ytt
table_name: t2
index_name: PRIMARY
compress_ops: 2
compress_ops_ok: 2
compress_time: 0
uncompress_ops: 0
uncompress_time: 0
1 row in set (0.00 sec)4、更新大量数据
照例执行后缀 _RESET 的表，清空两张表。
mysql> update t2 set r1 = '20000' where 1;
Query OK, 199996 rows affected (26.59 sec)
Rows matched: 199996 Changed: 199996 Warnings: 0查看对应监测表的数据，compress_ops_ok/compress_ops 比率很低，失败的操作占了一半。结果表明大量更新应该规避压缩表。
mysql> select * from innodb_cmp where page_size=4096\G
*************************** 1. row ***************************
page_size: 4096
compress_ops: 48789
compress_ops_ok: 6251
compress_time: 4
uncompress_ops: 21269
uncompress_time: 0
1 row in set (0.01 sec)
mysql> select * from innodb_cmp_per_index\G
*************************** 1. row ***************************
database_name: ytt
table_name: t2
index_name: PRIMARY
compress_ops: 48789
compress_ops_ok: 6251
compress_time: 4
uncompress_ops: 21269
uncompress_time: 0
1 row in set (0.00 sec)这篇主要介绍压缩表在各个场景下的简单监测，可以总结为：压缩表只适合应用在读密集型应用，或者少量删除或者更新的场景，其他的场景不建议用压缩表。下一篇我详细介绍表统计信息计算。
关于 MySQL 的技术内容，你们还有什么想知道的吗？赶紧留言告诉小编吧！
mysql
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用。你还可以使用@来通知其他用户。