里昂酒店
新聞中心
近年來,我們專注于提供全系列企業級性能管理方案和相關的IT服務,在幫助用戶提高業務效率和整體生產力的同時,降低運營和運維成本。
如何構建基于成本的 SQL 優化器?
來源:   日期:2019-04-19

SQL 優化器會分析一個 SQL 查詢語句并選擇最高效的方式來執行請求。非常簡單的查詢可能只有一種執行的方法,與此同時,復雜的查詢請求可能有數以千計,甚至數以百萬計的方式可供選擇。優化器的優化效果越好,就越接近最佳的執行方案,而這個最佳方案將會是執行查詢請求的最高效方法。


下面是一條看上去簡單的查詢 SQL :

SELECT *FROM customers c, orders oWHERE c.id=o.cust_id AND c.name < ’John Doe’


我不會用優化器在處理上述查詢時需要考慮的諸多問題讓你(或者我)感到無聊和厭煩,但下面的幾個注意點會傳達出我想要表達的觀點;


我們應該在表關聯前后評估字段名過濾么?

我們應該在索引上使用散列關聯、合并關聯、或者嵌套循環關聯(在 CockroachDB 稱為“查找關聯”)?

如果是查詢關聯或者散列關聯,我們是要通過枚舉客戶的方式來查詢訂單,還是通過枚舉訂單的方式來查詢客戶?

如果有一個建立在“name”字段上的輔助索引,我們是否可以通過這個索引來查詢匹配的名字?或者說最好用主鍵索引來查找匹配的id?


除此之外,讓優化器孤立地解決上述問題是肯定沒辦法勝任實際需要的,它需要縱觀所有解決方案來發現最好的那一個??贍芡ü褂貌檠亓岷細ㄖ饕姆絞絞欠淺2淮淼姆椒?,但如果可以使用合并關聯,主鍵索引會有更好的執行效果。實際上,一個方案是否是最佳的解決方案受數據行總數、眾多的硬件運算器的相對性能、數據值的存儲位置和查詢頻率以及其他因素的共同影響。



啟發式 vs 基于成本

人們是如何在眾多可能的執行計劃中做出優化選擇的?在這件事情上進行開發與思考的歷史比我的年齡還要大,所以任何人的回答都會顯得不那么精確。但是別灰心,在這個問題上,我們還是有兩條通用的途徑可以解決的。

第一條途徑是每個人都在第一時間構建自己需要的優化器。人們基于常規原則收集預置的啟發式規則。舉例來說,假設有一條等式條件是預置的,則可能有一條啟發式規則總是使用哈希連接(hash join)來代替嵌套循環連接。大多數情況下,執行計劃在結果上表現得好,那么,這就是一條好的啟發式規則。也就是說,像這樣的基于規則的優化器就叫做啟發式優化器。


然而,靜態啟發式規則也有缺點。他們在大多數情況下工作得很好,但在其他情況下,他們可能找不到最優執行計劃。例如,查找聯接循環遍歷來自外部關系的行,并通過反復探測內部關系上的索引來查找匹配的內部行。當外部行數很少時,這種方法很有效,但是隨著外部行數的增加,這種方法會逐漸降低,并且每一行的探測開銷開始拖長執行時間。在某些交叉點,散列或合并連接可能會更好。但是很難設計出啟發式規則來捕捉這些微妙之處。

接下來就開始介紹基于成本的優化器?;誄殺鏡撓嘔鶻毒倏贍艿鬧蔥屑蘋?,并為每個計劃分配成本,成本是執行該計劃所需的時間和資源的估計值。一旦這些可能性被列舉出來,優化器就會選擇成本最低的計劃并將其交付執行。雖然成本模型通常被設計為最大化吞吐量(即每秒查詢),但它也可以被設計為支持其他期望指標的查詢行為,例如最小化延遲(即檢索第一行的時間)或最小化內存使用。


