網站首頁 編程語言 正文
1、標量子查詢與filter
當一個查詢在select和from之間，那么這種子查詢就是標量子查詢。實際應用中，很多人在寫SQL時為了方便會寫一堆標量子查詢的SQL，在表數據不大時，一般并不會有什么影響，但當數據量較大時，往往會對性能造成巨大影響。
因為標量子查詢類似于一個天然的嵌套循環，而且驅動表固定為主表。如下所示：
bill=# explain select empno,ename,sal,deptno,
bill-# (select d.dname from dept d where d.deptno = e.deptno) as dname
bill-# from emp e;
QUERY PLAN
--------------------------------------------------------------
Seq Scan on emp e (cost=0.00..15.84 rows=14 width=64)
SubPlan 1
-> Seq Scan on dept d (cost=0.00..1.05 rows=1 width=9)
Filter: (deptno = e.deptno)
(4 rows)
對于上面的SQL，emp表每輸出一行數據，都要去dept表中全表掃描一遍。
而我們都知道，嵌套循環的被驅動表的連接列必須包含在索引中，同理，標量子查詢的表的連接列也必須包含在索引中。但是我們在實際寫SQL時還是要避免使用標量子查詢，否則主表返回大量數據時，子表得被多次遍歷，從而對SQL性能產生巨大影響。
那么對于標量子查詢的SQL我們該怎么優化呢？最常用的就是改寫成外連接，這樣對于PostgreSQL的優化器而言可以根據實際情況去選擇表的連接方式。這里需要注意的是，不能將標量子查詢改成內連接，我們前面的例子中也可以看到，標量子查詢實際是一個傳值的過程，當主表傳值給子表時，如果沒有相應的值則會顯示NULL，而如果使用內連接的話這部分數據就丟失了。
因此，上面的標量子查詢可以改寫成：
可以看到，優化器根據實際情況選擇了更合適的hash join。
bill=# explain select e.empno,e.ename,e.sal,e.deptno,d.dname
bill-# from emp e
bill-# left join dept d on (d.deptno = e.deptno);
QUERY PLAN
-------------------------------------------------------------------
Hash Left Join (cost=1.09..2.31 rows=14 width=27)
Hash Cond: (e.deptno = d.deptno)
-> Seq Scan on emp e (cost=0.00..1.14 rows=14 width=18)
-> Hash (cost=1.04..1.04 rows=4 width=13)
-> Seq Scan on dept d (cost=0.00..1.04 rows=4 width=13)
(5 rows)
當主表連接列是外鍵，而子表的連接列是主鍵時，使用內連接也可以，因為外鍵自然不會存在NULL值。
bill=# explain select e.empno,e.ename,e.sal,e.deptno,d.dname
bill-# from emp e
bill-# inner join dept d on (d.deptno = e.deptno);
QUERY PLAN
-------------------------------------------------------------------
Hash Join (cost=1.09..2.31 rows=14 width=27)
Hash Cond: (e.deptno = d.deptno)
-> Seq Scan on emp e (cost=0.00..1.14 rows=14 width=18)
-> Hash (cost=1.04..1.04 rows=4 width=13)
-> Seq Scan on dept d (cost=0.00..1.04 rows=4 width=13)
(5 rows)
除了標量子查詢外，往往filter也會產生類似的情況，因為在filter中驅動表也會被固定住，那么優化器可能會選擇低效的執行計劃。而對于PostgreSQL而言本身也不支持hint功能，如果錯誤的執行計劃被固定，那么往往只能去改寫SQL。
這里說明下下filter，在PostgreSQL中filter主要有2種情況，一種是我們常見的where后面過濾數據的，這種一般不會產生什么性能問題，例如：
bill=# explain select * from t where id < 10;
QUERY PLAN
-------------------------------------------------------
Seq Scan on t (cost=0.00..16925.00 rows=100 width=4)
Filter: (id < 10)
(2 rows)
而另一種就是filter中是一些表的連接條件，這種呢便是我們前面說的情況，往往需要去關注的，例如：
bill=# explain select exists (select 1 from t where t.id=n.id) from n;
QUERY PLAN
-------------------------------------------------------------
Seq Scan on n (cost=0.00..169250145.00 rows=10000 width=1)
SubPlan 1
-> Seq Scan on t (cost=0.00..16925.00 rows=1 width=0)
Filter: (id = n.id)
(4 rows)
那么哪些寫法會容易產生filter呢？在PostgreSQL中當使用exists或者not exists時，或者子查詢中有固話子查詢的關鍵詞，如union、union all、cube、rollup、limit等，那么執行計劃往往容易產生filter。
因此上面的SQL我們用in去替換exists進行改寫：
bill=# explain select id in (select id from t) from n;
QUERY PLAN
-------------------------------------------------------------------------
Seq Scan on n (cost=0.00..129160170.00 rows=10000 width=1)
SubPlan 1
-> Materialize (cost=0.00..23332.00 rows=1000000 width=4)
-> Seq Scan on t (cost=0.00..14425.00 rows=1000000 width=4)
(4 rows)
除此之外，在PostgreSQL中我們更推薦使用= any的方式去改寫該類SQL：
bill=# explain select id = any(array(select id from t)) from n;
QUERY PLAN
-------------------------------------------------------------------
Seq Scan on n (cost=14425.00..14695.00 rows=10000 width=1)
InitPlan 1 (returns $0)
-> Seq Scan on t (cost=0.00..14425.00 rows=1000000 width=4)
(3 rows)
當然這并不是說in的寫法就一定比exists要好，只是相較于exists更不容易產生filter。這是為什么呢？因為如果子查詢中包含我們上面提到的固化關鍵字時，子查詢會被固化為一個整體，當采用exists寫法時，如果子查詢中有主表的連接列，那么便只能是主表通過連接列給子查詢中的表傳值，因此會選擇filter。而使用in的寫法，即使子查詢被固化，但如果沒有主表連接列的字段，那么便不會選擇filter。
2、視圖合并
不知道大家有沒有遇到過類似下面的情況：
select xxx from () t1, () t2 where t1.id = t2.id;明明t1和t2兩個子查詢單獨執行都很快，但是放到一起速度卻變得特別慢，這種情況往往就是視圖合并所導致的。
例如下面的SQL：
我們按照SQL中的順序來看應該是emp和dept兩表先進行關聯，然后再去和salgrade表關聯。但執行計劃中的順序卻變成了emp和salgrade表先關聯，最后才去關聯dept表。
這說明發生了視圖合并，即視圖/子查詢中的內容被拆開了。
bill=# explain select a.*,c.grade
bill-# from (select ename,sal,a.deptno,b.dname
bill(# from emp a,dept b
bill(# where a.deptno = b.deptno) a,
bill-# salgrade c
bill-# where a.sal between c.losal and c.hisal;
QUERY PLAN
-----------------------------------------------------------------------------
Hash Join (cost=1.09..4.56 rows=8 width=27)
Hash Cond: (a.deptno = b.deptno)
-> Nested Loop (cost=0.00..3.43 rows=8 width=18)
Join Filter: ((a.sal >= c.losal) AND (a.sal <= c.hisal))
-> Seq Scan on emp a (cost=0.00..1.14 rows=14 width=14)
-> Materialize (cost=0.00..1.07 rows=5 width=12)
-> Seq Scan on salgrade c (cost=0.00..1.05 rows=5 width=12)
-> Hash (cost=1.04..1.04 rows=4 width=13)
-> Seq Scan on dept b (cost=0.00..1.04 rows=4 width=13)
(9 rows)
從上面的例子可以看出，視圖合并一般產生性能問題都是因為發生視圖合并后表的連接順序變化導致的。不過一般優化器這么做是為了幫我們選擇更合適的表連接順序，而當優化器選擇了錯誤的連接順序時，我們就有必要對SQL進行改寫了。
由于PostgreSQL中我們無法使用hint來讓優化器禁止視圖合并，所以我們便需要了解一些SQL改寫的技巧。
和前面的filter一樣，當我們將視圖/子查詢固化后，那么便不能進行視圖合并。因此上面的SQL我們可以改寫為：
加上group by后，子查詢被固化，視圖沒有發生合并，emp和dept表先進行關聯了。
bill=# explain select a.*,c.grade
bill-# from (select ename,sal,a.deptno,b.dname
bill(# from emp a,dept b
bill(# where a.deptno = b.deptno group by ename,sal,a.deptno,b.dname) a,
bill-# salgrade c
bill-# where a.sal between c.losal and c.hisal;
QUERY PLAN
-------------------------------------------------------------------------------
Nested Loop (cost=2.45..5.02 rows=8 width=27)
Join Filter: ((a.sal >= c.losal) AND (a.sal <= c.hisal))
-> HashAggregate (cost=2.45..2.59 rows=14 width=23)
Group Key: a.ename, a.sal, a.deptno, b.dname
-> Hash Join (cost=1.09..2.31 rows=14 width=23)
Hash Cond: (a.deptno = b.deptno)
-> Seq Scan on emp a (cost=0.00..1.14 rows=14 width=14)
-> Hash (cost=1.04..1.04 rows=4 width=13)
-> Seq Scan on dept b (cost=0.00..1.04 rows=4 width=13)
-> Materialize (cost=0.00..1.07 rows=5 width=12)
-> Seq Scan on salgrade c (cost=0.00..1.05 rows=5 width=12)
(11 rows)
3、謂詞推入
說完視圖合并，我們再來看看你視圖不能合并時會出現的一種情況——謂詞推入。即對于那些不能合并的視圖，并且有謂詞進行過濾，CBO會將謂詞過濾條件推入到視圖內，為了盡早的過濾掉無用的數據，從而提升性能。
從CBO的角度來看，進行謂詞推入自然是好的，因為可以提前過濾掉不需要的數據。但是如果推入的謂詞是連接列的，那么可能導致表的join產生變化，SQL性能變得更差。
如下SQL所示：
外層的謂詞d.deptno between c.losal and c.hisal推入到了視圖里面。
bill=# create or replace view v1 as select ename,sal,a.deptno,b.dname
bill-# from emp a,dept b
bill-# where a.deptno = b.deptno;
CREATE VIEW
bill=# explain select d.*,c.grade from v1 d,salgrade c
bill-# where d.deptno between c.losal and c.hisal;
QUERY PLAN
-----------------------------------------------------------------------------
Hash Join (cost=1.09..4.56 rows=8 width=27)
Hash Cond: (a.deptno = b.deptno)
-> Nested Loop (cost=0.00..3.43 rows=8 width=18)
Join Filter: ((a.deptno >= c.losal) AND (a.deptno <= c.hisal))
-> Seq Scan on emp a (cost=0.00..1.14 rows=14 width=14)
-> Materialize (cost=0.00..1.07 rows=5 width=12)
-> Seq Scan on salgrade c (cost=0.00..1.05 rows=5 width=12)
-> Hash (cost=1.04..1.04 rows=4 width=13)
-> Seq Scan on dept b (cost=0.00..1.04 rows=4 width=13)
(9 rows)
那我們該如何防止謂詞內推呢？在Oracle中可以通過關閉連接列的謂詞推入的隱藏參數_push_join_predicate來實現，那么在PostgreSQL中又該如何實現呢？
和上面類似，我們可以將視圖固化來避免這種情況，但一般來說不建議將視圖固化，因為大部分情況謂詞推入大多數對性能是有好處的。例如當我們在視圖中使用limit時會導致謂詞無法推入，因此一般也不建議在視圖中使用limit，為什么呢？因為如果謂詞進行推入的話，limit取到的值可能就不同了，會對結果集產生影響，所以自然不能推入了，因為優化器的任何等價轉換都是在不改變SQL結果的情況下才能進行的。
原文鏈接：https://foucus.blog.csdn.net/article/details/122366849
相關推薦
- 2023-07-13 websocket的使用及nginx通信的ws代理配置
- 2022-03-08 用C語言實現鏈式棧介紹_C 語言
- 2021-12-10 時間戳處理的幾種方式
- 2022-07-01 Go?數據結構之二叉樹詳情_Golang
- 2022-05-27 python中torch.nn.identity()方法詳解_python
- 2022-04-12 【debug】illegal hardware instruction
- 2022-10-24 Winform控件優化之圓角按鈕2_C#教程
- 2022-09-19 Redis實現事物以及鎖的方法_Redis
- 最近更新
-
- window11 系統安裝 yarn
- 超詳細win安裝深度學習環境2025年最新版（
- Linux 中運行的top命令 怎么退出？
- MySQL 中decimal 的用法？ 存儲小
- get 、set 、toString 方法的使
- @Resource和 @Autowired注解
- Java基礎操作-- 運算符，流程控制 Flo
- 1. Int 和Integer 的區別，Jav
- spring @retryable不生效的一種
- Spring Security之認證信息的處理
- Spring Security之認證過濾器
- Spring Security概述快速入門
- Spring Security之配置體系
- 【SpringBoot】SpringCache
- Spring Security之基于方法配置權
- redisson分布式鎖中waittime的設
- maven:解決release錯誤：Artif
- restTemplate使用總結
- Spring Security之安全異常處理
- MybatisPlus優雅實現加密？
- Spring ioc容器與Bean的生命周期。
- 【探索SpringCloud】服務發現-Nac
- Spring Security之基于HttpR
- Redis 底層數據結構-簡單動態字符串（SD
- arthas操作spring被代理目標對象命令
- Spring中的單例模式應用詳解
- 聊聊消息隊列，發送消息的4種方式
- bootspring第三方資源配置管理
- GIT同步修改后的遠程分支
提供CDN加速