MySQL 独立索引和联合索引的选择

MySQL 独立索引和联合索引的选择

通常会对多列索引缺乏理解，常见的错误是将很多列设置独立索引，或者是索引列使用错误的次序。我们在下一篇讨论索引列次序的问题，首先看一下多列独立索引的情况，以下面的表结构为例：

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

使用这种索引策略通常是一些权威的建议（例如在WHERE条件中用到的条件列增加索引）的结果。事实上，这是大错特错的，要评分的话顶多给1颗星。这种方式的索引与真正优化的索引相比，要慢上几个数量级。有时候当你不能设计三星以上的索引时，去关注优化行次序或者创建覆盖索引都比忽略WHERE条件强。

覆盖索引（covering index）指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。也可以称之为实现了索引覆盖。 当一条查询语句符合覆盖索引条件时，MySQL只需要通过索引就可以返回查询所需要的数据，这样避免了查到索引后再返回表 *** 作，减少I/O提高效率。 如，表covering_index_sample中有一个普通索引 idx_key1_key2(key1,key2)。当我们通过SQL语句：select key2 from covering_index_sample where key1 = ‘keytest';的时候，就可以通过覆盖索引查询，无需再从数据表找数据行。

对很多列创建独立的索引在很多情况下，并不能帮助MySQL改善性能。MySQL 5.0及更新的版本可以使用索引合并策略对这类设计进行些许的优化 —— 这种方式允许在有多列索引的数据表中的查询中限制在索引的使用去定位所需的数据行。

index merge 是对多个索引分别进行条件扫描，然后将它们各自的结果进行合并(intersect/union)

早期的MySQL版本只能使用一个索引，因此当没有索引辅助时，MySQL通常进行全表扫描。例如在film_actor表有一个film_id和actor_id索引，但是在WHERE条件中同时使用这两个索引并不是一个好的选择：

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

在早期的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;

在MySQL 5.0之后的版本中，查询会同时使用两个索引并且合并最终的结果。需要三个变体的算法实现这个过程：

1. 使用OR条件获取并集（union）数据
2. 使用AND条件获取交集数据
3. 将上面两个步骤的数据的交集再取并集。
   

上面有点费解，其实应该是分布使用单个条件（以便使用索引）查出全部数据，然后再组合数据。下面使用EXPLAIN查看一下。

EXPLAIN SELECT `film_id`,`actor_id` FROM `film_actor` WHERE `actor_id`=1 OR `film_id`=1

可以看到查询方式是全表扫描，但是使用了Extra做优化。MySQL在处理负责查询时会使用这种技巧，因此你可能会在Extra中看到嵌套 *** 作。这种索引合并的策略有些时候会发挥很好的作用，但更多的时候应该当作是对差劲索引使用的一个指示：

1. 当服务器使用交集索引（通常是使用AND条件），通常意味着你需要一个索引包含所有相关的列，而不是独立的索引列再组合。
2. 当服务器使用并集索引（通常是使用OR条件），有时候缓存、排序和合并 *** 作会占用很多的CPU和内存资源，尤其是索引并不都是具备筛选的时候，这会导致扫描返回大量的数据行供合并 *** 作。
3. 记住优化器并不承担这些成本——它仅仅是优化随机页读取的数量。这会使得查询“掉价”，导致全表扫描造成事实上更慢。CPU和内存的高占用会影响并发查询，但这些影响在你单独运行查询语句时并不会发生。因此，有时候像在MySQL 4.1版本那样重写那些使用UNION的查询会得到更优的效果。
   

当你使用EXPLAIN分析的时候看到了索引合并，你应该检查查询语句和表结构，看看是不是最优的方式。你可以使用optimizer_switch（优化开关）禁用索引合并来检查。

再将film_actor的索引改为联合索引(删除原先的两列独立索引film_id和actor_id)看一下效果，可以看到此时避免了全表查询。

ALTER TABLE film_actor ADD INDEX `sindex` (`film_id`,`actor_id`);

以上就是MySQL 独立索引和联合索引的选择的详细内容，更多关于MySQL 独立索引和联合索引的资料请关注脚本之家其它相关文章！