Mysql 索引系列（四）：前缀索引

什么是前缀索引？

前缀索引也叫局部索引，比如给身份证的前 10 位添加索引，类似这种给某列部分信息添加索引的方式叫做前缀索引。

前缀索引能有效减小索引文件的大小，让每个索引页可以保存更多的索引值，从而提高了索引查询的速度。但前缀索引也有它的缺点，不能在 order by 或者 group by 中触发前缀索引，也不能把它们用于覆盖索引。

当字符串本身可能比较长，而且前几个字符就开始不相同，适合使用前缀索引；

举例：如果有一个字段是一串 md5 加密后的字符串，那么就适合建前缀索引。

建立前缀索引的语法为：

ALTER TABLE table_name ADD KEY(column_name(prefix_length));

建立索引之前，我们要关注字段的区分度，区分度越大，性能越高，意味着重复的值就越少。

这里最关键的参数就是 prefix_length，这个值需要根据实际表的内容，得到合适的索引选择性（Index Selectivity）。索引选择性就是不重复的个数与总个数的比值。

1 2	select 1.0count(distinct column_name)/count() from table_name

比如我们现在有个Employee表，其中有个FirstName字段，是varchar(50)的，我们查询该字段的索引选择性：

1 2	select 1.0count(distinct FirstName)/count() from Employee

得到结果0.7500，然后我们希望对FirstName建立前缀索引，希望前缀索引的选择性能够尽量贴近于对整个字段建立索引时的选择性。我们先看看3个字符，如何：

1 2	select 1.0count(distinct left(FirstName,3))/count() from Employee

得到的结果是0.58784，好像差距有点大，我们再试一试4个字符呢：

1 2	select 1.0count(distinct left(FirstName,4))/count() from Employee

得到0.68919，已经提升了很多，再试一试5个字符，得到的结果是0.72297，这个结果与0.75已经很接近了，所以我们这里认为前缀长度5是一个合适的取值。所以我们可以为FirstName建立前缀索引：

1	alter table test.Employee add key(FirstName(5))

建立前缀索引后查询语句并不需要更改，如果我们要查询所有FirstName为Devin的Employee，那么SQL仍然写成：

1
2
3

select *
from Employee e
where e.FirstName='Devin';