T O P

[资源分享]     你的 SQL 还在回表查询吗?快给它安排覆盖索引

  • By - 楼主

  • 2021-09-07 12:00:09
  • 什么是回表查询

    小伙伴们可以先看这篇文章了解下什么是聚集索引和辅助索引:Are You OK?主键、聚集索引、辅助索引,简单回顾下,聚集索引的叶子节点包含完整的行数据,而非聚集索引的叶子节点存储的是每行数据的辅助索引键 + 该行数据对应的聚集索引键(主键值)。

    假设有张 user 表,包含 id(主键),name,age(普通索引)三列,有如下数据:

    id	name	age
    1	Jack	    18
    7	Alice	    28
    10	Bob	    	38
    20	Carry	    48
    

    画一个比较简单比较容易懂的图来看下聚集索引和辅助索引:

    • 聚集索引:

    • 辅助索引(age):

    如果查询条件为主键,则只需扫描一次聚集索引的 B+ 树即可定位到要查找的行记录。举个例子:

    select * from user where id = 7;
    

    查找过程如图中绿色所示:

    如果查询条件为普通索引(辅助索引) age,则需要先查一遍辅助索引 B+ 树,根据辅助索引键得到对应的聚集索引键,然后再去聚集索引 B+ 树中查找到对应的行记录。举个例子:

    select * from user where age = 28;
    

    上述 select * 等同于 select id, age, name 对吧,id 是主键索引,age 是普通索引,而 name 并不存在于 age 索引的 B+ 树上,所以通过 age 索引查询到 id 和 age 的值之后,还需要去聚集索引上才能查到 name 的值。

    如图所示,第一步,查 age 辅助索引:

    第二步,查聚集索引:

    这就是所谓的回表查询,因为需要扫描两次索引 B+ 树,所以很显然它的性能较扫一遍索引树更低。

    什么是覆盖索引

    覆盖索引的目的就是避免发生回表查询,也就是说,通过覆盖索引,只需要扫描一次 B+ 树即可获得所需的行记录。

    如何实现覆盖索引

    上文解释过,下面这个 SQL 语句需要查询两次 B+ 树:

    select * from user where age = 28;
    

    我们将其稍作修改,使其只需要查询一次 B+ 树:

    select id, age from user where age = 28;
    

    之前我们的返回结果是整个行记录,现在我们的返回结果只需要 id 和 age。

    id 是什么?主键索引(聚集索引),age 是什么?普通索引(辅助索引),age 索引的 B+ 树的叶子节点存储的是什么?辅助索引键 + 对应的聚集索引键

    所以这条 SQL 语句只需要扫描一次 age 索引的 B+ 树就行了

    这样,结合这个例子,不知道各位有没有受到启发,如何实现覆盖索引拒绝回表查询呢?

    答:联合索引

    我们把 age,name 设置为联合索引:

    create index idx_age_name on user(`age`,`name`);
    

    此时 age 和 name 作为辅助索引键都在同一棵辅助索引的 B+ 树上,所以只需扫描一次这个组合索引的 B+ 树即可获取到 id、age 和 name,这就是实现了索引覆盖

    覆盖索引的常见使用场景

    在下面三个场景中,可以使用覆盖索引来进行优化 SQL 语句:

    1)列查询回表优化(如上面讲的例子,将单列索引 age 升级为联合索引(age, name))

    2)全表 count 查询

    举个例子,假设 user 表中现在只有一个索引即主键 id:

    select count(age) from user;
    

    可以用 explain 分析下这条语句,如果 Extra 字段为 Using index 时,就表示触发索引覆盖:

    显然现在是没有触发覆盖索引的,我们来优化下:将 age 列设置为索引 create index idx_age on user(age),这样只需要查一遍 age 索引的 B+ 树即可得到结果:

    3)分页查询

    select id, age, name from user order by username limit 500, 100;
    

    对于这条 SQL,因为 name 字段不是索引,所以在分页查询需要进行回表查询。

    Using filesort 表示没有使用索引的排序,或者说表示在索引之外,需要额外进行外部的排序动作。看到这个字段就应该意识到你需要对这条 SQL 进行优化了。

    使用索引覆盖优化:将 (age, name) 设置为联合索引,这样只需要查一遍 (age, name) 联合索引的 B+ 树即可得到结果。

    我是小牛肉,长风破浪会有时,小伙伴们下篇文章再见 :wave:

    :tada: 关注公众号 | 飞天小牛肉,即时获取更新

    • 博主东南大学硕士在读,携程 Java 后台开发暑期实习生,利用课余时间运营一个公众号『 飞天小牛肉 』,2020/12/29 日开通,专注分享计算机基础(数据结构 + 算法 + 计算机网络 + 数据库 + 操作系统 + Linux)、Java 技术栈等相关原创技术好文。本公众号的目的就是让大家可以快速掌握重点知识,有的放矢。关注公众号第一时间获取文章更新,成长的路上我们一起进步

    • 并推荐个人维护的开源教程类项目: CS-Wiki(Gitee 推荐项目,现已累计 1.8k+ star), 致力打造完善的后端知识体系,在技术的路上少走弯路,欢迎各位小伙伴前来交流学习 ~ :blush:

    • 如果各位小伙伴春招秋招没有拿得出手的项目的话,可以参考我写的一个项目「开源社区系统 Echo」Gitee 官方推荐项目,目前已累计 900+ star,基于 SpringBoot + MyBatis + MySQL + Redis + Kafka + Elasticsearch + Spring Security + ... 并提供详细的开发文档和配套教程。公众号后台回复 Echo 可以获取配套教程,目前尚在更新中。

    本帖子中包含资源

    您需要 登录 才可以下载,没有帐号?立即注册