GreatSQL 优化技巧：将 MINUS 改写为标量子查询

栀子花开是离别

GreatSQL 优化技巧：将 MINUS 改写为标量子查询

前言

minus  指令运用在两个 SQL 语句上，取两个语句查询结果集的差集。它先找出第一个 SQL 所产生的结果，然后看这些结果有没有在第二个 SQL 的结果中，如果在，那这些数据就被去除，不会在最后的结果中出现，第二个 SQL 结果集比第一个SQL结果集多的数据也会被抛弃。
这两天的优化工作中遇到这样一种案例，第一个SQL语句结果集很小，第二个SQL语句结果集很大，这种情况下我们怎么来优化处理呢？
实验

创建测试表

1. CREATE TABLE t1(id int primary key auto_increment,
2. subscriber_id decimal(20, 0) not null,
3. member_num varchar(20) not null,
4. effectdate datetime,
5. expirydate datetime,
6. create_date datetime,
7. key idx_subscriber(subscriber_id));

复制代码

创建存储过程，向测试插入50万数据。（实际生产案例中表中数据有几千万）
注意下面的存储过程中，是GreatSQL在Oracle模式下创建的，GreatSQL实现了大量的Oracle语法兼容，比如下面存储过程中遇到的日期加减，add_months函数，while loop循环等，数据库由Oracle向GreatSQL迁移时，会节省很多代码改造工作。

1. set sql_mode=oracle;
2. delimiter //
3. create or replace procedure p1() as
4. p1 int :=1;
5. n1 int;
6. d1 datetime;
7. begin
8.    while p1<=500000 loop
9.        n1:=round(rand()*500000);
10.        d1:=to_date('2016-01-01','yyyy-mm-dd') + round(rand()*3000);
11.        insert into t1(subscriber_id,member_num,effectdate,expirydate,create_date) values(n1,concat('m_',n1),last_day(d1)+1,add_months(last_day(d1)+1,100),d1);
12.        set p1=p1+1;
13.    end loop;
14. end;
15. //
16. delimiter ;

复制代码

这条SQL是根据生产环境使用的语句简化而来的，只为突出本文要说明的知识点。
此SQL的执行计划如下：

1. SELECT DISTINCT subscriber_id, member_num
2.   FROM t1
3. WHERE create_date >= '2024-02-01'
4.    AND create_date < '2024-03-01'
5.    AND to_char(effectdate, 'yyyymm') > '202402'
6. minus
7. SELECT DISTINCT subscriber_id, member_num
8.   FROM t1
9. WHERE 202402 BETWEEN to_char(effectdate, 'yyyymm') AND
10.        to_char(expirydate, 'yyyymm');

复制代码

从执行计划看出，SQL总体耗时2.47s。 第一部分的查询结果集有4855条，耗时221.962ms，第二部分的查询结果集有307431条，耗时1571.682ms。
优化分析：
首先第一部分create_date加上索引会提升查询效率，因为只需要查询一个月的数据，而此SQL耗时最多的是第二部分，重在第二部分的优化处理。
第二部分查询结果集在做minus运算时大部分记录都是要被抛弃的，查询出来再被抛弃相当于做了无用功，而SQL优化的核心思想就是在于减少IO，那我们要做的就是想办法省去第二部分SQL的全面查询，只需要验证第一部分的查询结果集是否在第二部分查询结果中存在就好了。
那如何验证呢?
把第一部分select的列值传给第二部分作为where条件去查找，只要能查到，无论几条都算在第二部分存在，这部分数据就要被去除，查不到就是在第二部分不存在，数据保留在最终结果集。根据这个逻辑我想到了标量子查询的妙用。
标量子查询改写参考：

