B+树索引（10）之回表的代价

B+树索引（10）之回表的代价

前言

什么是回表

回表操作一般发生在二级索引中，查询的列不全部包含在二级索引中，需要根据二级索引叶子节点中的主键id，再去聚簇索引中查询完整的用户数据，这个查询过程称为回表。

如存在测试表person_info

CREATE TABLE person_info(
    id INT NOT NULL auto_increment,
    name VARCHAR(100) NOT NULL,
    birthday DATE NOT NULL,
    phone_number CHAR(11) NOT NULL,
    country varchar(100) NOT NULL,
    PRIMARY KEY (id),
    KEY idx_name_birthday_phone_number (name, birthday, phone_number)
);
1
2
3
4
5
6
7
8
9

同时二级索引也被称为组合索引的idx_name_birthday_phone_number数据索引结构如下

B+树叶子节点中包含name、birthday、phone_number、id不包含country列，如果存在如下SQL

select country,name,birthday from person_info limit 10;
1

name,birthday列可以通过组合索引idx_name_birthday_phone_number的叶子节点直接获取，而country需要根据主键id到主键索引树获取，获取country列信息的过程被称为回表。

回表的代价

又如存在如下SQL

select * from person_info where name > 'Asa' and name < 'Barlow';
1

因为查询列是全部sql显然也是需要回表操作，不过这里需要提到两个概念随机IO和顺序IO。

由于存在组合索引idx_name_birthday_phone_number，所以name是根据顺序排列，而根据条件name > ‘Asa’ and name < 'Barlow’取这中间的数据大概率是一片连续的空间，所以我们可以通过获取一个数据页或者几个数据页就能很快得到数据，这种读取磁盘数据的过程称为顺序IO。

而我们的二级索引不包含country列，所以需要根据在name > ‘Asa’ and name < 'Barlow’范围内的所有记录id，去聚簇索引中再次查询完整记录，而id可能是随机分配到不同的数据页，这个查询难度远高于name列值的查询，这种读取方式被称为随机IO。

大多数情况下顺序IO性能远高于随机IO。

所以我们可以根据分析步骤可以得到使用组合索引查询时分为两个特点

• 查询会用到两个索引一个为组合索引，一个为聚簇索引。

• 访问组合索引一般采用顺序IO，访问聚簇索引一般采用随机IO。

也就是说回表的数据量大那么使用组合索引的效率越低，如果在上面**name > ‘Asa’ and name < ‘Barlow’**范围内的数据占据整个表数据的90%以上那么还不如采用全表扫描，那么什么时候采用全表扫描，什么时候采用索引查询呢？这取决于优化器，而优化器参考的就是回表的记录数。

所以上述SQL如果写成如下所示

select * from person_info where name > 'Asa' and name < 'Barlow' limit 10;
1

回表次数减少自然优化器会更加偏向于二级索引（组合索引）+回表的操作。

如何避免回表

知道了回表的代价，我们就需要知道如何避免，前面分析回表的原因就是因为查询列不完全在索引列之间，所以才需要根据主键id，去聚簇索引中再次查询，根据这个原因我们可以采用索引覆盖来解决此问题

如下，虽然没有用limit限制，但是在组合索引idx_name_birthday_phone_number中包含了查询的所有列，所以不需要再次回表，就可以达到目的。

select name, birthday, phone_number from person_info 
where name > 'Asa' and name < 'Barlow';
1
2

通过上面的分析我们知道查询列会影响到组合索引的查询，所以我们尽量不要使用类似**select * **的语法，需要明确指定查询的列。