基于此,你可能會問:“如果成本模型被證明是有缺陷的怎么辦?”。確實如此,基于成本的優化器是否足夠好和它分配的成本是成正比的。此外,成本的精確度也高度依賴于優化器評估數據行數時達到的精確度。這聽上去就像優化器在執行查詢過程中的每個階段評估實際返回的數據行總數。此時,我們已經引入了另一個研究了數十年的課題:數據庫統計。

收集得到的數據庫統計信息被發送給優化器希望其可以提供更精確的評估數據行數。有實際幫助的統計信息涵蓋了表數據行、無重復的和存在空值的列數以及用于理解值分布的直方圖等。這些信息都會傳給優化器,優化器依據得到的信息給出關于采用何種關聯類型、關聯順序、索引選擇以及其他問題的解決方案。


優化器的重生

隨著時間的推移,CockroachDB從一個簡單的、具有啟發式探索性質的優化器發展成一個足夠復雜的優化器。在2.0版本產品中,基于不可能簡單的通過逃避事實的現實,我們開始限制啟發式規則的使用數量。經過細微調整過的啟發式規則開始與其他規則相互沖突,而我們卻很難調查出問題到底出在了哪條規則上。一個非常簡單的啟發式規則:

當遇到一個相等條件時采用散列關聯

被變更為

當遇到一個相等條件時采用三聯關聯,除非所有的輸入參數都是關聯鍵,這種情況下會采用合并關聯

到最后,我們甚至考慮了諸如

當遇到一個相等條件時采用三聯關聯,除非所有的輸入都是關聯鍵,這種情況下會采用合并關聯。如果其中的一個關聯輸入參數由數量不多的列提供,而其他輸入參數可以使用一個有效的索引集,那么就使用查找關聯

這樣的規則設置。

我們加入的每一條啟發式規則都必須基于已經存在的規則進行測試檢查,以此保證這些新加入的規則可以和其他現有規則正常高效的完成工作。如果說基于成本的優化器是一個平衡的積木塔的話,那么啟發探索式優化器則是一個不穩定的結構,非常容易崩潰。

在2017年后半年,Cockroach實驗室內要求取代年代久遠的啟發探索式優化器的呼聲和勢頭越來越強烈。實驗室的聯合創始人之一 -- Peter Mattis,安排了一個長達數月的由數據庫優化器領域里的專家進行授課的訓練營。這個訓練營的宗旨是向開發人員傳授關于最先進的優化器如何工作的知識,還要求參與學員閱讀領域內有影響力的重要文獻。為了可以快速決定和推動,Pete創建了一個名叫“opttoy”的基于成本的優化器原型。這個原型演示了一些非常重要的概念,同時也規劃了之后的工作方向。


如下是CockroachDB2.1版本中的優化器已經支持的幾個非常重要的新特性:

關聯子查詢 - 這些查詢請求包含了需要引用外部查詢列的內部子查詢,比如如下的例子:

SELECT *FROM customers cWHERE EXISTS (	SELECT *	FROM orders o	WHERE o.cust_id = c.id)

有一篇博客會單獨介紹關聯子查詢的優化,這篇博客會在不久之后完成。


自動規劃查詢關聯:當需要決定如何執行一個關聯操作時,優化器除了合并關聯和散列關聯之外,還會考慮查詢關聯。查詢關聯是一種非常重要的用來快速執行相等關聯的手段,經常被用于其中一個輸入參數由數量不多的數據行提供,而另一個則具有一個相等條件下的索引集:

SELECT COUNT(*)FROM customers c, orders oWHERE c.id=o.cust_id AND c.zip='12345' AND c.name='John Doe'


在這個例子中,優化器會決定如何找到第一個客戶名為“John Doe”且居住在郵政編碼為“12345”地區的客戶,然后統計出該客戶的下單數。



高級選項

正如我之前所說的,我會引導諸位讀者一覽新優化器的一些高級選項特征。在開始之前,把一個查詢想象成一棵樹結構,執行計劃的中每一個步驟對應樹結構的一個節點。事實上,這也是SQL解釋器如何展示一個執行計劃的整個過程:

