索引篇(二)

如何给字符串字段建立合适的索引？

MySQL支持前缀索引，前缀索引是指可以取原索引的前多少个字节作为一个新的索引，而取前多少个字节取决于你数据的情况，前缀索引的优势在于占用空间会更小。而坏处在于使用前缀索引会导致查询语句读数据的次数变多。

前缀索引的长度如何定义？ 对于前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。

索引区分度，区分度越高，意味着重复的键值越少。我们可以通过统计索引上有多少个不同的值来判断要使用多长的前缀。

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;

预先设定一个可以接受的损失比例，比如5%，在返回的L值里，找出不小于L*95%的值。假设这里L6，L7都满足，就可以选择前缀长度为6.

还有一点，使用前缀索引，innoDB并不知道你给的是不是完整的信息，哪怕截取了原长度，这里会用不上覆盖索引对查询性能的优化，InnoDB会再回到主键索引再查一下，

身份证号这种例子，采用倒叙存储。

第一种方式是使用倒序存储 如果你存储身份证号的时候把它倒过来存，每次查询的时候，你可以这么写：

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

第二种方式是使用 hash 字段。你可以在表上再创建一个整数字段，来保存身份证的校验码，同时在这个字段上创建索引。

mysql> alter table t add id_card_crc int unsigned, add index(id_card_crc);

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

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

案例：给一个学号字段创建索引。

由于这个学号的规则，无论是正向还是反向的前缀索引，重复度都比较高。因为维护的只是一个学校的，因此前面 6 位（其中，前三位是所在城市编号、第四到第六位是学校编号）其实是固定的，邮箱后缀都是 @gmail.com，因此可以只存入学年份加顺序编号，它们的长度是 9 位。而其实在此基础上，可以用数字类型来存这 9 位数字。比如 201100001，这样只需要占 4 个字节。其实这个就是一种 hash，只是它用了最简单的转换规则：字符串转数字的规则，而刚好我们设定的这个背景，可以保证这个转换后结果的唯一性。