高性能的MySQL（5）索引策略

一、索引的优点

1、索引可以大大减少服务器需要扫描的数据量

2、索引可以帮助服务器避免排序和临时表。

3、索引可以将随即I/O变为顺序I/O

二、索引策略

1、独立的列

独立的列是指索引列不能是表达式的一部分，也不能是函数参数。例如：

一个有500W条记录的表，id是主键。

所以要始终将索引列单独放在比较符号的一侧。

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

对于比较大的列，通常可以索引开始的部分字符，这样可以节约索引空间，提高索引率。但是也会降低索引的选择性。

索引的选择性是指，不重复的索引值（基数）和表的记录总数的比值，索引的选择性越高查询效率就越高，因为可以在查找时过滤掉更多的行，唯一索引的选择性是1，性能是最好的。

如何来选择一个合适的长度，让前缀的基数接近完整列的基数，一般我们有2中方法：

a、为了决定前缀的合适长度，需要找到最常见的值的列表，然后和最常见的前缀列表进行比较。

举例：有一张100W记录的表，要对name添加前缀索引

首先找到最常见的name列表

1	`select` `count(*)` `as` `cnt,name` `from` `vemp` `group` `by` `name` `order` `by` `cnt` `desc` `limit 10;`

每个值出现的次数在12-15之间，现在开始测试前缀从3个前缀开始

可以看到每个前缀出现的次数明显增多了，也就是唯一前缀重复的太多，这需要增加前缀个数直到前缀出现的次数基本接近即可，因为本表此列长度很短并且比较固定，所以并不是很理想的测试数据，可以看到当长度为5的时候就接近一些了。

b、另外一个办法就是计算整列的选择性，并使前缀的选择性接近完整列的选择性。

先计算整列的选择性

计算不同的前缀对应的选择性值

这里差距有点大，主要因为测试数据太短，没必要使用前缀，这里只是介绍一个方法，选取一个合适的长度，要足够接近就好了。

找到合适的长度就可以创建索引了

1	`alter` `table` `vemp` `add` `key(name(5));`

前缀索引可以更小，更快，但是也有缺点：

1、无法使用前缀索引做order by和group by，也无法使用前缀做覆盖扫描。

注意：可以把字符串反转后存储，建立前缀索引，其实就相当于建立后缀索引了，例如找到某个域名的所有电子邮件地址。

3、多列索引（复合索引）

不要盲目的在很多个单列上创建单列的索引。

MySQL5.0以后的版本，引入了“索引合并”，在一定程度上可以使用表上的多个单列索引来定位指定的行，这种算法有3个变种：OR条件的联合（union）、AND条件的相交（intersection）、组合前两种情况的联合及相交。如果你在Extra看到下面的语句就说明用到了索引合并：

索引合并有时候是一种优化的结果，但实际上说明表的索引建立的很糟糕。

4、选择合适的索引序列

一个经验法则：当不考虑排序和分组时，将选择性最高的列放在前面通常是好的。

例如下列查询

1	`select` `*` `from` `emp2` `where` `num2=2000` `and` `job_num=373;`

是应该创建一个（num2,job_num）索引还是应该颠倒一下呢？可以看看哪个列的选择性更好或者看看where条件哪个分支对应的基数数据有多大：

所以应该把job_num放到前面，因为对应条件数量更小。

这只是个别数据，可能对其他一些数值查询不公平，如果考虑全局，可以计算一下选择性，这个方法前面已经提到了，把选择性高的列放到前面。

秒客网