group                    |             | │                       | aggregate 0 | count_rows() │                       | scalar      | └── render              |             |      └── filter         |             |           │             | filter      | ((id = cust_id) AND           │             |             | (zip = '12345')) AND           │             |             | (name = 'John Doe')           └── join      |             |                │        | type        | cross                ├── scan |             |                │        | table       | [email protected]                │        | spans       | ALL                └── scan |             |                         | table       | [email protected]                         | spans       | ALL

上圖所示的輸出結果顯示了一個未經優化 的查詢請求是如何執行的:首先,計算得到一個完整的關于客戶表和訂單表的交叉乘積結果,之后根據WHERE條件過濾結果集,之后計算結果行數。但是!這個執行過程的性能非常差!如果客戶表里有10,000個用戶,訂單表里有100,000條訂單,那么兩表交叉乘積后會產生10億條記錄,而大部分數據都是要被過濾廢棄掉的,這會造成極大的浪費。

接下來會證明新優化器的價值所在。新優化器首先會將最開始的執行計劃樹結構轉換 成一系列邏輯上等價的執行計劃樹,然后從中選出成本最小的那一個。那么問題來了,什么叫“邏輯上等價”呢?兩棵執行計劃樹邏輯上等價意味著這兩棵樹被執行完成后會返回同樣的結果(在沒有ORDER BY條件限制時可能數據行順序不一致)?;謊災?,從正確性的角度來看,優化器會選中哪個執行計劃并不重要,它只關心哪個計劃的性能更好。


轉化

新優化器不會一次性生成所有的等價執行計劃樹。相反,最開始的時候有一個初始化樹結構,接著通過一系列遞增的轉換操作來生成可供選擇的樹結構。每一次獨立的轉換操作都是相對簡單的;許多簡單的轉換操作相互結合共同實現復雜的優化需求。觀察優化器的運行過程會讓你覺得非常不可思議:即使你了解優化器使用的每一個獨立的轉換操作,它還是會發現可以產生許多令人驚訝的組合,而這些組合可以生產我們從未想到的執行計劃。即使對于上述展示的相對簡單的查詢請求,優化器應用了12個轉換操作來實現最終請求。下面展示了4個關鍵的轉換操作流程圖。


640.webp.jpg

 

你可能注意到了為了最大程度的追求性能過濾器條件被向下移動到了join部分,成為了掃描操作的一部分。在最后的轉換操作中,優化器決定采用一個帶有輔助索引的查詢關聯來響應查詢請求。

截止到本文撰寫時,基于成本的優化器已經實現了超過160個不同的轉換操作,我們會繼續在未來的發行版本中加入更多的轉換操作類型。由于轉換操作完全依賴于新優化器的核心,所以我們花費大量的時間使得這些轉換操作盡可能容易的被定義、學習和維護。為了實現這個目的,我們創造了一個稱為Optgen的域細節語言(DSL)來表述轉換操作的結構,以及用來從DSL生產真實Go語言代碼的工具。如下是用Optgen語言表述轉換操作的一個例子:

[MergeSelectInnerJoin, Normalize](Select	$input:(InnerJoin $left:* $right:* $on:*)	$filter:*)=>(InnerJoin	$left	$right	(ConcatFilters $on $filter))

這個轉換將WHERE子句中的條件與內部連接的ON子句中的條件進行了合并。這個操作生成了大約25行的Go語言代碼,其中包含了保證傳遞匹配轉換被采用的代碼。之后還會有更多的博客文章來詳細解釋更多的Optgen特性,事實上需要涵蓋的內容太多了。如果想要迫切的了解相關知識,Optgen文檔是一個不錯的選擇。各位讀者也瀏覽了一些轉換操作的定義文件,如果你的能力非常優秀,嘗試實現一個新的、我們遺漏的轉換操作,我們對社區貢獻持非?;隊奶?。


