[MySQL]WHERE句でLIKEを使っている場合のINDEX

RDBMSでレコードを検索する際、曖昧検索等でLIKEを使うことがある。
その場合にはINDEXが効くよう、クエリを作るときに注意が必要。

テスト用のテーブルを用意

まずはテスト用テーブルを用意する。

前回の記事でテストデータを作るために用意したテーブルを利用して、100000件のレコードをINSERTする。

次にnameカラムに対してINDEXを貼る。

nameカラムの値で検索する

では、ここからnameの2~3文字目が’AA’のレコードを調べる場合はLIKEを利用して

こんなクエリを考えると思う。(クエリがキャッシュでHITしないようSQL_NO_CACHEを追加)

しかし、このクエリはindexを貼った意味がない。explainで実行計画を見てみると

typeがindexになっており、indexをフルスキャンしている。
なのでindexによって対象レコードを絞れていない。

LIKEは前方一致にしなければindexが効かない

先ほどのクエリでindexが役に立たない理由は検索条件の文字列が’_AA%’になっていることが原因だ。
実はLIKEでは前方一致の条件にしなければindexの効果がない。1文字目がワイルドカードになってしまっているため、B-Treeインデックスの構造上、全件スキャンするしかない。
試しに検索条件を’BAA%’に変更し、explainを見てみると

このようにindexが効くようになる。

部分一致で検索したい場合は別の方法を考える

今回は2~3文字目に絞って話しているので、無理やり前方一致でindexを効かせるには

のように無理やりやることも可能だが、クエリ自体が冗長なものになってしまう。
データ数が大した数ではないテーブルであれば、フルスキャンをしたとしても問題ないが、膨大なレコード数になってくるとボトルネックの原因にもなってしまう。
なので、部分一致をしないで済むようにできないかを考える必要もときにはある。
全文検索等、どうしても部分一致検索が必要な場合は検索エンジンの利用や、indexの代わりになるような中間テーブルを作ることも考える。

シェアする

  • このエントリーをはてなブックマークに追加

フォローする