从数据页和B+树的角度看索引失效原因

最新推荐文章于 2025-12-22 09:37:19 发布
原创 最新推荐文章于 2025-12-22 09:37:19 发布 · 186 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#mysql #后端 #1024程序员节

本文详细解释了InnoDB存储引擎中索引失效的原理，通过数据页结构和B+树的视角分析索引失效场景，包括聚簇索引、二级索引和联合索引的B+树结构，以及索引覆盖和回表的概念。

📚 全文字数 : 6k+

⏳ 阅读时长 : 8min

📢 关键词 : 数据页结构、索引的B+树结构、索引失效原理

场景再现，有多少朋友有过的？

面试官👴：我看你熟悉简历数据库索引，那索引失效有哪些场景？

我👦：巴拉巴拉，把从晚上背的6，7条失效场景一字不落的背出来了

我👦：心里想，这问题能难道我？

面试官👴：能具体一点吗，上一位面试者也是这么说的

我👦：额，心里想，我没背过这个知识点啊，芭比Q了

前言

文章开头的面试场景不是我编出来的，兄弟们，刚毕业一两年面试的我就出现过这种问题。仅仅问你失效场景，只要准备过面试的人都能答出来。但是再往下问问，就不知道怎么答了。

这篇文章将从InnoDB存储引擎的索引B+树和数据页的角度一起来看实际的索引失效问题，设计的内容比较多，关于【数据页】和【索引的知识结构】只是可以翻看我之前的文章有更详细的内容。

大纲

先看大纲，让自己知道今天要讲的内容。

什么是索引失效

我们知道创建索引的目的是为了提高查询速率，但并不是创建了索引就能让查询速率显著提高的。

稍不注意，你是在列上建了索引，可能你写的查询条件也是索引列，但最终执行计划没有走它的索引，从而走了全表扫描，这种建了索引而实际索引没用的情形就是索引失效。

从数据页看B+树

（1）在叶子节点一层，所有记录的主键按照从小到大的顺序排列，并且形成了一个双向链表，便于范围查询。叶子节点的每一个Key指向一条记录。

（2）非叶子节点取的是叶子节点里面Key的最小值。这意味着所有非叶子节点的Key都是冗余的叶子节点。同一层的非叶子节点也互相串联，形成了一个双向链表

在了解索引和索引失效之前，我们应该对数据页，数据页中数据的存储方式，如何构建B+树的这些原理搞清楚！

这些对后续理解为啥使用B+树，B+树的特性会有很大的帮助，而不是为了应付才去死记硬背一些没什么营养的答案。

数据页结构

MySQL读取数据都是以【数据页】为单位读取的，而不是需要读取一条记录的时候就读记录本身，以数据页为读取单位的话，需要将其整体读取内存中，但是各个数据页之间是不连续的。

而数据页默认大小为16KB，意味着每次至少是将16KB的内容疏导内存中。

左侧的是组成数据页的7大部分，右侧是这几部分的简要说明。

不同数据页之间既然不是连续的，那怎么知道这个数据页的下个页在哪？

其实File Header中有两个指针，分别指向上一个数据页和下一个数据页