備忘

我已經解釋了新發布的優化器是如何生成許多的等價執行計劃,并且基于成本估計選擇其中最理想的一個執行計劃。理論上這一套機制聽上去還不錯,但是優化器實際的效果怎么樣呢?它會不會有可能需要指數級的內存空間來存儲生成的這些計劃?我們可以從一個被稱為備忘的精巧的數據結構中得到這個問題的答案。

備忘被設計用來利用計劃之間有意義的冗余來有效存儲根據一個給定的查詢請求生成的所有執行計劃樹集合。例如,一個關聯查詢可能有數個邏輯上相等的、從各個方面角度看都完全相同的執行計劃,只不過一個計劃采用了散列關聯,一個計劃采用了合并關聯,另一個采用查詢關聯。除此之外,每個計劃可能會有多個變量:一個變量里左連接輸入參數使用主鍵索引來掃描行,在另一個變量里使用輔助索引做相同的工作。單純地編碼的話,這會因為計劃的指數膨脹導致需要指數范圍的空間來存儲這些計劃樹。

備忘通過定義一個名為備忘組的等值類集合來解決上述問題,在每個備忘組中包含了一個邏輯上等值的表達式集合。下圖詳細說明了備忘組的工作原理:


6402.webp.jpg


為了構建一個執行計劃,從組1中選任意一個運算符,然后分別從組2和組3中選擇操作的左操作數和右操作數。因為在同一個組里的運算符一定是邏輯上相等的,所以不管你選中的是什么,你都會得到一個合法執行計劃。簡單的算術計算顯示了共有12個(3 * 2 * 2)可能的執行計劃會被編碼到備忘里。所以,可以盡情想象一下擁有6種關聯方式、復雜的聚合、眾多的過濾器條件的報表查詢可能帶來的復雜度。其生成的計劃樹可能會以千計,如果你對備忘結構一無所知,那么在編碼過程過程中需要的存儲空間比你期望的要小的多。


當然,新的優化器不會隨機的從備忘中挑選其中的一個執行方案樹。相反,它會記錄并選擇每個備忘組中成本最小的那一個,然后基于這些選擇的表達式中遞歸構建最終的執行方案。而且,備忘也是一種優雅的數據結構,這讓我想起了Dan Brown的小說《天使與魔鬼》中光明會(illuminati)的鉆石雙重性。兩者都會編碼出比看上去可能更多的信息。


往期干貨推薦

案例 | 通過調整SCN值恢復數據庫緊急故障

揭秘PostgreSQL | 這些該嘗試的功能你一定不知道

干貨 | GC日志分析與策略選擇的最佳方案

干貨 | 最佳的數據庫系統IO性能測試方法

案例 | ASM元數據之FST損壞的修復

案例 | 業務員的非常規操作OOM故障處理

案例 | 核心系統中間件Hibernate一級緩存導致內存溢出的故障診斷

案例 | 100G+表改造為分區表實施腳本

案例 | 操作系統故障~通過RAC刪增節點修復集群

案例 | OLM助力信誠聚益的核心數據上云

案例 | logfile sync調優的最佳方案



|  北京    |    上海    |   廣州    |   成都    |


4008-906-960



4008-906-960

全國免費咨詢電話
  • 官方微博
  • 官方微信
Copyright 1998-2016 版權所有 北京東方龍馬軟件發展有限公司 京ICP備14000200號-1
开冲货店赚钱吗 微淘米赚钱是真的吗 用新能源车跑滴滴顺风车赚钱攻略 英雄联盟符文 街电如何赚钱 福建麻将16张玩法 现货客户亏钱公司才赚钱 大唐天下 赚钱模式 微乐辽宁麻将透视 在家如何游戏赚钱 9A彩票苹果 微信偷菜赚钱 诊断试剂销售赚钱吗 科创板赚钱么 巴蜀麻将官网 qq农场杂交和有机种子哪个更赚钱