一:回表查询
InnoDB引擎 索引分为2大类:
1:聚集索引(clustered index)
2:普通索引(secondary index)
InnoDB聚集索引的叶子节点存储行记录,因此 InnoDB必须要有而且只有一个聚集索引
如果表定义了PK,则PK就是聚集索引
如果表没有定义PK,则第一个not NULL unique列是聚集索引
否则,InnoDB会创建一个隐藏的row-id作为聚集索引
画外音:所以PK查询非常快,直接定位行记录
InnoDB普通索引的叶子节点存储主键值
画外音:不是存储行记录头指针,MyISAM的索引叶子节点存储记录指针。
例子说明问题:
id是主键,聚集索引,叶子节点存储行记录
name是key,普通索引,叶子节点存储的是PK值,即id
从上图看出,从普通索引无法直接定位行记录,那么普通索引的查询过程是怎么样的?
通常情况下需要扫码2遍索引树
举个例子
select * from index_testing where name = "lisi"
粉红色路径,需要扫码两遍索引树:先通过普通索引定位到主键值id=5;在通过聚集索引定位到行索引,这就是所谓的回表查询,先定位主键值再定位行记录,它的性能比扫一遍索引树更低!!
二:索引覆盖 Covering index
explain的输出结果Extra字段为Using index时,能够触发索引覆盖
只需要在一棵索引树上就能获取SQL所需的所有列数据,无需回表,速度更快
如何实现索引覆盖:将被查询的字段,建立到联合索引里去
例子1:
explain select id,name from index_testing where name = "lisi"
explain select id from index_testing where name = "lisi"
explain select name from index_testing where name = "lisi"
能够命中 index_name 索引,索引叶子节点存储了主键id,通过name的索引树就可以获得id和name,不要回表,符合索引覆盖,效率高
例子2:
explain select id,name,sex from index_testing where name = "lisi"
能够命中 index_name 索引,索引叶子节点存储了主键id,但是sex字段必须回表才能查询到,不符合索引覆盖,需要再次通过id值扫描聚集索引获得sex字段,效率会降低。
例子3:
将索引index_name 改成 name和sex 2个字段
explain select id,name from index_testing where name = "lisi";
explain select id,name,sex from index_testing where name = "lisi";
都能命中索引覆盖,不需要回表
三:可以利用索引覆盖来优化SQL的场景
1:全表count查询优化
优化前:没有index_name索引,不能利用索引覆盖
优化后:添加索引,能够使用索引覆盖提效
2:列查询回表优化:将单列索引(name)升级为联合索引(name, sex)
如果索引是这么建立的也是不行的
3:分页查询
select id,name,sex from index_testing order by name limit 500,100;