当前位置:Gxlcms > 数据库问题 > MySQL Execute Plan--Index Merge特性

MySQL Execute Plan--Index Merge特性

时间:2021-07-01 10:21:17 帮助过:13人阅读

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

假设表TB1001上C1和C2列分别有单列索引,如对下面查询:

SELECT * FROM TB1001 WHERE C1=XXX OR C2=XXX;

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

在MySQL 5.5版本中引入Index Merge特性,允许:
查询对一个表上多个索引进行范围扫描并将多个扫描结果进行合并(UNION/INTERSECT)。

Index Merge三种合并算法:

1Index Merge Intersect:对多个结果集求交集
2Index Merge Union:对多个结果集求UNION集合(无需对结果集排序)
3Index 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操作前避免回表查询。

MySQL Execute Plan--Index Merge特性

标签:随机   读取   font   获取   ***   计划   push   col   nod   

人气教程排行