最佳化器在資料庫中一直位於至關重要的位置，效能調優也常常需要圍繞最佳化器來進行。作為資料庫廠商，我們希望在各類複雜的業務場景中，TiDB 都能夠給出比較理想的執行計劃，因此在最佳化器和執行器上做了非常多的工作和努力，但是選錯執行計劃或者索引的情況仍然是日常中最為常見的一個問題。

最佳化器有關的問題可以簡單歸結為兩種：

統計資訊準確的情況下給出了錯誤的執行計劃。
另一類則是在統計資訊過期的情況下給錯了執行計劃。

選錯索引是其中比較常見的一種情況，使用者希望新增索引來加速查詢速度，某些情況下，最佳化器可能會走到全表掃的物理執行計劃或者選錯索引使得實際執行效果退化成全表掃的情況。

針對上述情況，我們需要從更微觀的層面來度量最佳化器的執行計劃和索引選擇的效能，評估在最佳化器上做的改進工作能否切實起到期望的效果。

為什麼我們要開發 Horoscope?

為了測量最佳化器和執行器，從去年開始我們構建了daily benchmark 平臺 perf.pingcap.com，覆蓋常見的幾種複雜查詢的測試場景，包含 TPC-H、TPC-DS、Star Schema Benchmark 等，跟蹤每天開發分支上這些查詢的執行速度情況。

透過 daily benchmark，我們觀測和定位到了若干次效能提升以及效能回退的情況。有些提升或者回退是最佳化器元件上的最佳化導致的，有些則是 TiDB 其他元件，或者儲存層引發的。

雖然 daily benchmark 能夠觀測到效能改進或者回退，但是對於以下幾個問題它卻束手無策：

當前選擇的執行計劃是否最優？選擇率估計是否準確？
是否選擇到了正確的索引？
現有的啟發演算法能否應對統計資訊一定程度的過期？

因此，我們需要另外一種更系統的測試工具，用於最佳化器的測量。

Horoscope 是如何做的?

要測量最佳化器，我們需要：

定義最佳化器的效能指標
遍歷執行計劃空間
資料集以及查詢生成

定義最佳化器的效能指標

這裡我們參考“OptMark: A Toolkit for Benchmarking Query Optimizers”給出的方法來度量最佳化器有效性。簡單地講某個查詢的有效性指標，是指在可遍歷的執行計劃空間中，最佳化器選出的預設執行計劃的執行時間比其他的執行計劃的執行時間更快的比例。

例如 100% 可以解釋為預設執行計劃的執行時間比其他執行計劃的執行時間都更快，50% 解釋為有一半的執行計劃要比預設執行計劃更快。

遍歷執行計劃空間

由於需要一種方式能夠讓 TiDB 按照我們所指定的物理執行計劃來實際執行查詢，為此我們在 TiDB 中新增了 nth_plan(n) 這個 SQL hint。

當查詢語句提交到 TiDB 後，TiDB 會為搜尋空間中的每個執行計劃繫結一個固定的序號，透過這個序號我們就能指定最佳化器去選擇哪一個執行計劃。

nth_plan 的序號從 1 開始遞增，當其超出最佳化器對該條查詢的搜尋空間時，查詢返回會產生一個 warning 來提示當前已經完成了搜尋空間上的遍歷。


TiDB(root@127.0.0.1:test) 
> 
explain 
select 
/*+ 
nth_plan(1) 
*/ 
* 
from 
t 
where 
a 
= 
1 
and 
b 
> 
0 
and 
b 
< 
10
;


+-------------------------+----------+-----------+---------------+----------------------------------------------------+


| 
id                      
| 
estRows  
| 
task      
| 
access 
object 
| 
operator 
info                                      
|


+-------------------------+----------+-----------+---------------+----------------------------------------------------+


| 
TableReader_7           
| 
0.25     
| 
root      
|               
| 
data:Selection_6                                   
|


| 
└─Selection_6           
| 
0.25     
| 
cop[tikv] 
|               
| 
eq(hehe.t.a, 
1
), 
gt(hehe.t.b, 
0
), 
lt(hehe.t.b, 
10
) 
|


