全文検索エンジンGroongaのMySQLストレージエンジンであるMroongaのソースをいじる機会があったので、ついでにMySQLのgenerated columnとMariaDBのvirtual column(computed column)の対応をしました。
次回リリースのMroonga 7.06からはgenerated columnを作ってそれに全文インデックスを使って高速に全文検索できるようになります。
MySQL5.7ではJSON型に対してFulltextインデックスを作るのが許可されていないのですが、generated columnとJSON関数を使うことにより、JSONの中の特定の値に対して、全文検索を行うことができるようになります。
たとえば、JSON型のカラムにログを蓄積しておき、必要になったタイミングで特定の値のみをgenerated columnを作って全文検索を行ったりできます。
OAuthのAPI取得結果をJSONで入れておいて、後で必要になったら名前とかプロフィールを引っ張りだして検索するとか。
mysql> CREATE TABLE logs ( -> id INT, -> record JSON, -> message VARCHAR(255) GENERATED ALWAYS AS (json_extract(`record`, '$.message')) STORED, -> FULLTEXT INDEX(message) comment 'tokenizer "TokenBigramSplitSymbolAlphaDigit"' -> ) ENGINE=Mroonga DEFAULT CHARSET=utf8mb4; Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> mysql> INSERT INTO logs(id, record) VALUES (1, '{"level": "info", "message": "start"}'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO logs(id, record) VALUES (2, '{"level": "info", "message": "restart"}'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO logs(id, record) VALUES (3, '{"level": "warn", "message": "abort"}'); Query OK, 1 row affected (0.00 sec) mysql> mysql> SELECT * FROM logs WHERE MATCH(message) AGAINST("ar" IN BOOLEAN MODE); +------+-----------------------------------------+-----------+ | id | record | message | +------+-----------------------------------------+-----------+ | 1 | {"level": "info", "message": "start"} | "start" | | 2 | {"level": "info", "message": "restart"} | "restart" | +------+-----------------------------------------+-----------+ 2 rows in set (0.04 sec)
Inplace alter tableの対応もいれれたので、既存のテーブルに対しても追加するカラムに対する値のコピーの負荷のみでスキーマ変更ができます（ALTER TABLE ADD col GENERATED ALWAYS..で自動的に値はコピーされる）。
検索時に毎回関数を評価しなくていいように、２つの日付のうち、最先の日のカラムを作っておくとかもできるかな。
mysql> CREATE TABLE Docs ( -> id INT, -> app_date DATE, -> priority_date DATE, -> earliest_date DATE GENERATED ALWAYS AS (LEAST(app_date,priority_date)) STORED, -> INDEX(earliest_date) -> ) ENGINE=Mroonga DEFAULT CHARSET=utf8mb4; Query OK, 0 rows affected (0.01 sec) mysql> mysql> INSERT INTO Docs(id, app_date, priority_date) VALUES (1, "2017-01-01", "2016-12-24"); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO Docs(id, app_date, priority_date) VALUES (2, "2017-02-01", "2016-11-24"); Query OK, 1 row affected (0.00 sec) mysql> mysql> EXPLAIN SELECT * FROM Docs WHERE earliest_date > "2016-12-23"; +----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+-------------+ | 1 | SIMPLE | Docs | NULL | range | earliest_date | earliest_date | 4 | NULL | 1 | 100.00 | Using where | +----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> SELECT * FROM Docs WHERE earliest_date > "2016-12-23"; +------+------------+---------------+---------------+ | id | app_date | priority_date | earliest_date | +------+------------+---------------+---------------+ | 1 | 2017-01-01 | 2016-12-24 | 2016-12-24 | +------+------------+---------------+---------------+ 1 row in set (0.01 sec)
なお、MySQL5.7では、VIRTUALなgenerated columnには、Fulltextインデックスが許可されていないので、インデックスを作りたい場合は、
STOREDで作る必要があります。
MariaDBのInnoDBラッパーモードであれば、たぶんVIRTUALでもFulltextインデックスが作れると思います。
また、ストレージモードのVIRTUALカラムに対するインデックスは対応していません。
Mroongaを脱却してGroonga直接構成にしようと思ったりもしていましたが、MySQL Routerを使うと、mroonga_commandのUDFでも向き先をちゃんと変更してくれたり、mroonga_commandのエスケープの問題も少し緩和されたようなので、まだしばらくMroonga構成で行こうと思っています。
