MySQL之创建高性能的索引(五)

创建高性能的索引

多列索引

很多人对多列索引的理解都不够。一个常见的错误就是，为每个列创建独立的索引，或者按照错误的顺序创建多列索引。先来看第一个问题，为每个列创建独立的索引，从SHOW CREATE TABLE总很容易看到这种情况:

CREATE TABLE t (
c1 INT,
c2 INT ,
c3 INT,
KEY(c1),
KEY(c2),
KEY(c3)
);

这种索引策略，一般是由于人们听到一些专家诸如"把WHERE条件里面的列都建上索引"这样模糊的建议导致的.实际上这个建议是非常错误的。这样一来最好的情况也只能是一星索引，其性能必去真正最优的索引可能差几个数量级。有时如果无法设计一个三星索引，那么不如忽略掉WHERE子句，集中精力优化索引列的顺序，或者创建一个全覆盖索引。在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能。MySQL5.0和更新版本引入了一种"索引合并"(index merge)的策略，一定程度上可以使用表上的多个单列索引来定位指定的行。更早版本的MySQL只能使用其中某一个单列索引，然而这种情况没有哪一个独立的单列索引是非常有效的。例如，表fime_actor在字典fim_id和actor_id上各有一个单列索引。但对于下面的这个查询WHERE条件，这两个单列索引都不是好的选择:

CREATE TABLE t (
c1 INT,
c2 INT ,
c3 INT,
KEY(c1),
KEY(c2),
KEY(c3)
);

在老的MySQL版本中，MySQL对这个查询会使用全表扫描。除非改写成如下的两个查询UNION的方式:

mysql>  SELECT film_id,actor_id FROM film_actor WHERE actor_id = 1
UNION ALL
SELECT film_id,actor_id FROM film_actor WHERE film_id =1 AND actor_id <>1;

但在MySQL5.0和更新的版本中，查询能够同时使用这两个单列索引进行扫描，并将结果进行合并。这种算法有三个变种:OR条件的联合(union),AND条件的相交(intersection),组合前两种情况的联合及相交。下面的查询就是使用了两个索引扫描的联合，通过EXPLAIN中的Extra列可以看到这点:

mysql> EXPLAIN SELECT film_id,actor_id FROM film_actor WHERE actor_id =