MySQL实战之 如何为长字节的字段建立索引

---

前言

如果需要建立索引的字段数据字节数过长 , 那么在查找过程中会消耗一部分不必要的性能 , 因此,本文介绍如何合理地为长字节字段建立索引

---

提示：以下是本篇文章正文内容，下面案例可供参考

建立索引方法

1.前缀索引

鉴于MySQL支持前缀索引 , 因此,我们可以为长字段建立一个前缀索引 . 语法如下:

alter table T add index index01(email(6));

以上SQL语句即为前缀索引 , 设想一下 , 如果是QQ邮箱 , 前六位就是QQ号的一部分 , 占用空间小

但是如果不是QQ邮箱呢? 如果邮箱的前6位是字符串 , 那么就极有可能造成重复的情况. 比方说zhangsan@163.com和zhangsanfeng@163.com ,前六位字符都是zhangs , 此时反而会增加检索次数.

那么如何选择前缀位数呢? : 这取决于索引的区分度 , 如果索引的区分度大,前缀位数可以少取几位

这里有一个算法可供参考

先计算出索引的区分度
select count(distinct 字段名) as dis from T\
然后依次尝试不同的前缀长度
select 
	count(distinct left(字段名,4)) as dis4,
	count(distinct left(字段名,5)) as dis5,
	count(distinct left(字段名,6)) as dis6,
	count(distinct left(字段名,7)) as dis7,
from T
由于使用前缀索引必然导致区分度下降 , 因此可以设定一个可接受范围,如5%
然后计算出dis4~dis7中不低于dis*0.95的值,选中最小的长度做为前缀索引长度

注: 前缀索引与覆盖索引不能同时使用

2.倒序存储

前面我们都没有使用身份证作为过索引,而采用倒叙存储将身份证后六位作为索引 , 就能提供较大的区分度

3.哈希字段

使用hash字段需要在表中再创建一个整数字段 , 然后对某字段进行哈希(crc32())运算 , 得到结果.
这么做既可以减小字段长度, 又可以提高区分度. 但是进行hash过后,就只能对字段进行等值查找了

alter table T add id_crc int unsigned ,add index(id_crc) //建立hash字段
select field from T where id_crc = crc32('目标字段')

---