mysql表数据压缩

转载

小叔叔书 2021-08-09 16:16:34 博主文章分类：Mysql

文章标签 数据 mysql sed 压缩率数据库 文章分类 MySQL 数据库

记得一次面试中，面试官问我是否知道表的压缩，这个时候我才知道mysql有个表压缩这么个功能，今天试用下看看表的压缩率怎么样。

这里分两个部分说明，第一部分：官方文档说明；第二部分：具体实例测试。

【第一部分】

一、表压缩概述：

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

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

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

页的大小通过KEY_BLOCK_SIZE参数指定。不同大小的页意味着需要使用独立表空间，不能使用系统共享表空间，可以通过innodb_file_per_table指定。KEY_BLOCK_SIZE的值越小，你获得I/O好处就越多，但是如果因为你指定的值太小，当数据被压缩到不足够满足每页多行数据记录时，会产生额外的开销来重组页。对于一个表，KEY_BLOCK_SIZE的值有多小是有严格的限制的，一般是基于每个索引键的长度。有时指定值过小，当create table或者alter table会失败。

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

二、使用表的压缩

在创建一个压缩表之前，需要启用独立表空间参数innodb_file_per_table=1；也需要设置innodb_file_format=Barracuda，你可以写到my.cnf文件中不需要重启mysql服务。

SET GLOBAL innodb_file_per_table=1;
SET GLOBAL innodb_file_format=Barracuda;
CREATE TABLE t1
 (c1 INT PRIMARY KEY) 
 ROW_FORMAT=COMPRESSED  
 KEY_BLOCK_SIZE=8;

如果你指定ROW_FORMAT=COMPRESSED，那么可以忽略KEY_BLOCK_SIZE的值，这时使用默认innodb页的一半，即8kb；
如果你指定了KEY_BLOCK_SIZE的值，那么你可以忽略ROW_FORMAT=COMPRESSED，因为这时会自动启用压缩；
为了指定最合适KEY_BLOCK_SIZE的值，你可以创建表的多个副本，使用不同的值进行测试，比较他们的.ibd文件的大小；
KEY_BLOCK_SIZE的值作为一种提示，如必要，Innodb也可以使用一个不同的值。0代表默认压缩页的值，Innodb页的一半。KEY_BLOCK_SIZE的值只能小于等于innodb page size。如果你指定了一个大于innodb page size的值，mysql会忽略这个值然后产生一个警告，这时KEY_BLOCK_SIZE的值是Innodb页的一半。如果设置了innodb_strict_mode=ON，那么指定一个不合法的KEY_BLOCK_SIZE的值是返回报错。

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

三、InnoDB表的压缩优化

在进行表压缩时需要考虑影响压缩性能的因素，如：

哪些表需要压缩
如何选择压缩表的页大小
基于运行时性能特征是否需要调整buffer pool大小，如系统在压缩和解压缩数据所花费的时间量，系统负载更像一个数据仓库还是OLTP事务性系统。
如果在压缩表上执行DML操作，由于数据分布的方式，可能导致压缩失败，这时你可能需要配置额外的更高级的配置选项

1、何时用压缩表

一般而言，对于读远远大于写的应用以及拥有合理数量的字符串列的表，使用压缩效果会更好。

2、数据特性及压缩率

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

为了决定是否对某个表进行压缩，你需要进行试验，可以对比未压缩与压缩后的数据文件的大小，以及监控系统对于压缩表的工作负载进行决定。具体试验请查看第二部分。

查看监控压缩表的负载，如下：

对于简单的测试，如一个mysql实例上没有其他的压缩表了，直接查询INFORMATION_SCHEMA.INNODB_CMP表数据即可，该表存一些压缩表的数据状态，结构如下：

Column name	Description
`PAGE_SIZE`	采用压缩页大小（字节数）.
`COMPRESS_OPS`	Number of times a B-tree page of the size `PAGE_SIZE` has been compressed. Pages are compressed whenever an empty page is created or the space for the uncompressed modification log runs out.
`COMPRESS_OPS_OK`	Number of times a B-tree page of the size `PAGE_SIZE` has been successfully compressed. This count should never exceed `COMPRESS_OPS`.
`COMPRESS_TIME`	Total time in seconds spent in attempts to compress B-tree pages of the size `PAGE_SIZE`.
`UNCOMPRESS_OPS`	Number of times a B-tree page of the size `PAGE_SIZE` has been uncompressed. B-tree pages are uncompressed whenever compression fails or at first access when the uncompressed page does not exist in the buffer pool.
`UNCOMPRESS_TIME`	Total time in seconds spent in uncompressing B-tree pages of the size `PAGE_SIZE`.

对于精细的测试，如多个压缩表，查询INFORMATION_SCHEMA.INNODB_CMP_PER_INDEX表数据，由于该表收集数据需要付出昂贵得代价，所以必须启动innodb_cmp_per_index_enabled选项才能查询。一般不要在生产环境下开启该选项。
还可以针对压缩运行一些测试SQL看看效率如何。
如果发现很多压缩失败，那么你可以调整innodb_compression_level, innodb_compression_failure_threshold_pct, 和innodb_compression_pad_pct_max参数。

3、数据库压缩和应用程序压缩

不需要在应用端和数据库同时压缩相同的数据，那样效果并不明显而且还消耗很多CPU资源。对于数据库压缩，是在server端进行的。如果你在插入数据前通过代码进行数据压缩，然后插入数据库，这样耗费很多CPU资源，当然如果你的CPU有大量结余。你也可以结合两者，对于某些表进行应用程序压缩，而对其他数据采用数据库压缩。

