MYSQL45讲学习笔记

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存储，可以节省索引空间，保证索引区分度，但是无法范围查询，有额外的存储和计算消耗。