欢迎光临
我们一直在努力

【MYSQL】MYSQL学习的一大重点:索引(下)- B+树

头像

🎬 个人主页:艾莉丝努力练剑

❄专栏传送门:《C语言》《数据结构与算法》《C/C++干货分享&学习过程记录》 《Linux操作系统编程详解》《笔试/面试常见算法:从基础到进阶》《Python干货分享》

⭐️为天地立心,为生民立命,为往圣继绝学,为万世开太平


🎬 艾莉丝的简介:

在这里插入图片描述


文章目录

  • MySQL 索引思维导图
  • 1 ~> 索引底层核心原理
    • 1.1 磁盘 IO 与 Page 机制
      • 1.1.1 核心概念
    • 1.2 单页与多页查找优化
      • 1.2.1 单页内部优化:页目录
      • 1.2.2 多页场景的原生瓶颈
    • 1.3 B + 树索引结构
      • 1.3.1 结构定义
      • 1.3.2 核心优势
    • 1.4 候选数据结构对比
      • 1.4.1 线性结构与二叉树族
      • 1.4.2 Hash 索引
      • 1.4.4 B 树 vs B + 树
  • 2 ~> 聚簇索引与非聚簇索引
    • 2.1 核心定义
    • 2.2 InnoDB 聚簇索引
      • 2.2.1 主键索引(聚簇索引)
      • 2.2.2 普通(辅助)索引
      • 2.2.3 回表查询
    • 2.3 MyISAM 非聚簇索引
      • 2.3.1 结构特征
      • 2.3.2 文件形式
    • 2.4 引擎索引文件对比
  • 3 ~> 索引分类与 SQL 操作
    • 3.1 主键索引
      • 3.1.1 核心特点
      • 3.1.2 创建语法
      • 3.1.3 查看索引
      • 3.1.4 删除语法
    • 3.2 唯一键索引
      • 3.2.1 核心特点
      • 3.2.2 创建语法
      • 3.2.3 删除语法
    • 3.3 普通索引
      • 3.3.1 核心特点
      • 3.3.2 创建语法
      • 3.3.3 删除语法
    • 3.4 复合索引
      • 3.4.1 定义与创建
      • 3.4.2 最左匹配原则
      • 3.4.3 索引覆盖
      • 3.4.4 设计原则
  • 4 ~> 索引设计原则
    • 4.1 推荐创建索引的场景
    • 4.2 不推荐创建索引的场景
    • 4.3 补充:常见索引失效场景(面试高频)
  • 5 ~> 全文索引(选学)
    • 5.1 基础概念
      • 5.1.1 **全文索引支持情况**
      • 5.1.2 作用
      • 5.1.3 创建语法
    • 5.2 使用与验证
      • 5.2.1 查询语法
      • 5.2.2 执行计划验证
  • 6 ~> 性能复盘与优化体系
    • 6.1 索引的价值与代价
    • 6.2 数据库性能优化体系
  • 结尾

在这里插入图片描述


MySQL 索引思维导图

MySQL索引体系
├─ 一、索引底层核心原理
│ ├─ 1. 磁盘IO与Page机制
│ ├─ 2. 单页与多页查找优化
│ │ ├─ 页目录(单页内优化)
│ │ └─ 多页链表的性能瓶颈
│ ├─ 3. B+树索引结构
│ │ ├─ 结构特征
│ │ └─ 核心优势
│ └─ 4. 候选数据结构对比
│ ├─ 线性结构 / 二叉搜索树 / 平衡二叉树
│ ├─ Hash索引
│ └─ B树 vs B+树
├─ 二、聚簇索引与非聚簇索引
│ ├─ 1. InnoDB 聚簇索引
│ │ ├─ 主键索引(聚簇索引本体)
│ │ ├─ 普通辅助索引
│ │ └─ 回表查询
│ ├─ 2. MyISAM 非聚簇索引
│ │ ├─ 主键索引
│ │ └─ 普通索引
│ └─ 3. 文件存储形式对比
├─ 三、索引分类与SQL操作
│ ├─ 1. 主键索引(创建/查看/删除)
│ ├─ 2. 唯一键索引(创建/查看/删除)
│ ├─ 3. 普通索引(创建/查看/删除)
│ └─ 4. 复合索引
│ ├─ 最左匹配原则
│ └─ 索引覆盖
├─ 四、索引设计原则
│ ├─ 推荐建索引的场景
│ └─ 不推荐建索引的场景
├─ 五、全文索引(选学)
│ ├─ 基础概念与引擎支持
│ └─ 查询语法与验证
└─ 六、性能复盘与优化体系
├─ 索引的收益与代价
└─ 高阶数据库优化方向


