通俗的讲就是,如果索引的列在 select 所需获得的列中(因为在 mysql 中索引是根据索引列的值进行排序的,所以索引节点中存在该列中的部分值)或者根据一次索引查询就能获得记录就不需要回表,如果 select 所需获得列中有大量的非索引列,索引就需要到表中找到相应的列的信息,这就叫回表。
InnoDB聚集索引的叶子节点存储行记录,因此, InnoDB必须要有,且只有一个聚集索引:
(1)如果表定义了主键,则PK就是聚集索引;
(2)如果表没有定义主键,则第一个非空唯一索引(not NULL unique)列是聚集索引;
(3)否则,InnoDB会创建一个隐藏的row-id作为聚集索引;
先创建一张表,sql 语句如下:
然后,我们再执行下面的 SQL 语句,插入几条测试数据。
假设,现在我们要查询出 id 为 2 的数据。那么执行 select * from xttblog where ID = 2这条 SQL 语句就不需要回表。原因是根据主键的查询方式,则只需要搜索 ID 这棵 B+ 树。主键是唯一的,根据这个唯一的索引,MySQL 就能确定搜索的记录。
但当我们使用 k 这个索引来查询 k = 2 的记录时就要用到回表。select * from xttblog where k = 2原因是通过 k 这个普通索引查询方式,则需要先搜索 k 索引树,然后得到主键 ID 的值为 1,再到 ID 索引树搜索一次。这个过程虽然用了索引,但实际上底层进行了两次索引查询,这个过程就称为回表。
也就是说,基于非主键索引的查询需要多扫描一棵索引树。因此,我们在应用中应该尽量使用主键查询。
我这里表里的数据量比较少,如果数据量大的话,你能很明显的看出两次查询所用的时间,很明显使用主键查询效率更高。
更多如下图:
(1)先通过普通索引定位到主键值id=5;
(2)在通过聚集索引定位到行记录;
这就是所谓的回表查询,先定位主键值,再定位行记录,它的性能较扫一遍索引树更低。
使用聚集索引(主键或第一个唯一索引)就不会回表,普通索引就会回表。
只需要在一棵索引树上就能获取SQL所需的所有列数据,无需回表,速度更快。
explain的输出结果Extra字段为Using index时,能够触发索引覆盖。
例子
第一个sql:
select id,name from user where name='shenjian'
Extra:Using index。
第二个sql:
select id,name,sex from user where name='shenjian'
能够命中name索引, 索引叶子节点存储了主键id,没有储存sex,sex字段必须回表查询才能获取到 ,不符合索引覆盖,需要再次通过id值扫描聚集索引获取sex字段,效率会降低。
Extra:Using index condition。
如果把(name)单列索引升级为联合索引(name, sex)就不同了。
可以看到:
select id,name ... where name='shenjian'
select id,name,sex ... where name='shenjian'
单列索升级为联合索引(name, sex)后,索引叶子节点存储了主键id,name,sex ,都能够命中索引覆盖,无需回表。
画外音,Extra:Using index。
场景1:全表count查询优化
原表为:
user(PK id, name, sex);
直接:
select count(name) from user
不能利用索引覆盖。
添加索引:
alter table user add key(name)
就能够利用索引覆盖提效。
场景2:列查询回表优化
这个例子不再赘述,将单列索引(name)升级为联合索引(name, sex),即可避免回表。
场景3:分页查询
将单列索引(name)升级为联合索引(name, sex),也可以避免回表。
假设有这么个需求,查询表中“名字第一个字是张,性别男,年龄为10岁的所有记录”。那么,查询语句是这么写的:
根据前面说的“最左前缀原则”,该语句在搜索索引树的时候,只能匹配到名字第一个字是‘张’的记录(即记录ID3),接下来是怎么处理的呢?当然就是从ID3开始,逐个回表,到主键索引上找出相应的记录,再比对age和ismale这两个字段的值是否符合。
但是!MySQL 5.6引入了索引下推优化,可以在索引遍历过程中, 对索引中包含的字段先做判断,过滤掉不符合条件的记录,减少回表字数 。
下面图1、图2分别展示这两种情况。
图 1 中,在 (name,age) 索引里面我特意去掉了 age 的值, 这个过程 InnoDB 并不会去看 age 的值 ,只是按顺序把“name 第一个字是’张’”的记录一条条取出来回表。因此,需要回表 4 次。
图 2 跟图 1 的区别是,InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 10,对于不等于 10 的记录,直接判断并跳过。在我们的这个例子中,只需要对 ID4、ID5 这两条记录回表取数据判断,就只需要回表 2 次。
如果没有索引下推优化(或称ICP优化),当进行索引查询时, 首先根据索引来查找记录,然后再根据where条件来过滤记录 ;在支持ICP优化后,MySQL会在取出索引的同时, 判断是否可以进行where条件过滤再进行索引查询 ,也就是说提前执行where的部分过滤操作,在某些场景下,可以大大减少回表次数,从而提升整体性能。
Index Condition Pushdown(索引下推) MySQL 5.6引入了索引下推优化,默认开启,使用SET optimizer_switch = ‘index_condition_pushdown=off’可以将其关闭。官方文档中给的例子和解释如下: people表中(zipcode,lastname,firstname)构成一个索引SELECT * FROM people WHERE zipcode=‘95054’ AND lastname LIKE ‘%etrunia%’ AND address LIKE ‘%Main Street%’
如果没有使用索引下推技术,则MySQL会通过zipcode='95054’从存储引擎中查询对应的数据,返回到MySQL服务端,然后MySQL服务端基于lastname LIKE '%etrunia%'和address LIKE '%Main Street%'来判断数据是否符合条件。 如果使用了索引下推技术,则MYSQL首先会返回符合zipcode='95054’的索引,然后根据lastname LIKE '%etrunia%'和address LIKE '%Main Street%'来判断索引是否符合条件。如果符合条件,则根据该索引来定位对应的数据,如果不符合,则直接reject掉。 有了索引下推优化,可以在有like条件查询的情况下,减少回表次数。
欢迎分享,转载请注明来源:夏雨云
评论列表(0条)