MySQL单表查询时索引使用情况

本文针对 MySQL 单表查询时索引使用的几种场景情况进行分析。

假设有一个表如下：

CREATE TABLE single_table (id INT NOT NULL AUTO_INCREMENT,key1 VARCHAR(100),key2 INT,key3 VARCHAR(100),key_part1 VARCHAR(100),key_part2 VARCHAR(100),key_part3 VARCHAR(100),common_field VARCHAR(100),PRIMARY KEY (id),KEY idx_key1 (key1),UNIQUE KEY idx_key2 (key2),KEY idx_key3 (key3),KEY idx_key_part(key_part1, key_part2, key_part3)
) Engine=InnoDB CHARSET=utf8;

多个索引仅单个生效

SELECT * FROM single_table WHERE key1 = 'abc' AND key2 > 1000;

上面的查询可以拆分为两个步骤：

1. 查询 key1 或 key2 对应的二级索引，根据过滤条件得到索引上的记录。
2. 根据主键进行回表，拿到完整的记录，后根据另一个条件进行过滤。

这里的第一步选取哪个二级索引进行过滤，主要取决于优化器认为哪个条件到对应的二级索引中查询扫描的行数会更少。

我们假设 key1 对应的索引需要扫描的行数更少，那么查询时就会先在对应的二级索引树中过滤出 key1 = ‘abc’ 的记录，再根据该记录上的主键 id 去进行回表拿到完整的记录，最后按 key2 > 1000 进行过滤。

索引不生效

SELECT * FROM single_table WHERE key2 > 100 OR common_field = 'abc';

上面的例子中两个过滤条件通过 OR 连接，且只有 key2 列存在索引 idx_key2 。这是一种典型的索引失效场景。

原因是，条件 key2 > 100 和条件 common_field = ‘abc’ 之间是“OR”的关系。这代表着我们不能先按条件 key2 > 100 在二级索引中进行过滤，然后再进行回表，再按条件 common_field 进行过滤。

因此，在该查询中索引相当于失效，查询会走全表扫描。

索引合并

索引合并（index merge）指的是在一次查询中使用到多个二级索引，以此来优化查询效率。

具体的索引合并算法有下边三种：

一：Intersection合并（适用于使用不同索引的搜索条件之间使用AND连接起来的情况）

不过 MySQL 在某些特定的情况下才可能会使用到 Intersection 索引合并：

• 情况一：一个或多个二级索引的列都是等值匹配。且对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只匹配部分列的情况。
• 情况二：主键列可以是范围匹配

二：Union合并（适用于使用不同索引的搜索条件之间使用OR连接起来的情况）

• 情况一：二级索引列是等值匹配的情况，对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只出现匹配部分列的情况。
• 情况二：主键列可以是范围匹配
• 情况三：使用 Intersection 索引合并的搜索条件

三：Sort-Union合并
适合用于每个索引过滤完后集合较小，即使按主键排序一下，再回表，访问成本也不会太高。

需要注意的是，上面提到的索引合并的情况都是必要不充分条件。意思是满足了这些条件，不一定会进行索引的合并（得看优化器认为什么样的“访问方法”成本更低）。但一旦发生了索引合并，就一定需要满足下面提到的条件。

参考

《MySQL是怎样运行的》