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，就會出現行溢出，溢出的行是存放在另外的地方（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

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

以上是MySQL中怎麼進行大文字儲存壓縮的詳細內容。更多資訊請關注PHP中文網其他相關文章！