mysql多列索引优化

 

 “把Where条件里面的列都建上索引”，这种说法其实是非常错误的！

 

这样一个查询，假设actor_id与film_id都单独建立索引 

SELECT film_id , actor_id FROM sakila.film_actor 
WHERE actor_id = 1 OR film_id = 1;

在老的mysql版本中，mysql对这个查询会使用全表扫描，除非改写成下面这样

SELECT film_id , actor_id FROM sakila.film_actor WEHRE actor_id = 1
UNION ALL
SELECT film_id , actor_id FROM sakila.film_actor WEHRE actor_id = 1
AND actor_id <> 1;

 

但在mysql 5.0中，查询能够同时使用这两个单列索引进行扫描，并将结果进行合并，通过Explain的Extra列可以看到这点，这种算法有3个变种:

　　1.OR条件的联合（union）

　　2.AND条件的相交（intersection）

　　3.组合前两种情况的联合与相交

　　详解具体可以参考这里

 

索引合并策略有时候是一种优化结果，但实际上更多时候说明了表上的索引建得很糟糕：

　　1.当出现服务器对多个索引做相交操作时（通常有多个AND），通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引

　　2.当服务器需要对多个索引做联合操作时（通常有多个OR），通常需要耗费大量CPU和内存资源在算法的缓存、排序和合并上，特别是当其中有些索引选择性不高，对扫描返回的大量数据进行合并时

所以如果在Explain中看到有索引合并，要好好检查以下查询和表的结构是否已经是最优的

 

 

多列索引

顾名思义，多列索引就是将多个列作为一个索引，在一个多列B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，然后第二列第三列等等。所以可以按照升序或者降序进行扫描，以满足精确符合列顺序的ORDER BY , GROUP BY 和 DISTINCT等子句，所以，多列索引的顺序至关重要，key(列1, 列2) 和 key(列2, 列1) 这两个多列索引很可能有巨大的性能差距

以下面查询为栗

SELECT * FROM payment WHERE staff_id = 2 AND customer_id = 584;

是应该创建一个 key(staff_id, customer_id) 还是 key(customer_id, staff_id) ？可以跑一些查询来确定在这个表中值的分布情况，来确定哪个列的选择性更高

SELECT SUM(staff_id = 2), SUM(customer_id = 584) FROM payment

结果里，哪个列的数量更少，就代表选择性更高，那个列就应该在建立时放在多列索引的最前面、最左侧

不过这样也有需要注意的地方，因为这个例子查询的结果非常依赖具体的值（2，584），这样会对其他条件值的查询不公平，应该结合实际情况从全局的角度来考虑