sql語句性能優化介紹

1， 對查詢進行優化，應盡量避免全表掃描，首先應考慮在 where 及 order by 涉及的列上建立索引

2，應盡量避免在 where 子句中對字段進行 null 值判斷，創建表時NULL是默認值，但大多數時候應該使用NOT NULL，或者使用一個特殊的值，如0，-1作為默  認值。

3，應盡量避免在 where 子句中使用!=或<>操作符， MySQL只有對以下操作符才使用索引：<，<=，=，>，>=，BETWEEN，IN，以及某些時候的LIKE。

4，應盡量避免在 where 子句中使用 or 來連接條件， 否則將導致引擎放棄使用索引而進行全表掃描， 可以 使用UNION合并查詢： select id from t where num=10 union all select id from t where num=20

5，in 和 not in 也要慎用，否則會導致全表掃描，對于連續的數值，能用 between 就不要用 in 了：Select id from t where num between 1 and 3

6，下面的查詢也將導致全表掃描：select id from t where name like '%abc%' 或者select id from t where name like '%abc'若要提高效率，可以考慮全文檢索。而select id from t where name like 'abc%' 才用到索引

7， 如果在 where 子句中使用參數，也會導致全表掃描。

8，應盡量避免在 where 子句中對字段進行表達式操作，應盡量避免在where子句中對字段進行函數操作

9,很多時候用 exists 代替 in 是一個好的選擇：   select num from a where num in(select num from b).用下面的語句替換：   select num from a where exists(select 1 from b where num=a.num)

10,索引固然可以提高相應的 select 的效率，但同時也降低了 insert 及 update 的效率，因為 insert 或 update 時有可能會重建索引，所以怎樣建索引需要慎重考慮，視具體情況而定。一個表的索引數最好不要超過6個，若太多則應考慮一些不常使用到的列上建的索引是否有必要。

11,應盡可能的避免更新 clustered 索引數據列，  因為 clustered 索引數據列的順序就是表記錄的物理存儲順序，一旦該列值改變將導致整個表記錄的順序的調整，會耗費相當大的資源。若應用系統需要頻繁更新 clustered 索引數據列，那么需要考慮是否應將該索引建為 clustered 索引。

12，盡量使用數字型字段，若只含數值信息的字段盡量不要設計為字符型，這會降低查詢和連接的性能，并會增加存儲開銷。

13，盡可能的使用 varchar/nvarchar 代替 char/nchar ，   因為首先變長字段存儲空間小，可以節省存儲空間，其次對于查詢來說，在一個相對較小的字段內搜索效率顯然要高些。

14，最好不要使用"*"返回所有： select * from t ，用具體的字段列表代替“*”，不要返回用不到的任何字段。

15，盡量避免向客戶端返回大數據量，若數據量過大，應該考慮相應需求是否合理。

16，使用表的別名(Alias)：

簡化SQL語句的重要方法就是采用臨時表暫存中間結果，但是，臨時表的好處遠遠不止這些，將臨時結果暫存在臨時表，后面的查詢就在tempdb中了，這可以避免程序中多次掃描主表，也大大減少了程序執行中“共享鎖”阻塞“更新鎖”，減少了阻塞，提高了并發性能。

19，常見的簡化規則如下：不要有超過5個以上的表連接（JOIN），考慮使用臨時表或表變量存放中間結果。少用子查詢，視圖嵌套不要過深,一般視圖嵌套不要超過2個為宜。

20，將需要查詢的結果預先計算好放在表中，查詢的時候再Select。這在SQL7.0以前是最重要的手段。例如醫院的住院費計算。

21，用OR的字句可以分解成多個查詢，并且通過UNION 連接多個查詢。他們的速度只同是否使用索引有關,如果查詢需要用到聯合索引，用UNION all執行的效率更高.多個OR的字句沒有用到索引，改寫成UNION的形式再試圖與索引匹配。一個關鍵的問題是否用到索引。

22，在IN后面值的列表中，將出現最頻繁的值放在最前面，出現得最少的放在最后面，減少判斷的次數。

23，盡量將數據的處理工作放在服務器上，減少網絡的開銷，如使用存儲過程。存儲過程是編譯好、優化過、并且被組織到一個執行規劃里、且存儲在數據庫中的SQL語句，是控制流語言的集合，速度當然快。反復執行的動態SQL,可以使用臨時存儲過程，該過程（臨時表）被放在Tempdb中。

24，當服務器的內存夠多時，配制線程數量 = 最大連接數+5，這樣能發揮最大的效率；否則使用 配制線程數量<最大連接數啟用SQL SERVER的線程池來解決,如果還是數量 = 最大連接數+5，嚴重的損害服務器的性能。

25，查詢的關聯同寫的順序
       select a.personMemberID, * from chineseresume a,personmember b where personMemberID = b.referenceid and a.personMemberID = 'JCNPRH39681' （A = B ,B = '號碼'） 
　　 
