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中出现
  • 当查询的列是主键和唯一键不需要一致

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

首先创建emailjonId复合索引

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之后,只要查询的列在索引中出现,不关心顺序和个数(不存在索引中的列必须为主键),都会用到创建的复合索引索引,不遵守最左原则

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值