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

导语

描述 MySQL 压缩的使用场景和解决方案,包括压缩传输协议、压缩列解决方案和压缩表解决方案。

提到 MySQL 压缩相关的内容,我们能想到的可能是如下几种和压缩相关的场景:

1、客户端和服务器之间传输的数据量太大,需要进行压缩,节约带宽

2、MySQL 某个列的数据量大,只针对某个列的数据压缩

3、MySQL 某个或者某几个表数据太多,需要将表数据压缩存放,减少磁盘空间的占用

这几个问题在 MySQL 侧都有很好的解决方案 ,针对第 1 个问题,可以使用 MySQL 的压缩协议解决;针对第 2 个问题,可以采用 MySQL 的压缩和解压函数完美解决;而针对最复杂的第 3 个问题,则可以在引擎层面进行解决,目前 myisam、innodb、tokudb、MyRocks 等引擎都支持表的压缩。本篇文章要详细讨论的就是此类关于 MySQL 压缩机制相关 的问题,下面是主要的内容:

一、MySQL 压缩协议介绍

1、适用场景

MySQL 压缩协议适合的场景是 MySQL 的服务器端和客户端之间传输的数据量很大,或者可用带宽不高的情况,典型的场景有如下两个:

a、查询大量的数据,带宽不够(比如导出数据的时候);

b、复制的时候 binlog 量太大,启用 slave_compressed_protocol 参数进行日志压缩复制。

2、压缩协议简介

压缩协议是 MySQL 通信协议的一部分,要启用压缩协议进行数据传输,需要 MySQL 服务器端和客户端都支持 zlib 算法。启动压缩协议会导致 CPU 负载略微上升。使用启用压缩协议使用-C 参数或者 --compress=true 参数启动客户端的压缩功能。如果启用了-C 或者 compress=true 选项,那么在连接到服务器段的时候,会发送 0x0020(CLIENT_COMPRESS)的服务器权能标志位,和服务器端协商通过后(3 次握手以后),就支持压缩协议了。由于采用压缩,数据包的格式会发生变化,具体的变化如下:

未压缩的数据包格式:

压缩后的数据包格式:

大家可能留意到压缩后的数据报格式有压缩和未压缩之分,这个是 MySQL 为了较少 CPU 开销而做的一个优化。如果内容小于 50 个字节的时候,就不对内容进行压缩,而大于 50 字节的时候,才会启用压缩功能。具体的规则如下:

当第三个字段的值等于 0x00 的时候,表示当前包没有压缩,因此 n * byte 的内容为 1 * byte,n * byte,即请求类型和请求内容。

当第三个字段的值大于 0x00 的时候,表示当前包已采用 zlib 压缩,因此使用的时候需要对 n * byte 进行解压,解压后内容为 1 * byte,n * byte,即请求类型和请求内容。

3、方案实践

在客户端连接的时候加上-C 或者--compress=true 参数。如果是对同步添加压缩协议支持的时候,则需要配置 slave_compressed_protocol=1。下面是采用压缩协议连接 MySQL 服务端的范例:

MySQL -h hostip -uroot -p password --compress

MySQLdump -h hostip -uroot -p password -default-character-set=utf8 --compress --single-transaction dbname tablename > tablename.sql

如果需要在主从复制中启用压缩传输,则在从机开启 slave_compressed_protocol=1 参数就 OK。

4、压缩效果

可以通过在 MySQLdump 中使用--compress 选项来观察压缩传输的效果,也可以通过主从复制中已用 slave_compressed_protocol 参数来观察压缩传输的效果,很容易看出效果,这里不再截图说明。

二、MySQL 列压缩解决方案

MySQL 针对列的压缩目前直接的方案并不支持,映象中腾讯的 TMySQL 可以直接针对列的压缩。这里主要介绍一个曲线救国的办法,那就是在业务层面使用 MySQL 提供的压缩和解压函数来针对列进行压缩和解压操作。也就是要对某一列做压缩,就需要在写入的时候调用 COMPRESS 函数对那个列的内容进行压缩,然后存放到对应的列。读取的时候,使用 UNCOMPRESSED 函数对压缩的内容进行解压缩。

