「Mysql索引原理（五）」多列索引

很多人对多列索引的理解都不够。一个常见的错误就是，为每个列创建独立的索引，或者按照错误的顺序创建多列索引。

先来看第一个问题：为每个列创建独立的索引

create table t(
c1 INT,
c2 INT,
c3 INT,
KEY(c1),
KEY(c2),
KEY(c3)
)

这种索引策略，一般是由于人们听到一些专家诸如“把where条件里面的列都建上索引”这样模糊的建议所导致的。你们公司有没有这样的人？实际上这个建议是非常错误的。这样一来最好的情况下也只能是“一星”索引，其性能比起真正最优的索引可能差几个数量级。有时如果无法设计一个“三星”索引，那么不如忽略掉where子句，集中精力优化索引列的顺序，或者创建一个全覆盖索引。

三星系统：
一星：索引将相关的记录放到一起则获得一星
二星：如果索引中的数据顺序和查找中的排序顺序一致则获得二星
三星：如果索引中的列包含了查询中需要的全部列则获得三星</pre>

在多个列上创建独立的单列索引大部分情况下并不能提高MySQL的查询性能。MySQL5.0或者更新的版本引入了一种叫“索引合并”的策略，一定程度上可以使用表上的多个单列索引来定位指定的行。更早的版本的MySQL只能使用其中一个单列索引，然而这种情况下没有哪一个独立的单列索引是非常有效的。 例如，

CREATE TABLE `film_actor` (
  `film_id` int(10) DEFAULT NULL,
  `actor_id` int(10) DEFAULT NULL,
   KEY `film_id` (`film_id`) USING BTREE,
   KEY `actor_id` (`actor_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

查询语句：

select  film_id, actor_id  from  film_actor  where  actor_id=8  or  film_id=8;

这两个单列索引都不是好的选择。(分别演示actor_id=8， film_id=8，actor_id=8 or film_id=8)

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

select  film_id, actor_id  from  film_actor  where  actor_id=8
union all 
select  film_id, actor_id  from  film_actor  where  film_id=8 and actor_id <> 8;

但是在MySQL5.0和更新的版本中，查询能够同时使用者两个单列索引进行扫描，并将结果进行合并。 索引合并案例：

select  film_id, actor_id  from  film_actor  where  actor_id=8  and  film_id=8;

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

到底什么时候创建多列索引？

1. 当出现服务器对多个索引做相交操作时（通常有多个and操作），则意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。
2. 当服务器需要对多个索引联合操作时（通常有多个or操作），通常需要消耗大量cup和内存资源在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高，需要合并扫描返回的大量数据的时候。

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

多列索引的顺序 正确的顺序依赖于使用该索引的查询，并同时需要考虑如何更好地满足排序和分组的需要。 在一个多列BTree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，等等。所以，索引可以按照升序或者降序进行扫描，以精确满足符合列顺序的order by 、group by和distinct等子句的查询需求。在三星系统中，列顺序也决定了是否能够成为一个真正的“三星索引”。

经验法则：将选择性最高的列放到索引的最前面。这个建议有用吗？在某些场景可能有帮助，但通常不如避免随机IO和排序那么重要，我们考虑问题需要全面，场景不同选择不同，没有一个放之四海皆准的法则。这只是一个说明，这个法则在你的实际工作中可能没你想象的那么重要。

当不需要考虑排序和分组时，将选择性最高的列放在前面通常是最好的。这时候索引的作用只是用于优化WEHRE条件的查找，过滤掉更多的行。但是，性能不只是依赖于索引列的选择性，也和查询条件的具体值的分布有关。可能需要根据那些运行频率最高的查询来调整索引列的顺序，使这种情况下索引的选择性最高。

例1： select * from film_actor where film_id=1 and actor_id=8

是创建一个(film_id,actor_id)索引还是应该颠倒一下顺序？可以用一些查询来确定这个表中值的分布情况，并确定哪个列的选择性最高。

select sum(film_id=1) , sum(actor_id=8) from film_actor

根据咱们的经验法则，应该将索引列actor_id放到前面，因为对应条件值的actor_id数量更少。我们在来看看这个actor_id的条件值对应的film_id列的选择性如何：

select sum(film_id=1) from film_actor where actor_id=8;

也就是说，如果actor_id列放在索引前面，利用索引查询的行很少，速度很快，叶子节点最多用到6个，而film_id作为前列的话，叶子节点可能要用到4101个，差别还是蛮大的。 但是这样选定列顺序非常依赖于选定列的具体值。按上述办法优化，可能对其他一些条件值的查询不公平，其他一些查询的运行变得不如预期。 如，我们反过来：

例2： select * from film_actor where film_id=1 and actor_id=8

select sum(film_id=8) , sum(actor_id=1) from film_actor

select sum(actor_id=1) from film_actor where film_id=8;

此时就应该将film_id放在前面了，所以说，到底谁放在前面？

select count(distinct film_id)/count(*)  as film_id_actor, count(distinct actor_id)/count(*) as actor_id_film,count(*) from film_actor

由此可见，film_id的选择性更高，所以答案是将film_id作为索引的第一列。

alter table film_actor add key(film_id,actor_id) 

我们要考虑的是全局基数和选择性，而不是某个具体的查询。但是我们的应用日常工作中例1的使用频率相当高，而其他查询很少，那么我们就得以actor_id作为索引第一列，所以说没有一个统一的最好索引标准，只有最合适你应用的索引。

案例；

EXPLAIN结果：

Mysql为这个查询选择了索引（groupId,userId），如果不考虑列的技术，这是一个很合理的选择。但如果考虑一下uerId和groupId条件匹配的行数：

从结果看，符合groupId条件几乎满足表中的所有行，符合用户userId条件的有130万行记录，也就是说索引基本没什么作用，查询速度是很慢的。解决办法是，从业务角度入手，修改应用程序代码，禁止此类查询。

总结：经验法则在多数情况下是有用的，但要注意不要觉着平均情况下的性能也能代表特殊情况下的性能，特殊情况可能会摧毁整个应用的性能，然而真实的数据只有应用上线后才能知道，所以上线前我们要做充分的调研与测试。

注意，以上将的内容适用于B+树索引，哈希或者其他类型的索引并不会像B树一样按顺序存储数据，