MySQL中如何進(jìn)行大文本存儲壓縮

今天小編給大家分享一下MySQL中如何進(jìn)行大文本存儲壓縮的相關(guān)知識點(diǎn)，內(nèi)容詳細(xì)，邏輯清晰，相信大部分人都還太了解這方面的知識，所以分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后有所收獲，下面我們一起來了解一下吧。

存量數(shù)據(jù)分析

select
  table_name as '表名',
  table_rows as '記錄數(shù)',
  truncate(data_length/1024/1024, 2) as '數(shù)據(jù)容量(MB)',
  truncate(index_length/1024/1024, 2) as '索引容量(MB)',
  truncate(DATA_FREE/1024/1024, 2) as '碎片占用(MB)'
from
  information_schema.tables
where
  table_schema=${數(shù)據(jù)庫名}
order by
  data_length desc, index_length desc;

相關(guān)內(nèi)容介紹

innodb引擎頁數(shù)據(jù)超出16kb怎么辦？

我們都知道innodb的頁塊默認(rèn)大小為16k，如果表中一行數(shù)據(jù)長度超出了16k，就會出現(xiàn)行溢出，溢出的行是存放在另外的地方（uncompress blob page）。由于innodb采用聚簇索引把數(shù)據(jù)進(jìn)行存放起來，即B+Tree結(jié)構(gòu)，因此每個頁塊中至少有兩行數(shù)據(jù)，否則就失去了B+Tree的意義，這樣就得出一行數(shù)據(jù)最大的長度限制為8k（大字段在數(shù)據(jù)頁會存儲768個字節(jié)數(shù)據(jù)，剩余的數(shù)據(jù)溢出到另外的頁中，數(shù)據(jù)頁還有20個字節(jié)記錄溢出頁的地址）

• 對 dynamic 格式來說，如果大對象字段（text/blob）存儲數(shù)據(jù)大小小于 40 字節(jié)，那全部放在數(shù)據(jù)頁，剩余的場景，數(shù)據(jù)頁只保留一個 20 字節(jié)的指針指向溢出頁。 這種場景下，如果每個大對象字段保存的數(shù)據(jù)小于 40 個字節(jié)，也就和 varchar(40)，效果一樣。

• innodb-row-format-dynamic：dev.mysql.com/doc/refman/…

Linux 稀疏文件 & 空洞

• 稀疏文件（Sparse File）：稀疏文件與其他普通文件基本相同，區(qū)別在于文件中的部分?jǐn)?shù)據(jù)全為0，且這部分?jǐn)?shù)據(jù)不占用磁盤空間

• 文件空洞：文件位移量可以大于文件的實際長度（位于文件中但未被寫過的字節(jié)被設(shè)為0），空洞是否占用磁盤空間由操作系統(tǒng)決定

• 

文件空洞部分不占用磁盤空間、文件所占用的磁盤空間仍然是連續(xù)的

innodb提供的壓縮方案

頁面壓縮

適用場景：由于數(shù)據(jù)量太大，磁盤空間不足，負(fù)載主要體現(xiàn)在IO上，而服務(wù)器的CPU又有比較多的余量的場景。

1）COMPRESS頁壓縮

相關(guān)文檔：dev.mysql.com/doc/refman/…

• 在MySQL5.7版本之前就提供的頁壓縮功能，在創(chuàng)建表時指定 ROW_FORMAT = COMPRESS，并通過 KEY_BLOCK_SIZE 設(shè)置壓縮頁的大小

• 存在設(shè)計上的缺陷，有可能會導(dǎo)致性能下降明顯，然后其設(shè)計初衷是為了提升性能，引入了“日志即數(shù)據(jù)”的理念

• 

• 

• 對于壓縮頁的數(shù)據(jù)修改，并不會直接修改頁本身，而是將修改日志存儲在這個頁中，這確實對數(shù)據(jù)的變更比較友好，不用每次修改都進(jìn)行壓縮/解壓

• 對于數(shù)據(jù)的讀取，壓縮的數(shù)據(jù)是無法直接讀取的，所以這種算法會在內(nèi)存中保留一個解壓后的16K的頁，以供數(shù)據(jù)的讀取

• 這就導(dǎo)致了一個頁在緩沖池中可能會有兩個版本（壓縮版和非壓縮版），引發(fā)一個非常嚴(yán)重的問題，即緩沖池中能緩存的頁的數(shù)量大大的減少了，從而可能會導(dǎo)致數(shù)據(jù)庫的性能極大的下降

2）TPC（透明頁壓縮）

• 工作原理：寫入頁面時，使用指定的壓縮算法對頁面進(jìn)行壓縮，壓縮后寫入磁盤，其中通過打孔機(jī)制從頁面末尾釋放空（需要操作系統(tǒng)支持空洞特性）

• ALTER TABLE xxx COMPRESSION = ZLIB 可以啟用TPC頁壓縮功能，但這只是對后續(xù)增量數(shù)據(jù)進(jìn)行壓縮，如果期望對整個表進(jìn)行壓縮，則需要執(zhí)行 OPTIMIZE TABLE xxx

