tencent cloud

TencentDB for MySQL

DokumentasiTencentDB for MySQL

Column compression

Download
Mode fokus
Ukuran font
Terakhir diperbarui: 2026-06-17 17:23:16
Diterjemahkan oleh AI

Feature Introduction

Currently, there is compression for row formats and compression for data pages. However, in scenarios where a table contains some large fields and many small fields, with frequent read/write operations on the small fields and infrequent access to the large fields, these two compression methods can lead to significant unnecessary computational resource waste during read/write operations.
The column compression feature can compress infrequently accessed large fields while leaving frequently accessed small fields uncompressed. This approach not only reduces the storage space required for entire rows but also improves the efficiency of read/write operations.
For example, consider an employee table: create table employee(id int, age int, gender boolean, other varchar(1000) primary key (id)). When the small fields id, age, gender are accessed frequently, while the large field other is accessed infrequently, you can create the other column as a compressed column. Typically, only read/write operations on other trigger the compression and decompression of this column. Accessing other columns does not trigger its compression or decompression. This further reduces the storage size of row data, enabling faster access to frequently accessed small fields and achieving a further reduction in storage space for infrequently accessed large fields.
Note:
The parameter `cdb_column_compression_enabled` is the switch for the column compression feature.
The column compression feature in MySQL 5.7 is disabled by default. To use it, submit a ticket to enable the feature.
The column compression feature is enabled by default for MySQL 8.0 kernel version 20221215 and later.
Single-node (cloud disk) architecture instances do not support column compression.
Because TencentDB for MySQL version 8.0 incorporates the open-source collaborative version of column compression, which differs in implementation from the column compression in version 5.7, this section provides separate usage instructions for the column compression capabilities of both versions. You can click "MySQL 5.7 Column Compression" or "MySQL 8.0 Column Compression" to switch between and learn about them.
MySQL 5.7 Column Compression
MySQL 8.0 Column Compression

Supported Versions

Kernel version MySQL 5.7 20210330 or later

Applicable Scenarios

In scenarios where a table contains some large fields and many small fields, with frequent read/write operations on the small fields and infrequent access to the large fields, you can designate the large fields as compressed columns.

Usage Instructions

Supported data types:

1. BLOB (including TINYBLOB, MEDIUMBLOB, and LONGBLOB)
2. TEXT (including TINYTEXT, MEDIUMTEXT, and LONGTEXT)
3. VARCHAR
4. VARBINARY
Note:
The maximum length supported for LONGBLOB and LONGTEXT is 232-2, which is one byte less than the 232-1 supported by the official String Type Storage Requirements.

Supported DDL Syntax Types

Compared with the official table creation syntax, the definition of COLUMN_FORMAT in column_definition has been modified. Additionally, column compression only supports tables of the Innodb storage engine type.
      column_definition:
        data_type [NOT NULL | NULL] [DEFAULT default_value]
          [AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]
          [COMMENT 'string']
          [COLLATE collation_name]
          [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}|COMPRESSED=[zlib]]  # COMPRESSED compressed column keyword
          [STORAGE {DISK|MEMORY}]
          [reference_definition]
A simple example is provided below:
CREATE TABLE t1(
  id INT PRIMARY KEY,
  b BLOB COMPRESSED
);
The compression algorithm is omitted by default, and zlib is selected. You can also explicitly specify the compression algorithm keyword. Currently, only the zlib compression algorithm is supported.
CREATE TABLE t1(
  id INT PRIMARY KEY,
  b BLOB COMPRESSED=zlib
);
The supported DDL syntax is summarized as follows:
create table:
DDL
Compression Attribute Inherited or Not
CREATE TABLE t2 LIKE t1;
Y
CREATE TABLE t2 SELECT * FROM t1;
Y
CREATE TABLE t2(a BLOB) SELECT * FROM t1;
N
alter table:
DDL
Description
ALTER TABLE t1 MODIFY COLUMN a BLOB;
Converting a compressed column to a non-compressed column
ALTER TABLE t1 MODIFY COLUMN a BLOB COMPRESSED;
Converting a non-compressed column to a compressed column

Parameter Description

