验证最左匹配

我们知道，当表中存在联合索引时，需要遵循最左匹配原则
如果最左前缀列没有指定的话，理应是不会使用到索引的来使用的
比如：

mysql> create table db1(id int primary key,a int,b int,c int); # 创建表db1 
Query OK, 0 rows affected (0.01 sec)

mysql> alter table db1 add INDEX db1_index(a,b); # 增加联合索引(a,b)
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from db1 where a = 1 and b = 1;
Empty set (0.00 sec)

mysql> explain select * from db1 where a = 1 and b = 1;
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref         | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | db1   | NULL       | ref  | db1_index     | db1_index | 10      | const,const |    1 |   100.00 | NULL  |                        # 可见，这里使用到了索引 db1_index
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

不会使用到索引的情况：

mysql> explain select * from db1 where b = 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | db1   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |                              # 可见，此次查询并没有使用到联合索引
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

以上是正常情况，但有一个例外，那便是当表中列全是索引时，那么每次查询必定会使用到索引！

mysql> create table db2(id int primary key,a int,b int,c int);
Query OK, 0 rows affected (0.02 sec)

mysql> alter table db2 add INDEX db2_index(a,b,c); # (a,b,c)，可见表上列都是索引
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from db2 where c = 1;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | db2   | NULL       | index | db2_index     | db2_index | 15      | NULL |    1 |   100.00 | Using where; Using index |           # 可见，这次还是使用到了索引
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

这其实很正常，就此表来分析，联合索引是非聚簇索引，叶子节点存储着的是聚簇索引键以及(a,b,c)，对于此表而言就是全量数据了，那边不用再去回表查询了