mysql压缩表小记 - 柴米油盐酱醋

link之家
链接快照平台
输入网页链接，自动生成快照
标签化管理网页链接
一、压缩分类

1、COMPRESS 页压缩
2、TPC 压缩
二、自建MySQL环境：

版本：5.7
建表语句：
 CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB 
填充数据：
mysql> insert into t1 select null,repeat('a',200);
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> insert into t1 select null,repeat('a',200) from t1;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0
... 重复执行
mysql> insert into t1 select null,repeat('a',200) from t1;
Query OK, 32768 rows affected (0.50 sec)
Records: 32768  Duplicates: 0  Warnings: 0
mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|    65536 |
+----------+
1 row in set (0.04 sec)
三、实验 - COMPRESS页压缩
填充的测试数据几乎都是 'a' ，这种数据应该会有很好的压缩性。
COMPRESS 压缩方式主要针对 Innodb 页进行压缩，将一个 16k (innodb 页默认大小)的页面可压缩为8k、4k、2k、1k，如果不指定 key_block_size 大小，该值默认为页的一半大小，也就是8，默认会将一个 16k 的页面压缩为 8k ，这种场景理论上最多将数据压缩为之前的一半。
操作系统查看文件大小
[root@root ceshi]# du -sh *t1*
12K     t1.frm
24M     t1.ibd
数据库系统表查看文件大小,注意 FILE_SIZE 和 ALLOCATED_SIZE 字段值是一样的
MySQL [ceshi]> select * from information_schema. INNODB_SYS_TABLESPACES where name like '%ceshi/t1%';
+-------+----------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+
| SPACE | NAME     | FLAG | FILE_FORMAT | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE |
+-------+----------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+
|   832 | ceshi/t1 |   33 | Barracuda   | Dynamic    |     16384 |             0 | Single     |          4096 |  24117248 |       24121344 |
+-------+----------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+
1 row in set (0.001 sec)
MySQL [ceshi]> select file_name,total_extents from information_schema.files where file_name like '%ceshi/t1%';
+----------------+---------------+
| file_name      | total_extents |
+----------------+---------------+
| ./ceshi/t1.ibd |            23 |
+----------------+---------------+
1 row in set (0.001 sec)
修改 row_format 值，压缩表数据页,注意，默认 key_block_size=8
MySQL [ceshi]> alter table t1 ROW_FORMAT=compressed;
Query OK, 0 rows affected (1.230 sec)
Records: 0  Duplicates: 0  Warnings: 0
MySQL [ceshi]> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=131056 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED
再次查看文件大小
[root@root ceshi]# du -sh *t1*
12K     t1.frm
12M     t1.ibd
使用 SQL 查询文件大小
MySQL [ceshi]> select * from information_schema. INNODB_SYS_TABLESPACES where name like '%ceshi/t1%';
+-------+----------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+
| SPACE | NAME     | FLAG | FILE_FORMAT | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE |
+-------+----------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+
|   833 | ceshi/t1 |   41 | Barracuda   | Compressed |     16384 |          8192 | Single     |          4096 |  11534336 |       11538432 |
+-------+----------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+
1 row in set (0.002 sec)
MySQL [ceshi]>  select file_name,total_extents from information_schema.files where file_name like '%ceshi/t1%';
+----------------+---------------+
| file_name      | total_extents |
+----------------+---------------+
| ./ceshi/t1.ibd |            11 |
+----------------+---------------+
1 row in set (0.001 sec)
从以上结果看，innodb 文件大小为之前的一半，只有 12MB 了。
修改 key_block_size 值为4、2、1 ，分别查看文件大小
MySQL [ceshi]> alter table t1 KEY_BLOCK_SIZE=4;
Query OK, 0 rows affected (1.464 sec)
Records: 0  Duplicates: 0  Warnings: 0
MySQL [ceshi]> select * from information_schema. INNODB_SYS_TABLESPACES where name like '%ceshi/t1%';
+-------+----------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+
| SPACE | NAME     | FLAG | FILE_FORMAT | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE |
+-------+----------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+
|   834 | ceshi/t1 |   39 | Barracuda   | Compressed |     16384 |          4096 | Single     |          4096 |   6291456 |        6295552 |
+-------+----------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+
1 row in set (0.001 sec)
MySQL [ceshi]>  select file_name,total_extents from information_schema.files where file_name like '%ceshi/t1%';
+----------------+---------------+
| file_name      | total_extents |
+----------------+---------------+
| ./ceshi/t1.ibd |             6 |
+----------------+---------------+
1 row in set (0.001 sec)
MySQL [ceshi]>  alter table t1 KEY_BLOCK_SIZE=2;
Query OK, 0 rows affected (1.236 sec)
Records: 0  Duplicates: 0  Warnings: 0
MySQL [ceshi]> select * from information_schema. INNODB_SYS_TABLESPACES where name like '%ceshi/t1%';
+-------+----------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+
| SPACE | NAME     | FLAG | FILE_FORMAT | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE |
+-------+----------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+
|   835 | ceshi/t1 |   37 | Barracuda   | Compressed |     16384 |          2048 | Single     |          4096 |   3145728 |        3149824 |
+-------+----------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+
1 row in set (0.001 sec)
MySQL [ceshi]>  select file_name,total_extents from information_schema.files where file_name like '%ceshi/t1%';
+----------------+---------------+
| file_name      | total_extents |
+----------------+---------------+
| ./ceshi/t1.ibd |             3 |
+----------------+---------------+
1 row in set (0.001 sec)
MySQL [ceshi]>  alter table t1 KEY_BLOCK_SIZE=1;
Query OK, 0 rows affected (2.009 sec)
Records: 0  Duplicates: 0  Warnings: 0
MySQL [ceshi]> select * from information_schema. INNODB_SYS_TABLESPACES where name like '%ceshi/t1%';
+-------+----------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+
| SPACE | NAME     | FLAG | FILE_FORMAT | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE |
+-------+----------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+
|   836 | ceshi/t1 |   35 | Barracuda   | Compressed |     16384 |          1024 | Single     |          4096 |   5242880 |        5242880 |
+-------+----------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+
1 row in set (0.001 sec)
MySQL [ceshi]>  select file_name,total_extents from information_schema.files where file_name like '%ceshi/t1%';
+----------------+---------------+
| file_name      | total_extents |
+----------------+---------------+
| ./ceshi/t1.ibd |             4 |
+----------------+---------------+
1 row in set (0.002 sec)
可见并不是 key_block_size 值越小越好。
key_block_size 值
数据文件大小
初始化实验环境，删除重新创建之前的 t1 表；

