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

总结：根据实验大概得知结果

1. and查询会根据关联性高(符合该条件的行数少)选择具体走哪个索引，
2. 如果关联性相等(符合该条件的行数相同)，满足一条，会走最左索引，大于一条，会走两个索引
3. and查询会根据关联性高(符合该条件的行数少)，最左索引最小，会选择两个索引，
4. 两个都不满足，走最左索引

以上测试结果仅供参考，如有错误，欢迎指正

---