MySql教程

11.Mysql之回表、最左匹配、索引下推

本文主要是介绍11.Mysql之回表、最左匹配、索引下推,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

1.前言:

  通过前面的两节,很容易简单地理解Mysql索引到底是干什么用的,不过这里说的回表、最左匹配、索引下推又是什么呢?

2.回表:

  1.我们都知道Mysql索引对数据库的查询有很重要的位置,通过通过索引很快的查找到需要的数据,通过Mysql的索引分为两种:一种是主键索引,一种是辅助索引(辅助索引),主键索引就是按照主键字段进行构成的索引组织表,通常我们在按照主键索引查询数据时,是直接就能返回所需要的记录的,因为主键索引的叶子节点上记录了该主键索引字段对应的行记录,但是如果我们要是通过辅助索引查找数据时,我们只能在它的叶子节点上先查找它对应的主键,然后再去主键索引上查找它的行记录,这种按照:辅助索引-->主键索引--->row记录的过程,我们就称之为回表操作。

  2.从上面来看回表操作的过程,我们发现要经过两次的B+树搜素,这样相对与一次的搜素,它浪费了时间还消耗了磁盘的i/O

  3.那么我们怎样可以避免回表操作呢?

  • 能尽量用主键索引查询就能查询结果的的都用主键索引进行查询
  • 不能直接用主键索引查询的,我们最好建立联合索引,其实联合索引只是减少了查询过程中回表的次数,但有时也能完全消除操作,具体还是要看select 后面所要返回的字段信息,
  • 建立联合索引的其中的一个技术是用到了索引覆盖(索引覆盖其实也是减少查询过程中回表的次数),通过我们在执行计划中的Extra字段中会有'use index'信息出现

3.最左匹配原则

  1.最左匹配其实主要就是针对联合索引的,通过联合索引都是两个字段以上的组合,但通过不要超过三个字段,应该字段越多组成的联合索引后期的维护的成本也就越高

  2.通过我们使用索引进行查询时,往往会发现执行计划中没有用到我们索建立的索引,这样就导致我们建立的索引失效,因此知道索引匹配原则,而对于Mysql而言,索引匹配原则用的是最左匹配原则,知道最左匹配原则,对我们建立索引是要很大帮助的。

  3.这里关于最左匹配原则用如下一张表来表示:  其中(a,b,c)是构造的联合索引

  

4.索引下推

 4.1简介:

  • 索引条件下推(index condition pushdown),简称ICP.mysql5.6新添加的,用于优化数据查询
  • 当你不使用ICP,通过使用费主键索引(普通索引or二级索引)进行查询,存储引擎通过索引检索数据,然后在返回mysql服务器,服务器再判断是否符合条件。
  • 使用ICP, 当存在索引的列作为判断条件时,mysql服务器将这一部分判断条件传递给存储引擎,然后存储引擎通过判断索引是否符合mysql服务器传递条件,只有当索引符合条件是才会将数据检索出来返回给mysql服务器    

  4.2.示例:

  在开始之前先准备一张用户表(user),其中主要有几个字段:id ,name age .address. 然后在建立联合索引(name,age)

  要求:匹配姓名第一个字为陈,年龄为20岁的用户

SELECT * from user where  name like '陈%' and age=20

  看看这条sql语句是如何执行的?

  5.6版本之前:

  

  • 执行过程:首先忽略age这个字段,直接通过name字段在联合索引(name,age)进行寻找,查到对应的结果id为1,2,然后再根据主键id进行回表操作,注意这里的回表是要进行两次的,意思是每查询到对应的id就立刻进行回表查询数据,然后再传到server层进行age字段的过滤,最后再将最终的结果集返回给客户端。

  5.6版本以及之后的版本

  • nnoDB并没有忽略age这个字段,而是在索引内部就判断了age是否等于20,对于不等于20的记录直接跳过,因此在(name,age)这棵索引树中只匹配到了一个记录,此时拿着这个id去主键索引树中回表查询全部数据,这个过程只需要回表一次。然后再传送到server层,最后再由server层将结果集传送给客户端。

 4.3 总结

  • 索引下推在非主键索引上的优化,可以有效减少回表的次数,大大提升了查询的效率
  • 关闭索引下推可以使用如下命令,配置文件的修改不再讲述了,毕竟这么优秀的功能干嘛关闭呢:
set optimizer_switch='index_condition_pushdown=off';

 

  

  

这篇关于11.Mysql之回表、最左匹配、索引下推的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!