MySQL索引原理：从B+树手绘到EXPLAIN

最近在学后端，学到了这里做个记录

---

一、为什么索引像书的目录？

• 类比：500页的技术书籍 vs 10页的目录
• 缺点：全表扫描就像逐页翻找内容
• 优点：索引将查询速度从O(n)提升到O(log n)

二、B+树手绘课堂

1. 结构解剖（建议手绘图包含以下元素）

          +------------+
          |  非叶子节点 |
          | [10, 20, 30] | ← 键值+指针
          +-----|--|-----+
            ____/  \____
           /            \
+-----------+    +-----------+
| 叶子节点   |    | 叶子节点   |
| [5,8,10] → | ←→ | [20,25,30] | ← 双向链表
| 行数据指针 |    | 行数据指针 |
+-----------+    +-----------+

• 用不同颜色标注：
  • 红色：节点分裂临界点（每个节点最多存3个键值）
  • 蓝色：叶子节点的双向指针
  • 绿色：行数据实际存储位置

2. 动态过程

• 插入过程：

  插入28 → 定位到[20,25,30]节点 → 分裂为[20,25]和[28,30]
  新增父节点指针
  

• 查询路径：

  查找25 → 根节点→中间节点→叶子节点（3次磁盘IO）
  

三、InnoDB索引落地实现

1. 聚簇索引结构

[表空间文件示意图]
+----------------------+
| 页1: 索引节点        |
| 页2: 叶子节点(行记录)|
| 页3: 溢出页(TEXT字段)|
+----------------------+

2. 二级索引特殊机制

-- 创建示例
CREATE TABLE users (
  id INT PRIMARY KEY,
  username VARCHAR(20),
  INDEX idx_name (username)  -- 存储(username,id)
);

-- 回表示例
EXPLAIN SELECT * FROM users WHERE username='Alice';

四、EXPLAIN执行计划实战

1. 关键字段解读表

列名	索引健康度指标	优化提示
type	ALL(全表) → range(范围) → const(常量)	争取至少达到range级别
key_len	使用的索引字节数	utf8mb4字段需×4计算
Extra	Using index(覆盖索引)	出现Using filesort立即警报

2. 典型案例分析

-- 案例1：最左前缀原则
EXPLAIN SELECT * FROM orders 
WHERE status='paid' AND create_time > '2023-01-01';
-- 可能的索引方案：
-- 方案A: INDEX(status, create_time) → type: range
-- 方案B: INDEX(create_time, status) → type: ref

-- 案例2：索引合并
EXPLAIN SELECT * FROM products 
WHERE category=1 OR price < 100;
-- 出现Using union(idx_category,idx_price)说明触发了索引合并

五、性能优化-checklist

1. 设计阶段

   • 整型主键比UUID减少30%存储空间
   • 联合索引字段顺序遵循ARC原则：
     A(等值条件) → R(范围条件) → C(排序字段)

2. 调优阶段

   -- 查看索引使用情况
   SELECT * FROM sys.schema_unused_indexes;
   
   -- 强制索引测试
   SELECT * FROM table1 FORCE INDEX(idx_col1) WHERE...
   

3. 紧急处理

   # 快速定位索引问题
   pt-index-usage /var/lib/mysql/mysql-slow.log
   

六、学习资源

• 推荐工具：
  B+ Tree可视化工具