并行Semi-Join性能提升
对于选择Semi-Join策略的查询,PolarDB对Semi-Join所有策略实现了并行加速,通过拆分Semi-Join的任务,多线程模型并行运行任务集,强化去重能力,使查询性能得到了显著的提升,以Q20为例。SELECT
s_name,
s_address
FROM
supplier, nation
WHERE
s_suppkey IN (
SELECT
ps_suppkey
FROM
partsupp
WHERE
ps_partkey IN (
SELECT
p_partkey
FROM
part
WHERE
p_name LIKE '[COLOR]%'
)
AND ps_availqty > (
SELECT
0.5 * SUM(l_quantity)
FROM
lineitem
WHERE
l_partkey = ps_partkey
AND l_suppkey = ps_suppkey
AND l_shipdate >= date('[DATE]’)
AND l_shipdate < date('[DATE]’) + interval ‘1’ year
)
)
AND s_nationkey = n_nationkey
AND n_name = '[NATION]'
ORDER BY
s_name;
本文例子中将物化处理提前,并且以并行度(DOP)为32执行,后续的处理通过共享之前的物化表,同样充分发挥CPU的处理能力,将主查询的并行能力最大化,在如下的执行计划中,在标准TPCH
SCALE为1G的数据量场景下,开启并行后,双层并行处理能力:
在标准TPCH SCALE为1G的数据量场景下,串行的执行时间:
并行开启情况下的执行时间:
在如下自定义SQL并行中,使用了Semi-Join下推的并行方式,在max_parallel_degree=32的情况下,执行时间从2.59s减少到0.34s:mysql> SELECT c1,d1 FROM t1 WHERE c1 IN ( SELECT t2.c1 FROM t2 WHERE t2.c1 = 'f' OR t2.c2 < 'y' ) AND t1.c1 AND d1 > '1900-1-1' LIKE "R1%" ORDER BY t1.c1 DESC, t1.d1 DESC;
Empty set, 1024 warnings (0.34 sec)
mysql> SET max_parallel_degree=0;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT c1,d1 FROM t1 WHERE c1 IN ( SELECT t2.c1 FROM t2 WHERE t2.c1 = 'f' OR t2.c2 < 'y' ) AND t1.c1 AND d1 > '1900-1-1' LIKE "R1%" ORDER BY t1.c1 DESC, t1.d1 DESC;
Empty set, 65535 warnings (2.69 sec)
mysql> EXPLAIN SELECT c1,d1 FROM t1 WHERE c1 IN ( SELECT t2.c1 FROM t2 WHERE t2.c1 = 'f' OR t2.c2 < 'y' ) AND t1.c1 AND d1 > '1900-1-1' LIKE "R1%" ORDER BY t1.c1 DESC, t1.d1 DESC;
+----+--------------+-------------+------------+--------+---------------+------------+---------+----------+--------+----------+---------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+-------------+------------+--------+---------------+------------+---------+----------+--------+----------+---------------------------------------------------------+
| 1 | SIMPLE | | NULL | ALL | NULL | NULL | NULL | NULL | 33464 | 100.00 | Merge sort |
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 62802 | 30.00 | Parallel scan (32 workers); Using where; Using filesort |
| 1 | SIMPLE | | NULL | eq_ref | | | 103 | sj.t1.c1 | 1 | 100.00 | NULL |
| 2 | MATERIALIZED | t2 | p0,p1 | ALL | c1,c2 | NULL | NULL | NULL | 100401 | 33.33 | Using where |
+----+--------------+-------------+------------+--------+---------------+------------+------