|   
└─TableFullScan_5     
| 
10000.00 
| 
cop[tikv] 
| 
table:t       
| 
keep 
order:false, 
stats:pseudo                     
|


+-------------------------+----------+-----------+---------------+----------------------------------------------------+


3 
rows 
in 
set 
(0.00 
sec)




TiDB(root@127.0.0.1:test) 
> 
explain 
select 
/*+ 
nth_plan(2) 
*/ 
* 
from 
t 
where 
a 
= 
1 
and 
b 
> 
0 
and 
b 
< 
10
;


+-------------------------------+---------+-----------+-------------------------+---------------------------------------------+


| 
id                            
| 
estRows 
| 
task      
| 
access 
object           
| 
operator 
info                               
|


+-------------------------------+---------+-----------+-------------------------+---------------------------------------------+


| 
IndexLookUp_11                
| 
0.25    
| 
root      
|                         
|                                             
|


| 
├─IndexRangeScan_8(Build)     
| 
10.00   
| 
cop[tikv] 
| 
table:t, 
index:idx_a(a) 
| 
range:[1,1], 
keep 
order:false, 
stats:pseudo 
|


| 
└─Selection_10(Probe)         
| 
0.25    
| 
cop[tikv] 
|                         
| 
gt(hehe.t.b, 
0
), 
lt(hehe.t.b, 
10
)           
|


|   
└─TableRowIDScan_9          
| 
10.00   
| 
cop[tikv] 
| 
table:t                 
| 
keep 
order:false, 
stats:pseudo              
|


+-------------------------------+---------+-----------+-------------------------+---------------------------------------------+


4 
rows 
in 
set 
(0.00 
sec)

資料集以及查詢生成

網際網路上有很多開放的資料集，也有一些 benchmark 提供了 dbgen 工具用來隨機構造資料集，我們比較傾向於選擇真實資料集，因此我們選擇了 IMDB 資料集來進行測試。

有了資料集，我們需要在其上構造一些查詢。為了測試索引選擇問題，參考 Manuel Rigger 的 “Testing Database Engines via Pivoted Query Synthesis” 論文中的思路，Horoscope 會在某些表中隨機選擇一行資料作為 pivot row 去構建查詢，使得查詢返回的結果會包含這些選擇的行。透過這種方式，我們能保證生成的查詢是更具意義。

例如針對索引選擇問題，查詢構造的流程如下所示，透過在有索引覆蓋的列上構造條件來測試是否選對了索引。

例如會生成如下的查詢：

開始測量

我們預先匯入了一份 IMDB 資料集到 imdb 資料庫中，可以透過如下命令使用 Join Order Benchmark 的查詢度量有效性指標。

$ git clone 
https:/
/github.com/chaos-mesh/horoscope.git 

$ cd horoscope && make 

$ ./bin/horo --round 
4 -d 
root:@tcp(
localhost:
4000)/imdb?charset=utf8 bench -p -w benchmark/job

經過漫長的等待，在測量結束時 Horoscope 會出入一份測試報告：

ID 列標識查詢的名稱，#PLAN SPACE 是這條查詢當前 TiDB 的搜尋空間，DEFAULT EXECUTION TIME 記錄了預設執行計劃的執行時間（透過中值以及上下界偏差比例給出），BEST PLAN EXECUTION TIME 給出最優的執行計劃的執行時間，EFFECTIVENESS 算出該條查詢最佳化器的有效性，BETTER OPTIMAL PLANS 給出更優的執行計劃的 ID 以及對應執行時間和預設執行計劃執行時間的佔比。

我們使用 Horoscope 測量了不同數量級的 TPC-H，並且 IMDB 資料集上針對索引選擇生成了一些查詢來測試。我們也在 Github 上建立了一個專案來跟蹤這些問題的進展：

相比於 TPC-H，Horoscope 在 IMDB 的資料集和查詢上發現了更多更優的執行計劃，但因為 IMDB 資料是靜態的，當想驗證統計資訊過期場景下最佳化器的情況時比較困難。

