前言

查詢最佳化器是關聯式資料庫系統的核心模組，是資料庫核心開發的重點和難點，也是衡量整個資料庫系統成熟度的“試金石”。

查詢最佳化理論誕生距今已有四十來年，學術界和工業界其實已經形成了一套比較完善的查詢最佳化框架(System-R 的 Bottom-up 最佳化框架和 Volcano/Cascade 的 Top-down 最佳化框架)，但圍繞查詢最佳化的核心難題始終沒變—— 如何利用有限的系統資源儘可能為查詢選擇一個“好”的執行計劃 。

近年來，新的儲存結構（如 LSM 儲存結構）的出現和分散式資料庫的流行進一步加大了查詢最佳化的複雜性，本文章結合 OceanBase 資料庫過去近十年時間的實踐經驗，與大家一起探討查詢最佳化在實際應用場景中的挑戰和解決方案。

查詢最佳化器簡介

SQL 是一種結構化查詢語言，它只告訴資料庫”想要什麼”，但是它不會告訴資料庫”如何獲取”這個結果，這個"如何獲取"的過程是由資料庫的“大腦”查詢最佳化器來決定的。在資料庫系統中，一個查詢通常會有很多種獲取結果的方法，每一種獲取的方法被稱為一個"執行計劃"。給定一個 SQL，查詢最佳化器首先會列舉出等價的執行計劃。

其次，查詢最佳化器會根據統計資訊和代價模型為每個執行計劃計算一個“代價”，這裡的代價通常是指執行計劃的執行時間或者執行計劃在執行時對系統資源(CPU + IO + NETWORK)的佔用量。最後，查詢最佳化器會在眾多等價計劃中選擇一個"代價最小"的執行計劃。下圖展示了查詢最佳化器的基本元件和執行流程。

查詢最佳化器面臨的挑戰

查詢最佳化自從誕生以來一直是資料庫的難點，它面臨的挑戰主要體現在以下三個方面:

挑戰一：精準的統計資訊和代價模型

統計資訊和代價模型是查詢最佳化器基礎模組，它主要負責給執行計劃計算代價。精準的統計資訊和代價模型一直是資料庫系統想要解決的難題，主要原因如下:

1、 統計資訊 ：在資料庫系統中，統計資訊蒐集主要存在兩個問題。首先，統計資訊是透過取樣蒐集，所以必然存在取樣誤差。其次，統計資訊蒐集是有一定滯後性的，也就是說在最佳化一個 SQL 查詢的時候，它使用的統計資訊是系統前一個時刻的統計資訊。

2、 選擇率計算和中間結果估計 ：選擇率計算一直以來都是資料庫系統的難點，學術界和工業界一直在研究能使選擇率計算變得更加準確的方法，比如動態取樣，多列直方圖等計劃，但是始終沒有解決這個難題，比如連線謂詞選擇率的計算目前就沒有很好的解決方法。

3、 代價模型 ：目前主流的資料庫系統基本都是使用靜態的代價模型，比如靜態的 buffer 命中率，靜態的 IO RT，但是這些值都是隨著系統的負載變化而變化的。如果想要一個非常精準的代價模型，就必須要使用動態的代價模型。

挑戰二：海量的計劃空間

複雜查詢的計劃空間是非常大的，在很多場景下，最佳化器甚至沒辦法列舉出所有等價的執行計劃。下圖展示了星型查詢等價邏輯計劃個數(不包含笛卡爾乘積的邏輯計劃)，而最佳化器真正的計劃空間還得正交上運算元物理實現，基於代價的改寫和分散式計劃最佳化。在如此海量的計劃空間中，如何高效的列舉執行計劃一直是查詢最佳化器的難點。

挑戰三：高效的計劃管理機制

計劃管理機制分成計劃快取機制和計劃演進機制。

1、 計劃快取機制 ：計劃快取根據是否引數化，最佳化一次/總是最佳化以及是否快取可以劃分成如下圖所示的三種計劃快取方法。每個計劃快取方法都有各自的優缺點，不同的業務需求會選擇不同的計劃快取方法。在螞蟻/阿里很多高併發，低時延的業務場景下，就會選擇 引數化+最佳化一次+快取 的策略，那麼就需要解決不同引數對應不同計劃的問題(parametric query optimization)，後面我們會詳細討論。