最低0.47元/天 解锁文章
MySQL高频基本面试问题整理
曾经“等你生日那天”都遥远得像未来，如今却可欢愉的挥手说“下个十年见”
04-07 174万+
总结大厂MySQL高频基本面试问题整理，用于快速查缺补漏
使用B+树优化LSM树的Tiering merge策略的读取速度
C++，存储，模糊测试，Linux
04-03 1134
本博文主要内容为自己对一篇论文的理解，论文将LSM树和B+Tree这两种在数据存储中最为经典高效的数据结构进行结合，使用B+树优化LSM树的Tiering merge策略的读取速度。实验结果表明，所设计的数据结构和策略可以在与tiering merge策略达到相同抑制写放大的水平的情况下，提高其读取的吞吐量。
MySQL：InnoDB磁盘数据页的存储结构
OceanStar的博客
12-24 1794
磁盘数据页的存储结构 在深入研究索引之前，我们必须先了解磁盘上的数据文件中的数据页的物理存储结构，因为索引的物理存储结构和使用原理都是跟数据页的物理存储结构有很大关联的 我们知道，数据库最终所有的数据（包括我们建的各种表和表里的数据）都是要存放在磁盘上的文件的，然后在文件里存放的物理格式就是数据页，那么大量的数据页在磁盘文件里是怎么存储的呢？ 另外，大量的数据页是按照顺序一页一页的存放的，然后两个相邻的数据页之间会采用双向链表的格式相互引用。如下图： 那么上图中在磁盘文件里到底是怎么弄出来的呢？ 其实
数据库页的槽(slot)管理-保证数据库页写入数据的有序性
snoopy
02-14 2719
数据存储结构 mysql数据库的数据存储结构是通过表空间（table space）->段(segment)->区（或者簇extends)->页（page)进行管理。 其中页是最小管理单元，默认页的大小为16kb.现在的问题是数据库表的记录，在页内是如何进行管理的。本文主要关注的是数据记录在页内是如何保持有序的呢？答案就是槽管理。 槽管理 Innodb是多条记录对应一个槽，而槽的作用就是用来在页面内进行数据搜索的，因为查找一条数据时，是用B+tree来保证通过树形结构找到一个记录所在的页，而
mysql page_一文理解MySQL中的page页
weixin_26786277的博客
01-18 2511
在介绍InnoDB中的页的时候，很有必要先让大家了解一下InnoDB中的存储结构从InnoDB存储引擎的逻辑结构看，所有数据都被逻辑地存放在一个空间内，称为表空间(tablespace)，而表空间由段(sengment)、区(extent)、页(page)组成。 在一些文档中extend又称块(block)。一、表空间(table space)表空间(Tablespace)是一个逻辑容器，表空间存...
【MySQL】好好学习一下InnoDB中的页
DreamSun的博客
08-20 627
周末没啥计划，把大佬的又翻了出来，重新学习一下页的概念。页这个东西，看起来不怎么显眼，但是深层的东西都会碰到他，又爱又恨，逼着人必须弄懂。页是存储的基础，也是索引的基础，了解了页后面就可以深入的了解索引了。这一块没了解太深，毕竟这东西其实我应用的场景几乎没有，主要是不弄清楚后面读起来很难受。尽量做到了自己去输出东西，整理了一些问题，但是毕竟站在别人修好的路上面，有些东西不能保证一定是对的，也有可能是我理解有误，如果有问题建议去看原文或者官方文档。
【MySQL】InnoDB数据页结构
liuwanqing233333的博客
02-28 453
本文主要是 InnoDB 数据页结构的介绍
03- MySql 从数据页的角度看 B+ 树
YoungSoulwt的博客
07-09 1125
MySql 从数据页的角度看 B+ 树
B+树实现MySQL索引原理及索引失效问题
qq_44751486的博客
03-22 1792
常见的索引实现方式优缺点分析（为什么选择B+树？）： 哈希表： 1、Hash存储需要将所有的数据文件全部加载到内存，比较浪费存储空间 2、Hash存储适合等值查询，而目前根据范围区间查询数据使用的更多 平衡二叉查找树 / B树： 尽管平衡二叉查找树查询的性能也很高，时间复杂度是 O(logn)。而且，对树进行中序遍历，我们还可以得到一个从小到大有序的数据序列，但这仍然不足以支持按照区间快速查找数据。 B+树： B+树中的节点并不存储数据本身，而是只是作为索引。除此之外，每个叶子节点串在一条链表上，链表中的数
大厂面试官问我：Mysql B+树索引中B最后一层是双向链表还是单向链表？为什么这么设计？【后端八股文十四：Mysql索引八股文合集】
qq_33445788的博客
07-24 1880
范围查询效率高：由于叶子节点之间有指针连接，可以通过顺序遍历叶子节点获取范围内的数据记录，避免了不必要的磁盘访问和索引的跳转。顺序访问优化：当进行顺序访问时，B+树可以通过双向链表进行高效的顺序遍历，减少磁盘IO次数，提高访问性能。如果 SQL 语句中用到了组合索引中的最左边的索引，那么这条 SQL 语句就可以利用这个组合索引去进行匹配。当遇到范围查询(betweenlike)就会停止匹配，后面的字段不会用到索引。例子：对(a,b,c,d)建立索引，查询条件为。
阿里三面：你跟我讲讲MySQL的B+树
wdjnb的博客
12-17 2207
大家好，我是小林。 大家背八股文的时候，都知道 MySQL 里 InnoDB 存储引擎是采用 B+ 是来组织数据的。 这点没错，但是大家知道 B+ 树里的节点里存放的是什么呢？查询数据的过程又是怎样的？ 这次，我们从数据页的角度看 B+ 树，看看每个节点长啥样。 InnoDB 是如何存储数据的？ MySQL 支持多种存储引擎，不同的存储引擎，存储数据的方式也是不同的，我们最常使用的是 InnoDB 存储引擎，所以就跟大家图解下InnoDB 是如何存储数据的。 记录是按照行来存储的，但是数
InnoDB数据存储结构
m0_57001006的博客
06-27 1888
页a, 页b, 页c… 页n这些页可以不在物理结构上相连, 只要通过双向链表相关联即可. 每个数据页中的记录会按照主键值从小到达的顺序组成一个单向链表, 每个数据页都会为存储在它里面的记录生成一个页目录, 在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽位, 然后再遍历该槽对应分组中的记录即可快速找到指定的记录页目录等我们下面详细介绍页结构的会讲解注意: 这些页是同一层的页, 可以不在物理结构上相连, 但是页分配的时候确实是相连的, 但是呐不是同一层相连。
深度整理总结MySQL——数据页(索引页)的结构
左灯右行的爱情
02-06 1433
页是InnoDB管理存储空间的基本单位，一个页的大小一般是16KB。InnoDB为了不同的目的而设计了许多种不同类型的页，比如存放表空间头部信息的页，存放Insert Buffer信息的页，存放INODE信息的页，存放undo日志信息的页等等等等。本章聚焦在存放我们表中记录的那种类型的页，官方称这种存放记录的页为索引（INDEX）页,我们也可以叫做数据页.nnoDB 的数据是按「数据页」为单位来读写的，默认数据页大小为 16 KB。每个数据页之间通过双向链表的形式组织起来，物理上不连续，但是逻辑上连续。
SQL Server 存储(1/8)：理解数据页结构
Hehuyi_In的博客
05-06 3460
以下来自Woodytu的sqlserver存储系列，一共八篇，记录下来学习 https://www.cnblogs.com/woodytu/p/4484328.html SQL Server 存储(1/8)：理解数据页结构 SQL Server用8KB的页来存储数据，并且在SQL Server里磁盘 I/O 操作在页级执行。SQL Server把数据记录存在数据页（Data Page）...
MySQL 无缝迁移到达梦：前置方法与全面评估分析指南
数记
12-20 1057
MySQL迁移至达梦数据库（DM）的兼容性评估与迁移策略摘要 本文系统分析了从MySQL迁移到达梦数据库的关键步骤： 兼容性评估：检查表结构、SQL语法、存储过程等对象的兼容性，对比事务隔离、字符集等特性差异，使用达梦工具（如DTS、SQL Checker）辅助分析。 工作量分级：根据适配难度分为低（直接兼容）、中（语法调整）、高（代码重写）和阻断项（需业务改造），示例评估显示存储过程改造占比最高（40%）。 迁移实施：分阶段进行试点验证、灰度同步（dmdrs工具）和全量切换，结合达梦DTS工具自动转换，配
基于 MySQL Monitoring and Management 的性能保障实践
星哥玩云
12-17 971
摘要：本文探讨MySQL Monitoring and Management（MMM）工具在数据库性能保障中的实践应用。MMM作为官方推荐的轻量化监控方案，具有全栈指标覆盖、易部署和可视化告警等优势。重点分析了查询执行时间、连接数和缓存命中三大核心指标的监控逻辑与优化策略，并提供了从部署到日常监控的完整操作流程。通过电商平台案例展示了MMM在实际场景中的故障定位与性能优化价值，最后提出指标联动、动态阈值调整等优化建议，为构建高效的MySQL监控体系提供实用指南。
海外开发者实践分享：用 MoonBit 开发 SQLC 插件（其二）
m0_74743788的博客
12-18 848
第一篇的链接：https://zenn.dev/4245ryomt/articles/9680434dc60c0c本文中使用的moon。
MySQL 数据类型全面详解（含案例+总结）
最新发布
qq_59093178的博客
12-22 519
MySQL 数据类型全面详解（含案例+总结）
Canal深度解析：MySQL增量数据订阅与消费实战
qq_41262225的博客
12-18 827
Canal实战指南：MySQL增量数据同步利器 Canal是阿里巴巴开源的MySQL增量数据订阅组件，基于MySQL主从复制协议实现毫秒级数据同步。核心原理是伪装成MySQL从库，通过解析binlog获取数据变更，支持Row格式的binlog解析。典型应用场景包括缓存同步、实时数据分析、数据异构迁移等。 Canal架构包含三个核心组件：EventParser负责连接MySQL并解析binlog；EventSink进行数据过滤和路由；EventStore存储事件数据。部署时需配置MySQL开启binlog并设
从数据结构的角度讲一下索引加快查询的本质
12-04
<think> 我们正在讨论MySQL索引如何从数据结构角度加快查询速度。用户希望从数据结构的角度理解索引加速查询的本质。 根据引用[1]，我们知道索引遵循一些基本原则，但用户现在关心的是背后的数据结构原理。 在数据库中，索引是一种数据结构，它允许快速查找和访问数据。最常用的索引结构是B树（及其变种如B+树）和哈希索引。 1. B树/B+树索引： - B树是一种自平衡的树数据结构，它保持数据排序，并允许对数时间复杂度的搜索、顺序访问、插入和删除。 - 在数据库中，B+树是更常用的索引结构。与B树相比，B+树的所有数据都存储在叶子节点，内部节点只存储键（用于导航）。叶子节点之间通过指针连接，便于范围查询。 - 通过使用B+树索引，数据库可以快速定位到数据所在的位置，而不需要全表扫描。例如，在一个有1亿条记录的表上，全表扫描需要遍历1亿条记录，而使用B+树索引，可能只需要3-4次磁盘I/O（因为树的高度很低）就能定位到数据。 2. 哈希索引： - 哈希索引基于哈希表实现，它通过将键值映射到哈希表中的位置来快速查找。 - 哈希索引对于等值查询非常快，时间复杂度接近O(1)。但它不支持范围查询（如WHERE a > 10），并且哈希冲突也需要处理。 为什么索引能加快查询？ 本质上是减少了需要扫描的数据量（即减少了磁盘I/O操作）。在没有索引的情况下，数据库必须进行全表扫描（顺序访问每一行数据）来找到满足条件的行。而使用索引后，数据库可以利用索引数据结构（如B+树）快速定位到数据的位置，然后只读取相关的数据页（通常是磁盘块）。 举例说明： 假设有一个表`users`，其中有一个索引列`age`。当执行查询`SELECT * FROM users WHERE age=25`时： - 如果没有索引：数据库需要逐行扫描整个表，检查每一行的`age`是否为25。 - 如果有B+树索引：数据库从索引的根节点开始，根据键值（25）逐层向下查找，最终在叶子节点找到所有age=25的行的位置（通常是行的物理地址），然后根据这些地址去读取对应的行。由于B+树的高度通常很低（3-4层），所以只需要几次磁盘I/O操作。 注意：索引虽然能加快查询，但也有代价。它会占用额外的存储空间，并且在数据更新（插入、删除、修改）时需要维护索引，这会导致写操作变慢。 因此，从数据结构的角度看，索引加速查询的本质在于其高效的数据结构（如B+树）能够快速定位数据，从而避免了低效的全表扫描。 参考文献： [1] MySQL索引原理及慢查询优化 §§相关问题§§ 1. B树和B+树在索引中的区别是什么？ 2. 为什么数据库索引通常使用B+树而不是哈希表？ 3. 索引的维护成本体现在哪些方面？ </think> ### 从数据结构角度分析索引加速查询的本质 索引的本质是一种**高效查找的数据结构**，它通过建立键值与数据位置的映射关系，将随机查询转化为高效的有序查找。核心原理如下： #### 1. **数据结构基础：B+树** MySQL索引主要采用**B+树**结构（InnoDB引擎），其优势在于： - **多路平衡查找树**：每个节点可存储多个键值（如1000+），大幅降低树高度（3-4层可存储数十亿数据）[^1] - **有序性存储**：所有键值按顺序存储在叶子节点，满足快速范围查询（`WHERE age>20`） - **物理连续性**：叶子节点通过指针串联，支持高效顺序扫描（避免磁盘随机I/O） 例如查询`WHERE id=100`： ```mermaid graph LR A[根节点<br>50, 150] --> B[子节点1<br>20,40] A --> C[子节点2<br>100,120] C --> D[叶子节点<br>100→磁盘地址0x123] ``` 仅需3次磁盘I/O即可定位数据（实际通过缓存优化更快）。 #### 2. **查询加速原理** | 操作类型 | 无索引 | 有索引 | 加速本质 | |----------------|-------------------|-------------------|--------------------------| | 等值查询 | $O(n)$ 全表扫描 | $O(\log n)$ 树搜索| 从线性到对数复杂度 | | 范围查询 | $O(n)$ | $O(\log n + k)$ | 叶子节点顺序访问 | | 排序操作 | 外排序$O(n\log n)$| 索引遍历$O(n)$ | 避免临时排序 | #### 3. **与哈希索引的对比** 哈希索引（如Memory引擎）虽能实现$O(1)$查找： ```python hash_map = {"id_100": "0x123"} ``` 但存在致命缺陷： - ❌ 不支持范围查询（`id>100`失效） - ❌ 哈希冲突需额外处理 - ❌ 无序性导致排序开销大 因此**B+树在通用场景更优**。 #### 4. **索引查询的物理过程** 1. 索引根节点常驻内存（Buffer Pool） 2. 二分查找定位子节点路径 3. 叶子节点获取行指针（ROWID） 4. 根据指针回表取数据页 **关键优化**： - 索引覆盖：若所需字段全在索引中，直接返回结果（避免回表） - 最左匹配：复合索引`(a,b,c)`仅支持`a=?`、`a=? AND b=?`等查询 > 索引的本质是通过预排序和分层结构，将数据查找的**随机访问转化为顺序访问**，减少磁盘I/O次数。当树高为$h$时，查询复杂度仅为$O(h)$而非$O(n)$[^1]。
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值