[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。