共計(jì) 3546 個字符，預(yù)計(jì)需要花費(fèi) 9 分鐘才能閱讀完成。

今天丸趣 TV 小編給大家分享一下 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) 
 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ù)不占用磁盤空間

文件空洞：文件位移量可以大于文件的實(shí)際長度（位于文件中但未被寫過的字節(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è)計(jì)上的缺陷，有可能會導(dǎo)致性能下降明顯，然后其設(shè)計(jì)初衷是為了提升性能，引入了“日志即數(shù)據(jù)”的理念

對于壓縮頁的數(shù)據(jù)修改，并不會直接修改頁本身，而是將修改日志存儲在這個頁中，這確實(shí)對數(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

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

TPC 雖好，但它依賴操作系統(tǒng)的 Hole Punch 特性，且裁剪后的文件大小需要和文件系統(tǒng)塊大小對齊（4K）。即假如壓縮后的頁大小是 9K，那么實(shí)際占用的空間是 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 的計(jì)算能力，性能相對會比較差

表空間壓縮：壓縮效率高，但要求表空間文件是靜態(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í)是一種平衡，并不一定是為了提升數(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：文件的實(shí)際大小，即磁盤上分配的空間量

壓縮率：

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)容，感謝各位的閱讀！相信大家閱讀完這篇文章都有很大的收獲，丸趣 TV 小編每天都會為大家更新不同的知識，如果還想學(xué)習(xí)更多的知識，請關(guān)注丸趣 TV 行業(yè)資訊頻道。