[MySQL学习] MySQL压缩表

以下是在阅读官方文档时的笔记

官方文档:

http://dev.mysql.com/doc/refman/5.6/en/innodb-compression.html#innodb-compression-tuning

关于压缩表，可以调整的参数看起来只有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。