mysql 如何为字符串合理创建索引

Posted on 2021-11-20 In mysql

对于一般长度的字符串，用整个字符串直接作为索引即可，但对于比较长的字符串，比如email，身份证号如果直接作为索引，会占用较大的磁盘空间

前缀索引

可以为较长的字符串设置前缀索引，缩短索引字段的长度，减少占用磁盘的空间

alter table users add index index1(email);

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

索引 index1 包含了整个字符串，而 index2 只包含了前6个字符串，比 index1 占用更少的空间。那么如何定义前缀长度呢？如果太短，索引的区分度就会不高，增加额外的扫描次数，查询效率下降，太长又不能节省空间。可以用下面的方法进行判断，选择最接近1的，同时长度最短的来作为前缀。

前缀索引的长度

首先计算出这个列上有多少个不同的值(计为total)

select count(distinct email) as total from users;

然后取不同长度的前缀，计算有多少个不同的值，分别除以 total，选择最接近1的且前缀较短的

select 
  count(distinct left(email,4)）/ total as L4,
  count(distinct left(email,5)）/ total as L5,
  count(distinct left(email,6)）/ total as L6,
  count(distinct left(email,7)）/ total as L7,
from users;

假设结果为： L4 40%，L5 90%， L6 96% ，L7 96%。而你只接受大于95%的区分度，L6/L7等符合，那么选择前缀为6的最为适合。

增加扫描次数

假设users表有如下记录，分别建立索引 index1(email)，index2(email,5)

id,  email,              ,username, adders
id1, xiaoming123@xyz.com ,省略
id2, xiaoming456@xyz.com ,省略
id3, xiaoming789@xyz.com ,省略
id4, xiaomingABC@xyz.com ,省略
id5, zhanghao123@xyz.com    ,省略
id6, zhanghao456@xyz.com    ,省略

索引 index1的页节点数据

(xiaoming123@xyz.com,id1),(xiaoming456@xyz.com,id2),(xiaoming789@xyz.com,id3),(xiaomingABC@xyz.com,id4),(zhang123@xyz.com,id5),(zhang456@xyz.com,id6)

索引 index2的页节点数据

(xiaom,id1),(xiaom,id2),(xiaom,id3),(xiaom,id4),(zhang,id5),(zhang,id6)

可以看到，前缀索引占用更少的数据空间

索引查询过程

现查询email为xiaoming456@xyz.com'的用户信息

select * from users where email='xiaoming456@xyz.com';

index1索引查询过程

搜索index1索引树，找到等于 xiaoming456@xyz.com 的记录，取出主键的值 id2
根据主键值 id2 回表查询，把记录放入返回结果集中
再接着向右移动，发现记录不符，返回，查询结束，把结果集返回客户端

index2索引查询过程

搜索index2索引树，找到等于 xiaom 的记录(取前5位进行查询)，取出主键的值 id1
根据主键值 id1 回表查询，发现email值不等于xiaoming456@xyz.com
再接着向右移动，发现等于xiaom，取出主键的值 id2
根据主键值 id2 回表查询，发现email值等于xiaoming456@xyz.com，放入返回结果集
重复以上过程，直到遇到zhang时，查询结束

使用前缀索引 index2 一共需要查找4次，增加了扫描的次数。但如果把前缀索引设置为email(9)，也只需要查找一次，因为等于xiaoming4只有一条，找到后查询也结束了。

通过选择适当的前缀索引的长度，即节省空间，查询成本也不会太高

覆盖索引无效

同样是查询select id, email from users where email='xiaoming456@xyz.com';

如果在 email 上建立普通索引，在找到记录后，由于索引包含了id的值，不用回表，直接返回结果即可
如果在 email 上建立前缀索引，在找到记录后，由于索引信息不完整，即使包含了id的值，也需要回表查询是否与email的完整值相匹配

这样就导致了在前缀索引上，无法使用覆盖索引对查询性能的优化

Hash字段

比如对身份证进行查询，其长度为18位，直接建立索引会占用较多的空间，如果使用前缀索引只取前几位但相同的概率很大，取太长又不能节省空间，此时可以添加一个字段，用来存放身份证的 hash 值，并为这个hash字段建立索引

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

索引的长度变成了 4 个字节，就算算上hash字段本身占用的空间，也要比原来小了很多。由于hash值存在冲突，在查询时还要加上身份证字段，确保精确匹配以取到正确的记录，这样就可以即节省空间，又高效的查询了

select * from t where idcard_hash=hash_algorithm('input_id_card_string') and id_card='input_id_card_string'

hash字段的缺点

不能进行范围查询
使用hash函数会消耗CPU

这里其实还有一个思路，把身份证倒序存储，这样就不用额外再建立字段，同时可以使用前缀索引，但至少要用前8位来建立前缀索引，即8字节，其占用空间和使用hash字段就差不多了，因为是前缀索引，必定要回表查询，增加了扫描次数，查询性能也有没hash字段稳定，况且8位应该是不够的，占用空间肯定要比使用hash字段要大。

小结

直接创建完整索引，这样可能比较占用空间
创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引
倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题
创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描