MySQL优化之索引下推和索引合并

索引下推

ref 1-MySQL性能优化之索引下推 | Blog

什么是索引下推

MySQL 5.6 版本中，引入了索引下推（Index Condition Pushdown，简称 ICP），它能减少回表查询次数，提高查询效率。

在 MySQL 5.6 之前，使用非主键索引进行查询的时候，存储引擎通过索引查询数据，然后将结果返回给MySQL Server 层，在 Server 层判断是否符合条件。

在 MySQL 5.6 版本引入「索引下推」后，在使用包含索引的列进行条件判断时，存储引擎会直接根据索引条件过滤掉不符合要求的记录，将原本应交由「Server层」处理的工作，直接在「存储引擎层」完成了。然后再回表，最后将结果返回给 Server 层。

综上，索引下推的好处是

存储引擎层可以在回表查询之前，对数据进行过滤，减少回表次数
可以减少 MySQL Server 层从存储引擎接收到的数据

索引下推实战

此处给出一个使用索引下推的实例，加深理解。

创建一个 user 表，包含 id、name、age 字段，并创建联合索引 name_age_idx(name,age)。并插入必要的测试数据。

create table `user` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `age` int unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `name_age_idx` (`name`,`age`)
) ENGINE=InnoDB CHARSET=utf8;


insert into user values
(1,"刘大宝",30),
(2,"刘小宝",20),
(3,"周大宝",40),
(4,"周小宝",10);

执行如下查询语句。对 name 的筛选使用到了范围匹配，根据「最左匹配原则」，将只会使用到 name 字段的索引。

select * from user where name like "大%" and age = 30

在 MySQL 5.6 版本前，根据 name like "大%" 筛选条件，存储引擎将匹配到两条记录，然后进行两次回表，最后将 (1,"刘大宝",30) 和 (3,"周大宝",40) 这两条记录返回给 Server 层。Server 层再根据 age = 30 的查询条件进行过滤，最终只返回 (1,"刘大宝",30) 这一条记录。
在 MySQL 5.6 版本之后，存储引擎在使用索引列筛选后，将得到 ("刘大宝",30) 和 ("周大宝",40) 这两条记录。根据索引下推，存储引擎可以执行 age = 30的筛选，最终只得到一条记录，此时只需要一次回表。

索引下推配置

可以使用如下命令关闭索引下推。

set optimizer_switch='index_condition_pushdown=off';

索引合并

ref-1-MySQL优化之索引合并 | 简书
ref-2-Index Merge Optimization | MySQL Cookbook
ref 3-MySQL索引合并算法 | Blog
ref 4-MySQL 优化之 index merge | Blog

什么是索引合并

MySQL 5.0 之前，一个表一次只能使用一个索引，无法同时使用多个索引分别进行条件扫描。但是从 5.1 版本开始，引入了索引合并（Index Merge）技术，对同一个表可以使用多个索引分别进行条件扫描，然后将结果进行合并处理，最后再进行回表查询。

与其说是「MySQL 5.0 之前，一个表一次只能使用一个索引」，倒不如说是「大多数情况下，使用两个/多个索引，分析两个/多个索引B+树，比只使用一个索引和全表扫描，会更耗时」。所以，绝大多数情况下，数据库都是只用一个索引。

Ref. Index Merge Optimization | MySQL Cookbook

The Index Merge access method retrieves rows with multiple range scans and merges their results into one. This access method merges index scans from a single table only, not scans across multiple tables. The merge can produce unions, intersections, or unions-of-intersections of its underlying scans.

Index Merge is not applicable to full-text indexes.

索引合并技术，可以把几个索引的范围扫描合并成一个索引。
索引合并的时候，会对索引进行并集（unions），交集（intersections）或者交集的并集（unions-of-intersections，即先交集再并集），并合并为一个索引。
索引合并技术只适用于单表查询，不适用于多表查询。
索引合并并不适用于全文索引。

索引合并的优缺点

优点

可以让一条 SQL 语句使用多个索引，减少不必要的回表，提高查询效率。