4、工作负载特性和压缩率

为了选择哪些表可以使用压缩，工作负载是另一个决定因素，一般而言，如果你的系统是I/O瓶颈，那么可以使用CPU进行压缩与解压缩，以CPU换取I/O。

四、INNODB表是如何压缩的？

1、压缩算法

mysql进行压缩是借助于zlib库，采用L777压缩算法，这种算法在减少数据大小、CPU利用方面是成熟的、健壮的、高效的。同时这种算法是无失真的，因此原生的未压缩的数据总是能够从压缩文件中重构，LZ777实现原理是查找重复数据的序列号然后进行压缩，所以数据模式决定了压缩效率，一般而言，用户的数据能够被压缩50%以上。

不同于应用程序压缩或者其他数据库系统的压缩，InnoDB压缩是同时对数据和索引进行压缩，很多情况下，索引能够占数据库总大小的40%-50%。如果压缩效果很好，一般innodb文件会减少25%-50%或者更多，而且减少I/O增加系统吞吐量，但是会增加CPU的占用，你可通过设置innodb_compression_level参数来平衡压缩级别和CPU占用。

2、InnoDB数据存储及压缩

所有数据和b-tree索引都是按页进行存储的，每行包含主键和表的其他列。辅助索引也是b-tree结构的，包含对值：索引值及指向每行记录的指针，这个指针实际上就是表的主键值。

在innodb压缩表中，每个压缩页（1,2,4,8）都对应一个未压缩的页16K，为了访问压缩页中的数据，如果该页在buffer pool中不存在，那么就从硬盘上读到这个压缩页，然后进行解压到原来的数据结构。为了最小化I/O和减少解压页的次数，有时，buffer pool中包括压缩和未压缩的页，为给其他页腾出地方，buffer pool会驱逐未压缩页，仅仅留下压缩页在内存中。或者如果一个页一段时间没有被访问，那么会被写到硬盘上。这样一来，任何时候，buffer pool中都可以包含压缩页和未压缩页，或者只有压缩页或者两者都没有。

Mysql采用LRU算法来保证哪些页应该在内存中还是被驱逐。因此热数据一般都会在内存中。

五、OLTP系统压缩负载优化

一般而言，innodb压缩对于只读或者读比重比较多的应用效果更好，SSD的出现，使得压缩更加吸引我们，尤其对于OLTP系统。对于经常update、delete、insert的应用，通过压缩表能够减少他们的存储需求和每秒I/O操作。

下面是针对写密集的应用，设置压缩表的一些有用参数：

innodb_compression_level：决定压缩程度的参数，如果你设置比较大，那么压缩比较多，耗费的CPU资源也较多；相反，如果设置较小的值，那么CPU占用少。默认值6，可以设置0-9
innodb_compression_failure_threshold_pct：默认值5，范围0到100.设置中断点避免高昂的压缩失败率。
innodb_compression_pad_pct_max：指定在每个压缩页面可以作为空闲空间的最大比例，该参数仅仅应用在设置了innodb_compression_failure_threshold_pct不为零情况下，并且压缩失败率通过了中断点。默认值50，可以设置范围是0到75.

【第二部分】实验：

mysql表数据压缩_数据

#没有设置压缩前的数据大小
-rw-rw----. 1 mysql mysql 368M 12月 29 11:05 test.ibd
#设置KEY_BLOCK_SIZE=1
(product)root@localhost [sakila]> alter table test KEY_BLOCK_SIZE=1;
Query OK, 0 rows affected (14 min 49.30 sec)
Records: 0  Duplicates: 0  Warnings: 0

-rw-rw----. 1 mysql mysql 204M 1月  11 21:43 test.ibd      #####压缩率44.5%

#设置KEY_BLOCK_SIZE=2
(product)root@localhost [sakila]> alter table test KEY_BLOCK_SIZE=2;
Query OK, 0 rows affected (9 min 55.60 sec)
Records: 0  Duplicates: 0  Warnings: 0

-rw-rw----. 1 mysql mysql 180M 1月  12 13:40 test.ibd      #####压缩率51%
     
#设置KEY_BLOCK_SIZE=4
(product)root@localhost [sakila]> alter table test KEY_BLOCK_SIZE=4;
Query OK, 0 rows affected (7 min 24.52 sec)
Records: 0  Duplicates: 0  Warnings: 0

-rw-rw----. 1 mysql mysql 172M 1月  11 21:09 test.ibd      #####压缩率53.2%

#设置KEY_BLOCK_SIZE=8
(product)root@localhost [sakila]> alter table test KEY_BLOCK_SIZE=8;
Query OK, 0 rows affected (5 min 16.34 sec)
Records: 0  Duplicates: 0  Warnings: 0

-rw-rw----. 1 mysql mysql 172M 1月  11 21:00 test.ibd      #####压缩率53.2%

#设置KEY_BLOCK_SIZE=16
(product)root@localhost [sakila]> alter table test KEY_BLOCK_SIZE=16;
Query OK, 0 rows affected (2 min 47.48 sec)
Records: 0  Duplicates: 0  Warnings: 0

-rw-rw----. 1 mysql mysql 336M 1月  12 13:54 test.ibd      #####压缩率8.6%

mysql表数据压缩_数据

【总结】：通过以上测试可知，当KEY_BLOCK_SIZE的值设置为4或者8时，压缩效果最好，设置为16效果最差，因为页的默认值16K。通常我是设置为8。