UUUMエンジニアブログ

UUUMのエンジニアによる技術ブログです

JSON型にindexも！MySQL 5.7のGenerated Columnsの可能性について探る

Database インフラ

nazoです。

今回はMySQL 5.7の新機能であるGenerated Columnsについて紹介したいと思います。

Generated Columnsとは？

簡単に言うと、トリガーで特定のカラムにデータを入れるのを簡単に定義する方法みたいなもので、カラム定義時にAS 計算式と書くことで、そのカラムの値が該当の計算結果になります。

この機能はMySQL 5.7.6から追加されました。日本語では「生成列」と呼ぶこともあるようです。

基本的な使い方

リファレンスに書いてあるクエリを実行してみましょう。

mysql> CREATE TABLE triangle (
    ->   sidea DOUBLE,
    ->   sideb DOUBLE,
    ->   sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb))
    -> );
Query OK, 0 rows affected (0.11 sec)

mysql> INSERT INTO triangle (sidea, sideb) VALUES(1,1),(3,4),(6,8);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

普通に追加されました。ここでsidecがGenerated Columnsに該当します。INSERT文ではsideaとsidebにしかデータを入れていません。

ではsidecの中身はどうなっているでしょうか。

mysql> select * from triangle;
+-------+-------+--------------------+
| sidea | sideb | sidec              |
+-------+-------+--------------------+
|     1 |     1 | 1.4142135623730951 |
|     3 |     4 |                  5 |
|     6 |     8 |                 10 |
+-------+-------+--------------------+
3 rows in set (0.00 sec)

このように、(SQRT(sidea * sidea + sideb * sideb))の計算結果がsidecカラムに自動的に入っているのがわかります。

書式とオプション

Generated Columnsは以下の書式で指定ができます。

col_name data_type [GENERATED ALWAYS] AS (expression)
  [VIRTUAL | STORED] [UNIQUE [KEY]] [COMMENT comment]
  [[NOT] NULL] [[PRIMARY] KEY]

GENERATED ALWAYSオプションは、そのカラムがGenerated Columnsであることを明示するために追加します。少なくともMySQL 5.7.15の時点ではオプションの有無による挙動の変化はないようです。DB2などでは他のオプションで「任意の値も入れることができるGenerated Columns」を指定できることもあるようなので、今後MySQLに同様の機能が入るのかもしれません。

Generated Columnsなカラムに対して更新をかけようとするとエラーになります。

mysql> update triangle set sidec = 1;
ERROR 3105 (HY000): The value specified for generated column 'sidec' in table 'triangle' is not allowed.

VIRTUAL | STORED指定は、生成した値をストレージに格納するかどうかを決めます。デフォルトはVIRTUALで、ストレージに格納せず、データ読み取りに評価されます。STOREDの場合はINSERTやUPDATE時にデータがストレージに格納されます。STOREDの場合はストレージの容量が増えますが、恐らくSTOREDのほうが読み取り時のパフォーマンスは高いのではないかと思います。特にVIRTUAL指定したGenerated Columnsのことを「仮想列」と訳している例もあります。

MySQL 5.7.8から、VIRTUALな計算後のデータに対してインデックスが貼れるようになっています。

mysql> INSERT INTO triangle (sidea, sideb) SELECT t1.sidea, t1.sideb from triangle t1, triangle t2, triangle t3, triangle t4, triangle t5, triangle t6, triangle t7, triangle t8;
Query OK, 6561 rows affected (0.16 sec)
Records: 6561  Duplicates: 0  Warnings: 0

わかりやすいようにデータを増殖させてみました。この状態で普通にEXPLAINしてみます。

mysql> explain select * from triangle where sidec = 5;                                                                                                                    +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | triangle | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 6564 |    10.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

ALLで検索されていることがわかりました。

インデックスを張ってみます。

mysql> alter table triangle add index (sidec);
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from triangle where sidec = 5;
+----+-------------+----------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+-------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | triangle | NULL       | ref  | sidec         | sidec | 9       | const | 2188 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+-------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

インデックスが効いているのがわかりました。

制限

Generated Columnsは残念ながらサブクエリには対応していないようです。また当然ですが、RANDOM()やNOW()のような実行結果が毎回変わるようなものは指定できません。

mysql> ALTER TABLE triangle ADD t DATETIME AS (NOW());
ERROR 3102 (HY000): Expression of generated column 't' contains a disallowed function.

基本的な考え方としては、「同一カラム内のデータの計算結果を別カラムに格納できる」とするのが良いでしょう。一応例外として、毎回結果が変わらない単純なスカラー値（1とか"a"とか）も入れることができますが、その用途で使うことはないと思います。

JSON型で使う

今までの例だとあまりメリットを感じないように思えますが、例えばこれが、同時期に入ったJSON型と組み合わせると大きく威力を発揮します。

