MySQL索引的物理层存储是怎样的？-CSDN博客

本文链接：https://blog.csdn.net/qq_36777143/article/details/148429782

MySQL索引的物理层存储：像图书馆的书架一样排列数据

一、索引是什么？（图书馆的“书位地图”）

想象图书馆有10万本书，如果没有索引：

找《哈利波特》需要逐本翻看（全表扫描），可能要1小时！
有了索引（如按书名首字母排序），直接去H区找，1分钟搞定！

MySQL索引的本质：
是一种特殊的数据结构（类似B+树），存储索引键（如书名）和数据行地址（如书架位置），让查询像查字典一样快。

二、索引的物理存储结构：像多层书架的“B+树”

1. B+树结构（MySQL最常用的索引结构）

        [根节点：中间字母]
        /               \
    [H-K节点]         [L-O节点]
    /    |    \       /    |    \
[Ha-He] [Hf-Hn]... [La-Le] [Lf-Ln]...
  |       |           |       |
指向数据行 指向数据行   指向数据行 指向数据行

叶子节点：存储完整的索引键值和数据行地址（如磁盘块号）。
非叶子节点：存储“索引键的中间值”，用于快速定位叶子节点（类似字典的部首目录）。

2. 聚集索引 vs 非聚集索引

类型	聚集索引（主键索引）	非聚集索引（普通索引）
存储内容	索引键 + 完整数据行	索引键 + 主键值（或数据行地址）
物理结构	数据行直接按索引顺序存储（像字典）	独立于数据表的索引结构（像附录）
查找流程	直接定位数据（一步到位）	先查索引，再通过主键查数据（两步）

-- 创建示例表（含聚集索引和非聚集索引）
CREATE TABLE books (
    id INT PRIMARY KEY,            -- 聚集索引（主键）
    title VARCHAR(100),
    author VARCHAR(50),
    price DECIMAL(10,2),
    INDEX idx_title (title)        -- 非聚集索引（普通索引）
);

三、索引的物理存储文件（硬盘上的“地图册”）

在InnoDB存储引擎中：

数据表文件（.ibd）：存储聚集索引和数据行，按B+树结构组织。
索引文件：每个非聚集索引单独存储为B+树，叶子节点存储主键值。

# Linux系统下查看MySQL数据文件
ls -la /var/lib/mysql/mydatabase/books.ibd
# 这个文件包含：
# - 聚集索引（主键+完整数据）
# - 所有非聚集索引（索引键+主键值）

四、索引如何加速查询？（查字典的过程）

-- 查询书名以"哈利波特"开头的书
SELECT * FROM books WHERE title LIKE '哈利波特%';

执行流程：

通过索引定位：
- 在idx_title索引的B+树中，快速找到“哈利波特”开头的叶子节点（类似查字典部首）。
- 叶子节点存储主键值（如id=1,2,3）。
回表查询：
- 通过主键值（id）在聚集索引中找到完整数据行（类似根据字典页码翻到对应页）。

五、使用场景：何时该用索引？（像给不同书做不同标签）

场景	示例	索引选择建议
高频WHERE查询	`WHERE age > 18`	为`age`字段创建索引
JOIN关联条件	`FROM users JOIN orders ON...`	为关联字段（如`user_id`）加索引
排序（ORDER BY）	`ORDER BY create_time DESC`	为`create_time`创建索引
唯一约束（UNIQUE）	`CREATE UNIQUE INDEX...`	确保字段值唯一（如邮箱）

六、底层原理：B+树如何让查询变快？（为什么不用数组？）

二叉树 vs B+树：
- 二叉树（每个节点2个子节点）：查询深度大（如100万数据需20层）。
- B+树（每个节点100-1000个子节点）：查询深度小（3-4层即可存100万数据）。
磁盘IO优化：
- B+树每个节点大小为1个磁盘页（通常16KB），每次IO读取整个节点。
- 非叶子节点不存储数据，只存索引键，可容纳更多索引项，减少IO次数。

七、思维导图：索引的物理层世界

MySQL索引物理存储
├── 核心结构
│   ├── B+树（多层书架）
│   │   ├── 叶子节点（存索引键+数据地址）
│   │   └── 非叶子节点（存中间值，快速定位）
│   ├── 聚集索引（主键+完整数据）
│   └── 非聚集索引（索引键+主键值）
├── 存储文件
│   ├── .ibd文件（数据表+聚集索引）
│   └── 独立索引文件（非聚集索引）
├── 工作原理
│   ├── 快速定位（类似查字典）
│   ├── 回表查询（通过主键找数据）
│   └── 磁盘IO优化（节点大小=磁盘页）
└── 使用场景
    ├── WHERE条件字段
    ├── JOIN关联字段
    ├── ORDER BY排序字段
    └── UNIQUE唯一约束

八、流程图：索引查询的“快递路线”

用户查询 → 检查WHERE条件 → 
    ┌─ 有索引 → 通过索引B+树找到主键值 → 通过主键查聚集索引 → 返回数据
    └─ 无索引 → 全表扫描（逐行检查）

九、概念图：聚集索引与非聚集索引的协作

┌─────────────────────────────────────────────┐
│                数据表（books）                 │
│  ┌───────────────────┐  ┌─────────────┐      │
│  │  聚集索引（主键）   │  │  数据行     │      │
│  │  （按id排序）      │  │  （按id存储）│      │
│  └───────────────────┘  └─────────────┘      │
└───────────────────────────┬─────────────────┘
                            │ 数据行地址
                            ▼
┌─────────────────────────────────────────────┐
│               非聚集索引（idx_title）           │
│  ┌───────────────────┐  ┌─────────────┐      │
│  │  B+树结构         │  │  叶子节点    │      │
│  │  （按title排序）   │  │  （title→id）│      │
│  └───────────────────┘  └─────────────┘      │
└───────────────────────────┬─────────────────┘
                            │ 主键值
                            ▼
┌─────────────────────────────────────────────┐
│               聚集索引（主键id）               │
│  ┌───────────────────┐  ┌─────────────┐      │
│  │  B+树结构         │  │  叶子节点    │      │
│  │  （按id排序）      │  │  （id→完整数据）│
│  └───────────────────┘  └─────────────┘      │
└─────────────────────────────────────────────┘