回表与innodb和myisam索引的差异

简单了解两种引擎的索引的结构

myisam索引

非聚集索引

MyISAM的索引与行记录是分开存储的。叫做非聚集索引（UnClustered Index）。

• myisam索引使用b+tree储存
• 主键索引和普通索引结构相同（所以myisam可以没有主键索引）
• 索引的叶子节点储存数据记录的地址/指针

innodb索引

聚集索引

InnoDB的主键索引与行记录是存储在一起的，故叫做聚集索引（Clustered Index），也由此提升主键查询速度

• 索引同样使用b+tree储存
• 聚集索引叶子节点直接储存相应记录（主键索引一般为聚集索引，也一定会有一个聚集索引）
• 普通索引叶子节点储存主键

回表

概念

现在如果在innodb进行一次普通索引的查询

1. 在普通索引树中查找，找到对应叶子节点中储存的主键
2. 返回主键索引树中查找，找到对应叶子节点中储存的数据

以上过程就是回表

前提

所以回表有以下前提

1. innodb引擎
2. 普通索引查询
3. 非覆盖索引的查询
   下面就介绍下如何用覆盖索引避免回表

索引覆盖

概念

如果一个索引包含或者覆盖所有需要查询的字段的值，就称之为索引覆盖
在explain中，显示using index 即为使用索引覆盖

举例

创建User表,只有主键索引userid和普通索引cname

create table User (

userid int primary key,

cname varchar(50),

email varchar(50),

index(cname)

)engine=innodb;

select userid, cname from User where cname = 'wen'

这条sql，在使用cname索引就行查询时，在叶子节点能获取到cname-wen，也能获取到主键userid-5，此时就不在不再需要回表

如果使用这条sql

select userid, cname，email from User where cname = 'wen'

在索引叶子节点，无法获取email的数据，此时就会进行回表
像这样的sql，我们可以建立复合索引

alter table User add index index_cname_email(cname,email);

这时进行查询就不在回表