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 字段索引,查询性能稳定,有额外的存储和计算消耗,跟第三种方式一样,都不支持范围扫描