MySQL에서 대용량 텍스트 저장소를 압축하는 방법

앞서 언급했듯이 스냅샷 콘텐츠가 대용량 텍스트 저장소인 db에 직접 저장되는 클라우드 문서 프로젝트가 있습니다. 문서 스냅샷의 콘텐츠 필드는 대부분 kb 수준에 있으며 일부는 심지어 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를 초과하면 행 오버플로가 발생하고 오버플로된 행은 다른 위치(압축 해제 Blob 페이지)에 저장됩니다. innodb는 데이터를 저장하기 위해 클러스터링된 인덱스, 즉 B+Tree 구조를 사용하므로 각 페이지 블록에는 최소 2개의 데이터 행이 있습니다. 그렇지 않으면 B+Tree의 의미가 손실되므로 한 행의 최대 길이 제한은 다음과 같습니다. 데이터는 8k입니다(대형 필드는 데이터 페이지에 768바이트의 데이터를 저장하고 나머지 데이터는 다른 페이지로 오버플로됩니다. 데이터 페이지에도 오버플로 페이지의 주소를 기록하기 위한 20바이트가 있습니다)

• For 동적 형식, 대형 개체 필드(텍스트/BLOB)에 저장된 데이터 크기가 40바이트 미만인 경우 모든 데이터가 데이터 페이지에 배치됩니다. 나머지 시나리오에서는 데이터 페이지에 20바이트 포인터만 유지됩니다. 오버플로 페이지를 가리키고 있습니다. 이 시나리오에서 각 대형 개체 필드에 저장된 데이터가 40바이트 미만인 경우 varchar(40)과 동일한 효과를 갖습니다.
• innodb-row-format-dynamic: dev.mysql.com/doc/refman/…

Linux Sparse Files & Holes

• Sparse 파일: Sparse 파일은 기본적으로 다른 일반 파일과 동일합니다. 파일의 일부 데이터가 모두 0이고 데이터의 이 부분은 디스크 공간을 차지하지 않습니다.
• 파일 구멍: 파일 변위는 파일의 실제 길이(파일에 있지만 바이트 수)보다 클 수 있습니다. 기록되지 않은 경우 0)으로 설정되며, 홀이 디스크 공간을 차지하는지 여부는 운영 체제에 의해 결정됩니다
  • 

파일의 홀 부분은 디스크 공간을 차지하지 않으며, 파일이 차지하는 디스크 공간은 여전히 연속적입니다

innodb에서 제공하는 압축 방식

페이지 압축

이 적용 가능합니다. 시나리오: 데이터 양이 많고 디스크 공간이 부족하여 부하가 주로 IO에 반영되고, 서버의 CPU 여유가 상대적으로 큽니다. .

1) COMPRESS 페이지 압축

관련 문서: dev.mysql.com/doc/refman/…

• MySQL 버전 5.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

ALTER TABLE xxx COMPRESSION = ZLIB TPC 페이지 압축 기능을 활성화할 수 있지만 이는 예상되는 경우 후속 증분 데이터만 압축합니다. 전체 테이블을 압축하려면 OPTIMIZE TABLE xxx

🎜🎜를 실행해야 합니다. 구현 프로세스: 압축된 페이지는 버퍼 풀에 있는 16K의 압축되지 않은 페이지로, 데이터가 플러시되는 경우에만 압축됩니다. 압축 후 남은 공간은 0x00으로 채워지며 파일 시스템의 홀 펀치 기능을 사용하여 파일을 잘라내어 0x00🎜🎜🎜🎜🎜🎜🎜TPC는 좋지만 구멍에 의존합니다. 운영 체제의 펀치 기능과 잘린 파일 크기에는 파일 시스템 블록 크기(4K)에 맞춰 정렬이 필요합니다. 즉, 압축된 페이지 크기가 9K이면 실제 차지하는 공간은 12K입니다.🎜🎜

열 압축

🎜MySQL에는 현재 열 압축을 위한 직접적인 솔루션이 없습니다. 하나가 있습니다. 나라를 구하는 방법은 비즈니스 계층에서 MySQL이 제공하는 압축 및 압축 풀기 기능을 사용하여 열에 대한 압축 및 압축 풀기 작업을 수행하는 것입니다. 즉, 특정 열을 압축해야 하는 경우 쓰기 시 COMPRESS 함수를 호출하여 해당 열의 내용을 압축하고, 압축된 내용을 압축하려면 UNCOMPRESS 함수를 사용하세요. 읽을 때 데이터가 압축 해제됩니다. 🎜🎜🎜사용 시나리오: 테이블에 있는 일부 열의 데이터 길이가 상대적으로 긴 상황(보통 varchar, text, blob, json 및 기타 데이터 유형)의 경우🎜🎜관련 함수: 🎜🎜압축 함수: COMPRESS( )🎜🎜압축 해제 함수: UNCOMPRESS()🎜🎜문자열 길이 함수: LENGTH()🎜🎜압축 해제된 문자열 길이 함수: UNCOMPRESSED_LENGTH()🎜🎜🎜🎜테스트: 🎜🎜데이터 삽입: <code>xxx(콘텐츠) 값에 삽입 ​​(compress('xxx....'))🎜🎜🎜압축된 데이터 읽기: xxx에서 c_id, uncompressed_length(c_content) uncompress_len, length(c_content) 압축_len 선택🎜🎜🎜🎜🎜🎜🎜🎜

为什么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

【相关推荐：mysql视频教程】

위 내용은 MySQL에서 대용량 텍스트 저장소를 압축하는 방법의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!