技術干貨實戰(10) - 值得一看的常見SQL語句性能優化方式

作者: 修羅debug
版權聲明:本文為博主原創文章,遵循 CC 4.0 by-sa 版權協議,轉載請附上原文出處鏈接和本聲明。



作為一名開發者,想必每一天都會跟數據庫打交道,像MySQL、Oracle、SqlServer等都是常見的數據庫產品;而打交道的方式無非就是建庫、建表、寫SQL、寫存儲過程、寫視圖、寫觸發器等等,其中,“寫SQL”應當是最常做的事情了,本文將羅列整理出SQL語句常見的幾十種性能優化方式,建議諸位可以收藏!

   

1.SQL查詢應盡量避免全表掃描,首先應考慮的是在 where order by 涉及的列上建立索引;

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

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

4.應盡量避免在 where 子句中使用 or 來連接條件,否則將有可能導致引擎放棄使用索引而進行全表掃描,可以考慮使用 UNION合并查詢,比如:   

select name from t where id=10 union all select name from t where id=20;


5. 慎用in not in,否則很可能會導致全表掃描,對于連續的數值,能用 between 就不要用 in 了,比如:

select name from t where age 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 id,age from a where id  in(select id  from b).用下面的語句替換:   

select id,age from a where exists(select 1 from b where id=a.id);


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

 

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

 

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

 

14.最好不要使用 * 返回所有: select * from t ,用具體的字段列表代替“*”,不要返回用不到的任何字段:select id,age,name from t

 

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

 

16.使用表的別名(Alias):當在SQL語句中連接多個表時,請使用表的別名并把別名前綴于每個字段列上,這樣一來,就可以減少解析的時間并減少那些由字段列歧義引起的語法錯誤;

 

17.使用“臨時表”暫存中間結果:

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

 

18.一些SQL查詢語句應加上nolock:

讀、寫是會相互阻塞的,為了提高并發性能,對于一些查詢,可以加上nolock,這樣讀的時候可以允許寫,但缺點是可能讀到未提交的臟數據;使用 nolock3條原則:查詢的結果用于“插、刪、改”的不能加nolock ;查詢的表屬于頻繁發生頁分裂的,慎用nolock ;使用臨時表一樣可以保存“數據前影”,起到類似Oracleundo表空間的功能,能采用臨時表提高并發性能的,不要用nolock ;

 

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

 

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

 

21.IN后面值的列表中,將出現最頻繁的值放在最前面,出現得最少的放在最后面,減少判斷的次數;

 

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

 

23,盡量使用“>=”,不要使用“>”;

 

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

 

25.下列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秒)


26.當有一批數據需要批量插入或更新時,應該更多的是用批量插入或批量更新的方式,而不建議一條一條記錄的去更新;

 

27.提高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


28Oracle數據庫的SQL語句用大寫,因為Oracle總是先解析SQL語句,把小寫的字母轉換成大寫的再執行;

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

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

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

32索引創建規則:

1)表的主鍵、外鍵必須有索引;

2)數據量超過300的表應該有索引;

3)經常與其他表進行連接的表,在連接字段上應該建立索引;

4)經常出現在Where子句中的字段,特別是大表的字段,應該建立索引;

5)索引應該建在選擇性能高效的字段上;

6)索引應該建在小字段上,對于大的文本字段甚至超長字段,不要建索引;

7)復合索引的建立需要進行仔細分析,盡量考慮用單字段索引代替;

8)正確選擇復合索引中的主列字段,一般是選擇性較好的字段;

9)復合索引的幾個字段是否經常同時以AND方式出現在Where子句中?單字段查詢是否極少甚至沒有?如果是,則可以建立復合索引;否則考慮單字段索引;

10)如果復合索引中包含的字段經常單獨出現在Where子句中,則分解為多個單字段索引;

11)如果復合索引所包含的字段超過3個,那么仔細考慮其必要性,考慮減少復合的字段;

12)如果既有單字段索引,又有這幾個字段上的復合索引,一般可以刪除復合索引;

13)頻繁進行數據操作的表,不要建立太多的索引;

14)刪除無用的索引,避免對執行計劃造成負面影響;

15)表上建立的每個索引都會增加存儲開銷,索引對于插入、刪除、更新操作也會增加處理上的開銷。另外,過多的復合索引,在有單字段索引的情況下,一般都是沒有存在價值的;相反,還會降低數據增加刪除時的性能,特別是對頻繁更新的表來說,負面影響更大。

16)盡量不要對數據庫中某個含有大量重復的值的字段建立索引。


33.MySQL查詢優化總結:

1)使用慢查詢日志去發現慢查詢,使用執行計劃去判斷查詢是否正常運行,總是去測試你的查詢看看是否他們運行在最佳狀態下;久而久之性能總會變化,避免在整個表上使用count(*),它可能鎖住整張表,使查詢保持一致以便后續相似的查詢可以使用查詢緩存;

