MySQLのLIKEとREGEXP、RLIKEの速度の違いについて

素朴な疑問なのですが、結果は同じになるようにLIKEとREGEXP、RLIKEを実行したら速度差が出ました。
先頭文字マッチのみLIKEの方が速度が早いのは何故でしょうか。
ただの誤差の範囲なのでしょうか。

先頭文字マッチの場合、LIKE(0.01sec) < RLIKE(0.08sec) < REGEXP(0.16sec)
末尾文字マッチの場合、RLIKE(0.01sec) = REGEXP(0.01sec) < LIKE(0.07sec)
任意文字マッチの場合、RLIKE(0.00sec) < REGEXP(0.01sec) < LIKE(0.09sec)

MySQL
1使用したデータ
2mysql> SELECT * FROM Address;
3+-----------+---------------+--------------+-----+-----+
4| name      | phone_nbr     | address      | sex | age |
5+-----------+---------------+--------------+-----+-----+
6| 井上      | NULL          | 福島県       | 女  |  55 |
7| 佐々木    | 080-5848-XXXX | 千葉県       | 女  |  19 |
8| 佐藤      | 090-1922-XXXX | 三重県       | 女  |  25 |
9| 前田      | 090-0000-XXXX | 東京都       | 女  |  21 |
10| 小川      | 080-3333-XXXX | 東京都       | 男  |  30 |
11| 松本      | NULL          | 千葉県       | 女  |  20 |
12| 林        | 080-3333-XXXX | 福島県       | 男  |  32 |
13| 森        | 090-2984-XXXX | 東京都       | 男  |  45 |
14| 鈴木      | 090-0001-XXXX | 和歌山県     | 男  |  32 |
15+-----------+---------------+--------------+-----+-----+
169 rows in set (0.03 sec)

###先頭文字マッチ

MySQL
1LIKEの場合
2mysql> SELECT * FROM Address WHERE phone_nbr LIKE "090%";
3+--------+---------------+--------------+-----+-----+
4| name   | phone_nbr     | address      | sex | age |
5+--------+---------------+--------------+-----+-----+
6| 佐藤   | 090-1922-XXXX | 三重県       | 女  |  25 |
7| 前田   | 090-0000-XXXX | 東京都       | 女  |  21 |
8| 森     | 090-2984-XXXX | 東京都       | 男  |  45 |
9| 鈴木   | 090-0001-XXXX | 和歌山県     | 男  |  32 |
10+--------+---------------+--------------+-----+-----+
114 rows in set (0.01 sec)
12
13REGEXPの場合
14mysql> SELECT * FROM Address WHERE phone_nbr REGEXP "^090";
15~中略~
164 rows in set (0.16 sec)
17
18RLINKの場合
19mysql> SELECT * FROM Address WHERE phone_nbr RLIKE "^090";
20~中略~
214 rows in set (0.08 sec)

###末尾文字マッチ

MySQL
1mysql> SELECT * FROM Address WHERE name LIKE "%木";
2+-----------+---------------+--------------+-----+-----+
3| name      | phone_nbr     | address      | sex | age |
4+-----------+---------------+--------------+-----+-----+
5| 佐々木    | 080-5848-XXXX | 千葉県       | 女  |  19 |
6| 鈴木      | 090-0001-XXXX | 和歌山県     | 男  |  32 |
7+-----------+---------------+--------------+-----+-----+
82 rows in set (0.07 sec)
9
10mysql> SELECT * FROM Address WHERE name REGEXP "木$";
11~中略~
122 rows in set (0.01 sec)
13
14mysql> SELECT * FROM Address WHERE name RLIKE "木$";
15~中略~
162 rows in set (0.01 sec)

###任意文字マッチ

