索引原理

索引是 SQL 性能的核心。这一篇只讲一条主线：B+ 树长什么样 → 因此哪些查询能加速 → 因此索引怎么建、什么时候失效。

一、为什么是 B+ 树

InnoDB 的索引结构是 B+ 树，不是哈希表也不是二叉树，原因在于磁盘 I/O：

结构问题
哈希索引O(1) 等值查找，但不支持范围查询、排序、最左前缀匹配
二叉搜索树 / 红黑树树高 O(log₂n)，1000 万数据约 24 层 → 24 次磁盘 I/O
B 树非叶子节点也存数据，单页能放的键更少，树更高；范围查询要回溯
B+ 树非叶子节点只存键 + 指针，扇出大；数据全在叶子层，且叶子间双向链表相连

InnoDB 的页（page）默认 16KB。以 BIGINT 主键（8 字节）+ 页号指针（6 字节）计，一个非叶子页约能放 16384 / 14 ≈ 1170 个键。因此：

  • 2 层：1170 × 每页行数
  • 3 层：1170 × 1170 × 约 16 行 ≈ 2000 万行

也就是说，千万级表的 B+ 树只有 3 层，查一行最多 3 次页访问，而且根节点和内节点常驻 Buffer Pool，实际磁盘 I/O 往往只有 1 次。这就是索引的威力来源。

叶子节点串成双向链表，因此 ORDER BY、BETWEEN、> 这类范围扫描可以顺着链表连续读取，不必回到树根。

二、聚簇索引与二级索引 ⭐⭐⭐

InnoDB 是索引组织表（IOT）：

  • 聚簇索引（clustered index）：叶子节点存放完整的行数据。每张表有且只有一个。 选取顺序：主键 → 第一个非空唯一索引 → 隐藏的 6 字节 DB_ROW_ID。
  • 二级索引（secondary index）：叶子节点存放索引列 + 主键值，不存整行。

由此推出两个最重要的结论：

1. 回表（back to table）

-- idx_name 是 name 上的二级索引
SELECT * FROM employee WHERE name = 'Tom';

执行过程：在 idx_name 上找到 ('Tom', id=42) → 拿着 id=42 再去聚簇索引上查完整行。走了两棵 B+ 树，这就是回表。如果匹配 1000 行，就要回表 1000 次（每次都是随机 I/O）——这正是优化器有时宁可全表扫描的原因。

2. 主键设计

  • 主键要短：每个二级索引的叶子节点都要存一份主键值。用 VARCHAR(64) 当主键，会让所有二级索引膨胀。
  • 主键要有序：AUTO_INCREMENT 顺序插入，新行总是追加到最右侧的页，页利用率高。用 UUID 这类随机值做主键，插入位置随机分布，会频繁触发页分裂、产生碎片、Buffer Pool 命中率下降。

    需要全局唯一 ID 时用雪花算法（趋势递增）或 MySQL 8.0.13+ 的 UUID_TO_BIN(uuid, 1)（交换时间低位到高位，使其有序）。

三、联合索引与最左前缀 ⭐⭐⭐

联合索引 (a, b, c) 的 B+ 树按 (a, b, c) 字典序排列：先按 a 排，a 相同再按 b 排，b 也相同才按 c 排。

因此可以匹配：

查询条件能用到的索引部分
a = 1a ✅
a = 1 AND b = 2a, b ✅
a = 1 AND b = 2 AND c = 3a, b, c ✅
a = 1 AND c = 3只有 a（c 用于 索引条件下推过滤）
b = 2❌ 用不上，因为 b 只在 a 相同的局部有序
a > 1 AND b = 2只有 a：范围条件之后的列无法再用于定位
a = 1 AND b > 2 AND c = 3a, b（c 只能过滤）
a = 1 ORDER BY ba 定位 + b 天然有序，免排序

联合索引的列序原则

  1. 等值条件的列放前面，范围条件的列放最后——范围列之后的列失去定位能力。
  2. 区分度高的列靠前（区分度 = COUNT(DISTINCT col) / COUNT(*)，越接近 1 越好）。
  3. 兼顾 ORDER BY / GROUP BY：把它们的列按顺序接在等值列之后，可以省掉 filesort。
  4. (a, b) 已经覆盖了 (a) 的能力，不要再单独建 (a) 索引。

WHERE 中条件的书写顺序无关紧要：WHERE b = 2 AND a = 1 和 WHERE a = 1 AND b = 2 完全等价，优化器会自动调整。真正有顺序要求的是索引定义中的列序。

四、覆盖索引

如果查询需要的所有列都在索引里，就不需要回表，EXPLAIN 的 Extra 会显示 Using index。

-- 索引 idx_dept_salary (department_id, salary)
SELECT department_id, salary FROM employee WHERE department_id = 3;  -- ✅ 覆盖索引
SELECT * FROM employee WHERE department_id = 3;                       -- ❌ 需要回表

二级索引里"免费"包含主键，所以 SELECT id, salary FROM employee WHERE department_id = 3 同样是覆盖索引。

这也是禁止 SELECT * 的首要理由：多取一列就可能从"覆盖索引"退化成"每行回表"，性能相差一个数量级。

五、其他索引形态

类型说明
前缀索引KEY idx (email(20))。长字符串列只索引前 N 个字符，节省空间，但无法用于覆盖索引和排序
唯一索引保证唯一性。写入时无法使用 change buffer（必须读页判重），写多读少场景略慢于普通索引
函数索引8.0.13+，ALTER TABLE t ADD INDEX ((MONTH(created_at)))，可让原本失效的函数条件走索引
降序索引8.0+，KEY idx (a ASC, b DESC) 真正按降序存储。8.0 以前 DESC 关键字被静默忽略
不可见索引8.0+，ALTER TABLE t ALTER INDEX idx INVISIBLE。删索引前先设为不可见观察影响，可秒级回滚
全文索引FULLTEXT，配合 MATCH ... AGAINST。中文需要 ngram 解析器
多值索引8.0.17+，针对 JSON 数组，配合 MEMBER OF / JSON_CONTAINS

选前缀长度的方法：找到让区分度接近完整列的最短前缀。

SELECT COUNT(DISTINCT LEFT(email, 6)) / COUNT(*),
       COUNT(DISTINCT LEFT(email, 8)) / COUNT(*),
       COUNT(DISTINCT email)          / COUNT(*)
FROM users;

六、索引失效场景 ⭐⭐⭐

先说清楚一件事

"索引失效"分两种，很多资料混为一谈：

  • 无法使用（语义上不可能用索引定位），如左模糊、对索引列做运算；
  • 优化器选择不用（能用，但代价估算认为全表扫描更划算），如返回行数占比过大、!=、OR。 后者不是失效，是优化器的正常决策。判断依据只有一个：EXPLAIN + optimizer_trace。

一定用不上索引：

  1. 索引列参与运算或被函数包裹
    WHERE YEAR(created_at) = 2024        -- ❌
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'  -- ✅
WHERE id + 1 = 10                    -- ❌
WHERE id = 9                         -- ✅
  2. 隐式类型转换（本质也是函数）
    -- phone 是 VARCHAR，传入数字会触发 CAST(phone AS DOUBLE)，索引失效
WHERE phone = 13800138000            -- ❌
WHERE phone = '13800138000'          -- ✅

    反过来，列是 INT 而传字符串 WHERE id = '9' 不影响索引使用，因为转换发生在常量侧。 关联字段的字符集或排序规则不一致同样会导致转换，是 JOIN 慢的常见隐藏原因。

  3. 左模糊 / 全模糊：LIKE '%abc'、LIKE '%abc%'。B+ 树按前缀有序，没有前缀就无法定位。需要全文检索请用 FULLTEXT 或 Elasticsearch。
  4. 违反最左前缀：(a, b, c) 索引下的 WHERE b = 1。

    例外：MySQL 8.0.13+ 的索引跳跃扫描（skip scan）在首列基数很低时可能仍然用上索引。

可能用不上（取决于代价）：

  1. OR 连接的条件中有列没有索引 → 整体退化为全表扫描。若两列都有索引，优化器可能选择 index_merge。
  2. != / NOT IN / IS NOT NULL → 通常匹配大部分行，优化器判断回表代价高于全表扫描时会放弃索引。
  3. 返回行数占比过高（经验值 > 20%~30%）→ 随机回表不如顺序全表扫描。
  4. ORDER BY 与索引方向不一致：ORDER BY a ASC, b DESC 在 8.0 之前无法利用 (a, b) 索引消除排序。

七、索引条件下推（ICP）

MySQL 5.6 引入的 Index Condition Pushdown：把能用索引列判断的条件下推到存储引擎层，在二级索引上先过滤，减少回表次数。

-- 索引 (name, age)
SELECT * FROM t WHERE name LIKE 'Z%' AND age = 20;
  • 无 ICP：引擎层按 name LIKE 'Z%' 取出所有匹配行 → 全部回表 → Server 层再过滤 age = 20；
  • 有 ICP：引擎层在索引里就用 age = 20 过滤 → 只回表真正需要的行。

EXPLAIN 的 Extra 中出现 Using index condition 即表示生效。这也解释了为什么"范围条件后面的列"依然值得放进联合索引——不能定位，但能过滤。

八、索引使用的实践清单

✅ 该建索引：

  • WHERE、JOIN ON、ORDER BY、GROUP BY 中高频出现的列
  • 区分度高的列
  • 能构成覆盖索引的组合

❌ 不该建：

  • 区分度极低的列（如性别），单独建索引几乎无意义
  • 很少查询的列，以及频繁更新的列（每次更新都要维护索引 B+ 树）
  • 冗余索引：有了 (a, b) 就不要再建 (a)
  • 单表索引不宜过多（经验值 5~6 个以内）：索引占空间，且拖慢写入、增加优化器选择成本

排查工具

-- 查看表上的索引与基数
SHOW INDEX FROM employee;
-- 8.0：找出从未被使用过的索引（需开启 performance_schema）
SELECT * FROM sys.schema_unused_indexes;
-- 找出冗余/重复索引
SELECT * FROM sys.schema_redundant_indexes;

下一步 👉 事务与锁

上次更新:
贡献者: Joe