【mysql】回表、覆盖索引、索引下推

发布时间 2023-11-01 16:34:22作者: 朝阳1

索引类型

索引类型分为主键索引和非主键索引

主键索引的叶子节点存的是整行数据。在 InnoDB 里,主键索引也被称为聚簇索引(clustered index)。
非主键索引的叶子节点内容是主键的值。在 InnoDB 里,非主键索引也被称为二级索引(secondary index)。
聚簇索引
聚簇索引默认是主键,如果表中没有定义主键,InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引,InnoDB 会隐式定义一个主键来作为聚簇索引。
因为B+TREE的原因,最好使用连续的整数字段,更好确定查找访问范围

回表

根据上面的索引结构说明,主键索引和普通索引的查询区别
如果语句是 select * from T where id=500,即主键查询方式,则只需要搜索 id 这棵 B+ 树;
如果语句是 select * from T where k=5,即普通索引查询方式,则需要先搜索 k 索引树,得到 id 的值为 500,再到 id 索引树搜索一次。这个过程称为回表。

覆盖索引

如果执行的语句是 select id from T where k between 3 and 5,这时只需要查 id 的值,而 id 的值已经在 k 索引树上了,因此可以直接提供查询结果,不需要回表。也就是说,在这个查询里面,索引 k 已经“覆盖了”我们的查询需求,我们称为覆盖索引。

索引下推

索引下推是MySQL5.6版本推出的优化手段
默认是开启的,可以通过下面命令关闭
SET optimizer_switch = 'index_condition_pushdown=off';
索引为 index(name,age)
mysql> select * from tuser where name like '张%' and age=10 and ismale=1;
无索引下推每次都需要回表查询,而下推的是在先过滤好结果集,再从结果集的数据