MySQLで複合列PKなテーブルに対するWHERE条件でINを使うと、実行計画が変わる？

何言っているかわかんねえよ。

と思うので、事例で語る。

なお、この事象が出たのは、MySQL5.6.23です。

実験1：PK列が1つのケース

準備

こんな感じでテーブルとデータを用意します。

mysql > create table test_in_exp_tbl(
    ->  col1 int(11) not null,
    ->  col2 int(11) not null,
    ->  col3 char(128),
    ->   PRIMARY KEY (col1)
    -> );
Query OK, 0 rows affected (0.07 sec)

mysql > insert into test_in_exp_tbl values (1,1,'1-1');
Query OK, 1 row affected (0.04 sec)

mysql > insert into test_in_exp_tbl values (2,1,'2-1');
Query OK, 1 row affected (0.05 sec)

mysql > insert into test_in_exp_tbl values (3,1,'3-1');
Query OK, 1 row affected (0.05 sec)

mysql > commit;
Query OK, 0 rows affected (0.04 sec)

mysql > select * from test_in_exp_tbl;
+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
|    1 |    1 | 1-1  |
|    2 |    1 | 2-1  |
|    3 |    1 | 3-1  |
+------+------+------+
3 rows in set (0.05 sec)

実験1-1：WHEREで「PK列 = x」の場合

何件指定しても、key=PRIMARYです。
なお、複数件にすると、type=rangeになりますが、ロックはnot gapです。
（ロックモニタの結果は張りません）

mysql > explain select * from test_in_exp_tbl where col1 = 1;
+----+-------------+-----------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table           | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-----------------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | test_in_exp_tbl | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |
+----+-------------+-----------------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.05 sec)

mysql > explain select * from test_in_exp_tbl where col1 = 1 or col1 = 2;
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table           | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | test_in_exp_tbl | range | PRIMARY       | PRIMARY | 4       | NULL |    2 | Using where |
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.04 sec)

mysql > explain select * from test_in_exp_tbl where col1 = 1 or col1 = 2 or col1 = 3;
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table           | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | test_in_exp_tbl | range | PRIMARY       | PRIMARY | 4       | NULL |    3 | Using where |
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.05 sec)

実験1-2：WHEREで「PK列 in (x)」の場合

「PK列 = x」と同様に、何件指定しても、key=PRIMARYです。

mysql > explain select * from test_in_exp_tbl where (col1) IN (1);
+----+-------------+-----------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table           | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-----------------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | test_in_exp_tbl | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |
+----+-------------+-----------------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.04 sec)

mysql > explain select * from test_in_exp_tbl where (col1) IN (1,2);
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table           | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | test_in_exp_tbl | range | PRIMARY       | PRIMARY | 4       | NULL |    2 | Using where |
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.04 sec)

mysql > explain select * from test_in_exp_tbl where (col1) IN (1,2,3);
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table           | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | test_in_exp_tbl | range | PRIMARY       | PRIMARY | 4       | NULL |    3 | Using where |
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.04 sec)

実験2：PK列が2つのケース

準備

こんな感じでテーブルとデータを用意します。

mysql > create table test_in_exp_tbl2(
    ->  col1 int(11) not null,
    ->  col2 int(11) not null,
    ->  col3 char(128),
    ->   PRIMARY KEY (col1,col2)
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql > insert into test_in_exp_tbl2 values (1,1,'1-1');
Query OK, 1 row affected (0.05 sec)

mysql > insert into test_in_exp_tbl2 values (2,1,'2-1');
Query OK, 1 row affected (0.05 sec)

mysql > insert into test_in_exp_tbl2 values (3,1,'3-1');
Query OK, 1 row affected (0.05 sec)

mysql > select * from test_in_exp_tbl;
+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
|    1 |    1 | 1-1  |
|    2 |    1 | 2-1  |
|    3 |    1 | 3-1  |
+------+------+------+
3 rows in set (0.04 sec)

実験2-1：WHEREで「PK列 = x」の場合

何件指定しても、key=PRIMARYです。
なお、複数件にすると、type=rangeになりますが、ロックはnot gapです。

mysql > explain select * from test_in_exp_tbl2 where (col1 = 1 and col2 = 1);
+----+-------------+------------------+-------+---------------+---------+---------+-------------+------+-------+
| id | select_type | table            | type  | possible_keys | key     | key_len | ref         | rows | Extra |
+----+-------------+------------------+-------+---------------+---------+---------+-------------+------+-------+
|  1 | SIMPLE      | test_in_exp_tbl2 | const | PRIMARY       | PRIMARY | 8       | const,const |    1 | NULL  |
+----+-------------+------------------+-------+---------------+---------+---------+-------------+------+-------+
1 row in set (0.03 sec)

mysql > explain select * from test_in_exp_tbl2 where (col1 = 1 and col2 = 1) or (col1 = 2 and col2 = 1) ;
+----+-------------+------------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table            | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+------------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | test_in_exp_tbl2 | range | PRIMARY       | PRIMARY | 8       | NULL |    2 | Using where |
+----+-------------+------------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.04 sec)

mysql > explain select * from test_in_exp_tbl2 where (col1 = 1 and col2 = 1) or (col1 = 2 and col2 = 1) or (col1 = 3 and col2 = 1);
+----+-------------+------------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table            | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+------------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | test_in_exp_tbl2 | range | PRIMARY       | PRIMARY | 8       | NULL |    3 | Using where |
+----+-------------+------------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.04 sec)

実験2-2：WHEREで「PK列 in (x)」の場合

2件、3件指定に着目！
type=ALL、key=NULL、つまり、インデックス使ってません。

mysql > explain select * from test_in_exp_tbl2 where (col1,col2) IN ((1,1));
+----+-------------+------------------+-------+---------------+---------+---------+-------------+------+-------+
| id | select_type | table            | type  | possible_keys | key     | key_len | ref         | rows | Extra |
+----+-------------+------------------+-------+---------------+---------+---------+-------------+------+-------+
|  1 | SIMPLE      | test_in_exp_tbl2 | const | PRIMARY       | PRIMARY | 8       | const,const |    1 | NULL  |
+----+-------------+------------------+-------+---------------+---------+---------+-------------+------+-------+
1 row in set (0.04 sec)

mysql > explain select * from test_in_exp_tbl2 where (col1,col2) IN ((1,1),(2,1));
+----+-------------+------------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table            | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+------------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test_in_exp_tbl2 | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+------------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.04 sec)

mysql > explain select * from test_in_exp_tbl2 where (col1,col2) IN ((1,1),(2,1),(3,1));
+----+-------------+------------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table            | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+------------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test_in_exp_tbl2 | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+------------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.03 sec)

じゃあ、USE INDEX、FORCE INDEXどうなのよ。

mysql > explain select * from test_in_exp_tbl2 FORCE INDEX (PRIMARY) where (col1,col2) IN ((1,1),(2,1));
+----+-------------+------------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table            | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+------------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test_in_exp_tbl2 | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+------------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.04 sec)

mysql > explain select * from test_in_exp_tbl2 USE INDEX (PRIMARY) where (col1,col2) IN ((1,1),(2,1));
+----+-------------+------------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table            | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+------------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test_in_exp_tbl2 | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+------------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.04 sec)