作者：dx152 | 来源：互联网 | 2023-09-25 19:31
表压缩能提升性能，减少存储空间，主要是用在字符类型比较大的表上（VARCHAR，VARBINARY和BLOB和TEXT类型），且读多写少的情况下，如果你的应用是io密集型的，不是cpu密集型的，那么压缩会带来很多性能的提升，例如：数据仓库。
- innodb_file_format = Barracuda --模式支持压缩
- innodb_file_per_table = on --必须是独立表空间
InnoDB支持两种文件格式 Antelope（羚羊）和Barracuda（梭鱼）：
ROW_FORMAT值：
ROW_FORMAT | 支持索引前缀 | 独立表空间压缩 | 系统表空间压缩
|
|---|
COMPRESSED | 3072字节 | 支持 | 不支持 |
DYNAMIC | 3072字节 | 不支持 | 不支持 |
COMPACT | 768字节 | 不支持 | 支持 |
REDUNDANT | 768字节 | 不支持 | 支持 |
默认情况下（innodb_page_size=16K），前缀索引最多包含768个字节。如果开启innodb_large_prefix，且Innodb表的存储格式为 DYNAMIC 或 COMPRESSED，则前缀索引最多可包含3072个字节，前缀索引也同样适用。
DYNAMIC和COMPRESSED行格式是COMPACT行格式的变体，早期版本的InnoDB对数据库文件使用文件格式Antelope。用这种文件格式，行格式为ROW_FORMAT = COMPACT或REDUNDANT，索引记录中最多存储768个字节的可变长度列（VARCHAR，VARBINARY和BLOB和TEXT类型），其余部分存储在溢出页中。InnoDB还将长度大于或等于768字节的固定长度字段为可变长度字段，将多余的存储在溢出页。例如，如果字符集的最大字节长度大于3（utf8mb4），char(255)列可能会超过768个字节。
COMPRESSED和DYNAMIC这种格式对可变长度列的处理方式是在page里只存储一个20字节大小的指针，其它全存在溢出页，所以轻易超不了innodb_page_size的一半（Innodb表为IOT，采用了B+tree类型，故每个页至少要存储2行数据，如果行过大则会产生行溢出，不论是varchar还是blob/text，只要保证一个16k的页面能容下2行数据，应该不会行溢出，而一旦行溢出，字段前768字节依旧存放于当前页面，数据一般使用B-tree Node页，而溢出的行存放于Uncompress Blob页；而barracuda采用了完全行溢出，即只保留字段的前20字节）。
详细说明：https://dev.mysql.com/doc/refman/5.6/en/innodb-physical-record.html
压缩算法采用LZ77，在这个算法下，如果压缩效率好点的话，压缩后的大小和未压缩的数据大小比如在25-50%左右，在这种情况下就会有效地通过消耗一些CPU来减少IO操作，增大吞吐量，可以通过调节压缩程度（innodb_compression_level参数）来权衡压缩比和CPU使用率。
- innodb_compression_level：默认值为6，可选值0-9，数值越大表示压缩程度越大，消耗的CPU也越多。
- innodb_compression_failure_threshold_pct：默认为5，可取值0-100，表示更新一个压缩表时，指定一个压缩失败的临界值。当超过这个临界值，mysql会为每个压缩页添加额外的空间来避免再次压缩失败。值为0表示禁用监控压缩效率，改为动态调整。
- innodb_compression_pad_pct_max：重新压缩时为每个压缩页额外分配的空间比例，默认50，可取值0-75.这个参数值只有当参数innodb_compression_failure_threshold_pct非0时才生效。
如果设置了ROW_FORMAT=COMPRESSED，那可以忽略key_block_size设置，这时默认的key_block_size为innodb_page_size值的一半，MYSQL默认设置innodb_page_size=16k；
如果设置了key_block_size，那可以忽略ROW_FORMAT=COMPRESSED，这时压缩时自动打开的，key_block_size的值设置8或4最优；
alter table test ROW_FORMAT=COMPRESSED;
或
alter table test key_block_size=8;
key_block_size的值只能是小于或等于innodb_page_size，如果设置过大的话，会有告警，并忽略这个值，使用innodb_page_size的一半去设置。如何去决定key_block_size的大小，可以使用不同的值创建几个副本，对比ibd文件。
key_block_size该值如果太小，插入和更新也许会导致耗时的解压操作，b-tree节点也许会更频繁的分裂，导致更大的数据文件和低效的索引。一般情况下key_block_size=8是个安全的设置。
key_block_size这个值决定了每个压缩chunk的大小，多少行能被打包到一个压缩页中。
可通过查询INFORMATION_SCHEMA下相关INNODB压缩表，获取压缩表的数据状态：
INNODB_CMP和INNODB_CMP_RESET：压缩页的数据状态信息；
INNODB_CMPMEM和INNODB_CMPMEM_RESET：innodb_buffer_pool中压缩页的信息；
INNODB_CMP_PER_INDEX和INNODB_CMP_PER_INDEX_RESET：MYSQL5.7新加，该表提供每一张表和索引的压缩情况，测试时候需要开启innodb_cmp_per_index_enabled参数
innodb_file_format = Barracuda（之前是Antelope）
innodb_file_per_table = ON
innodb_page_size = 16384
innodb_large_prefix = ON （之前是OFF）
innodb_default_row_format = dynamic （Mysql5.7新加）
以下参数可能在未来版本中删除：
innodb_file_format
innodb_file_format_check
innodb_file_format_max
innodb_large_prefix
- InnoDB未压缩的数据页是16K，根据选项组合值，mysql为每个表的.ibd文件使用1kb,2kb,4kb,8kb,16kb页大小，实际的压缩算法并不会受KEY_BLOCK_SIZE值影响，这个值只是决定每个压缩块有多大，从而影响多少行被压缩到每个页。设置KEY_BLOCK_SIZE值等于16k并不能有效的进行压缩，因为默认的innodb页就是16k，但是对于拥有很多BLOB,TEXT,VARCHAR类型字段的表可能会有效果的。
- 压缩表上大量的dml操作可能会导致压缩失败，如何调整额外的参数来解决这个问题调整innodb_online_alter_log_max_size 大小或者采用pt工具修改，尽量在非高峰期操作
京公网安备 11010802041100号