MySQL 索引原理

作者：洲覆日期：2025/10/28

文章目录

  • 一、索引
    • 1.1 索引分类
    • 1.2 主键选择🌟
  • 二、约束
    • 2.1 外键约束
    • 2.2 约束与索引的区别
  • 三、索引实现原理
    • 3.1 索引存储层级结构
    • 3.2 B+ 树
      • B+ 树层高问题🌟
        • 关于自增 ID
  • 四、索引类型
    • 4.1 聚集索引
    • 4.2 辅助索引

一、索引

在数据库中，索引是提高查询性能的关键机制。它相当于书籍的目录，通过索引可以快速定位到数据在磁盘中的位置，从而减少 I/O 操作。对于 InnoDB 而言，索引不仅影响查询性能，还决定了数据在物理层的存储结构。

1.1 索引分类

① 主键索引（Primary Key Index）
非空唯一索引，一个表只有一个主键索引；且在 InnoDB 中是表的物理存储顺序。一个表只能有一个主键索引，它的 B+ 树叶子节点存储了整行数据。

1PRIMARY KEY(key1, key2)
2

② 唯一索引（Unique Index）：不可以出现相同的值，可以有 NULL 值

1CREATE UNIQUE INDEX idx_user_email ON user(email);
2

③ 普通索引（Normal Index）
普通索引仅用于加速查询，没有唯一性或非空约束。

1CREATE INDEX idx_name ON user(name);
2

④ 组合索引（Composite Index）
组合索引是对多个列建立的索引，用于多个字段组合查询时提升效率。MySQL 遵循“最左前缀匹配原则”，即只会利用从左到右连续的字段部分。

1CREATE INDEX idx_user_name_age ON user(name, age);
2

⑤ 全文索引（FullText Index）
全文索引用于在长文本中进行关键词搜索，而不是简单的字符串匹配。它通常通过 MATCH ... AGAINST 来使用。

  • 对短字符串：使用 LIKE '%关键字%'
  • 对长文本：使用 MATCH(text_column) AGAINST('keyword')

1.2 主键选择🌟

InnoDB 中表是索引组织表，主键索引的叶子节点直接保存整行记录，因此每张表必须有且仅有一个主键。

主键的选择遵循以下规则：

  1. 如果显示设置 PRIMARY KEY，则该设置的 key 为该表的主键
  2. 如果没有显示设置，则选择第一个非空唯一索引作为主键。
  3. 如果没有非空唯一索引，InnoDB 会自动生成一个隐藏的 6 字节 _rowid 作为主键。

设计主键时建议使用递增的整型自增主键，不仅能避免页分裂，还能提高插入性能。

二、约束

约束是保证数据完整性和一致性的规则。
为了实现数据的完整性，对于 InnoDB，提供了以下几种约束：
primary key、unique key、foreign key、default、not null

2.1 外键约束

外键用来关联两个表，来保证参照完整性：

  • MyISAM 存储引擎本身并不支持外键，只起到注释作用
  • InnoDB 完整支持外键，并具备事务性
1-- 父表（被引用表）
2create table parent (
3  id int not null,
4  primary key(id)
5) engine=innodb;
6
7-- 子表（引用表）：parent_id关联父表id
8create table child (
9  id int,
10  parent_id int,
11  foreign key(parent_id) references parent(id) 
12  ON DELETE CASCADE  -- 父表删除时，子表同步删除
13  ON UPDATE CASCADE  -- 父表更新时，子表同步更新
14) engine=innodb;
15
16INSERT INTO parent VALUES (1);
17INSERT INTO parent VALUES (2);
18INSERT INTO child VALUES (10, 1);
19INSERT INTO child VALUES (20, 2);
20DELETE FROM parent WHERE id = 1;
21

外键行为选项：

  • CASCADE：子表做同样的行为
  • SET NULL：更新子表相应字段为 NULL
  • NO ACTION：父类做相应行为报错
  • RESTRICT：同 NO ACTION

2.2 约束与索引的区别

约束是逻辑层面的规则，用于保证数据的正确性；索引是物理层面的结构，用于加速数据访问。两者虽有关联，但本质不同。

