利用索引覆盖95%的数据

获得字符串列索引前缀长度

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

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

比如name字段记录的是人的名字，定义为：

name varchar(128) NOT NULL DEFAULT '' COMMENT '姓名'

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

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

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

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

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

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

select min(cc.l) as ninty_five_percentfrom (select length(filename) as l, count(distinct filename) as c, @ccount := @ccount + count(distinct filename) as tfrom filenames, (select @ccount := 0) as foogroup by length(filename)) as cc where cc.t >= (@ccount*0.95) order by cc.t;

例如：

现在有一个filename表

[5.6.10-log root@woqu-mysql3 /root root@127.0.0.1:test 15:31:09>show create table filenames\G*************************** 1. row ***************************Table: filenamesCreate Table: CREATE TABLE `filenames` (`id` int(11) NOT NULL,`filename` varchar(255) NOT NULL,PRIMARY KEY (`id`),KEY `filename` (`filename`)span>) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)

该表有10条数据如下：

[5.6.10-log root@woqu-mysql3 /root root@127.0.0.1:test 15:30:43>select * from filenames;+----+-----------------------+| id | filename |+----+-----------------------+| 1 | sdfsdf || 4 | sdfsdsf32sf || 3 | sdfsdsfds3fsf || 8 | sdfsf || 2 | sdfsfsf || 5 | sdsf2f32sf || 9 | sdsf2sffff || 10 | sdsf2sffffdsjjjjjf || 7 | sdsf2sfffffsdfsdfsfsf || 6 | sdsf2sffffsf |+----+-----------------------+10 rows in set (0.00 sec)

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

[5.6.10-log root@woqu-mysql3 /root root@127.0.0.1:test 15:31:38>select min(cc.l) as ninty_five_percent-> from (-> select length(filename) as l, count(distinct filename) as c, @ccount := @ccount + count(distinct filename) as t, id,filename-> from filenames, (select @ccount := 0) as foo-> group by length(filename)-> ) as cc where cc.t >= (@ccount*0.95) order by cc.t;+--------------------+| ninty_five_percent |+--------------------+| 21 |+--------------------+1 row in set (0.00 sec)

子查询查询结果如下：

[5.6.10-log root@woqu-mysql3 /root root@127.0.0.1:test 15:44:37>select length(filename) as l, count(distinct filename) as c, @ccount := @ccount + count(distinct filename) as t, id, filename-> from filenames, (select @ccount := 0) as foo-> group by length(filename);+----+---+------+----+-----------------------+| l | c | t | id | filename |+----+---+------+----+-----------------------+| 5 | 1 | 1 | 8 | sdfsf || 6 | 1 | 2 | 1 | sdfsdf || 7 | 1 | 3 | 2 | sdfsfsf || 10 | 2 | 5 | 9 | sdsf2sffff || 11 | 1 | 6 | 4 | sdfsdsf32sf || 12 | 1 | 7 | 6 | sdsf2sffffsf || 13 | 1 | 8 | 3 | sdfsdsfds3fsf || 18 | 1 | 9 | 10 | sdsf2sffffdsjjjjjf || 21 | 1 | 10 | 7 | sdsf2sfffffsdfsdfsfsf |+----+---+------+----+-----------------------+9 rows in set (0.00 sec)

按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

from：http://www.woqutech.com/?p=1325