对于Mysql的索引有很多比较专业的词汇,这次就来讲讲这些回表查询、索引覆盖、最佳左前缀、索引下推。
1. 回表查询
先通过普通索引的值定位聚簇索引值,再通过聚簇索引的值定位行记录数据,需要扫描两次索引B+树,它的性能较扫一遍索引树更低。
我们在往表里面插入数据的时候,必须要有一个key值。若这张表中有主键,就取主键当做key值,如果没有主键,就取一个唯一键来当做key值,如果也没有唯一键,那么就生成一个6字节的row_id来当做key值,以下就以主键为key值为例。
Mysql回表查询.png
如图所示,如果我们以主键为条件去查询的话,就会直接走主键索引的B+树,这棵B+树中的叶子节点存储的就是整个对应数据行的数据,我们只需要扫描着一次B+树即可。但如果我们以name字段为条件的话,就不能直接走主键索引的这棵B+树了,而是要先去name字段的这棵B+树中找,可以找到对应的主键,再去扫描主键索引的B+树,即可获得数据,这就是回表查询,需要扫描两次索引B+树。因此我们要尽量的避免回表查询。
2. 索引覆盖(covering index)
指一个查询语句的执行只用从索引中就能够取得,不必从数据表中读取。也可以称之为实现了索引覆盖。 当一条查询语句符合覆盖索引条件时,MySQL只需要通过索引就可以返回查询所需要的数据,这样避免了查到索引后再返回表操作,减少I/O提高效率。
我们还是以上面的这张图为例,先写两个SQL:
- select * from user where name = "张三"
- select id from user where name = "张三"
这两条语句就很好的体现了这个索引覆盖。第一条语句,就必须像刚才说的那样,扫描两次B+树,进行回表查询(这里图画的简略了点,实际上这个表还有很多其他的字段)。而第二条语句呢,它也会先去扫描name索引的B+树,扫描完,就能得到id字段值了,那就没有回表的必要了。直接从索引中就获得了需要的数据。
3. 最佳左前缀
即最左优先,在检索数据时从联合索引的最左边开始匹配
我们先假设建立一个name,age的组合索引
现在有四条SQL语句
- select * from user where name = "xxx" and age = xx;
- select * from user where name = "xxx";
- select * from user where age = xx ;
- select * from user where age = xx and name = "xxx";
请问哪条语句的索引会失效?
答案是只有第三条,这就是最佳左前缀。可能有人会问,为什么第四条可以?因为Mysql中有一个优化器,它会帮我们把顺序换过来。
4. 索引下推(Index Condition Pushdown)
可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。
我们还是假设建立一个联合索引(name,age)
在5.6以前:Mysql会忽略age这个字段,直接通过name进行查询,假设在(name,age)这课树上查找到了两个结果,id分别为1,2,然后拿着取到的id值一次次的回表查询,因此这个过程需要回表两次。
在5.6以后:InnoDB并没有忽略age这个字段,而是在索引内部就判断了age是否等于20,对于不等于20的记录直接跳过,因此在(name,age)这棵索引树中只匹配到了一个记录,此时拿着这个id去主键索引树中回表查询全部数据,这个过程只需要回表一次。这样减少了回表次数,也就减少了IO。
网友评论