キーワードでの検索ってどうすれば早くなるのか？を調べてみる
アプリとか使っているとフリーワード検索がよく実装されている。
しかし、SQLでキーワードを使って検索するとしたらあいまい検索・LIKE検索をまっさきに思い浮かぶけど、LIKE検索は遅い。
フリーワード検索ってどうすれば早くなるのか？どういった実装をしているのか？っていうのをちょっと調べてみようと思う。
そもそもLIKE検索は遅いのか？
調べてみると、LIKE検索が常に遅いわけではないようだ。
インデックス貼っているカラムに対してでも、中間一致・後方一致の場合（%で始まる条件の時）はインデックスは使われずに、データを全部読み込んで条件に一致するかをすべて照らし合わせて結果を求めるので、データ量が多ければ多いほど遅くなってしまう。ということになるみたい。
クエリでの工夫
中間一致・後方一致の場合はインデックスは使われずに遅くなってしまうので、前方一致のクエリを使えばインデックスは使われるという事になる。
例えば、「LIEK "ABC%"」という条件であれば良いのではないか？
ただ、これだと実際にサービスを使うユーザー視点では使い勝手が悪い。
例えば、ソシャゲでキャラクターを探したいならばキャラクター名を完全に覚えている必要があるし、進化や限定などで同じキャラでも複数のプレイアブルキャラとして存在している場合に全プレイアブルキャラを探すことができない。
よって、サービスとしてユーザーに提供するとしてはユーザビリティに欠けるので仕様としてNGになるだろう。
MySQLでngram パーサーを使用する FULLTEXT インデックス
MySQLではngram 全文パーサーという、日本語全文検索の仕組みが存在しているようだ。
イメージとしては全文検索用のインデックスといった感じだろうか？
ngram 全文パーサーは、MySQL5.7以上のInnoDB および MyISAM での使用がサポートされているようなので、日本語のキーワード検索したいならば使える機能と思っても良さそ。
詳しくは以下。
私は使ったことないのですが、使ってみた系の記事を読んでみると、後方一致のLIKE検索と比較して50倍以上早くなったという記事もありました。
デメリット
- 完全一致を求めるにはクエリだけでなく工夫が必要
- データ量が多くなる
- データ挿入に時間がかかる
検索用に別のサービスと組み合わせる
例えばOracle DatabaseにはOracle Textという全文検索機能があり、フリーワード検索だけOracle Databaseにアクセスするとか。
例えばElasticsearchという検索エンジンサービスがあり、定期的に検索したいMySQLのデータをElasticsearchにインポートするなどでElasticsearchとの組み合わせもできるかなと。
まとめ
MySQLだけでもngramパーサーを利用した FULLTEXT インデックスという手法があったり、Elasticsearchなどの他サービスとの掛け合わせという手法もありそうです。
実際に使っている訳ではないので経験や実感と共に書いているわけではないですが、「こういった機能やサービスがある」という知識としては調べてよかったと思います。
