1、创建完整的索引

但是这样如果字段过长的话， 可能会比较占用空间

2、创建前缀索引

比如alert table add index index1(email(6)) 。

影响

1、如果该索引的值的前6位重复项较多的话，创建前缀索引之后，可能会增加回表的次数，也就是可能会增加扫描的次数，这就说明索引的长度定义的不好。但是如果前缀索引的长度定义的好的话，就可以做到既省空间，又不用增加太多查询成本。
可以通过sql语句选取不同长度的前缀来看不同长度的值的区分度

  1. mysql> select 
  2.   count(distinct left(email,4)）as L4,
  3.   count(distinct left(email,5)）as L5,
  4.   count(distinct left(email,6)）as L6,
  5.   count(distinct left(email,7)）as L7,
  6. from SUser

2、前缀索引可能会使覆盖索引失效
例如需要查询的字段a(索引),id(主键)

select a,id from t where a='12345678'
  • 如果a不是前缀索引的话，那么由于a的索引树上已经有id主键的信息了，就不需回表了
  • 如果a是前缀索引的话，那么就需要回表一次，确定a字段的值是否符合查询条件。

    3、其他方式

    针对区分度较高的字段比如邮箱这种直接使用前缀索引的效果还是不错的，但是如果遇到前缀的区分度不够好的情况时，比如身份证号字段，同一地区的身份证号前边都是一样的，这个时候使用前缀索引的区分度就很低了，就需要创建更长的索引才可以。
    这种情况下需要怎么做？

    使用倒叙存储

    例如，把身份证倒着存储下来，每次查询的时候利用reverse()函数来查询
select field from t where id_card=reverse('input id card')

实践中需要去distinct验证一下前缀索引的长度是否够。

使用hash字段

在表上再创建一个整数字段，来保存身份证的校验码，同时在这个字段上建索引。
使用方法：每次插入记录的时候都使用crc32()函数得到校验码填到这个字段中。

alert table t add id_card_crc int unsigned,add index(id_card_crc)
select field from t where id_card_crc=crc32('input id card string') and id_card='input id card sring'

注意事项：因为是通过crc32函数计算的，可能会出现重复值，所以你的查询语句where部分要判断id_card部分是否相等。
这样索引的长度变成了4个字节，比原来小了很多。

使用倒序存储和使用hash字段两种方法的异同点。

相同点：都不支持范围查找。
不同点

  1. 从占用空间：倒序索引不会消耗额外的存储空间，而hash字段方法需要增加一个字段，当然倒序索引使用4个字节的前缀长度应该是不够的，如果再长点，这个消耗就和增加一个hash字段也差不多了
  2. 从CPU消耗方面：倒序索引每次写和读的时候，都需要调用一次reverse函数，而hash字段方式需要调用一次crc32函数。如果只从计算复杂度来看的话，reverse函数额外消耗的CPU更少些
  3. 从查询效率方面：使用hash函数的方式查询性能相对稳定一些。因为crc32的值虽然有冲突的概率，但是很低，可以认为每次查询的平均扫描行数接近1，而倒序索引毕竟还是前缀索引，会增加扫描行数。