1. 建立前缀索引
假设建立一个支持邮箱登录的用户表，对于邮件字段来说，可以有以下几种建立索引的方式：
1.直接对整个字符串建立索引
alter table SUser add index index1(email);
2.对整个字符串的前一部分建立索引 - 前缀索引
alter table SUser add index index2(email(6));
方式 2 相较于 方式 1 来说，利用前缀索引，占用的空间更小。但有可能造成性能的损失，读取数据的次数变多。
假设在 user 表中存在
[email protected]
,
[email protected]
,
[email protected]
, 三条记录。
有这样一条语句
select id,name,email from SUser where email='[email protected]';
使用 index1 索引时，流程如下：
-
-
在主键索引上对应 ID的行，判断 email 是否正确，将记录加入结果集。
-
接着取 index1 索引的下一条记录，发现不满足 email 格式，结束循环。
使用 index2 索引：
-
在 index2 中，找到名字是
zhangs
的记录，获取 ID.
-
-
接着在 index2 循环，拿到下一条记录 ID。
-
-
接着在 index2 循环，拿到下一条记录 ID。
-
-
接着在 index2 循环，发现记录格式不符合，结束循环。
看这个过程，很容易发现，前缀索引会增加查询语句读取数据的次数。
但如果将前缀索引的 email(6) 改成 email(7)，就会减少查询的次数，对应在主键索引上只搜索一次。这就说明，如果能合适的设置前缀索引的长度，就能在空间和效率上取得平衡。
如何找到合适的前缀索引长度
在建立索引时，应该去关注区分度，区分度越高，则说明重复的键值越少。
可以通过执行查询来统计列上有多少不同的值。
mysql> select
count(distinct email）as L,
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;
接着确定业务上可以接受的顺势区分度，比如
5%
, 用 L 的数量 * 区分度比例（1-5%=95%），然后看在 L4 到 L7 中哪个满足。
前缀索引的影响
在之前覆盖索引的文章中，如果查询的列的信息被包含在二级索引上，那么就可以避免
回表
的过程，进而减少查询次数，提供效率。但如果在建立索引时，使用了前缀索引，那么无论满不满足覆盖索引的规则，都会回表。因为系统不能确定前缀索引是否截取了完成信息，进而必须做一次判断。
也就是说，前缀索引除了会增加查询语句的次数，还会禁止使用覆盖索引。
2. 倒序存储
对于邮箱这类的字符串来说，由于前几位有较大的区分度，所以用前缀索引还不错。但如果是区分度不好的情况，比如身份证，前 6 位都是地址码，很多人都会一样。这时如果想要使用前缀索引，就需要至少 12 位以上，对应查询效率和空间都不是很合适。
一个比较好的办法是将字符串倒序存储，将区分度高的字符开头。
例如：
mysql> select field_list from t \
where id_card = reverse('input_id_card_string');
3. 使用 hash 字段
在网络传输时，CRC - 循环冗余校验被用于检验文件。对应在 MySQL 里也有这个函数，
crc32()
.
该函数的返回范围是
0-4294967296
也就是 4 字节，相对于其他字符串来说，属于较短的长度。
在创建表时，可再创建一个整数字段，来保存这类字符串，如身份证的校验码（crc32()的返回值）, 并为该字段创建索引。
如：
mysql> alter table t add id_card_crc int unsigned, \
add index(id_card_crc);
在插入记录时，将
crc32()
的结果插入到记录中。
但由于
crc32()
只有 32 位的特性，容易发生 hash 碰撞，就是说可能两个字符串经过计算后得到相同的验证码。这时就存在冲突，所以还需要判断下查询的值是否一致。
如：
mysql> select field_list from t where \
id_card_crc=crc32('input_id_card_string') and \
id_card='input_id_card_string'
