索引策略-多列索引

一. 前言

当我们对多列索引的理解不够深刻的时候,往往会为每个列创建独立的索引或者按照错误的顺序创建多列索引。

二. 案例说明

问题一: 为每个列创建独立索引

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

这种索引策略,一般都是由于人们听到一些专家诸如"把Where条件里面的列都建上索引"这样模糊的建议导致的。这样一来最好也只能是"一星"索引,其性能比真正最优的索引可能差几个数量级。有时如果无法设计一个"三星"索引,那么不如忽略掉Where子句,集中精力优化索引顺序,或者创建一个全覆盖索引。

2.1 三星索引

一星: 索引将相关的记录放在一起
二星: 如果索引中的数据顺序和查找顺序一致
三星: 如果索引中的列包含了查询中需要的全部列

三星索引的相关实践可以参考: 三星索引实战

2.2 Mysql底层对单列索引的处理

在多个列上建立独立的单列索引大部分情况下并不能提高Mysql的查询性能。Mysql 5.0 和更新版本引入了一种叫"索引合并"(index merge)的策略,一定程度上可以使用表上的多个单列索引来定位指定的行。更早版本的 Mysql只能使用其中某一个单列索引,然而这种情况下没有哪一个独立的单独索引是非常有效的。例如，下表 film_actor在字段 film_id 和 actor_id 各有一个单列索引。但对于下面这个查询的 Where 条件，这两个单列索引都不是好的选择:

建表语句:

CREATE TABLE `film_actor` (
  `id` int(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `actor_id` int(20) NOT NULL COMMENT '演员id',
  `film_id` int(20) DEFAULT NULL COMMENT '电影id',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_film_id` (`film_id`) USING BTREE,
  KEY `idx_actor_id` (`actor_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4

查询语句:

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

执行计划:

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

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和更新的版本中,查询能够同时使用两个单列索引进行扫描,并将结果进行合并。这种算法有三个变种: OR条件的联合(union),AND条件的相交(intersection),组合前两种情况的联合及相交。

Tips: 索引合并通过能在执行计划的 Extra上看到

2.3 索引合并背后的问题

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

• 当出现服务器对多个索引做相交操作时(通常有多个AND条件),通常意味着需要一个包含所有相关列的多列索引,而不是多个独立的单列索引。
• 当服务器需要对多个索引做联合操作时(通常有多个OR条件),通常需要消耗大量CPU和内存资源在算法的缓存,排序和合并操作上。特别是当中有些索引选择性不高,需要合并扫描返回大量数据的时候。
• 更重要的是,优化器不会把这些计算到"查询成本"(cost) 中,优化器只关心随机页面读取。这会使得查询的成本被"低估",导致该执行计划还不如直接走全表扫描。这样做不但会消耗更多的CPU和内存资源,还可能会影响查询的并发性,但如果是单独运行这样的查询则往往会忽略对并发性的影响。通常来说,还不如像在Mysql 4.1 或者更早的时代一样,将查询改写成 UNION的方式往往更好。

如果在EXPLAIN中看到索引合并,应该好好检查一下查询和表的结构,看是不是已经是最优的。也可以通过参数 optimizer_switch 来关闭索引合并功能,也可以使用 Ignore index 提示让优化器忽略掉某些索引