SQL子查詢的感悟

今天在聽陳華軍老師的課時；感觸頗多。其中講到“不同執行計劃的選擇（子查詢）”這一欄。我們在平時工作也經常要用到子查詢。有哪些思路來優化這種子查詢呢？

例如我們今天實驗的表結構

• 表T1 有10000條記錄;併在id欄位創建btree索引
• 表T2 有1000條記錄

postgres=# create table t1(id int primary key, info text, reg_time timestamp);
CREATE TABLE
postgres=# create table t2(id int, name text);
CREATE TABLE
postgres=# insert into t1 select generate_series(1, 10000),'lottu', now();
INSERT 0 10000
postgres=# insert into t2 select (random()*1000)::int, 'lottu'||id  from generate_series(1,1000) id;
INSERT 0 1000
postgres=# create index ind_t1_id on t1(id);
CREATE INDEX

實驗對象SQL;

select * from t1 where id in (select id from t2);

SQL語法改造

我們先看下這SQL的執行計劃

postgres=# explain (analyze,verbose,costs,timing) select * from t1 where id in (select id from t2);
                                                             QUERY PLAN                                                             
----------------------------------------------------------------------
 Merge Join  (cost=54.25..99.73 rows=628 width=18) (actual time=1.319..2.365 rows=628 loops=1)
   Output: t1.id, t1.info, t1.reg_time
   Inner Unique: true
   Merge Cond: (t1.id = t2.id)
   ->  Index Scan using ind_t1_id on public.t1  (cost=0.29..337.29 rows=10000 width=18) (actual time=0.014..0.421 rows=997 loops=1)
         Output: t1.id, t1.info, t1.reg_time
   ->  Sort  (cost=53.97..55.54 rows=628 width=4) (actual time=1.298..1.387 rows=628 loops=1)
         Output: t2.id
         Sort Key: t2.id
         Sort Method: quicksort  Memory: 54kB
         ->  HashAggregate  (cost=18.50..24.78 rows=628 width=4) (actual time=0.730..0.877 rows=628 loops=1)
               Output: t2.id
               Group Key: t2.id
               ->  Seq Scan on public.t2  (cost=0.00..16.00 rows=1000 width=4) (actual time=0.013..0.267 rows=1000 loops=1)
                     Output: t2.id
 Planning Time: 0.454 ms
 Execution Time: 2.507 ms
(17 rows)

從該執行計劃可以看到很多信息;

• 其中獲取的行數只有628條;
• 執行時間是2.507ms;
• 兩表之間採用Merge Join；由於t2表沒有索引且無須存放；需要使用記憶體進行排序。

若採用join的方式

如果子查詢被迴圈執行導致SQL慢，可嘗試改成等價的join；

postgres=# explain (analyze,verbose,costs,timing) select t1,* from t1 , t2 where t1.id = t2.id ;
                                                             QUERY PLAN                                                             
------------------------------------------------------------------------------------------------------------------------------------
 Merge Join  (cost=66.11..117.17 rows=1000 width=72) (actual time=0.601..2.184 rows=1000 loops=1)
   Output: t1.*, t1.id, t1.info, t1.reg_time, t2.id, t2.name
   Merge Cond: (t1.id = t2.id)
   ->  Index Scan using ind_t1_id on public.t1  (cost=0.29..337.29 rows=10000 width=60) (actual time=0.021..0.726 rows=997 loops=1)
         Output: t1.*, t1.id, t1.info, t1.reg_time
   ->  Sort  (cost=65.83..68.33 rows=1000 width=12) (actual time=0.573..0.721 rows=1000 loops=1)
         Output: t2.id, t2.name
         Sort Key: t2.id
         Sort Method: quicksort  Memory: 71kB
         ->  Seq Scan on public.t2  (cost=0.00..16.00 rows=1000 width=12) (actual time=0.013..0.226 rows=1000 loops=1)
               Output: t2.id, t2.name
 Planning Time: 0.288 ms
 Execution Time: 2.421 ms
(13 rows)

性能有點提升；其實兩個SQL之間不等價；因為T2有重覆id；導致最後的結果集是1000條；而非上面的628.

採用array的方式改寫

postgres=# explain (analyze,verbose,costs,timing) select * from t1 where id = any(array(select id from t2));
                                                        QUERY PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------
 Index Scan using ind_t1_id on public.t1  (cost=16.29..59.03 rows=10 width=18) (actual time=0.418..1.108 rows=628 loops=1)
   Output: t1.id, t1.info, t1.reg_time
   Index Cond: (t1.id = ANY ($0))
   InitPlan 1 (returns $0)
     ->  Seq Scan on public.t2  (cost=0.00..16.00 rows=1000 width=4) (actual time=0.014..0.127 rows=1000 loops=1)
           Output: t2.id
 Planning Time: 0.106 ms
 Execution Time: 1.178 ms
(8 rows)

結果跟SQL1是等價的；用時只有1.178ms；且未用記憶體；效果最優。選它準沒錯

思路轉換

前面我們t2表只有1000條記錄，且id小於1000；若我們t2表有1000000條甚至更多；且ID也沒有限制。

select * from t1 where id in (select id from t2 where id <= 1000);
或者
with t as
(select id from t2 where id <= 1000)
select t1.* from t1 where id in (select id from t);

我相信很多人還是會採用這種寫法。這些寫不好；雖然你一個SQL搞定；但是效率慢。這是有人說你可以在t2表建個索引；這個是可以的；效率確實提升很多。若t2沒有這個索引；你沒必要單獨為這個需求創建一個索引。

我建議可以用一個子表用來存放；

select id from t2 where id <= 1000);

子表：你可以用臨時表/表/物化視圖。

這樣的優勢；減少多次掃描t2表的數據塊；只要掃描一次即可