優化 SQL：語法與資料庫的最佳化應用
簡介
本書從基礎開始，一步一步引導讀者學習實用的最佳化技巧。作者首先解釋SQL最佳化的基礎概念，並以不同表連接的方式解釋其對效能的影響。在有了基本知識後，才會教讀者如何正確地對資料表收集統計資料，然後進一步推算SQL指令的成本，完成真正有效的最佳化。
SQL最佳化必懂概念
- 基數 : 欄位唯一鍵的數量，主鍵基數為row數，性別欄位基數為2，基數少表示有大量重複值
- 通常基數佔row數5%以下時走索引，以上時走全表掃描
- 選擇性 : 基數/row數*100%
- 選擇性大於20%表示資料分布均衡，需要建索引
- 只有大表才會產生效能問題
如果欄位的基數低，又沒有收集統計資訊，基於成本最佳化器(CBO, Cost-Based Optimization)會假設欄位是均衡的，此時用CBO對查詢估計欄位會不準- 執行計畫裡的row是根據統計資訊估算的，也就是假的
- SQL最佳化最需要做的工作，就是幫CBO估算出比較準確的row數
- 如果CBO每次都能算到精確的row數，就不會走錯執行計畫了
- 收集直方圖資訊可以幫助CBO在基數很低且分佈不均勻的欄位更精確的估算row數
- 對沒有出現在where條件的欄位收集直方圖只是浪費時間
- 當對欄位建索引後，索引會包含表中的鍵值以及對應的row id
- 透過索引中的row id存取資料就叫做回表，一般是單塊讀取，回表次數太多會影響效能，要改成全表掃描
SQL最佳化一定要注意回表次數，特別是IO次數- 上述的5%判斷方式，原因就是基於回表很好效能，如果資料太多則可能散在不同資料塊，增加回表次數
- 叢集因數可以用來判斷索引回表需要消耗的實體IO次數
- 叢集因數接近塊數時，表示資料順序基本上與索引順序一致，索引掃描時只需掃描少數資料塊
- 叢集因數與資料row數，表示資料與索引順序差異很大，索引掃描時要掃描更多資料塊
- 重建索引不會改善叢集因數，因為原始資料的順序沒變
- 當索引掃描不回表或返回資料量很少時，不管叢集因數多大，對SQL查詢效能幾乎沒有任何影響
- SQL最佳化時往往會建立合適的組合索引消除回表，或盡量減少回表次數
- 如果沒辦法避免回表，可以把資料放進buffer cache，消除實體IO
統計資訊
只有大表才會產生效能問題- 採樣率
- 表小於1GB : 100%採樣
- 1~5GB : 50%
- 大於5GB : 30%
- 大於10GB : 分區後個別進行30%採樣
- 收集直方圖資訊
- 不要對所有欄位收集
- 可以根據選擇性自動收集
- 可以根據欄位是否出現在where條件自動收集
- 可以根據收集過直方圖的欄位繼續收集
- 要定期檢查統計資訊是否過期，比方說每天跑自訂job檢查
- 注意因為欄位選擇性會連乘，下多個條件估計row數時會過小，此時需要擴展統計欄位或提供動態採樣等級
表連接方式
表連接的方式嚴重影響CBO的執行效能- 多表連接的狀況下，一般只能依序兩兩連接
- 巢狀嵌套迴圈nested loops
- 分為驅動表以及被驅動表
- 驅動表返回多少列資料，被驅動表就要掃描幾次
- 驅動表返回過多資料時，被驅動表會被誤認為熱點表，索引會被誤認為熱點索引
- 由於被驅動表會被多次掃瞄，因此連接欄位一定要有索引
- (O) INDEX UNIQUE SCAN
- (O) INDEX RANGE SCAN
- (X) TABLE FULL ACCESS
- (O) INDEX FULL SCAN
- (O) INDEX SKIP SCAN
- (O) INDEX FAST FULL SCAN
- 由於連接欄位一定要有索引，因此基數一定要高
- 簡單來說只有兩表關聯資料量少的時候才能使用巢狀嵌套迴圈
- 外連接(left join)時驅動表只能是主表
- 外連接時如果從表有選擇條件，則會變為內連
- Hash連接hash join
- 兩表等值(=)關聯大量資料時應使用Hash關聯
- 將驅動表的select欄與join欄讀入PGA的work area，然後對join欄生成Hash Table
- 在被驅動表的索引中比對資料
- OLTP一般是高併發小物件居多，SQL返回結果少，執行計畫多用巢狀迴圈，SPA較大
- OLAP都是大規模ETL，SQL返回量大，執行計畫多用Hash為主，PGA設定較大
- 如果反為資料量大，會溢出暫時表到磁碟，效能會急速變差，因此最好只select必要欄位
- 排序合併連接sort merge join
- 兩表不等值(<,≤,>,≥,≠)關聯大量資料時使用
- 先對兩表根據連接欄排序，將小的表做為驅動表
- 在記憶體PGA的work area中比對資料
- 最佳化時盡量利用業務邏輯把不等值比較改成等值比較
- 笛卡爾連接cartesian join
- 兩表關聯沒有連接條件時，會對其中一個表排序，返回兩個表的乘積
- 當row數被錯誤估算成1時且沒有條件約束時，最佳化程式可能會選擇笛卡爾連接，反正row只有1的話資料不會增加
- 最佳化時盡量加上選擇挑件，並仔細檢查估算的row數為何為1
- 純量子查詢scalar subquery
- 介於select與form中間的查詢
- 結構上類似於巢狀連接
- 盡量避免
- 使用內連接的寫法可能會丟失資料，因為主表可能傳Null給子查詢，除非主表的連接欄位是鍵值
- 盡量使用外連接寫法，使用Hash連接
- 半連接semi join
- 兩表連接，但是只回傳其中一個表的內容，通常發生在使用in或是exists時
- 等價改寫成內連接寫法再group by其實會更慢
- 最不容易最佳化
- 反連接anti join
- 兩表連接，但是只回傳主表中與子表沒關聯的資料，一般就是指not in或是not exists
- 可以等價改寫成外連接，使用Hash連接
- filter
- 如果子查詢(in/exists/not in/not exists)沒能展開(unset)，執行計畫中就話產生filter
- filter的算法跟純量子查詢一樣
- 盡量避免
- in跟exist並不一定有哪一方比較快，要看表連接的方式而定
所有SQL子查詢都能改寫成表連接的形式，可以幫助分析如何最佳化
成本計算
SQL的cost是根據統計資訊得到的，因此在SQL需要最佳化的時候cost往往是錯誤的，因此不需要去看- 如果資料庫沒有收集過系統統計資訊，可能會採用非工作量方式計算cost
- CBO估算Row時會使用選擇條件的有效選擇性做數值連乘，通常會低估
- 成本計算在全資料表掃描時基本上只考慮多塊讀取實體IO次數x耗時
- 成本計算在索引範圍掃描時基本上只考慮單塊讀取實體IO次數x耗時
- 最佳化的重點 : 兩種掃瞄方式分別是基於多塊讀取實體IO以及單塊讀取實體IO，哪一邊IO次數比較少就選相應的執行計畫
心得
本書雖然以特定版本的資料庫做說明且參雜作者個人見解，但是確實說明了SQL最佳化的基本原理與重要概念，除了這裡的筆記之外，書中還有其他章節在介紹詳細的實務技巧，可以更加提升開發者的專業技能 :
- 調校最佳化技巧
- 全自動SQL審核