• 首页 首页 icon
  • 工具库 工具库 icon
    • IP查询 IP查询 icon
  • 内容库 内容库 icon
    • 快讯库 快讯库 icon
    • 精品库 精品库 icon
    • 问答库 问答库 icon
  • 更多 更多 icon
    • 服务条款 服务条款 icon

MySQL怎么进行大文本存储压缩

武飞扬头像
PHP中文网
帮助72

前面提到,我们有个云文档项目的快照内容是直接存储到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.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(透明页压缩)

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

  • 工作原理:写入页面时,使用指定的压缩算法对页面进行压缩,压缩后写入磁盘,其中通过打孔机制从页面末尾释放空(需要操作系统支持空洞特性)
  • 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)

参考: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_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

这篇好文章是转载于:学新通技术网

  • 版权申明: 本站部分内容来自互联网,仅供学习及演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系,请提供相关证据及您的身份证明,我们将在收到邮件后48小时内删除。
  • 本站站名: 学新通技术网
  • 本文地址: /boutique/detail/tanfefch
系列文章
更多 icon
同类精品
更多 icon
继续加载