缺点

索引合并技术只适用于单表查询，不适用于多表查询。
索引合并并不适用于全文索引。
如果在 WHERE 语句中，存在多层嵌套的 AND/OR，MySQL 可能不会选择最优的方案，可以尝试通过拆分 WHERE 子句的条件来进行转换

(x AND y) OR z => (x OR z) AND (y OR z)
(x OR y) AND z => (x AND z) OR (y AND z)

如何查看索引合并的使用

使用 EXPLAIN 对 SQL 语句进行查看，如果使用了索引合并

type 列会限制 index_merge
key 列会显示出所有使用的索引
Extra 列会显示具体的索引合并算法
- Using union 表示对多个索引求并集
- Using intersect 表示对多个索引求交集
- Using sort_union表示对查询到的记录先按 rowid 排序再并集

索引合并算法

索引合并算法包括

Index Merge Intersection Access Algorithm（交集）
Index Merge Union Access Algorithm （并集）
Index Merge Sort-Union Access Algorithm（排序并集）

Index Merge Intersection Access Algorithm

Index Merge Intersection Access Algorithm（索引合并交集访问算法））会对多个索引条件扫描得到的结果进行交集运算。WHERE 子句中多个查询条件需要 AND 连接，并且满足下面的条件

二级索引是等值查询；如果是组合索引，组合索引的每一位都必须覆盖到，不能只是部分
InnoDB 表上的主键范围查询条件

-- 主键可以使范围查询，二级索引只能是等值查询
SELECT * FROM innodb_table
  WHERE primary_key < 10 AND key_col1 = 20;

-- 没有主键的情况
SELECT * FROM tbl_name
  WHERE key1_part1 = 1 AND key1_part2 = 2 AND key2 = 2;

Index Merge Union Access Algorithm

Index Merge Union Access Algorithm （索引合并并集访问算法）会对多个索引条件扫描得到的结果进行并集运算。WHERE 子句中多个查询条件需要 OR 连接，并且满足下面的条件

二级索引是等值查询；如果是组合索引，组合索引的每一位都必须覆盖到，不能只是部分
InnoBD 表上的主键范围查询
符合 index merge intersect 的条件

-- 无主键，or 连接
SELECT * FROM t1
  WHERE key1 = 1 OR key2 = 2 OR key3 = 3;

-- 既有and 也有or
SELECT * FROM innodb_table
  WHERE (key1 = 1 AND key2 = 2)
     OR (key3 = 'foo' AND key4 = 'bar') AND key5 = 5;

Index Merge Sort-Union Access Algorithm

Index Merge Sort-Union Access Algorithm（索引合并排序并集访问算法）适用于 WHERE 子句中的条件是通过 OR 结合的不同索引的范围条件，但是不能使用 Index Merge Union 算法的情景。

SELECT * FROM tbl_name
  WHERE key_col1 < 10 OR key_col2 < 20;

SELECT * FROM tbl_name
  WHERE (key_col1 > 10 OR key_col2 = 20) AND nonkey_col = 30;

Sort-Union Access Algorithm 和 Union Access Algorithm 算法的区别是

Sort-Union Access Algorithm 必须在返回行数据前，先获取行ID（rowId）并对行 ID 进行排序。
Sort-Union Access Algorithm 放宽了使用条件，二级索引不必等值查询，联合索引也不必匹配所有的索引项。

索引合并实战

先创建一个 t2 表，创建主键索引和联合索引，并插入足量的测试数据。

create table t2(
	id int primary key, 
    a int not null, 
    b int not null,
    c int not null, 
    d int not null,
    f int not null,
    index idx_abc(a,b,c), 
    index idx_d(d),
    index idx_f(f)
);

DELIMITER $$  -- 使用$$作为结束符
CREATE PROCEDURE t2_copy()
BEGIN
SET @i=1;
WHILE @i<=10000 DO
INSERT INTO t2 VALUES(@i,@i,@i,@i,@i,@i);
SET @i=@i+1;
END WHILE;
END $$
DELIMITER ; -- 使用;作为结束符

