「分散式技術專題」SQL最佳化的前置條件和最佳化技巧分享

一、 SQL最佳化的前置條件

1、硬體基礎
資料庫是作業系統之上的一種資料管理軟體，其 SQL最終的執行還是需要在硬體層面執行，所以硬體條件如CPU核數、記憶體大小、磁碟轉速、網路頻寬等是保障資料庫的SQL指令碼能快速執行的基礎。

2、業務型別判斷
• 瞭解業務是讀多寫少還是讀寫均衡，判斷是偏OLAP還是OLTP業務
• 根據表的使用方式判斷其資料分佈和形態

• 事實表   含有大量的事實資料，包含描述業務的特定事件的資料，如商品交易情況表。 - 維度表   使用者來分析資料，會在某一維度彙總事實資料表資料。 - 流水錶   存放的是一個使用者的變更記錄，用於記錄業務軌跡。比如在一張流水錶中，一天的資料中，會存放一個使用者的每條修改記錄。 - 寬表   彙總多個維度或指標，例如計算使用者畫像指標的結果表。 - 全量表   只記錄所有資料的最新狀態。   - 增量表   按天分割槽，每一天會存放當天所產生的增量資料。   - 快照表 按天分割槽，每一天的資料都是截止到那一天的全量資料。- 切片表
 根據基礎表，往往只反映某一個維度的相應資料。   其表結構與基礎表結構相同，但資料往往只有某一維度，或者某一個事實條件的資料。   - 拉鍊表
資料倉儲中經常用到，所謂拉鍊，就是記錄歷史。   使用區間段記錄資料，表中有一個資料的起始時間和結束時間，記錄資料的有效區間。   相比每天分割槽，既能檢視歷史，又很節省空間。 
- 字典表   在系統中充當基礎引數的角色。比如機構程式碼表、商品種類表。   - 碼錶   類似於資料字典。   - 臨時表
 在業務計算中，保留中間的計算結果，使用完成後會刪除或清空。
在 SQL最佳化中，瞭解業務型別和資料的分佈情況是一個重要的前提，如果只基於SQL規則去最佳化無異於盲人摸象。

3、儲存結構設計
資料的儲存結構和資料查詢的效率是密切相關的。
分割槽分桶
在傳統的關係型資料庫中，分庫分表是資料體量較大時一種常用的解決方式。
在分散式資料庫中，叢集節點可以線性擴充套件，分割槽分桶即可實現資料按某一維度分散儲存。
行存 vs 列存
對於 OLAP的統計分析型的業務，列存是優先的儲存格式；對於OLTP的事務及高併發業務，行存則佔據優勢。
壓縮
無論列存還是行存，如果資料儲存時能夠壓縮，則可節省大量的儲存空間，進行 SQL查詢時，也可大大減少IO，加快查詢速度。

4、索引設計
本質上來說，索引是一種空間換時間的策略，透過索引減少全表掃描，能大大降低 IO，提高查詢速度。但同時也會犧牲一定的更新速度。
索引分類
邏輯上：
    • 單列索引
    • 多列索引
    • 唯一索引
    • 非唯一索引
    • 函式索引
物理上：
    • 分割槽索引
    • 非分割槽索引
    • B樹
    • 點陣圖索引
    • 雜湊索引
索引的代價
    • 空間上的代價
每建立一個索引都要為它建立一個資料頁
    • 時間上的代價
增刪改查都要對索引同步操作
索引設計原則
    • 為查詢頻率高的欄位建立索引
    • 為經常需要排序、分組和聯合操作的欄位建立索引
    • 選擇區分度高的列作為索引
例如電話、身份證號碼基本唯一，適合做索引，但性別不適合做索引
    • 使用資料型別佔用空間小的欄位做索引
一般來說，數字型別、日期型別、 char型別的欄位適合做索引；字串和BLOG等型別的欄位不適合做索引。

二、 SQL最佳化技巧

孫子兵法有云， “不戰而屈人之兵，善之善者也。故上兵伐謀，其次伐交，其次伐兵，其下攻城。”
當業務模型和資料模型等都已設計完成，資料庫即將入數或已經開始對外提供服務時， SQL最佳化能力就可以大顯身手，為資料庫高效執行保駕護航了。