2、 計劃演進機制 ：計劃演進是指對新生成計劃進行驗證，保證新計劃不會造成效能回退。在資料庫系統中， 新計劃因為一些原因(比如統計資訊重新整理，schema版本升級)無時無刻都在才生，而最佳化器因為各種不精確的統計資訊和代價模型始終是沒辦法百分百的保證新生成的計劃永遠都是最優的，所以就需要一個演進機制來保證新生成的計劃不會造成效能回退。

OceanBase 查詢最佳化器工程實踐

下面我們來看一下 OceanBase 根據自身的框架特點和業務模型如何解決查詢最佳化器所面臨的挑戰。

從統計資訊和代價模型的維度看，OceanBase 發明了基於 LSM-TREE 儲存結構的基表訪問路徑選擇。從計劃空間的角度看，因為 OceanBase 原生就是一個分散式關聯式資料庫系統，它必然要面臨的一個問題就是分散式計劃最佳化。從計劃管理的角度看，OceanBase 有一整套完善的計劃管理機制。

1.基於 LSM - TREE 的基表訪問路徑選擇

基表訪問路徑選擇方法是指最佳化器選擇索引的方法，其本質是要評估每一個索引的代價並選擇代價最小的索引來訪問資料庫中的表。對於一個索引路徑，它的代價主要由兩部分組成，掃描索引的代價和回表的代價(如果一個索引對於一個查詢來說不需要回表，那麼就沒有回表的代價)。

通常來說，索引路徑的代價取決於很多因素，比如掃描/回表的行數，投影的列數，謂詞的個數等。為了簡化我們的討論，在下面的分析中，我們從行數這個維度來介紹這兩部分的代價。 

• 掃描索引的代價

掃描索引的代價跟掃描的行數成正比，而掃描的行數則是由一部分查詢的謂詞來決定，這些謂詞定義了索引掃描開始和結束位置。理論上來說掃描的行數越多，執行時間就會越久。掃描索引的代價是順序 IO。

• 回表的代價

回表的代價跟回表的行數也是正相關的，而回表的行數也是由查詢的謂詞來決定，理論上來說回表的行數越多，執行時間就會越久。回表的掃描是隨機 IO，所以回表一行的代價通常會比順序掃描索引一行的代價要高。

在傳統關聯式資料庫中，掃描索引的行數和回表的行數都是透過最佳化器中維護的統計資訊來計算謂詞選擇率得到(或者透過一些更加高階的方法比如動態取樣)。

舉個簡單的例子，給定聯合索引(a,b)和查詢謂詞 a > 1 and a < 5 and b < 5, 那麼謂詞 a > 1 and a < 5 定義了索引掃描開始和結束的位置，如果滿足這兩個條件的行數有 1w 行，那麼掃描索引的代價就是 1w 行順序掃描，如果謂詞 b < 5 的選擇率是 0.5，那麼回表的代價就是 5k 行的隨機掃描。

那麼問題來了： 傳統的計算行數和代價的方法是否適合基於 LSM-TREE 的儲存引擎？

LSM-TREE 儲存引擎把資料分為了兩部分(如下圖所示)， 靜態資料 (基線資料)和 動態資料 (增量資料)。其中靜態資料不會被修改，是隻讀的，儲存於磁碟；所有的增量修改操作（增、刪、改）被記錄在動態資料中，儲存於記憶體。靜態資料和增量資料會定期的合併形成新的基線資料。在 LSM-TREE 儲存引擎中，對於一個查詢操作，它需要合併靜態資料和動態資料來形成最終的查詢結果。

考慮下圖中 LSM-TREE 儲存引擎基線資料被刪除的一個例子。在該圖中，基線中有 10w 行資料，增量資料中維護了對這 10w 行資料的刪除操作。在這種場景下，這張表的總行數是 0 行，在傳統的基於 Buffer-Pool 的儲存引擎上，掃描會很快，也就是說行數和代價是匹配的。但是在 LSM-TREE 儲存引擎中，掃描會很慢(10w 基線資料 + 10w 增加資料的合併)，也就是行數和代價是不匹配的。

