长字符串创建索引

1.前缀索引和普通索引的区别

 　　当系统中有一个长的字符串需要创建索引的时候，常见的有两种方式，一个创建普通索引，全字段创建；另外一种就是创建前缀索引；那么这两种索引有什么区别呢？

　　

mysql> alter table SUser add index index1(email);
或
mysql> alter table SUser add index index2(email(6));

　　

　　select id,name,email from SUser where email='zhangssxyz@xxx.com';

　　普通索引查询的过程：

• 在index1索引的内存页查询到该key,去主键索引中找出改行数据比对email字段成功后，把查询的数据放入结果集；
• 比对刚才查询index1索引树的位置的一个位置，查看不符合条件直接返回；
• 此过程只经过了一次回表操作

　　前缀索引查询的过程：

• 在index2索引的内存页查询到该key,去主键索引中找出改行数据比对email字段发现匹配不上；
• 在index2的索引树中查询刚才位置的下一个key,重复上述步骤，直到全部key搜索完成；
• 在此过程中可能进行多次回表操作；

　　由此可见前缀索引虽然在存储索引方面占用了更少的空间，减少了索引树中的io次数，但是也会增加回表次数。

　　如果把索引长度变成7：alter table SUser add index index2(email(7));  回表次数和普通索引就相差无几了；

　　综上所述，在创建前缀索引的时候索引的长度是关键。

　　

2.如何确定前缀索引的长度

 　　关系到前缀索引长度的选择的一个重要参数是：索引的区分度，区分度越高，键值的重复度越少，由于长度引起的回表就会越少；不过区分度越高，索引长度越长，越占用空间，所以需要根据业务衡量可以接受的损失比例m%,当 distinct(left(key))/distinct(key)>m%的基础上 长度越小越好；

 

3.前缀索引对覆盖索引的影响

　　即使sql中用到了覆盖索引，因为是使用的前缀索引，拿不到index的完整数据，还是需要回表获取完整的数据，索引前缀索引是无法使用索引覆盖的；

4.当前缀索引区分度低的时候，采取倒叙索引或者hash

 　　当对身份证这种前半部分区分度小的字符串创建索引时，推荐使用倒叙创建前缀索引；

　　或者增加一个hash(cardId)的字段，用 crc32()函数计算数值，创建索引，查询的时候也倒叙查询，因为 crc32()这个函数可能把不同的身份证号计算出相同的值，所以产寻得时候需要判定index的值。

5.倒叙索引和hash索引的区别 

• 占用表空间层面：倒叙索引不会多于占用空间，hash会创建一个多余的字段；
• 占用索引空间层面上：hash索引用crc32()函数计算出来的是4位，倒叙索引一般四位是不够的的；
• cpu消耗方面：每次查询倒叙索引需要使用reverse（）函数，hash需要使用 crc32()函数， crc32()函数相对消耗的资源小一点；
• 查询效率上：倒叙索引说白了还是前缀索引，区分度还是没有hash好；