创建主键索引或者唯一索引的时候同时创建了相应的约束；但是约束是逻辑上的概念；索引是一个数据结构既包含逻辑的概念也包含物理的存储方式。

维度约束（如主键约束）索引（如主键索引）
本质逻辑概念（仅保证数据完整性）数据结构（含逻辑规则 + 物理存储）
关联创建主键 / 唯一索引时自动生成对应约束索引是约束的物理实现载体

三、索引实现原理

在 InnoDB 存储引擎中，每一个索引都对应一棵 B+ 树。而每棵 B+ 树的叶子节点或非叶子节点都存储在 页（Page） 中。

3.1 索引存储层级结构

InnoDB 的存储由段、区、页组成：

  • 段：按功能分数据段（存表数据）、索引段（存索引）、回滚段（存事务回滚信息）
  • 区：大小为 1 MB（一个区由 64 个连续页构成）。为了保证连续性，InnoDB 一次通常会申请 4~5 个区。
  • 页：
    • InnoDB 磁盘管理最小单位，默认 16KB，可通过innodb_page_size修改；
    • 逻辑页（与磁盘物理页 4KB/8KB 区分）；
    • B + 树的一个节点大小 = 页大小（默认 16KB，两个物理页）。

在这里插入图片描述

3.2 B+ 树

全称：多路平衡搜索树，减少磁盘访问次数
用来组织磁盘数据，以页为单位。

特征：

  • 非叶子节点只存储索引信息
  • 叶子节点存储完整数据
  • 叶子节点之间互相连接，方便范围查询
  • 节点的大小为 16 KB，映射的是连续的磁盘页

在这里插入图片描述

B+ 树层高问题🌟

B+ 树的一个节点对应一个数据页；B+ 树的层越高，要读取到内存的数据页越多，IO 次数越多。

假设：

  • key 为 10 byte 且指针大小 6 byte
  • 假设一行记录的大小为 1KB
  • InnoDB 一个节点 16KB

计算：

层高非叶子节点数量叶子节点数量最大记录数
21（根节点）1024（16KB/16byte）1024 × 16 = 16384
31 + 10241024×10241024×1024×16 = 16777216
41 + 1024 + 1024²1024³1024³×16 = 17179869184

三层树即可支持上千万数据量，因此 B+ 树能高效应对大规模数据存储。

总结

  • 为什么采用“多路”的树结构：一个节点多条链路，相较于平衡二叉搜索树是更矮胖的结构，树的高度较低，能减少磁盘 IO 次数来索引数据。
  • 为什么非叶子节点只存储索引信息：B+ 树节点映射固定大小的磁盘数据，可包含更多索引信息，能快速锁定数据所在叶子节点位置。
  • 为什么叶子节点依次相连：便于范围查询，避免中序遍历回溯查找下一个节点。

总之：索引信息和数据信息的分层管理，便于高效组织磁盘数据、快速实现单点和范围查询。

B+树通过“非叶子节点存索引、叶子节点存数据且依次相连、节点为16KB并映射连续磁盘页”的特征，结合多路结构降低树高以减少磁盘IO、非叶子节点只存索引以快速定位数据、叶子节点相连便于范围查询的设计，实现了索引与数据的分层管理，从而高效组织磁盘数据，快速支持单点查询与范围查询。

关于自增 ID

在 InnoDB 中，主键常用自增 ID（AUTO_INCREMENT）。
型通常为 BIGINT，范围是 ( − 2 63 , 2 63 − 1 ) (-2^{63}, 2^{63}-1)(−263,263−1)。即使每秒插入 1 亿条记录，也需要约 5849 年 才会耗尽。因此在绝大多数业务场景中完全够用。

四、索引类型

在 InnoDB 中，索引分为两种：聚集索引（Clustered Index） 和 辅助索引（Secondary Index）。

4.1 聚集索引

