MySQL小ネタ／JOINでCASEを使う場合にINDEXは使われるのか？

MySQLに限りませんが、JOINでCASEを使うことで、結合条件を変えることができます。

駆動表（外部表）または内部表のいずれかが「複数のサブタイプをまとめたテーブル」である場合にそのようなことをしたくなるのだと思いますが、このとき、結合処理にINDEXは使われるのか？というのが今回のテーマです。

※MySQL 5.7.21で検証しました。

駆動表（外部表）にtype列がある（＝複数のサブタイプを持つ）ケース

以下のようなテーブルを作成します。

※カラムは可能な限り削ってあります。

駆動表にサブタイプ

mysql> CREATE DATABASE join_case_test;
Query OK, 1 row affected (0.00 sec)

mysql> USE join_case_test;
Database changed
mysql> CREATE TABLE join_outer (id int unsigned NOT NULL AUTO_INCREMENT, type int unsigned NOT NULL, PRIMARY KEY (id)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE join_inner (id int unsigned NOT NULL AUTO_INCREMENT, key1 int unsigned NOT NULL, key2 int unsigned NOT NULL, PRIMARY KEY (id), INDEX (key1), INDEX (key2)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

join_outerテーブルにデータを入れるSQL（10,000行分）

SET AUTOCOMMIT=0;
INSERT INTO join_case_test.join_outer SET type = FLOOR(RAND() * 2 + 1);
INSERT INTO join_case_test.join_outer SET type = FLOOR(RAND() * 2 + 1);
INSERT INTO join_case_test.join_outer SET type = FLOOR(RAND() * 2 + 1);
（中略）
INSERT INTO join_case_test.join_outer SET type = FLOOR(RAND() * 2 + 1);
COMMIT;

join_innerテーブルにデータを入れるSQL（10,000行分）

SET AUTOCOMMIT=0;
INSERT INTO join_case_test.join_inner SET key1 = FLOOR(RAND() * 10000 + 1), key2 = FLOOR(RAND() * 10000 + 1);
INSERT INTO join_case_test.join_inner SET key1 = FLOOR(RAND() * 10000 + 1), key2 = FLOOR(RAND() * 10000 + 1);
INSERT INTO join_case_test.join_inner SET key1 = FLOOR(RAND() * 10000 + 1), key2 = FLOOR(RAND() * 10000 + 1);
（中略）
INSERT INTO join_case_test.join_inner SET key1 = FLOOR(RAND() * 10000 + 1), key2 = FLOOR(RAND() * 10000 + 1);
COMMIT;

テーブルデータ確認

mysql> SELECT * FROM join_outer LIMIT 10;
+----+------+
| id | type |
+----+------+
|  1 |    2 |
|  2 |    1 |
|  3 |    1 |
|  4 |    2 |
|  5 |    2 |
|  6 |    1 |
|  7 |    1 |
|  8 |    2 |
|  9 |    2 |
| 10 |    1 |
+----+------+
10 rows in set (0.00 sec)

mysql> SELECT * FROM join_inner LIMIT 10;
+----+------+------+
| id | key1 | key2 |
+----+------+------+
|  1 | 7042 | 9458 |
|  2 | 6162 | 2437 |
|  3 | 3699 | 1185 |
|  4 | 4825 |  570 |
|  5 | 8373 |  155 |
|  6 | 5657 | 7818 |
|  7 | 2118 | 7137 |
|  8 | 9331 | 5241 |
|  9 | 8213 | 5339 |
| 10 | 2058 | 4273 |
+----+------+------+
10 rows in set (0.00 sec)

EXPLAINで確認してみます。
まずは、通常のJOINから。

EXPLAIN確認／通常JOIN

mysql> EXPLAIN SELECT * FROM join_outer o INNER JOIN join_inner i ON o.id = i.key1 WHERE o.id BETWEEN 1 AND 100;
+----+-------------+-------+------------+-------+---------------+---------+---------+---------------------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref                 | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+---------------------+------+----------+-------------+
|  1 | SIMPLE      | o     | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL                |  100 |   100.00 | Using where |
|  1 | SIMPLE      | i     | NULL       | ref   | key1          | key1    | 4       | join_case_test.o.id |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+-------+---------------+---------+---------+---------------------+------+----------+-------------+
2 rows in set, 1 warning (0.01 sec)

mysql> EXPLAIN SELECT * FROM join_outer o INNER JOIN join_inner i ON o.id = i.key2 WHERE o.id BETWEEN 1 AND 100;
+----+-------------+-------+------------+-------+---------------+---------+---------+---------------------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref                 | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+---------------------+------+----------+-------------+
|  1 | SIMPLE      | o     | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL                |  100 |   100.00 | Using where |
|  1 | SIMPLE      | i     | NULL       | ref   | key2          | key2    | 4       | join_case_test.o.id |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+-------+---------------+---------+---------+---------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

INDEXは効いています。
続いて、CASEでtypeによる条件分岐を行ってみます。

EXPLAIN確認／JOINでCASE

mysql> EXPLAIN SELECT * FROM join_outer o INNER JOIN join_inner i ON o.id = (CASE WHEN o.type = 1 THEN i.key1 ELSE i.key2 END) WHERE o.id BETWEEN 1 AND 100;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows  | filtered | Extra                                              |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+----------------------------------------------------+
|  1 | SIMPLE      | o     | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |   100 |   100.00 | Using where                                        |
|  1 | SIMPLE      | i     | NULL       | ALL   | NULL          | NULL    | NULL    | NULL | 10195 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM join_outer o, join_inner i WHERE o.id = (CASE WHEN o.type = 1 THEN i.key1 ELSE i.key2 END) AND o.id BETWEEN 1 AND 100;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows  | filtered | Extra                                              |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+----------------------------------------------------+
|  1 | SIMPLE      | o     | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |   100 |   100.00 | Using where                                        |
|  1 | SIMPLE      | i     | NULL       | ALL   | NULL          | NULL    | NULL    | NULL | 10195 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM join_outer o LEFT JOIN join_inner i ON o.id = (CASE WHEN o.type = 1 THEN i.key1 ELSE i.key2 END) WHERE o.id BETWEEN 1 AND 100;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows  | filtered | Extra                                              |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+----------------------------------------------------+
|  1 | SIMPLE      | o     | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |   100 |   100.00 | Using where                                        |
|  1 | SIMPLE      | i     | NULL       | ALL   | NULL          | NULL    | NULL    | NULL | 10195 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

INNER JOINと、それをJOINを使わない等結合に書き換えたもの、LEFT JOINのいずれも、内部表のINDEXは使われません。

内部表にtype列がある（＝複数のサブタイプを持つ）ケース

今度は、サブタイプを持つ内部表を作成します。

内部表にサブタイプ

mysql> CREATE TABLE join_inner2 (id int unsigned NOT NULL AUTO_INCREMENT, type int unsigned NOT NULL, key1 int unsigned NOT NULL, key2 int unsigned NOT NULL, PRIMARY KEY (id), INDEX (key1), INDEX (key2)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

join_inner2テーブルにデータを入れるSQL（10,000行分）

SET AUTOCOMMIT=0;
INSERT INTO join_case_test.join_inner2 SET type = FLOOR(RAND() * 2 + 1), key1 = FLOOR(RAND() * 10000 + 1), key2 = FLOOR(RAND() * 10000 + 1);
INSERT INTO join_case_test.join_inner2 SET type = FLOOR(RAND() * 2 + 1), key1 = FLOOR(RAND() * 10000 + 1), key2 = FLOOR(RAND() * 10000 + 1);
INSERT INTO join_case_test.join_inner2 SET type = FLOOR(RAND() * 2 + 1), key1 = FLOOR(RAND() * 10000 + 1), key2 = FLOOR(RAND() * 10000 + 1);
（中略）
INSERT INTO join_case_test.join_inner2 SET type = FLOOR(RAND() * 2 + 1), key1 = FLOOR(RAND() * 10000 + 1), key2 = FLOOR(RAND() * 10000 + 1);
COMMIT;

テーブルデータ確認

mysql> SELECT * FROM join_inner2 LIMIT 10;
+----+------+------+------+
| id | type | key1 | key2 |
+----+------+------+------+
|  1 |    1 | 8481 | 2557 |
|  2 |    2 | 9025 | 3108 |
|  3 |    2 | 3004 | 9621 |
|  4 |    2 | 6608 | 5755 |
|  5 |    2 | 7492 |  605 |
|  6 |    1 |  923 | 2973 |
|  7 |    1 | 1556 | 1494 |
|  8 |    1 | 9521 | 9203 |
|  9 |    2 | 9650 | 5895 |
| 10 |    1 | 4940 | 3122 |
+----+------+------+------+
10 rows in set (0.00 sec)

EXPLAINで確認してみます。

EXPLAIN確認／JOINでCASEその2

mysql> EXPLAIN SELECT * FROM join_outer o INNER JOIN join_inner2 i ON o.id = (CASE WHEN i.type = 1 THEN i.key1 ELSE i.key2 END) WHERE o.id BETWEEN 1 AND 100;
+----+-------------+-------+------------+--------+---------------+---------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | i     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL | 10294 |   100.00 | NULL        |
|  1 | SIMPLE      | o     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | func |     1 |   100.00 | Using where |
+----+-------------+-------+------------+--------+---------------+---------+---------+------+-------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM join_outer o LEFT JOIN join_inner2 i ON o.id = (CASE WHEN i.type = 1 THEN i.key1 ELSE i.key2 END) WHERE o.id BETWEEN 1 AND 100;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows  | filtered | Extra                                              |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+----------------------------------------------------+
|  1 | SIMPLE      | o     | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |   100 |   100.00 | Using where                                        |
|  1 | SIMPLE      | i     | NULL       | ALL   | NULL          | NULL    | NULL    | NULL | 10294 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

INNER JOINの場合は駆動表と内部表が入れ替わりましたが、入れ替わった後の駆動表（元の内部表）はINDEXが効いていません。
LEFT JOINの場合は先のケース（駆動表にサブタイプ）と同じで、やはり内部表のINDEXが効いていません。

※USE INDEXやFORCE INDEXを指定しても変化はありませんでした。

まとめ

クエリとしては正しく結果の抽出ができるのですが、INDEXが効果的に使われないため、データが増えたときにパフォーマンスの悪化が予想されます。
また、それを避けるために「同一テーブルからtype毎にSELECTしたものをUNION」という、見るからに効率の悪いクエリを書くことになったりもします（INDEXまたは複合INDEXにtype列を加えないといけなくなることも）。

そもそも、このようなクエリを書く以前に、テーブル設計の段階で「本当に1つのテーブルに複数のサブタイプをまとめて実装すべきか」をよく検討したほうが良いでしょう。