MySQL で大きなテキスト ストレージを圧縮する方法

前述したように、スナップショット コンテンツが大規模なテキスト ストレージである 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 を超えると、行のオーバーフローが発生し、オーバーフローした行は別の場所 (非圧縮) に格納されます。 BLOB ページ)。 innodb はクラスター化インデックスを使用してデータを格納するため、つまり B ツリー構造を使用するため、各ページ ブロックには少なくとも 2 行のデータが存在します。そうでないと B ツリーの意味が失われるため、データ行の最大長は制限されています。 ～ 8k (大きなフィールドはデータ ページに 768 バイトのデータを格納し、残りのデータは別のページにオーバーフローします。データ ページには、オーバーフロー ページのアドレスを記録するための 20 バイトもあります)

• 動的フォーマットの場合 たとえば、ラージ オブジェクト フィールド (テキスト/ブロブ) に格納されているデータのサイズが 40 バイト未満の場合、そのすべてがデータ ページに配置されます。残りのシナリオでは、データ ページにはデータのみが保持されます。オーバーフロー ページを指す 20 バイトのポインター。このシナリオでは、各ラージ オブジェクト フィールドに格納されているデータが 40 バイト未満の場合、varchar(40) と同じ効果があります。
• innodb-row-format-dynamic：dev.mysql.com/doc/refman/…

Linux スパース ファイルとホール

• スパース ファイル: スパース ファイルは、基本的に他の通常のファイルと同じです。違いは、ファイル内の一部のデータがすべて 0 であり、データのこの部分はディスク領域を占有しないことです。
• ファイル ホール: ファイル ディスプレイスメント この量は、ファイルの実際の長さよりも大きくなる可能性があります (ファイル内に存在するが書き込まれていないバイトは 0 に設定されます)。ホールがディスク領域を占有するかどうかは、オペレーティング システムによって決まります
  • 

ファイルのホール部分はディスク領域を占有せず、ファイルが占有するディスク領域は引き続き連続しています。

innodb によって提供される圧縮スキーム

ページ圧縮

該当するシナリオ: 大量のデータと不十分なディスク領域のため、負荷は主に IO に反映され、サーバーの CPU は比較的大きいマージン。

1) COMPRESS ページ圧縮

関連ドキュメント: dev.mysql.com/doc/refman/…

• MySQL5.7 バージョンより前に提供されていたページ圧縮機能では、テーブル作成時に ROW_FORMAT = COMPRESS を指定し、KEY_BLOCK_SIZE で圧縮ページのサイズを設定していました。
• 設計上の欠陥があり、可能性があります。これは明らかなパフォーマンスの低下につながります。本来の設計はパフォーマンスを向上させ、「ログはデータである」という概念を導入することです。
  • 圧縮されたページのデータ変更の場合、ページ自体は直接変更されません。ただし、ログは変更されます。このページに保存すると、データの変更がより容易になります。変更のたびに圧縮/解凍する必要はありません。
    • 
  • データ読み取りの場合、圧縮データを直接読み取ることはできないため、このアルゴリズムはデータ読み取り用に解凍された 16K ページをメモリに保持します
    • 
  • # #これにより、バッファ プール内に 2 つのバージョン (圧縮バージョンと非圧縮バージョン) が存在するページが生成され、バッファ プールにキャッシュできるページ数が大幅に減少するという非常に深刻な問題が発生します。データベースのパフォーマンスが大幅に低下する可能性があります。

2) TPC (透過的ページ圧縮)

関連ドキュメント: dev.mysql.com /doc/refman/…

• 動作原理: ページを書き込むときは、指定された圧縮アルゴリズムを使用してページを圧縮し、圧縮後にホールパンチメカニズムを通じてディスクに書き込みます。ページの終わりから空きスペースを解放します (オペレーティング システムが hole 機能をサポートする必要があります)
• 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()
テスト:
• データの挿入:
  • xxx (コンテンツ) 値に挿入 (compress('xxx....'))
  • 圧縮データの読み取り:

    select c_id, uncompressed_length(c_content) uncompress_len, length(c_content) compress_len から 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

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

以上がMySQL で大きなテキスト ストレージを圧縮する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。