MYSQL索引之高性能的索引策略

1.独立的列

通常会有一些不正确的使用索引， 或者使得mysql 无法使用已有的索引。如果查询中的列不是独立的，则MySql就不会使用索引。“独立的列”是指索引列不能是表达的一部分，也不能是函数的参数。
例如： 下面这个查询无法使用actor_id列的索引：

select actor_id from actor where actor_id +1 = 5

很容易能够看出来其实是等价于actor_id = 4,但是MySql无法自动解析这个方程式。另一种常见错误:
select * from actor where TO_DAYS(cuurent_date) - TO_DAYS(date_col) <=10

2.前缀索引和索引选择性

有时候需要索引很长的字符列，这会让索引变得大且慢。通常可以索引开始的部分字符，这样可以大大的节约索引空间，从而提高索引效率。但这样也会降低索引的选拔性。索引的选择性是指，不重复的索引值(也称为基数)和数据表的记录总数(#T)的比值，范围从1/#T到1之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySql在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
一般情况下某个列前缀的选择性也是足够高的，足以满足查询性能。对于BLOB、TEXT或者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度。
诀窍在于要选择足够长的前缀以保证较高的选择性，同时又不能太长（以便节约空间）、前缀应该足够长，以使得前缀索引的选择性接近于索引整个列。换句话说，前缀的“基数”应该接近于完整列的“基数”。
为了决定前缀的合适长度，需要找到最常见的值 的列表，然后和最常见的前缀列表进行比较。我们创建一个city表来进行演示。

create table city_demo(city VARHCAR(50) NOT  NULL)

随机在这个表里面添加一些数据。
首先我们找到最常见的城市列表。

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

cnt	city
65	London
49	Hiroshima
48	Teboksary
46	Cabuyao

注意到，上面每个值都出现了45-65次。现在查询到最频繁出现 的城市列表，先从3个前缀字母开始。

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

cnt	pref
483	San
195	Cha
177	Tan
167	Sou
163	al-
163	Sal
146	Shi

每个前缀都比原来的城市出现的次数更多，因此唯一前缀比唯一 城市要少得多。然后我们增加前缀长度，走到这个前缀的选择性接近完整列。经过实验后发现前缀长度为7时比较合适：

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

计算合适的前缀长度 的另外一个方法就是计算完整列的选择性，并使前缀的选择性接近于完整列的选择性。下面显示如何计算完整列的选择性：

select count(distinct city)/ count(*) from city_demo;

count(distinct city )/count(*)
0.0312

通常来说，这个例子如果前缀的选择性能够接近于0.031，基本上就可用了。可以在一个查询中针对不同前缀长度进行计算，这对于大表非常有用。下面给出了如何在同一个查询中计算不同的前缀长度的选择性：

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 city_demo;

sel3	sel4	sel5	sel6	sel7
0.0239	0.0293	0.0305	0.0309	0.0310

查询显示当前缀长度到达7的时候 ，再增加前缀长度，选择性提升的幅度已经很小了。
只看平均选择性是不够的，也有例外的情况，需要考虑最坏情况下的选择性。平均选择性会让你认为前缀长度 为4或者5的索引已经足够了，但如果数据分布不均匀，可能就会有陷阱。如果观察前缀0 4的最常出现城市的次数，可以看到明显不均匀：

    select count(*) as cnt,left(city,4) as pref from city_demo group by pref order by cnt desc limit 5;

cnt	pref
205	san
200	Sant
135	Sout
104	Chan
91	Toul

如果前缀是4个字节，则最常出现的前缀的出现次数比最常出现的城市的出现次数要大很多。即这些值的选择性比平均选择性要低。如果有比这个随机生成的示便更真实的数据，就更有可能看到这种现象。例如在真实的城市名上建一个长度 为4的前缀索引，对于“san”和“new” 开头的城市的选择性就会非常糟糕，因为很多城市都以这两个词开头。
注： 这里说的是美国城市，因为美国之前是西班牙和英国的殖民地，西班牙人起城市民经常以神来起，因为就是“san”开头， 而英国就是以”new“+英国城市名称来给地方命名。因为就会出现这种情况了。
在上面的示例中，已经找到了合适的前缀长度，下面演示一下如何创建前缀索引：

alter table city_demo add key(city(7))

前缀索引是一种能使索引更小，更快的有效办法，但另一方面也有其缺点:MySQL无法使用前缀索引做order by 和group by，也无法使用前缀索引做覆盖扫描。
一个常见的场景是针对很长的十六进制唯一的ID使用前缀索引。