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

冬晨酱_8884

冬晨酱_8884

2023-02-02

2863人浏览

转载

前面提到,我们有个云文档项目的快照内容是直接存储到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;

image.pngimage.png

相关内容介绍

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),空洞是否占用磁盘空间由操作系统决定
    • image.png

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

innodb提供的压缩方案

页面压缩

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

1)COMPRESS页压缩

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

  • 在MySQL5.7版本之前就提供的页压缩功能,在创建表时指定 ROW_FORMAT = COMPRESS,并通过 KEY_BLOCK_SIZE 设置压缩页的大小
  • 存在设计上的缺陷,有可能会导致性能下降明显,然后其设计初衷是为了提升性能,引入了“日志即数据”的理念
    • 对于压缩页的数据修改,并不会直接修改页本身,而是将修改日志存储在这个页中,这确实对数据的变更比较友好,不用每次修改都进行压缩/解压
      • image.png
    • 对于数据的读取,压缩的数据是无法直接读取的,所以这种算法会在内存中保留一个解压后的16K的页,以供数据的读取
      • image.png
    • 这就导致了一个页在缓冲池中可能会有两个版本(压缩版和非压缩版),引发一个非常严重的问题,即缓冲池中能缓存的页的数量大大的减少了,从而可能会导致数据库的性能极大的下降

2)TPC(透明页压缩)

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

  • 工作原理:写入页面时,使用指定的压缩算法对页面进行压缩,压缩后写入磁盘,其中通过打孔机制从页面末尾释放空(需要操作系统支持空洞特性)
  • ALTER TABLE xxx COMPRESSION = ZLIB 可以启用TPC页压缩功能,但这只是对后续增量数据进行压缩,如果期望对整个表进行压缩,则需要执行 OPTIMIZE TABLE xxx
  • 实现过程:一个压缩页在缓冲池中都是一个16K的非压缩页,只有在数据刷盘的时候,会进行一次压缩,压缩后剩余的空间会用 0x00 填满,利用文件系统的空洞特性(hole punch)对文件进行裁剪,释放 0x00 占用的稀疏空间

image.png

win可用的mysql指令skill
win可用的mysql指令skill

安装、修复、更新或打包一个基于 Python 和 PyMySQL 实现的 Windows `mysql` 命令。适用于用户希望在 PowerShell/CMD 中输入 `mysql` 时重定向到 Python 实现、需要交互式输入 MySQL 连接信息、需要同步到当前用户本地 bin 目录,或需要将该能力封装成可公开复用的 Codex skill。

下载
  • 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

image.png

为什么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%';

image.png

  • 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视频教程】

相关文章

PHP速学视频免费教程(入门到精通)
PHP速学视频免费教程(入门到精通)

PHP怎么学习?PHP怎么入门?PHP在哪学?PHP怎么学才快?不用担心,这里为大家提供了PHP速学教程(入门到精通),有需要的小伙伴保存下载就能学习啦!

下载

相关标签:

mysql

本站声明:本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn

相关专题

更多
GoFrame框架Web开发与路由中间件
GoFrame框架Web开发与路由中间件

本专题聚焦GoFrame框架的Web开发,拆解路由注册与匹配规则、请求参数获取、参数校验与中间件执行顺序,说明统一响应、JWT鉴权、文件上传与接口文档生成的落地方式,并补充优雅重启与测试实践。

2026.11.17

20

15

GoFrame框架安装与gf命令行工具
GoFrame框架安装与gf命令行工具

本专题围绕GoFrame框架的安装与工具链配置展开,讲清Go版本要求、gf命令行的安装方式、代理镜像与依赖准备,说明项目初始化、热编译、交叉编译打包的做法,并整理安装阶段的常见报错与验证步骤。

2026.11.17

60

15

GoFrame框架零基础入门
GoFrame框架零基础入门

本专题整理GoFrame框架的入门知识,从模块化设计理念、环境准备到跑通第一个Web服务,讲清目录分层、配置加载、日志与中间件的用法,并对比Gin、Go-Zero的差异,帮助Go开发者建立整体认知。

2026.11.17

60

15

SQLAlchemy 一对多关系怎么建立
SQLAlchemy 一对多关系怎么建立

SQLAlchemy 一对多关系怎么建立?本专题整理父子表外键、relationship 双向映射、关联查询、级联删除和加载优化等相关文章,帮助你理解并实现常见的 ORM 关系建模。

2026.11.17

20

10

SQLAlchemy 如何增删改查
SQLAlchemy 如何增删改查

SQLAlchemy 如何增删改查?本专题整理 ORM 新增记录、条件查询、更新和删除、Session 事务提交与批量处理等实用文章,帮助你掌握 Python 数据库操作流程。

2026.11.17

60

11

SQLAlchemy ORM 模型怎么写
SQLAlchemy ORM 模型怎么写

SQLAlchemy ORM 模型怎么写?本专题整理模型类定义、Mapped 字段映射、主键与约束、表结构创建和常见报错的相关文章,帮助你从基础配置到实际建模逐步掌握 SQLAlchemy。

2026.11.17

40

16

Go-Zero API开发使用教程
Go-Zero API开发使用教程

本专题聚焦Go-Zero的API开发,拆解.api描述文件的语法结构、路由与分组定义、参数绑定与校验规则,说明统一响应体、JWT鉴权、跨域与接口文档生成的落地方式,并给出网关聚合与接口测试的实践建议。

2026.11.12

180

15

Go-Zero安装与goctl环境配置教程
Go-Zero安装与goctl环境配置教程

本专题围绕Go-Zero的安装与工具链配置展开,讲清Go版本要求、goctl安装方式、代理镜像、protoc依赖与环境变量设置,说明多版本共存、升级回退与CI流水线安装的做法,并整理安装阶段的常见报错与验证步骤。

2026.11.12

140

15

Go-Zero零基础入门教程
Go-Zero零基础入门教程

本专题整理Go-Zero的入门知识,从框架定位、环境准备到跑通第一个API服务,讲清目录分层、配置加载、日志与中间件的基本用法,并对比Gin、Kratos的差异,帮助Go开发者建立整体认知,判断它是否适合当前项目。

2026.11.12

160

15

热门下载

更多
网站特效
/
网站源码
/
网站素材
/
前端模板

精品课程

更多
相关推荐
/
热门推荐
/
最新课程
Docker Desktop设置与维护手册
Docker Desktop设置与维护手册

共0课时 | 0人学习

LLVM官方文档
LLVM官方文档

共0课时 | 0人学习