Oceanbase 和 TiDB 粗淺對比之 - 執行計劃

一、前言

     OceanBase和TiDB作為國內2款的比較流行的相容MySQL協議的開源資料庫使用者也越來越多，兩種資料庫不僅在架構原理上有較大差異，在開源方式上有較大的不同：

     TiDB 採用的Apache License 2.0開源協議，其第一行程式碼提交就是在github上，和企業版相比社群版只是不包含訪問白名單和審計2個外掛功能，其他與企業版完全相同且同步發版(之前閉源的tiflash也於2022.4.1完全開源)。

     OceanBase社群版採用國內的木蘭公共協議 MulanPubL-2.0開源，官方劃分成社群版、雲服務版、企業版三種型別，開源的社群版與企業版相比存在較多功能缺失或效能降低(如目前對比版本不支援oracle相容、不支援閃回、不支援analyze語句、ocp不支援備份功能等)，且社群版本版釋出與企業版不同步。另外ob的文件和資源相比較tidb還不夠完善和豐富。

   本文針對tidb、oceanbase在執行計劃的相關內容進行粗淺的對比，也對學習做個總結，對比版本為OceanBase3.1.2-CE(2022-03-29發版 )、TiDB v5.2.3(2021-12-31發版)

二、檢視執行計劃

• TiDB：

(1) explain SQL方式：該方式只是展示可能的執行計劃並非實際的執行計劃，目前各資料庫都存在此問題使用explain方式並不是真正SQL執行時的計劃，少數情況下會存在不一致。

(2) explain analyze方式：該方式會真正執行SQL並展示執行時的執行計劃，執行計劃中增加實際的執行資訊包括實際返回行數、各運算元時間和呼叫及資源消耗等。

(3) select tidb_decode_plan()方式: tidb的慢SQL日誌裡會以hash值方式記錄慢SQL的執行計劃，然後使用tidb_decode_plan()函式即可解析。

(4) dashboard檢視：tidb的PD元件包含dashboard功能，慢SQL、SQL統計頁面可以檢視每個SQL的執行計劃

• OceanBase:

(1) explain SQL方式：包含BASIC、OUTLINE、EXTENDED、EXTENDED_NOADDR、PARTITIONS、FORMAT = {TRADITIONAL| JSON}多個展示選項，除了extended方式大部分情況展示的內容基本一致，extended方式時會增加hint、outline、plan type、optimizerinfo等資訊。

(2) 使用系統檢視方式：oceanbase在實現上一直努力方便oracle dba使用，透過v$plan_cache_plan_explain/ v$plan_cache_plan_stat等檢視可以檢視執行計劃及運算元的執資訊(如行數、時間等)，類似oracle的v$sql、v$sql_plan等檢視

(3) 因未部署ob 圖形化管理平臺ocp，因此未看SQL執行計劃的頁面展示。

三、執行計劃內容

• TiDB:

   TiDB的執行計劃展示與oracle類似，以縮排的方式展示運算元間的層次關係，同時使用折線進行運算元連線展示，當SQL複雜執行步驟較多時可以很明顯看出處於同一縮排深度的運算元，explian方式下執行計劃包括運算元資訊(id列)、預估行數(estRows列)，訪問物件(access object列)、過濾條件和操作資訊(operator info列)

     使用explain analyze或檢視慢SQL中記錄的執行計劃時還包括每個運算元實際的返回行數(actRows列)、運算元的執行時間和分佈統計(execution info列)、記憶體佔用(memory)、磁碟讀(dsik)

execution info列展示的內容如下：

     execution info因為是和每個運算元展示成一行，且資訊較多輸出時較多換行，對執行計劃閱讀有些影響，如果能放到下面進行額外展示的話，就能使執行計劃步驟展示看起來更方便些。

• OceanBase:

   Oceanbase將執行計劃劃分為了本地執行計劃、遠端執行計劃、分散式執行計劃。執行計劃展示非常接近oracle的展示方式，explain basic下展示執行計劃和output&filter。 樹形執行計劃中包括運算元展示id、運算元內容(OPERATOR列)、訪問的物件資訊(NAME列)、預估行數(EST. ROWS)、評估的成本(COST)。output&filter展示的列過濾和投影后列資訊，相比oracle展示的內容沒有access資訊，且列值可讀性差。

     在exteneded方式下還包括SQL使用HINT、SQL執行生產的outline(outline部分基本和oracle一致)、最佳化器的執行資訊optimizer info。

     Oceanbase中關於路徑訪問的運算元較少，目前只有TABLE GET(直接主鍵定位)、TABLE SCAN(全表或索引掃描回表)、LOOKUP TABLE(全域性索引掃描回表)，針對執行計劃中是否使用索引需要結合name列是否有索引以及filter中is_index_back=true判斷，對於掃描方式不夠直接和方便，比如索引全掃描、索引範圍掃描、是否使用覆蓋索引等。對於分割槽資訊的顯示ocenbase和oracle一樣展示的分割槽partition id，tidb內展示的是分割槽名更直觀一些。

