前面提到，我们有个云文档项目的快照内容是直接存储到db的，属于大文本存储，文档快照的内容字段大部分都是kb级别，部分甚至到MB级别。目前对于数据的读取，已经进行了CDN缓存优化（静态资源缓存利器——CDN），对于数据的写入和存储还有待优化，如果可以通过一些压缩算法在大文本进行压缩存储，可以在很大程度上节省DB的存储空间，缓解DB的I/O压力。
存量数据分析
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)'from information_schema.tableswhere 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（透明页压缩）
相关文档：dev.mysql.com/doc/refman/…
工作原理：写入页面时，使用指定的压缩算法对页面进行压缩，压缩后写入磁盘，其中通过打孔机制从页面末尾释放空（需要操作系统支持空洞特性）ALTER TABLE xxx COMPRESSION = ZLIB 可以启用TPC页压缩功能，但这只是对后续增量数据进行压缩，如果期望对整个表进行压缩，则需要执行 OPTIMIZE TABLE xxx
实现过程：一个压缩页在缓冲池中都是一个16K的非压缩页，只有在数据刷盘的时候，会进行一次压缩，压缩后剩余的空间会用 0x00 填满，利用文件系统的空洞特性（hole punch）对文件进行裁剪，释放 0x00 占用的稀疏空间
Ideogram
Ideogram是一个全新的文本转图像AI绘画生成平台，擅长于生成带有文本的图像，如LOGO上的字母、数字等。
512 查看详情 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）
参考：dev.mysql.com/doc/refman/…
测试数据
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_SIZEFROM 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 mszlib：878540 mslz4：875259 ms循环查询10w条记录原表：332519 mszlib：373387 mslz4：343501 ms
【相关推荐：mysql视频教程】
以上就是MySQL中怎么进行大文本存储压缩的详细内容，更多请关注创想鸟其它相关文章！
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 chuangxiangniao@163.com 举报，一经查实，本站将立刻删除。
发布者：程序猿，转转请注明出处：https://www.chuangxiangniao.com/p/777471.html
微信扫一扫 
支付宝扫一扫 
