GreatSQL 优化技巧：将 MINUS 改写为标量子查询
前言
minus 指令运用在两个 SQL 语句上，取两个语句查询结果集的差集。它先找出第一个 SQL 所产生的结果，然后看这些结果有没有在第二个 SQL 的结果中，如果在，那这些数据就被去除，不会在最后的结果中出现，第二个 SQL 结果集比第一个 SQL 结果集多的数据也会被抛弃。这两天的优化工作中遇到这样一种案例，第一个 SQL 语句结果集很小，第二个 SQL 语句结果集很大，这种情况下我们怎么来优化处理呢？
实验
创建测试表
CREATE TABLE t1(id int primary key auto_increment,subscriber_id decimal(20, 0) not null,member_num varchar(20) not null,effectdate datetime,expirydate datetime,create_date datetime,key idx_subscriber(subscriber_id));
复制代码
创建存储过程，向测试插入 50 万数据。（实际生产案例中表中数据有几千万）
注意下面的存储过程中，是 GreatSQL 在 Oracle 模式下创建的，GreatSQL 实现了大量的 Oracle 语法兼容，比如下面存储过程中遇到的日期加减，add_months 函数，while loop 循环等，数据库由 Oracle 向 GreatSQL 迁移时，会节省很多代码改造工作。
set sql_mode=oracle;delimiter //create or replace procedure p1() as p1 int :=1; n1 int; d1 datetime;begin while p1<=500000 loop n1:=round(rand()*500000); d1:=to_date('2016-01-01','yyyy-mm-dd') + round(rand()*3000); 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); set p1=p1+1; end loop;end;//delimiter ;
复制代码
这个表 create_date 列的数据是从 2016 年 1 月到 2024 年 3 月的数据，使用了随机值，保证每个月的数据量相近，subscriber_id 也是随机值生成的，选择性很好，这个模型数据与生产环境差不多。
执行下面这个 SQL 语句：
SELECT DISTINCT subscriber_id, member_num FROM t1 WHERE create_date >= '2024-02-01' AND create_date < '2024-03-01' AND to_char(effectdate, 'yyyymm') > '202402'minusSELECT DISTINCT subscriber_id, member_num FROM t1 WHERE 202402 BETWEEN to_char(effectdate, 'yyyymm') AND to_char(expirydate, 'yyyymm');
复制代码
这条 SQL 是根据生产环境使用的语句简化而来的，只为突出本文要说明的知识点。
此 SQL 的执行计划如下：
greatsql> explain analyze -> select distinct subscriber_id, member_num -> from t1 -> where create_date >= '2024-02-01' -> and create_date < '2024-03-01' -> and to_char(effectdate, 'yyyymm') > '202402' -> minus -> select distinct subscriber_id, member_num -> from t1 -> where 202402 between to_char(effectdate, 'yyyymm') and -> to_char(expirydate, 'yyyymm')\G*************************** 1. row ***************************EXPLAIN: -> Table scan on <except temporary> (cost=168492.31..169186.99 rows=55375) (actual time=2420.123..2420.896 rows=1758 loops=1) -> Except materialize with deduplication (cost=168492.30..168492.30 rows=55375) (actual time=2420.121..2420.121 rows=4855 loops=1) -> Table scan on <temporary> (cost=55858.24..56552.91 rows=55375) (actual time=221.965..223.384 rows=4855 loops=1) -> Temporary table with deduplication (cost=55858.23..55858.23 rows=55375) (actual time=221.962..221.962 rows=4855 loops=1) -> 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) -> Table scan on t1 (cost=50320.70 rows=498477) (actual time=0.084..179.826 rows=500000 loops=1) -> Table scan on <temporary> (cost=100168.41..106401.86 rows=498477) (actual time=1520.965..1571.682 rows=307431 loops=1) -> Temporary table with deduplication (cost=100168.40..100168.40 rows=498477) (actual time=1520.963..1520.963 rows=307431 loops=1) -> 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) -> Table scan on t1 (cost=50320.70 rows=498477) (actual time=0.104..716.919 rows=500000 loops=1)
1 row in set (2.47 sec)
复制代码
从执行计划看出，SQL 总体耗时 2.47s。 第一部分的查询结果集有 4855 条，耗时 221.962ms，第二部分的查询结果集有 307431 条，耗时 1571.682ms。
优化分析：
首先第一部分 create_date 加上索引会提升查询效率，因为只需要查询一个月的数据，而此 SQL 耗时最多的是第二部分，重在第二部分的优化处理。
第二部分查询结果集在做 minus 运算时大部分记录都是要被抛弃的，查询出来再被抛弃相当于做了无用功，而 SQL 优化的核心思想就是在于减少 IO，那我们要做的就是想办法省去第二部分 SQL 的全面查询，只需要验证第一部分的查询结果集是否在第二部分查询结果中存在就好了。
那如何验证呢?
把第一部分 select 的列值传给第二部分作为 where 条件去查找，只要能查到，无论几条都算在第二部分存在，这部分数据就要被去除，查不到就是在第二部分不存在，数据保留在最终结果集。根据这个逻辑我想到了标量子查询的妙用。
标量子查询改写参考：
select distinct subscriber_id, member_num from (select a.subscriber_id, a.member_num, (select count(*) cnt from t1 b where a.subscriber_id = b.subscriber_id and a.member_num = b.member_num and 202402 between to_char(effectdate, 'yyyymm') and to_char(expirydate, 'yyyymm')) as cnt from t1 a where create_date >= '2024-02-01' and create_date < '2024-03-01' and to_char(effectdate, 'yyyymm') > '202402') where cnt = 0
复制代码
改后 SQL 的执行计划如下：
greatsql> explain analyze -> select distinct subscriber_id, member_num -> from (select a.subscriber_id, -> a.member_num, -> (select count(*) cnt -> from t1 b -> where a.subscriber_id = b.subscriber_id -> and a.member_num = b.member_num -> and 202402 between to_char(effectdate, 'yyyymm') and -> to_char(expirydate, 'yyyymm')) as cnt -> from t1 a -> where create_date >= '2024-02-01' -> and create_date < '2024-03-01' -> and to_char(effectdate, 'yyyymm') > '202402') -> where cnt = 0\G*************************** 1. row ***************************EXPLAIN: -> Table scan on <temporary> (cost=3172.53..3235.95 rows=4875) (actual time=168.555..168.775 rows=1758 loops=1) -> Temporary table with deduplication (cost=3172.51..3172.51 rows=4875) (actual time=168.553..168.553 rows=1758 loops=1) -> Index lookup on alias_temp_-1556603461854822391 using <auto_key0> (cnt=0) (cost=2681.86..2685.01 rows=10) (actual time=166.656..167.178 rows=1765 loops=1) -> Materialize (cost=2681.51..2681.51 rows=4875) (actual time=166.649..166.649 rows=4875 loops=1) -> Filter: (to_char(a.effectdate,'yyyymm') > '202402') (cost=2194.01 rows=4875) (actual time=0.380..45.477 rows=4875 loops=1) -> Index range scan on a using idx_creatdate over ('2024-02-01 00:00:00' <= create_date < '2024-03-01 00:00:00'), with index condition: ((a.create_date >= TIMESTAMP'2024-02-01 00:00:00') and (a.create_date < TIMESTAMP'2024-03-01 00:00:00')) (cost=2194.01 rows=4875) (actual time=0.344..43.143 rows=4875 loops=1) -> Select #3 (subquery in projection; dependent) -> Aggregate: count(0) (cost=0.42 rows=1) (actual time=0.022..0.022 rows=1 loops=4875) -> Filter: ((a.member_num = b.member_num) and (202402 between to_char(b.effectdate,'yyyymm') and to_char(b.expirydate,'yyyymm'))) (cost=0.40 rows=0.2) (actual time=0.019..0.021 rows=1 loops=4875) -> Index lookup on b using idx_subscriber (subscriber_id=a.subscriber_id) (cost=0.40 rows=2) (actual time=0.018..0.019 rows=2 loops=4875)
1 row in set, 2 warnings (0.26 sec)
复制代码
从执行计划可以看出，子查询执行次数依赖于主查询，执行了 4875 次，因为 subscriber_id 列选择性很好，所以每次查询效率很高。SQL 总体耗时 0.26 秒，而原 SQL 耗时 2.47s，性能提升了将近 10 倍。在实际生产案例中第二部分结果集有 5000 万左右，第一部分结果集只有几十条，SQL 执行半天都跑不出结果，改造后几乎秒出。
提醒一点，注意 NULL 值比较，当 select 列表中的部分列存在 NULL 值时就不能直接用等号（=）关联来判断了，得用 is NULL 来判断，本案例不涉及此问题，语句是否等价有时需要结合业务，具体情况具体分析。
结论：
本文提供了一种 minus 语句的优化方法，将 minus 转化为标量子查询表达，这种优化方式适用于第一部分查询结果集比较小，查询的列比较少的情况，且要结合业务判读是否需要对 NULL 值进行判断。优化时一般避免使用标量子查询，因为标量子查询会构造天然的嵌套循环连接，但也并不是说标量子查询一定不可用，还是要从根儿上考虑，优化核心思想，减少 IO 是要点。
评论