如何建立合适的索引
什么叫做合适的索引?
笔者认为,在满足基本业务需求的条件下,尽可能的节约索引带来的时间和空间成本,或者是当时间与空间互斥时,选择一种最适合自己的索引方案。其实就是寻找最优解的过程。
字符串字段索引
创建完整索引
将字段完整字符串放进索引。
这种方法简单粗暴,就是有一个弊端:当字符串过长时可能比较占用空间。
创建前缀索引
顾名思义,字符串截取头部一定长度放进索引。
例如有一个邮箱字段 email
其中一行的值为 juneone9@163.com
,如果对 email
创建前缀索引,指定长度为 8,那么该行在索引中的值就为juneone9
。
时间换空间
前缀索引是一种以时间换空间的策略。既然减小了索引的长度,势必也会降低索引的区分度,例如 juneone9@qq.com
、juneone9@163.com
、juneone9@gmail.com
这几个值在前缀索引里都为juneone9
。
执行select id,name,email from t where email= 'juneone9@163.com';
时,完整索引只需要扫描一行即可得到结果,而前缀索引可能需要增加额外的记录扫描次数,造成时间消耗。
虽然如此,如果能保证截取的前缀依然有足够的区分度,那么将节省大量空间的同时,保证查询的执行效率。这就需要好好把握前缀索引的指定长度。
那么如何选择合适的前缀长度呢?
- 首先确定需要建立前缀索引列上的区分度,也就是不同值的个数
- 确定一个可以区分度损失的比例,例如5%。那么就要求前缀索引的区分度要在 (95% * 列总区分度) 以上
- 查询不同前缀长度的索引区分度
# 查询列总区分度
SELECT
count( DISTINCT email ) AS L
FROM t;
# 查询不同前缀索引长度的区分度
SELECT
count( DISTINCT LEFT ( email, 1 ) ) AS L1,
count( DISTINCT LEFT ( email, 2 ) ) AS L2,
count( DISTINCT LEFT ( email, 3 ) ) AS L3,
count( DISTINCT LEFT ( email, 4 ) ) AS L4
FROM t;
对覆盖索引的影响
使用前缀索引将无法利用覆盖索引机制来优化查询。
即使前缀索引包括了所有的字符串,依然需要回表到主键索引查询,因为系统不确定是否有截断完整的信息
倒序存储和Hash字段
当字符串首部具有足够区分度创建前缀索引,才有可观的收益,例如邮箱等;但如果是身份证字符串,一般前六位是地址码,同一县的人前六位是一样的,这时可能需要很长的前缀才具有足够的区分度。
遇到这种情况,就不适合直接创建前缀索引了,有什么解决方法吗? 一般有两种思路借鉴。
- 倒序存储
- 像身份证这种,前六位可能没什么区分度,但是后六位可是有很高的区分度,如果把它进行倒序存储,倒序的身份证建立前缀索引即可。
- 每次查询时都要通过 翻转函数
reverse
处理
- 增加 hash 字段
- 在表上增加一个整数字段,保存身份证的 hash 码,并对这个字段建立索引
- 每次查询需要通过 hash函数
crc32()
计算出身份证的 hash 码,作为条件查询。另外,不同身份证可能计算得到的 hash 码一致,这就需要条件加入 身份证精确匹配
两种方法的异同点:
- 相同点:
- 两种方式都不支持范围查询。
- 不同点:
- 额外占用空间:两者差不多。倒序存储需要建立前缀索引;增加 hash字段是一个整型字段,它的索引长度变为 4个字节,相比前缀索引应该小很多。
- CPU消耗:倒序存储读写调用 reverse 函数;hash 字段方式需要调用 crc32() 函数。一般来说前者消耗更小。
- 查询效率:倒序存储还是基于前缀索引;hash 字段更稳定,哈希冲突概率非常小。后者的扫描行数应该会小于前者。