為此 Horoscope 提供了將資料按照某個欄位進行切分然後匯出的功能，透過分批次插入資料，提供了資料更新情況下的最佳化器測試場景。

資料切片和按切片更新資料

真實資料集上的資料分佈往往具備傾斜的特徵，而這種傾斜的性質對於最佳化器也更有挑戰。

以 IMDB 為例，資料在 title.produciton_year 上發生了傾斜，越靠後的年份，所關聯的資料行數越多。我們透過對資料集在 title.prodution_year 上將資料集切分成一塊塊不均等的切片，再進行分批匯入，可以模擬資料修改所引發的統計資訊過期對於最佳化器的影響。

切分的過程如下：

將各個表之間的關係透過主外來鍵進行關聯，構造出一張無向圖；
選擇某個表上的欄位，查詢出其上不同的值；
以這個欄位的值作為線索，構造查詢語句，在無向圖上串聯不同表上能關聯到的資料，匯出到切片檔案中；
標記串聯過的資料，後續切片忽略已標記過的資料。

在 IMDB 上，我們選擇 title.produciton_year 進行資料切分，切分後每個切片檔案的大小如下圖所示。

約有一半的資料集中在最後 20 份切片中，越往後匯入資料的修改行增速越快，統計資訊的過期速度也愈快。

我們設計了 2 個對照試驗，實驗開始之前預先匯入切片 0 到切片 124 的資料，並從切片 125 開始，每匯入一個切片，測量一輪各查詢的有效性指標。

在第一組試驗中我們關閉了 auto analyze 和 feedback，第二組關閉了 auto analyze 但會開啟 feedback。然後讓 Horoscope 隨機生成一批簡單查詢，在得到資料後我們分別繪製了有效性指標的比例曲線以及散點圖。

曲線上的點表示有效性指標大於橫座標數值的查詢的比例。從資料上看，當開啟 feedback 時，有 50.77% 查詢的有效性指標超過了 80%，即對於一半以上的查詢最佳化器選擇到了較優的執行計劃。而當關閉 feedback 時，這個比例只有 38.70%。這和我們通常所認為的 feedback 能夠一定程度抵抗統計資訊過期相符。

另外從散點圖上看會發現開啟 feedback 也有可能會讓最佳化器選擇到更差的執行計劃。例如下面的這條 SQL，feedback 機制反而使最佳化器選擇到了更差的執行計劃，這些可以作為 bad case 來具體分析。


SELECT *


FROM title

WHERE (title.id 
IS 
NOT 
NULL

       
AND title.title!=
"(#1.69)"

       
AND title.imdb_index 
IS 
NULL

       
AND title.kind_id<
8

       
AND title.production_year!=
1974

       
AND title.imdb_id 
IS 
NULL

       
AND title.phonetic_code 
IS 
NULL

       
AND title.episode_of_id>
184590

       
AND title.season_nr 
IS 
NULL

       
AND title.episode_nr 
IS 
NULL

       
AND title.series_years 
IS 
NULL

       
AND title.md5sum<=
"7cf95ddbd379fdb3e530e0721ff61494")


LIMIT 
100

後記

Horoscope 還可以做更多的事情，例如當版本升級時，可以用 Horoscope 來測試執行計劃會不會變化，如果變化了，是否發生了回退。

使用者線上的資料通常十分敏感，我們內部積累了比較多的有統計資訊和 schema 但無實際資料的用例集，透過 Horoscope 我們現在希望能夠將這些用例集利用起來，擴充最佳化器測試用例，來幫助最佳化器的開發者們決策一些最佳化策略是否要合併到下一版本中。

此外，Horoscope 也提供了一種測試最佳化器正確性的途徑。我們正在計劃讓 Horoscope 生成更復雜的查詢，透過比對每個物理執行計劃的結果來驗證最佳化器實現的正確性。

最佳化器的工作是個長期且難度非常大的事情，最佳化器的測試也是如此，如果您有更多更好的關於最佳化器或者其他元件的最佳化以及測試的方法或者思路，歡迎在 TiDB 社群中和我們進行交流。

使用 Horoscope 測試 TiDB 最佳化器