LoginSignup
0
0
@fukamatsuinDB-plus

compress関数を使ったカラム圧縮の検討

Last updated at Posted at 2024-03-28

compress関数とは

MySQLには文字列を圧縮しその結果をバイナリ文字列として返すcompress()関数というものがあります。この関数を利用すると圧縮された文字列はバイナリ型となるためblob型のカラムなどに保存するのが一般的です。圧縮された文字列はuncompress()関数により解凍することができます。

そこで、この関数を使うことで容量が大きいカラム(今回はJSON型のカラムを想定)を小さく保持できるのではないか？ということを思いついたので検証します。

実験

comress_colにはlongblob型にして、JSON文字列をcompress()関数を利用して挿入します。
uncompress_colにはgenerated column(virtial) を使ってuncompress()を使って解凍するようにします。
ただ、uncompress(compress_col)ではバイナリ型であるため、バイナリ型をJSON型には直接CASTできません。
そこで一度文字列型へのCASTを間に挟む必要があります。

mysql> CREATE TABLE ct(
    ->     id INT PRIMARY KEY AUTO_INCREMENT,
    ->     compress_col longblob,
    ->     uncompress_col JSON as (cast(cast(uncompress(compress_col) as char ) as JSON)) VIRTUAL
    -> );
Query OK, 0 rows affected (0.02 sec)
 
mysql> INSERT INTO ct(compress_col) VALUES
    -> (compress(JSON_OBJECT('name', 'Joe', 'age', 24,'data', 'x'))),
    -> (compress(JSON_OBJECT('name', 'Sue', 'age', 32,'data', 'y'))),
    -> (compress(JSON_OBJECT('name', 'Pete', 'age', 40,'data','z'))),
    -> (compress(JSON_OBJECT('name', 'Jenny', 'age', 27,'data','w')));
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0
 
mysql> SELECT * FROM ct;
+----+----------------------------------------------------------------------------------------------------+-------------------------------------------+
| id | compress_col                                                                                       | uncompress_col                            |
+----+----------------------------------------------------------------------------------------------------+-------------------------------------------+
|  1 | 0x27000000789CAB564A4C4F55B2523032D151504A492C4904B2952A94809CBCC45C908492577EAA522D00CC1F0A57     | {"age": 24, "data": "x", "name": "Joe"}   |
|  2 | 0x27000000789CAB564A4C4F55B2523036D251504A492C4904B2952A95809CBCC45C9084527069AA522D00CC590A66     | {"age": 32, "data": "y", "name": "Sue"}   |
|  3 | 0x28000000789CAB564A4C4F55B2523031D051504A492C4904B295AA94809CBCC45C908452406A49AA522D00D71E0AC7   | {"age": 40, "data": "z", "name": "Pete"}  |
|  4 | 0x29000000789CAB564A4C4F55B2523032D751504A492C4904B295CA95809CBCC45C908492576A5E5EA5522D00E2EF0B3F | {"age": 27, "data": "w", "name": "Jenny"} |
+----+----------------------------------------------------------------------------------------------------+-------------------------------------------+
4 rows in set (0.00 sec)

期待通り、圧縮して挿入したJSONがuncompressにより復元して取得することが確認できました。

JSONの要素にIndexを貼りたい

JSONの特定の要素に対してIndexを貼りたい事があるかもしれません。
Genrated Column VirtualのままだとIndexを貼れないため、Indexを貼りたいカラム列を生成し、Indexを貼る必要があります。

mysql> SELECT json_extract(CAST(CAST(UNCOMPRESS(compress_col) as CHAR) as JSON),'$.age') FROM ct;
+----------------------------------------------------------------------------+
| json_extract(CAST(CAST(UNCOMPRESS(compress_col) as CHAR) as JSON),'$.age') |
+----------------------------------------------------------------------------+
| 24                                                                         |
| 32                                                                         |
| 40                                                                         |
| 27                                                                         |
+----------------------------------------------------------------------------+
4 rows in set (0.01 sec)
 
mysql> ALTER TABLE ct ADD column age int AS (json_extract(CAST(CAST(UNCOMPRESS(compress_col) as CHAR) as JSON),'$.age')) STORED;
Query OK, 4 rows affected (0.04 sec)
Records: 4  Duplicates: 0  Warnings: 0
 
mysql> ALTER TABLE ct ADD INDEX idx_age(age);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

ageに対してIndexを貼ることができました。これでageを条件にIndexを使って検索することができます。

どれくらい容量が浮くのか？

実際この圧縮はどれくらい容量が浮くのでしょうか。zlibはパターンや文字列が繰り返された場合は圧縮効率がよく、文字列が短かったりランダムな場合は圧縮が効きにくいことは知られています。

そこで、繰り返しの文字列を入れたパターンとランダムな文字列を入れたパターンで比較してみます。

繰り返しの文字列を入れた場合

mysql> CREATE TABLE t(id INT PRIMARY KEY AUTO_INCREMENT,json_col JSON);
Query OK, 0 rows affected (0.01 sec)
 
mysql> CREATE TABLE ct(
    -> id INT PRIMARY KEY AUTO_INCREMENT,
    -> compress_col longblob,
    -> uncompress_col JSON as (cast(cast(uncompress(compress_col) as char ) as JSON)) VIRTUAL
    -> );
