🔍 MySQL索引原理详解

从数据结构到工作流程，完整图解索引机制

📖 什么是索引？

索引（Index）是数据库表中一列或多列值的集合，以及指向数据行物理位置的指针。 本质上，索引就像书的目录——不需要翻完整本书，就能快速定位到目标内容。

无索引 vs 有索引 对比

❌ 无索引：全表扫描 id name age ... 1 张三 25 ... 2 李四 30 ... 3 王五 28 ... 逐行扫描🔍🔍🔍 100万行 = 100万次IO ✅ 有索引：B+树查找 B+树索引 name → id 数据表 id → 完整行 根 只查3层 → 3次IO
核心结论：索引的本质是用额外的存储空间换取查询速度。 索引文件比数据文件小很多，但通过有序数据结构实现快速定位。

📊 MySQL索引类型

🔑

主键索引 (Primary Key)

表中每行数据的唯一标识，不允许NULL值

▸ 每个表只能有一个主键索引

▸ InnoDB会自动创建聚集索引

✓

唯一索引 (Unique)

索引列的值必须唯一，但允许NULL值

▸ 可有多个唯一索引

▸ 常用于业务唯一约束

📋

普通索引 (Index)

最基本的索引类型，没有唯一性限制

▸ 可有多个，无限制

▸ 纯粹加速查询

📝

全文索引 (FullText)

用于文本内容的关键字搜索

▸ CHAR、VARCHAR、TEXT

▸ 支持中文需用分词器

🎯

前缀索引 (Prefix)

索引字符串列的前N个字符

▸ 节省索引空间

▸ 不支持覆盖索引

🔗

复合索引 (Composite)

多个列组合而成的索引

▸ 遵循最左前缀原则

▸ 覆盖更复杂查询场景

🌳 B+树数据结构

InnoDB存储引擎使用B+树作为索引的数据结构。为什么是B+树？

B+树 vs 其他数据结构

B+树索引 范围查询、排序、主键/外键 ✅ InnoDB默认 Hash索引 等值查询 =、IN() ⚠️ Memory引擎 R树索引 空间数据、地理位置 ⚡ MyISAM支持

B+树的特征

17 根节点 8 12 内部节点 25 33 内部节点 🔗 指针 id:1 id:3 id:7 → → → 🔗 指针 id:8 id:10 id:11 → → → 🔗 指针 id:12 id:15 id:19 → → → 🔗 指针 id:25 id:28 id:31 → → → 🔗 指针 id:33 id:35 id:40 → → → 🔗 指针 id:42 id:50 ... → → → 图例： 内部节点 叶子节点 双向链表
内部节点：只存索引键和指针
叶子节点：存储完整数据/行指针
叶子节点链表：支持范围查询

B+树查询演示

🔍 搜索进度

点击按钮开始搜索

📖 B+树核心特性

  • 多叉平衡树：所有叶子节点在同一层，树高通常为3-4层
  • 范围查询快：叶子节点通过双向链表连接，可顺序遍历
  • 磁盘友好：每个节点默认16KB，默认3层可存2000万+数据
  • 查询稳定：无论查什么数据，IO次数相同（树高次）

🏠 聚集索引 vs 非聚集索引

🔑 聚集索引 (Clustered Index)

InnoDB的主键索引。叶子节点存储完整的行数据。

InnoDB 表结构 主键(id) 1 2 3 name 张三 李四 王五 age 25 30 28

✓ 主键查询直接返回完整数据

📋 非聚集索引 (Secondary Index)

叶子节点存储主键值，需要回表查询完整数据。

name索引 (非聚集) 张三 → id=1 李四 → id=2 王五 → id=3 回表 ↓ ↑ 获取数据 主键索引(聚集) (主键→完整数据) id=1 → (张三,25,...) id=2 → (李四,30,...) id=3 → (王五,28,...)

⚡ 非主键查询需2次索引查找

对比项 聚集索引 非聚集索引
存储位置 叶子节点存完整行数据 叶子节点存主键值
数量限制 每个表只能有1个 每个表可有多个
查询效率 主键查询直接返回数据 需回表，多一次IO
适用场景 主键、范围查询 普通列查询、联合查询
数据物理顺序 按主键顺序物理存储 独立于数据表存储

📐 最左前缀原则

复合索引 (a, b, c) 相当于创建了三个索引：(a)、(a,b)、(a,b,c)。 查询时必须包含最左边的列，才能使用索引。
INDEX idx_name_age_dept (name, age, dept) 复合索引 = 三棵索引树 索引树①: name ✅ 生效 索引树②: name+age ✅ 生效 索引树③: name+age+dept ✅ 生效 索引树④: age ❌ 不生效 SQL查询 WHERE name = '张三' → 使用索引树① WHERE name = '张三' AND age = 25 → 使用索引树② WHERE name = '张三' AND age = 25 AND dept = 'IT' → 使用索引树③ WHERE age = 25 → ❌ 不使用索引（跳过了name）

最佳实践

1
考虑查询频率
高频列放左边
→
2
遵循最左前缀
不跳过前列
→
3
避免范围中断
between破坏索引
→
4
控制索引长度
节省存储空间

🔄 索引查询完整流程

1️⃣ SQL解析 识别 WHERE 条件 提取过滤条件 2️⃣ 优化器决策 选择最优索引 计算执行代价 3️⃣ 索引树查找 从根到叶子 定位目标记录 4️⃣ 回表/返回 聚集索引获取 返回查询结果 📊 IO次数分析（B+树高=3） 主键查询： SELECT * FROM users WHERE id = 100 根节点 →1次IO 内部节点 →1次IO 叶子节点 →1次IO = 3次IO ✓ 普通列查询： SELECT * FROM users WHERE name = '张三' 根节点 →1次IO 内部节点 →1次IO 叶子节点 →1次IO →回表1次 聚集索引 →1次IO = 4次IO

⚡ 覆盖索引优化

当索引包含查询所需的所有列时，无需回表，直接在索引中获取数据。 这称为索引覆盖（Index Covering）。
❌ 普通查询（需回表） name索引 name → id 回表 聚集索引 id → 完整行 SELECT * FROM users WHERE name = '张三' 需要所有列 → 必须回表 ✅ 覆盖索引（无需回表） idx_name_age name → id → age ✓ 包含所有查询列 直接返回！ SELECT name, age FROM users WHERE name = '张三' 只查索引 → 不回表
优化建议：将高频查询的列加入索引，利用覆盖索引避免回表，可显著提升查询性能。

⚠️ 索引失效的常见场景

❌ 函数/运算

WHERE YEAR(created_at) = 2024

→ WHERE created_at >= '2024-01-01'

❌ 类型转换

WHERE phone = 13800138 (字符串存数字)

→ WHERE phone = '13800138'

❌ LIKE %开头

WHERE name LIKE '%张三'

→ 可以用前缀索引或ES

❌ OR不匹配

WHERE name = 'A' OR age = 20

→ age列无索引导致全表

❌ 范围中断

WHERE a = 1 AND b > 5 AND c = 3

→ c无法使用索引

❌ IS NOT NULL

WHERE name IS NOT NULL

→ 通常全表扫描更快

📋 知识点总结

维度 说明
索引本质 额外存储空间换取查询速度的有序数据结构
核心结构 InnoDB使用B+树，多叉平衡，磁盘友好
聚集索引 主键索引，叶子节点存完整数据，每表一个
非聚集索引 普通索引，叶子节点存主键，需回表
最左前缀 复合索引从左开始使用，不能跳过
覆盖索引 查询列全在索引中，避免回表，性能最优
索引代价 占用磁盘空间，降低写入性能（维护成本）