列前缀匹配索引的原理

calendar Mar 10, 2025 · 1 min read · 性能优化 mysql

当对 name 列的所有内容建立完整索引时，WHERE name LIKE 'John%' 仍然可以利用索引的核心原因在于 B+Tree 索引的排序存储特性和 前缀匹配的天然优势。以下是详细解释：

1. B+Tree 索引的排序存储

完整索引会存储 name 列的完整值，并按字符顺序排序在 B+Tree 结构中。例如，值 'John'、'Johnny'、'Johnson' 会按字典序排列为：

1John
2Johnson
3Johnny
4...

由于索引键是完全有序的，数据库可以通过以下步骤高效处理 LIKE 'John%'：

  1. 定位起点：快速找到第一个以 'John' 开头的索引条目（通过二分查找或树遍历）。
  2. 顺序扫描：从起点开始，顺序读取后续所有以 'John' 开头的条目，直到遇到不匹配的值（如 'Jonathan' 后的 'Mike'）。

这一过程仅需遍历索引的一部分，无需全表扫描。

2. 前缀匹配的天然优势

LIKE 'John%' 是典型的前缀匹配查询，而 B+Tree 索引的排序特性使其天然支持此类查询。以下对比说明不同匹配模式的索引利用率：

查询模式 是否利用索引 原因
WHERE name LIKE 'John%' ✅ 是 前缀匹配，索引有序，可快速定位起点并顺序扫描。
WHERE name LIKE '%John' ❌ 否 后缀匹配，索引无法反向遍历，需全表扫描。
WHERE name = 'John' ✅ 是 精确匹配，直接通过索引定位。

3. 完整索引 vs 前缀索引

虽然完整索引和前缀索引都支持 LIKE 'John%'，但二者在性能和存储上有差异：

特性 完整索引 前缀索引
存储空间 较大（存储完整值） 较小（仅存储前缀）
查询性能 更高（无需回表验证） 可能较低（需回表验证）
适用场景 精确匹配、前缀匹配 前缀匹配、存储敏感场景
  • 完整索引的优势：
    由于存储了完整值，数据库在索引中即可完成 LIKE 'John%' 的匹配，无需回表读取数据行验证完整值（覆盖索引）。而前缀索引可能需要回表确认剩余字符是否匹配。

4. 覆盖索引的进一步优化

如果查询仅需访问 name 列（如 SELECT name FROM users WHERE name LIKE 'John%'），完整索引可直接作为覆盖索引（Covering Index），避免访问数据行，进一步提升性能。

5. 为什么后缀匹配（LIKE '%John'）无法利用索引？

B+Tree 索引的键值按从左到右的字符顺序排序，而 LIKE '%John' 要求匹配末尾的 'John'。由于索引无法反向遍历或跳跃匹配，数据库只能全表扫描所有值，逐个检查是否符合条件。

总结

使用 name 列的完整内容建立索引时，WHERE name LIKE 'John%' 能利用索引的核心原因是：

  1. 索引的有序性：B+Tree 按完整值排序，支持快速定位前缀匹配的起点。
  2. 顺序扫描效率：从起点开始顺序遍历索引条目，直到不满足前缀条件。
  3. 避免全表扫描：仅扫描索引的一部分，而非全表数据。

完整索引在支持前缀匹配的同时，还能优化精确匹配和覆盖索引场景，但需权衡存储成本。