聚集索引是按主键构造的 B + 树，叶子节点直接存表数据页（整行数据），也就是说，索引即数据，查到索引项就相当于拿到了完整记录。

  • 每张表只有一个聚集索引；
  • 数据物理顺序与主键逻辑顺序一致；
  • 范围查询效率极高。
1SELECT * FROM user WHERE id >= 18 AND id < 40;
2

查找流程：
1. 根节点定位 id=18~40 的索引项；
2. 指针指向叶子节点（数据页，如 “页 10、页 12”）；
3. 直接读取叶子节点数据（无需回表）。

在这里插入图片描述

4.2 辅助索引

辅助索引的叶子节点不存放完整记录，只保存：

  • 索引列值；
  • 主键值（主键 id，称为“书签”或 Bookmark）。

查询时，需要先通过辅助索引定位主键，再通过主键去聚集索引中取出完整行数据，过程称为**回表。

1select * from user where lockyNum = 33;
2

表含id（主键）、name、lockyNum（辅助索引），索引定义KEY(lockyNum)；
查找流程：
1. 遍历lockyNum辅助 B + 树，找到 lockyNum=33 的叶子节点；
2. 从 “书签” 获取主键 id；
3. 遍历聚集索引，通过 id 找到完整数据（需回表）。
在这里插入图片描述
辅助索引查询效率略低于主键索引，在只查询索引字段时可避免回表，尽量使用覆盖索引（即查询字段都在索引中），以减少磁盘访问。

《MySQL 索引原理》 是转载文章，点击查看原文。

上一篇：前端别再乱存数据了！这3种存储方案让你的应用快如闪电

下一篇：某银行大厂面试技术问题深度解析(一)

相关推荐

Python 的内置函数 delattr
IMPYLH2025/10/25

Python 内建函数列表 > Python 的内置函数 delattr def delattr(obj, name:str): ''' 删除指定的属性 :param obj: 一个对象 :param name: 要删除的属性的名字 ''' Python 的内置函数 delattr 用于动态删除对象的属性。该函数需要两个参数：第一个参数是目标对象，第二个参数是要删除的属性名称（字符串形式）。 示例 运行 class Person: d

c++算法题目总结
July尘2025/10/23

5分题 001 Hello World（输出语句） #include<stdio.h> int main(){ printf("Hello World!"); return 0; } 004计算摄氏温度（简单计算） #include<stdio.h> int main(){ int F; scanf("%d",&F); int C = 5 * (F-32) / 9; printf("Celsius = %d",C);

【自然资源】自然资源系统业务全梳理，点赞收藏
jr4282025/10/22

自然资源系统业务全梳理 结合为自然部门的业务和多方资料来源，对自然资源业务体系和信息化做了梳理，使测绘地理信息行业在自然资源领域如何落地变得具象化。 自然资源管理业务框架的共性特征 分为基础性业务、核心业务与综合性业务3类。 基础性业务包括调查监测，确权登记，主要是摸清家底，确定权属，统一底图底数，由原来部门分治管理时土地、森林等分头调查确权统一到以三调为基础的自然资源一张底图， 测绘和地理信息业务主要是建立自然资源及国土空间数据基准，进行数据采集，并提供信息化技术支撑。 核心业务主要包括所有权

将 EasySQLite 解决方案文件格式从 .sln 升级为更简洁的 .slnx
追逐时光者2025/10/20

前言 EasySQLite 是一个 .NET 9 操作 SQLite 入门到实战详细教程，主要是对学校班级，学生信息进行管理维护。本文的主要内容是将 EasySQLite 项目解决方案文件格式从 .sln 格式升级为更简洁的 .slnx 格式。 GitHub开源地址：github.com/YSGStudyHar… 选型、开发、部署详细教程 第一天、SQLite 简介 第二天、在 Windows 上配置 SQLite环境 第三天、SQLite快速入门 第四天、EasySQLite前后端项目框

汽车免拆诊断案例 | 2014 款宝马 M4 车冷起动后发动机抖动
虹科Pico汽车示波器2025/10/19