1、适用场景

针对 MySQL 中某个列或者某几个列数据量特别大,一般都是 varchar、text、char 等数据类型。

2、压缩函数简介

MySQL 的压缩函数 COMPRESS 压缩一个字符串,然后返回一个二进制串。使用该函数需要 MySQL 服务端支持压缩,否则会返回 NULL,压缩字段最好采用 varbinary 或者 blob 字段类型保存。使用 UNCOMPRESSED 函数对压缩过的数据进行解压。注意,采用这种方式需要在业务侧做少量改造。压缩后的内容存储方式如下:

a、空字符串就以空字符串存储

b、非空字符串存储方式为前 4 个 bype 保存未压缩的字符串,紧接着保存压缩的字符串

3、方案实践

字段压缩方案涉及到的几个相关的函数如下:

压缩函数

COMPRESS()

解压缩函数

UNCOMPRESS()

字符串长度函数

LENGTH()

未解压字符串长度函数

UNCOMPRESSED_LENGTH()

实践步骤:

a、创建一张测试表

CREATE TABLE IF NOT EXISTS `test`.`test_compress` (

`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',

`content` blob NOT NULL COMMENT '内容列',

PRIMARY KEY (`id`)

 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='压缩测试表';

b、网表中插入压缩的数据

insert into `test`.`test_compress`(content) values(COMPRESS(REPEAT('a',1000)));

c、读取压缩的数据

select UNCOMPRESS(content) from `test`.`test_compress`;

d、查询对应的长度和内容

复制代码 代码如下:

SELECT UNCOMPRESSED_LENGTH(content) AS length, LENGTH(content) AS compress_length, UNCOMPRESS(content), content FROM `test`.`test_compress`

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、压缩效果

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

时间: 2017-06-11

MySQL实现MYISAM表批量压缩的方法

本文实例讲述了MySQL实现MYISAM表批量压缩的方法.分享给大家供大家参考,具体如下: 关于对MYISAM表的压缩,可以使用myisampack和myisamchk完成(myisampack完之后必须进行myisamchk才能使用压缩后的表,而且是只读的), 其详细地用法可以参考官方文档: http://dev.mysql.com/doc/refman/5.1/zh/client-side-scripts.html. 这两个操作需要谨慎使用,在压缩之前需要确认mysqld已关闭或者要压缩的表

mysql数据库备份命令分享(mysql压缩数据库备份)

备份MySQL数据库的命令 复制代码 代码如下: mysqldump -hhostname -uusername -ppassword databasename > backupfile.sql 备份MySQL数据库为带删除表的格式 备份MySQL数据库为带删除表的格式,能够让该备份覆盖已有数据库而不需要手动删除原有数据库. 复制代码 代码如下: mysqldump -–add-drop-table -uusername -ppassword databasename > backupfile

MySQL 加密/压缩函数

这些问题可能导致数据值的改变.一般而言,上述问题可能在你使用非二进制串数据类型(如char,varchar,text等数据类型)的情况下发生. AES_ENCRYPT()和AES_DECRYPT() AES_ENCRYPT()和AES_DECRYPT()可以加密/解密使用官方AES算法的数据.该算法使用128位密钥来编码,但用户可以将其扩展到256位.MySQL选用128位密钥,因为这样算法实现更快,而且对大多数用户而言它也足够安全了. AES_ENCRYPT(str,key_str)函数加密一

phpmyadmin MySQL 加密配置方法

以版本phpMyAdmin-2.6.1.tar.gz为例 先解压phpMyAdmin-2.6.1.tar.gz到/usr/local/apache2/htdocs, 得到文件夹phpMyAdmin-2.6.1,将其重命名为phpmyadmin,(这样在以后的操作中将会变的简便). 进入到phpmyadmin文件夹,用vim命令打开config.inc.php. 即#vim config.inc.php. 加密只需要修改两个部分: 1. 找到 $cfg['Servers'][$i]['auth_t

MySQL加密和解密实例详解

MySQL加密和解密实例详解 数据加密.解密在安全领域非常重要.对程序员而言,在数据库中以密文方式存储用户密码对入侵者剽窃用户隐私意义重大. 有多种前端加密算法可用于数据加密.解密,下面我向您推荐一种简单的数据库级别的数据加密.解密解决方案.以MySQL数据库为例,它内建了相应的加密函数(AES_ENCRYPT() )和解密函数(AES_DECRYPT()). 在建表的时候,要注意字段的类型.如下图所示: 在表中插入加密数据 上面的插入语句有三个字段,"用户名"."密码&qu

利用MySQL加密函数保护Web网站敏感数据的方法分享

如果您正在运行使用MySQL的Web应用程序,那么它把密码或者其他敏感信息保存在应用程序里的机会就很大.保护这些数据免受黑客或者窥探者的获取是一个令人关注的重要问题,因为您既不能让未经授权的人员使用或者破坏应用程序,同时还要保证您的竞争优势.幸运的是,MySQL带有很多设计用来提供这种类型安全的加密函数.本文概述了其中的一些函数,并说明了如何使用它们,以及它们能够提供的不同级别的安全. 双向加密 就让我们从最简单的加密开始:双向加密.在这里,一段数据通过一个密钥被加密,只能够由知道这个密钥的人来

MySQL 密码设置

一个重要的应用就是如何在遗忘root用户密码的时候修改密码,使用的方法是启动MySQL服务器时忽略加载授权表.由MySQL使用用户名和口令的方法与Unix或Windows使用的方式有很多不同之处: ·MySQL 使用于认证目的的用户名,与Unix用户名(登录名字)或Windows用户名无关.缺省地,大多数MySQL客户尝试使用当前 Unix用户名作为MySQL用户名登录,但是这仅仅为了方便.客户程序允许用-u或--user选项指定一个不同的名字,这意味着无论如何你不能使得一个数据库更安全,除非所

安装mysql8.0.11及修改root密码、连接navicat for mysql的思路详解

1.1. 下载: 官网下载zip包,我下载的是64位的: 下载地址:https://dev.mysql.com/downloads/mysql/ 下载zip的包: 下载后解压:(解压在哪个盘都可以的) 我放在了这里 E:\web\mysql-8.0.11-winx64 ,顺便缩短了文件名,所以为 E:\web\mysql-8.0.11. 1.3. 生成data文件: 以管理员身份运行cmd 程序--输入cmd 找到cmd.exe 右键以管理员身份运行 进入E:\web\mysql-8.0.11\

MySQL数据库对敏感数据加密及解密的实现方式

大数据时代的到来,数据成为企业最重要的资产之一,数据加密的也是保护数据资产的重要手段.本文主要在结合学习通过MySQL函数及Python加密方法来演示数据加密的一些简单方式. 1. 准备工作 为了便于后面对比,将各种方式的数据集存放在不同的表中. 创建原始明文数据表 /* 创建原始数据表 */ CREATE TABLE `f_user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL, `tel` v

mysql双向加密解密方式用法详解

如果你使用的正是mysql数据库,那么你把密码或者其他敏感重要信息保存在应用程序里的机会就很大.保护这些数据免受黑客或者窥探者的获取是一个令人关注的重要问题,因为您既不能让未经授权的人员使用或者破坏应用程序,同时还要保证您的竞争优势.幸运的是,MySQL带有很多设计用来提供这 种类型安全的加密函数.本文概述了其中的一些函数,并说明了如何使用它们,以及它们能够提供的不同级别的安全. 双向加密 就让我们从最简单的加密开始:双向加密.在这里,一段数据通过一个密钥被加密,只能够由知道这个密钥的人来解密.

MySQL 整表加密解决方案 keyring_file详解

说明 MySql社区版从5.7.11开始支持基于表的数据加密方案,模块名为keyring_file,支持加密整张表.这种是加密方式其实是基于文件加密的,一旦mysqld读取key启动后,将会解密整张表的数据,在mysql服务内,读取的数据都是解密后的,也就是说对客户端而言是无感知的.而这个key是本地存放的,mysql服务拥有读写这个key的权限. 总体看这种方案不太安全,原因是数据库文件是加密的,但只要能有mysql服务的账户,那么访问数据都是解密后的,加密不攻自破.而且解密key也是本地存放