11.如何给字符串字段加索引
在日常开发中,我们有时会用到字符串字段来做条件查询,如果字符串字段上没有索引,则会走全表扫描。
但是如果建立一个全字段索引,可能会导致索引占用的空间较大,消耗CPU资源,影响性能,那么我们应该如何给字符串字段加索引?
1.使用前缀索引
例如现在有一个用户表里有邮箱字段:email,要给这个字段加索引,我们应该怎么加?
对于区分度越大的字段,越能体现索引的优势,因为邮箱字段后面的"@xxxx.com"区分度不大,所以我们可以建立一个前缀索引,例如:
alter table t add index idx_email(email(6));
这样既保证了索引的区分度, 也节省了索引的空间。
但是前缀索引可能导致扫描行数增加,并且导致无法使用覆盖索引。
假设现在有这样一条SQL语句,
select id, name ,email from SUser where email = 'zhangssxyz@xxx.com';
然后我们在emial字段上建立两种索引来分别分析他们的性能
alter table SUser add index index1(email);
或
alter table SUser add index index2(email(6));
两种索引的存储结构如图所示:
如果emial字段上创建了索引index1,那么它的执行顺序是这样的:
1.找到zhangssxyz@xxx.com的ID2
2.根据ID2找到全部行数据
3.判断下一行不是zhangssxyz@xxx.com,结束流程。
整个过程回表一次,我们认为扫描了一行。
如果emial字段上创建了index2,那么它的执行顺序是这样的:
1.在idx_email索引上找到 "zhangs"的主键ID1,
2.根据ID1找到email的全部字段,判断email不是"zhangssxyz@xxx.com",
3.判断下一行的emial是不是"zhangssxyz@xxx.com",
4.重复上述流程,直到找到第一个emial不是"zhangssxyz@xxx.com",
整个过程回表四次,扫描了四行。
使用了前缀索引之后,必须回表去判断email的值,即使创建的索引为email(18),即全部字段,MYSQL也会回表去判断email的值,因为MYSQL不知道前缀索引的完整度。
2.倒序存储
假设现在有一个身份信息表,有一个字段是身份证号,前六位代表地区,中间八位代表出生日期,最后四位没有规律(此处的意思是区分度较大),如果我们要建立前缀索引,并且要保证足够的区分度,那么我们至少要建立14位的前缀索引,但是这样会导致索引占用空间变大。
因为身份证最后四位区分度较大,所以我们可以采用倒序存储,SQL语句可以这样写,
select field_list from t where id_card = reverse('input_id_card_string');
存储的时候也是用倒序来存,当然我们不要忘记使用count(distinct)来验证区分度。
3.hash存储
我们还可以增加一个字段,用来存储身份证的校验码,同时在新的字段上创建索引。
alter table t add id_card_crc int unsigned, add index(id_card_crc);
在存储身份证时,我们可以对身份证使用crc32()函数来得到身份证的校验码,其实就是一个hash运算,并将其一并存入表中。
当查询身份证时,我们可以这样查询:
select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'
因为crc32函数可能发生碰撞,所以我们在后面加上了 id_card='input_id_card_string' 的条件。
如果crc32函数碰撞比较多,还可以采用crc64()函数。
小结
本节主要学习了给字符串加索引的几种方式以及他们的优缺点:
1.前缀索引,可以节省索引空间,同时保证区分度,但是会增加扫描行数,无法使用覆盖索引
2.先倒序存储,再建立前缀索引,可以保证索引区分度,但是无法范围查询
3.hash存储,可以节省索引空间,保证索引区分度,但是无法范围查询,有额外的存储和计算消耗。