您可以使用Semi-Join半连接优化子查询,减少查询次数,提高查询性能,本文将介绍Semi-Join半连接的基本信息和操作方法。
前提条件
PolarDB集群版本需为PolarDB MySQL 8.0且Revision version为8.0.1.1.2或以上,您可以参见查询版本号确认集群版本。
背景信息
MySQL 5.6.5引入了Semi-Join半连接,当外表在内表中找到匹配的记录之后,Semi-Join会返回外表中的记录。但即使在内表中找到多条匹配的记录,外表也只会返回已经存在于外表中的记录。而对于子查询,父表的每个符合条件的元组都要执行一轮子查询,效率比较低下。此时使用半连接操作优化子查询,会减少查询次数,提高查询性能,其主要思路是将子查询上拉到父查询中,这样子查询的表和父查询中的表是并列关系,父表的每个符合条件的元组,只需要在子表中找符合条件的元组即可,所以效率会大大提高。

策略
Semi-Join主要使用了如下策略:
- DuplicateWeedout Strategy
该策略创建由
row id
组成唯一ID的临时表,再通过该唯一ID达到去重目的。 - Materialization Strategy
该策略将
nested tables
物化到临时表中,再通过查找物化表或者遍历物化表查找外表的方法达到去重目的。 - Firstmatch Strategy
该策略采用顺序查找表的方式,找到第一个匹配的记录后立即跳转到最后一个外表,并对外表的下一条记录执行JOIN操作,从而达到去重的目的
- LooseScan Strategy
该策略对内表基于索引(Index)进行分组,分组后与外表执行JOIN(进行Condition的匹配)操作,如果存在匹配的记录,则提取外表的记录,内表选取下一个分组继续进行计算,从而达到去重目的。
语法
Semi-Join通常使用IN或EXISTS作为连接条件。
IN SELECT * FROM Employee WHERE DeptName IN ( SELECT DeptName FROM Dept )
EXISTS SELECT * FROM Employee WHERE EXISTS ( SELECT 1 FROM Dept WHERE Employee.DeptName = Dept.DeptName )
并行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 | <gather1> | 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 | <subquery2> | NULL | eq_ref | <auto_key> | <auto_key> | 103 | sj.t1.c1 | 1 | 100.00 | NULL |
| 2 | MATERIALIZED | t2 | p0,p1 | ALL | c1,c2 | NULL | NULL | NULL | 100401 | 33.33 | Using where |
+----+--------------+-------------+------------+--------+---------------+------------+------
在文档使用中是否遇到以下问题
更多建议
匿名提交