开始压缩表，并使用 optimize table 命令重建表。
MySQL [ceshi]> alter table t1 COMPRESSION='ZLIB';
Query OK, 0 rows affected (0.011 sec)
Records: 0  Duplicates: 0  Warnings: 0
MySQL [ceshi]> optimize table t1;
+----------+----------+----------+-------------------------------------------------------------------+
| Table    | Op       | Msg_type | Msg_text                                                          |
+----------+----------+----------+-------------------------------------------------------------------+
| ceshi.t1 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| ceshi.t1 | optimize | status   | OK                                                                |
+----------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.604 sec)
操作系统查看文件大小
[root@root ceshi]# du -sh *t1*
12K     t1.frm
12M     t1.ibd
SQL 查看表大小
MySQL [ceshi]>  select * from information_schema. INNODB_SYS_TABLESPACES where name like '%t1%';
+-------+-----------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+
| SPACE | NAME      | FLAG | FILE_FORMAT | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE |
+-------+-----------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+
|   830 | ceshi/t1  |   33 | Barracuda   | Dynamic    |     16384 |             0 | Single     |          4096 |  24117248 |       12066816 |
+-------+-----------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+
3 rows in set (0.001 sec)
注意看这两个字段，

FILE_SIZE：文件的表面大小，即未压缩文件大小。(ls -l 结果值)

ALLOCATED_SIZE：文件的实际大小，即磁盘上的文件大小。

从结果看，文件缩小了一半，

还有一些压缩相关参数都是默认值，还没时间去做详细测试。

innodb_compression_level
四、实验 - TPC表压缩（aliyun-rds）
参考文章开头，在云上 RDS 初始化环境。

直接看测试结果吧，
mysql> select * from information_schema. INNODB_SYS_TABLESPACES where name like '%t1%';
+-------+------------------------+------+-------------+------------+-----------+---------------+------------+---------------+------------+----------------+
| SPACE | NAME | FLAG | FILE_FORMAT | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE |
+-------+------------------------+------+-------------+------------+-----------+---------------+------------+---------------+------------+----------------+
| 140 | test/t1 | 33 | Barracuda | Dynamic | 16384 | 0 | Single | 4096 | 25165824 | 25120768 |
+-------+------------------------+------+-------------+------------+-----------+---------------+------------+---------------+------------+----------------+
FILE_SIZE 值是24M
开始表级压缩
mysql> alter table t1 COMPRESSION='ZLIB';
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> optimize table t1;
+---------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+---------+----------+----------+-------------------------------------------------------------------+
| test.t1 | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| test.t1 | optimize | status | OK |
+---------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.37 sec)
mysql> select * from information_schema. INNODB_SYS_TABLESPACES where name like '%t1%';
+-------+------------------------+------+-------------+------------+-----------+---------------+------------+---------------+------------+----------------+
| SPACE | NAME | FLAG | FILE_FORMAT | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE |
+-------+------------------------+------+-------------+------------+-----------+---------------+------------+---------------+------------+----------------+
| 142 | test/t1 | 33 | Barracuda | Dynamic | 16384 | 0 | Single | 4096 | 25165824 | 25120768 |
+-------+------------------------+------+-------------+------------+-----------+---------------+------------+---------------+------------+----------------+
从 FILE_SIZE 结果看，还是24M , 表没有任何变化，如果相同的实验，我放在自建mysql，压缩后大约是12MB。
造成这个的原因也许云上底层文件系统或存储的 block 已经是16kb，这个功能就失去了意义。