　　select a.personMemberID, * from chineseresume a,personmember b where a.personMemberID = b.referenceid and a.personMemberID = 'JCNPRH39681' and b.referenceid = 'JCNPRH39681' （A = B ,B = '號碼'， A = '號碼'） 
　　 
　　select a.personMemberID, * from chineseresume a,personmember b where b.referenceid = 'JCNPRH39681' and a.personMemberID = 'JCNPRH39681' （B = '號碼'， A = '號碼'）

26，盡量使用exists代替select count(1)來判斷是否存在記錄，count函數只有在統計表中所有行數時使用，而且count(1)比count(*)更有效率。

27，盡量使用“>=”，不要使用“>”。

28，索引的使用規范：索引的創建要與應用結合考慮，建議大的OLTP表不要超過6個索引；盡可能的使用索引字段作為查詢條件，尤其是聚簇索引，必要時可以通過index index_name來強制指定索引；避免對大表查詢時進行table scan，必要時考慮新建索引；在使用索引字段作為條件時，如果該索引是聯合索引，那么必須使用到該索引中的第一個字段作為條件時才能保證系統使用該索引，否則該索引將不會被使用；要注意索引的維護，周期性重建索引，重新編譯存儲過程。　　

29，下列SQL條件語句中的列都建有恰當的索引，但執行速度卻非常慢：
SELECT * FROM record WHERE substrINg(card_no,1,4)='5378' (13秒)
SELECT * FROM record WHERE amount/30< 1000 （11秒）
SELECT * FROM record WHERE convert(char(10),date,112)='19991201' （10秒）
分析：
WHERE子句中對列的任何操作結果都是在SQL運行時逐列計算得到的，因此它不得不進行表搜索，而沒有使用該列上面的索引；如果這些結果在查詢編譯時就能得到，那么就可以被SQL優化器優化，使用索引，避免表搜索，因此將SQL重寫成下面這樣：
SELECT * FROM record WHERE card_no like '5378%' （< 1秒）
SELECT * FROM record WHERE amount< 1000*30 （< 1秒）
SELECT * FROM record WHERE date= '1999/12/01' （< 1秒）

30，當有一批處理的插入或更新時，用批量插入或批量更新，絕不會一條條記錄的去更新!

31，在所有的存儲過程中，能夠用SQL語句的，我絕不會用循環去實現!
--      (例如：列出上個月的每一天，我會用connect by去遞歸查詢一下，絕不會去用循環從上個月第一天到最后一天)

32，選擇最有效率的表名順序(只在基于規則的優化器中有效)：
ORACLE 的解析器按照從右到左的順序處理FROM子句中的表名，FROM子句中寫在最后的表(基礎表 driving table)將被最先處理，在FROM子句中包含多個表的情況下,你必須選擇記錄條數最少的表作為基礎表。如果有3個以上的表連接查詢, 那就需要選擇交叉表(intersection table)作為基礎表, 交叉表是指那個被其他表所引用的表.

33，提高GROUP BY語句的效率, 可以通過將不需要的記錄在GROUP BY 之前過濾掉.下面兩個查詢返回相同結果，但第二個明顯就快了許多.
低效:
SELECT JOB , AVG(SAL)
FROM EMP
GROUP BY JOB
HAVING JOB =’PRESIDENT’
OR JOB =’MANAGER’
高效:
SELECT JOB , AVG(SAL)
FROM EMP
WHERE JOB =’PRESIDENT’
OR JOB =’MANAGER’
GROUP BY JOB

34，sql語句用大寫，因為oracle 總是先解析sql語句，把小寫的字母轉換成大寫的再執行。

35，別名的使用，別名是大型數據庫的應用技巧，就是表名、列名在查詢中以一個字母為別名，查詢速度要比建連接表快1.5倍。

36，避免死鎖，在你的存儲過程和觸發器中訪問同一個表時總是以相同的順序;事務應經可能地縮短，在一個事務中應盡可能減少涉及到的數據量;永遠不要在事務中等待用戶輸入。

37，避免使用臨時表，除非卻有需要，否則應盡量避免使用臨時表，相反，可以使用表變量代替;大多數時候(99%)，表變量駐扎在內存中，因此速度比臨時表更快，臨時表駐扎在TempDb數據庫中，因此臨時表上的操作需要跨數據庫通信，速度自然慢。

38，最好不要使用觸發器，觸發一個觸發器，執行一個觸發器事件本身就是一個耗費資源的過程;如果能夠使用約束實現的，盡量不要使用觸發器;不要為不同的觸發事件(Insert，Update和Delete)使用相同的觸發器;不要在觸發器中使用事務型代碼。

39，索引創建規則：

經常與其他表進行連接的表，在連接字段上應該建立索引；

40，mysql查詢優化總結：使用慢查詢日志去發現慢查詢，使用執行計劃去判斷查詢是否正常運行，總是去測試你的查詢看看是否他們運行在最佳狀態下

生活不易，碼農辛苦
如果您覺得本網站對您的學習有所幫助,可以手機掃描二維碼進行捐贈