GreatSQL 優(yōu)化技巧：將 MINUS 改寫為標(biāo)量子查詢
前言
minus 指令運(yùn)用在兩個(gè) SQL 語(yǔ)句上，取兩個(gè)語(yǔ)句查詢結(jié)果集的差集。它先找出第一個(gè) SQL 所產(chǎn)生的結(jié)果，然后看這些結(jié)果有沒有在第二個(gè) SQL 的結(jié)果中，如果在，那這些數(shù)據(jù)就被去除，不會(huì)在最后的結(jié)果中出現(xiàn)，第二個(gè) SQL 結(jié)果集比第一個(gè)SQL結(jié)果集多的數(shù)據(jù)也會(huì)被拋棄。 這兩天的優(yōu)化工作中遇到這樣一種案例，第一個(gè)SQL語(yǔ)句結(jié)果集很小，第二個(gè)SQL語(yǔ)句結(jié)果集很大，這種情況下我們?cè)趺磥?lái)優(yōu)化處理呢？
實(shí)驗(yàn)
創(chuàng)建測(cè)試表
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));創(chuàng)建存儲(chǔ)過程，向測(cè)試插入50萬(wàn)數(shù)據(jù)。（實(shí)際生產(chǎn)案例中表中數(shù)據(jù)有幾千萬(wàn)）
注意下面的存儲(chǔ)過程中，是GreatSQL在Oracle模式下創(chuàng)建的，GreatSQL實(shí)現(xiàn)了大量的Oracle語(yǔ)法兼容，比如下面存儲(chǔ)過程中遇到的日期加減，add_months函數(shù)，while loop循環(huán)等，數(shù)據(jù)庫(kù)由Oracle向GreatSQL遷移時(shí)，會(huì)節(jié)省很多代碼改造工作。
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 ;這個(gè)表create_date列的數(shù)據(jù)是從2016年1月到2024年3月的數(shù)據(jù)，使用了隨機(jī)值，保證每個(gè)月的數(shù)據(jù)量相近，subscriber_id也是隨機(jī)值生成的，選擇性很好，這個(gè)模型數(shù)據(jù)與生產(chǎn)環(huán)境差不多。
執(zhí)行下面這個(gè)SQL語(yǔ)句：
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');這條SQL是根據(jù)生產(chǎn)環(huán)境使用的語(yǔ)句簡(jiǎn)化而來(lái)的，只為突出本文要說明的知識(shí)點(diǎn)。
此SQL的執(zhí)行計(jì)劃如下：
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)從執(zhí)行計(jì)劃看出，SQL總體耗時(shí)2.47s。 第一部分的查詢結(jié)果集有4855條，耗時(shí)221.962ms，第二部分的查詢結(jié)果集有307431條，耗時(shí)1571.682ms。
優(yōu)化分析：
首先第一部分create_date加上索引會(huì)提升查詢效率，因?yàn)橹恍枰樵円粋€(gè)月的數(shù)據(jù)，而此SQL耗時(shí)最多的是第二部分，重在第二部分的優(yōu)化處理。
第二部分查詢結(jié)果集在做minus運(yùn)算時(shí)大部分記錄都是要被拋棄的，查詢出來(lái)再被拋棄相當(dāng)于做了無(wú)用功，而SQL優(yōu)化的核心思想就是在于減少IO，那我們要做的就是想辦法省去第二部分SQL的全面查詢，只需要驗(yàn)證第一部分的查詢結(jié)果集是否在第二部分查詢結(jié)果中存在就好了。
那如何驗(yàn)證呢?
把第一部分select的列值傳給第二部分作為where條件去查找，只要能查到，無(wú)論幾條都算在第二部分存在，這部分?jǐn)?shù)據(jù)就要被去除，查不到就是在第二部分不存在，數(shù)據(jù)保留在最終結(jié)果集。根據(jù)這個(gè)邏輯我想到了標(biāo)量子查詢的妙用。
標(biāo)量子查詢改寫參考：
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的執(zhí)行計(jì)劃如下：
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)從執(zhí)行計(jì)劃可以看出，子查詢執(zhí)行次數(shù)依賴于主查詢，執(zhí)行了4875次，因?yàn)閟ubscriber_id列選擇性很好，所以每次查詢效率很高。SQL總體耗時(shí)0.26秒，而原SQL耗時(shí)2.47s，性能提升了將近10倍。在實(shí)際生產(chǎn)案例中第二部分結(jié)果集有5000萬(wàn)左右，第一部分結(jié)果集只有幾十條，SQL執(zhí)行半天都跑不出結(jié)果，改造后幾乎秒出。
提醒一點(diǎn)，注意NULL值比較，當(dāng)select 列表中的部分列存在NULL值時(shí)就不能直接用等號(hào)（=）關(guān)聯(lián)來(lái)判斷了，得用is NULL來(lái)判斷，本案例不涉及此問題，語(yǔ)句是否等價(jià)有時(shí)需要結(jié)合業(yè)務(wù)，具體情況具體分析。
結(jié)論：
本文提供了一種minus語(yǔ)句的優(yōu)化方法，將minus轉(zhuǎn)化為標(biāo)量子查詢表達(dá)，這種優(yōu)化方式適用于第一部分查詢結(jié)果集比較小，查詢的列比較少的情況，且要結(jié)合業(yè)務(wù)確認(rèn)是否需要對(duì)NULL值進(jìn)行判斷。優(yōu)化時(shí)一般避免使用標(biāo)量子查詢，因?yàn)闃?biāo)量子查詢會(huì)構(gòu)造天然的嵌套循環(huán)連接，但也并不是說標(biāo)量子查詢一定不可用，還是要從根兒上考慮，優(yōu)化核心思想，減少IO是要點(diǎn)。
