例如,select actor_id from actor where actor_id+1=5;
这个查询无法使用actor_id列的索引。
下面是另一个常见的错误:
select ... where TO_DATS(CURRENT_DATA) - TO_DAYS(date_col ) <=10;
列不能是函数的一部分。
1.创建模拟数据 数据分布不是真实分布,对练习来说并不重要。我们以城市出现的次数决定该城市的常见与否,出现次数最多表示该城市最常见。 2.找到最常见的
select count(*) as cnt,city from city_demo group by city order by cnt desc;
3.查找最频繁出现的城市前缀,先从1个前缀字幕开始:
select count(*) as cnt , left(city,1) as pref from city_demo group by pref order by cnt desc ;
每个前缀都比原来的城市出现的次数更多,因此唯一前缀比唯一城市要少
4.增加前缀长度
直到这个前缀的选择性接近完整列的选择性。
两个前缀:
三个前缀:
四个前缀:
发现前缀为3的时候,选择性最接近完整列,所以说以列的前三个字符来做索引是最合适的。索引体积小且查询速度快。
计算合适的前缀长度的另一个办法就是计算完整列的选择性,并使前缀的选择性接近于完整列的选择性。
如:
select count(distinct city)/count(*) from city_demo
完整列的选择性是0.15,可以在一个查询中针对不同前缀长度进行计算
select count(distinct city)/count(*) as orginal,
count(distinct left(city,1))/count(*) as sel1,
count(distinct left(city,2))/count(*) as sel2,
count(distinct left(city,3))/count(*) as sel3,
count(distinct left(city,4))/count(*) as sel4,
count(distinct left(city,5))/count(*) as sel5
from city_demo
前缀为3的时候最接近完整列的选择性,再增加前缀长度,选择性提升的幅度已经很小了。
alter table city_demo add key (city(3));
前缀索引是一种能使索引更小、更快的有效办法,但另一方面也有缺点:mysql无法使用前缀索引做order by和group by,也无法使用前缀索引做覆盖扫描。
存储网站会话时,需要在一个很长的十六进制字符串上创建索引。此时如果采用长度为8的前缀索引通常能显著地提升性能,且对上层应用完全透明。
字符串反转后做前缀索引