性能优化二 高性能的索引策略

二、高性能的索引策略

1.独立的列

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

select actor_id from sakila.actor where actor_id + 1 = 5;

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

有时候需要索引很长的字符列，这会让索引变的大且慢。
通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但这个也会降低索引的选择性。所谓选择性，也就是说使查询条件变少了，匹配率更高了，索引的效率就低了。

一般情况下某个列前缀的选择性也是足够高的，足以满足查询性能。对于BLOB、TEXT或很长的VARCHAR类型的列，必须使用前缀索引。

诀窍在于要选择足够长的前缀保证较高的选择性，同时又不能太长，前缀应该足够长，以使得前缀索引的选择性接近于索引整个列。

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

例子如下：

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

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

找到合适的前缀的长度，下面创建前缀索引：

alter table sakila.city_demo add key(city(7));

select * from sakila.city_demo where city=’北京’

3.多列索引

很多人对多列索引的理解都不够，一个常见的错误就是，为每个列创建独立的索引，或者按照错误的顺序创建多列索引。例如：
create table t(
c1 int,
c2 int,
c3 int,
key(c1);
key(c2);
key(c3)
);
这种索引策略，也只能算作“一星”索引，其性能比起真正优秀的索引可能差好几个数量级。在多个列上建立独立的单列索引大部分情况下并不能提高mysql的查询性能。

在5.0及以后版本，引入了一种叫“索引合并”的策略，一定程度上可以使用表上的多个单列索引来定位指定的行。更早版本的mysql只能使用其中某一个单列索引，然而这种情况下没有哪一个单独的单列索引是非常有效的。例如，表film_actor在字段film_id 和 actor_id上各有一个单列索引。但对于下面这个索引where条件，这两个单列索引都不是最好的选择：

select film_id,actor_id from sakila.film_actor where actor_id =1 or film_id =1;

在老的msql版本中，mysql对这个查询会使用全表扫描（索引无用）。除非改写成如下的查询：

select film_id,actor_id from sakila.film_actor where actor_id =1 union all select film_id,actor_id from sakila.film_actor where film_id = 1;

但是在5.0和更新的版本中，查询能够同时使用这两个单列索引进行扫描，并讲结果进行合并。
这种算法有三个变种：
OR条件 联合（union）;
and条件 相交（intersection）;
组合前两种情况的联合及相交；

通过explain中的extra列可以看到：

1. 当出现对多个索引做相互操作时（通常有多个AND条件），通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。
2. 当需要对多个索引做联合操作时（通常有多个OR条件），通常需要消耗大量CPU和内存资源在算法的缓存、排序和合并上。
3. 更重要的是，优化器不会计算优化的成本，导致还不如直接走全表扫描。

如果再explain中看到索引合并，应该好好检查一下查询和表的结构，看是不是已经是最优了。

4.使用索引扫描来做排序

mysql有两种方式可以生成有序的结果：

1. 通过排序操作；
2. 按索引顺序扫描。

如果explain出来的Type列值为“index”，则说明mysql使用了索引扫描来做排序。
只有当索引的列和order by子句的顺序完全一致，并且所有的排序方向都一样时，mysql才能使用索引来对结果做排序。

有一种情况下order by子句可以不满足索引的最左前缀的要求，就是前导列为常量的时候。如何where子句或者join子句中对这些列指定了常量，就可以“弥补”索引的不足。如下表：

mysql可以使用rental_date索引为下面的查询做排序

即使order by子句不满足索引的最左前缀的要求，也可以由于查询拍下，这是因为索引的第一列被指定为一个常数。

还有更多可以使用索引做排序的查询示例。下面这个查询可以利用索引排序，是因为查询为索引的第一列提供了常量条件，而是用第二列进行排序，将两列组合在一起，就形成了索引的最左前缀：

... where rental_date = '2005-05-25' order by inventory_id desc;

下面这个查询也没问题，因为order by使用的就是索引的最左前缀：

... where rental_data > '2005-05-25' order by rental_date,inventory_id;

下面一些不能使用索引做排序的查询：
- 下面这个查询不能使用两种不同的排序方向，但是索引列都是正序排序的：

 ... where rental_date = '2005-05-25' order by inventory_id desc,customer_id asc;

• 下面这个查询的order by 子句中引用了一个不在索引中的列：

 ... where rental_date = '2005-05-25' order by inventory_id,staff_id;

• 下面这个查询的where 和 order by中的列无法组合成索引的最左前缀：

 ... where rental_date = '2005-05-25' order by customer_id;

• 下面这个查询在索引列的第一列上是范围条件，所以mysql无法使用索引的其余列：

 ... where rental_date > '2005-05-25' order by customer_id;

• 这个查询在inventory_id列上有多个等于条件。对于排序来说，这也是一种范围查询：

 ... where rental_date = '2005-05-25'  and inventory_id in(1,2) order by customer_id;