MySQL中如何进行大文本存储压缩

共计 3546 个字符，预计需要花费 9 分钟才能阅读完成。

今天丸趣 TV 小编给大家分享一下 MySQL 中如何进行大文本存储压缩的相关知识点，内容详细，逻辑清晰，相信大部分人都还太了解这方面的知识，所以分享这篇文章给大家参考一下，希望大家阅读完这篇文章后有所收获，下面我们一起来了解一下吧。

存量数据分析

select
 table_name as  表名 ,
 table_rows as  记录数 ,
 truncate(data_length/1024/1024, 2) as  数据容量 (MB) ,
 truncate(index_length/1024/1024, 2) as  索引容量 (MB) ,
 truncate(DATA_FREE/1024/1024, 2) as  碎片占用 (MB) 
 information_schema.tables
where
 table_schema=${数据库名}
order by
 data_length desc, index_length desc;

相关内容介绍 innodb 引擎页数据超出 16kb 怎么办？

我们都知道 innodb 的页块默认大小为 16k，如果表中一行数据长度超出了 16k，就会出现行溢出，溢出的行是存放在另外的地方（uncompress blob page）。由于 innodb 采用聚簇索引把数据进行存放起来，即 B +Tree 结构，因此每个页块中至少有两行数据，否则就失去了 B +Tree 的意义，这样就得出一行数据最大的长度限制为 8k（大字段在数据页会存储 768 个字节数据，剩余的数据溢出到另外的页中，数据页还有 20 个字节记录溢出页的地址）

对 dynamic 格式来说，如果大对象字段（text/blob）存储数据大小小于 40 字节，那全部放在数据页，剩余的场景，数据页只保留一个 20 字节的指针指向溢出页。这种场景下，如果每个大对象字段保存的数据小于 40 个字节，也就和 varchar(40)，效果一样。

innodb-row-format-dynamic：dev.mysql.com/doc/refman/…

Linux 稀疏文件 空洞

稀疏文件（Sparse File）：稀疏文件与其他普通文件基本相同，区别在于文件中的部分数据全为 0，且这部分数据不占用磁盘空间

文件空洞：文件位移量可以大于文件的实际长度（位于文件中但未被写过的字节被设为 0），空洞是否占用磁盘空间由操作系统决定

文件空洞部分不占用磁盘空间、文件所占用的磁盘空间仍然是连续的

innodb 提供的压缩方案页面压缩

适用场景：由于数据量太大，磁盘空间不足，负载主要体现在 IO 上，而服务器的 CPU 又有比较多的余量的场景。

1）COMPRESS 页压缩

相关文档：dev.mysql.com/doc/refman/…

在 MySQL5.7 版本之前就提供的页压缩功能，在创建表时指定 ROW_FORMAT = COMPRESS，并通过 KEY_BLOCK_SIZE 设置压缩页的大小

存在设计上的缺陷，有可能会导致性能下降明显，然后其设计初衷是为了提升性能，引入了“日志即数据”的理念

对于压缩页的数据修改，并不会直接修改页本身，而是将修改日志存储在这个页中，这确实对数据的变更比较友好，不用每次修改都进行压缩 / 解压

对于数据的读取，压缩的数据是无法直接读取的，所以这种算法会在内存中保留一个解压后的 16K 的页，以供数据的读取

这就导致了一个页在缓冲池中可能会有两个版本（压缩版和非压缩版），引发一个非常严重的问题，即缓冲池中能缓存的页的数量大大的减少了，从而可能会导致数据库的性能极大的下降

2）TPC（透明页压缩）

工作原理：写入页面时，使用指定的压缩算法对页面进行压缩，压缩后写入磁盘，其中通过打孔机制从页面末尾释放空（需要操作系统支持空洞特性）

ALTER TABLE xxx COMPRESSION = ZLIB 可以启用 TPC 页压缩功能，但这只是对后续增量数据进行压缩，如果期望对整个表进行压缩，则需要执行 OPTIMIZE TABLE xxx

实现过程：一个压缩页在缓冲池中都是一个 16K 的非压缩页，只有在数据刷盘的时候，会进行一次压缩，压缩后剩余的空间会用 0x00 填满，利用文件系统的空洞特性（hole punch）对文件进行裁剪，释放 0x00 占用的稀疏空间