1 ~> 索引底层核心原理

1.1 磁盘 IO 与 Page 机制

1.1.1 核心概念

MySQL(InnoDB 引擎)与磁盘进行 IO 交互的最小单位是Page(页),默认大小为 16KB,而非按单条记录交互。

  • 设计依据:局部性原理—— 单次加载整页数据,后续访问同页内的相邻数据无需再次发起磁盘 IO,大幅减少 IO 次数。
  • 性能本质:磁盘 IO 的核心瓶颈是IO 次数,而非单次 IO 传输的数据量。

1.2 单页与多页查找优化

1.2.1 单页内部优化:页目录

  • 页内数据按主键有序排列,通过双向链表组织;数据有序是引入页目录的前提。
  • 页目录是典型的空间换时间方案:将页内数据划分为多个分组,目录项存储每组的起始主键值。
  • 查找流程:先通过目录定位分组,再在分组内线性遍历,避免全页扫描。

1.2.2 多页场景的原生瓶颈

  • 多个 Page 通过page_prev/page_next指针构成双向链表。
  • 无索引时,多页查找为线性遍历,需依次加载 Page 到内存,数据量越大,IO 次数越多,性能越低。
  • 单页的页目录优化无法解决页间遍历的 IO 瓶颈。

1.3 B + 树索引结构

1.3.1 结构定义

