MySQL Execute Plan--Index Merge特性

index merge特性

在mysql 5.5之前版本中，查询或子查询被限制在一个表只能使用一个索引(回表查询除外)。

假设表tb1001上c1和c2列分别有单列索引，如对下面查询：

select * from tb1001 where c1='xxx' or c2='xxx';

单独使用任一索引都无法获取到所有满足条件的数据，因此查询只能使用全表扫描。

在mysql 5.5版本中引入index merge特性，允许：
查询对一个表上多个索引进行范围扫描并将多个扫描结果进行合并(union/intersect)。

index merge三种合并算法：

1、index merge intersect：对多个结果集求交集
2、index merge union：对多个结果集求union集合(无需对结果集排序)
3、index merge sort-union：对多个结果集先排序再求union集合

 

 

index merge intersect算法

当查询过滤条件(where部分)上使用and关联多个不同key的过滤条件时，如：

# 表tb1001有主键索引primary key(id)
# 表tb1001有辅助索引idx_c1(c1) 和辅助索引idc_c2(c2)

select * from tb1001 where c1='xxx' and c2='xxx';

不使用index merge intersect算法时执行计划伪代码为：

select * from tb1001
where id in (
select id from tb1001 where c1='xxx')
and c2='xxx';

使用index merge intersect算法时执行计划伪代码为：

select t2.* from (
select id from tb1001 where c1='xxx'
intersect
select id from tb1001 where c2='xxx'
) as t1
inner join tb1001 as t2
on t1.id=t2.id;

 

操作成本假设1：

假设：
满足c1='xxx'的记录有10000行：索引idx_c1上每个数据页存放500行索引记录，满足条件数据：
    a、"顺序存放"在索引idx_c1上"连续"的20个索引页中。
    b、"分散存放"在主键上"随机"的2000个数据页中。
满足c2='xxx'的记录有20000行，索引idx_c2上每个数据页存放500行索引记录，满足条件数据:
    a、"顺序存放"在索引idx_c2上"连续"的40个索引页中。
    b、"分散存放"在主键上"随机"的4000个数据页中。
同时满足c1='xxx' and c2='xxx'的记录有200行，满足条件数据:
    a、"分散存放"在主键上"随机"的40个数据页中

那么：
1、不使用index merge intersect算法需要"顺序读取"20个idx_c1索引页+"随机读取"2000个主键索引数据页
2、使用index merge intersect算法需要"顺序读取"20个idx_c1索引页+"顺序读取"40个idx_c2索引页+"随机读取"40个主键索引数据页
针对上面情况，使用index merge intersect算法能有效降低对主键的回表查找次数和随机读取次数(从2000次下降至40次)。

操作成本假设2：

假设：
满足c1='xxx'的记录有20行：索引idx_c1上每个数据页存放500行索引记录，满足条件数据：
    a、"顺序存放"在索引idx_c1上"连续"的1个索引页中。
    b、"分散存放"在主键上"随机"的20个数据页中。
满足c2='xxx'的记录有200000行，索引idx_c2上每个数据页存放500行索引记录，满足条件数据:
    a、"顺序存放"在索引idx_c2上"连续"的400个索引页中。
    b、"分散存放"在主键上"随机"的40000个数据页中。
同时满足c1='xxx' and c2='xxx'的记录有19行，满足条件数据:
    a、"分散存放"在主键上"随机"的19个数据页中

那么：
1、不使用index merge intersect算法需要"顺序读取"1个idx_c1索引页+"随机读取"20个主键索引数据页
2、使用index merge intersect算法需要"顺序读取"1个idx_c1索引页+"顺序读取"400个idx_c2索引页+"随机读取"19个主键索引数据页
针对上面情况，使用index merge intersect算法需要额外读取400个idx_c2索引页才能降低1次主键的回表查询和随机读取，显然性能更差。

index merge intersect算法和index condition pushdown特性

在mysql官方文档中，index merge intersect算法可以应用在分别使用主键和二级索引的查询中，如：

select *
from innodb_table
where primary_key < 10
and key_col1 = 20;

 

