用邮箱登录
假设用户表是这样设计的:
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字段这两种方法的异同点:
相同点就是,都不支持范围查询。都是只支持等值查询。
区别,主要体现在一下三方面:
- 占用空间,倒叙不会占用额外的空间,hash字段方法需要增加一个字段。 但是如果倒叙之后前缀索引的长度稍微长的话,可能hash字段额外的空间消耗跟这个也差不多了。
- cpu消耗方面,倒叙每次都要额外调用一次reverse()函数,而hash字段方法需要额外调一次crc32()函数。如果只从两个函数的计算复杂度来看的话,reverse函数额外消耗的CPU资源会更小些。
- 从查询效率上看,使用hash字段方式的查询性能相对更稳定一些。因为crc32算出来的值冲突的概率很小,每次查询平均扫描行数接近1。而倒叙方式毕竟使用的还是前缀的方式,肯定回增加扫描行数。