SQL書寫規范有哪些

本篇文章給大家分享的是有關SQL書寫規范有哪些，小編覺得挺實用的，因此分享給大家學習，希望大家閱讀完這篇文章后可以有所收獲，話不多說，跟著小編一起來看看吧。

一.如何使用高效的索引

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協助分析原因

以上就是SQL書寫規范有哪些，小編相信有部分知識點可能是我們日常工作會見到或用到的。希望你能通過這篇文章學到更多知識。更多詳情敬請關注億速云行業資訊頻道。