回表查询 覆盖索引 索引下推

一、什么是回表查询

如果索引的列在select所需获得的列中（因为在mysql中索引是根据索引的值进行排序的，所以索引节点中存在该列中的部分值）或者根据一次索引查询就能获得记录就不需要回表，如果select所需获得的列中有大量非索引列，索引就需要到表中找到相应的列信息，这就叫做回表。

InnoDB聚集索引的叶子结点存储行记录，因此InnoDB必须要有且只有一个聚集索引。

（1）如果表定义了主键，则PK就是聚集索引；

（2）如果表没有定义主键，则第一个非空唯一索引（NOT NULL UNIQUE）列就是聚集索引；

（3）不满足（1）和（2）的话，InnoDB会创建一个隐藏的row-id作为聚集索引

InnoDB聚集索引的叶子节点存储行记录的原因是为了提高查询性能和减少磁盘I/O操作。InnoDB是一种基于簇集索引的存储引擎，聚集索引是按照主键的顺序将数据存储在磁盘上。在InnoDB中，表的主键是聚集索引的键，即聚集索引的叶子节点存储了整个行记录的数据。

这种设计有以下几个好处：

1. 减少磁盘I/O操作：由于数据是按照主键顺序存储的，相邻的行记录通常会在磁盘上相邻存储。这样，当根据主键范围进行查询时，可以减少磁盘的I/O操作，提高查询性能。
2. 避免重复数据：由于聚集索引的叶子节点存储了整个行记录的数据，可以避免在非聚集索引中重复存储主键和行记录的数据，节省了磁盘空间。
3. 支持覆盖索引：由于聚集索引的叶子节点存储了整个行记录的数据，当查询只需要使用聚集索引的列时，可以直接从聚集索引中获取数据，而不需要再去磁盘上读取行记录的数据，提高了查询性能

比如创建表：

create table xttblog(
    id int primary key, 
    k int not null, 
    name varchar(16),
    index (k)
)engine = InnoDB;
1
2
3
4
5
6

然后，我们再执行下面的 SQL 语句，插入几条测试数据:

INSERT INTO xttblog(id, k, name) VALUES(1, 2, 'xttblog'),
    (2, 1, '对线JAVA面试'),
    (3, 3, '对线JAVA面试公众号');
1
2
3

假设，现在我们要查询出 id 为 2 的数据。那么执行 select * from xttblog where ID = 2; 这条 SQL 语句就不需要回表。原因是根据主键的查询方式，则只需要搜索 ID 这棵 B+ 树。主键是唯一的，根据这个唯一的索引，MySQL 就能确定搜索的记录。

但当我们使用 k 这个索引来查询 k = 2 的记录时就要用到回表。select * from xttblog where k = 2; 原因是通过 k 这个普通索引查询方式，则需要先搜索 k 索引树，然后得到主键 ID 的值为 1，再到 ID 索引树搜索一次。这个过程虽然用了索引，但实际上底层进行了两次索引查询，这个过程就称为回表。

也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

总结

使用聚集索引（主键或第一个唯一索引）就不会回表，普通索引就会回表。

二、什么是覆盖索引

那么有没有可能存在这样一种情况，仅仅使用普通索引而不需要回表就可以拿到所需的数据呢？答案是可以的，覆盖索引就可以满足这样的要求。

覆盖索引就是select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说索引中的列包含了想要查询的列。

因为覆盖索引可以减少树的搜索次数，显著提升查询性能，所有覆盖索引是一个常用的性能优化手段。

三、如何实现覆盖索引

1.将被查询的字段，建立到联合索引里去

create table user (
id int primary key,
name varchar(20),
sex varchar(5),
index(name)
)engine=innodb;
1
2
3
4
5
6

select id,name from user where name='shenjian';
1

能够命中name索引，索引叶子节点存储了主键id,通过name的索引树即可获得id和name,无需回表，符合索引覆盖，效率高。Using index

select id,name,sex from user where name='shenjian';
1

能够命中name索引，索引叶子存储了主键id，没有存储sex,sex字段必须回表查询才能获取到，不符合索引覆盖，需要再次通过id值扫描聚集索引获取sex字段，效率回降低。

Using index condition

如果把(name)单列索引升级为联合索引(name, sex)就不同了。

create table user1 (
id int primary key,
name varchar(20),
sex varchar(5),
index(name, sex)
)engine=innodb;
1
2
3
4
5
6

单列索升级为联合索引(name, sex)后，索引叶子节点存储了主键id，name，sex，都能够命中索引覆盖，无需回表。

四、什么是索引下推

是一种优化技术，用于在数据库查询中将过滤条件下推到数据存储引擎的索引层，以减少数据的读取和处理量，提高查询性能。

可以在索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表字数。

select * from tuser where name like '张 %' and age=10
1

图 1 中，在 (name,age) 索引里面我特意去掉了 age 的值，这个过程 InnoDB 并不会去看 age 的值，只是按顺序把“name 第一个字是’张’”的记录一条条取出来回表。因此，需要回表 4 次。

图 2 跟图 1 的区别是，InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 10，对于不等于 10 的记录，直接判断并跳过。在我们的这个例子中，只需要对 ID4、ID5 这两条记录回表取数据判断，就只需要回表 2 次。

总结:

如果没有索引下推优化（或称ICP优化），当进行索引查询时，首先根据索引来查找记录，然后再根据where条件来过滤记录；在支持ICP优化后，MySQL会在取出索引的同时，判断是否可以进行where条件过滤再进行索引查询，也就是说提前执行where的部分过滤操作，在某些场景下，可以大大减少回表次数，从而提升整体性能。

MySQL会在取出索引的同时，判断是否可以进行where条件过滤再进行索引查询，也就是说提前执行where的部分过滤操作，在某些场景下，可以大大减少回表次数，从而提升整体性能。