SQL書寫規範(通用)

SQL語句是訪問DB中資料的主要途徑和方式，所以SQL的書寫對訪問資料庫時的速度和效能起著至關重要的作用。

本文針對SQL日常書寫中遇到的一些常見問題和注意事項做說明。

注：本文基本適用大多型別的RDBMS關係型資料庫：Oracle，SQLServer, MySQL...

一.如何使用高效的索引

1. 索引的作用？

舉個例子來說明索引的作用：

一本書西遊記一共1250頁，現在你想找到孫悟空大戰黃獅精的章節拿來看，如果你一頁頁的翻書找可能要翻1000多頁紙才能找到對應章節，但是如果你透過目錄來查詢就很快速了，在目錄中很容易的找到 孫悟空大戰黃獅精在第836頁，直接去書中第836頁就能看到 孫悟空大戰黃獅精的章節所有內容了。

資料庫中的表就如同一本書，索引如同目錄。書的目錄存放著章節內容標題和頁碼的對應關係，而索引存放著資料鍵值和表中該行對應的位置，知道的資料在表中位置就可以快速的訪問到資料行，這樣透過索引你就可以快速查出表中想要的資料行了。

2.什麼樣需求建立索引合理？

前面提到了使用索引可以檢索訪問表中資料行，那我們是不是把任何欄位都就建立上索引都好使嘛，當然不是。

舉個例子:

公司有3萬員工，資料庫中員工基本資訊表EMP，主要欄位有工號EMP_NO,姓名EMP_NAME,性別EMP_SEX,部門DEPT,入職時間WORK_DATE等

需求一： 需要透過工號或者姓名查詢員工的詳細資訊

因為工號是唯一的，姓名相同的人也不是很多，對 EMP_NO和 EMP_NAME分別建立一個單獨的索引都會對檢索效能提高很多。

需求二： 查詢公司的男員工有多少

因為性別只是二種， 建立索引就不合理了，為什麼？因為索引中存放的是鍵值和行的物理位置。如果透過索引你需要把索引中所有性別為男的鍵值對應的物理位置都找出來(光檢索索引就消耗大量資源)，再透過這些位置回表到出對應所有行的資料，效率很低了。

以上主要涉及索引selectivity(選擇性)的概念，索引選擇性計算公式：

索引選擇性 = 基數 / 總行數 

基數:鍵值在table中行數

總行數:table總行數

意思公式算出值越小選擇性越強，類似EMP_NO,EMP_NAME選擇性就很強， EMP_SEX選擇性就很弱。

這裡結合本人經驗及多位專家指出設計和選擇索引欄位時，建議選擇的欄位索引選擇性的值小於1/100 以上，而極端情況下可以到1/10

3.什麼樣情形用不到索引？

很多時候有人說明明建立了引為何SQL還是很慢，可以透過執行計劃看到索引沒有被使用。為什麼沒使用？可能的原因以下：

一.索引欄位使用函式

如索引欄位serial_number使用了函式 upper使用不到索引

正確方法：如果迫不得已需要在索引欄位中使用函式，可以建立函式索引替代普通索引

二.不匹配資料型別做 比較

如WROK_ID欄位是VARCHAR2型別，但是SQL寫為 WROK_ID=20190507，字元型別和一個數字去=比較 ，DB的查詢最佳化器會在解析時做一個to_number( WROK_ID) =20190507 的隱式轉換，這樣也用不到索引

正確方法：

直接使用 WROK_ID ='20190507'字元做比較

三 . 模糊查詢like ‘%xxx%’ 或‘%xxx’

因為索引Btree資料結構決定，在檢索值最前面加% like查詢是無法索引索引的

且只能在 檢索值最後面加% like查詢，可使用索引

正確方法：

避免like‘%xxx%’寫法，如有特殊需求查詢like ‘%xxx’可參考反向索引文章：
http://blog.itpub.net/25583515/viewspace-2146401/

四 .組合索引沒有使用先導列

CREATE INDEX SFISM4.GWL_INDEX ON SFISM4.R_STATION_REC_T

(GROUP_NAME, WORK_DATE, LINE_NAME)

組合索引 (GROUP_NAME, WORK_DATE, LINE_NAME)

因為索引Btree資料結構決定，如果在查詢時沒有指定先導列( leading column)第一列 GROUP_NAME，只寫 WORK_DATE, LINE_NAME時用不到高效能 索引的RANGE SCAN，只能使用FULL TAB SCAN或者效能並不高的INDEX SKIP SCAN

正確方法：

寫全先導列查詢或設計新的索引

五 .不等於查詢

<>  != 的不等查詢無法使用索引

正確方法：

重新評估業務邏輯，以其它變通方法解決

六 .NULL,空值

Oracle 無法和NULL 及''，NOT NULL 做比較時使用索引

(注，像MySQL innodb的預設定義null欄位 IS NULL是可以使用索引，SQL server非聚族索引的NULL也是可以被索引的)

正確方法：

Oracle中避免和空比較

七 .不合理的where條件

比如SN_DETAIL表保留著三年內的資料，現在你要查詢2017年1月到2019年1月的資料，因為DB的最佳化器會認為資料量過大，使用索引還不如全表掃描效率

正確方法：

重新評估需求，必要時找DBA協助

二 .SQL,PL/SQL編寫規範習慣

1>開發首先寫好begin..exception..end; 以免遺漏

2>程式碼做好縮排，方便檢視

3>程式碼和table欄位做好註解，方便後續他人閱讀

4>table和sp等物件定義好命名字首字尾規則，C_ R_ I_ SP_ _T _I等

5> table和sp等物件定義好命名長度，儘可能簡短

6>多次使用值如1/24*60可賦予變數重用，否則每次使用會被運算一次帶來開銷

7>避免事務執行過程中失敗業務資料不一致 exception中可以寫上rollback

8>業務邏輯最佳化，避免死鎖，如SP1中第1條更新tab1,第2條更新tab2,SP2中就按照 第1條更新tab1,第2條更新tab2。避免在其它SP中出現 第1條更新tab2,第2條更新tab1的順序

9>SQL中能不使用distinct,group by,order by,having等操作就不要使用 ，避免帶來負載

10>能使用union all操作就不要使用union，union去重操作也會來帶負載

11> SQL能寫短，就不要太長，避免太多表的join，最佳化器可能會選擇錯誤的執行計劃

12>執行完事務記得及時commit,rollback

13>同一個程式 處理 過程中儘可能減少commit頻率

14>SQLServer查詢時儘量使用nolock，避免lock爭用

15>CS架構，Clinet機器名儘可能15位長度之類，方便異常時捕獲分析，Oracle11G及之前版本擷取主機名長度有限，過長會不利於 捕獲 Clinet機器分析異常

16>在執行過久SQL時，檢視執行計劃並調整，也 可找資深開發人員或DBA協助分析原因

目前主要總結以上，後續工作學習中再有遇到相關問題也會不斷補充進來。

文中如有不準確之處也請大家提出。