GreatSQL 優化技巧:將 MINUS 改寫為標量子查詢

来源:https://www.cnblogs.com/greatsql/p/18107817
-Advertisement-
Play Games

GreatSQL 優化技巧:將 MINUS 改寫為標量子查詢 前言 minus 指令運用在兩個 SQL 語句上,取兩個語句查詢結果集的差集。它先找出第一個 SQL 所產生的結果,然後看這些結果有沒有在第二個 SQL 的結果中,如果在,那這些數據就被去除,不會在最後的結果中出現,第二個 SQL 結果集 ...


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'
minus
SELECT 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是要點。


Enjoy GreatSQL

您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • windows 11 家庭版/家庭單語言版/教育版/專業版/專業教育版/專業工作站版 簡體中文 ed2k://|file|zh-cn_windows_11_consumer_editions_version_23h2_updated_march_2024_x64_dvd_bcbf6ac6.iso|6 ...
  • 本系列為《Learning eBPF》一書的翻譯系列。 (內容並非機翻,部分夾帶私貨)筆者學習自用,歡迎大家討論學習。 ...
  • 實驗介紹: 活動目錄簡稱AD,在裡面創建對象包括組織單位對象,用戶對象,電腦對象 實驗環境: 兩台虛擬機包括一臺dns1域控制器,一臺虛擬機 一:在AD管理中心建“組織單位”對象財務部 1.在dns1打開AD管理中心,右鍵本地,新建組織單位 2.輸入財務部,這裡用財務部做例子也可輸入其他內容 二: ...
  • 緩存穿透、擊穿和雪崩是緩存使用中的常見問題,對它們的理解和相應的解決方法對於維護系統性能和穩定性至關重要。 1.緩存穿透 定義:當客戶端請求的數據在緩存中和資料庫中都不存在時,該請求會直接打到資料庫上,這種情況稱為緩存穿透。如果持續請求這樣的數據,會給資料庫帶來不必要的壓力。 解決方法: 緩存空對象 ...
  • 1.安裝Docker - 2024.03.28 官方手冊 清華大學開源軟體鏡像站 | 可從主頁找到Docker資源 # Add Docker's official GPG key: sudo apt-get update sudo apt-get install ca-certificates cu ...
  • 熟悉鍵盤操作可以極大地提高操作效率,熟練的 Linux 用戶幾乎不需要使用滑鼠,僅用鍵盤就可以更快且更方便地完成所有操作。 Linux 命令解釋器(也可以叫 Shell,終端,命令行等)也有很多快捷鍵,熟練掌握可以極大的提高操作效率。在本文整理一些最常用的快捷鍵。 常用命令行操作快捷鍵 : Ctrl ...
  • 對於生活中最常見的小游戲——走迷宮,相信大家都不陌生,人為走相信大家都會走,但能不能用代碼實現,我們認為是可以的,以下是我們對如何走迷宮的一些看法和代碼實現(cz負責隊列解決,mml負責用棧解決): 1.關於用隊列解決: 先簡單介紹一下隊列:隊列是一種操作受限的線性表,只允許在表的一端進行插入,在表 ...
  • 華為雲數倉GaussDB(DWS)研發專家高若岳老師,深入解析GaussDB(DWS)數據倉庫如何與大數據生態快速對接。 隨著智能數據時代的到來,數據量爆髮式增長,數據形態呈海量化和多樣化發展,不再是單一的結構化數據。從海量和多樣化的數據做融合分析,創造更多業務價值的訴求日益強烈。在本期《Gauss ...
一周排行
    -Advertisement-
    Play Games
  • 隨著Aspire發佈preview5的發佈,Microsoft.Extensions.ServiceDiscovery隨之更新, 服務註冊發現這個屬於老掉牙的話題解決什麼問題就不贅述了,這裡主要講講Microsoft.Extensions.ServiceDiscovery(preview5)以及如何 ...
  • 概述:通過使用`SemaphoreSlim`,可以簡單而有效地限制非同步HTTP請求的併發量,確保在任何給定時間內不超過20個網頁同時下載。`ParallelOptions`不適用於非同步操作,但可考慮使用`Parallel.ForEach`,儘管在非同步場景中謹慎使用。 對於併發非同步 I/O 操作的數量 ...
  • 1.Linux上安裝Docken 伺服器系統版本以及內核版本:cat /etc/redhat-release 查看伺服器內核版本:uname -r 安裝依賴包:yum install -y yum-utils device-mapper-persistent-data lvm2 設置阿裡雲鏡像源:y ...
  • 概述:WPF界面綁定和渲染大量數據可能導致性能問題。通過啟用UI虛擬化、非同步載入和數據分頁,可以有效提高界面響應性能。以下是簡單示例演示這些優化方法。 在WPF中,當你嘗試綁定和渲染大量的數據項時,性能問題可能出現。以下是一些可能導致性能慢的原因以及優化方法: UI 虛擬化: WPF提供了虛擬化技術 ...
  • 引言 上一章節介紹了 TDD 的三大法則,今天我們講一下在單元測試中模擬對象的使用。 Fake Fake - Fake 是一個通用術語,可用於描述 stub或 mock 對象。 它是 stub 還是 mock 取決於使用它的上下文。 也就是說,Fake 可以是 stub 或 mock Mock - ...
  • 為.net6在CentOS7上面做準備,先在vmware虛擬機安裝CentOS 7.9 新建CentOS764位的系統 因為CentOS8不更新了,所以安裝7;簡單就一筆帶過了 選擇下載好的操作系統的iso文件,下載地址https://mirrors.aliyun.com/centos/7.9.20 ...
  • 經過前面幾篇的學習,我們瞭解到指令的大概分類,如:參數載入指令,該載入指令以 Ld 開頭,將參數載入到棧中,以便於後續執行操作命令。參數存儲指令,其指令以 St 開頭,將棧中的數據,存儲到指定的變數中,以方便後續使用。創建實例指令,其指令以 New 開頭,用於在運行時動態生成並初始化對象。方法調用指... ...
  • LiteDB 是一個輕量級的嵌入式 NoSQL 資料庫,其設計理念與 MongoDB 類似,但它是完全使用 C# 開發的,因此與 C# 應用程式的集成非常順暢。與 SQLite 相比,LiteDB 提供了 NoSQL(即鍵值對)的數據存儲方式,並且是一個開源且免費的項目。它適用於桌面、移動以及 We ...
  • 1 開源解析和拆分文檔 第三方的工具去對文件解析拆分,去將我們的文件內容給提取出來,並將我們的文檔內容去拆分成一個小的chunk。常見的PDF word mark down, JSON、HTML。都可以有很好的一些模塊去把這些文件去進行一個東西去提取。 優勢 支持豐富的文檔類型 每種文檔多樣化選擇 ...
  • OOM是什麼?英文全稱為 OutOfMemoryError(記憶體溢出錯誤)。當程式發生OOM時,如何去定位導致異常的代碼還是挺麻煩的。 要檢查OOM發生的原因,首先需要瞭解各種OOM情況下會報的異常信息。這樣能縮小排查範圍,再結合異常堆棧、heapDump文件、JVM分析工具和業務代碼來判斷具體是哪 ...