什么是回表（使用索引查询完整数据过程）？

当我们需要查询一条完整的数据的时候：

  • 如果是通过聚簇索引来查询数据，例如 select * from user where id=100，那么此时只需要搜索聚簇索引的 B+Tree 就可以找到数据。
  • 如果是通过非聚簇索引来查询数据，例如 select * from user where username=zhangsan'，那么此时需要先搜索 username 这一列索引的 B+树，搜索完成后得到主键的值，然后再去搜索聚簇索引的 B+树，就可以获取到一行完整的数据。
    对于第二种查询方式而言，一共搜索了两棵 B+树，第一次搜索 B+树 拿到主键值后再去搜索聚簇索引的 B+树，这个过程就是所谓的回表。

回表的代价？

回表的代价：

  • 需要回表的记录越多，使用二级索引的性能就越低，甚至让某些查询宁愿使用全表扫描也不使用二级索引 。
  • 比方说 name 值在 Asa ～ Barlow 之间的用户记录数量占全部记录数量90%以上，那么如果使用 idx_name_birthday_phone_number 索引的话，有90%多的 id 值需要回表，这不是吃力不讨好么，还不如直接去扫描聚簇索引（也就是全表扫描）。
    那什么时候采用全表扫描的方式，什么时候使用采用 二级索引 + 回表 的方式去执行查询呢？
  • 这个就是查询优化器做的工作，查询优化器会事先对表中的记录计算一些统计数据，然后再利用这些统计数据根据查询的 条件来计算一下需要回表的记录数，需要回表的记录数越多，就越倾向于使用全表扫描，反之倾向于使用 二级索引 + 回表 的方式。

非聚簇索引一定会回表查询吗？

不一定：

  • 这涉及到查询语句所要求的字段是否全部命中了索引，如果全部命中了索引，那么就不必再进行回表查询。一个索引包含所有需要查询字段的值，被称之为”覆盖索引”。
  • 举个简单的例子，假设我们在学生表的成绩上建立了索引，那么当进行select score from student where score > 90的查询时，在索引的叶子节点上，已经包含了score 信息，不会再次进行回表查询。