在未引入icp特性的早期mysql版本中，主键上过滤条件(primary_key < 10)不会"下推"到查询满足key_col1 = 20条件的过程中，因此可以使用index merge intersect算法来减少回表查找次数。

在引入icp特性的mysql版本中，由于辅助索引的索引记录中都包含主键列数据，因此主键上过滤条件(primary_key < 10)可以"下推"到查询满足key_col1 = 20条件的过程中，无需再使用index merge intersect算法。

## 在mysql 5.7版本中测试
select *
from tb001
where c1=10
and id<100;
## 执行计划为：
*************************** 1. row ***************************
           id: 1
  select_type: simple
        table: tb001
   partitions: null
         type: ref
possible_keys: primary,idx_c1
          key: idx_c1
      key_len: 5
          ref: const
         rows: 1
     filtered: 33.33
        extra: using where; using index
## 执行计划extra部分没有index merge相关信息

 

index merge intersect性能问题优化

 

在部分场景中，使用index merge intersec算法会带来严重的性能问题，dba可以通过mysql参数optimizer_switch来关闭该特性。

对于通过index merge intersec算法受益的查询，可以考虑使用组合索引或覆盖索引来替换单列索引。

如对上面查询，可以将索引idx_c1(c1)调整为idx_c1_c2(c1,c2)，其查询性能更佳。

 

 

index merge union算法
当查询过滤条件(where部分)上使用or关联多个不同key的过滤条件时，如：

# 表tb1001有主键索引primary key(id)
# 表tb1001有辅助索引idx_c1(c1) 和辅助索引idc_c2(c2)
select * from tb1001 where c1='xxx' or c2='xxx';

其操作步骤为：

1、使用idx_c1索引获取到满足条件的[c1,id]记录，记录默认按照id排序
2、使用idx_c1索引获取到满足条件的[c1,id]记录，记录默认按照id排序
3、将已经按照id排序的步骤1和步骤2的数据进行合并去重id。
4、按照id回表查找并返回

伪代码为：

select t2.* from (
select id from tb1001 where c1='xxx'
union
select id from tb1001 where c2='xxx'
) as t1
inner join tb1001 as t2
on t1.id=t2.id

在创建索引idx_c1(id)时，其等价为idx_c1(c1,id),相同c1值的记录按id值排序，因此union操作的两个中见结果集在id上时有序的。

 

index merge sort-union算法

当查询过滤条件(where部分)上使用or关联多个不同key的过滤条件时，如：

# 表tb1001有主键索引primary key(id)
# 表tb1001有辅助索引idx_c1(c1) 和辅助索引idc_c2(c2)
select * from tb1001 where c1>'xxx' or c2<'xxx';

其操作步骤为：

1、使用idx_c1索引获取到满足条件的[c1,id]记录，再按照id进行排序
2、使用idx_c1索引获取到满足条件的[c1,id]记录，再按照id进行排序
3、将步骤1和步骤2的已按id排序后数据进行合并去重id。
4、按照id回表查找并返回

伪代码为：

select t2.* from (
select id from tb1001 where c1>'xxx'
order by id
union
select id from tb1001 where c2>'xxx'
order by id
) as t1
inner join tb1001 as t2
on t1.id=t2.id

在创建索引idx_c1(id)时，其等价为idx_c1(c1,id),对c1列进行范围查询返回数据的数据按照c1+id排序，在id列上是无序的，因此union操作前需先对两个中间结果集排序。

index merge union相关优化
在禁用index merge特性时，可以通过sql将or操作改写为union all操作，使查询同时使用多个索引。

如上面使用index merge union算法的查询，可以改写为：

#改写前：
select * from tb1001 where c1='xxx' or c2='xxx';

# 改写后
select t2.* from (
select id from tb1001 where c1='xxx'
union all
select id from tb1001 where c2='xxx' and (c1<>'xxx' or c1 is null)
) as t1
inner join tb1001 as t2
on t1.id=t2.id

ps: 将idx_c2(c2)改写为idx_c2_c2(c1,c2)能在union操作前避免回表查询。