sql語句優化(sql語句的優化方法有哪些)

sql語句優化的幾種方法有:1、統一SQL語句的格式;2、對查詢進行優化,應盡量避免全表掃描;3、SQL語句要簡潔;4、考慮使用“臨時表”暫存中間結果;5、盡量避免大事務操作;6、盡量避免向客戶端返回大數據量。

我們開發項目上線初期,由于業務數據量相對較少,一些SQL的執行效率對程序運行效率的影響不太明顯,而開發和運維人員也無法判斷SQL對程序的運行效率有多大,故很少針對SQL進行專門的優化,而隨著時間的積累,業務數據量的增多,SQL的執行效率對程序的運行效率的影響逐漸增大,此時對SQL的優化就很有必要。

sql語句優化的幾種方法:

1、統一SQL語句的格式

對于以下兩句SQL語句,很多人認為是相同的,但是,數據庫查詢優化器認為是不同的。

　●　select * from dual

　●　select * From dual

雖然只是大小寫不同,查詢分析器就認為是兩句不同的SQL語句,必須進行兩次解析。生成2個執行計劃。所以作為程序員,應該保證相同的查詢語句在任何地方都一致,多一個空格都不行!

2、少用 * ,用具體的字段列表代替“*”,不要返回用不到的任何字段。

3、對查詢進行優化,應盡量避免全表掃描

1)、應考慮在 where 及 order by 涉及的列上建立索引。

2)、應盡量避免在 where 子句中對字段進行 null 值判斷,否則將導致引擎放棄使用索引而進行全表掃描,如:

select id from t where num is null

可以在num上設置默認值0,確保表中num列沒有null值,然后這樣查詢:

select id from t where num=0

3)、應盡量避免在 where 子句中使用!=或<>操作符,否則將導致引擎放棄使用索引而進行全表掃描

4)、應盡量避免在 where 子句中使用 or 來連接條件,否則將導致引擎放棄使用索引而進行全表掃描,如:

select id from t where num=10 or num=20

可以這樣查詢:

select id from t where num=10unionall    select id from t where num=20

5)、慎用in 和 not in,否則會導致全表掃描,如:

select id from t where num in(1,2,3)

對于連續的數值,能用 between 就不要用 in 了:

select id from t where num between 1 and 3

6)、合理使用like模糊查詢

有的時候會需要進行一些模糊查詢比如:

select * from contact where username like ‘%yue%’

關鍵詞 %yue%,由于yue前面用到了“%”,因此該查詢必然走全表掃描,除非必要,否則不要在關鍵詞前加%

7)、應盡量避免在 where 子句中對字段進行表達式操作,這將導致引擎放棄使用索引而進行全表掃描。如:

select id from t where num/2=100

應改為:

select id from t where num=100*2

8)、應盡量避免在where子句中對字段進行函數操作,這將導致引擎放棄使用索引而進行全表掃描。如:

查詢name以abc開頭的id

select id from t where substring(name,1,3)='abc'

應改為:

select id from t where name like 'abc%'

4、用 exists 代替 in

很多時候用 exists 代替 in 是一個好的選擇,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)

5、不要把SQL語句寫得太長,太過冗余、要簡潔;能用一句千萬不要用兩句

一般,將一個select語句的結果作為子集,然后從該子集中再進行查詢,這種一層嵌套語句還是比較常見的,但是根據經驗,超過3層嵌套,查詢優化器就很容易給出錯誤的執行計劃。因為它被繞暈了。像這種類似人工智能的東西,終究比人的分辨力要差些,如果人都看暈了,我可以保證數據庫也會暈的。

另外,執行計劃是可以被重用的,越簡單的SQL語句被重用的可能性越高。而復雜的SQL語句只要有一個字符發生變化就必須重新解析,然后再把這一大堆垃圾塞在內存里?？上攵?數據庫的效率會何等低下。

6、考慮使用“臨時表”暫存中間結果

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

7、在使用索引字段作為條件時,如果該索引是復合索引,那么必須使用到該索引中的第一個字段作為條件時才能保證系統使用該索引, 否則該索引將不會被使用,并且應盡可能的讓字段順序與索引順序相一致。

8、盡量使用數字型字段,若只含數值信息的字段盡量不要設計為字符型,這會降低查詢和連接的性能,并會增加存儲開銷。
這是因為引擎在處理查詢和連接時會逐個比較字符串中每一個字符,而對于數字型而言只需要比較一次就夠了。

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

10、避免頻繁創建和刪除臨時表,以減少系統表資源的消耗。

11、盡量避免使用游標,因為游標的效率較差,如果游標操作的數據超過1萬行,那么就應該考慮改寫。

12、盡量避免大事務操作,提高系統并發能力。

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