29.进阶-索引-使用规则-单列&联合索引
(1)单列索引与联合索引
单列索引:即一个索引只包含单个列。
联合索引:即一个索引包含了多个列。
我们先来看看 tb_user 表中目前的索引情况:
在查询出来的索引中,既有单列索引,又有联合索引。
接下来,我们来执行一条SQL语句,看看其执行计划:
通过上述执行计划我们可以看出来,在and连接的两个字段 phone、name上都是有单列索引的,但是最终mysql只会选择一个索引,也就是说,只能走一个字段的索引,此时是会回表查询的。
紧接着,我们再来创建一个phone和name字段的联合索引来查询一下执行计划。
create unique index idx_user_phone_name on tb_user(phone,name); #因为phone是唯一的,再加上name肯定也是唯一的,所以这里创建的是unique唯一索引。
此时,查询时,就走了联合索引,而在联合索引中包含 phone、name的信息,在叶子节点下挂的是对应的主键id,所以查询是无需回表查询的。
在业务场景中,如果存在多个查询条件,考虑针对于查询字段建立索引时,建议建立联合索引,而非单列索引。
如果查询使用的是联合索引,具体的结构示意图如下:
解释:
每一个结点中所存储的键值就是phone和name两个字段的组合情况。0000吕布,就是1的组合情况…,他会先按照手机号phone进行排序,如果手机号一致,再按照name字段进行排序。最终构建了如图所示的b+tree。所构建出来的这个联合索引也属于二级索引,叶子结点挂的是这一页行记录对应的主键。
为什么不需要回表查询呢?
因为我们查询返回的只是id,phone和name,这三个字段的值在联合索引中都有,走这一个获取到的已经满足需求了,所以不需要回表查询。尽量使用联合索引,避免使用单列索引。使用单列索引很容易造成回表查询,造成性能降低。
在外面创建联合索引的是,哪个字段放前面?哪个字段放后面?对于查询结果有没有影响?
有影响。前面提到的最左前缀法则,如果说我们创建一个联合索引,每一次查询要想用这个联合索引,最左边的列必须存在,也就是phone这个列必须存在。所以创建联合索引的时候,,我们得考虑字段的顺序。
左边的列必须存在,也就是phone这个列必须存在。所以创建联合索引的时候,,我们得考虑字段的顺序。