MySQL 压缩的使用场景和解决方案

当前第2页 返回上一页

4、压缩效果

从上面截图可以看出压缩效果比较好，针对 text、char、varchr、blob 等，如果里面重复的数据越多压缩效果就越好。

三、InnoDB 表压缩方案解决方案

1、适用场景

采用压缩表一般都用在由于数据量太大，磁盘空间不足，负载主要体现在 IO 上，而服务器的 CPU 又有比较多的余量的场景。

2、表压缩简介 a、为什么需要压缩

目前很多表都支持压缩，比如 Myisam、InnoDB、TokuDB、MyRocks 。由于使用 InnoDB 主要是不需要做什么改动，对线上完全透明，压缩方案也非常成熟，因此这里只对 InnoDB 做详细说明。对于 TokuDB 和 MyRocks 的压缩方案将在 MySQL 的压缩方案（二）中撰文说明。

在 SSD 没有大量横行的时候，数据库几乎都是 IO 负载型的，在 CPU 有大量余量的时候，磁盘 IO 的瓶颈就已经凸显出来。而数据的大量存储，尤其是日志型数据和监控类型的数据，会导致磁盘空间快速增长。硬盘不够用也会在很多业务中凸显出来。一种比较好的方式就诞生了，那就是通过牺牲少量 CPU 资源，采用压缩来减少磁盘空间占用，以及优化 IO 和带宽。尤其针对读多些少的业务。

SSD 出来后，数据库的 IO 负载有所降低，但是对于磁盘空间的问题还是没有很好的解决。因此压缩表使用还是非常的广泛。这也就是为什么那么多的引擎都支持压缩的原因。而 innodb 在 MySQL 5.5 的时候就支持了压缩功能，只是压缩比比较低，通常在 50%左右。而 tokuDB 能达到 80%左右，MyRocks 的压缩比能达到 70%左右。

注意：压缩比和你存储的数据组成有很大的关系，并不是所有的数据都能达到上面所说的压缩比。如果大部分都是字符串，并且重复的数据比较多，压缩比会很好。

b、innodb 的压缩介绍

使用 innodb 压缩的前提条件是，innodb_file_per_table 这个参数要启用，innodb_file_format 这个参数设置成 Barracuda。

你可以使用 ROW_FORMAT=COMPRESSED 来 create 或者 alter 表来开启 innodb 的压缩功能，如果没有指定 KEY_BLOCK_SIZE 的大小，默认 KEY_BLOCK_SIZE 为 innodb_page_size 大小的一半，也可以通过指定 KEY_BLOCK_SIZE=n 参数来开启 innodb 的压缩功能，n 可以为 1、2、4、8、16，单位是 K。n 的值越小，压缩比越高，消耗的 CPU 资源也越多。注意 32K 或者 64K 的页不支持压缩。启用压缩后，索引数据也同样会被压缩。

你也可以通过调整 innodb_compression_level 来设置压缩的级别，级别从 1~9，默认是 6。级别越低，意味着压缩比越高，同时也意味着需要更多的 CPU 资源。

c、压缩算法

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

d、压缩表在 buffer_pool 中如何处理

在 buffer_pool 缓冲池中，压缩的数据通过 KEY_BLOCK_SIZE 的大小的页来保存，如果要提取压缩的数据或者要更新压缩数据对应的列，则会创建一个未压缩页来解压缩数据，然后在数据更新完成后，会将为压缩页的数据重新写入到压缩页中。内存不足的时候，MySQL 会讲对应的未压缩页踢出去。因此如果你启用了压缩功能，你的 buffer_pool 缓冲池中可能会存在压缩页和未压缩页，也可能只存在压缩页。不过可能仍然需要将你的 buffer_pool 缓冲池调大，以便能同时能保存压缩页和未压缩页。

MySQL 采用最少使用（LRU）算法来确定将哪些页保留在内存中，哪些页剔除出去，因此热数据会更多地保留在内存中。当压缩表被访问的时候，MySQL 使用自适应的 LRU 算法来维持内存中压缩页和非压缩页的平衡。当系统 IO 负载比较高的时候，这种算法倾向于讲未压缩的页剔除，一面腾出更多的空间来存放更多的压缩页。当系统 CPU 负载比较高的时候，MySQL 倾向于将压缩页和未压缩页都剔除出去，这个时候更多的内存用来保留热的数据，从而减少解压的操作。

e、如何评估 KEY_BLOCK_SIZE 是否合适

为了更深入地了解压缩表对性能的影响，在 Information Schema 库中有对应的表可以用来评估内存的使用和压缩率等指标。INNODB_CMP 是收集的是某一类的 KEY_BLOCK_SIZE 压缩表的整体状况的信息，汇总的是所有 KEY_BLOCK_SIZE 压缩表的统计。而 INNODB_CMP_PER_INDEX 表则是收集各个表和索引的压缩情况信息，这些信息对于在某个时间评估某个表的压缩效率或者诊断性能问题很有帮助。INNODB_CMP_PER_INDEX 表的收集会导致系统性能受到影响，必须 innodb_cmp_per_index_enabled 选项才会记录，生产环境最好不要开启。

我们可以通过观察 INNODB_CMP 表的压缩失败情况，如果失败比较多，则需要调大 KEY_BLOCK_SIZE。一般建议 KEY_BLOCK_SIZE 设置为 8。

3、方案实践

a、设置好 innodb_file_per_table 和 innodb_file_format 参数

SET GLOBAL innodb_file_per_table=1;SET GLOBAL innodb_file_format=Barracuda;

b、创建对应的压缩表

代码如下:

CREATE TABLE compress_test (c1 INT PRIMARY KEY,content varchar(255)) ROW_FORMAT=COMPRESSEDKEY_BLOCK_SIZE=8;

如果是已经存在的表，则通过 alter 来修改，SQL 如下：

ALTER TABLE compress_test ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

4、压缩效果

压缩效果通过线上的一个监控的表修改为压缩后的文件大小来说明，压缩前后对比如下：

更多相关Mysql内容来自木庄网络博客

标签：Mysql

返回前面的内容

相关阅读 >>

mysql如何设置慢查询日志？

mysql如何修改字段为null

mysqld.exe有什么用

mysql所支持的数据类型与表字段约束类型的学习教程

一分钟带你了解mysql ddl

mysql如何批量删除数据库中的数据

mysql 语法整理介绍

mysql中什么是大表？什么是大事务？该如何处理？

mysql数据库服务器端核心参数详解和推荐配置

解决 sql 问题绝对能让你对 mysql 的理解更进一步！

更多相关阅读请进入《mysql》频道 >>

书籍

数据库系统概念 第6版

￥41.1元   机械工业出版社

本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。

转载请注明出处：木庄网络博客 » MySQL 压缩的使用场景和解决方案