什么情况下mysql innodb会发生回表操作?
MySQL innodb的
-
主键索引是
簇集索引(聚簇索引)
,也就是索引的叶子节点存的是整个单条记录的所有字段值; -
非主键索引(其它索引)的就是
非簇集索引(非聚簇索引)
,非簇集索引的叶子节点存的是主键字段的值。
索引的叶子节点结构是key value
,key是索引项
,value存放具体值
,
主键索引在mysql中是簇集索引,key是主键,value是单条记录的所有值。但一张表为了避免数据太冗余,只能有一个簇集索引,所以非簇集索引的value值存放的是主键值,这样才能根据主键找到具体的数据。
回表是什么意思?
就是你执行一条sql语句,需要从两个b+索引中去取数据。
举个例子:
表tbl
有a,b,c
三个字段,其中a
是主键,b
上建了索引,然后编写sql语句SELECT * FROM tbl WHERE a=1
这样不会产生回表,因为所有的数据在a的索引树
中均能找到;
如果是SELECT * FROM tbl WHERE b=1
这样就会产生回表,因为where条件是b
字段,那么会去b的索引树
里查找数据,但b
的索引里面只有a,b
两个字段的值,没有c
,那么这个查询为了取到c
字段,就要取出主键a
的值,然后去a
的索引树去找c
字段的数据。查了两个索引树,这就叫回表。
什么是索引覆盖?
索引覆盖就是查这个索引能查到你所需要的所有数据,不需要去另外的数据结构去查。其实就是不用发生回表操作。
怎么避免?
-
用主键搜索。
-
或者b,c建联合索引(只查询被联合索引覆盖的字段)。
但具体情况要具体分析,索引字段多了,存储和插入数据时的消耗会更大。这是个平衡问题。
为什么设置了命中了索引但还是造成了全表扫描
其中一个原因就是虽然命中了索引,但在叶子节点查询到记录后还要大量的回表,导致优化器认为这种情况还不如全表扫描会更快些