「Mysql索引原理（四）」单列索引

单列索引

独立的列

例如，select actor_id from actor where actor_id+1=5;

这个查询无法使用actor_id列的索引。

下面是另一个常见的错误：

select ... where TO_DATS(CURRENT_DATA) - TO_DAYS(date_col ) <=10;

列不能是函数的一部分。

前缀索引和索引选择性

1. 索引的选择性：不重复的索引值（也称为基数）和数据表的记录总数（#T）的比值，范围从1/T到1之间。选择性越高则查询效率越高，因为选择性高的索引可以让Mysql在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
2. 对于BLOB、TEXT或者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度。
3. 前缀的“基数”应该接近于完整列的“基数”。

如何选取适合的前缀长度？

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的前缀索引通常能显著地提升性能，且对上层应用完全透明。

后缀索引

字符串反转后做前缀索引