深度 | 如何玩轉PG查詢處理與執行器演算法
作者介紹
孫旭,騰訊雲高階工程師。10年資料庫核心研發經驗,熟悉PostgreSQL、Teradata資料庫核心,熟悉資料庫的查詢優化、執行、事務併發以及儲存等子系統;對分散式資料庫有深入的研究和研發經驗。目前在騰訊雲從事CynosDB資料庫研發工作。
一、導語
資料庫查詢處理(Query Processing)是資料庫比較核心的技術,也是距離使用者最近的子系統。資料庫系統在除了實現事務的隔離界別外,還需要在SQL上做到一定程度的相容,因為資料庫本身就是在做查詢處理,很多的核心模組工作都是為了支援這個功能。本文將簡要介紹一下PostgreSQL的查詢處理過程。
二、關係代數與SQL(結構化查詢語言)
大家在學校學到的可能更多的是關係代數(Relational Algebra),它定義了一組在關係(Relation)上進行操作的操作符。關係代數的運算元是關係(即,資料庫中的二維表),其結果也是關係。操作符包含如下幾類:
-
集合操作符:交,並,差;
-
過濾/投影;
-
連線;
-
別名(alias);
-
一些擴充套件的操作符,例如:分組,去重,Aggregate。
除了關係代數,還有一種描述二維關係表的操作方法:DataLog(Database Logic)。這種方式相對來說比較強大,關係代數的操作符都可以用它來表述,但是有些關係的操作是關係代數表示不了的,只能用DataLog來表述,比如:遞迴查詢。
直接使用關係代數對資料庫操作比較晦澀,難度比較高,因此,今天的商業資料庫都實現了一種更高階的查詢語言——SQL(Structured Query Language),在表達上更加簡潔易懂,也更容易學習。
實際上,在資料庫系統內部,SQL語句也是被轉化成對應關係代數的操作符,然後再進行處理,只是這些工作對終端使用者來說是不可見的。其實,關係型資料庫直接的“本地語言”是關係代數,SQL語言只是人類與關聯式資料庫進行交流的“更加便捷的”橋樑。
可能大家有疑問,為何使用SQL作為交流橋樑,而不是用C、Java或者Python作為資料庫的查詢語言?
因為一個較短的SQL可以完成千百行C或者Java的工作,特別是在訪問一些層次化的資料模型(例如:Oracle的層次查詢,一條語句可以把層次結構輸出出來;PostgreSQL的WITH-RECURSIVE語句也可以完成類似的功能)。
更加重要的是,資料庫核心在實現SQL查詢的時候,可以對SQL進行特定的優化,產生更加有效的訪問方法,這些都是高階語言不太可能具備的功能。
三、PostgreSQL查詢處理流程
從使用者在客戶端傳送一條SQL語句,經過網路傳輸給PostgreSQL進行處理、執行,其流程經過如下幾個步驟:
1、語法分析
SQL字串可以認為是一個大的正則式,語法分析來檢查這個大的“正則式”是否match定義好的規則。
在PostgreSQL中,pg_parse_query是語法分析的入口函式,實際上由scan.l(Flex檔案)以及gram.y(Bison檔案)完成語法檢查。
scan.l是詞法分析,將輸入SQL分解一個個的Token,輸入到gram.y中進行規則匹配。gram.y中定義了所有SQL型別的語法規則以及操作符的優先順序和結合律,例如,下段程式碼定義了操作符的優先順序和結合規則:
下段程式碼定了語法規則:
語法分析結束後,以查詢(SELECT)為例,返回的結構體是SelectStmt,它會作為作為語義分析模組的輸入。SelectStmt儲存了SQL語句中的各個語法子部分,例如:from子句,投影列,group子句等,從其定義可以看出更多細節:
2、語法檢查
parse_analyze()函式是這一步的入口函式,根據不同的語句型別呼叫transformXXXXStmt()函式進行分析處理。對於SelectStmt,呼叫的transformSelectStmt(),對於DeleteStmt呼叫transformDeleteStmt()。在這一步將會:
- 檢查表是否存在,列是否合法,將表、排序列、投影列等轉化為內部物件ID;
- SQL語義是否正確合法。
比如:Aggregate 函式不能用在WHERE中。如下查詢:
select 1 from x where max(x2) > 1;
調整聚集函式在適當的層次中計算,如下查詢:
select (select max(x.x2) from y) from x;
max(x.x2)在SQL語義上應該是在最外層查詢中計算,而不是將x.x2傳入到內層子查詢,在內層子查詢中計算Aggregate函式max()的值。而對於如下查詢:
select (select max(x.x2+y.x2) from y) from x;
max(x.x2+y.x2)是在內層子查詢中被計算,而不是作為外層查詢的Aggregate函式。
經過語義檢查,會將SelectStmt變形為Query結構,作為查詢重寫的輸入。Query結構包含的部分與SelectStmt類似,只不過內容更加豐富:
-
儲存的都是資料庫內部的物件資訊;
-
一些flag標記,表明是否包含:Aggregate函式、視窗函式、SubLink子查詢等;
-
確定了表示式所在的Query層次。
之前提到過,資料庫核心處理SQL時都是轉化成關係代數相關的元素,這個在Query結構體中可以看到這點:
例如:
-
關係代數的投影是:targetList;
-
關係代數的過濾/join是:jointree;
-
關係代數的Aggregate是:targetList;
-
關係代數的分組:groupClause;
-
關係代數的sort是:sortClause。
後續所有的工作都是基於上面的元素進行。
3、查詢重寫
根據使用者定義的規則對查詢進行重寫,實際是對Query結構裡面的成員進行修改或替換,這些規則可以使用CREATE RULE建立。如果使用者在查詢對應的表上沒有規則,此步跳過。
4、查詢優化
查詢優化是比較複雜子系統,通常稱這個模組是“優化器”,也用來衡量資料庫系統優秀的一個方面。在資料庫領域另一個複雜的子系統是事務處理,這裡也不做展開。
PostgreSQL在這一步的輸入是Query物件,入口函式是planner(),輸出查詢計劃(Query Plan),查詢計劃是指導查詢如何被執行以及用何種方法執行的一種結構,通常是樹形結構。
優化器做的主要工作就是對Query結構的各個語法部分,選擇較優的執行演算法,輸出較優的執行計劃。在PostgreSQL中,通常分成如下幾步:
1)子查詢處理
在PostgreSQL內部有2類的子查詢:一種在from語句後面稱為SubQuery,另一種在作為表示式的一部分,可以出現在targetList,過濾條件,連線條件中,稱為sub-link。
這兩種都可以統稱為Sub-Select,而優化器在這一步會進行Sub-Select Elimination:將子查詢上拉到頂層查詢,消除子查詢。
這樣做可以減少查詢層數,增加上層表的個數,從而增加join順序的搜尋空間,有助於找到較優的連線順序。以sub-link為例,說明一下這個步驟的工作。對於查詢:
select * from x where x.x2 in (select y.x2 from y);
PostgreSQL在這步可以將IN語句轉化成Semi-Join,原來的O(m*n)的查詢演算法簡化為O(1)HASH-JOIN演算法。
這裡執行計劃並沒有使用Hash Semi-Join,是因為inner plantree用了group hashagg進行了去重,所以原來的Semi-Join可以進一步優化為Hash Join,這種優化進一步擴大了Join順序搜尋空間。
2)執行表示式預處理
在這一步,會將targetList,過濾條件等列修改為對基表的引用;對錶達式裡面的SubLink遞迴呼叫優化器優先進行優化;計算表示式裡面的常量表示式等。
3)移除無用的GROUP BY列
如果核心可以確定GROUP BY中的一些屬性集合Y函式依賴於其他屬性集合X,那麼可以刪除GROUP BY中的屬性集合Y。函式依賴檢查工作由check_functional_grouping完成。這樣可以減少分組計算代價。
4)Reduce outer join
將某些OUTER JOIN轉化為INNER JOIN。
5)選擇優化的Join順序
在這一步完成主要完成:條件的下推,基於連線條件生成等價類,以及通過動態規劃選擇較優的JOIN順序。從整體來看,JOIN順序的選擇是Condition-Driven,而不是完全的對所有的表進行排列組合求解。例如對於查詢:
select * from r, p, q where r1 = (p1+q1) and r2=q2;
通常我們可能認為r和q在r2=q2的條件進行連線,然後與p在r1 = (p1+q1)上進行連線;但是PostgreSQL核心在也會做這樣的嘗試:將p和q進行product join,再與r在條件r1 = (p1+q1) and r2=q2;進行連線,p和q之所以可以連線完全是由r1 = (p1+q1)決定的。
6)其他子句優化處理
做完Join Plan之後,再針對GROUP BY、Aggregate、ORDER BY、LIMIT等子句進行處理。以GROUP BY為例,在PostgreSQL內部,實現GROUP BY的有2個演算法:Sort Group By以及 HashAgg Group By,通過函式cost_group以及cost_agg分別來計算二者代價,選擇較優的演算法執行。
完成這些這些步驟後,呼叫set_plan_references()以及SS_finalize_plan()函式最後處理引數和變數引用後,就可以輸出最終的查詢計劃(Execution Query Plan)了。查詢計劃由很多節點組成:投影、掃描、連線、Aggregate、GROUP BY、排序等,從這些名稱也可以看出他們就是關係代數的操作符,它們會被傳給查詢執行元件進行執行。如下查詢計劃示例:
5、查詢執行
這是查詢處理的最後一步,將優化器輸出的執行計劃,進行初始化、執行。查詢執行子系統我們一般稱為執行器。執行過程有ExecutorStart、ExecutorRun、ExecutorFinish這三個入口函式,分別完成對查詢計劃的初始化,執行,以及清理。在這個過程中會訪問資料庫的其他子系統,如:事務系統、儲存系統、日誌系統。
以上就是在PostgreSQL核心中對一個查詢處理的整個生命週期,基本可以瞭解到一個SQL字串在資料庫核心中是如何一步步被解析,直到到執行的基本過程。
上文中描述的一些方法和理論不僅僅在PostgreSQL資料庫有效,也可以推導到其他資料庫系統中。
四、PostgreSQL執行器演算法之SeqScan
上文講述了資料庫核心中查詢處理的基本流程,現在我們先展開講述執行器演算法。
資料庫的執行器包含了很多個運算元的執行演算法,比較簡單的一種就是SeqScan,就是從按照順序(一般是儲存順序)對錶進行掃描。
1、頁面結構
PostgreSQL頁面儲存與大多數資料庫的類似,包含:頁面頭,ItemId 陣列,以及Item(元組),佈局如下:
其中PageHeader包含了頁面LSN,ItemId陣列最後一個元素的頁面偏移(pd_lower),第一條元組在頁面內偏移(pd_upper),以及其他欄位。
2、順序掃描演算法
PostgreSQL的順序掃描的入口函式是SeqNext,每次執行這個函式會返回一條元組,主要工作是由heapgettup:
1)初始化掃描過程
初始化掃描過程就是設定HeapScanDesc物件,主要設定初始掃描的頁面,一般從0號頁面的第一個元組開始,即scan->rs_startblock是0。
在PostgreSQL的掃描過程有一個優化,即sync_scan,這個特性允許當前的掃描從表的中間頁面開始掃描,這個頁面是其他掃描程式填寫到共享記憶體,由ss_report_location完成,代表這些頁面剛剛被訪問過,如果當前掃描從這些頁面開始,那麼可以直接在記憶體中訪問到,從而減少儲存讀取頁面的IO次數,提升效能。
每次更新表的sync start page時,需要遍歷整個list。為了減少這個list的訪問,每隔SYNC_SCAN_REPORT_INTERVAL個頁面才去更新list,這個數值是128 * 1024 / BLCKSZ。
2)讀取頁面進行掃描
按照頁面結構掃描頁面。首先讀取頁面頭(PageHeaderData)的pd_linp成員,這是一個Offset陣列(ItemIdData),記錄了元組在頁面上的偏移(lp_off)。
後續的主要邏輯是遍歷pd_linp陣列,通過offset+page地址獲取到元組記憶體地址。然後對元組做可見性判斷。邏輯如下:
HeapTupleSatisfiesVisibility進行元組可見性判斷,PostgreSQL是MVCC實現的事務隔離,這個函式就是MVCC的入口邏輯。
3)讀取下一個頁面繼續進行掃描
繼續讀取後續頁面進行掃描。
所有的掃描狀態儲存在HeapScanDesc,下次掃描的時候,可以從上次的狀態開始。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/69940575/viewspace-2675256/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- MySQL 查詢處理 SQL查詢執行順序MySql
- Oracle 優化器與sql查詢執行順序Oracle優化SQL
- D4.玩轉查詢與替換
- SQL Server調優系列玩轉篇(如何利用查詢提示(Hint)引導語句執行)SQLServer
- PostgreSQL 查詢當前執行中sql的執行計劃——pg_show_plans模組SQL
- sql查詢是如何執行的?SQL
- Oracle 最佳化器與sql查詢執行順序OracleSQL
- [玩轉MySQL之六]MySQL查詢優化器MySql優化
- 如何處理執行緒死鎖執行緒
- library cache lock\pin的查詢與處理
- 如何處理 MySQL 萬用字元的模糊查詢MySql字元
- 【轉】JAVA處理執行緒超時Java執行緒
- MySQL邏輯查詢處理MySql
- oracle 死鎖查詢處理Oracle
- 如何使用find和xargs查詢和處理檔案
- 深度學習高效計算與處理器設計深度學習
- 多執行緒查詢執行緒
- QT執行緒同步與非同步處理QT執行緒非同步
- Spring如何處理執行緒併發Spring執行緒
- 常用查詢演算法 (轉)演算法
- MyBatis Like查詢處理%_符號MyBatis符號
- Oracle死鎖查詢及處理Oracle
- 【手摸手玩轉 OceanBase 175】如何執行執行全量恢復
- 深度解析Java執行緒池的異常處理機制Java執行緒
- 02. x86處理器執行方式
- 查詢處理死鎖會話的sql語句(轉貼)會話SQL
- explain 查詢執行計劃AI
- 一條 SQL 查詢語句是如何執行的?SQL
- 如何在Linux中執行MySQL/MariaDB查詢LinuxMySql
- 如何在MySQL中查詢OS執行緒id(LWP)?MySql執行緒
- IO處理執行緒執行緒
- 如何在批處理模式下執行 top 命令模式
- 玩轉大資料系列之二:資料分析與處理大資料
- 從零玩轉jQuery-文件處理jQuery
- ORACLE 查詢語句處理過程(Oracle
- 查詢等待事件及處理指令碼事件指令碼
- 查詢處理的邏輯順序
- [轉帖]SQL Server簡潔查詢正在執行的程序SQLServer