一、什么是回表查询?
这先要从InnoDB的索引实现说起,InnoDB有两大类索引:
聚集索引(clustered index)
普通索引(secondary index)
InnoDB聚集索引和普通索引有什么差异?
InnoDB 聚集索引 的叶子节点存储行记录,因此,
InnoDB必须要有,且只有一个聚集索引:
如果表定义了PK,则PK就是聚集索引;
如果表没有定义PK,则第一个not NULL unique列是聚集索引;
否则,InnoDB会创建一个隐藏的row-id作为聚集索引;
所以PK查询非常快,直接定位行记录。
InnoDB 普通索引 的叶子节点存储主键值。
注意,不是存储行记录头指针,MyISAM的索引叶子节点存储记录指针。
举个栗子,不妨设个表:
t(id PK, name KEY, sex, flag);
画外音:id是聚集索引,name是普通索引。
表中有四条记录:
1, shenjian, m, A
3, zhangsan, m, A
5, lisi, m, A
9, wangwu, f, B
两个B+树索引分别如上图:
- id为PK,聚集索引,叶子节点存储行记录;
- name为KEY,普通索引,叶子节点存储PK值,即id;
既然从普通索引无法直接定位行记录,那 普通索引的查询过程是怎么样的呢?
通常情况下,需要扫码两遍索引树。
例如:
select * from t where name=‘lisi’;
是如何执行的呢?
如 粉红色 路径,需要扫码两遍索引树:
- (1)先通过普通索引定位到主键值id=5;
- (2)在通过聚集索引定位到行记录;
索引覆盖
执行:
- select id,name from user where name = ‘zs’;
能够命中name索引,索引叶子节点存储了主键id,通过name的索引树即可获取id和name,无需回表,符合索引覆盖,效率较高。
- select id,name,sex from user where name = ‘zs’;
能够命中name索引,索引叶子节点存储了主键id,但age字段必须回表查询才能获取到,不符合索引覆盖,需要再次通过id值扫码聚集索引获取age字段,效率会降低
优化:
Q :上述这种情况怎样能避免回表查询呢?
A : 将单列索引(name)升级为联合索引(name,sex)