表压缩后从磁盘占用上看要比原始表要小很多。如果你熟悉列式数据库,那对这个概念一定不陌生。比如,基于 PostgreSQL 的列式数据库 Greenplum;早期基于 MySQL 的列式数据库 inforbright;或者 Percona 的产品 tokudb 等,都是有压缩能力非常强的数据库产品。
压缩表的优点非常明显,占用磁盘空间小!由于占用空间小,从磁盘置换到内存以及之后经过网络传输都非常节省资源。
简单来讲:节省磁盘 IO,减少网络 IO。
当然压缩表也有缺点,压缩表的写入(INSERT,UPDATE,DELETE)比普通表要消耗更多的 CPU 资源。
压缩表的写入涉及到解压数据,更新数据,再压缩数据,比普通表多了解压和再压缩两个步骤,压缩和解压缩需要消耗一定的 CPU 资源。所以需要选择一个比较优化的压缩算法。
1.5 MySQL 支持的压缩算法
这块是 MySQL 所有涉及到压缩的基础,不仅仅用于压缩表,也用于其它地方。比如客户端请求到 MySQL 服务端的数据压缩;主从之间的压缩传输;利用克隆插件来复制数据库操作的压缩传输等等。
从下面结果可以看到 MySQL 支持的压缩算法为 zlib 和 zstd,MySQL 默认压缩算法为 zlib,当然你也可以选择非 zlib 算法,比如 zstd。至于哪种压缩算法最优,暂时没办法简单量化,依赖表中的数据分布或者业务请求。
select @@protocol_compression_algorithms;
基于 InnoDB 引擎的以页为单位的压缩表
这也是 MySQL 现在主推的方式。后期所有的压缩表如果没有特别说明,都指的是 InnoDB 的压缩表。
InnoDB 压缩表和 MyISAM 压缩表不同是针对页的压缩。InnoDB 不仅压缩了数据,也压缩了索引。InnoDB 页大小分别为 1K、2K、4K、8K、16K、32K、64K,默认为 16K,32K 和 64K 不支持压缩。
以上规律也就是说表压缩是针对默认 16K 大小的页的倍数递减,通过指定 key_block_size 来设置压缩表的页大小。比如 8K 的页,key_block_size=8,默认 row_format 为 compressed,或者把 row_format 设置为 compressed,即代表 key_block_size=8。
在默认单表空间下,建立一张表 t1,默认为 InnoDB 引擎,默认页大小为 8K。
3.1 对 B-tree 页面的压缩
1)每个 B-tree 页压缩表至少保存一条记录
这一点相比普通表页来说,相对灵活些,比如普通表每个页至少保留两条记录。
2)更改日志(modification log)
MySQL 为每个压缩页里设置一个 16K 大小的更改日志,用来解决对压缩表进行写入时的一系列问题。比如,页分裂或者不必要的解压和重新压缩等。
每个页面会预留空一部分空余空间来保存压缩页需要修改的行。这样做的好处是不用每次都对整个页进行解压、再更新、再压缩等步骤,节省开销。那这些行的更新放在更改日志里,当更改日志满了,就进行一次数据压缩。对应参数为:innodb_compression_pad_pct_max(默认 50,代表 50%)。如果重新压缩时失败了,那就需要进行相关页的分裂与合并,直到重新压缩成功。
举个例子:假设压缩页 1 里保存了 10 条记录,可能每分钟要轮流更新一行记录,那如果每分钟都对整个页进行解压,更新,再压缩,对 CPU 开销很大,此时可以把这些更新的行放到更改日志里,等更改日志满了,再一次性重新压缩这些记录。
3.2 压缩表和 InnoDB Buffer Pool
每个压缩页在 InnoDB Buffer Pool 里存放的是压缩页和非压缩并存的形式。
比如说,读取一张压缩表的一行记录,如果 Buffer Pool 里没有,就需要回表找到包含这行记录的压缩页(1k,2k,4k,8k),放入 Buffer Pool,同时放入包含这行的非压缩页(16K)
这么做的目的减少不必要的页解压。如果 Buffer Pool 满了,把原始页面踢出,保留压缩页;极端情形,Buffer Pool 里两者都不包含。
四、压缩表的限制
1)系统表空间不支持;
2)通用表空间不能混合存储压缩表以及原始表;
3)row_format=compressed,这种方式容易混淆成针对行的压缩,其实是针对表和相关索引的压缩。这点和其他列式存储引擎的表完全不一样;
4)临时表不支持。
https://blog.csdn.net/weixin_42366200/article/details/113121352