いきさつ
自分用のタグ式画像管理システムを構築していた時、
タグの検索がやたら遅いので何とかならないかと考えていた…
動作確認環境、使用ライブラリ等
- Fedora 24 (Server Edition)
- MariaDB 10.1.19
テーブル定義、データの状態
画像管理テーブルの定義
画像管理テーブル：MyISAM型
| カラム名 | データ型 | 照合順序 | 属性 | インデックス |
|---|---|---|---|---|
| id | int(10) | UNSIGNED | PRIMARY | |
| tags | text | utf8_general_ci | FULLTEXT | |
| filepath | varchar(2048) | utf8_general_ci | NOT NULL | |
| created_at | datetime | NOT NULL | ||
| updated_at | datetime | NULLABLE |
- 画像そのものはファイルシステムに格納されており、ファイルパスだけテーブルで管理している。
- 「tags」には、その画像に付けたタグ(キーワード)がスペース区切り(前後付加)で格納されている。
- また、タグデータは、表記ゆれを極力回避するため、下記のようなルールに則って付与されている。
- 文字はすべて半角英数小文字のみとし、可能な限り英語を用いる。ただし例外的に一部の記号も認める。
- 文字列内にスペースがある場合は、半角のアンダースコアに置換する。
- そのルールに則り、たとえば下記のようなタグを付与してある。
| 元文字列 | 変換後文字列 |
|---|---|
| 艦隊これくしょん | kantai_collection |
| 響 (艦これ) | hibiki_(kantai_collection) |
| 我那覇 響 | ganaha_hibiki |
- データは約20万行存在する。
クエリテスト
LIKE検索の場合
MariaDB [imagedb]> SELECT COUNT(*) FROM `画像管理テーブル`
WHERE
`tags` LIKE '% ganaha_hibiki %';
+----------+
| COUNT(*) |
+----------+
| 3311 |
+----------+
1 row in set (1.31 sec)
MariaDB [imagedb]> SELECT COUNT(*) FROM `画像管理テーブル`
WHERE
`tags` LIKE '% ganaha_hibiki %'
AND
`tags` LIKE '% school_uniform %'
+----------+
| COUNT(*) |
+----------+
| 25 |
+----------+
1 row in set (1.33 sec)
返ってくるのに1秒強かかっている。
※なお、同一クエリを連続で発行した場合はクエリキャッシュに乗っかるので、0.00秒以下で返ってくる。
全文検索の場合
MariaDB [imagedb]> SELECT COUNT(*) FROM `画像管理テーブル`
WHERE
MATCH(`tags`) AGAINST( '+ganaha_hibiki' IN BOOLEAN MODE);
+----------+
| COUNT(*) |
+----------+
| 3311 |
+----------+
1 row in set (0.05 sec)
MariaDB [imagedb]> SELECT COUNT(*) FROM `画像管理テーブル`
WHERE
MATCH(`tags`) AGAINST( '+ganaha_hibiki +school_uniform' IN BOOLEAN MODE);
+----------+
| COUNT(*) |
+----------+
| 25 |
+----------+
1 row in set (0.08 sec)
なんだこれは。爆速じゃないか。
LIKE検索と比較すると、16～26倍高速。
簡単な解説
全文検索ができる環境について
- MyISAMおよびAriaテーブルにて使用可能。
- MariaDB 10.0.5より、InnoDBテーブルでも使用可能に。
- MariaDB 10.0.15より、MroongaテーブルのCHAR、VARCHAR、TEXTカラムで使用可能に。
- パーティション化されたテーブルでは使用不可。
なぜLIKE検索は遅いのか？
EXPLAINで分析してみると一目瞭然
LIKEの場合
MariaDB [imagedb]> explain select * from `table` where `tags` LIKE '% ganaha_hibiki %';
+------+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | table | ALL | NULL | NULL | NULL | NULL | 204843 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)
これを見ると「select_type」が「SIMPLE」、「type」が「ALL」、「rows」が「204843」となっており
単純に、全ての行(20万件)に対して条件に合致するか照合した ということが分かる。
一方、FULLTEXTは
MariaDB [imagedb]> EXPLAIN SELECT * FROM `table` WHERE MATCH(`tags`) AGAINST( '+ganaha_hibiki' IN BOOLEAN MODE);
+------+-------------+-------+----------+---------------+------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+----------+---------------+------------+---------+------+------+-------------+
| 1 | SIMPLE | table | fulltext | tags | tags | 0 | | 1 | Using where |
+------+-------------+-------+----------+---------------+------------+---------+------+------+-------------+
1 row in set (0.00 sec)
FULLTEXTインデックスを活用していることが分かる。
FULLTEXTインデックスの張り方
コンソールから
ALTER TABLE `sometable` ADD FULLTEXT(` somerow `);
phpMyAdminからであれば、テーブル構造の「操作」から「その他」→「全文」を選択することで設定可能。
MyISAMテーブルにおいては、FULLTEXTインデックスを張らなくてもMATCH～AGAINST句を使用できるが「遅い」とのこと。
FULLTEXTインデックスのデメリット
行挿入・更新が遅くなる
テキストの解析およびインデックス追加の処理が重く、頻繁にINSERT/UPDATEするような環境には不向き。
どの程度遅くなるのかについては未検証なので不明。
頻繁に、あるいはバッチ処理等一括でINSERT/UPDATEを行うような環境においての回避策(案)
-
FULLTEXTインデックスを取り除いて更新処理を行い、その後ふたたびインデックスを張る作戦
一時的に全文検索不能になるが、簡単で速いと思われる。
また、前述の通りMyISAMテーブルにおいてはFULLTEXTインデックスを張らなくても、(遅いながら)検索可能ということなのでリアルタイム更新可能。 -
全文検索用のテーブルを別途用意し、夜間バッチ等で元テーブルよりSELECT/INSERTする作戦
更新処理そのものは、通常通り実施することが可能だが、全文検索結果への即時反映はされなくなる。
インデックスが肥大化する
一般的な(integer等の)インデックスよりも、データ容量が大きくなる。
私の環境では、実データ170MBに対して、インデックス用として75MB程度を使用している。
integerのインデックスだけだと3MB程度。
IN BOOLEAN MODE について
通常の全文検索においては、自然言語検索が行われる。
「IN BOOLEAN MODE」を明示的に指定することにより、ブール全文検索を行うことができる。
ブール検索モードには以下のような特徴がある。
- 関連性によるソートが行われない。
- 演算子が使用可能。たとえば：
- 「+」… この単語が行に含まれるレコードを探す。
複数入れることで、LIKEのANDのような動きをする - 「-」… この単語が行に含まれないレコードを探す。
複数入れることで、NOT LIKEのANDのような動きをする
記号を含むクエリを投げたときの挙動
IN BOOLEAN MODEで特別な意味を持つ記号（たとえば -や(、)など）を含めて検索したときどうなるか？
MariaDB [imagedb]> SELECT COUNT(*) FROM `画像管理テーブル`
WHERE
MATCH(`tags`) AGAINST( '+hibiki_(kantai_collection)' IN BOOLEAN MODE);
+----------+
| COUNT(*) |
+----------+
| 12700 |
+----------+
1 row in set (0.129 sec)
MariaDB [imagedb]> SELECT COUNT(*) FROM `画像管理テーブル`
WHERE
`tags` LIKE '%hibiki_(kantai_collection)%';
+----------+
| COUNT(*) |
+----------+
| 12591 |
+----------+
1 row in set (13.404 sec)
件数が微妙に違う…。なんで？
MariaDB [imagedb]> SELECT `tags` FROM `画像管理テーブル`
WHERE
MATCH(`tags`) AGAINST( '+hibiki_(kantai_collection)' IN BOOLEAN MODE)
AND
`tags` NOT LIKE '%hibiki_(kantai_collection)%';
+------------------------------+
| tags |
+------------------------------+
| hibiki_(azul_lane) |
+------------------------------+
1 row in set (0.189 sec)
おや？ 新入りさんですね
どうやら全文検索の _ 以降のものが無視されてしまっているようです…。
MariaDB [imagedb]> SELECT `tags` FROM `画像管理テーブル`
WHERE
MATCH(`tags`) AGAINST( '+hibiki_(kantai_collection)' IN BOOLEAN MODE)
AND
`tags` LIKE '%hibiki_(kantai_collection)%';
+------------------------------+
| tags |
+------------------------------+
| hibiki_(kantai_collection) |
+------------------------------+
1 row in set (0.199 sec)
少しめんどくさいけども、さらにLIKE句で検索をかけることで、望んだ結果を得ることができた。
あらかじめ全文検索で母数を削っているため、LIKEを付加してもそんなに速度が落ちることはなかった。
できればMATCH ~ AGAINSTのみでサクッと取ってきたいけども…。
エスケープの仕方など、いい感じにできる方法をご存知であればぜひ教えてください。
まとめ
- インデックスが効かないLIKEは激遅
- FULLTEXTを使えば、結構な高速化が見込まれる。(ただし更新が遅いなどデメリットもある)
- 実際に使う時は検索語に記号が含まれると望んだ結果を得られない。十分注意を
以上