如何给字符串字段家索引

如何给字符串字段加索引

1. 常见的字符串索引有哪些

• 完整索引。
• 前缀索引。
• 倒叙索引。
• 哈希索引。

2. 完整索引

在整个字符串上建立索引。

2.1 完整索引的问题

完整索引使用整个字符串字段建立索引。当字段的长度过长时，会占用较多的存储空间。

3. 前缀索引

使用字符串的前n个字符创建索引。

3.1 如何确定前缀索引的n值

建议索引的关注点是区分度，即某列中不同的值的个数/数据总条数。区分度越高的时候，重复的值越少。

我们可以使用试验的方法，确定不同n值的区分度，然后选取区分度最大的n值。

例如我们要在email字段上建立索引:

select 
  count(distinct left(email, 4)) as L4,
  count(distinct left(email, 5)) as L5,
  count(distinct left(email, 6)) as L6,
  count(distinct left(email, 7)) as L7,
from SUser;

假设我们设定的区分度目标是 95%，若L6，L7都达标，则选择L6，即前缀索引的长度为6。

3.1 前缀索引的问题

• 增加扫描次数。
• 不能使用覆盖索引。若列值x使用前缀索引，则查找语句涉及列值x的话，需要回表。

4. 倒序索引

将字符串倒置，再创建前缀索引。

4.1 倒序索引的使用场景

字符串后几位的区分度更高，例如身份证的后六位。

4.2 倒序索引的问题

倒叙索引无法使用范围查询。

5. 哈希索引

在表上再创建一个字段，用于保存某个字段的校验码。例如在身份证号上使用crc32() 校验码。

5.1 哈希索引的问题

哈希索引无法使用范围查询。

6. 总结

最常见的字符串索引是完整索引，后续的改进都是为了减少字符串索引的存储空间，但同时引入了不同的问题。

在实际的应用中，需要根据不同的场景选择合适的策略。