关于组合索引

大多数书的目录中会列出“章”和“节”，这便是组合索引。

在实际的数据库访问中，大多数的查询都包含组合条件，比如：

 

... where a = 1 and b = 2

... where a = 1 order by b

... where a = 1 group by b

 

这个时候，即使字段 a 和字段 b 已经分别建立了索引，它们仍然不能同时发挥作用，因为一次查询对于一个数据表只能使用一个索引，它们是无法进行效用叠加的。这样一来，便会存在一定程度的局部行扫描（Range Scan），这在有些特定的场景中将严重影响查询性能，比如上述第一条查询，数据库会先利用字段a的索引快速匹配a=1的记录，然后在这些记录中帅选b=2的记录，而此时b字段的索引将爱莫能助，试想，如果a=1的匹配行非常多的话，查询时间将花在b字段的帅选操作上。

为了应付这样的查询，我们不得不使用组合索引。

 

我们来创建这样一个数据表：

CREATE TABLE `key_t` (
  `id` int(11) NOT NULL,
  `key1` int(11) NOT NULL DEFAULT '0',
  `key2` int(11) NOT NULL DEFAULT '0',
  `key3` int(11) NOT NULL DEFAULT '0',
   PRIMARY KEY (`id`),
   KEY `normal_key` (`key1`,`key2`,`key3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

 这个数据表建立了一个包含了三个字段的组合索引 normal_key，同时它还有一个自增类型的主键。我们为这个表填充了 100 万行的记录，其中 key1、key2、key3的内容均为0到999的随机整数。

 

SELECT COUNT(*) FROM `key_t`

 

我们知道以下几个查询都可以直接使用 normal_key 索引，而不需要任何的行扫描。

SELECT * FROM `key_t` WHERE key1=1
SELECT * FROM `key_t` WHERE key1=1 and key2=2 
SELECT * FROM `key_t` WHERE key1=1 and key2=2 and key3=3

 必要的时候，查询优化器还会帮你调整条件表达式的顺序，以匹配组合索引的要求，比如以下这个查询：

SELECT * FROM `key_t` WHERE key1=1 and key3=3 and key2=2 

 它会被查询优化器理解为：

SELECT * FROM `key_t` WHERE key1=1 and key2=2 and key3=3

 优化器是很智能的。

 

其次，组合索引对于包含 order by 和 group by 的查询也发挥着重要的作用，它们同样也遵循最左前缀原则，我们看以下这个SQL语句的分析：

 

其中 type 为 index，表示这个查询只需要在索引中扫描即可，这里的索引即 normal_key 。也就是说，查询语句中 order by 指定的排序规则正好是索引本身的顺序，可以直接拿来派上用场，不需要重新排序。需要注意的是，有些非顺序的索引类型（如 Hash），对 order by 是无效的。

 

下面这个查询正是符合最左前缀的原则，它也使用了 normal_key 索引。

 

 

 那么，再看看下面这个SQL语句：

 虽然它用到了 normal_key 索引，但只是对 where 子句起作用，而后面的 order by 则需要排序计算，Using filesort 已经证明了这点。

 

对于包含 group by 的查询，数据库一般需要先将记录分组后放置在新的临时表中，然后分别对它们进行函数计算，比如 count()、sum() 或 max() 等。当有切当的索引存在时，group by 有时也可以使用索引来取代创建临时表，这当然是我们所希望的。以下这个 SQL 语句便利用了 normal_key 索引，避免了创建临时表。

 

而对于另外一些情况，组合索引就无法帮助 group by 了，比如以下的 SQL 语句：

 

 的确，Using temporary 和 Using filesort 非常不受欢迎，它们越少越好。

 

组合索引的副作用

在有些情况下，组合索引对于一些查询会产生误导，你需要考虑是否应该阻止组合索引，或者预先设计更加适合的索引。同样针对刚才那个数据表，我们看以下这个查询：

 

 

根据最左前缀原则，以上的查询没有可使用的索引，索引要进行全表扫描，必然花费很长的时间。但是，并不是我们想象的那样，看下面的分析：

 

 竟然使用了 normal_key 索引，而且 type 为 index ，表示这里进行了索引扫描，显然优化器认为在索引扫描中扫描要比在全表数据中扫描更加高效，但是，这次它的如意算盘打错了。仔细看，这 10 行记录是按照 key1 字段来顺序排列的，这说明查询是基于 normal_key 索引的扫描，而不是基于数据本身的扫描。Innodb 类型表中数据的存储顺序是按照主键来排列的。

 

我们在查询的尾部增加了 ORDER BY id ，结果如下所示：

 用 EXPLAIN 分析如下：

 

 由于我们指定了 order by id ，查询优化器聪明地放弃了 normal_key 索引，而使用主键进行扫描，这基本上相当于全表扫描。如果你希望结果仍然按照 key1 排序，这不是什么问题，你可以增加一个包含（key2,key1）字段的组合索引，注意它们的顺序，（key1,key2）索引和（key2,key1）索引完全不同，你必须根据需要来进行抉择，而优化器对此无能为力。