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`

阅读剩余部分

相关阅读 >>

学习mysql事件如何调用存储过程的方法

如何登录mysql以及cmd如何连接mysql数据库？

mysql如何给表设置组合主键？

mysql怎么删除触发器

mysql中非常重要的索引操作

mysql中如何实现按照特定顺序排序

如何查看本机是否安装了mysql

mysql版本enterprise/community/cluster等版本的区别

windows下mysql详细安装过程及基本使用

mysql练习之2：运算符的使用

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

书籍

数据库系统概念 第6版

￥41.1元   机械工业出版社

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

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