[MySQL學習] MySQL壓縮表

以下是在閱讀官方文檔時的筆記

官方文檔:

<a href="http://dev.mysql.com/doc/refman/5.6/en/innodb-compression.html#innodb-compression-tuning">http://dev.mysql.com/doc/refman/5.6/en/innodb-compression.html#innodb-compression-tuning</a>

////////////////////////////////////////////////////////////////////////

關于壓縮表，可以調整的參數看起來隻有key_block_size，在建表時指定，意味着innodb會将page壓縮到指定的大小，例如，如果設定key_block_size=8，則将其壓縮到8k。key_block_size的值應根據記錄的長度來确定，如果設定的過小，可能由于一個page無法壓縮太多行而出現高機率的壓縮失敗，導緻不得不split page.但設定為16k則不會取得太好的壓縮效果，當然這種情況依然對存在長blob/varchar/text類型的列有好處，這可以避免過多的”overflow” page

在buffer pool中，壓縮資料也以小page的形式存在，為了擷取或更新資料，在innodb中也為解壓資料建立了16kb的page，任何對解壓page的修改，也會寫入到壓縮page中。當空間不夠時，解壓page會被驅逐出bp。

注意實際的壓縮算法并不受key_block_size的影響。

key_block_size的預設值為8k。

在表上建立索引時create index指定row_format和key_block_size沒有效果，這取決于建表時的設定。

總的來說，壓縮表更加适用于合理長度的字元串，并且其資料讀比寫更多。何時使用壓縮表并無定論，這取決于你的負載和資料集合，或者特定的配置。可以考慮如下因素：

a.由于壓縮本身通過識别資料塊中的重複字元串來進行壓縮，完全随機的資料可能會得到很差的效果。典型的資料通常有很多重複，是以能獲得更好的壓縮效果。字元串(char,  varchar, text 或者 blob)可以獲得更好的效果。

可以從一個非壓縮表中拷貝資料到一個相同的壓縮表中，觀察資料大小，來決定是否适合壓縮。在innodb_cmp中，通過觀察compress_ops_ok/compress_ops來獲得壓縮成功率。如果該比率較高，則表明适合作為壓縮表。

b.已經在應用中壓縮過的資料，不适合存儲到壓縮表中。

c.通過like或order by來測試壓縮後的索引性能

d.在應用中進行壓縮(使用mysql提供的compress/uncompress函數進行壓縮/解壓)

e.在表上的workload是一個關鍵性因素，如果更新主要作用在外部存儲的長字元串的非索引列上，壓縮的開銷可能是可以接受的。如果你的負載是i/o bound而非cpu bound的，壓縮可能會改善整體性能。

f.配置特性；壓縮可以通過消耗cpu來減少io，如果io是相對緊缺的資源時，會獲得更好的效果。

g.選擇壓縮page的大小應該比記錄更大，否則可能會引起大量的壓縮失敗。通常情況下key_block_size=8是比較安全的設定。

h.運作時監控壓縮

a.cpu和io使用率，資料檔案大小等

b.通過innodb_cmp/innodb_cmp_reset表進行監控，如果compress_ops_ok/compress_ops的比率較高，說明系統工作的很好，如果很低，則表明innodb可能有太多的重新組織，重新壓縮或b-tree節點分裂。這種情況下避免壓縮這些表，或調大key_block_size的值。

###壓縮表内部實作tips###

innodb使用的是zlib中的lz77算法

b-tree node中的某些系統資訊并未壓縮，這有利于in-place update，例如标記删除以及無需解壓的删除操作。

為了避免dml産生的過多的解壓/壓縮，在每個b-tree page中，維持了一段非壓縮的“modification log”，來記錄page上的更改。

當“modification log”過大時，innodb會解壓page，執行更新，然後重新壓縮page。如果重新壓縮失敗，就需要分裂b樹節點。

通常情況下innodb要求每個page至少容納2條記錄，對于壓縮表，這個限制被放松了，葉子節點可以隻容納一條記錄，但是 but that record must fit in uncompressed form, in the per-page modification log(暫時沒搞明白，待分析)

為了減少io和解壓page的次數，在buffer pool中可能會維護page的壓縮和解壓兩類，當空間不夠時，innodb可能将解壓的page驅逐掉，保留壓縮的page在bp中。如果一個page在一段時間内沒有被使用，壓縮格式的page也會被寫會到磁盤中，以釋放空間。

innodb使用一個adaptive lru算法來維持記憶體内壓縮和非壓縮page的平衡，目的是為了避免在cpu繁忙時減少解壓page的開銷，當cpu富餘時避免過多的i/o。當系統是i/o bound時，傾向于選擇驅逐page的非壓縮拷貝，以留下空間給其他讀入磁盤的page。當是cpu bound時，innodb選擇驅逐壓縮和非壓縮這兩種page，這樣記憶體可以更多的留給“hot page”，并減少記憶體中需要解壓的page。