故障现象　 一辆2014款宝马M4车，搭载S55B30A发动机，累计行驶里程约为9.4万km。车主反映，冷起动后发动机抖动严重，且组合仪表上的发动机故障灯异常点亮；行驶一段距离后熄火再重新起动发动机，发动机工作恢复正常。 故障诊断 接车后试车，发动机工作正常。用故障检测仪检测，发动机控制单元（DME）中存储有气缸5和气缸6间歇失火的故障代码（图1）。 图1 DME中存储的故障代码 将车停放一晚，第二天早上试车，同时用虹科Pico汽车示波器测量相关信号波形。由图2可知，冷起动后发

最强流式渲染，没有之一
Simon_He2025/10/18

vue-markdown-renderer 阮一峰推荐，性能比vercel出品streamdown 好 100 倍，性能以及渲染表现一定是市面上所有流式渲染中最好的一档，视频中会去讲解为什么，视频比较干，建议仔细看完到最后，希望对于ai相关的小伙伴有一定的收获，谢谢～ 下面有详细分析市面上的 ai 渲染，以及 vercel 的 streamdown 和如何优化的细节，在面对现阶段 ai 的岗位越来越多，作为前端，掌握 ai 流式渲染，也可以成为你面试的亮点 【最强流式渲染没有之一】 www.bi

开源 ETL（Extract，Transform，Load）工具之Apache Hop
西京刀客2025/10/16

文章目录 开源 ETL（Extract，Transform，Load）工具之Apache HopApache Hop起源与背景什么是Apache Hopapache hop 核心概念Tools（工具）Item types（条目类型）Projects（项目）关键概念对比与应用建议 术语对照下载安装使用在 Hop GUI 中创建 Pipeline（步骤） Apache Hop 的局限Apache Hop 的外部插件仓库（hop-plugins）参考 开源 ETL（Extrac

Subword算法之WordPiece、Unigram与SentencePiece
红纸2812025/10/15

在昨天的内容里，我们已经围绕 Subword 分词体系中最基础的两类算法 ——BPE（字节对编码）与 BBPE（双向字节对编码）展开了详细拆解：从 BPE 的 “训练 - 编码” 核心逻辑切入，用具体案例还原了它如何通过迭代合并高频字节对构建词汇表，也分析了其在控制词汇表规模、处理未登录词上的优势与局限；随后又聚焦 BBPE 与 BPE 的差异，通过预处理、多轮字节对统计与合并的完整实例，清晰呈现了 BBPE 的工作原理，以及它在特定场景下的应用价值。 但 Subword 分词的技术版图远不止

Redis（60） Redis的复制延迟如何优化？
Victor3562025/10/14

在 Redis 的主从复制架构中，复制延迟是一个不可忽视的问题。复制延迟可能会影响数据一致性和系统性能。以下是一些优化 Redis 复制延迟的策略及其实现方法，并结合代码进行详细说明。 复制延迟的原因 网络延迟：主从节点之间的网络延迟是导致复制延迟的主要原因。 主节点性能：主节点的负载过高，处理客户端请求和复制请求的速度减慢。 从节点性能：从节点性能不足，处理来自主节点的复制数据速度慢。 数据量过大：数据量过大，尤其是在初次同步时，会导致复制延迟。 优化策略 1. 优化网络延迟 部署在同一

Jenkins Pipeline post指令详解
00后程序员张2025/10/12

Jenkins Pipeline post指令详解：掌握构建后处理的艺术 引言 在持续集成和持续部署（CI/CD）的实践中，构建过程的稳定性和可靠性至关重要。然而，任何复杂的软件项目都难免会遇到构建失败、测试不通过或环境不稳定的情况。Jenkins Pipeline作为自动化构建的核心工具，提供了强大的post指令来帮助开发者优雅地处理这些各种情况。本文将深入探讨post指令的各个方面，帮助您全面掌握构建后处理的最佳实践。 什么是post指令？ 基本概念 post指令是Jenkins Pipe

上一篇：前端别再乱存数据了！这3种存储方案让你的应用快如闪电

下一篇：某银行大厂面试技术问题深度解析(一)

首页编辑器站点地图

Copyright © 2025 聚合阅读

License: CC BY-SA 4.0