Mysql联合索引违反最左匹配，依然走联合索引

创建表t2，设置id字段为主键，给字段 a,b,c 建立联合索引 index_abc:

mysql> create table t2(id int primary key,
    -> a int,
    -> b int,
    -> c int,
    -> index index_abc(a,b,c));

插入数据：

+----+------+------+------+
| id | a    | b    | c    |
+----+------+------+------+
|  0 |    1 |    3 |    2 |
|  1 |    2 |    4 |    3 |
|  2 |    3 |    6 |    4 |
+----+------+------+------+

违反最左匹配，where条件中以字段b开始查询:

mysql> explain select * from t2 where b=1 and c=3;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t2    | NULL       | index | index_abc     | index_abc | 15      | NULL |    3 |    33.33 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+

发现mysql依然走了联合索引 index_abc，与联合索引最左匹配原则相悖，那么原因是什么呢？

向表t2中插入一个新字段d：

mysql> alter table t2 add d int;

mysql> desc t2;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| id    | int  | NO   | PRI | NULL    |       |
| a     | int  | YES  | MUL | NULL    |       |
| b     | int  | YES  | MUL | NULL    |       |
| c     | int  | YES  |     | NULL    |       |
| d     | int  | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+

在字段d插入数据：

mysql> select * from t2;
+----+------+------+------+------+
| id | a    | b    | c    | d    |
+----+------+------+------+------+
|  0 |    1 |    3 |    2 |    1 |
|  1 |    2 |    4 |    3 |    2 |
|  2 |    3 |    6 |    4 |    3 |
+----+------+------+------+------+

 再次运行这条sql语句：

mysql> explain select * from t2 where b=1 and c=3;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

mysql此时并没有走联合索引 index_abc，而是进行了全表扫描。

总结

如果联合索引包含了所有字段（除了主键），不论是否违反联合索引最左匹配原则，都会走联合索引，反之，查询依然符合联合索引最左匹配原则。