MySQL中最左原则和覆盖索引的讨论

最左原则

create table Demo(id int primary key auto_increment,
	phoneNum int,
	email int,
	jobId int,
	addId int,
	unique id(phoneNum));
	

我么先创建了Demo表，然后紧接着创建了索引Demo_e_j_add，首先匹配email，然后再匹配剩下的列数，要是没有了email，那么就不会用到索引，我们也可以从type类型中看出：有email时的类型为range，用到了索引，当没有email，mysql就无法利用索引进行查询，也就使用了ALL进行全局搜索

这个怎么解释呢？因为我们这张表只有四个列，除去主键剩下的三个列都是组合索引，所以当不满足最左前缀时就会走覆盖索引，也就是查询的列能够从索引列中直接获取，这里的select * 就等同于查询了所有索引列，所以会使用using index
具体请看：最佳左前缀法则失效

覆盖索引

现在在网上有很多讨论：

• 当需要查的列和复合索引的列个数和顺序必须一致，从而在possible_keys中，没有的key值会在key中出现
• 当查询的列是主键和唯一键不需要一致

我们通过实验来证明上面的讨论

首先创建email和jonId复合索引

mysql> create index Demo_email_job on Demo(email,jobId);

1.首先进行最规范的写法，此时是覆盖索引，可以得到结论：

• 在possible_keys中，没有的key值会在key中出现

mysql> explain select email,jobId from Demo;
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key            | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-------------+
|  1 | SIMPLE      | Demo  | index | NULL          | Demo_email_job | 10      | NULL |    1 | Using index |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-------------+
1 row in set (0.00 sec)

2.顺序调换，同样可以得出相同的结论

mysql> explain select jobId,email from Demo;
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key            | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-------------+
|  1 | SIMPLE      | Demo  | index | NULL          | Demo_email_job | 10      | NULL |    1 | Using index |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-------------+
1 row in set (0.00 sec)

3.出现索引中不存在的列，且不是索引的列

此时进行全表搜索，没有用到索引

mysql> explain select jobId,email,addId from Demo;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | Demo  | ALL  | NULL          | NULL | NULL    | NULL |    1 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

4.出现索引中不存在的列，且是索引的列

此处的id为主键，是默认存在的索引

mysql> explain select jobId,email,id from Demo;
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key            | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-------------+
|  1 | SIMPLE      | Demo  | index | NULL          | Demo_email_job | 10      | NULL |    1 | Using index |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-------------+
1 row in set (0.00 sec)

当查询列出现复合索引中的一个或几个，可以用到覆盖索引

结论：

关于覆盖索引，在mysql5.6之后，只要查询的列在索引中出现，不关心顺序和个数（不存在索引中的列必须为主键），都会用到创建的复合索引索引，不遵守最左原则