首页 > 其他分享 >索引的分类及优化回表查询的方案

索引的分类及优化回表查询的方案

时间:2023-02-07 19:22:40浏览次数:46  
标签:聚簇 Tree 查询 回表 索引 主键

索引可以按照以下几个角度进行分类:

从数据结构的角度分类

  • B+tree 索引
  • Hash 索引
  • Full-texts索引

从物理存储的角度分类

  • 聚簇索引
  • 二级索引 【 辅助索引 】

从索引字段特性的角度分类

  • 主键索引
  • 唯一索引
  • 普通索引
  • 前缀索引

从组成索引的字段个数的角度分类

  • 单列索引
  • 联合索引 【 复合索引 】


一:聚簇索引

  InnoDB表要求必须有聚簇索引(Clustered Index),默认在主键字段上建立聚簇索引,在没有主键字段的情况下,表的第一个 NOT NULL 的唯一索引将被建立为聚簇索引,在前两者都没有的情况下,InnoDB 将自动生成一个隐式自增id列并在此列上创建聚簇索引。除聚簇索引外,其他的都称之为非主键索引,非主键索引也被称为二级索引(Secondary Index),或者叫作辅助索引。

  对于 聚簇索引二级索引,使用的数据结构都是 B+Tree,唯一的区别在于叶子结点中存储的内容不同

  • 聚簇索引的叶子结点存储的是一行完整的数据。
  • 二级索引的叶子结点存储的则是主键值。

二:回表(问题)

当我们需要查询的时候:

  1. 如果是通过主键索引来查询数据,例如 select * from user where id=100,那么此时只需要搜索主键索引的 B+Tree 就可以找到数据。
  2. 如果是通过非主键索引来查询数据,例如 select * from user where username='carver',那么此时需要先搜索 username 这一列索引的 B+Tree,搜索完成后得到主键的值,然后再去搜索主键索引的 B+Tree,就可以获取到一行完整的数据。

    注意:对于第二种查询方式而言,一共搜索了两棵 B+Tree,第一次搜索 B+Tree 拿到主键值后再去搜索主键索引的 B+Tree,这个过程就是所谓的回表查询 哦!

从上面的分析中我们也能看出,通过非主键索引查询要扫描两棵 B+Tree,而通过主键索引查询只需要扫描一棵 B+Tree,所以如果条件允许,还是建议在查询中优先选择通过主键索引进行搜索。
  那么不用聚簇索引就一定需要回表吗?不一定,请看覆盖索引

三:覆盖索引(解决方案)

  如果索引包含所有需要查询的数据, 这样的索引称为覆盖索引具有覆盖索引的表查询时不需要回表

  简单的说,覆盖索引覆盖所有需要查询的字段(即:大于或等于所查询的字段)。MySQL可以通过索引获取查询数据,因而不需要读取数据行。

  覆盖索引的好处:

    1. 索引大小远小于数据行大小。因而,如果只读取索引,则能极大减少对数据访问量。
    2. 索引按顺序储存。对于IO密集型的范围查询会比随机从磁盘读取每一行数据的IO要少。
    3. 避免对主键索引的二次查询。二级索引的叶子节点包含了主键的值,如果二级索引包含所要查询的值,则能避免二次查询主键索引(聚簇索引:聚簇索引既存储了索引,也储存了值)。

最后附MySQL数据库体系结构图一张

 

标签:聚簇,Tree,查询,回表,索引,主键
From: https://www.cnblogs.com/carver/p/17099552.html

相关文章