前面提到，我們有個雲端文檔專案的快照內容是直接儲存到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/…
- TPC雖好，但它依賴作業系統的Hole Punch 特性，且裁剪後的檔案大小需要和檔案系統區塊大小對齊（ 4K）。即假如壓縮後的頁大小是9K，那麼實際佔用的空間是12K
列壓縮
MySQL目前沒有直接針對列壓縮的方案，有一個曲線救國的方法，就是在業務層使用MySQL提供的壓縮和解壓函數來針對列進行壓縮和解壓操作。也就是如果需要對某一列做壓縮，在寫入時呼叫COMPRESS函數對那個列的內容進行壓縮，讀取的時候，使用UNCOMPRESS函數對壓縮過的數據進行解壓縮。
- 使用場景：針對表中某些列資料長度比較大的情況，一般是varchar、text、blob、json等資料型別
- 相關函數：
- 壓縮函數：
COMPRESS() - 解壓縮函數：
UNCOMPRESS() - 字串長度函數：
LENGTH() - 未解壓縮字串長度函數：
UNCOMPRESSED_LENGTH()
##測試：
#
为什么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中文網其他相關文章！
