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
ちょっとデータをいじって、４つのカラムがすべて同じ値かつテーブルで一意な値を用意した。
> 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よりほんのり速い気がする(けど気にしなくてもいいかなってレベル)
- インデックス使えてたら気にしなくていいレベル
- フルテーブルスキャンの場合は前方一致の場合と中間一致・後方一致のばあいだと前方一致検索のほうが速い
- フルテーブルスキャン・前方一致の場合だと「_」と「%」で差が無い
- 一方、フルテーブルスキャン・中間一致/後方一致だと「_」のほうが効率がいいのが明らか
まあ、文字列探索とかしたことある人はイメージしやすいですよね。(インデックスの素晴らしさも改めて実感しましたｗ)
それぞれの速度結果
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を使ってみた話」です。
