欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL Execute Plan--Index Merge特性

程序员文章站 2022-06-27 22:21:40
Index Merge特性 在MySQL 5.5之前版本中,查询或子查询被限制在一个表只能使用一个索引(回表查询除外)。 假设表TB1001上C1和C2列分别有单列索引,如对下面查询: SELECT * FROM TB1001 WHERE C1='XXX' OR C2='XXX'; 单独使用任一索引 ......

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操作前避免回表查询。