GaussDB(DWS)性能调优:LEFT JOIN不能走索引扫描的性能优化
DWS的MPP分布式架构,数据重分布动作导致NestLoop+IndexScan的使用场景受限,特别是LEFT JOIN场景下。针对LEFT JOIN特定场景,本文提供一种改写方案实现业务SQL性能提升。
1 预置条件
DROP TABLE tab1;
DROP TABLE tab2;
CREATE TABLE tab1(a int, b int, sysid text) DISTRIBUTE BY HASH(a);
CREATE TABLE tab2(a int, b int, sysid text) DISTRIBUTE BY HASH(a);
INSERT INTO tab1 VALUES (generate_series(1, 2^10), generate_series(1, 2^10), sys_guid());
INSERT INTO tab2 SELECT a, b, sysid FROM tab1;
INSERT INTO tab2 SELECT a+2^10, b+2^10, sys_guid() FROM tab2;
INSERT INTO tab2 SELECT a+2^11, b+2^11, sys_guid() FROM tab2;
INSERT INTO tab2 SELECT a+2^12, b+2^12, sys_guid() FROM tab2;
INSERT INTO tab2 SELECT a+2^13, b+2^13, sys_guid() FROM tab2;
INSERT INTO tab2 SELECT a+2^14, b+2^14, sys_guid() FROM tab2;
INSERT INTO tab2 SELECT a+2^15, b+2^15, sys_guid() FROM tab2;
INSERT INTO tab2 SELECT a+2^16, b+2^16, sys_guid() FROM tab2;
INSERT INTO tab2 SELECT a+2^17, b+2^17, sys_guid() FROM tab2;
INSERT INTO tab2 SELECT a+2^18, b+2^18, sys_guid() FROM tab2;
CREATE INDEX idx1 ON tab2 USING btree(sysid);
ANALYZE tab1;
ANALYZE tab2;
2 原始查询
2.1 原始查询语句
SELECT
t1.*
FROM tab1 t1
LEFT JOIN tab2 t2 ON t1.sysid = t2.sysid
WHERE t1.a IN(1, 1001)
ORDER BY t1.a;
2.2 执行分析
原始语句performance信息(完整信息见附件《LEFT JOIN原始语句performance信息》)概要如下
从执行信息上看,主要是因为tab2表的全表扫描(Seq Scan)+重分布(Streaming(type: REDISTRIBUTE))+随后的HashJoin,这三个步骤较长。占整体耗时的99%以上
仔细分析,可以发现上述执行有如下特征
1)tab1的结果集较小
2)tab2的数据较大
3)tab1和tab2的关联结果集较小
4) tab2的关联列不是分布列,因此LEFT JOIN的时候tab2必须做重分布
5)tab2表的关联字段sysid上存在索引
通过上述分析,可以确定如果tab2走了sysid字段的索引扫描,执行的性能会导入提升。但是因为如上特征tab2上层算子必须为Streaming,导致tab2无法走索引。
3 语句改写
我们尝试通过SQL改写实现对tab2表在关联字段sysid上执行Index Scan的方式提升性能。
从LEFT JOIN的语义上讲,tab2中满足匹配条件t1.sysid = t2.sysid的记录才会对关联结果产生影响,所以我们的改写实际上分为两步
1))把tab1和tab2做一个INNER JOIN,把tab2中满足等值关联条件的记录筛选出来。
tab1 INNER JOIN tab2执行时,tab1可以现在broadcast,然后tab2扫描的时候就可以走Index Scan。预期此步骤输出的结果集较小
2)把上一步的结果再和tab1做LEFT JOIN
3.1 改写语句
SELECT
t1.*
FROM tab1 t1
LEFT JOIN (SELECT t2.* /* 先把tab2跟tab1做INNER JOIN,从tab2中刷选出可以跟tab1匹配上的记录 */
FROM tab2 t2
INNER JOIN tab1 t1 ON t1.sysid = t2.sysid
WHERE t1.a IN(1, 1001)
) t2 ON t1.sysid = t2.sysid
WHERE t1.a IN(1, 1001)
ORDER BY t1.a;
3.2 执行分析
改写后语句performance信息(完整信息见附件《LEFT JOIN改写语句performance信息》)概要如下
对比执行耗时分析,可以发现优化后的SQL语句执行耗时在7.5ms左右,比优化前(513.5ms)提升近百倍。
4. 场景扩展
当tab1为一个子查询,或者原始语句可以提炼出一个类似tab1的子查询时,上述思路同样适用。比如原始查询语句如下
SELECT
*
FROM f1_stg.odst_hst o
INNER JOIN f1_stg.fwwiptransaction a ON o.txnid = a.sysid
INNER JOIN f1_stg.fwwipstephistory c ON a.wipstepdef = c.sysid
LEFT JOIN f1_stg.fabwiptransactionext g2 ON c.trackintxn = g2.parent
WHERE o.activity IN ('Job', 'Rule', 'Smart');
当表f1_stg.fabwiptransactionext很大,且在字段parent有索引,同时o a c三个表的关联结果集比较小,可以把o a c三个表提炼成一个类似tab1的一个子查询,然后套用如上的逻辑把SQL语句改写为
SELECT
*
FROM f1_stg.odst_hst o
INNER JOIN f1_stg.fwwiptransaction a ON a.txnid = a.sysid
INNER JOIN f1_stg.fwwipstephistory c ON a.wipstepdef = c.sysid
LEFT JOIN (SELECT g2.*
FROM f1_stg.fabwiptransactionext g2
INNER f1_stg.fwwipstephistory c ON c.trackintxn = g2.parent
INNER f1_stg.fwwiptransaction a ON a.wipstepdef = c.sysid
INNER f1_stg.odst_hst o ON o.txnid = a.sysid
WHERE o.activity IN ('Job', 'Rule', 'Smart')
) g2 ON c.trackintxn = g2.parent
WHERE o.activity IN ('Job', 'Rule', 'Smart');
或者
WITH t AS(
SELECT
*
FROM f1_stg.odst_hst o
INNER JOIN f1_stg.fwwiptransaction a ON a.txnid = a.sysid
INNER JOIN f1_stg.fwwipstephistory c ON a.wipstepdef = c.sysid
WHERE o.activity IN ('Job', 'Rule', 'Smart')
)
SELECT
*
FROM t
LEFT JOIN (SELECT g2.*
FROM f1_stg.fabwiptransactionext g2
INNER JOIN t ON t.trackintxn = g2.parent
) g2 ON t.trackintxn = g2.parent;
- 点赞
- 收藏
- 关注作者
评论(0)