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