mysql where andで使用されるインデックスの順序

mysql> select * from test;
+----+------+------+
| id | a    | b    |
+----+------+------+
|  1 |    1 |    1 |
|  2 |    1 |    2 |
|  3 |    1 |    2 |
|  4 |    2 |    2 |
|  5 |    2 |    2 |
|  6 |    3 |    3 |
|  7 |    4 |    4 |
|  8 |    4 |    4 |
+----+------+------+
69 rows in set (0.00 sec)

mysql> show index from test;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test  |          0 | PRIMARY  |            1 | id          | A         |          69 |     NULL | NULL   |      | BTREE      |         |               |
| test  |          1 | a        |            1 | a           | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
| test  |          1 | b        |            1 | b           | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

EXPLAIN select * from test where b=31 and a=31;

mysql> EXPLAIN select * from test where b=31 and a=31;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ref  | a,b           | a    | 5       | const |    1 |     5.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

：a b ， a

mysql> EXPLAIN select * from test where b=11 and a=1;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+

| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ref  | a,b           | b    | 5       | const |    1 |     5.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

：a ,b ， b

mysql> EXPLAIN select * from test where b=1 and a=11;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ref  | a,b           | a    | 5       | const |    1 |     5.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

：a ,b ， a

mysql> EXPLAIN select * from test where b=1 and a=1;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ref  | a,b           | b    | 5       | const |    1 |     5.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

：a ,b ， b

explain select * from test where  b=2 and a=1;
+----+-------------+-------+------------+-------------+---------------+------+---------+------+------+----------+------------------------------------------------+
| id | select_type | table | partitions | type        | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                          |
+----+-------------+-------+------------+-------------+---------------+------+---------+------+------+----------+------------------------------------------------+
|  1 | SIMPLE      | test  | NULL       | index_merge | a,b           | a,b  | 5,5     | NULL |    2 |   100.00 | Using intersect(a,b); Using where; Using index |
+----+-------------+-------+------------+-------------+---------------+------+---------+------+------+----------+------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

：a ,b ， a b， a,b

mysql> explain select * from test where  b=3 and a=3;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ref  | a,b           | a    | 5       | const |    1 |    16.67 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

：a ,b ， a b， a

mysql> explain select * from test where  b=4 and a=4;
+----+-------------+-------+------------+-------------+---------------+------+---------+------+------+----------+------------------------------------------------+
| id | select_type | table | partitions | type        | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                          |
+----+-------------+-------+------------+-------------+---------------+------+---------+------+------+----------+------------------------------------------------+
|  1 | SIMPLE      | test  | NULL       | index_merge | a,b           | a,b  | 5,5     | NULL |    1 |   100.00 | Using intersect(a,b); Using where; Using index |
+----+-------------+-------+------------+-------------+---------------+------+---------+------+------+----------+------------------------------------------------+
1 row in set, 1 warning (0.00 sec

：a ,b ， a b( 2)， a,b
まとめ:実験によってだいたい結果がわかる

andクエリは、関連性が高い(この条件に合致する行数が少ない)ことに基づいて、どのインデックスを特定するかを選択します.

関連性が等しい(この条件に合致する行数が同じ)場合、1つを満たすと、最も左のインデックスが1つより大きく、2つのインデックス

が実行されます.

andクエリは、関連性が高い(この条件を満たすローの数が少ない)ことに基づいて、最も左のインデックスが最も小さく、2つのインデックスが選択されます.

両方とも満たされず、一番左のインデックス

を歩きます.
以上のテスト結果は参考までにして、間違いがあれば、ご指摘ください.