🍣
【MySQL】WHERE BETWEENは本当に速いのか ~datetimeカラムの場合~
はじめに
この前仕事の本番環境にて、あるSQLがいつの間にか4000msほどかかるようになっていました。
WHERE句で使用していたdatetime型のカラムにインデックスを張っておらず、
100万レコードに対してフルスキャンが走っていたことが原因の１つでした。
インデックスを張り、速度も70msに短縮できたので上長に報告したところ、
「もともとのSQLは比較演算子で絞ってるの？ BETWEENの方が速いんじゃない？」
との指摘があったので検証を行いました。
MySQLでは度々
「WHERE句で範囲を絞るときは比較演算子よりもBETWEENの方が速い」
と言われます。
本当にBETWEEN演算子は速いのか、検証してみたいと思います。
対象読者
■対象
SQL初心者の方。
結論
・ BETWEENよりも比較演算子の方が速いことがある。
・ CAST('Y-m-d H:i:s' as datetime)等、ちゃんと型変換するとBETWEEN演算子の方が速い。
検証環境
- Google Cloud SQL vCPU2, メモリ8GB, SSDストレージ100GB
- MySQL version: 8.0.26
テーブル情報
テーブル
+-----------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| user_id | int | NO | | NULL | |
| page_id | int | YES | | NULL | |
| viewed_at | datetime | NO | MUL | NULL | |
+-----------+----------+------+-----+---------+----------------+
格納レコード数
ひとまず200万件ほどのレコードを用意しました。
mysql> select count(*) from user_history;
+----------+
| count(*) |
+----------+
| 2050048 |
+----------+
1 row in set (0.18 sec)
インデックス
datetime型のカラムに張っています。
+--------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| user_history | 0 | PRIMARY | 1 | id | A | 1997216 | NULL | NULL | | BTREE | | | YES | NULL |
| user_history | 1 | index_on_viewd_at | 1 | viewed_at | A | 1997216 | NULL | NULL | | BTREE | | | YES | NULL |
+--------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
検証
view_atカラムに対して特定の範囲の行を抽出するためのSELECT文の実行計画を、比較演算子とBETWEEN演算子を使ってそれぞれ試してみたいと思います。
比較演算子を使用した実行計画
mysql> explain analyze select count(id) from user_history where viewed_at >= '2023-01-01 00:00:00' and viewed_at <= '2023-03-30 23:59:59';
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Aggregate: count(user_history.id) (cost=300212.19 rows=998608) (actual time=491.761..491.762 rows=1 loops=1)
-> Filter: ((user_history.viewed_at >= TIMESTAMP'2023-01-01 00:00:00') and (user_history.viewed_at <= TIMESTAMP'2023-03-30 23:59:59')) (cost=200351.39 rows=998608) (actual time=0.032..428.378 rows=1163251 loops=1)
-> Index range scan on user_history using index_on_viewd_at (cost=200351.39 rows=998608) (actual time=0.030..278.750 rows=1163251 loops=1)
|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.58 sec)
比較演算子の結果は、0.58秒でした。
BETWEEN演算子を使用した実行計画
mysql> explain analyze select count(id) from user_history where viewed_at between '2023-01-01 00:00:00' and '2023-03-30 23:59:59';
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Aggregate: count(user_history.id) (cost=300212.19 rows=998608) (actual time=801.417..801.418 rows=1 loops=1)
-> Filter: (user_history.viewed_at between '2023-01-01 00:00:00' and '2023-03-30 23:59:59') (cost=200351.39 rows=998608) (actual time=0.040..737.879 rows=1163251 loops=1)
-> Index range scan on user_history using index_on_viewd_at (cost=200351.39 rows=998608) (actual time=0.033..286.074 rows=1163251 loops=1)
|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.92 sec)
BETWEEN演算子の結果は、0.92秒でした。
結果
検証の結果、比較演算子の方が0.34秒速いという結果になりました。
一般的に「BETWEENの方が比較演算子より速い」と言われているのに、
どうしてBETWEEN演算子の方が遅くなってしまったのでしょうか？
BETWEEN演算子の特徴
MySQLリファレンスのBETWEEN演算子に関する項目を確認してみます。
日付または時間の値とともに BETWEEN を使用したときの結果を最適にするには、CAST() を使用して明示的に値を目的のデータ型に変換します。 例 : DATETIME を 2 つの DATE 値と比較する場合は、DATE 値を DATETIME 値に変換します。 DATE との比較で '2001-1-1' などの文字列定数を使用する場合は、文字列を DATE にキャストします。
WHERE viewed_at BETWEEN ’YYYY-MM-DD hh:mm:ss’ AND ’YYYY-MM-DD hh:mm:ss’という条件を使用する場合、BETWEENの最適化の恩恵を確実に受けるには、どうやら'YYYY-MM-DD hh:mm:ss'を明示的にdatetime型へとキャストする必要があるようです。
CAST()を使用したBETWEEN演算子の実行計画
型変換をしたら本当にBETWEENが速くなるのか、確認してみます。
mysql> explain analyze select count(id) from user_history where viewed_at between cast('2023-01-01 00:00:00' as datetime) and cast('2023-03-30 23:59:59' as datetime);
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Aggregate: count(user_history.id) (cost=300212.19 rows=998608) (actual time=431.097..431.097 rows=1 loops=1)
-> Filter: (user_history.viewed_at between <cache>(cast('2023-01-01 00:00:00' as datetime)) and <cache>(cast('2023-03-30 23:59:59' as datetime))) (cost=200351.39 rows=998608) (actual time=0.032..373.734 rows=1163251 loops=1)
-> Index range scan on user_history using index_on_viewd_at (cost=200351.39 rows=998608) (actual time=0.030..275.973 rows=1163251 loops=1)
|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.49 sec)
結果は0.49秒と、比較演算子より90ms速くなりました！
CAST()を使用した比較演算子の実行計画
比較演算子でもCASTを使用したらどうなるのか、検証してみます。
mysql> explain analyze select count(id) from user_history where viewed_at >= cast('2023-01-01 00:00:00' as datetime) and viewed_at <= cast('2023-03-30 23:59:59' as datetime);
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Aggregate: count(user_history.id) (cost=300212.19 rows=998608) (actual time=493.382..493.382 rows=1 loops=1)
-> Filter: ((user_history.viewed_at >= <cache>(cast('2023-01-01 00:00:00' as datetime))) and (user_history.viewed_at <= <cache>(cast('2023-03-30 23:59:59' as datetime)))) (cost=200351.39 rows=998608) (actual time=0.038..435.449 rows=1163251 loops=1)
-> Index range scan on user_history using index_on_viewd_at (cost=200351.39 rows=998608) (actual time=0.034..278.131 rows=1163251 loops=1)
|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.55 sec)
比較演算子の場合、CASTの有無では30msしか変わりませんでした。
まとめ
以上の処理速度を速い順でまとめると、
1. CAST()を使用して型変換したBETWEEN演算子(0.49秒)
2. CAST()を使用して型変換した比較演算子(0.55秒)
3. CAST()無しの比較演算子(0.58秒)
4. CAST()無しのBETWEEN演算子(0.92秒)
という結果になりました。
「WHERE BETWEENは本当に速いのか」という問いに対して、
「CASTを使えば速い」という検証結果になったと思います。
ネット上には、値のCAST無しで比較演算子よりBETWEEN演算子の方が速いという結果を紹介する記事もあります。
実行計画は一定のものではありませんし、CASTをしなくても他の条件の影響でBETWEEN演算子の方が速いということもあるのかもしれません。
しかし今回の結果や、MySQLのリファレンスを見るに、
BETWEEN演算子がいついかなる場合でも比較演算子より速いとは約束されておらず、確実に最適化するためにはCASTする必要がある
とも言えるのかと思いました。
自分の書いたSQLが最適で適切な結果を得られているか、今後改めてきちんと確認していきたいと思います。
参考
検証中、
「なんで比較演算子はCASTしてもしなくてもそんなに速度が変わらないんだろう？」
という別の疑問が生まれました。
調査のため比較演算子の実行計画の中身をよくよく見てみると、
'YYYY-MM-DD hh:mm:ss'の値の前に、あるリテラルが追加されています。
Filter: ((user_history.viewed_at >= TIMESTAMP'2023-01-01 00:00:00') and (user_history.viewed_at <= TIMESTAMP'2023-03-30 23:59:59'))
日付の前に、もともとのSQL文にはなかったTIMESTAMPというリテラルが入っています。
このTIMESTAMPというのは、MySQLリファレンスでは型キーワードやtypeキーワードと呼ばれており、後続の文字列を型変換するもののようです。
TIMESTAMP 構文は、MySQL で DATETIME 値を生成します。
次にBETWEEN演算子です。
Filter: (user_history.viewed_at between '2023-01-01 00:00:00' and '2023-03-30 23:59:59')
BETWEEN演算子の前にはTIMESTAMPと追加されていません。
どうして比較演算子の場合のみ、TIMESTAMPと型変換が行われたのでしょうか？
MySQLの式評価
リファレンスでは式評価について、
いずれかの引数が TIMESTAMP カラムまたは DATETIME カラムで、もう一方の引数が定数の場合、定数は比較が実行される前にタイムスタンプに変換されます。
と書かれていました。
よって、viewed_at >= '2023-01-01 00:00:00' は
「DATETIMEカラム 比較演算子 定数」の形に当てはまるため、定数がタイムスタンプに変換されたようです。
一方でBETWEEN演算子に関しては
たとえば、日付または時間の値とともに BETWEEN を使用したときの結果を最適にするには、CAST() を使用して、明示的に値を目的のデータ型に変換します。
と書かれています。
やはりBETWEEN演算子では明示的に型変換したほうがよさそうです。
感想
MySQL全般、特に型周りの知識がかなり浅いなと痛感しました...。むずかしい。
Discussion