mysql之给字符串加索引

前言

之前的文章介绍了主键索引和唯一索引的区别，也介绍了主键索引和唯一索引在不同业务场景下的区别。今天我们继续介绍，普通索引怎么合理的使用。

长字段加索引

这里我们就用邮箱举个例子。大多数的邮箱都比较长，格式一般为xxxxx@xxx.com。那我们加索引的话，一般有两种形式

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

一个指定了索引的长度，一个没有指定索引的长度。有什么区别呢，显而易见，一个索引存储的长度是6，一个索引存储的长度是整个邮箱的长度


这两个查询有什么区别呢，其实就是比如前六个字符串有四个相同的，它会先扫描第一个，然后去主键索引上取值匹配，发现不匹配，接着下一个，下一个匹配成功，加入结果集，知道前六个匹配不到为止。而全部索引也是一样。但是全部索引到主键索引取值的次数会少。这个就是区分度的问题。如果前六个字段的区分度和整个索引的区分度一样。那么就不会有这个问题。

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

前缀索引会导致覆盖索引失效，原因就是即使索引a加上前缀索引b匹配上了，它也需要去主键索引取值匹配，因为他不知道你这个索引截取的是否是全的，即使你前缀索引是全的，它也会去主键取值匹配。

合理的使用前缀索引

第一种选取合适的长度：相比学过算法的同学都知道，算法的本质就是在空间和时间上取一个平衡点。
这个同样，在前缀索引长度和区分度之间取一个平衡点即可
下面是查询不同长度的前缀索引的区分度

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;

第二种采用倒叙存储：如存储身份证的时候，我们可以采用后几位进行区分，这个时候你可以把身份证倒着存储。下面是倒着存储的查询方式。

mysql> select field_list from t where id_card = reverse('input_id_card_string');

第三种使用hash字段：比如身份证，你可以再加一个字段作为校验字段。比如通过crc32函数生成校验码。

mysql> select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'

.

最后总结下集中方式的区别
第一种：比较通用，但是不好取舍。
第二种和第三种：不同担心取舍的问题。基本都能保证区分度。但是不支持范围查找。
这里我们在说下23种的区别
1.空间消耗，hash字段会有额外的空间消耗，但是如果倒叙字段过长，其实两个是差不多的。
2.性能方面：一个是用reverse函数，一个使用crc函数，相比较来说，可能crc会稍微好一点，但是很有限。
3.查询效率：hash虽然会有冲突，但是概率会比较低，所以可以认为每次都是唯一匹配，扫描行数为1，但是倒叙索引也是前缀索引，如果不能像主键索引一样完全区分，还是会增加扫描行数的。

总结

总的来说，第三种还是比行的一种方法，相对于前缀索引和倒序索引，不管是存储还是查询效率方面来说，都是比较强的。但是我这里有一点问题，就是函数可能会导致索引失效，不知道这个reverse函数和crc函数会不会导致索引失效（这个问题我也不是很清楚，希望有知道的大佬评论区捞我一把）。