MYSQL INNODB表压缩

压缩前提

表压缩能提升性能,减少存储空间,主要是用在字符类型比较大的表上(VARCHAR,VARBINARY和BLOB和TEXT类型),且读多写少的情况下,如果你的应用是io密集型的,不是cpu密集型的,那么压缩会带来很多性能的提升,例如:数据仓库。

innodb_file_format = Barracuda --模式支持压缩
innodb_file_per_table = on --必须是独立表空间

压缩原理

InnoDB支持两种文件格式 Antelope(羚羊)和Barracuda(梭鱼):

Antelope :是5.6之前的文件格式,支持InnoDB表的COMPACT和REDUNDANT行格式,共享表空间默认为Antelope

Barracuda:是最新的文件格式,支持所有innodb行格式,包括最新的COMPRESSED和DYNAMIC行格式。

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

注意事项

  1. 什么场景需要压缩? 磁盘空间达到瓶颈、存在大字段、读多写少的表
  2. KEY_BLOCK_SIZE该取值多少? InnoDB未压缩的数据页是16K,根据选项组合值,mysql为每个表的.ibd文件使用1kb,2kb,4kb,8kb,16kb页大小,实际的压缩算法并不会受KEY_BLOCK_SIZE值影响,这个值只是决定每个压缩块有多大,从而影响多少行被压缩到每个页。设置KEY_BLOCK_SIZE值等于16k并不能有效的进行压缩,因为默认的innodb页就是16k,但是对于拥有很多BLOB,TEXT,VARCHAR类型字段的表可能会有效果的。
  3. 压缩表上大量的dml操作可能会导致压缩失败,如何调整额外的参数来解决这个问题 调整innodb_online_alter_log_max_size 大小或者采用pt工具修改,尽量在非高峰期操作

原文发布于微信公众号 - MYSQL轻松学(learnmysql)

原文发表时间:2017-12-29

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Golang

influxdb 简介与实现(一)

InfluxDB是一个开源的没有外部依赖的时间序列数据库。适用于记录度量,事件及执行分析。

1314
来自专栏MasiMaro 的技术博文

OLEDB事务

学过数据的人一般都知道事务的重要性,事务是一种对数据源的一系列更新进行分组或者批处理以便当所有更新都成功时同时提交更新,或者任意一个更新失败时进行回滚将数据库中...

954
来自专栏Java编程技术

Dubbo剖析-Dubbo协议

TCP协议栈中,每层模型都有自己的协议报文格式,TCP协议是网络七层模型中的传输层,在TCP上层是应用层,应用层协议常见的有telnet等,Dubbo协议作为建...

391
来自专栏MasiMaro 的技术博文

OLEDB 数据变更通知

除了之前介绍的接口,OLEDB还定义了其他一些支持回调的接口,可以异步操作OLEDB对象或者得到一些重要的事件通知,从而使应用程序有机会进行一些必要的处理。其中...

553
来自专栏程序员八阿哥

王老板Python面试(2): 常见的Python爬虫面试题,让你轻松拿offer!

有些自学爬虫(python)的同学因为没有经历过专业面试,所以在找工作之前难免有些抓不住重点。即便掌握技术熟练,却因为发挥不好而错失工作机会。今天就和大家分享几...

631
来自专栏大内老A

谈谈分布式事务之三: System.Transactions事务详解[上篇]

在.NET 1.x中,我们基本是通过ADO.NET实现对不同数据库访问的事务。.NET 2.0为了带来了全新的事务编程模式,由于所有事务组件或者类型均定义在Sy...

1908
来自专栏pangguoming

分布式系统唯一ID生成方案汇总

系统唯一ID是我们在设计一个系统的时候常常会遇见的问题,也常常为这个问题而纠结。生成ID的方法有很多,适应不同的场景、需求以及性能要求。所以有些比较复杂的系统会...

4526
来自专栏杨建荣的学习笔记

MySQL单表模拟锁和事务的几个场景

在MySQL中对于并发,锁问题总是会有很多值得讨论的地方,但是通常来说,要模拟这些锁或者一些锁的问题需要花点功夫,比如创建多个表,创建大量的数据,然后像调试...

3378
来自专栏程序猿

带您理解SQLSERVER是如何执行一个查询的

带您理解SQLSERVER是如何执行一个查询的 连接方式和请求 如果你是一个开发者,并且你的程序使用SQLSERVER来做数据库的话 你会想知道当你用你的程序执...

4119
来自专栏沃趣科技

Oracle压缩黑科技(一)—基础表压缩

原文链接 https://www.red-gate.com/simple-talk/sql/oracle/compression-oracle-basic-ta...

2558

扫描关注云+社区