前缀索引. 索引选择性

前缀索引/索引选择性

如果有很长的字符,如 blog, text, 如果索引这个列,索引会很大,而且 MySQL 也不支持对这种列进行索引.所以需要前缀索引,对这些索引的前面几个字符进行索引.

为了让这种索引效率最高,也就是查询出来的结果最真实,先要确定需要索引前面几个字符.示例:

select count(*) as cnt, city from my_table group by city order by cnt desc limit 10;

该语句查询访问量排前十的城市及它的访问量.假定 city 字段是很长的字符串.

然后再用语句:

select count(*) as cnt, left(city, 3) as pref from my_table group by city order by cnt desc limit 10;

group by 的内容变成了 left(city, 3) 当然,该语句查询了的结果可能和前面不一样. left 的第二个参数越长,肯定越准确.多试几次,索引选择性最高的数值.

索引的选择性是指不重复的索引列数和数据表的记录总数的比值.索引的选择性越高则查询效率越高.因为它可以让 MySQL 在查找时过滤掉更多的行,唯一索引的选择性是 1, 这是最好的索引选择性,性能也是最好的.

计算方式可能是: select count(distinct city) / count(*) from my_table;

上面找出最高选择性的办法:

select count(distinct left(city, 3)) / count(*) as sel3, 

count(distinct left(city, 4)) / count(*) as sel4 ,

count(distinct left(city, 5)) / count(*) as sel5 ,

count(distinct left(city, 6)) / count(*) as sel6 ,

count(distinct left(city, 7)) / count(*) as sel7 ,

from my_table

结果:

sel3 sel4 sel5 sel6 sel7

0.0239 0.0293 0.0305 0.0309 0.0310

看出,从 sel7 开始,上升的幅度已经很少了,如果再往后增成 8, 9 一样.所以,选择 7 即可.有些时候为了效率考虑,可以选择更低的 5, 6

我们就可以创建前缀索引:

alter table my_table add key(city(7));

前缀索引的缺点是, MySQL 无法使用前缀索引做 order by, group by.