mysql-7.字符串字段索引的优化

对于字符串字段建立索引，字段越长那么这个字段索引树占用的磁盘空间就越大，相同的数据页能放下的索引值就越少，搜索的效率也就会越低。而且伴随着表数据量的不断增大，会导致这颗索引树占用磁盘空间最后会非常大。这就需要我们根据实际业务场景来优化这个字符串字段的索引了。

前缀索引

例如现在有张员工表：

mysql> create table t_staff(
id bigint unsigned primary key,
email varchar(64), -- 10位员工工号@shenzhenxx.com
username varchar(10),
password varchar(16)
... 
)engine=innodb; 

邮箱格式：10位员工工号@shenzhenxx.com
然后需要用邮箱进行登录操作，所以我们一般会选择email字段建立索引，来加快查询效率。
建立索引我们可以如下两种方式建立。

alter table t_staff add index idx_email1(email);
或者
alter table t_staff add index idx_email2(email(10));

第一个语句创建的 idx_email1索引里面，包含了每个记录的整个字符串；而第二个语句创建的 idx_email2索引里面，对于每个记录都是只取前 10个字节。

优缺点：
这两种不同的定义在数据结构和存储上是不一样的，由于 email(10)这个索引结构中每个邮箱字段都只取前 10 个字节（即：10位工号）。
所以占用的空间会更小，这就是使用前缀索引的优势。所以当我们如果需要考虑节省磁盘空间时候，我们可以使用前缀索引的方式来进行优化。
使用前缀索引就用不上覆盖索引对查询性能的优化了（缺点），这也是你在选择是否使用前缀索引时需要考虑的一个因素。

---

对于前缀区分度不高的情况，建立前缀索引是不合适的。例如，下表中的id_card身份证号字段

create table user(
id bigint unsigned primary key,
id_card varchar(64), --身份证号
name varchar(32)
)

例如我们建立一个idx_id_card（id_card(6)） 这样一个前缀索引，然后执行下面查询语句：

select id,id_card,name   from user where id_card='421123199910111234'

对于这个查询的执行流程是：

1. 从idx_id_card索引树找到满足索引值是421123的记录，然后找到第一个id（叶子节点存储主键值）;
2. 回表，到主键上查到主键值是 这个id 的行，判断出 id_card的值是不是421123199910111234。不是，这行记录丢弃；是，将这行记录加入结果集。
3. 重复上一步，直到在 idx_id_card上取到的值不是421123时，循环结束

结论： 在这样的一个执行流程下，大家可以想一下，由于前缀区分度不高，前缀是421123值非常多，最后导致一个简单的查询语句，需要不停回表查询，非常影响效率。所以这个时候建立前缀索引是不合适的。

那么我们有其他方法解决嘛？当然是有的，使用 hash 字段

hash字段

你可以在表上再创建一个整数字段，来保存身份证的校验码，同时在这个字段上创建索引。

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

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

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

这样，索引的长度变成了 4 个字节，比原来小了很多。算然多增加了一个字段，但是从整体上看，还是节约了存储的消耗。