四、慢SQL記錄

• TiDB:

   超過slow_launch_time引數值的SQL會被記錄到tidb_slow_query.log。可透過information_schema.CLUSTER_SLOW_QUERY或dashboard檢視。

• Oceanbase:

   執行時間超過trace_log_slow_query_watermark引數值設定的會記錄到observer.log。
  
   使用檢視v$plan_cache_plan_explain/ v$plan_cache_plan_stat也可以按條件過濾慢SQL，不過查詢時如果沒有指定ip\port\tenant\plan_id等條件是資料返回空行，即使count(*)整個基表表也是返回空。
  
   此外還可以透過v$sql_audit檢視查詢會話和SQL執行資訊，其類似於oracle的v$session檢視。

五、HINT

     對於hint使用OceanBase和tidb的方式基本一樣，oceanbase中除了常規的hint外，還可以像oracle一樣使用 outline data作為hint內容。

六、執行計劃繫結

• TiDB:

   TiDB執行計劃繫結功能叫SPM(sql plan managment)包括手動繫結執行計劃、自動捕獲執行計劃和演進功能。執行SQL繫結時會將SQL進行標準化進行變數值的替換和空格轉換等，在執行SQL時會將SQL進行標準化，與標準化後的SQL進行比對，如果一直則使用繫結的執行計劃。TiDB中繫結SQL與原始SQL大小寫不一致、空格換行不一致等不影響繫結使用。TiDB內不能使用SQL_digest/plan_digest等hash值方式進行SQL繫結，在建立和刪除繫結時都必須使用原始SQL和HINT SQL，對於較長的複雜SQL不是很方便。
  
   執行計劃繫結詳細資訊可參考官方文件和專欄文章：<https://tidb.io/blog/83b454f1>

• OceanBase:

   Oceanbase的執行計劃繫結可使用2種方式，2個從概念上都參考了oracle，一個是使用outline方式進行執行計劃繫結，一個是使用SPM方式進行繫結和執行計劃捕獲和演進(開源版不支援SPM)。Outline使用方式和tidb建立SQL binding類似都是使用HINT SQL和原始SQL繫結，不過oceanbase的SQL繫結嚴格要求原始SQL和HINT SQL必須完全一致（類似oracle的sql_id計算），大小寫和空格對繫結有影響。Oceanbase支援使用SQL_ID、PLAN_ID的值進行執行計劃繫結，方便繫結操作。
  
   無論TiDB還是OceanBase兩個都不支援HINT SQL使用force index類提示繫結執行計劃。

     Oceanbase的SPM執行計劃管理和oracle非常類似，都是使用dbms_spm包進行管理，其語法基本一致，同樣透過幾個引數控制是否進行自動繫結和演進。

七、執行計劃快取

• TiDB:

   使用Prepare/execute方式，Prepare 時將引數化的 SQL 查詢解析成 AST（抽象語法樹），每次 Execute 時根據儲存的 AST 和具體的引數值生成執行計劃，對於Prepare的語句在第一次execute時會檢查該語句是否可以使用執行計劃快取(比如包含分割槽表、子查詢的語句不能快取)，如果可以則將語句執行計劃放入到快取中，後續的execute會首先檢查快取中是否有執行計劃可用，有的話則進行合法性檢查，透過後使用快取的執行計劃，否則重新生成執行計劃放入到快取中。
  
   快取是session級的，以LRU連結串列方式管理，連結串列元素為kv對，key由庫名、prepare語句標識、schema版本、SQL_Mode、timezone組成，value是執行計劃。透過prepared-plan-cache下的相關選項可以控制是否啟用快取、快取條目數和佔記憶體大小。

• OceanBase:

   Oceanbase內除了可以使用prepare方式外，oceanbase對執行計劃快取參照oracle做了大量工作。和Oracle rac類似每個observer只管理自己節點上的快取，不同節點相同SQL快取的執行計劃可能不同。
  
   Oceanbase將SQL文字進行引數化處理後作為執行計劃快取的鍵值key，value是執行計劃。Oceanbase的SQL匹配也參考了oracle，引入了cursor_sharing引數和HINT，引數值為excat要求SQL匹配必須完全一樣，包括空格、大小寫、欄位值等。引數值為force時則以引數化後的SQL進行匹配。

