878
技術社區[雲棲]
MySQL · 性能優化· CloudDBA SQL優化建議之統計信息獲取
阿裏雲CloudDBA具有SQL優化建議功能，包括SQL重寫建議和索引建議。SQL索引建議是幫助數據庫優化器創造最佳執行路徑，需要遵循數據庫優化器的一係列規則來實現。CloudDBA需要首先計算表統計信息，是因為：
- 數據庫優化器通常是基於代價尋找執行路徑；
- SQL優化建議所針對的數據庫不限於MySQL數據庫，也不局限於某一個特定版本；
1. 基本原則
數據庫統計信息在SQL優化起到重要作用。用來估算查詢條件選擇度的常見統計信息包括表統計信息和字段統計信息。DBA計算查詢條件選擇度或代價時經常通過手工執行SQL語句獲取，並進行返回行數或代價的粗略估算。
- 表統計信息：表中總記錄數；
- 字段統計信息：包括最大值，最小值；以及不同值個數；
而要相對更準確的獲取條件選擇度的估算，往往需要統計直方圖(Histogram)，因為多數情況，每個值的出現頻度是不一樣的。針對複雜SQL的優化，比如多條件查詢、Range查詢以及多表關聯查詢等，統計直方圖能幫助DBA更好的進行代價估算。
在雲上環境，獲取統計信息以最小代價為前提的，不能對生產係統造成任何性能上的負麵影響，也不能耗費較長時間。獲取統計數據的基本原則如下：
- 從備庫獲取統計數據；
- 隻統計最近數據；
- 采取抽樣的方式獲取數據；
- 不抽取原始數據，隻對數據的hash值進行統計；
2. 最近數據統計
長期變化的數據通常具有周期性，並且以天為基本周期符合一般業務邏輯。因此多數情況無需對全量數據進行統計，抽取最近一天的數據通常具有代表性。
3. 樣例數據統計
雲上數據庫通常要求表設計中有自增主鍵。在這一條件下獲取表的最近數據的方法較為簡單，比如：
select * from tab order by id desc limit 1000;
該語句通過在自增主鍵上做排序並獲取最近插入的1000行數據。由於id是主鍵，排序並無額外代價。類似方式可以獲取第其它樣例數據，比如：
select * from tab order by id desc limit 10000, 1000;
4. 數據特征分析
基於抽樣數據，對影響選擇度或查詢返回行數的特性進行分析：
-
數據頻率
對每一份樣例數據中不同字段的頻率統計之後，需要推導出或預測字段中的某個數值在全表中的頻率情況。通過分析不同樣例數據間的數據重合度在具體實踐中具有實際意義。
-
數據密度
獲取每個字段的最大值和最小值代價較高。變通方法就是通過樣例數據的最大最小值以及頻率進行數據密度計算。基於數據密度數據，估算範圍查詢返回行數。
-
字段關聯性
評估多條件查詢的選擇度需要首先獲取字段之間的關聯性。若多條件查詢條件關聯性很低，則綜合選擇度就是單個條件選擇度的乘積；若多條件查詢條件關聯性較高，則采用最小選擇度（或乘以係數）作為綜合選擇度。
5. 總結
- 直方圖是對基本數據的估計，任何直方圖都不是精確的；
- 雲上環境以最小代價獲取統計數據是基本前提；
- 數據庫優化器需要選擇的是最佳路徑，得出字段之間選擇度的相對值更為重要；
最後更新：2017-10-21 09:03:55