Query OK, 0 rows affected (0.02 sec)
 
mysql> INSERT INTO t(json_col) VALUES
    -> (JSON_OBJECT('name', 'Joe', 'age', 24,'data', REPEAT('x', 60000))),
    -> (JSON_OBJECT('name', 'Sue', 'age', 32,'data', REPEAT('y', 60000))),
    -> (JSON_OBJECT('name', 'Pete', 'age', 40,'data', REPEAT('z', 60000))),
    -> (JSON_OBJECT('name', 'Jenny', 'age', 27,'data', REPEAT('w', 60000)));
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0
 
mysql> INSERT INTO ct(compress_col) VALUES
    -> (compress(JSON_OBJECT('name', 'Joe', 'age', 24,'data', REPEAT('x', 60000)))),
    -> (compress(JSON_OBJECT('name', 'Sue', 'age', 32,'data', REPEAT('y', 60000)))),
    -> (compress(JSON_OBJECT('name', 'Pete', 'age', 40,'data', REPEAT('z', 60000)))),
    -> (compress(JSON_OBJECT('name', 'Jenny', 'age', 27,'data', REPEAT('w', 60000))));
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0
 
mysql> INSERT INTO t(json_col) SELECT json_col FROM t; INSERT INTO ct(compress_col) SELECT compress_col FROM ct;
↑これを10回実施
...

mysql> INSERT INTO t(json_col) SELECT json_col FROM t; INSERT INTO ct(compress_col) SELECT compress_col FROM ct;
Query OK, 2048 rows affected (2.42 sec)
Records: 2048  Duplicates: 0  Warnings: 0
 
Query OK, 2048 rows affected (2.35 sec)
Records: 2048  Duplicates: 0  Warnings: 0
 
 
$ sudo ls -althS ./db1 | grep "t.ibd"
-rw-r-----  1 mysql mysql 260M Mar 19 11:06 t.ibd
-rw-r-----  1 mysql mysql 9.0M Mar 19 11:06 ct.ibd

繰り返しの文字列は大きな差が出ています。

行数 t.ibd ct.ibd
4096 260M 9M
8192 528M 9M
16384 1.1G 11M
32768 2.1G 13M

ランダムな文字列を入れた場合

続いて先ほどのテーブルを使って今度は短めのランダム文字列で実験してみます。

mysql> truncate table t;
Query OK, 0 rows affected (0.13 sec)
 
mysql> truncate table ct;
Query OK, 0 rows affected (0.03 sec)
 
mysql> INSERT INTO t(json_col) VALUES
    -> (JSON_OBJECT('name', 'Joe', 'age', 24,'data', substring(MD5(RAND()) FROM 1 FOR 36))),
    -> (JSON_OBJECT('name', 'Sue', 'age', 32,'data', substring(MD5(RAND()) FROM 1 FOR 36))),
    -> (JSON_OBJECT('name', 'Pete', 'age', 40,'data', substring(MD5(RAND()) FROM 1 FOR 36))),
    -> (JSON_OBJECT('name', 'Jenny', 'age', 27,'data', substring(MD5(RAND()) FROM 1 FOR 36)));
 
mysql> INSERT INTO ct(compress_col) VALUES
    -> (compress(JSON_OBJECT('name', 'Joe', 'age', 24,'data', substring(MD5(RAND()) FROM 1 FOR 36)))),
    -> (compress(JSON_OBJECT('name', 'Sue', 'age', 32,'data', substring(MD5(RAND()) FROM 1 FOR 36)))),
    -> (compress(JSON_OBJECT('name', 'Pete', 'age', 40,'data', substring(MD5(RAND()) FROM 1 FOR 36)))),
    -> (compress(JSON_OBJECT('name', 'Jenny', 'age', 27,'data', substring(MD5(RAND()) FROM 1 FOR 36))));
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0
 
mysql> INSERT INTO t(json_col) SELECT json_col FROM t; INSERT INTO ct(compress_col) SELECT compress_col FROM ct;
↑これを数回繰り返す。
行数 t.ibd ct.ibd
524288 64M 64M
1048576 116M 120M
2097152 224M 232M
4194304 440M 456M

圧縮がほとんど効いてないですね。逆に、圧縮したテーブルのibdファイルのほうが少し大きくなりました。

注意点

JSONデータを圧縮して挿入するときにいくつか注意点があります。

  • COMRESS()でデータを挿入、更新する必要があるため、開発や運用の手間が増える
  • CASTで使えるCHARの文字数の最大が65535文字までなのでそれ以上のJSONを入れようとすると以下のワーニングが表示され、正常に解凍できない（データが壊れる）
Warning (Code 1259): ZLIB: Input data corrupted
  • CASTを2回挟んだ後JSONに変換なのでそれなりにCPUを使うと思われる

まとめ

今回はGeneralted columnとcomrepss関数を使って特定のカラムを圧縮させる方法を試してみました。実験をしてみて、データの生成部分がすごくややこしいため、運用には不向きかなと思います。
データ容量を減らすための小技としてこういうやり方もあるという程度にとどめておいたほうが良さそうです。

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0