参考文档：

https://www.163.com/dy/article/GI4CH5N305319P76.html

https://learn.lianglianglee.com/专栏/MySQL实战宝典/06 表压缩：不仅仅是空间压缩.md

https://blog.csdn.net/zgaoq/article/details/120522590

https://dev.mysql.com/doc/refman/5.7/en/innodb-page-compression.html

https://dev.mysql.com/doc/refman/5.7/en/innodb-compression-background.html

网上关于这方面资料很多，尤其是姜老师写的最详细，

一、压缩分类

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 值 数据文件大小
不压缩 24M
8 12M
4 6M
2 3M
1 5M

三、实验 - TPC表压缩

初始化实验环境，删除重新创建之前的 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，这个功能就失去了意义。

最新文章

  1. CSS之div和span标签
  2. Mysql基础（二）
  3. Java的四种内部类
  4. android Gui系统之SurfaceFlinger(4)---Vsync(1)
  5. DOM参考手册及事件参考手册
  6. SharePoint通过stsadm备份和还原子网站(不是网站集)
  7. [SAP ABAP开发技术总结]屏幕跳转
  8. ReactNative 大图手势浏览技术分析
  9. 【转】shell中IFS用法
  10. Heritrix源码分析(十五)
  11. Oracle DBlink的创建-查看与删除
  12. Microsoft Build 2015
  13. 小白浅论JAVA数组中“for加强版”
  14. Bootstrap中关闭第二个模态框时出现的问题和解决办法
  15. SLAM+语音机器人DIY系列：（四）差分底盘设计——1.stm32主控硬件设计
  16. Spring Boot 系列总目录
  17. 初始easyUI
  18. 转载 jQuery和js自定义函数和文件的方法（全网最全）
  19. Vue + vant-UI 打造移动商城
  20. 20155229《网络对抗技术》Exp6：信息收集与漏洞扫描

热门文章

  1. .net 移动mas短信接口开发
  2. NameError: name '_name_' is not defined
  3. PostgreSQL 绑定变量浅析
  4. 前端随笔0：URL与状态的双向绑定
  5. Java第二讲动手动脑
  6. Qt-FFmpeg开发-视频播放【软解码】（1）
  7. 一步步入门Jenkins+Net Core3.1+Gitlab，实现 CICD
  8. Jmeter 快速生成测试报告
  9. IT之软件公司组织架构
  10. VUE学习-表单输入绑定