§ 带字典的压缩列


per-column compression 功能是一个数据类型修饰符,独立于用户级 SQL 和 InnoDB 数据压缩,它会导致列中存储的数据在写入存储时压缩并在读取时解压缩。对于所有其他目的,数据类型与没有修饰符的数据类型相同,即不会创建新的数据类型。压缩是通过使用 zlib 库完成的。

此外,可以为每个压缩列预先定义一组字符串,以在相对较小的单个数据项上实现更好的压缩比。

此功能提供:

  • 使用静态字典的压缩方法,对由大量预定义单词(例如 JSON 或 XML)组成的文本数据提供更好的压缩率
  • 一种选择表中的列进行压缩的方法(与 InnoDB 行压缩方法相反)。

§ 规格

该功能仅限于 InnoDB 存储引擎和以下数据类型的列:

  • BLOB (包括 TINYBLOB 、MEDIUMBLOB、LONGBLOG)
  • TEXT (包括 TINYTEXT、 MEDUUMTEXT、LONGTEXT)
  • VARCHAR (包括 NATIONAL VARCHAR)
  • VARBINARY
  • JSON

压缩列是通过使用扩展现有 COLUMN_FORMAT 修饰符的语法来声明的:COLUMN_FORMAT COMPRESSED。如果将此修饰符应用于不支持的列类型或存储引擎,则会返回错误。

可以指定压缩:

  • 创建表时:
    • CREATE TABLE ... (..., foo BLOB COLUMN_FORMAT COMPRESSED, ...);
  • 当更改表并将列修改为压缩格式时:
    • ALTER TABLE ... MODIFY [COLUMN] ... COLUMN_FORMAT COMPRESSED
    • ALTER TABLE ... CHANGE [COLUMN] ... COLUMN_FORMAT COMPRESSED

与 MySQL 不同,压缩适用于生成的存储列。使用此语法扩展如下:

greatsql> CREATE TABLE t1(
       id INT,
       a BLOB,
       b JSON COLUMN_FORMAT COMPRESSED,
       g BLOB GENERATED ALWAYS AS (a) STORED COLUMN_FORMAT COMPRESSED WITH COMPRESSION_DICTIONARY numbers
     ) ENGINE=InnoDB;
1
2
3
4
5
6

要解压缩列,请指定 COMPRESSEDCOLUMN_FORMAT 以外的值: FIXEDDYNAMICDEFAULT 。如果 ALTER TABLE 中有列压缩/解压缩请求,则强制使用 COPY 算法。

两个新变量:innodb_compressed_columns_zip_levelinnodb_compressed_columns_threshold 已实现

§ 压缩字典支持

为了对相对较小的单个数据项实现更好的压缩比,可以预定义压缩字典,它是每个压缩列的一组字符串。

压缩字典可以表示为字符串形式的单词列表(逗号或任何其他字符可以用作分隔符,但不是必需的)。换句话说, a, bb, ccca bb cccabbccc 将具有相同的效果。然而,后者更为紧凑。引用符号引用由常规 SQL 引用处理。支持的最大字典长度为 32506 字节( zlib 限制)。

压缩字典存储在新的系统InnoDB表中。由于该表是数据字典类型,因此允许并发读取,但写入是序列化的,并且读取会被写入阻塞。不支持通过旧的读视图读取表,类似于InnoDB内部DDL事务。

与 innodb_force_recovery 变量交互

压缩字典操作被视为 DDL 操作,但当innodb_force_recovery 设置为 3 时例外:值小于 3 时,允许压缩字典操作,并且值 >= 3 ,它们是被禁止的。

§ 自定义压缩键

创建一个名为dictionary_data的变量,并将其值设置为字符串'one' 'two' 'three' 'four'

greatsql> SET @dictionary_data = 'one' 'two' 'three' 'four';
Query OK, 0 rows affected (0.00 sec)
1
2

创建一个名为numbers的压缩字典,并使用前面定义的dictionary_data变量作为字典的内容:

greatsql> CREATE COMPRESSION_DICTIONARY numbers (@dictionary_data);
Query OK, 0 rows affected (0.05 sec)
1
2

要创建同时支持压缩和压缩字典的表,应该运行:

greatsql> CREATE TABLE t1(
    id INT, 
    a BLOB COLUMN_FORMAT COMPRESSED, 
    b BLOB COLUMN_FORMAT COMPRESSED WITH COMPRESSION_DICTIONARY numbers 
) ENGINE=InnoDB;
Query OK, 0 rows affected (0.07 sec)
1
2
3
4
5
6

以下示例显示如何将 JSON 数据示例插入表中:

