11 | 怎么给字符串字段加索引？

用邮箱登录

假设用户表是这样设计的：

create table SUser(

ID bigint unsigned primary key,

email varchar(64),

...

)engine=innodb;

业务中肯定会有类似这样的语句：

select f1, f2 from SUser where email='xxx';

如果email这个字段上没有加索引，那么这个语句就只能做全表扫描。

MySQL支持前缀索引，可以定义字符串的一部分作为索引。如果不指定，索引默认会包含整个字符串。

比如在email字段上建立索引：

alter table SUser add index index1(email);

或

alter table SUser add index index2(email(6));

这两种定义结构的区别如下图：

可以看出指定了长度的索引占用空间会更小，这就是使用前缀索引的优势。

但是这样同时会是扫描行数增加（也可能会增加回表的次数）。

也就是说，使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。

 

那么，怎么样才能知道需要创建多少长度的索引呢？这儿要说的就是要看要建索引字段的区分度，区分度越高越好。因此可以统计索引上有多少个不同的值来判断要使用多长的前缀。

可以通过下面的语句，算出这个列上有多少个不同的值：

select count(distinct email) as L from SUser;

然后，依次选取不同长度的前缀来看这个值，比如我们要看一下4~7个字节的前缀索引，可以用下面的语句：

select

count(distinct left(email,4)) as L4,

count(distinct left(email,5)) as L4,

count(distinct left(email,6)) as L4,

count(distinct left(email,7)) as L4

from SUser;

前缀索引很可能会损失区分度，所以需要预先设置一个可以接受的损失比例，比如5%。然后再返回的L4~L7中，找出不小于L*95%的值，假设这里L6、L7都满足，可以选择前缀长度为6。

 

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

上面说使用前缀索引会影响到性能。其实，不止如此。

对于那些只需要查询主键的，如果使用完整索引，不用回表，索引覆盖就没问题。但是如果使用了前缀索引，就不能直接返回主键值，必须回表，到主键索引上判断完整的值是不是相等的，也就是说使用前缀索引会使索引覆盖失效。

这也是使用前缀索引的时候，需要考虑的一个因素。

 

其他方式

对于邮箱这样的字段，区分度还可以，使用前缀索引的效果还不错。

但是如果遇到前缀的区分度不够好的情况，应该怎么办？

比如身份证号，我国的身份证号一共是18位，前六位是地址码，所以同一个县的人的身份证号的前六位一般会是相同的。

这样看的话，取前六位的区分度就非常低了，可能需要取12位以上的索引才能满足区分度的要求。但是索引取的越长，占用的磁盘空间就越多，相同的数据页能放下的索引值就越少，搜索的效率也就越低。

那有没有好的方法呢？

第一：使用倒叙存储。比如身份证号就可以考虑倒过来存储。每次查询的时候可以这么写：

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

实践中，不要忘记使用count(distinct)方法去做个验证。

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

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

每次插入的时候都使用crc32()这个函数得到校验码填到这个新字段。因为校验码可能会冲突，所以判断时候还要加上身份证号的判断。

select field_list_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string';

 

使用倒叙和hash字段这两种方法的异同点：

相同点就是，都不支持范围查询。都是只支持等值查询。

区别，主要体现在一下三方面：

1. 占用空间，倒叙不会占用额外的空间，hash字段方法需要增加一个字段。   但是如果倒叙之后前缀索引的长度稍微长的话，可能hash字段额外的空间消耗跟这个也差不多了。
2. cpu消耗方面，倒叙每次都要额外调用一次reverse()函数，而hash字段方法需要额外调一次crc32()函数。如果只从两个函数的计算复杂度来看的话，reverse函数额外消耗的CPU资源会更小些。
3. 从查询效率上看，使用hash字段方式的查询性能相对更稳定一些。因为crc32算出来的值冲突的概率很小，每次查询平均扫描行数接近1。而倒叙方式毕竟使用的还是前缀的方式，肯定回增加扫描行数。

 

上一篇：10 | MySQL为什么有时候会选错索引？

下一篇：12 | 为什么我的MySQL会“抖”一下？