后端►MySQL

MySQL 索引与 SQL 优化全梳理：从 InnoDB 存储结构到执行计划

一、MySQL 存储引擎与体系结构

1. MySQL 体系结构概览

MySQL 整体可以分为四层：

  • 连接层：负责连接处理、权限认证和安全校验
  • 服务层：SQL 解析、优化、缓存、执行等核心逻辑
  • 引擎层：真正负责数据的存储和读取（通过统一 API）
  • 存储层：将数据、索引、日志等持久化到文件系统

MySQL 的核心优势在于 存储引擎插件化：

查询处理与数据存储解耦，可以根据业务场景选择最合适的引擎。

2. InnoDB 存储引擎特性

InnoDB 是 MySQL 5.5 之后的默认存储引擎，兼顾高可靠性与高性能，主要特性包括：

  • 支持事务（ACID）
  • 行级锁，支持高并发
  • 支持外键约束

每张 InnoDB 表都对应一个 .ibd 文件，用于存储 表结构 + 数据 + 索引。

3. InnoDB 逻辑存储结构

从大到小依次为：

  • 表空间（Tablespace）：.ibd 文件本身
  • 段（Segment）：数据段、索引段、回滚段
  • 区（Extent）：1MB，一个区 = 64 个页
  • 页（Page）：16KB，InnoDB 磁盘 I/O 的最小单位
  • 行（Row）：真实数据记录，包含隐藏字段（事务 ID、回滚指针）

InnoDB 是按页而不是按行进行磁盘读写的。

二、索引原理与结构

1. 索引是什么

索引是一种 有序的数据结构，用于加速数据检索。

无索引时：

查询只能全表扫描，从第一行扫到最后一行，性能极低。

索引的优缺点：

优点

  • 降低 I/O 成本
  • 减少排序开销（索引中的值是有序的），降低 CPU 消耗

缺点

  • 占用额外空间
  • 增删改时需要维护索引结构

2. 为什么 InnoDB 使用 B+Tree

B+Tree 相比其他结构的优势：

  • 相比二叉树：层级更低
  • 相比 B-Tree：
    • 非叶子节点不存数据，单页可存更多索引
    • 树高度更低
  • 相比 Hash：
    • 支持范围查询
    • 支持排序

InnoDB 在 B+Tree 基础上增加了 双向链表，极大提升了区间查询效率。

三、聚集索引与回表查询

1. 聚集索引 & 二级索引

  • 聚集索引：叶子节点存的是整行数据（主键）
  • 二级索引：叶子节点存的是主键值

通过二级索引查到主键，再回到聚集索引查整行数据，这个过程称为 回表查询。

2. B+Tree 高度估算（理解索引为什么快）

假设：

  • 一行数据 ≈ 1KB
  • 一页 16KB ⇒ 16 行
  • 主键 bigint：8 字节
  • 指针：6 字节

高度为 2 时：

1
2
n * 8 + (n + 1) * 6 = 16 * 1024 // n 代表一节点中存储值的个数
n ≈ 1170

可存储记录数：

1
1170 * 16 ≈ 1.8 万

高度为 3：

1
1171 * 1171 * 16 ≈ 2200 万

现实中索引高度通常 ≤ 3。

四、索引使用与失效场景

1. 最左前缀法则

联合索引 (a, b, c)：

  • ✅ a
  • ✅ a, b
  • ❌ b, c

查询条件顺序无关，优化器会自动调整。

2. 范围查询导致右侧索引失效

1
where a = 1 and b >= 10 and c = 5
  • a、b 可用
  • c 只能过滤，不能继续缩小扫描区间

key_len 不能判断是否完全走索引，必须结合 type = ref / range。

3. 常见索引失效原因

  • 左 / 左右模糊匹配：%xx
  • 对索引列使用函数或表达式
  • 隐式类型转换
  • OR 条件中有非索引列
  • 数据分布导致优化器放弃索引

五、SQL 优化总结

1. 插入优化

  • 批量插入(本质上是减少事务的数量)
  • 手动控制事务
  • 大数据量使用 LOAD DATA

2. 主键优化

  • 顺序插入 > 乱序插入
  • 避免 UUID
  • 减少页分裂 / 页合并（通过顺序插入）

3. 排序、分组、分页优化

  • ORDER BY 优先 using index
  • 大分页使用子查询 + 覆盖索引
  • GROUP BY 遵循最左前缀

4. COUNT 优化

1
2
效率排序：
count(字段) < count(主键) < count(1) = count(*)

六、总结

今天主要系统学习了 InnoDB 存储结构、索引原理以及 SQL 优化方法，核心结论如下：

  1. InnoDB 是面向事务与高并发的存储引擎
  2. B+Tree 是最适合磁盘索引的数据结构
  3. SQL 优化本质是：减少全表扫描（索引查询）、减少回表查询（索引覆盖）、减少文件排序（索引排序）
  4. explain 是定位性能问题的核心工具
  5. DML 优化要避免索引失效导致锁升级