Parameter Name
Dynamically Provisioned
Type
Default Value
Valid Value
Description
cdb_column_compression_enabled
Yes
bool
FALSE
TRUE/FALSE
The switch for column compression. When the switch is disabled, creating tables with compression attributes is not allowed, while existing tables with compression attributes remain unaffected.
innodb_column_compression_zlib_wrap
Yes
bool
TRUE
TRUE/FALSE
If it is enabled, generates the zlib header and zlib footer for the data and performs an adler32 checksum.
innodb_column_compression_zlib_strategy
Yes
Integer
0
[0,4]
The compression policy used for column compression. The minimum value is: 0, and the maximum value is: 4. Values 0 to 4 correspond one-to-one with the zlib compression policies Z_DEFAULT_STRATEGY, Z_FILTERED, Z_HUFFMAN_ONLY, Z_RLE, and Z_FIXED, respectively.
Generally, Z_DEFAULT_STRATEGY is often optimal for text data, while Z_RLE is optimal for image data.
innodb_column_compression_zlib_level
Yes
Integer
6
[0,9]
The compression level used for column compression. The minimum value is: 0, and the maximum value is: 9. A value of 0 indicates no compression. A higher value results in smaller compressed data but longer compression time.
innodb_column_compression_threshold
Yes
Integer
256
[0, 0xffffffff]
The compression threshold used for column compression. The minimum value is: 1, and the maximum value is: 0xffffffff, in bytes. Only data with a length greater than or equal to this value is compressed. Otherwise, the original data remains unchanged, and only a compression header is added.
innodb_column_compression_pct
Yes
Integer
100
[1, 100]
The compression ratio used for column compression. The minimum value is: 1, and the maximum value is: 100, in percentage. Data is compressed only when the compressed data size / uncompressed data size is less than this value. Otherwise, the original data remains unchanged, and only a compression header is added.
Note:
Currently, users cannot modify the values of the above parameters directly. If they need to be modified, submit a ticket for modification.

New Status Description

Name
Type
Description
Innodb_column_compressed
Integer
Number of column compressions, including compressions in both uncompressed and compressed formats.
Innodb_column_decompressed
Integer
Number of column decompressions, including decompressions in both uncompressed and compressed formats.

New Error Description

Name
Scope
Description
Compressed column '%-.192s' can't be used in key specification
Specified compressed column name
Compression attributes cannot be specified for columns with indexes.
Unknown compression method: %s
Compression algorithm name specified in DDL statement
Specifying an illegal compression algorithm other than zlib during create table or alter table.
Compressed column '%-.192s' can't be used in column format specification
Specified compressed column name
In the same column, if the COLUMN_FORMAT attribute has already been specified, the compression attribute cannot be specified, where COLUMN_FORMAT is only used in NDB.
Alter table ... discard/import tablespace not support column compression
\\
Tables with column compression cannot execute the Alter table ... discard/import tablespace statement.

Performance

Overall performance is divided into two aspects: DDL and DML.
For DDL, sysbench is used for testing:
Column compression has a significant performance impact on DDL operations using the COPY algorithm. After compression, performance is 7 to 8 times slower than before.
The impact on inplace operations depends on the size of the data after compression. If the overall data size is reduced after compression, DDL performance is improved. Otherwise, performance is degraded to some extent.
For instant DDL, column compression has virtually no impact.
For DML: Consider the most common compression scenario (compression ratio 1:1.8). In this scenario, there is a table with 8 columns. One column is a large varchar type, with inserted data lengths uniformly random between 1 and 6000, and inserted characters randomly selected from 0-9 and a-b. The other columns are of char(60) or int type. In this case, there is an improvement of up to 10% for insert, delete, and query operations on non-compressed columns. However, there is a degradation of up to 10% for update operations on non-compressed columns, and a performance drop of up to 15% for update operations on compressed columns. This is because during the update process, MySQL first reads the row's value and then writes the updated value for that row. The entire update process triggers one decompression and compression, whereas insert and query operations only involve one compression or decompression.

Precautions

1. For logical export, the create table statement still includes keywords related to Compressed during export. Therefore, it is supported internally by TencentDB for MySQL during import. Other MySQL branches and the official edition:
If the official version number is earlier than 5.7.18, you can import it directly.
If the official version number is 5.7.18 or later, you need to remove the compression keywords after the logical export.
2. When DTS exports data to other clouds or users, incompatibility issues may arise during binlog synchronization. You can skip DDL statements containing compression keywords.
3. Column compression uses the zlib compression algorithm to compress data. However, applying column compression to data that is already compressed typically does not significantly improve the compression ratio. Taking JPEG images as an example, JPEG is a highly optimized lossy compression format that already compresses image data into a relatively small file size. Therefore, in practice, applying column compression to JPEG data yields suboptimal results.

Supported Versions

Kernel version MySQL 8.0 20221215 or later

Applicable Scenarios

In scenarios where a table contains some large fields and many small fields, with frequent read/write operations on the small fields and infrequent access to the large fields, you can designate the large fields as compressed columns.

Usage Instructions

Supported data types:

