利用索引覆盖95%的数据

大家都知道索引可以极大的加快查询的速度,但是索引也是有代价的,它需要消耗额外的存储空间,对数据的更新操作也涉及到对应索引的一致性更新。

而对某些特殊的字符串索引来说,我们并不一定要整个字段长度都索引起来,只需要索引一定的前缀大小就可以达到快速查找指定数据的目的。

比如name字段记录的是人的名字,定义为:

之所以定义为这么长,是为了适应国外超长的名字。有兴趣的同学可以google一下“世界最长人名”。

但是正常的人名其实并不长,所以我们只需要索引前几个就行了。

所以如何确定索引的长度以保证更好的选择性,前缀性也是很多人研究的课题和论文题目。如果你把整个列都索引起来,对应的选择性当然非常高,但是浪费的空间却非常多;如果只索引列的部分前缀,那么有可能选择性比较差,达不到通过索引提高查询速度的目的。

我们这里不分析也不介绍现有的确定索引前缀长度的方法:) 仅仅介绍一种获得索引前缀长度的方法,它能保证95%的列数据都能索引起来。

按照@Dixon的理论,一般来说,你如果索引了95%的字符串前缀,它就可以满足大部分情况过滤需求。

对应的我们可以采用这样的sql来获得我们需要的95%字符前缀长度:

例如:
现在有一个filename表

该表有10条数据如下:

我们按照dixon的要求,需要给filename列找到一个最佳的前缀长度,就可以这样来查询:

子查询查询结果如下:

按length(filename)排序,其中l表示字符串列filename的长度,c表示filename列中长度为l的行数,t表示filename列中长度小于等于l的行数。@count表示filenames表的总行数。

其中,id和filename列是我特别加进去的,为了更直观的展示该子查询要表达的内容,这样的话,在外面的循环中,我们根据t来排序,找到大于95% * @count的第一个filename列的长度,也就是Dixon所说的95分为数(和中位数对应)。

参考:

http://www.jonathanlevin.co.uk/2011/11/get-95-for-your-index-prefix.html

http://tech.it168.com/a2011/0711/1216/000001216087_11.shtml

http://en.wikipedia.org/wiki/Dixon’s_Q_test

Leave a Reply