這個問題的本質原因是在基於 LSM-TREE 的儲存引擎上，傳統的基於動態取樣和選擇率資訊計算出來的行數不足以反應實際計算代價過程中需要的行數。

舉個簡單的例子，在傳統的關聯式資料庫中，我們插入 1w 行，然後刪除其中 1k 行，那麼計算代價的時候會用 9k 行去計算，在 LSM-TREE 的場景下，如果前面 1w 行是在基線資料裡面，那麼記憶體中會有額外的 1k 行，在計算代價的時候我們是需要用 11k 行去計算。

為了解決 LSM-TREE 儲存引擎的計算代價行數和表中真實行數不一致的行為，OceanBase 提出了“邏輯行”和“物理行”的概念以及計算它們的方法 。其中邏輯行可以理解為傳統意義上的行數，物理行主要用於刻畫 LSM-TREE 這種儲存引擎在計算代價時需要真正訪問的行數。

再考慮上圖中的例子，在該圖中，邏輯行是 0 行，而物理行是 20w 行。給定索引掃描的開始/結束位置，對於基線資料，因為 OceanBase 為基線資料維護了塊級別的統計資訊，所以能很快的計算出來基線行數。對於增量資料，則透過動態取樣方法獲取增/刪/改行數，最終兩者合併就可以得到邏輯行和物理行。下圖展示了 OceanBase 計算邏輯行和物理行的方法。

相比於傳統的基表訪問路徑方法，OceanBase 的基於邏輯行和物理行的方法有如下兩個優勢:

優勢一：實時統計資訊

因為同時考慮了增量資料和基線資料，相當於統計資訊是實時的，而傳統方法的統計資訊蒐集是有一定的滯後性的(通常是一張表的增/刪/修改操作到了一定程度，才會觸發統計資訊的重新蒐集)。

優勢二：解決了索引列上的謂詞依賴關係

考慮索引(a,b)以及查詢條件 a = 1 and b = 1 , 傳統的方法在計算這個查詢條件的選擇率的時候必然要考慮的一個問題是 a 和 b 是否存在依賴關係，然後再使用對應的方法(多列直方圖或者動態取樣)來提高選擇率計算的正確率。OceanBase 目前的估行方法預設能夠解決 a 和 b 的依賴關係的場景。

2.OceanBase 分散式計劃最佳化

OceanBase 原生就有分散式的屬性，那麼它必然要解決的一個問題就是分散式計劃最佳化。很多人認為分散式計劃最佳化很難，無從下手，那麼分散式計劃最佳化跟本地最佳化到底有什麼區別？分散式計劃最佳化是否需要修改現有的查詢最佳化框架來做最佳化？

在筆者看來，現有的查詢最佳化框架完全有能力處理分散式計劃最佳化，但是分散式計劃最佳化會大大增加計劃的搜尋空間，主要原因如下:

1、在分散式場景下，選擇的是運算元的分散式演算法，而運算元的分散式演算法空間比運算元本地演算法的空間要大很多。下圖展示了一個 Hash Join 在分散式場景下可以選擇的分散式演算法。

2、在分散式場景下，除了序這個物理屬性之外，還增加了分割槽資訊這個物理屬性。分割槽資訊主要包括如何分割槽以及分割槽的物理資訊。分割槽資訊決定了運算元可以採用何種分散式演算法。

3、在分散式場景下，分割槽裁剪/並行度最佳化/分割槽內(間)並行等因素也會增大分散式計劃的最佳化複雜度。

OceanBase 目前採用兩階段的方式來做分散式最佳化。在第一階段，OceanBase 基於所有表都是本地的假設生成一個最優本地計劃。在第二階段，OceanBase 開始做並行最佳化, 用啟發式規則來選擇本地最優計劃中運算元的分散式演算法。下圖展示了 OceanBase 二階段分散式計劃的一個例子。

