MySQL の Generated Columnsについて
この記事はフラー Advent Calendar 2020 - Adventar の16日目の記事です。 15日目は sohei さん で 「オンボーディングHACKS」でした。
MySQL 5.7.6以降ではGenerated Columns という機能を使うことができます。 これは実カラムの値を計算した結果を格納するためのカラムを作る機能です。
CREATE TABLE `member` ( `id` INT AUTO_INCREMENT NOT NULL, `first_name` VARCHAR(10) NOT NULL, `last_name` VARCHAR(10) NOT NULL, `full_name` VARCHAR(255) AS (CONCAT(last_name, ' ', first_name)), PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4;
Generated Columnsをデフォルトの設定で使う場合は上記のfull_name の AS expr で計算したい式を追加するだけで大丈夫です。
INSERT INTO `member` (first_name, last_name) VALUES ('shoma', 'okamoto');
厳密なsyntaxは以下のようになっています
col_name data_type [GENERATED ALWAYS] AS (expr) [VIRTUAL | STORED] [NOT NULL | NULL] [UNIQUE [KEY]] [[PRIMARY] KEY] [COMMENT 'string']
実際にデータを投入してみて、SELECTしてみます。
mysql> SELECT * FROM `member`; +----+------------+-----------+---------------+ | id | first_name | last_name | full_name | +----+------------+-----------+---------------+ | 1 | shoma | okamoto | okamoto shoma | +----+------------+-----------+---------------+ 1 row in set (0.00 sec)
取得できました。簡単！ ﾔｯﾀﾈ!
Types
STORE と VIRTUAL という格納タイプが存在します。
デフォルトでは VIRTUAL になります。
それぞれの意味は以下の通りです。
STORED: 挿入/更新時に列の値を計算してストレージに保持します。インデックスを貼ることが可能です。
VIRTUAL: 取得時に都度、仮想列の値を計算します。ストレージには保持されません。
InnoDBのみ仮想列にセカンダリインデックスを貼ることが可能です。
制約
- サブクエリで実行することができない
- 生成列を
AUTO_INCREMENTで使えない - 計算元の指標に
AUTO_INCREMENTが使えない - 外部キーが仮想列を参照することができない
等の細かい制約があるため使うときにはしっかりと公式ドキュメントを読んだ方が良いでしょう。 おそらくシンプルな使い方しかできないんじゃないかなと思います。
どんなときに使うのか
複雑な条件文をシンプルにしたいとき
生成カラム（関数インデックス）にインデックスを貼りたいとき
関数を使った条件文は基本的にインデックスが効きませんが、Generated Columnsでは関数に対してインデックスを貼ることができます。 yoku0825 さんのブログに書いてある具体例がわかりやすかったです。
日々の覚書: MySQL 5.7.6のgenerated columnは関数インデックスの夢を見るか
気になった箇所
For stored generated columns, the disadvantage of this approach is that values are stored twice; once as the value of the generated column and once in the index.
STORED 生成列にインデックスを貼る場合は生成列を計算するときとインデックスを貼るときで2回計算が走るようなので、注意が必要です。
When a secondary index is created on a virtual generated column, generated column values are materialized in the records of the index. If the index is a covering index (one that includes all the columns retrieved by a query), generated column values are retrieved from materialized values in the index structure instead of computed “on the fly”.
理由は仮想生成列にセカンダリインデックスが作成されると、生成された列の値をインデックスレコードが持つからだそうです。したがって、インデックスを貼りたい場合はデフォルトの VIRTUAL を使えば良いのかなと思っています。（間違っていたら、指摘してもらえると嬉しいです）
終わりに
ISUCON10の予選で先輩にGenerated Columnsという機能があるんだよと教えていただき、今回調べてみました。 明日は shmokmtさんで 「Go の context パッケージについて」です。
参考
MySQL :: MySQL 5.7 Reference Manual :: 13.1.18.7 CREATE TABLE and Generated Columns
MySQL :: MySQL 5.7 Reference Manual :: 13.1.18.8 Secondary Indexes and Generated Columns
ISUCON10 の振り返りと MySQL の generated columns - Techtouch Developers Blog
