专栏首页一个会写诗的程序员的博客聚合索引和辅助索引有什么区别?【BAT 面试题宝库附详尽答案解析】

聚合索引和辅助索引有什么区别?【BAT 面试题宝库附详尽答案解析】

B+树是为磁盘或其他直接存取辅助设备设计的一种平衡查找树。在B+树中,所有记录节点都是按照键值的大小顺序存放在同一层的叶子节点上,由各叶子节点指针进行连接。

B+树 索引的本质就是B+树在数据库中的实现。 B+索引在数据库中有一个特点是高扇出性,因此在数据库中,B+树的盖度一般都在 2~4层,这也就是说查找某一键值的行记录时最多只需要 2到4次IO, 这倒不错。因为当前一般的机械硬盘每秒至少可以做100次IO,2~4 次的IO意味查询时间只需 0.02 ~ 0.04 秒。

数据库中的B+树索引可以分为:

聚集索引 (clustered index) 和辅助索引 (secondary index),内部都是B+树,即高度平衡。

聚集索引与辅助索引不同的是:

叶子节点存放的是否是一整行的信息。


一 MyISAM索引实现

1. 主键索引

MyISAM引擎使用B+树作为索引结果,叶节点的data域存放的是数据记录的地址。下图为MyISAM表的主索引,Col1为主键。

2. 辅助索引

在MyISAM中,主索引和辅助索引在结构上没有任何区别,只是主索引要求key是唯一的,而辅助索引的key可以重复。下图在Col2上建立一个辅助索引

同样也是一颗B+Tree,data域保存数据记录的地址。因此,MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引,如果指定的Key存在,则取出其data域的值,然后以data域的值为地址,读取相应数据记录。

MyISAM的索引方式也叫做“非聚集”的,之所以这么称呼是为了与InnoDB的聚集索引区分。

二 InnoDB索引实现

1 主键索引

同样是B+树,实现方式却完全不同。InnoDB表数据文件本身就是一个索引结构,树的叶节点data域保存了完整的数据记录,这种索引叫做聚集索引

因为InnoDB的数据文件本身要按主键聚集,所以InnoDB要求表必须有主键(MyISAM可以没有),如果没有显式指定,则mysql会自动选择一个可以唯一标识数据记录的列作为主键。如果不存在这种列,则mysql自动为InnoDB表生成一个隐含字段作为主键,这个字段长度为6个字节,类型为长整型。

2 辅助索引

辅助索引(Secondary Index,也称为非聚集索引).

InnoDB的所有辅助索引都引用主键作为data域。下图为定义在Col3上的一个辅助索引

辅助索引搜索需要检索两遍索引:首先检索辅助索引获得主键,然后用主键到主索引中检索获得记录。

InnoDB 的索引能提供一种非常快速的主键查找性能。不过,它的辅助索引也会包含主键列,所以如果主键定义的比较大,其他索引也将很大。InnoDB 不会压缩索引。

不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助,例如知道了InnoDB的索引实现后,就很容易明白为什么不建议使用过长的字段作为主键,因为所有辅助索引都引用主索引,过长的主索引会令辅助索引变得过大。再例如,用非单调的字段作为主键在InnoDB中不是个好主意,因为InnoDB数据文件本身是一颗B+Tree,非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整,十分低效,而使用自增字段作为主键则是一个很好的选择。

辅助索引的叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外,每个叶子节点中的索引行中还包含了一个书签(bookmark)。该书签用来告诉InnoDB存储引擎哪里可以找到与索引相对应的行数据。书签就是相应行数据的聚集索引键(主键)。

当通过辅助索引来寻找数据时,InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键,然后再通过主键索引来找到一个完整的行记录。

参考资料

https://blog.csdn.net/biggoodloong/article/details/98886082

Kotlin 开发者社区

国内第一Kotlin 开发者社区公众号,主要分享、交流 Kotlin 编程语言、Spring Boot、Android、React.js/Node.js、函数式编程、编程思想等相关主题。

Kotlin 开发者社区

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • [zz学习]MySQL索引背后的数据结构及算法原理MySQL索引背后的数据结构及算法原理MyISAM索引实现InnoDB索引实现

    虽然InnoDB也使用B+Tree作为索引结构,但具体实现方式却与MyISAM截然不同。 第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道,...

    一个会写诗的程序员
  • 聚簇索引与非聚簇索引

    看上去聚簇索引的效率明显要低于非聚簇索引,因为每次使用辅助索引检索都要经过两次B+树查找,这不是多此一举吗?聚簇索引的优势在哪?

    一个会写诗的程序员
  • mysql 查看索引、添加索引、删除索引命令添加索引删除索引

    · Table 表的名称。 · Non_unique 如果索引不能包括重复词,则为0。如果可以,则为1。 · Key_name 索引的名称。 · S...

    一个会写诗的程序员
  • MySQL索引详解

    张申傲
  • 腾讯面试:一条SQL语句执行得很慢的原因有哪些?

    说实话,这个问题可以涉及到 MySQL 的很多核心知识,可以扯出一大堆,就像要考你计算机网络的知识时,问你“输入URL回车之后,究竟发生了什么”一样,看看你能说...

    小云
  • 从千万级数据查询来聊一聊索引结构和数据库原理

    在日常工作中我们不可避免地会遇到慢SQL问题,比如笔者在之前的公司时会定期收到DBA彪哥发来的Oracle AWR报告,并特别提示我某条sql近阶段执行明显很慢...

    Java_老男孩
  • 一看就会的mysql索引优化(真实案例)

    (使用的数据库:MYSQL 5.7 版本,InnoDB 引擎) 自从服务加了Skywalking后,将大部分慢接口暴露出来。于是就有了这次慢接口的优化。大概的...

    居士
  • 5年Java开发经验,面试挂在MySQL InnoDB上!大厂究竟多看重MySQL?

    前一段时间好兄弟找工作,面试 Java 资深研发工程师岗位,接到了不少大厂的面试邀请,有顺利接到 offer 的,也有半道儿面试被卡掉的。但最想去的企业却因为 ...

    烂猪皮
  • 每周一总结(3) 小规范学习

    2. 把带有业务逻辑的getter和setter和单纯的getter和setter从命名上区分开来,方便排查问题

  • Mysql高频面试题

    Feelings are just visitors. Let them come and go.

    小闫同学啊

扫码关注云+社区

领取腾讯云代金券