1. BLOB (including TINYBLOB, MEDIUMBLOB, and LONGBLOB)
2. TEXT (including TINYTEXT, MEDIUMTEXT, and LONGTEXT)
3. VARCHAR
4. VARBINARY
5. JSON
The syntax is as follows:
CREATE TABLE t1(
  id INT PRIMARY KEY,
  b BLOB COMPRESSED
);
or
CREATE TABLE t1(
  id INT PRIMARY KEY,
  b BLOB COLUMN_FORMAT COMPRESSED
);
Compression Threshold (Threshold)
The compression threshold is controlled by the parameter `innodb_min_column_compress_length`. The default value is 256. If the original size of a column exceeds the value of this parameter, the column is compressed. Otherwise, only a compression header is added, and the data is not actually compressed.
The supported DDL syntax is summarized as follows:
create table:
DDL
Compression Attribute Inherited or Not
CREATE TABLE t2 LIKE t1;
Y
CREATE TABLE t2 SELECT * FROM t1;
N
CREATE TABLE t2(a BLOB) SELECT * FROM t1;
N
alter table:
DDL
Description
ALTER TABLE t1 MODIFY COLUMN a BLOB;
Converting a compressed column to a non-compressed column.
ALTER TABLE t1 MODIFY COLUMN a BLOB COMPRESSED;
Converting a non-compressed column to a compressed column.

Parameter Description

Parameter Name
Dynamically Provisioned
Type
Default Value
Valid Value
Description
innodb_zlib_column_compression_level
Yes
UINT
6
[0-9]
zlib compression. 0 indicates no compression, 1 indicates the fastest compression, and 9 indicates the highest compression level. From 1 to 9, the compression speed decreases, but the compression ratio increases.
innodb_zstd_column_compression_level
Yes
UINT
3
[1-22]
zstd compression. 1 indicates the fastest compression, and 22 indicates the highest compression level. From 1 to 22, the compression speed decreases, but the compression ratio increases.
innodb_min_column_compress_length
Yes
UINT
256
［1,UINT_MAX32］
Controls the compression threshold, in bytes. If the original length of a column is greater than or equal to the value of this parameter, compression is performed. Otherwise, only a compression header is added, and the data is not actually compressed.

Supported Algorithms (Multiple Algorithms)

TencentDB for MySQL version 8.0 supports three compression algorithms: ZLIB, LZ4, and ZSTD. You can also omit the algorithm specification, in which case ZLIB is used as the default.
The syntax is as follows:
CREATE TABLE t1(
  id INT PRIMARY KEY,
  b BLOB COMPRESSED ALGORITHM = [ZLIB|LZ4|ZSTD]
);
or
CREATE TABLE t1(
  id INT PRIMARY KEY,
  b BLOB COLUMN_FORMAT COMPRESSED ALGORITHM = [ZLIB|LZ4|ZSTD]
);

Compression Algorithms and Compression Levels

1. ZLIB: Multiple compression levels are currently provided for ZLIB. The parameter is `innodb_zlib_column_compression_level`, with a value range of 0-9. A value of 0 indicates no compression, 1 indicates the fastest compression, and 9 indicates the highest compression level. The default value is 6.
2. LZ4: It aligns with MySQL's Page compression and does not support multiple compression levels for LZ4. When the LZ4 compression algorithm is used, note that the maximum original length for LZ4 compression is 231-1, while the maximum length for LONGBLOB is 232-1. If the original data length to be compressed is greater than or equal to 231, ZLIB compression is implicitly used.
3. ZSTD: ZSTD, also known as ZStandard, has three compression methods. The column compression supported here is the regular, non-dictionary, non-streaming compression. It provides multiple compression levels. The parameter is `innodb_zstd_column_compression_level`, with a value range of 1-22. A value of 1 indicates the fastest compression, and 22 indicates the highest compression level. The default value is 3.
Compression Attribute Display
The default compression algorithm is shown here: ALGORITHM = ZLIB.
CREATE TABLE t2 (a VARCHAR(100) COMPRESSED) ENGINE=InnoDB;

SHOW CREATE TABLE t2;

Precautions

1. For logical export, the create table statement still includes keywords related to Compressed during export. Therefore, it is supported internally by TencentDB for MySQL during import. Other MySQL branches and the official edition:
If the official version number is earlier than 8.0.22, you can import it directly.
If the official version number is 8.0.22 or later, you need to remove the compression keywords after the logical export.
2. When DTS exports data to other clouds or users, incompatibility issues may arise during binlog synchronization. In such cases, you can skip DDL statements containing compression keywords.
3. For physical backup, since the fields are already compressed within innodb during backup, the version used for this backup must also be a version with column compression.
4. Physical upgrade from MySQL 5.7 to MySQL 8.0 is not supported.
5. Column compression uses the ZLIB, LZ4, and ZSTD compression algorithms to compress data. However, applying column compression to data that is already compressed typically does not significantly improve the compression ratio. Taking JPEG images as an example, JPEG is a highly optimized lossy compression format that already compresses image data into a relatively small file size. Therefore, in practice, applying column compression to JPEG data yields suboptimal results.

Topik selanjutnya: Tutorial Pengguna

Bantuan dan Dukungan

Apakah halaman ini membantu?

masukan