Skip to content

字符串索引

一、 问题背景

  • 场景：需要根据字符串字段（如邮箱、身份证号）进行查询，例如用户登录验证 (WHERE email='xxx')。
  • 目标：在保证查询效率的同时，考虑索引占用的存储空间。

二、 字符串索引的主要方式

  1. 完整索引 (INDEX(col))

    • 原理：对整个字符串内容创建索引。
    • 优点：
      • 查询精确，通常只需一次索引查找（如果区分度高）+ 一次回表（如果需要非索引列）。
      • 可以使用覆盖索引：如果查询只需要索引列和主键列 (SELECT id, email FROM ...)，则无需回表，性能更优。
    • 缺点：
      • 索引占用 空间较大，尤其是对于长字符串。
      • 可能导致 B+ 树单个节点能存储的索引项减少，树的高度可能增加。

  2. 前缀索引 (INDEX(col(N)))

    • 原理：只取字符串的前 N 个字节（或字符，取决于字符集）创建索引。
    • 优点：
      • 节省索引空间。
      • B+ 树节点可存储更多索引项，可能降低树高。
    • 缺点：
      • 可能增加查询扫描次数：如果多个字符串共享相同的前缀，找到第一个匹配前缀的记录后，需要继续扫描并多次回表判断完整字符串是否匹配。
      • 无法使用覆盖索引：即使查询所需列（包括完整字符串）似乎都被前缀包含，InnoDB 仍需回表确认完整信息，因为系统不确定前缀是否截断了关键信息。
    • 如何选择前缀长度 N：
      • 核心原则：在节省空间和保持足够区分度之间取得平衡。
      • 方法：
        1. 计算完整列的区分度：SELECT COUNT(DISTINCT col) FROM table;
        2. 计算不同前缀长度的区分度：SELECT COUNT(DISTINCT LEFT(col, N)) FROM table; (尝试不同的 N)
        3. 选择一个最小的 N，使得其区分度接近完整列的区分度（例如，达到完整列区分度的 95% 以上）。

三、 处理低区分度前缀的替代方案 (主要针对等值查询)

  • 问题：某些字符串（如身份证号的前几位地址码）前缀区分度很低，需要很长的前缀索引才能有效，失去了节省空间的意义。

  • 倒序存储 + 前缀索引

    • 原理：将字符串倒序存储，然后对倒序后的字符串创建前缀索引。利用字符串尾部可能具有更高区分度的特性。
    • 查询：WHERE reversed_col = REVERSE('input_value')
    • 优点：可能用较短的前缀获得高区分度；不增加额外存储列。
    • 缺点：
      • 写和读都需要 REVERSE() 函数计算，增加 CPU 开销（但 REVERSE 通常比 CRC32 快）。
      • 仍然是前缀索引，可能增加扫描行数。
      • 不支持范围查询。

  • Hash 字段 + 索引

    • 原理：新增一个整数字段，存储原始字符串的 Hash 值（如 CRC32()），并对该 Hash 字段创建索引。
    • 查询：WHERE col_crc = CRC32('input_value') AND col = 'input_value' (必须加上原始字段的精确匹配以处理 Hash 冲突)。
    • 优点：
      • 索引小 (INT 通常 4 字节)，查询效率高且稳定（Hash 冲突概率低，平均扫描行数接近 1）。
    • 缺点：
      • 需要额外存储列。
      • 写和读都需要 CRC32() 函数计算，增加 CPU 开销。
      • 存在（虽然低概率的）Hash 冲突，需要二次精确匹配。
      • 不支持范围查询。

四、 方案对比与选择依据

特性 完整索引 前缀索引 倒序+前缀索引 Hash 字段索引
空间占用 大 小 (取决于N) 中 (看N) 小 (索引+列)
查询性能 好 可能下降 (多回表) 可能下降 好且稳定
覆盖索引 支持 不支持 不支持 不支持
范围查询 支持 支持 (但效率看N) 不支持 不支持
额外CPU开销 无 无 REVERSE() CRC32()
额外存储列 无 无 无 需要

选择建议：

  1. 如果空间允许且需要范围查询或覆盖索引优化，优先考虑完整索引。
  2. 如果空间敏感，且可以接受少量性能损失（额外扫描）和无法使用覆盖索引，考虑前缀索引，并仔细选择前缀长度 N。
  3. 如果字符串前缀区分度极低，且只需要等值查询：
    • 追求更稳定的查询性能和较小索引体积，可接受额外列和 CPU 开销，选择 Hash 字段索引。
    • 不希望增加额外列，或 REVERSE() CPU 开销更低，可接受潜在的额外扫描，选择 倒序存储 + 前缀索引。

五、 总结

为字符串字段加索引需要在空间、查询性能、是否支持范围查询/覆盖索引等多个维度进行权衡。理解各种方法的原理和优缺点，结合具体业务场景（字段特性、查询模式）做出合适的选择。