MySQL Advent Calendar 2019 の22日目です。
MySQLのあいまい検索時のLIKEで使える「_」と「%」について調べてみました。
あいまい検索時に使う「_」と「%」
会社でlike 検索した際に「_」と「%」で速度差ってあるのかな?という話になり、
こういうの調べたことある人って世の中に結構いるんじゃない?と調べてみたんですが、なかなか出てこないので自分で調べてみました
検証について
- カラムはとりあえず文字列としました
- セカンダリインデックスの有無とNULLの有無で比較
- NULLABLEなカラムのほうは全体の1割程度NULLにしてみた(根拠は無い)
- 作業環境はdockerに立てたMySQL8.0.18です(HDD上に結構小さいスペックで立ててます)
テーブル用意
CREATE TABLE `like_test` ( `like_test_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'LIKE文の検証ID', `uuid_key_ari_nn` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, `uuid_key_nashi_nn` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, `uuid_key_ari_dn` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin default NULL, `uuid_key_nashi_dn` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin default NULL, PRIMARY KEY (`like_test_id`), KEY `idx_uuid_key_ari_nn` (`uuid_key_ari_nn`), KEY `idx_uuid_key_ari_dn` (`uuid_key_ari_dn`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC COMMENT='LIKE文の検証' ;
カラム名 | カラムの役割 | インデックス有無 | NOT NULL |
---|---|---|---|
like_test_id | PK | 〇 | 〇 |
uuid_key_ari_nn | UUID値をNOT NULLの形で持つ | 〇 | 〇 |
uuid_key_nashi_nn | UUID値をNOT NULLの形で持つ | × | 〇 |
uuid_key_ari_dn | UUID値をNULLABLEの形で持つ | 〇 | × |
uuid_key_nashi_dn | UUID値をNULLABLEの形で持つ | × | × |
データ用意
INSERT INTO `test`.`like_test` (`like_test_id`, `uuid_key_ari_nn`, `uuid_key_nashi_nn`, `uuid_key_ari_dn`, `uuid_key_nashi_dn`) VALUES (null, uuid(), uuid(), uuid(), uuid());
pythonで書いてuuid v4で入れるか?とも考えたけど、とりあえずいーやってことでMySQLのUUID関数使う。
上のINSERTを適当にぶん回したりアレコレして480000件作った
> select count(1) from like_test; +------------+ | count(1) | |------------| | 480000 | +------------+ 1 row in set Time: 0.059s
ちょっとデータをいじって、4つのカラムがすべて同じ値かつテーブルで一意な値を用意した。
> select * from like_test where like_test_id = 14100; +----------------+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------+ | like_test_id | uuid_key_ari_nn | uuid_key_nashi_nn | uuid_key_ari_dn | uuid_key_nashi_dn | |----------------+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------| | 14100 | z9y8x7v6-99xx-99yy-99zz-zzzzzz123456 | z9y8x7v6-99xx-99yy-99zz-zzzzzz123456 | z9y8x7v6-99xx-99yy-99zz-zzzzzz123456 | z9y8x7v6-99xx-99yy-99zz-zzzzzz123456 | +----------------+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------+ 1 row in set Time: 0.010s
このレコードを使って検証してみる。
検証詳細
- mysqlslapを使う
- 並列数は1、1000回クエリを実行して平均、最小、最大時間を見る
- where句に
uuid_key_ari_nn
,uuid_key_nashi_nn
,uuid_key_ari_dn
,uuid_key_nashi_dn
の4カラムでLIKE文を作る - where 句の条件は以下のような感じ
パターンNo | 条件パターン | WHERE句 |
---|---|---|
1 | _ も% も使わないかつlikeじゃなくて= |
= 'z9y8x7v6-99xx-99yy-99zz-zzzzzz123456' |
2 | _ も% も使わない |
like 'z9y8x7v6-99xx-99yy-99zz-zzzzzz123456' |
3 | _ で前方一致検索 |
like 'z9y8x7v6-99xx-99yy-99zz-____________' |
4 | % で前方一致検索 |
like 'z9y8x7v6-99xx-99yy-99zz-%' |
5 | _ で中間があいまいな検索(一応前方一致) |
like 'z9y8x7v6-______________-zzzzzzz123456' |
6 | % で中間があいまいな検索(一応前方一致) |
like 'z9y8x7v6-%-zzzzzz123456' |
7 | _ で後方一致検索 |
like '________-99xx-99yy-99zz-zzzzzz123456' |
8 | % で後方一致検索 |
like '%-99xx-99yy-99zz-zzzzzz123456' |
9 | _ で中間一致検索 |
like '-99xx-99yy-99zz-____' |
10 | % で中間一致検索 |
like '%-99xx-99yy-99zz-%' |
ちなみに、後方一致と中間一致(7~10のパターンですね)はインデックス張っててもフルテーブルスキャンになります。
結果
分かりやすい結果が出ました。
- 検索条件値が曖昧じゃない場合は=のほうがLIKEよりほんのり速い気がする(けど気にしなくてもいいかなってレベル)
- インデックス使えてたら気にしなくていいレベル
- フルテーブルスキャンの場合は前方一致の場合と中間一致・後方一致のばあいだと前方一致検索のほうが速い
- フルテーブルスキャン・前方一致の場合だと「_」と「%」で差が無い
- 一方、フルテーブルスキャン・中間一致/後方一致だと「_」のほうが効率がいいのが明らか
まあ、文字列探索とかしたことある人はイメージしやすいですよね。(インデックスの素晴らしさも改めて実感しましたw)
それぞれの速度結果
uuid_key_ari_nn(インデックスあり・NOT NULL)
パターンNo | 平均時間(秒) | 最小時間(秒) | 最大時間(秒) |
---|---|---|---|
1 | 0.005 | 0.003 | 0.012 |
2 | 0.005 | 0.003 | 0.017 |
3 | 0.005 | 0.003 | 0.011 |
4 | 0.005 | 0.003 | 0.013 |
5 | 0.005 | 0.003 | 0.011 |
6 | 0.005 | 0.003 | 0.013 |
7 | 0.160 | 0.154 | 0.176 |
8 | 0.248 | 0.240 | 0.263 |
9 | 0.160 | 0.154 | 0.182 |
10 | 0.248 | 0.241 | 0.269 |
uuid_key_nashi_nn(インデックス無し・NOT NULL)
パターンNo | 平均時間(秒) | 最小時間(秒) | 最大時間(秒) |
---|---|---|---|
1 | 0.143 | 0.137 | 0.156 |
2 | 0.147 | 0.141 | 0.162 |
3 | 0.147 | 0.140 | 0.175 |
4 | 0.147 | 0.141 | 0.160 |
5 | 0.147 | 0.140 | 0.159 |
6 | 0.147 | 0.141 | 0.162 |
7 | 0.160 | 0.153 | 0.177 |
8 | 0.248 | 0.241 | 0.286 |
9 | 0.160 | 0.154 | 0.182 |
10 | 0.248 | 0.240 | 0.269 |
uuid_key_ari_dn(インデックスあり・NULLABLE)
パターンNo | 平均時間(秒) | 最小時間(秒) | 最大時間(秒) |
---|---|---|---|
1 | 0.005 | 0.003 | 0.013 |
2 | 0.005 | 0.003 | 0.017 |
3 | 0.005 | 0.003 | 0.011 |
4 | 0.005 | 0.003 | 0.013 |
5 | 0.005 | 0.003 | 0.011 |
6 | 0.005 | 0.003 | 0.015 |
7 | 0.158 | 0.151 | 0.171 |
8 | 0.234 | 0.227 | 0.255 |
9 | 0.157 | 0.151 | 0.172 |
10 | 0.234 | 0.227 | 0.253 |
uuid_key_ari_dn(インデックスあり・NULLABLE)
パターンNo | 平均時間(秒) | 最小時間(秒) | 最大時間(秒) |
---|---|---|---|
1 | 0.142 | 0.136 | 0.152 |
2 | 0.146 | 0.139 | 0.166 |
3 | 0.145 | 0.140 | 0.158 |
4 | 0.145 | 0.140 | 0.160 |
5 | 0.146 | 0.140 | 0.158 |
6 | 0.146 | 0.140 | 0.160 |
7 | 0.157 | 0.151 | 0.171 |
8 | 0.234 | 0.227 | 0.254 |
9 | 0.157 | 0.151 | 0.172 |
10 | 0.233 | 0.227 | 0.250 |
結論
- 中間一致検索、後方一致検索で「%」使うとパフォーマンス下がる可能性があるので、そういう場合は「_」に置き換えましょう
- そもそもそんな検索しないのがいいんですが・・・
明日は@asmrt_dsさんの「はじめてMySQLを使ってみた話」です。