MySQL · 性能優化· CloudDBA SQL優化建議之統計資訊擷取

阿裡雲CloudDBA具有SQL優化建議功能，包括SQL重寫建議和索引建議。SQL索引建議是幫助資料庫優化器創造最佳執行路徑，需要遵循資料庫優化器的一系列規則來實作。CloudDBA需要首先計算表統計資訊，是因為：

資料庫優化器通常是基于代價尋找執行路徑；

SQL優化建議所針對的資料庫不限于MySQL資料庫，也不局限于某一個特定版本；

1. 基本原則

資料庫統計資訊在SQL優化起到重要作用。用來估算查詢條件選擇度的常見統計資訊包括表統計資訊和字段統計資訊。DBA計算查詢條件選擇度或代價時經常通過手工執行SQL語句擷取，并進行傳回行數或代價的粗略估算。

表統計資訊：表中總記錄數；

字段統計資訊：包括最大值，最小值；以及不同值個數；

而要相對更準确的擷取條件選擇度的估算，往往需要統計直方圖(Histogram)，因為多數情況，每個值的出現頻度是不一樣的。針對複雜SQL的優化，比如多條件查詢、Range查詢以及多表關聯查詢等，統計直方圖能幫助DBA更好的進行代價估算。

在雲上環境，擷取統計資訊以最小代價為前提的，不能對生産系統造成任何性能上的負面影響，也不能耗費較長時間。擷取統計資料的基本原則如下：

從備庫擷取統計資料；

隻統計最近資料；

采取抽樣的方式擷取資料；

不抽取原始資料，隻對資料的hash值進行統計；

2. 最近資料統計

長期變化的資料通常具有周期性，并且以天為基本周期符合一般業務邏輯。是以多數情況無需對全量資料進行統計，抽取最近一天的資料通常具有代表性。

3. 樣例資料統計

雲上資料庫通常要求表設計中有自增主鍵。在這一條件下擷取表的最近資料的方法較為簡單，比如：

該語句通過在自增主鍵上做排序并擷取最近插入的1000行資料。由于id是主鍵，排序并無額外代價。類似方式可以擷取第其它樣例資料，比如：

4. 資料特征分析

基于抽樣資料，對影響選擇度或查詢傳回行數的特性進行分析：

資料頻率

對每一份樣例資料中不同字段的頻率統計之後，需要推導出或預測字段中的某個數值在全表中的頻率情況。通過分析不同樣例資料間的資料重合度在具體實踐中具有實際意義。

資料密度

擷取每個字段的最大值和最小值代價較高。變通方法就是通過樣例資料的最大最小值以及頻率進行資料密度計算。基于資料密度資料，估算範圍查詢傳回行數。

字段關聯性

評估多條件查詢的選擇度需要首先擷取字段之間的關聯性。若多條件查詢條件關聯性很低，則綜合選擇度就是單個條件選擇度的乘積；若多條件查詢條件關聯性較高，則采用最小選擇度（或乘以系數）作為綜合選擇度。

5. 總結

直方圖是對基本資料的估計，任何直方圖都不是精确的；

雲上環境以最小代價擷取統計資料是基本前提；

資料庫優化器需要選擇的是最佳路徑，得出字段之間選擇度的相對值更為重要；