mysql> CREATE TABLE jsontable (
    ->   id INTEGER,
    ->   json_data JSON,
    ->   user_id INTEGER GENERATED ALWAYS AS (json_unquote(json_extract(`json_data`,'$.user_id'))) VIRTUAL,
    ->   PRIMARY KEY(id)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO jsontable(id, json_data) VALUES(1, '{"name": "hoge", "user_id": 10}');
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO jsontable(id, json_data) VALUES(2, '{"name": "fuga", "user_id": 20}');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO jsontable(id, json_data) VALUES(3, '{"name": "piyo", "user_id": 30}');
Query OK, 1 row affected (0.00 sec)

mysql> ALTER TABLE jsontable ADD INDEX (user_id);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

この時点で大体想像できると思いますが、具体的な挙動は以下になります。

mysql> SELECT * FROM jsontable;
+----+---------------------------------+---------+
| id | json_data                       | user_id |
+----+---------------------------------+---------+
|  1 | {"name": "hoge", "user_id": 10} |      10 |
|  2 | {"name": "fuga", "user_id": 20} |      20 |
|  3 | {"name": "piyo", "user_id": 30} |      30 |
+----+---------------------------------+---------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM jsontable WHERE user_id = 10;
+----+---------------------------------+---------+
| id | json_data                       | user_id |
+----+---------------------------------+---------+
|  1 | {"name": "hoge", "user_id": 10} |      10 |
+----+---------------------------------+---------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM jsontable WHERE user_id = 10;
+----+-------------+-----------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | jsontable | NULL       | ref  | user_id       | user_id | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

このように、JSON型の特定の項目に対してインデックスを貼れるような感覚（というか実際に貼れる）になります。JSON型の場合は通常は構造が可変になることが多いと思いますし、JSONの値がある場合のみデータを足すというようなことは何かと大変なので、便利かと思います。また、当然ながらJSON以外にもXMLとかでも同様のことを行うことができます。

特定の順序の検索キーとして使う

ちょっと発想を変えると、検索条件を事前に用意することもできます。例えば、文字の長さでソートしたい場合とかを考えます。

CREATE TABLE lengthtable (
  id INTEGER,
  text VARCHAR(255),
  textlength INTEGER AS (CHAR_LENGTH(text)),
  PRIMARY KEY(id)
);

INSERT INTO lengthtable(id, text) VALUES(1, "tanaka");
INSERT INTO lengthtable(id, text) VALUES(2, "asada");
INSERT INTO lengthtable(id, text) VALUES(3, "mori");

ALTER TABLE lengthtable ADD INDEX(textlength);

文字数で並び替えをしてみます。（データ量が少なすぎてEXPLAINした時にALLになるのでFORCE INDEXを入れています）

mysql> select * from lengthtable force index (textlength) order by textlength;
+----+--------+------------+
| id | text   | textlength |
+----+--------+------------+
|  3 | mori   |          4 |
|  2 | asada  |          5 |
|  1 | tanaka |          6 |
+----+--------+------------+
3 rows in set (0.01 sec)

mysql> explain select * from lengthtable force index (textlength) order by textlength;
+----+-------------+-------------+------------+-------+---------------+------------+---------+------+------+----------+-------+
| id | select_type | table       | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------------+------------+-------+---------------+------------+---------+------+------+----------+-------+
|  1 | SIMPLE      | lengthtable | NULL       | index | NULL          | textlength | 5       | NULL |    3 |   100.00 | NULL  |
+----+-------------+-------------+------------+-------+---------------+------------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

このように、並び替えのためのキーにすることもできそうです。

ユニーク制約

Generated Columnsにもユニーク制約を入れることができ、違反する場合はエラーになります。

mysql> CREATE TABLE lengthtable (
    ->   id INTEGER,
    ->   text VARCHAR(255),
    ->   textlength INTEGER AS (CHAR_LENGTH(text)) UNIQUE,
    ->   PRIMARY KEY(id)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO lengthtable(id, text) VALUES(1, "tanaka");
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO lengthtable(id, text) VALUES(2, "asada");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO lengthtable(id, text) VALUES(3, "morita");
ERROR 1062 (23000): Duplicate entry '6' for key 'textlength'

普段では入れられないような制約を入れることができそうです。

その他

外部キーとして使う場合はSTOREDにして、ON UPADTE〜とかON DELETE〜とかを指定しないようにして設定する必要があるようです。

まとめ

ちょっと特殊なクエリを使わないとデータを取り出せないカラムに対して検索を行いたい場合や、テーブル内のデータだけの計算結果に対して検索を行いたい場合など、トリガーより簡単に指定できるGenerated Columnsの可能性はまだまだありそうです。皆さんも試してみてください。

UUUMでは、YouTube上の大量のデータをMySQLなどで操作したいDBエンジニアも募集しています。詳しくは以下をご覧下さい。

www.wantedly.com