call t2_copy;

Using intersect

mysql> explain select * from t2 where id>1000 and f=1000;
+----+-------------+-------+------------+-------------+---------------+---------------+---------+------+------+----------+---------------------------------------------+
| id | select_type | table | partitions | type        | possible_keys | key           | key_len | ref  | rows | filtered | Extra                                       |
+----+-------------+-------+------------+-------------+---------------+---------------+---------+------+------+----------+---------------------------------------------+
|  1 | SIMPLE      | t2    | NULL       | index_merge | PRIMARY,idx_f | idx_f,PRIMARY | 8,4     | NULL |    1 |   100.00 | Using intersect(idx_f,PRIMARY); Using where |
+----+-------------+-------+------------+-------------+---------------+---------------+---------+------+------+----------+---------------------------------------------+

Using union

mysql> explain select * from t2 where f=1000 or d=1000;

+----+-------------+-------+------------+-------------+---------------+-------------+---------+------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type        | possible_keys | key         | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+-------+------------+-------------+---------------+-------------+---------+------+------+----------+---------------------------------------+
|  1 | SIMPLE      | t2    | NULL       | index_merge | idx_d,idx_f   | idx_f,idx_d | 4,4     | NULL |    2 |   100.00 | Using union(idx_f,idx_d); Using where |
+----+-------------+-------+------------+-------------+---------------+-------------+---------+------+------+----------+---------------------------------------+

Using sort_union

mysql> explain select * from t2 where id >1000 or a=2;
+----+-------------+-------+------------+-------------+-----------------+-----------------+---------+------+------+----------+------------------------------------------------+
| id | select_type | table | partitions | type        | possible_keys   | key             | key_len | ref  | rows | filtered | Extra                                          |
+----+-------------+-------+------------+-------------+-----------------+-----------------+---------+------+------+----------+------------------------------------------------+
|  1 | SIMPLE      | t2    | NULL       | index_merge | PRIMARY,idx_abc | idx_abc,PRIMARY | 4,4     | NULL | 4974 |   100.00 | Using sort_union(idx_abc,PRIMARY); Using where |
+----+-------------+-------+------------+-------------+-----------------+-----------------+---------+------+------+----------+------------------------------------------------+

因为组合索引没有完全覆盖而导致未使用 Intersect

mysql> explain select * from t2 where id>1000 and a=1000;
+----+-------------+-------+------------+------+-----------------+---------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys   | key     | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+-----------------+---------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | t2    | NULL       | ref  | PRIMARY,idx_abc | idx_abc | 4       | const |    1 |    49.99 | Using index condition |
+----+-------------+-------+------------+------+-----------------+---------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

因二级索引不是等值查询而导致未使用 Intersect

mysql> explain select * from t2 where id>1000 and d<1000;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t2    | NULL       | range | PRIMARY,idx_d | PRIMARY | 4       | NULL | 4973 |    10.04 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

对索引合并的进一步优化

索引合并可以使用多个索引同时进行扫描，然后对结果进行合并。但是如果出现了索引合并（Index Merge），那么一般也意味着我们的索引建立得不太合理，因为可以通过建立联合索引进一步优化。

SELECT * FROM t1 WHERE key1=1 AND key2=2 AND key3=3;

如上查询语句，我们可以建立联合索引 key1_2_3_idx(key1,key2,key3) 进一步优化查询，这样只需要扫描一个索引B+树即可，而不是使用索引合并技术对三个索引B+树进行扫描再交集合并。

关于索引合并和多列联合索引的对比，可参考 Multi Column indexes vs Index Merge 做扩展阅读。

索引合并配置

使用 select @@optimizer_switch 查看 optimizer_swith 变量信息，可以发现和索引合并相关的配置信息

index_merge
index_merge_intersection
index_merge_union
index_merge_sort_union

默认情况下，上面四个变量的值都是 on，即索引合并是是启用的。可以要单独启或关闭（设置 off）某个算法。

mysql> select @@optimizer_switch \G;

*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on
1 row in set (0.00 sec)