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

創(chuàng)新互聯(lián)為您提適合企業(yè)的網(wǎng)站設(shè)計(jì)?讓您的網(wǎng)站在搜索引擎具有高度排名，讓您的網(wǎng)站具備超強(qiáng)的網(wǎng)絡(luò)競(jìng)爭(zhēng)力！結(jié)合企業(yè)自身，進(jìn)行網(wǎng)站設(shè)計(jì)及把握，最后結(jié)合企業(yè)文化和具體宗旨等，才能創(chuàng)作出一份性化解決方案。從網(wǎng)站策劃到網(wǎng)站設(shè)計(jì)、成都做網(wǎng)站， 我們的網(wǎng)頁(yè)設(shè)計(jì)師為您提供的解決方案。

存量數(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ù)庫(kù)名}
order by
  data_length desc, index_length desc;

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

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

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

• 對(duì) dynamic 格式來(lái)說(shuō)，如果大對(duì)象字段（text/blob）存儲(chǔ)數(shù)據(jù)大小小于 40 字節(jié)，那全部放在數(shù)據(jù)頁(yè)，剩余的場(chǎng)景，數(shù)據(jù)頁(yè)只保留一個(gè) 20 字節(jié)的指針指向溢出頁(yè)。 這種場(chǎng)景下，如果每個(gè)大對(duì)象字段保存的數(shù)據(jù)小于 40 個(gè)字節(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ù)不占用磁盤(pán)空間

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

• 

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

innodb提供的壓縮方案

頁(yè)面壓縮

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

1）COMPRESS頁(yè)壓縮

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

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

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

• 

• 

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

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

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

2）TPC（透明頁(yè)壓縮）

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

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

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

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

列壓縮

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

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

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

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

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

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

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

• 測(cè)試：

• 插入數(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提供的都是基于頁(yè)面的壓縮技術(shù)？

• 記錄壓縮：每次讀寫(xiě)記錄的時(shí)候，都要進(jìn)行壓縮或解壓，過(guò)度依賴(lài)CPU的計(jì)算能力，性能相對(duì)會(huì)比較差

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

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

總結(jié)

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

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

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

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

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

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

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

• 壓縮其實(shí)是一種平衡，并不一定是為了提升數(shù)據(jù)庫(kù)的性能，這種平衡取決于解壓縮帶來(lái)的收益和開(kāi)銷(xiāo)之間的一種權(quán)衡，但壓縮對(duì)存儲(chǔ)空間來(lái)說(shuō)，收益無(wú)疑是很大的

簡(jiǎn)單測(cè)試

innodb透明頁(yè)壓縮（TPC）

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

1）創(chuàng)建表

• create table table_origin ( ...... ) comment '測(cè)試原表';

• create table table_compression_zlib ( ...... ) comment '測(cè)試壓縮表_zlib' compression = 'zlib';

• create table table_compression_lz4 ( ...... ) comment '測(cè)試壓縮表_lz4' compression = 'lz4';

2）往表中寫(xiě)入10w行測(cè)試數(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í)際大小，即磁盤(pán)上分配的空間量

壓縮率：

• zlib：1320636416/3489660928 = 37.8%

• lz4：1566949376/3489660928 = 45%

耗時(shí)

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

• 原表：918275 ms

• zlib：878540 ms

• lz4：875259 ms

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

• 原表：332519 ms

• zlib：373387 ms

• lz4：343501 ms

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