OceanBase 二階段的分散式計劃最佳化方法能減少最佳化空間，降低最佳化複雜度，但是因為在第一階段最佳化的時候沒有考慮運算元的分散式資訊，所以可能導致生成的計劃次優。目前 OceanBase 正在實現一階段的分散式計劃最佳化:

1、在 System-R 的 Bottom-up 的動態規劃演算法中，列舉所有運算元的所有分散式實現並且維護運算元的物理屬性。

2、在 System-R 的 Bottom-up 的動態規劃演算法中，對於每一個列舉的子集, 保留代價最小/有 Interesting order/有 Interesting 分割槽的計劃。

一階段的分散式計劃最佳化可能會導致計劃空間增長很快，所以必須要有一些 Pruning 規則來減少計劃空間或者跟本地最佳化一樣在計劃空間比較大的時候，使用遺傳演算法或者啟發式規則來解決這個問題。

3.OceanBase 計劃管理機制

OceanBase 基於螞蟻/阿里真實的業務場景，構建了一套完善的計劃快取機制和計劃演進機制。

OceanBase 計劃快取機制

如下圖所示，OceanBase 目前使用引數化計劃快取的方式。這裡涉及到兩個問題：為什麼選擇引數化以及為什麼選擇快取？

1、 引數化 ：在螞蟻/阿里很多真實業務場景下，為每一個引數快取一個計劃是不切實際的。考慮一個根據訂單號來查詢訂單資訊的場景，在螞蟻/阿里高併發的場景下，為每一個訂單號換成一個計劃是不切實際的，而且也不需要，因為一個帶訂單號的索引能解決所有引數的場景。

2、 計劃快取 ：計劃快取是因為效能的原因，對於螞蟻/阿里很多真實業務場景來說，如果命中計劃，那麼一個查詢的效能會在幾百 us，但是如果沒有命中計劃，那麼效能大概會在幾個 ms。對於高併發，低時延的場景，這種效能優勢是很重要的。

OceanBase 使用引數化計劃快取的方式，但是在很多螞蟻真實的業務場景下，對所有的引數使用同一個計劃並不是最優的選擇。考慮一個螞蟻商戶域的業務場景，這個場景以商戶的維度去記錄每一筆賬單資訊，商戶可以根據這些資訊做一些分析和查詢。這種場景肯定會存在大小賬號問題，如下圖所示，淘寶可能貢獻了 50% 的訂單，LV 可能只貢獻了 0.1% 的訂單。考慮查詢“統計一個商戶過去一年的銷售額”，如果是淘寶和美團這種大商戶，那麼直接主表掃描會是一個合理的計劃，對於 LV 這種小商戶，那麼走索引會是一個合理的計劃。

為了解決不同引數對應不同計劃的問題，OceanBase 實現瞭如下圖所示的自適應計劃匹配。該方法會透過直方圖和執行反饋來監控每一個快取的計劃是否存在不同引數需要對應不同計劃的問題。一旦存在，自適應計劃匹配會透過漸進式的合併選擇率空間來達到把整個選擇率空間劃分成若干個計劃空間(每個空間對應一個計劃)的目的。

OceanBase 計劃演進機制

在螞蟻/阿里很多高併發，低時延的業務場景下，OceanBase 必須要保證新生成的計劃不會導致效能回退。下圖展示了 OceanBase 對新計劃的演進過程。不同於傳統的資料庫系統採用定時任務和後臺程式演進的方式，OceanBase 會使用真實的流量來進行演進，這樣的一個好處是可以及時的更新比較優的計劃。比如當業務新建了一個更優的索引時，傳統資料庫系統並不能立刻使用該索引，需要在演進定時任務啟動後才能演進驗證使用，而 OceanBase 可以及時的使用該計劃。

總結

OceanBase 查詢最佳化器的實現立足於自身架構和業務場景特點，比如 LSM-TREE 儲存結構、Share-Nothing 的分散式架構和大規模的運維穩定性。OceanBase 致力於打造基於 OLTP 和 OLAP 融合的查詢最佳化器。從 OLTP 的角度看，我們立足於螞蟻/阿里真實業務場景，完美承載了業務需求。從 OLAP 的角度看，我們對標商業資料庫，進一步打磨我們 HTAP 的最佳化器能力。