MySQL怎么给字符串字段加索引

1. 字符串索引的类别：

1. 全字符串长度完整索引
2. 前缀索引
3. 字段倒序存储 + 前缀索引（相当于倒序后缀索引）
4. 新建字符串hashcode字段作为索引

2. 考虑是建立整个字符串长度的索引还是使用前缀索引

前缀索引的优点：索引字段长度短，索引文件占用空间小

前缀索引的缺点：

• 1. 增加额外的扫描行数：

因为前缀索引截取了字符串字段的部分字符，字段具有的唯一性会被破坏，索引树上可能存在多个相同的字段，必须进行多次回表查询，比如在以下用户的邮箱上分别建立全字符串索引和前缀索引


结合下图进行分析，顺便回顾MySQL的数据查询顺序

前缀索引只截取前六个字符的话索引树上就有四个一样的节点，因此必须在主表中依次查找ID1-4的记录行（回表四次），找出所有满足条件的记录。（因为不保证邮箱号唯一，可能多行记录用的是同一个邮箱）

• 2. 使用前缀索引的话就不能使用覆盖索引

  覆盖索引的好处就是直接从索引树的节点中拿到其他需要的字段值，比如这里只查询id和email字段值（索引树节点上挂的就是ID），那么就不需要回表查找name字段值。

  但是前缀索引必须要进行回操作，因为索引树中只是截取了索引字段的部分字符，MySQL必须从主表中取出索引节点中查到的ID记录行的数据，比对该行数据的完整的email字段是否与where语句中的条件一致。（也就是无法避免回表 == 无法使用覆盖索引）

3. 解决前缀长度问题的方法（倒序/hash）：

1）可以比对索引基数与所有数据行的比值大小。（索引基数就是该索引字段取值不同的行数），再通过权衡选择合适的前缀长度。可以使用以下语句分别计算4,5,6,7个字节索引长度的索引区分度。

mysql> 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;

2）使用倒序存储字段：如身份证号字段，往往后面几个字段是随机的，区分度高。（不支持范围查询）
3）对某个字段求hashcode并作为新的字段插入，并作为索引（hash的原理就是压缩算法，将长字符串压缩为短字符串，也不支持范围查询）