1. greatsql> explain analyze
2.     -> select distinct subscriber_id, member_num
3.     ->   from t1
4.     ->  where create_date >= '2024-02-01'
5.     ->    and create_date < '2024-03-01'
6.     ->    and to_char(effectdate, 'yyyymm') > '202402'
7.     -> minus
8.     -> select distinct subscriber_id, member_num
9.     ->   from t1
10.     ->  where 202402 between to_char(effectdate, 'yyyymm') and
11.     ->        to_char(expirydate, 'yyyymm')\G
12. *************************** 1. row ***************************
13. EXPLAIN: -> Table scan on <except temporary>  (cost=168492.31..169186.99 rows=55375) (actual time=2420.123..2420.896 rows=1758 loops=1)
14.     -> Except materialize with deduplication  (cost=168492.30..168492.30 rows=55375) (actual time=2420.121..2420.121 rows=4855 loops=1)
15.         -> Table scan on <temporary>  (cost=55858.24..56552.91 rows=55375) (actual time=221.965..223.384 rows=4855 loops=1)
16.             -> Temporary table with deduplication  (cost=55858.23..55858.23 rows=55375) (actual time=221.962..221.962 rows=4855 loops=1)
17.                 -> Filter: ((t1.create_date >= TIMESTAMP'2024-02-01 00:00:00') and (t1.create_date < TIMESTAMP'2024-03-01 00:00:00') and (to_char(t1.effectdate,'yyyymm') > '202402'))  (cost=50320.70 rows=55375) (actual time=0.118..217.497 rows=4875 loops=1)
18.                     -> Table scan on t1  (cost=50320.70 rows=498477) (actual time=0.084..179.826 rows=500000 loops=1)
19.         -> Table scan on <temporary>  (cost=100168.41..106401.86 rows=498477) (actual time=1520.965..1571.682 rows=307431 loops=1)
20.             -> Temporary table with deduplication  (cost=100168.40..100168.40 rows=498477) (actual time=1520.963..1520.963 rows=307431 loops=1)
21.                 -> Filter: (202402 between to_char(t1.effectdate,'yyyymm') and to_char(t1.expirydate,'yyyymm'))  (cost=50320.70 rows=498477) (actual time=0.123..934.617 rows=492082 loops=1)
22.                     -> Table scan on t1  (cost=50320.70 rows=498477) (actual time=0.104..716.919 rows=500000 loops=1)
24. 1 row in set (2.47 sec)

复制代码

改后SQL的执行计划如下：

1. select distinct subscriber_id, member_num
2.   from (select a.subscriber_id,
3.                a.member_num,
4.                (select count(*) cnt
5.                   from t1 b
6.                  where a.subscriber_id = b.subscriber_id
7.                    and a.member_num = b.member_num
8.                    and 202402 between to_char(effectdate, 'yyyymm') and
9.                        to_char(expirydate, 'yyyymm')) as cnt
10.           from t1 a
11.          where create_date >= '2024-02-01'
12.            and create_date < '2024-03-01'
13.            and to_char(effectdate, 'yyyymm') > '202402')
14. where cnt = 0

复制代码

从执行计划可以看出，子查询执行次数依赖于主查询，执行了4875次，因为subscriber_id列选择性很好，所以每次查询效率很高。SQL总体耗时0.26秒，而原SQL耗时2.47s，性能提升了将近10倍。在实际生产案例中第二部分结果集有5000万左右，第一部分结果集只有几十条，SQL执行半天都跑不出结果，改造后几乎秒出。
提醒一点，注意NULL值比较，当select 列表中的部分列存在NULL值时就不能直接用等号（=）关联来判断了，得用is NULL来判断，本案例不涉及此问题，语句是否等价有时需要结合业务，具体情况具体分析。
结论：

本文提供了一种minus语句的优化方法，将minus转化为标量子查询表达，这种优化方式适用于第一部分查询结果集比较小，查询的列比较少的情况，且要结合业务判读是否需要对NULL值进行判断。优化时一般避免使用标量子查询，因为标量子查询会构造天然的嵌套循环连接，但也并不是说标量子查询一定不可用，还是要从根儿上考虑，优化核心思想，减少IO是要点。

Enjoy GreatSQL
来源:https://www.cnblogs.com/greatsql/p/18107817
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！