专栏首页一个会写诗的程序员的博客[zz学习]MySQL索引背后的数据结构及算法原理MySQL索引背后的数据结构及算法原理MyISAM索引实现InnoDB索引实现

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

MySQL索引背后的数据结构及算法原理

MyISAM索引实现

image.png

InnoDB索引实现

虽然InnoDB也使用B+Tree作为索引结构,但具体实现方式却与MyISAM截然不同。 第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道,MyISAM索引文件和数据文件是分离的,索引文件仅保存数据记录的地址。而在InnoDB中,表数据文件本身就是按B+Tree组织的一个索引结构,这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键,因此InnoDB表数据文件本身就是主索引。

图10 图10是InnoDB主索引(同时也是数据文件)的示意图,可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集,所以InnoDB要求表必须有主键(MyISAM可以没有),如果没有显式指定,则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL自动为InnoDB表生成一个隐含字段作为主键,这个字段长度为6个字节,类型为长整形。 第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说,InnoDB的所有辅助索引都引用主键作为data域。例如,图11为定义在Col3上的一个辅助索引:

图11 这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效,但是辅助索引搜索需要检索两遍索引:首先检索辅助索引获得主键,然后用主键到主索引中检索获得记录。 了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助,例如知道了InnoDB的索引实现后,就很容易明白为什么不建议使用过长的字段作为主键,因为所有辅助索引都引用主索引,过长的主索引会令辅助索引变得过大。再例如,用非单调的字段作为主键在InnoDB中不是个好主意,因为InnoDB数据文件本身是一颗B+Tree,非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整,十分低效,而使用自增字段作为主键则是一个很好的选择。

B-Tree

image.png

image.png

image.png

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

我来说两句

0 条评论
登录 后参与评论

相关文章

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

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

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

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

    一个会写诗的程序员
  • SQL 差集计算的例子

    一个会写诗的程序员
  • MySQL探秘(七):InnoDB行锁算法

     在上一篇《InnoDB一致性非锁定读》中,我们了解到InnoDB使用一致性非锁定读来避免在一般的查询操作(SELECT FOR UPDATE等除外)时使用锁。...

    程序员历小冰
  • MySQL探秘(七):InnoDB行锁算法

     在上一篇《InnoDB一致性非锁定读》中,我们了解到InnoDB使用一致性非锁定读来避免在一般的查询操作(SELECT FOR UPDATE等除外)时使用锁。...

    aoho求索
  • MySQL InnoDB表和索引之聚簇索引与第二索引

    每个InnoDB表都有一个称之为聚簇索引(clustered index)的特殊索引,存储记录行数据。通常,聚簇索引和主索引是近义的。

    授客
  • 「Mysql索引原理(六)」聚簇索引

    本节课主要关注InnoDB,但是这里讨论的原理对于任何支持聚簇索引的存储引擎都是适用的。

    源码之路
  • MySql中InnoDB表为什么要建议用自增列做主键

    如果我们定义了主键(PRIMARY KEY),那么InnoDB会选择主键作为聚集索引、如果没有显式定义主键,则InnoDB会选择第一个不包含有NULL值的唯一索...

    一觉睡到小时候
  • 优化页面访问速度(二) ——数据库优化

    数据库优化,主要包括数据表设计、索引、sql语句、表拆分、数据库服务器架构等方向的优化。

    用户1327360
  • 图解:深入理解MySQL索引底层数据结构与算法

    以Col1为主键,则上图是一个MyISAM表的主索引(Primary key)示意

    你好戴先生

扫码关注云+社区

领取腾讯云代金券