MySQLのLIKEが遅い

検索ボックスから入力すると WHERE column_name LIKE "%foo%" というような部分一致検索する機能があり、この処理が非常に遅い。
% で始まる LIKE はインデックスを使えないから。

データベースが MySQL 5.1(InnoDB) の場合に、これをはやくするアプローチを考えてみる

MySQL の設定を見直す

設定がデフォルトのままだったら、チューニングする。

ベースとなる設定 http://blog.nomadscafe.jp/2012/10/mysql-mycnf-github.html

SQL を見直す

explain実行計画を確認LIKE 以外の箇所で改善点がないか見直す。

今回のケースでは、サブクエリーがフルスキャンしていたので、インデックスを追加して3000倍達成。

前方一致検索にする

インデックスが使えるように、部分一致検索(LIKE "%foo%")はやめて前方一致検索(LIKE "foo%")にする

完全一致や前方一致で十分なのに、無意味に部分一致になっている箇所があれば完全/前方一致にする。
要件によってはどうしても部分一致が必要ということもあるので、ケースバイケース

フルテキスト検索を利用

MySQL の Full-Text Search を利用する
残念なことに、この機能はストレージエンジン MyISAM に対して提供されてきた機能で、InnoDB は 5.6 以降でしか利用できない。
更に残念なことに、パーサーは欧米言語仕様なので、分かち書きをしてあげないとまともに使えない

The FULLTEXT parser determines where words start and end by looking for certain delimiter characters; for example, “ ” (space), “,” (comma), and “.” (period). If words are not separated by delimiters (as in, for example, Chinese), the FULLTEXT parser cannot determine where a word begins or ends. To be able to add words or other indexed terms in such languages to a FULLTEXT index, you must preprocess them so that they are separated by some arbitrary delimiter such as “””.
http://dev.mysql.com/doc/refman/5.6/en/fulltext-natural-language.html

日本語検索したい場合は次の URL を参照
http://y-ken.hatenablog.com/entry/mysql-casual-talks-vol4-innodb-fts

5.1 から 5.6 へのバージョナップはすぐにはできないので今回は見送り。

他のサーチエンジンを利用

groonga/sphinx/solr など他のサーチエンジンを利用する。

検索は重要な機能ではなく、オーバースペックということで今回は見送り。

References

  • High Performance MySQL, 3rd Edition
    Ch.7 : Advanced MySQL Features – Full-Text Searching (pp. 305-)
Advertisements
Tagged with:
Posted in database

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Archives
%d bloggers like this: