4.2 索引类型

4.2 索引类型

本节摘要：B-Tree 是绝对主力但不是全部。本节把聚簇索引与二级索引这对最重要的概念讲透——它们决定了回表为何发生——再比较 Hash、全文、空间索引的适用边界。位置：4.1 树结构之上的形态学，4.3 覆盖索引的讨论以本节的回表机制为前提。

聚簇索引：数据本身就是一棵树

InnoDB 的表就是一棵 B+ 树，叶子页里放的是整行数据，按主键排序——这叫聚簇索引（clustered index）。这是 InnoDB 与 MyISAM 的根本差异：MyISAM 的索引树叶子放的是数据文件的地址指针，索引是索引、数据是数据；InnoDB 则是索引即数据。

这个设计的连锁反应值得逐条想清楚。第一，主键顺序决定物理存储顺序，所以 4.1 节说自增主键写入最友好。第二，全表扫描就是遍历这棵树的叶子链。第三，二级索引（在非主键列上建的索引）的叶子页里存的不是整行，而是"索引列的值 + 主键值"。查二级索引找到主键后，还要拿着主键回到聚簇索引再查一次拿整行——这个动作叫回表。

用订单表演示：

CREATE INDEX idx_status ON orders (status);

SELECT order_id, amount FROM orders WHERE status = 2;
-- 执行链路：
-- ① 在 idx_status 树上定位 status = 2 的叶子 → 拿到一批 order_id（主键）
-- ② 每个主键回到聚簇索引（主键树）再找整行
-- ③ 取出 amount，返回
​

回表本身不慢——每次回表是一次主键树查找，三次 IO 封顶。可怕的是回表次数：status 等于 2 的行有 50 万个，就是 50 万次逐行回表，随机 IO 累加起来比全表扫描还慢。优化器对此心知肚明，所以你会看到明明有索引它却选择全表扫——不是优化器蠢，是回表算下来不划算。这个现象在第 5 章 EXPLAIN 里表现为 type 是 ALL 而 possible_keys 却列出了索引。

Hash 索引：等值之王，范围之殇

Hash 索引用哈希函数把键映射到桶，等值查询理论上一次定位，O(1)。但代价清单很长：不支持范围查询（哈希后的值无序）、不支持最左前缀匹配（复合键整体哈希）、不支持排序、冲突多时退化成链表扫描。InnoDB 因此没有开放的 Hash 索引类型，只在内部维护自适应哈希索引——对热点页自动建哈希加速，用户无感知也无法干预。Memory 引擎支持显式 HASH 索引，适用于临时表场景。

评审会上若有人问"什么场景该选 Hash 索引"，标准答案几乎总是"不需要选"——等值查询走 B-Tree 同样是毫秒级，而业务 SQL 十有八九包含范围、排序或前缀匹配，B-Tree 全兼容。

全文与空间索引：特殊数据的专用工具

全文索引（FULLTEXT）解决 LIKE '%关键词%' 的痛点。模糊匹配左开百分号时 B-Tree 无能为力，倒排索引则把文本分词后建立"词 → 文档"的映射：

CREATE FULLTEXT INDEX ft_title ON product (title) WITH PARSER ngram;

SELECT product_id, title FROM product
WHERE MATCH(title) AGAINST('机械键盘' IN BOOLEAN MODE);
​

ngram 分词器让中文按 2 字切片也能建倒排，这是中文检索可用的前提。数据量小、并发低时够用；要做完整的站内搜索（分词质量、相关性打分、高亮），专业的搜索中间件仍是更优解，数据库全文索引是"够用就好"的第一梯队。

空间索引（SPATIAL）配合 GEOMETRY 等空间类型，服务"附近三公里的门店"这类地理位置查询，底层是 R-Tree。它要求列 NOT NULL 且通常配合特定函数使用。用不上地理业务的话可以只作了解。

选型对照与易错点

• 误区一：给每列都建独立索引。三个单列索引在多条件查询里多数用不上（index merge 的合并效率远不如一棵设计好的复合索引），白吃写入税；
• 误区二：把全文索引当搜索引擎。复杂查询语法、容错、同义词、权重调整都是它的弱项，规模上来就该换专门方案；
• 误区三：二级索引叶子存的是行地址。那是 MyISAM 的结构，InnoDB 存的是主键值——正因如此主键必须短小，主键越长每个二级索引越胖。

演练：唯一索引与普通索引的写入差异

背景：评审会上有人说"手机号用普通索引就行，唯一校验放应用层，听说唯一索引写入更慢"。用实验验证这个说法的成色。操作：准备两列数据相同（一列唯一索引、一列普通索引）的对照表，批量插入十万行计时：

-- 对照实验（示意）：同一批数据分别插入两张结构相同的表
-- 表 A：phone 列 UNIQUE INDEX
-- 表 B：phone 列普通 INDEX
-- 结果（测试环境十万行）：
-- 表 A：8.2 秒    表 B：7.9 秒    差距约 4%
​

解读：差距远小于传闻，原因是两者的写入差异只在缓冲池未命中时显现——普通索引更新可以借助 change buffer 攒着延后合并，唯一索引必须立即读页判断唯一性。热数据场景两者几乎无差，冷数据批量写场景普通索引才有明显优势。结论：手机号这类业务唯一键，唯一索引照用不误，应用层校验管的是报错文案友好度，数据库唯一索引管的是并发竞争下的最终正确性——两层各干各的活。变式：如果这张表是超高频写入的冷热混合表（如验证码流水），可以评估普通索引加应用层去重，那是写入吞吐优先的取舍。

要点回顾：聚簇索引即数据，主键顺序即物理顺序；二级索引叶子带主键，回表次数是性能命门；Hash 只赢纯等值，InnoDB 里基本见不到显式 Hash；全文与空间是专用工具，别当万能药；唯一索引与普通索引的写入差异远小于传闻，业务唯一性该用唯一索引。回表的机理明确了，下一节讲怎么设计索引让回表干脆不发生。