TPC 虽好，但它依赖操作系统的 Hole Punch 特性，且裁剪后的文件大小需要和文件系统块大小对齐（4K）。即假如压缩后的页大小是 9K，那么实际占用的空间是 12K

列压缩

MySQL 目前没有直接针对列压缩的方案，有一个曲线救国的方法，就是在业务层使用 MySQL 提供的压缩和解压函数来针对列进行压缩和解压操作。也就是如果需要对某一列做压缩，在写入时调用 COMPRESS 函数对那个列的内容进行压缩，读取的时候，使用 UNCOMPRESS 函数对压缩过的数据进行解压。

使用场景：针对表中某些列数据长度比较大的情况，一般是 varchar、text、blob、json 等数据类型

相关函数：

压缩函数：COMPRESS()

解压缩函数：UNCOMPRESS()

字符串长度函数：LENGTH()

未解压字符串长度函数：UNCOMPRESSED_LENGTH()

测试：

插入数据：insert into xxx (content) values (compress( xxx….))

读取压缩的数据：select c_id, uncompressed_length(c_content) uncompress_len, length(c_content) compress_len from xxx

为什么 innodb 提供的都是基于页面的压缩技术？

记录压缩：每次读写记录的时候，都要进行压缩或解压，过度依赖 CPU 的计算能力，性能相对会比较差

表空间压缩：压缩效率高，但要求表空间文件是静态不增长的，这对于我们大部分的场景都是不适用的

页面压缩：既能提升效率，又能在性能中取得一定的平衡

总结

对于一些性能不敏感的业务表，如日志表、监控表、告警表等，这些表只期望对存储空间进行优化，对性能的影响不是很关注，可以使用 COMPRESS 页压缩

对于一些比较核心的表，则比较推荐使用 TPC 压缩

列压缩过度依赖 CPU，性能方面会稍差，且对业务有一定的改造成本，不够灵活，需要评估影响范围，做好切换的方案。好处是可以由业务端决定哪些数据需要压缩，并控制解压操作

对页面进行压缩，在业务侧不用进行什么改动，对线上完全透明，压缩方案也非常成熟

为什么要进行数据压缩？

由于处理器和高速缓存存储器的速度提高超过了磁盘存储设备，因此很多时候工作负载都是受限于磁盘 I /O。数据压缩可以使数据占用更小的空间，可以节省磁盘 I /O、减少网络 I / O 从而提高吞吐量，虽然会牺牲部分 CPU 资源作为代价

对于 OLTP 系统，经常进行 update、delete、insert 等操作，通过压缩表能够减少存储占用和 IO 消耗

压缩其实是一种平衡，并不一定是为了提升数据库的性能，这种平衡取决于解压缩带来的收益和开销之间的一种权衡，但压缩对存储空间来说，收益无疑是很大的

简单测试 innodb 透明页压缩（TPC）测试数据 1）创建表

create table table_origin (……) comment 测试原表

create table table_compression_zlib (……) comment 测试压缩表_zlib compression = zlib

create table table_compression_lz4 (……) comment 测试压缩表_lz4 compression = lz4

2）往表中写入 10w 行测试数据压缩率

SELECT NAME, FS_BLOCK_SIZE, FILE_SIZE, ALLOCATED_SIZE
FROM information_schema.INNODB_TABLESPACES WHERE NAME like  test_compress%

FS_BLOCK_SIZE：文件系统块大小，也就是打孔使用的单位大小

FILE_SIZE：文件的表观大小，表示文件的最大大小，未压缩

ALLOCATED_SIZE：文件的实际大小，即磁盘上分配的空间量

压缩率：

zlib：1320636416/3489660928 = 37.8%

lz4：1566949376/3489660928 = 45%

耗时

循环插入 10w 条记录

原表：918275 ms

zlib：878540 ms

lz4：875259 ms

循环查询 10w 条记录

原表：332519 ms

zlib：373387 ms

lz4：343501 ms

以上就是“MySQL 中如何进行大文本存储压缩”这篇文章的所有内容，感谢各位的阅读！相信大家阅读完这篇文章都有很大的收获，丸趣 TV 小编每天都会为大家更新不同的知识，如果还想学习更多的知识，请关注丸趣 TV 行业资讯频道。