除此之外ocenabase也引入了自適應遊標共享ACS功能，針對一個SQL在使用不同欄位值時使用不同的執行計劃，透過引數可控制是否開啟該功能。

     快取的執行計劃可透過透過v$plan_cache_plan_explain/ v$plan_cache_plan_stat檢視。

開源版不支援cursor_sharing和ACS功能。

八、統計資訊

• TiDB:

   tidb統計資訊收集包括自動統計資訊收集和手動統計資訊收集。自動統計資訊收集根據表的情況和引數tidb_auto_analyze_start_time/tidb_evolve_plan_task_end_time/ tidb_auto_analyze_ratio決定何時進行統計資訊收集。手動統計資訊收集根據需要隨時執行analyze SQL。
  
   TiDB支援feedback特性，即在SQL執行時根據實際的執行資訊去更新統計資訊，以使統計資訊根據準確和及時更新，不過由於feedback特性會導致一些問題，改特性預設為關閉。Oracle資料庫在11g引入該特性時也引起一些問題，大部分情況DBA會將該功能關閉。
  
   Tidb內的統計資訊可以使用show stats_meta/stats_buckets/stats_histograms等檢視。
  
   關於統計資訊收集的更詳細收集可參考：https://tidb.io/blog/92447a59

• OceanBase:

   Oceanbase社群版不支援analyze語句收集統計資訊(商業版3.2才引入)，儲存層進行合併時更新統計資訊，可以手工觸發合併操作進行更新。SQL執行時從memtable進行動態取樣，取樣比例固定，無法更改。
  
   相關統計資訊可從_all_table_stat，__all_column_stat, __all_histogram_stat等系統檢視檢視。

九、SQL trace

• TiDB:

   tidb 直接使用trace SQL執行即可展示trace結果。Operation列展示函式呼叫層次和訪問的region資訊，startTS了展示該步的開始時間，duartion展示該步的消耗時間。

• OceanBase:

   OceanBase的trace使用類似和結果類似於mysql的Profiling。執行過程如下：

(1) 開啟trace: SET ob_enable_trace_log = 1;

(2) 執行SQL

(3) Show trace檢視，然後SET ob_enable_trace_log =0 關閉

從展示結果上看其資訊的直觀性和可用性上不如tidb。

十、遇到的問題

• TiDB:

(1) 執行計劃中不顯示不顯示子查詢的表資訊，無法判斷使用的掃描方式

該問題目前暫未完成修復: github.com/pingcap/tidb/issues/220...

     oceanbase執行計劃如下：

• OceanBase:

(1) 對於子查詢中不存在的列不會報錯仍然繼續執行

     Tidb執行如下：

(2) Oceanbase無法使用索引

     按id列進行小範圍查詢時無法使用id列索引，執行手工合併後仍然是全表掃描執行計劃。

tidb執行計劃：

(3) 不同的index hint方式導致執行計劃不同

TIDB執行計劃：

(4) explian展示的執行計劃不能使用繫結後的Outline ，資料字典內記錄的執行計劃使用了索引

Tidb執行計劃：

(5) 執行Prepare後會導致會話斷開，再次執行後成功，對於互動式客戶端oceanbase不支援顯示查詢結果。

TiDB執行計劃：

(6) Obproxy可能會和多個後端observer建立連線，導致相同會話執行的慢SQL會被記錄到多個observer的observer.log內(ob內使用資料字典查詢慢SQL資訊會更好些)。

十一、總結

     個人認為從功能上看oceanbase的執行計劃管理要TiDB更豐富些，如SPM、ACS等，但從實際使用看無論是操作的複雜性、執行計劃的可讀性、最佳化器的可靠性都要由於oceanbase。Oceanbase在各方面在努力的向oracle相容，比如系統檢視、SPM管理、自適應遊標共享、等待事件等，因架構不同、經驗積累等和oracle比還是有著不小的差距。

     針對TiDB建議如下:

(1) 執行計劃繫結管理可以使用sql_digest、plan_digest等，可避免使用SQL語句

(2) 執行計劃快取做成全域性管理方式，避免多個會話對相同SQL進行快取，浪費記憶體空間

(3) Explain analyze的execution info 在執行計劃下面獨立展示，否則執行計劃太長不方便閱讀。

作者：@h5n1 釋出時間：2022/4/12
原文連結：tidb.net/blog/f1fd1733

本作品採用《CC 協議》，轉載必須註明作者和本文連結