【mysql】InnoDB二级索引(辅助索引)

相关文章:
【mysql】聚簇索引(聚集索引)和非聚簇索引(二级索引、辅助索引)的区别
【mysql】InnoDB二级索引(辅助索引)

注意:本篇是InnoDB的二级索引实现,MyISAM实现不同。

二级索引:叶子节点中存储主键值,每次查找数据时,根据索引找到叶子节点中的主键值,根据主键值再到聚簇索引中得到完整的一行记录。

问题:

1.相比于叶子节点中存储行指针,二级索引存储主键值会占用更多的空间,那为什么要这样设计呢?

指针长度固定,比如4个字节而已,但是主键值是一个具体的值,那么就可能会很长了

InnoDB在移动行时,无需维护二级索引,因为叶子节点中存储的是主键值,而不是指针。

我们知道聚簇索引存储了完整的数据,当数据发生改动时,对应的叶子节点位置可能会变化,但是Key不会变化。二级索引依赖聚簇索引,因此,如果存储的是位置,那么当聚簇索引变化后,需要同步更新二级索引的的位置信息,难度大效率差。如果存的是key的值,永远能找到对应的叶子节点。

就像你有个朋友,记住手机号就行,随时能找到他;如果记得是门牌号,搬家后,你就必须重新记住新的地址,关键是别人还得通知到你他搬家了,麻烦死了。

2.那么InnoDB有了聚簇索引,为什么还要有二级索引呢?

聚簇索引的叶子节点存储了一行完整的数据,而二级索引只存储了主键值,相比于聚簇索引,占用的空间要少。当我们需要为表建立多个索引时,如果都是聚簇索引,那将占用大量内存空间,所以InnoDB中主键所建立的是聚簇索引,而唯一索引、普通索引、前缀索引等都是二级索引

3.为什么一般情况下,我们建表的时候都会使用一个自增的id来作为我们的主键?

InnoDB中表中的数据是直接存储在主键聚簇索引的叶子节点中的,每插入一条记录,其实都是增加一个叶子节点,如果主键是顺序的,只需要把新增的一条记录存储在上一条记录的后面,当页达到最大填充因子的时候,下一跳记录就会写入新的页中,这种情况下,主键页就会近似于被顺序的记录填满。

若表的主键不是顺序的id,而是无规律数据,比如字符串,InnoDB无法加单的把一行记录插入到索引的最后,而是需要找一个合适的位置(已有数据的中间位置),甚至产生大量的页分裂并且移动大量数据,在寻找合适位置进行插入时,目标页可能不在内存中,这就导致了大量的随机IO操作,影响插入效率。除此之外,大量的页分裂会导致大量的内存碎片。



参考:
《mysql——二级索引(辅助索引)》

### InnoDB 存储引擎中 B+ 树二级索引工作机制 #### 1. B+ 树结构概述 B+ 树是一种平衡树数据结构,广泛用于数据库管理系统中的索引实现。在 MySQLInnoDB 存储引擎里,B+ 树被用来构建高效的索引体系。与普通的 B 树不同,在 B+ 树中只有叶子节点存储真实的数据记录,而非叶节点仅保存键值用于引导查找路径[^3]。 #### 2. 二级索引定义 对于 InnoDB 表而言,默认情况下会创建一个主键作为聚簇索引(clustered index),而其他任何额外建立的索引则被称为二级索引(secondary indexes) 或者辅助索引(auxiliary indexes)[^5]。当用户基于某个字段创建了二级索引之后,该字段上的查询操作就可以利用这个索引来加速访问速度。 #### 3. 工作流程解析 假设存在一张 `users` 表,并且已经针对 `email` 列建立了名为 `idx_email` 的二级索引: - 当执行如下 SQL 查询语句时: ```sql SELECT * FROM users WHERE email='example@domain.com'; ``` - 首先会在内存缓存池(Buffer Pool) 中尝试定位到对应的页(Page), 如果命中,则直接读取;如果没有找到对应页面,则需要从磁盘加载相应部分进入缓冲区; - 接着按照 idx_email 这棵 B+ 树自顶向下逐层比较目标邮箱字符串与其他节点内的关键字大小关系直到抵达最底层即叶子结点位置; - 叶子结点除了包含 email 键外还会携带指向实际行记录物理地址的信息(通常是主键ID),因此下一步就是依据此信息再次回表扫描获取完整的列值集合返回给客户端应用。 这种设计使得即使是在大规模数据集上也能保持较高的检索效率,因为每次只需要遍历一棵相对较小的高度固定的树形结构即可完成精准匹配过程[^4]。 ```python def search_secondary_index(index_tree, key_value): current_node = index_tree.root while not is_leaf(current_node): # 不断深入直至到达叶子节点 child_pointer = find_child_pointer(current_node, key_value) current_node = follow(child_pointer) row_id = locate_row_in_leaf(current_node, key_value) return fetch_full_record(row_id) def is_leaf(node): """判断当前节点是否为叶子节点""" pass def find_child_pointer(node, value): """寻找合适的子节点指针""" pass def follow(pointer): """跟随指针移动至下一个节点""" pass def locate_row_in_leaf(leaf, target_key): """在叶子节点内定位具体的行号""" pass def fetch_full_record(record_identifier): """根据行标识符抓取整条记录""" pass ```
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值