• 實現(xiàn)過程：一個壓縮頁在緩沖池中都是一個16K的非壓縮頁，只有在數(shù)據(jù)刷盤的時候，會進(jìn)行一次壓縮，壓縮后剩余的空間會用 0x00 填滿，利用文件系統(tǒng)的空洞特性（hole punch）對文件進(jìn)行裁剪，釋放 0x00 占用的稀疏空間

• TPC雖好，但它依賴操作系統(tǒng)的 Hole Punch 特性，且裁剪后的文件大小需要和文件系統(tǒng)塊大小對齊（4K）。即假如壓縮后的頁大小是9K，那么實際占用的空間是12K

列壓縮

MySQL目前沒有直接針對列壓縮的方案，有一個曲線救國的方法，就是在業(yè)務(wù)層使用MySQL提供的壓縮和解壓函數(shù)來針對列進(jìn)行壓縮和解壓操作。也就是如果需要對某一列做壓縮，在寫入時調(diào)用COMPRESS函數(shù)對那個列的內(nèi)容進(jìn)行壓縮，讀取的時候，使用UNCOMPRESS函數(shù)對壓縮過的數(shù)據(jù)進(jìn)行解壓。

• 使用場景：針對表中某些列數(shù)據(jù)長度比較大的情況，一般是 varchar、text、blob、json等數(shù)據(jù)類型

• 相關(guān)函數(shù)：

• 壓縮函數(shù)：COMPRESS()

• 解壓縮函數(shù)：UNCOMPRESS()

• 字符串長度函數(shù)：LENGTH()

• 未解壓字符串長度函數(shù)：UNCOMPRESSED_LENGTH()

• 測試：

• 插入數(shù)據(jù)：insert into xxx (content) values (compress('xxx....'))

• 讀取壓縮的數(shù)據(jù)：select c_id, uncompressed_length(c_content) uncompress_len, length(c_content) compress_len from xxx

為什么innodb提供的都是基于頁面的壓縮技術(shù)？

• 記錄壓縮：每次讀寫記錄的時候，都要進(jìn)行壓縮或解壓，過度依賴CPU的計算能力，性能相對會比較差

• 表空間壓縮：壓縮效率高，但要求表空間文件是靜態(tài)不增長的，這對于我們大部分的場景都是不適用的

• 頁面壓縮：既能提升效率，又能在性能中取得一定的平衡

總結(jié)

• 對于一些性能不敏感的業(yè)務(wù)表，如日志表、監(jiān)控表、告警表等，這些表只期望對存儲空間進(jìn)行優(yōu)化，對性能的影響不是很關(guān)注，可以使用COMPRESS頁壓縮

• 對于一些比較核心的表，則比較推薦使用TPC壓縮

• 列壓縮過度依賴CPU，性能方面會稍差，且對業(yè)務(wù)有一定的改造成本，不夠靈活，需要評估影響范圍，做好切換的方案。好處是可以由業(yè)務(wù)端決定哪些數(shù)據(jù)需要壓縮，并控制解壓操作

• 對頁面進(jìn)行壓縮，在業(yè)務(wù)側(cè)不用進(jìn)行什么改動，對線上完全透明，壓縮方案也非常成熟

為什么要進(jìn)行數(shù)據(jù)壓縮？

• 由于處理器和高速緩存存儲器的速度提高超過了磁盤存儲設(shè)備，因此很多時候工作負(fù)載都是受限于磁盤I/O。數(shù)據(jù)壓縮可以使數(shù)據(jù)占用更小的空間，可以節(jié)省磁盤I/O、減少網(wǎng)絡(luò)I/O從而提高吞吐量，雖然會犧牲部分CPU資源作為代價

• 對于OLTP系統(tǒng)，經(jīng)常進(jìn)行update、delete、insert等操作，通過壓縮表能夠減少存儲占用和IO消耗

• 壓縮其實是一種平衡，并不一定是為了提升數(shù)據(jù)庫的性能，這種平衡取決于解壓縮帶來的收益和開銷之間的一種權(quán)衡，但壓縮對存儲空間來說，收益無疑是很大的

簡單測試

innodb透明頁壓縮（TPC）

測試數(shù)據(jù)

1）創(chuàng)建表

• 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行測試數(shù)據(jù)

壓縮率

SELECT NAME, FS_BLOCK_SIZE, FILE_SIZE, ALLOCATED_SIZE
FROM information_schema.INNODB_TABLESPACES WHERE NAME like 'test_compress%';

• FS_BLOCK_SIZE：文件系統(tǒng)塊大小，也就是打孔使用的單位大小

• FILE_SIZE：文件的表觀大小，表示文件的最大大小，未壓縮

• ALLOCATED_SIZE：文件的實際大小，即磁盤上分配的空間量

壓縮率：

• zlib：1320636416/3489660928 = 37.8%

• lz4：1566949376/3489660928 = 45%

耗時

• 循環(huán)插入10w條記錄

• 原表：918275 ms

• zlib：878540 ms

• lz4：875259 ms

• 循環(huán)查詢10w條記錄

• 原表：332519 ms

• zlib：373387 ms

• lz4：343501 ms

以上就是“MySQL中如何進(jìn)行大文本存儲壓縮”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家閱讀完這篇文章都有很大的收獲，小編每天都會為大家更新不同的知識，如果還想學(xué)習(xí)更多的知識，請關(guān)注億速云行業(yè)資訊頻道。