2)在適當的情形下使用GROUP BY而不是DISTINCT,在WHERE, GROUP BYORDER BY子句中使用有索引的列,保持索引簡單,不在多個索引中包含同一個列,有時候MySQL會使用錯誤的索引,對于這種情況使用USE INDEX,檢查使用SQL_MODE=STRICT的問題,對于記錄數小于5的索引字段,在UNION的時候使用LIMIT而不是使用OR。

3)為了 避免在更新前SELECT,使用INSERT ON DUPLICATE KEY或者INSERT IGNORE ,不要用UPDATE去實現,不要使用 MAX,使用索引字段和ORDER BY子句,LIMIT M,N實際上可以減緩查詢在某些情況下,有節制地使用,在WHERE子句中使用UNION代替子查詢,在重新啟動的MySQL,記得來溫暖你的數據庫,以確保您的數據在內存和查詢速度快,考慮持久連接,而不是多個連接,以減少開銷,基準查詢,包括使用服務器上的負載,有時一個簡單的查詢可以影響其他查詢,當負載增加您的服務器上,使用SHOW PROCESSLIST查看慢的和有問題的查詢,在開發環境中產生的鏡像數據中 測試的所有可疑的查詢。


34.我們應該為數據庫里的每張表都設置一個ID做為其主鍵,而且最好的是一個INT型的(推薦使用UNSIGNED),并設置上自動增加的AUTO_INCREMENT標志。


35.MySQL查詢可以啟用高速查詢緩存,這是提高數據庫性能的有效MySQL優化方法之一。當同一個查詢被執行多次時,從緩存中提取數據和直接從數據庫中返回數據快很多。


36.EXPLAIN SELECT 查詢用來跟蹤查看效果:

使用 EXPLAIN 關鍵字可以讓你知道MySQL是如何處理你的SQL語句的,這可以幫你分析你的查詢語句或是表結構的性能瓶頸。EXPLAIN 的查詢結果還會告訴你你的索引主鍵被如何利用的,你的數據表是如何被搜索和排序的……等等,等等。


37.當只要一行數據時使用 LIMIT 1

當你查詢表的有些時候,你已經知道結果只會有一條結果,但因為你可能需要去fetch游標,或是你也許會去檢查返回的記錄數。在這種情況下,加上 LIMIT 1 可以增加性能。這樣一樣,MySQL數據庫引擎會在找到一條數據后停止搜索,而不是繼續往后查少下一條符合記錄的數據。


38.選擇表合適存儲引擎:

1Myisam: 應用時以讀和插入操作為主,只有少量的更新和刪除,并且對事務的完整性,并發性要求不是很高的。

2Innodb: 事務處理,以及并發條件下要求數據的一致性。除了插入和查詢外,包括很多的更新和刪除。(Innodb有效地降低刪除和更新導致的鎖定)。對于支持事務的InnoDB類型的表來說,影響速度的主要原因是AUTOCOMMIT默認設置是打開的,而且程序沒有顯式調用BEGIN 開始事務,導致每插入一條都自動提交,嚴重影響了速度??梢栽趫绦?span lang="EN-US">sql前調用begin,多條sql形成一個事物(即使autocommit打開也可以),將大大提高性能。


39.優化表的數據類型,選擇合適的數據類型:

原則:更小通常更好,簡單就好,所有字段都得有默認值,盡量避免null。

例如:數據庫表設計時候更小的占磁盤空間盡可能使用更小的整數類型.(mediumint就比int更合適)


比如時間字段:datetimetimestamp, datetime占用8個字節,而timestamp占用4個字節,只用了一半,而timestamp表示的范圍是19702037適合做更新時間

MySQL可以很好的支持大數據量的存取,但是一般說來,數據庫中的表越小,在它上面執行的查詢也就會越快。

因此,在創建表的時候,為了獲得更好的性能,我們可以將表中字段的寬度設得盡可能??;


40.任何對列的操作都將導致表掃描,它包括數據庫函數、計算表達式等等,查詢時要盡可能將操作移至等號右邊


說在最后的話:

最近正值跳槽面試季,debug特意錄制來一套課程:Java工程師核心技術-典型案例與面試實戰系列二 ,課程主要內容在:1.以企業實際業務場景、功能需求為驅動,采用各種主流的技術進行實現;2.理論為輔、實戰為主,以實現業務功能需求為最終目標,追求性能、從一而終;3.深入底層,結合應用場景剖析技術的實現原理;

課程大綱如下圖所示:



   

詳細課程目錄以及課時內容可以在PC端打開鏈接觀看:   https://www.fightjava.com/web/index/course/detail/16


目前正值優惠階段,原價99元,現在只需要39.9 即可學習整個課程,感興趣的小伙伴可以加debug的微信:debug0868  咨詢購買與學習!

我是debug,一個相信技術改變生活、技術成就夢想 的攻城獅;如果本文對你有幫助,請關注公眾號,并動動手指收藏、點贊、以及轉發哦?。?!