收藏本页 | 设为主页 | RSS
LOGO

我就上乐乐吧

MySQL引擎InnoDB表压缩即可以在创建表时开启,也可以使用压缩表命令来随时调整。压缩表能够使表中的数据以压缩格式存储,能够显著提高原生性能和可伸缩性。压...

您当前的位置:首页 » 技术杂萃
全站公告
重要提示!

本站收集各类美图,以无LOGO原汁原味图为主!欢迎大家交流。
怕失联,请认准如下地址:
微博:传送门
土豆:传送门
电报:传送门
微信:superimagelove
花瓣:传送门
Q群1:231151757
Q群2:583138281
文件解压密码:593668.com
热心赞助

站内搜索
 
相关文章
热门文章
热搜词组
文章归档
友情链接
MySQL的InnoDB表数据压缩

发布日期:2020-03-13  关键词: MySQL InnoDB

MySQL引擎InnoDB表压缩即可以在创建表时开启,也可以使用压缩表命令来随时调整。压缩表能够使表中的数据以压缩格式存储,能够显著提高原生性能和可伸缩性。压缩意味着在硬盘和内存之间传输的数据更小且占用相对少的内存及硬盘,对于辅助索引,这种压缩带来更加明显的好处,因为索引数据也被压缩了。压缩对于硬盘是SSD的存储设备尤为重要,因为它们相对普通的HDD硬盘比较贵且容量有限。

从所周知,CPU和内存的速度都远远大于磁盘,因为对于数据库服务器,磁盘IO可能就会成为紧要资源或者瓶颈。数据压缩能够让数据库变得更小,从而减少磁盘的I/O,还能提高系统吞吐量,以很小的成本(就是要耗费较多的CPU资源)。对于读比重比较多的应用,压缩是特别有用的,还能够让系统拥有足够的内存来存储热数据。

在创建innodb表时带上ROW_FORMAT=COMPRESSED参数就能够使用比默认的16K更小的页。这样在读写时需要更少的I/O,对于SSD磁盘更有价值。

而页的大小也可通过KEY_BLOCK_SIZE参数来动态指定。不同大小的页意味着需要使用独立表空间,所以就不能使用系统共享表空间,必须开启独立表空空间。

可以通过my.cnf文件的innodb_file_per_table=1来指定,也可以使用如下SQL语句来设定。
SET GLOBAL innodb_file_per_table=1;

1)建表使用表的压缩
建表语句如下:
CREATE TABLE test
(id INT PrimaRY KEY)
ROW_FORMAT=COMPRESSED 
KEY_BLOCK_SIZE=8;
如果指定ROW_FORMAT=COMPRESSED,那么可以忽略KEY_BLOCK_SIZE的值,这时会使用默认innodb页的一半,即8kb;如果指定了KEY_BLOCK_SIZE的值,那么也可以忽略ROW_FORMAT=COMPRESSED,因为这时会自动启用压缩;

2)调整已经存在的表变为压缩表
ALTER TABLE test KEY_BLOCK_SIZE=8;

为了选出最合适KEY_BLOCK_SIZE的值,可以创建表的多个副本,使用不同的值进行测试,再来比较他们的.ibd文件的大小;

KEY_BLOCK_SIZE的值0代表默认压缩页的值,Innodb页的一半8。值只能小于等于innodb page size。如果指定了一个大于innodb page size的值,MySQL会忽略这个值然后产生一个警告,同时采用默认值。但如果设置了innodb_strict_mode=ON,那么指定一个不合法的KEY_BLOCK_SIZE的值是返回报错。

InnoDB未压缩的数据页是16K,根据选项组合值,MySQL为每个表的.ibd文件使用1kb,2kb,4kb,8kb,16kb页大小,实际的压缩算法并不会受KEY_BLOCK_SIZE值影响【采用L777压缩算法】,这个值只是决定每个压缩块有多大。设置KEY_BLOCK_SIZE值等于16k没啥意义,因为默认的innodb页就是16k,但是对于拥有很多BLOB,TEXT,VARCHAR类型字段的表可能会有效果的。

KEY_BLOCK_SIZE的值越小,获得I/O好处就越多,但是如果因为指定的值太小,当数据被压缩到不足够满足每页多行数据记录时,反而会产生额外的开销来重组页。对于一个表,KEY_BLOCK_SIZE的值有多小是有严格的限制的,一般是基于每个索引键的长度。有时指定值过小,建表或调整表时也会失败。

在缓冲池中,被压缩的数据是存储在小页中的,这个小页的实际大小就是KEY_BLOCK_SIZE的值。为了提取和更新列值,MySQL还会在缓冲池中创建一个未压缩的16k页。任何更新到未压缩的页也需要重新写入到压缩的页,这时需要估计缓冲池的大小以满足压缩和未压缩的页,尽管当缓冲空间不足时,未压缩的页会被挤出缓冲池。但在下次访问时,不压缩的页还会被创建。

3)选择那些表压缩
在进行表压缩时,需要考虑影响很多压缩性能的因素,如:
1、表数据读大于写
一般而言,对于读远远大于写的应用以及拥有合理数量的字符串列的表,使用压缩效果会更好。

2、数据特性及压缩率
影响数据文件压缩效率的一个关键因素是数据本身的结构,在块数据中,压缩是通过识别重复字符进行压缩的,对于完全随机的数据是一个糟糕的情况,一般而言,有重复数据的压缩更好。对于字符串的压缩就不错,另一方面,如果表中的数据是二进制类型,如整形、浮点型等或者之前别压缩过的如jpg、png类型的,压缩效果就得不偿失,但也不是绝对的。

为了决定是否对某个表进行压缩,可以先进行试验,可以对比未压缩与压缩后的数据文件的大小。

3、数据库压缩和应用程序压缩任选其一
不要在应用端和数据库同时压缩相同的数据,那样效果并不明显而且还消耗很多CPU资源。如果已经在前端插入数据前就进行了压缩,那么数据库压缩就没有必要了,除非CPU有大量结余,可以考虑结合两者。

4、工作负载特性和压缩率
为了选择哪些表可以使用压缩,工作负载是另一个决定因素,一般而言,如果系统有I/O瓶颈,那么可以考虑使用CPU进行压缩与解压缩,以CPU换取I/O。

在了解以上知识之后,就可以去尝试下压缩MySQL数据库表并提升性能了。
猜您喜欢:
Q群: 231151757微信:superimagelove 官网:www.593668.com