RDBMSでレコードを検索する際、曖昧検索等でLIKEを使うことがある。
その場合にはINDEXが効くよう、クエリを作るときに注意が必要。
テスト用のテーブルを用意
まずはテスト用テーブルを用意する。
mysql> create table sample(id int auto_increment primary key, name varchar(10)); Query OK, 0 rows affected (0.02 sec) mysql> desc sample; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(10) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.01 sec)
前回の記事でテストデータを作るために用意したテーブルを利用して、100000件のレコードをINSERTする。
mysql> insert into sample(name) select concat(t1.str, t2.str, t3.str, t4.str, t5.str) 'name' from testdata t1, testdata t2, testdata t3, testdata t4, testdata t5; Query OK, 100000 rows affected (0.95 sec) Records: 100000 Duplicates: 0 Warnings: 0 mysql> select * from sample; +--------+-------+ | id | name | +--------+-------+ | 1 | AAAAA | | 2 | BAAAA | | 3 | CAAAA | | 4 | DAAAA | | 5 | EAAAA | | ⋮ | | 99997 | G | | 99998 | H | | 99999 | I | | 100000 | | +--------+-------+ 100000 rows in set (0.07 sec)
次にnameカラムに対してINDEXを貼る。
mysql> alter table sample add index name_inx(name); Query OK, 0 rows affected (0.33 sec) Records: 0 Duplicates: 0 Warnings: 0
nameカラムの値で検索する
では、ここからnameの2~3文字目が’AA’のレコードを調べる場合はLIKEを利用して
select SQL_NO_CACHE * from sample where name like '_AA%';
こんなクエリを考えると思う。(クエリがキャッシュでHITしないようSQL_NO_CACHEを追加)
しかし、このクエリはindexを貼った意味がない。explainで実行計画を見てみると
mysql> explain select SQL_NO_CACHE * from sample where name like '_AA%'; +----+-------------+--------+------------+-------+---------------+----------+---------+------+--------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+----------+---------+------+--------+----------+--------------------------+ | 1 | SIMPLE | sample | NULL | index | NULL | name_inx | 33 | NULL | 100339 | 11.11 | Using where; Using index | +----+-------------+--------+------------+-------+---------------+----------+---------+------+--------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec)
typeがindexになっており、indexをフルスキャンしている。
なのでindexによって対象レコードを絞れていない。
LIKEは前方一致にしなければindexが効かない
先ほどのクエリでindexが役に立たない理由は検索条件の文字列が’_AA%’になっていることが原因だ。
実はLIKEでは前方一致の条件にしなければindexの効果がない。1文字目がワイルドカードになってしまっているため、B-Treeインデックスの構造上、全件スキャンするしかない。
試しに検索条件を’BAA%’に変更し、explainを見てみると
mysql> explain select SQL_NO_CACHE * from sample where name like 'BAA%'; +----+-------------+--------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | sample | NULL | range | name_inx | name_inx | 33 | NULL | 136 | 100.00 | Using where; Using index | +----+-------------+--------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec)
このようにindexが効くようになる。
部分一致で検索したい場合は別の方法を考える
今回は2~3文字目に絞って話しているので、無理やり前方一致でindexを効かせるには
where name like 'AAA%' or name like 'BAA%' or ...
のように無理やりやることも可能だが、クエリ自体が冗長なものになってしまう。
データ数が大した数ではないテーブルであれば、フルスキャンをしたとしても問題ないが、膨大なレコード数になってくるとボトルネックの原因にもなってしまう。
なので、部分一致をしないで済むようにできないかを考える必要もときにはある。
全文検索等、どうしても部分一致検索が必要な場合は検索エンジンの利用や、indexの代わりになるような中間テーブルを作ることも考える。
コメント