MySQL
1mysql> SELECT * FROM Address WHERE phone_nbr LIKE "%2%";
2+--------+---------------+-----------+-----+-----+
3| name   | phone_nbr     | address   | sex | age |
4+--------+---------------+-----------+-----+-----+
5| 佐藤   | 090-1922-XXXX | 三重県    | 女  |  25 |
6| 森     | 090-2984-XXXX | 東京都    | 男  |  45 |
7+--------+---------------+-----------+-----+-----+
82 rows in set (0.09 sec)
9
10mysql> SELECT * FROM Address WHERE phone_nbr REGEXP "2";
11+--------+---------------+-----------+-----+-----+
12| name   | phone_nbr     | address   | sex | age |
13+--------+---------------+-----------+-----+-----+
14| 佐藤   | 090-1922-XXXX | 三重県    | 女  |  25 |
15| 森     | 090-2984-XXXX | 東京都    | 男  |  45 |
16+--------+---------------+-----------+-----+-----+
172 rows in set (0.01 sec)
18
19mysql> SELECT * FROM Address WHERE phone_nbr RLIKE "2";
20+--------+---------------+-----------+-----+-----+
21| name   | phone_nbr     | address   | sex | age |
22+--------+---------------+-----------+-----+-----+
23| 佐藤   | 090-1922-XXXX | 三重県    | 女  |  25 |
24| 森     | 090-2984-XXXX | 東京都    | 男  |  45 |
25+--------+---------------+-----------+-----+-----+
262 rows in set (0.00 sec)

##(追記）

###先頭文字マッチのEXPLAINの実行結果

MySQL
1mysql> EXPLAIN  SELECT * FROM Address WHERE phone_nbr LIKE "090%";
2+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
3| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
4+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
5|  1 | SIMPLE      | Address | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    9 |    11.11 | Using where |
6+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
71 row in set, 1 warning (0.00 sec)
8
9mysql> EXPLAIN  SELECT * FROM Address WHERE phone_nbr REGEXP "^090";
10+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
11| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
12+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
13|  1 | SIMPLE      | Address | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    9 |   100.00 | Using where |
14+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
151 row in set, 1 warning (0.00 sec)

###ついでのSHOW INDEX

MySQL
1mysql> SHOW INDEX FROM Address;
2+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
4+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5| address |          0 | PRIMARY  |            1 | name        | A         |           9 |     NULL | NULL   |      | BTREE      |         |               |
6+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

退会済みユーザー

2018/10/30 00:39

これって複数回やった結果なんでしょうか？誤差の範囲な気もします。

hanlio

2018/10/30 06:01

改めて実行したところ、全て0.00secになりました。誤差の範囲だったのかも知れません。

行動規範の内容に同意します

回答2件

ベストアンサー

先頭文字マッチのみLIKEの方が速度が早いのは何故でしょうか。

先頭マッチの場合、LIKEではインデックスを利用可能、とのことです。RLIKEの場合は「先頭一致でインデックスを見る」という最適化がどうやらなされていないようです。

…といっても推測に過ぎないので、EXPLAINしてみましょう。インデックスを使っているかはそれで判別できます。

投稿2018/10/30 00:36

maisumakun

総合スコア145183

hanlio

2018/10/30 06:02

ありがとうございます。EXPLAINという構文があるのですね。知りませんでした。調べてみます。

hanlio

2018/10/30 06:55

インデックスというものを私は知らなかったのですが、EXPLAINの実行してみました。 filteredがLIKEは11.11。REGEXPは100ということはインデックスで絞られて検索されているということでしょうか。

maisumakun

2018/10/30 07:08

両方ALLとなれば、テーブルスキャンは共通なので、「先頭一致だと明示した検索」と、「正規表現で同じ意味を指定した検索の差」がメインになるのかと思います（同じ意味でも正規表現で実現すると遅いことはよくあります）。 filteredは、テーブルをJOINする際に行数を見積もるためのものなので、JOINしないときには特に関係なさそうです。 https://dev.mysql.com/doc/refman/5.6/ja/explain-output.html#explain_filtered

hanlio

2018/10/30 07:19

ご丁寧にご教授いただき、ありがとうございました。正規表現かどうかで速度差が出ることもあることも覚えておきます。