greatsql> SET @json_value =
'[\n'
' {\n'
' "one" = 0,\n'
' "two" = 0,\n'
' "three" = 0,\n'
' "four" = 0\n'
' },\n'
' {\n'
' "one" = 0,\n'
' "two" = 0,\n'
' "three" = 0,\n'
' "four" = 0\n'
' },\n'
' {\n'
' "one" = 0,\n'
' "two" = 0,\n'
' "three" = 0,\n'
' "four" = 0\n'
' },\n'
' {\n'
' "one" = 0,\n'
' "two" = 0,\n'
' "three" = 0,\n'
' "four" = 0\n'
' }\n'
']\n'
;
greatsql> INSERT INTO t1 VALUES(0, @json_value, @json_value);
Query OK, 1 row affected (0.05 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

§ 新增INFORMATION_SCHEMA表

此功能新增了两个新的 INFORMATION_SCHEMA

§ COMPRESSION_DICTIONARY

该表提供了内部压缩字典的视图。需要 SUPER 权限才能查询

查询该表结果如下:

greatsql> select * from INFORMATION_SCHEMA.COMPRESSION_DICTIONARY;
+--------------+-----------+----------------------------------+
| DICT_VERSION | DICT_NAME | DICT_DATA                        |
+--------------+-----------+----------------------------------+
|            1 | numbers   | 0x6F6E6574776F7468726565666F7572 |
+--------------+-----------+----------------------------------+
1 row in set (0.01 sec)
1
2
3
4
5
6
7
列名称 描述
DICT_VERSION 字典版本
DICT_NAME 字典名称
DICT_DATA 压缩字典字符串

§ COMPRESSION_DICTIONARY_TABLES

该表提供了内部表的视图,该内部表存储压缩字典和使用它们的列之间的映射。查询需要 SUPER 权限。

查询该表结果如下:

greatsql> select * from INFORMATION_SCHEMA.COMPRESSION_DICTIONARY_TABLES;
+--------------+------------+-------------+-----------+
| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | DICT_NAME |
+--------------+------------+-------------+-----------+
| test_db      | t1         | b           | numbers   |
+--------------+------------+-------------+-----------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
列名称 描述
TABLE_SCHEMA 表模式(库名)
TABLE_NAME 表名 来自 INFORMATION_SCHEMA.INNODB_TABLES 表
COLUMN_NAME 列名 来自INFORMATION_SCHEMA.INNODB_COLUMNS 表
DICT_NAME 字典名

§ 功能限制

压缩列不能在索引中使用(既不能单独使用,也不能作为组合键的一部分)。

CREATE TABLE t2 AS SELECT \* FROM t1 将创建一个包含压缩列的新表,而 CREATE TABLE t2 AS SELECT CONCAT(a,'') AS a FROM t1 将不会创建压缩列。

同时,执行 CREATE TABLE t2 LIKE t1 语句后 t2.a 将具有 COMPRESSED 属性。

具有压缩列的表不支持 ALTER TABLE ... DISCARD/IMPORT TABLESPACE

要导出和导入包含压缩列的表空间,请首先使用 ALTER TABLE ... MODIFY ... COLUMN_FORMAT DEFAULT 解压缩它们。

§ mysqldump 命令行参数

默认情况下,如果没有其他选项, mysqldump 将生成 GreatSQL 兼容的 SQL 输出。

所有 /\*!50633 COLUMN_FORMAT COMPRESSED \*//\*!50633 COLUMN_FORMAT COMPRESSED WITH COMPRESSION_DICTIONARY <dictionary> \*/ 都不会出现在转储中。

当指定新选项 enable-compressed-columns 时,所有 /\*!50633 COLUMN_FORMAT COMPRESSED \*/ 将保持不变,所有 /\*!50633 COLUMN_FORMAT COMPRESSED WITH COMPRESSION_DICTIONARY <dictionary> \*/ 将转换为 /\*!50633 COLUMN_FORMAT COMPRESSED \*/ 。在此模式下,转储将包含创建压缩列所需的 SQL 语句,但不包含字典。

当指定新的 enable-compressed-columns-with-dictionaries 选项时,转储将包含所有压缩列属性和压缩字典。

此外,在第一次使用这些字典的 CREATE TABLE 语句之前将添加以下字典创建片段。

/*!50633 DROP COMPRESSION_DICTIONARY IF EXISTS <dictionary>; */
/*!50633 CREATE COMPRESSION_DICTIONARY <dictionary>(...); */
1
2

两个新选项 add-drop-compression-dictionary 和skip-add-drop-compression-dictionary 将控制是否跳过上一段中的 /\*!50633 DROP COMPRESSION_DICTIONARY IF EXISTS <dictionary> \*/ 部分。默认情况下,将使用 add-drop-compression-dictionary 模式。

当同时指定 enable-compressed-columns-with-dictionaries 和 --tab=<dir> (每个表的单独文件)选项时,将使用以下片段在每个输出文件中创建必要的压缩字典(无论add-drop-compression-dictionary 和skip-add-drop-compression-dictionary 选项)。

/*!50633 CREATE COMPRESSION_DICTIONARY IF NOT EXISTS <dictionary>(...); */
1

§ 新增系统参数

§ innodb_compressed_columns_zip_level

System Variable Name innodb_compressed_columns_zip_level
Command-line YES
Config file YES
Variable Scope Global
Dynamic Variable YES
Data type Numeric
Default 6
Range 0-9

该变量用于指定压缩列的压缩级别。指定 0 将不使用压缩, 1 最快, 9 最佳压缩。默认值为 6

§ innodb_compressed_columns_threshold

System Variable Name innodb_compressed_columns_threshold
Command-line YES
Config file YES
Variable Scope Global
Dynamic Variable YES
Data type Numeric
Default 96
Range 1 - 2^64-1(或 32 位版本为 2^32-1)

默认情况下,如果插入的值的长度超过 innodb_compressed_columns_threshold 字节,则该值将被压缩。否则,它将以原始(未压缩)形式存储。

还请注意,由于某些数据的性质,压缩后的表示可能比原始值更长。在这种情况下,以压缩形式存储这些值是没有意义的,因为 GreatSQL 将不得不浪费内存空间和 CPU 资源来进行不必要的解压缩。因此,即使此类不可压缩值的长度超过innodb_compressed_columns_threshold它们也会以未压缩的形式存储(但仍会尝试压缩)。

对于用户事先已知其前 N 个字节的压缩率较差的值,可以调整此参数以跳过不必要的数据压缩尝试。

扫码关注微信公众号

greatsql-wx