まず自分自身がMariaDBの生成カラム周りおよびJSONについて、
以下２点勘違いをしていた。
- MariaDBでもMySQLと同様に、VIRTUAL COLUMNにインデックスを張ることができる（すいません、できないと思ってました。。）
- MariaDBでもCHECK制約を使うことで、「JSONの正しさ」を保証できる（すいません、できないと思ってました。。）
以前書いた記事にも訂正を追記しておきました。
というわけで、MariaDB10.2.17、10.3.10のバージョンで生成カラムについて見ていきます。
ではまず、以下のようなテーブルを作ってみます。
CREATE TABLE `mail` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`to` varchar(255) NOT NULL,
`cc` varchar(255),
`bcc` varchar(255),
`subject` varchar(255) NOT NULL,
`body` json NOT NULL,
`addrs` text AS (CONCAT_WS(',', `to`, `cc`, `bcc`)) PERSISTENT,
`sign` varchar(255) AS (JSON_VALUE(`body`, '$.sign')) VIRTUAL,
CHECK (JSON_VALID(`body`)),
PRIMARY KEY (`id`),
FULLTEXT KEY `mail_fidx01` (`addrs`),
KEY `mail_idx01` (`sign`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ちょっと長いのですが、ポイントは以下の通り。
- addrsカラムに対して、PERSISTENTタイプの生成カラムを利用して、全文検索INDEXを張る
- bodyカラムに対して、JSONの型チェックを行う
- signカラムに対して、VIRTUALタイプの生成カラムを利用して、JSONの部分抽出を行い、セカンダリーINDEXを張る
1はカラムを結合した状態を全文検索にしたい場合、
2はJSONの正しさを保証したい場合、
3はJSONの中の特定のキーを条件に高速にデータアクセスしたい場合、
に用いられるような想定です。
この状態のテーブルを見てみます。
MariaDB [test]> desc mail; +---------+--------------+------+-----+---------+-------------------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+-------------------+ | id | int(10) | NO | PRI | NULL | auto_increment | | to | varchar(255) | NO | | NULL | | | cc | varchar(255) | YES | | NULL | | | bcc | varchar(255) | YES | | NULL | | | subject | varchar(255) | NO | | NULL | | | body | longtext | NO | | NULL | | | addrs | text | YES | MUL | NULL | STORED GENERATED | | sign | varchar(255) | YES | MUL | NULL | VIRTUAL GENERATED | +---------+--------------+------+-----+---------+-------------------+ 8 rows in set (0.002 sec) MariaDB [test]> show index from mail; +-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | mail | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | | mail | 1 | mail_idx01 | 1 | sign | A | 0 | NULL | NULL | YES | BTREE | | | | mail | 1 | mail_fidx01 | 1 | addrs | NULL | NULL | NULL | NULL | YES | FULLTEXT | | | +-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3 rows in set (0.000 sec)
生成カラムの情報がdescのExtraにも表現されていますね。
ちなみに、生成カラムは「NOT NULL DEFAULT ・・・」は付けられないようで、生成カラムの評価ができない（失敗？）の場合は、NULLが入る仕様のようです。
次に以下のようなデータを流し込んで、結果を見てみます。
INSERT INTO `mail` (`to`, `cc`, `bcc`, `subject`, `body`) VALUES ('to-00001@example.com', 'cc-00001@example.com', 'bcc-00001@example.com', 'subject-00001', '{"sign": "sign-00001", "message": "message-00001"}');
INSERT INTO `mail` (`to`, `cc`, `bcc`, `subject`, `body`) VALUES ('to-00002@example.com', 'cc-00002@example.com', null , 'subject-00002', '{"sign": "sign-00002", "message": "message-00002"}');
INSERT INTO `mail` (`to`, `cc`, `bcc`, `subject`, `body`) VALUES ('to-00003@example.com', null , null , 'subject-00003', '{"sign": "sign-00003", "message": "message-00003"}');
INSERT INTO `mail` (`to`, `cc`, `bcc`, `subject`, `body`) VALUES ('to-00004@example.com', null , null , 'subject-00004', '{"message": "message-00004"}');
MariaDB [test]> select * from mail\G;
*************************** 1. row ***************************
id: 1
to: to-00001@example.com
cc: cc-00001@example.com
bcc: bcc-00001@example.com
subject: subject-00001
body: {"sign": "sign-00001", "message": "message-00001"}
addrs: to-00001@example.com,cc-00001@example.com,bcc-00001@example.com
sign: sign-00001
*************************** 2. row ***************************
id: 2
to: to-00002@example.com
cc: cc-00002@example.com
bcc: NULL
subject: subject-00002
body: {"sign": "sign-00002", "message": "message-00002"}
addrs: to-00002@example.com,cc-00002@example.com
sign: sign-00002
*************************** 3. row ***************************
id: 3
to: to-00003@example.com
cc: NULL
bcc: NULL
subject: subject-00003
body: {"sign": "sign-00003", "message": "message-00003"}
addrs: to-00003@example.com
sign: sign-00003
*************************** 4. row ***************************
id: 4
to: to-00004@example.com
cc: NULL
bcc: NULL
subject: subject-00004
body: {"message": "message-00004"}
addrs: to-00004@example.com
sign: NULL
4 rows in set (0.000 sec)
ちゃんとデータが入っていますので、
PERSISTENTタイプのaddrsへの全文検索と、
VIRTUALタイプのsignへの検索を行ってみます。
・PERSISTENTタイプのaddrsへの全文検索
MariaDB [test]> select * from mail where match(addrs) against('cc-00002')\G;
*************************** 1. row ***************************
id: 2
to: to-00002@example.com
cc: cc-00002@example.com
bcc: NULL
subject: subject-00002
body: {"sign": "sign-00002", "message": "message-00002"}
addrs: to-00002@example.com,cc-00002@example.com
sign: NULL
1 row in set (0.001 sec)
MariaDB [test]> explain select * from mail where match(addrs) against('cc-00002')\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: mail
type: fulltext
possible_keys: mail_fidx01
key: mail_fidx01
key_len: 0
ref:
rows: 1
Extra: Using where
1 row in set (0.000 sec)
・VIRTUALタイプのsignへの検索
MariaDB [test]> select * from mail where sign = 'sign-00003'\G;
*************************** 1. row ***************************
id: 3
to: to-00003@example.com
cc: NULL
bcc: NULL
subject: subject-00003
body: {"sign": "sign-00003", "message": "message-00003"}
addrs: to-00003@example.com
sign: sign-00003
1 row in set (0.001 sec)
MariaDB [test]> explain select * from mail where sign = 'sign-00003'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: mail
type: ref
possible_keys: mail_idx01
key: mail_idx01
key_len: 1023
ref: const
rows: 1
Extra: Using where
1 row in set (0.001 sec)
ちょっと件数少ないですが、両方共INDEXが効いていることがわかります。
生成カラムって実際どのくらい使われているのかわかりませんが、うまく使うことで、機能追加にも柔軟な対応ができそうな予感です。
生成カラムの進化はこれからも期待しています。
おっと、INSTANT ADDは、生成カラムには効かないので、ご注意ください。INSTANT対応も今後はあるのでは、と期待してます。
以上
