怎么给字符串字段加索引（mysql45讲）

问题

现在，几乎所有的系统都支持邮箱登录，如何在邮箱这样的字段上建立合理的索引，是我们今天要讨论的问题

普通索引到前缀索引

• Mysqll是支持前缀索引的，也就是说，你可以定义字符串的一部分作为索引，默认的，如果你创建索引的语句不指定前缀长度，那么索引就会包含整个字符串

比如下面两个例子：

ALTER TABLE SUser ADD INDEX index1(email);			//包含了记录的整个字符串
ALTER TABLE SUser ADD INDEX index2(email(6));		//对于每个记录只是取前6个字节

使用前缀索引占用的空间更小，但是也可能同时带来困难!!!

增加额外的记录扫描行数

如果使用的是index2

1. 从index2索引树找到满足索引值是zhangs的记录，找到的第一个值是ID1
2. 到主键索引上查到主键是ID1的行，判断出email的值不是’zhangssxyz@xxx.com’,这行记录丢弃
3. 取index2上刚刚查到的位置的下一条记录，发现仍然是zhangs,取出ID2,再到ID索引上取整行进行判断，这次值对了，将这行记录加入结果集
4. 重复上一步，直到在index2上取到的值不是zhangs时循环结束

在这个过程中，要回主键索引取4次数据，就是扫描了四行

• 通过这个对比就可以发现，使用前缀索引后，可能会导致查询语句读数据的次数变多

因此，使用前缀索引定义好长度很关键，如果你定义好长度，就可以一次查询到结果，然后又可以节省空间

什么方法能够确定我应该使用多长的索引呢？

实际上我们在建立索引的时候关注的是区分度，区分度越高越好，因为区分度越高，意味着重复的值越少，因此，我们可以通过统计索引上有多少个不同的值来判断要使用多长的前缀

1、使用下面语句，统计出这个列上有多少个不同的值(可以理解为求区分度)

SELECT COUNT(DISTINCT NAME) AS L FROM USER;

2、 依次选取不同长度的索引来看这个值，比如我们要看4-7个字节的前缀索引，可以用这个语句：

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;

• 使用前缀索引很可能会损失区分度，所以你需要预先设定一个可以接受的损失比例，比如5%,然后，在返回的L4~L7中，找到不小于L * 95%的值，假设L6,L7都满足，你就可以选择前缀长度为6

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

下面这个语句：

SELECT id,email FROM SUser WHERE email='zhangssxyz@xxx.com';

如果使用普通索引就可以直接使用覆盖索引来得到返回结果，如果使用前缀索引的话必须把zhangs回到主键上取出完整的email进行判断，这就意味着使用前缀索引的话覆盖索引就失效了

其他来给字符串加索引

• 对于类似于邮箱这样的字段来说，使用前缀索引的效果可能还不错，但是，遇到前缀的区分度不够好的情况时，我们要怎么办呢？

比如身份证号，前十几位都是相同的，这个时候我们取前六位的话意义不大！！！

解决方案：

1. 倒序存储，存值的时候倒序存储，由于身份证后六位没有地址码很少重复，每次查询时用如下语句：

SELECT field_list FROM t WHERE id_card = reverse（'input_id_card_string');

2. 使用hash字段

• 可以在表上创建一个整数字段，来保存身份证的校验码，同时在这个字段上建索引

ALTER TABLE t ADD id_card_crc INT UNSIGNED,ADD INDEX(id_card_crc);

• 然后每次插入新纪录的时候，都同时用CRC32()这个函数得到校验码填到这个新字段，由于校验码可能存在冲突，也就是说两个不同的身份证号通过CRC32()函数得到的结果可能是相同的，所以你的查询语句where部分要判断id_card的值是否精确相同

SELECT field_list FROM t WHERE id_card_crc = CRC32('input_id_card_string') AND id_card='input_id_card_string';

就这样，索引的长度变成了4个字节，比原来小了很多

倒序存储和hash存储的异同点

相同点：都不支持范围查询
不同点:
	1.占用的额外空间，hash增加了一个字段，不会消耗额外的存储空间
	2. 在CPU消耗上面，倒序方式每次读写时都会调用一个reverse函数，hash需要调用yicicrc32函数，reverse函数消耗的资源少一点
	3. 从查询效率上看，hsah索引好一点，因为发生冲突的可能性较小，而倒序索引可能会多次判断，比较耗时

小结

• 如果对很长的字段使用普通索引的话会很占用空间，因此考虑使用前缀索引，使用前缀索引的话需要选择一个合适的长度，这个合适的长度经常是索引的区分度，可以通过select count(distinct 字段名)来选择判断，除了选择长度外，前缀索引还会发生覆盖索引的索引失效的情况，除此之外还给出了两个解决方案，一个是反转字符串，一个是hash函数，使用hash函数更加健壮，但他们都不适合范围查询，只适合等值查询。