前缀索引，一种优化索引大小的解决方案

今天在读一篇关于数据库索引介绍的文章时，该文章提到了前缀索引，对于我这个搞数据库应用开发那么多年的人来说，这个词还真是一个新词，没用过。于是打算研究一番。

前缀索引似乎是MySQL中的一个概念，在SQL Server和Oracle中没提出这个概念。于是就安装了一个MySQL来做实验，搞清楚前缀索引。

前缀索引说白了就是对文本的前几个字符（具体是几个字符在建立索引时指定）建立索引，这样建立起来的索引更小，所以查询更快。有点相当于Oracle中对字段使用Left函数，建立函数索引，只不过MySQL的这个前缀索引在查询时是内部自动完成匹配的，并不需要使用left函数。

别的文章中提到：

MySQL 前缀索引能有效减小索引文件的大小，提高索引的速度。但是前缀索引也有它的坏处：MySQL 不能在 ORDER BY 或 GROUP BY 中使用前缀索引，也不能把它们用作覆盖索引(Covering Index)。

建立前缀索引的语法为：

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

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

select 1.0*count(distinct column_name)/count(*)
from table_name

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

select 1.0*count(distinct FirstName)/count(*)
from Employee

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

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

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

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

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

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

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

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

下面总结一下什么情况下使用前缀索引：

• 字符串列(varchar,char,text等)，需要进行全字段匹配或者前匹配。也就是=‘xxx’ 或者 like ‘xxx%'
• 字符串本身可能比较长，而且前几个字符就开始不相同。比如我们对中国人的姓名使用前缀索引就没啥意义，因为中国人名字都很短，另外对收件地址使用前缀索引也不是很实用，因为一方面收件地址一般都是以XX省开头，也就是说前几个字符都是差不多的，而且收件地址进行检索一般都是like ’%xxx%’，不会用到前匹配。相反对外国人的姓名可以使用前缀索引，因为其字符较长，而且前几个字符的选择性比较高。同样电子邮件也是一个可以使用前缀索引的字段。
• 前一半字符的索引选择性就已经接近于全字段的索引选择性。如果整个字段的长度为20，索引选择性为0.9，而我们对前10个字符建立前缀索引其选择性也只有0.5，那么我们需要继续加大前缀字符的长度，但是这个时候前缀索引的优势已经不明显，没有太大的建前缀索引的必要了。