MySQL의 압축 사용 시나리오 및 솔루션

데이터 분포 특성에 따라 공간 압축의 효율성이 결정됩니다. 저장된 데이터의 반복률이 높을수록 일반적으로 문자 유형데이터(CHAR, VARCHAR, TEXT 또는 BLOB)의 압축률이 높아집니다. 일부 바이너리 데이터 또는 이미 압축된 일부 데이터의 압축률은 그리 좋지 않습니다.

소개

압축 전송 프로토콜, 압축 열 솔루션 및 압축 테이블 솔루션을 포함하여 MySQL 압축의 사용 시나리오 및 솔루션을 설명합니다.

MySQL 압축 관련 콘텐츠의 경우 다음과 같은 압축 관련 시나리오를 생각해 볼 수 있습니다.

1. 클라이언트와 서버 간에 전송되는 데이터의 양이 너무 커서 대역폭을 절약하기 위해 압축해야 합니다

2. MySQL의 특정 열에 데이터가 많아 특정 열의 데이터만 압축합니다

3. MySQL의 하나 또는 여러 테이블에 데이터가 너무 많아서 테이블 데이터를 압축해야 합니다. 디스크 공간 사용량을 줄이기 위해 압축하고 저장합니다

이것은 MySQL 측에서 몇 가지 좋은 해결책이 있습니다. 첫 번째 문제의 경우 MySQL의 압축 프로토콜을 사용하여 두 번째 문제를 해결할 수 있습니다. 가장 복잡한 문제의 경우 세 번째 문제는 현재 myisam, innodb, tokudb 및 MyRocks와 같은 엔진에서 모두 테이블 압축을 지원합니다. 이 기사에서는 MySQL 압축 메커니즘과 관련된 문제에 대해 자세히 설명합니다. 다음은 주요 내용입니다.

1. MySQL 압축 프로토콜 소개

1. 적용 가능한 시나리오

MySQL 압축 프로토콜에 적합한 시나리오 MySQL입니다. 서버와 클라이언트 간에 전송되는 데이터의 양이 크거나 사용 가능한 대역폭이 높지 않습니다. 일반적인 시나리오는 다음 두 가지입니다.

a 데이터를 내보낼 때와 같이 대역폭이 부족합니다. );

b. 복사할 때 binlog의 양이 너무 큽니다. 로그 압축 및 복사를 위해slave_compressed_protocol 매개변수를 활성화하세요.

2. 압축 프로토콜 소개

압축 프로토콜은 MySQL 통신 프로토콜의 일부입니다. 데이터 전송을 위해 압축 프로토콜을 활성화하려면 MySQL 서버와 클라이언트가 모두 zlib 알고리즘을 지원해야 합니다. 압축 프로토콜을 활성화하면 CPU 로드가 약간 증가합니다. -C 매개변수 또는 --compress=true 매개변수를 사용하여 클라이언트의 압축 기능을 활성화하려면 압축 프로토콜 활성화를 사용합니다. -C 또는 압축=true 옵션이 활성화된 경우 서버 세그먼트에 연결할 때 서버 기능 플래그 비트 0x0020(CLIENT_COMPRESS)이 서버와의 협상 후(3번의 핸드셰이크 후) 전송됩니다. 지원됩니다. 압축 사용으로 인해 데이터 패킷의 형식이 다음과 같이 변경됩니다.

압축되지 않은 데이터 패킷 형식:

압축된 데이터 패킷 형식:

압축된 데이터 패킷 형식 압축된 데이터그램 형식과 압축되지 않은 데이터그램 형식이 있습니다. 이는 CPU 오버헤드를 줄이기 위해 MySQL에서 수행한 최적화입니다. 내용이 50바이트 미만이면 압축되지 않고, 50바이트보다 크면 압축 기능이 활성화됩니다. 구체적인 규칙은 다음과 같습니다.

세 번째 필드의 값이 0x00이면 현재 패키지가 압축되지 않았음을 의미하므로 n * 바이트의 내용은 1 * 바이트, n * 바이트, 즉 요청 유형 * byte 的内容为 1 * byte,n * byte，即请求类型和请求内容。

当第三个字段的值大于 0x00 的时候，表示当前包已采用 zlib 压缩，因此使用的时候需要对 n * byte 进行解压，解压后内容为 1 * byte,n *을 입력하고 콘텐츠를 요청하세요.

세 번째 필드의 값이 0x00보다 큰 경우 현재 패키지가 zlib로 압축되었음을 의미하므로 사용 시 n * 바이트를 압축 해제해야 하며 압축 해제된 내용은 1 * 바이트, n * 바이트이며 이는 요청 유형 및 요청 내용입니다.

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 매개변수를 활성화하면 해당 매개변수가 활성화됩니다. 괜찮아요.

4. 압축 효과

MySQLdump에서 --compress 옵션을 사용하면 압축 전송 효과를 관찰할 수 있고, 마스터-슬레이브 복제에서는 Slave_compressed_protocol 매개 변수를 사용하면 압축 전송 효과를 관찰할 수 있습니다. 효과를 확인하세요. 더 이상 스크린샷이 없습니다.

2. MySQL 열 압축 솔루션

MySQL은 현재 이미지 내 Tencent의 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、压缩效果

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

위 내용은 MySQL의 압축 사용 시나리오 및 솔루션의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!