前缀索引
前缀索引
有时候需要索引很长的字符串,这会让索引变的大且慢
通常情况下可以使用某个列开始的部分字符串,这样大大的节约索引空间,从而提高索引效率,但这会降低索引的选择性
索引的选择性是指不重复的索引值和数据表记录总数的比值,范围从1/#T到1之间
索引的选择性越高则查询效率越高,因为选择性更高的索引可以让mysql在查找的时候过滤掉更多的行
一般情况下某个列前缀的选择性也是足够高的,足以满足查询的性能,但是对应BLOB,TEXT,VARCHAR类型的列,必须要使用前缀索引
因为mysql不允许索引这些列的完整长度,使用该方法的诀窍在于要选择足够长的前缀以保证较高的选择性,通过又不能太长
创建数据表
create table citydemo(city varchar(50) not null); insert into citydemo(city) select city from city;
重复执行5次下面的sql语句
insert into citydemo(city) select city from citydemo;
更新城市表的名称
update citydemo set city=(select city from city order by rand() limit 1);
查找最常见的城市列表,发现每个值都出现45-65次
select count(*) as cnt,city from citydemo group by city order by cnt desc limit 10;
查找最频繁出现的城市前缀,先从3个前缀字母开始,发现比原来出现的次数更多,可以分别截取多个字符查看城市出现的次数
select count(*) as cnt,left(city,3) as pref from citydemo group by pref order by cnt desc limit 10;
可以看到当前缀长度到达7之后,再增加前缀长度,选择性提升的幅度已经很小了
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, count(distinct left(city,8))/count() as sel8 from citydemo;
计算完成之后可以创建前缀索引 alter table citydemo add key(city(7));
- 前缀索引是一种能使索引更小更快的有效方法
缺点
mysql无法使用前缀索引做order by 和 group by
论读书
睁开眼,书在面前 闭上眼,书在心里
睁开眼,书在面前 闭上眼,书在心里