最佳化法則 :
    • 透過索引減少磁碟IO
    • 透過謂詞下推減少網路傳輸
    • 去除非必要排序操作減少CPU和記憶體開銷
    • 增加資源加速查詢

資料儲存型別
    • 選擇最精簡的資料型別和儲存格式，例如：
長度固定的字串欄位使用 char型別；日期型別優先使用timestamp
    • 儘量把欄位設定為 not null

索引最佳化
    • 利用索引，可以避免大表全表掃描；但資料量不大時，全表掃描也可能更快
    • 避免在索引列上使用函式
    • 避免出現索引列隱式轉換（資料型別轉換）
    • 支援索引的 where 操作：=、>、>=、< 、<= 、between、in
    • 不支援索引的 where 操作：<> 、not in、表示式或函式計算、is null
    • like 語句前 % 不支援索引

聯合主鍵最佳化
    • 多列索引選擇合適的索引列順序(最左原則)
    • 聯合主鍵中如果不是從最左列查詢，則無法使用索引。
    • 範圍查詢時，也只能用最左列進行範圍查詢。
    • order by後欄位如果和聯合主鍵的順序不同，則無法使用索引
    • asc desc 混用，無法使用索引

select最佳化
    • 只查詢需要用到的列，而不是全欄位
    • 如果表多個列且沒有主鍵，則 count(1) 執行效率優於 count(*)
    • 如果有主鍵，則 select count(主鍵)的執行效率是最優的

order by 最佳化
可能會發生排序操作的 SQL語法：
    • Order by
    • Group by
    • Distinct
    • Exists子查詢
    • Not Exists子查詢
    • In子查詢
    • Not In子查詢
    • Union（並集），Union All不會發生排序
    • Minus（差集）
    • Intersect（交集）
    • Create Index
    • Merge Join

最佳化思路：
    • 減少使用不必要的ORDER BY排序
    • 排序時，返回的列要儘量少
    • 因為索引天然有序，所以儘量利用索引排序
    • 避免使用耗費資源的操作，包括DISTINCT,UNION,MINUS,INTERSECT,ORDER BY
    • 使用group by代替distinct
    • 對於範圍查詢，如果欄位恰好是排序的，則查詢和IO都會更快join 最佳化
    • 表聚合的本質是做笛卡爾積
    • 有小表時使用廣播
    • 驅動表選擇小表，驅動表量越大越會引起效能問題(大小表前後問題)
    • 使用 join 代替子查詢。子查詢需要在記憶體中建立臨時表。
    • 使用 join 代替子連線。子連線會先查外表再匹配內表。
    • 需要join的欄位最好建立索引，且是相同的資料型別
    • 儘量使用union all而不是union(有去重操作)
    • join 儘量不要超過 2 張表
    • 用UNION替換OR ，用UNION-ALL 替換UNION子查詢和子連線
    • 出現在FROM關鍵字後的子句是子查詢語句
SELECT * FROM STUDENT, (SELECT * FROM SCORE) as sc;
    • 出現在WHERE/ON等約束條件中或投影中的子句是子連線語句
SELECT * FROM STUDENT WHERE EXISTS (
 SELECT A FROM SCORE WHERE SCORE.sno = STUDENT.sno);
 其中STUDENT叫外表，SCORE稱內表。

where 條件最佳化
    • 謂詞下推，提前執行where謂詞邏輯
    • 用 in 替換 or
    • 使用 between或exists 代替 in
    • 用NOT EXISTS替代NOT IN
    • 用Where子句替換HAVING子句
    • 減少比較操作

on 與 where 的執行順序以及效率
    • on :與取得結果集同步進行資料刷選及過濾。
    • where : 獲得結果集之後，才進行資料刷選及過濾。

執行順序： on在上游，where在中游，having在下游。
    • left join 中on條件對左表無效，對右表有效；
    • right join中on條件對右表無效，對左表有效；
    • inner join時，結果與放在where條件中等價，但是on條件會先執行

以上為SQL最佳化的前置條件和最佳化技巧分享， 「分散式技術專題」是國產資料庫 hubble 團隊精心整編，專題會持續更新，歡迎大家保持關注。