UNIONを使うとWHERE a OR bより早いよ！って内容に対する調査

概要

WHERE a > xx OR b > xxのようなクエリはUNION使った方が早いよ！って記事があったので「本当に！？」と思って調べてみました。

準備

環境

MySQL 5.7.22

テストデータの作成

CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `a` int(11) NOT NULL DEFAULT 0,
  `b` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `a` (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB;

insert into test () values ();
insert into test (id) selet 0 from test;
insert into test (id) selet 0 from test;
insert into test (id) selet 0 from test;
insert into test (id) selet 0 from test;
insert into test (id) selet 0 from test;
insert into test (id) selet 0 from test;
insert into test (id) selet 0 from test;
insert into test (id) selet 0 from test;
insert into test (id) selet 0 from test;
insert into test (id) selet 0 from test;
insert into test (id) selet 0 from test;
insert into test (id) selet 0 from test;
insert into test (id) selet 0 from test;
insert into test (id) selet 0 from test;
insert into test (id) selet 0 from test;
insert into test (id) selet 0 from test;
insert into test (id) selet 0 from test;
insert into test (id) selet 0 from test;
insert into test (id) selet 0 from test;
insert into test (id) selet 0 from test;

UPDATE test SET
  a = CEIL(RAND() * 10000),
  b = CEIL(RAND() * 10000);

※約10万レコードを追加し、各テストカラムを1 ~ 10000の値で初期化しました。

結果

mysql> SELECT * FROM test WHERE a > 5000 OR b > 5000;
...
785925 rows in set (0.52 sec)

mysql> SELECT * FROM test WHERE a > 5000 UNION SELECT * FROM test WHERE b > 5000;
...
785925 rows in set (4.46 sec)

mysql> SELECT * FROM test USE INDEX (a) WHERE a > 5000 UNION ALL SELECT * FROM test USE INDEX (b) WHERE b > 5000;
...
1047773 rows in set (1.89 sec)

明確にUNION使ったほうが遅い。
UNION ALLでも遅いので絶望的。

mysql> SELECT * FROM test USE INDEX (a) WHERE a > 5000 UNION SELECT * FROM test USE INDEX (b) WHERE b > 5000;
...
785925 rows in set (4.45 sec)

Indexを使用してもさほど変わらず。

mysql> SELECT * FROM test WHERE a = 5000 OR b = 5000;
...
192 rows in set (0.00 sec)

mysql> SELECT * FROM test WHERE a = 5000 UNION SELECT * FROM test WHERE b = 5000;
192 rows in set (0.00 sec)

'='の場合は、この件数だとどちらが早いか判断がつかない。

考察

• 結合する件数が増えるとUNIONを使った場合、明確に遅くなる。
• 2つのOR文においてIndexがパフォーマンスに有効に機能し、かつ結果の件数が少ない場合はUNIONの方が早いのかもしれない。（未調査）

とはいえローカルでただクエリを実行した結果でしかないため、あくまで正確な調査ではない。
クエリは「キャッシュ」や「メモリに何が乗るか」、「データの分布」の影響も強いため、本番でどう影響出るかはフラットに判断するべきだろう。
ただ少なくとも脳死的にUNIONサイコーってことではなさそう。

おまけ

Indexについて

本題のUNIONではなく、Index周りで予想と違う挙動があったため深堀調査。

x > 9100だとIndexが使われない。

mysql> EXPLAIN SELECT * FROM test WHERE a > 9100 UNION SELECT * FROM test WHERE b > 9100;
+----+--------------+------------+------------+------+---------------+------+---------+------+---------+----------+-----------------+
| id | select_type  | table      | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra           |
+----+--------------+------------+------------+------+---------------+------+---------+------+---------+----------+-----------------+
|  1 | PRIMARY      | test       | NULL       | ALL  | a            | NULL | NULL    | NULL | 1043718 |    17.68 | Using where     |
|  2 | UNION        | test       | NULL       | ALL  | b         | NULL | NULL    | NULL | 1043718 |    18.25 | Using where     |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    NULL |     NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+------+---------+------+---------+----------+-----------------+
3 rows in set, 1 warning (0.00 sec)

9割(9万件以上)絞り込めててもIndexが使用されない？
昔はもっと緩い条件でもIndexが使われていたような。
この結果を見るに、範囲検索メインのカラムに対してIndex貼るのはあまり有効ではなさそうですね。

Index有無での速度調査

mysql> SELECT count(b) FROM test IGNORE INDEX (a) WHERE a > 5000;
+----------+
| count(b) |
+----------+
|   523664 |
+----------+
1 row in set (0.23 sec)

mysql> SELECT count(b) FROM test USE INDEX (a) WHERE a > 5000;
+----------+
| count(b) |
+----------+
|   523664 |
+----------+
1 row in set (0.23 sec)

変わらず。
この程度の件数じゃフルスキャンと変わらんよってことか。

mysql> SELECT * FROM test IGNORE INDEX (a) WHERE a = 5000;
...
101 rows in set (0.23 sec)

mysql> SELECT * FROM test USE INDEX (a) WHERE a = 5000;
...
101 rows in set (0.00 sec)

このくらいだとさすがに違いが出る。