B + 树是一种多叉平衡查找树,是 InnoDB 索引的底层实现,构建逻辑为对 Page 层级也建立目录:

  • 非叶子节点(目录节点):仅存储键值(索引列的值)与下层页的指针,不存储完整行数据。
  • 叶子节点:存储完整的索引数据(聚簇索引存整行数据,普通索引存主键 / 地址),所有叶子节点通过双向链表级联。
  • 形态特征:树型呈 “矮胖型”。单页可存储上千个目录项,通常 2~3 层即可管理千万级数据,树高直接决定 IO 次数。
  • 1.3.2 核心优势

    • 非叶子节点不存数据 → 单页容纳更多目录项 → 树高更低 → 磁盘 IO 次数更少。
    • 叶子节点双向链表串联 → 天然支持高效范围查询,无需重复回溯遍历树结构。

    1.4 候选数据结构对比

    1.4.1 线性结构与二叉树族

    数据结构不适用原因
    普通链表 纯线性遍历,时间复杂度 O (n),海量数据下 IO 次数不可接受
    二叉搜索树 存在退化问题,极端情况退化为链表,性能骤降
    AVL 树 / 红黑树 虽为平衡 / 近似平衡结构,但本质是二叉树,树高过高;千万级数据树高约 20 + 层,对应 20 + 次 IO,远高于 B + 树

    1.4.2 Hash 索引

    • 优势:等值查询时间复杂度接近 O (1)。
    • 劣势:不支持范围查询、排序、前缀模糊匹配;存在哈希冲突开销。
    • 引擎支持:MEMORY 引擎支持显式 HASH 索引;InnoDB 不支持用户手动创建,仅内部实现自适应哈希索引(由引擎自动根据热点数据构建,用户无法干预)。

    1.4.4 B 树 vs B + 树

    对比维度B 树B + 树
    数据存储 所有节点都存储键值 + 完整行数据 仅叶子节点存储完整数据,非叶子节点仅存键值 + 页指针
    叶子节点连接 叶子节点之间无链表串联 所有叶子节点通过双向链表级联
    树高特征 单节点目录项少,树更高,IO 次数多 单节点目录项多,树更矮,IO 次数少
    范围查询 需多次回溯遍历树,效率低 遍历叶子链表即可,天然支持范围查询

    2 ~> 聚簇索引与非聚簇索引

    2.1 核心定义

    • 聚簇索引:索引结构与数据存储在一起,叶子节点保存完整行数据,索引即数据。
    • 非聚簇索引:索引结构与数据文件分离,叶子节点保存数据行的物理地址。

    2.2 InnoDB 聚簇索引

    2.2.1 主键索引(聚簇索引)

    • 一张表有且仅有一个聚簇索引,默认以主键构建。
    • 无显式主键时,InnoDB 会选择第一个唯一非空索引作为聚簇索引键;若均无,则生成隐藏的 6 字节ROWID作为聚簇索引键。
    • 叶子节点存储完整行数据,主键查询无需回表,性能最高。
    • 文件形式:表数据 + 索引统一存储在.ibd文件中。

    2.2.2 普通(辅助)索引

    • 基于非主键列构建的 B + 树,一张表可存在多个普通索引,对应多棵 B + 树。
    • 叶子节点仅存储索引列的值 + 对应行的主键值,不存储完整行数据。

    2.2.3 回表查询

    通过普通索引定位到主键后,再通过主键索引查询完整行数据的过程,称为回表。本质是两次 B + 树查找,性能低于主键直接查询。

    2.3 MyISAM 非聚簇索引

    2.3.1 结构特征

    • 主键索引与普通索引结构无本质差异,叶子节点均存储数据行的物理地址。
    • 主键索引仅约束键值唯一,普通索引允许键值重复。
    • 不存在回表概念:查到索引后直接通过物理地址读取数据,仅一次定位。

    2.3.2 文件形式

    • .frm:表结构文件(MySQL 5.x,8.0 已废除)
    • .MYD:数据文件(My Data)
    • .MYI:索引文件(My Index)

    2.4 引擎索引文件对比

    引擎索引类型数据与索引关系文件组成
    InnoDB 聚簇索引 索引与数据合一 .ibd(数据 + 索引)
    MyISAM 非聚簇索引 索引与数据分离 .MYD(数据) + .MYI(索引)

    3 ~> 索引分类与 SQL 操作

    3.1 主键索引

    3.1.1 核心特点

    • 单表最多一个,支持复合主键。
    • 列值非空、不重复,查询效率最高。
    • InnoDB 下主键索引即聚簇索引。

    3.1.2 创建语法

    — 方式1:建表时在字段后指定
    CREATE TABLE user1(id INT PRIMARY KEY, name VARCHAR(30));

    — 方式2:建表末尾统一指定
    CREATE TABLE user2(id INT, name VARCHAR(30), PRIMARY KEY(id));

    — 方式3:建表后追加
    CREATE TABLE user3(id INT, name VARCHAR(30));
    ALTER TABLE user3 ADD PRIMARY KEY(id);

    3.1.3 查看索引

    — 方式1(最详细,推荐)
    SHOW INDEX FROM 表名;

    — 方式2
    SHOW KEYS FROM 表名;

    — 方式3(简略查看)
    DESC 表名;

    字段说明:Key_name为索引名,主键索引默认名为PRIMARY;Index_type: BTREE表示 B + 树索引(MySQL 统一标识为 BTREE,InnoDB/MyISAM 底层为 B + 树实现)。

    3.1.4 删除语法

    ALTER TABLE 表名 DROP PRIMARY KEY;

    3.2 唯一键索引

    3.2.1 核心特点

    • 单表可创建多个。
    • 索引列值不可重复,允许存在 NULL(可多个 NULL)。
    • 若列同时指定NOT NULL,则约束等价于主键索引。

    3.2.2 创建语法

    — 方式1:字段后指定
    CREATE TABLE user4(id INT PRIMARY KEY, name VARCHAR(30) UNIQUE);

    — 方式2:建表末尾指定
    CREATE TABLE user5(id INT PRIMARY KEY, name VARCHAR(30), UNIQUE(name));

    — 方式3:建表后追加
    CREATE TABLE user6(id INT PRIMARY KEY, name VARCHAR(30));
    ALTER TABLE user6 ADD UNIQUE(name);

    3.2.3 删除语法

    — 统一使用 DROP INDEX,不能用 DROP UNIQUE
    ALTER TABLE 表名 DROP INDEX 索引名;

    — 等价写法
    DROP INDEX 索引名 ON 表名;

    3.3 普通索引

    3.3.1 核心特点

    • 单表可创建多个,是业务开发中最常用的索引类型。
    • 允许列值重复、为 NULL,仅用于提升查询效率。

    3.3.2 创建语法

    — 方式1:建表末尾指定
    CREATE TABLE user8(
    id INT PRIMARY KEY,
    name VARCHAR(20),
    email VARCHAR(30),
    INDEX(name)
    );

    — 方式2:建表后追加
    CREATE TABLE user9(id INT PRIMARY KEY, name VARCHAR(20), email VARCHAR(30));
    ALTER TABLE user9 ADD INDEX(name);

    — 方式3:自定义索引名称
    CREATE TABLE user10(id INT PRIMARY KEY, name VARCHAR(20), email VARCHAR(30));
    CREATE INDEX idx_name ON user10(name);

    3.3.3 删除语法

    ALTER TABLE 表名 DROP INDEX 索引名;
    — 或
    DROP INDEX 索引名 ON 表名;

    3.4 复合索引

    3.4.1 定义与创建

    在多个列上共同构建的索引,也叫联合索引。索引键由多列组合而成,B + 树按最左列优先排序。

    — 创建复合索引(默认以首列名为索引名)
    ALTER TABLE test1 ADD INDEX(name, email);

    — 自定义索引名
    CREATE INDEX idx_name_email ON test1(name, email);

    3.4.2 最左匹配原则

    查询条件必须从索引的最左前列开始匹配,不可跳过前列。

    • 可命中:WHERE name='xxx'、WHERE name='xxx' AND email='yyy'
    • 不可命中:WHERE email='yyy'

    3.4.3 索引覆盖

    若查询返回的字段全部包含在复合索引中,无需回表即可直接返回结果,称为索引覆盖(覆盖索引),可大幅提升查询性能。

    3.4.4 设计原则

    优先使用列数少的窄索引,避免过多列的宽索引,减少索引空间占用与写入维护成本。


    4 ~> 索引设计原则

    4.1 推荐创建索引的场景

    • 频繁作为WHERE查询条件的字段。
    • 区分度(唯一性)高的字段,如用户 ID、手机号、订单号。
    • 读取频率高、更新频率低的字段。

    4.2 不推荐创建索引的场景

    • 区分度极低的字段,如性别、状态枚举,索引收益远低于维护成本。
    • 更新非常频繁的字段,索引维护开销会抵消查询收益。
    • 不会出现在WHERE、JOIN、ORDER BY子句中的字段。
    • 数据量过小的表,全表扫描成本低于索引查找。

    4.3 补充:常见索引失效场景(面试高频)

    • 索引列参与运算、函数操作。
    • 发生隐式类型转换。
    • 模糊查询使用前缀通配符%xxx。
    • 违反复合索引最左匹配原则。
    • 使用OR连接非索引列。
    • 数据量过小,优化器选择全表扫描。

    5 ~> 全文索引(选学)

    5.1 基础概念

    5.1.1 全文索引支持情况

    MySQL 5.6 及以上版本,InnoDB 引擎也支持全文索引;原生全文索引对中文支持不佳,中文全文检索通常借助 Sphinx/Coreseek 或 Elasticsearch 等中间件实现。

    5.1.2 作用

    解决大文本内容的检索问题,替代低效的LIKE '%xxx%'全表扫描。

    5.1.3 创建语法

    CREATE TABLE articles (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    title VARCHAR(200),
    body TEXT,
    FULLTEXT (title, body)
    ) ENGINE=MyISAM;
    — MySQL 5.6+ 可改为 ENGINE=InnoDB

    5.2 使用与验证

    5.2.1 查询语法

    — 使用全文索引检索
    SELECT * FROM articles WHERE MATCH(title, body) AGAINST ('database');

    5.2.2 执行计划验证

    — 命中全文索引:type 为 fulltext
    EXPLAIN SELECT * FROM articles WHERE MATCH(title, body) AGAINST ('database');

    — 未命中索引(全表扫描):type 为 ALL
    EXPLAIN SELECT * FROM articles WHERE body LIKE '%database%';


    6 ~> 性能复盘与优化体系

    6.1 索引的价值与代价

    • 价值:典型的空间换时间方案,在海量数据下可将查询性能从秒级提升至毫秒级。
    • 代价:增加磁盘存储空间;降低 INSERT、UPDATE、DELETE 性能(需同步维护索引树结构)。

    6.2 数据库性能优化体系

    索引是最基础的优化手段,高阶优化方案还包括:

    • 分库分表
    • 读写分离
    • 引入缓存中间件
    • SQL 语句优化
    • 数据库参数调优

    结尾

    uu们,本文的内容到这里就全部结束了,艾莉丝在这里再次感谢您的阅读!

    艾莉丝努力练剑

    C/C++ & Linux 底层探索者 | 一个正在努力练剑的技术博主


    👀
    【关注】 跟随我一起深耕技术领域,见证每一次成长。

    ❤️
    【点赞】 让优质内容被更多人看见,让知识传递更有力量。

    ⭐
    【收藏】 把核心知识点存好,在需要时随时查、随时用。

    💬
    【评论】 分享你的经验或疑问,评论区一起交流避坑!

    不要忘记给博主“一键四连”哦!

    “今日练剑达成!”

    “技术之路难免有困惑,但同行的人会让前进更有方向。”

    结语:希望对学习Linux相关内容的uu有所帮助,不要忘记给博主“一键四连”哦!

    往期回顾:

    【MYSQL】MYSQL学习的一大重点:索引(上)- 从磁盘存储到缓冲池机制

    🗡博主在这里放了一只小狗,大家看完了摸摸小狗放松一下吧!🗡

    ૮₍ ˶ ˊ ᴥ ˋ˶₎ა

    在这里插入图片描述

    赞(0)
    未经允许不得转载:171主机测评 » 【MYSQL】MYSQL学习的一大重点:索引(下)- B+树
    分享到: 更多 (0)

    评论 抢沙发

    • 昵称 (必填)
    • 邮箱 (必填)
    • 网址