SQL 語句優(yōu)化常用方法

1、具體要注意的:1.應(yīng)盡量避免在 where 子句中對(duì)字段進(jìn)行 null 值判斷,否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描,如:select id from t where num is null可以在num上設(shè)置默認(rèn)值0,確保表中num列沒有null值,然后這樣查詢: select id from t where num=02.應(yīng)盡量避免在 where 子句中使用!=或操作符,否則將引擎放棄使用索引而進(jìn)行全表掃描。優(yōu)化器將無法通過索引來確定將要命中的行數(shù),因此需要搜索該表的所有行。3.應(yīng)盡量避免在 where 子句中使用 or 來連接條件,否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描,如:sele

2、ct id from t where num=10 or num=20可以這樣查詢:select id from t where num=10union allselect id from t where num=204.in 和 not in 也要慎用,因?yàn)镮N會(huì)使系統(tǒng)無法使用索引,而只能直接搜索表中的數(shù)據(jù)。如:select id from t where num in(1,2,3對(duì)于連續(xù)的數(shù)值,能用 between 就不要用 in 了:select id from t where num between 1 and 35.盡量避免在索引過的字符數(shù)據(jù)中,使用非打頭字母搜索。這也使得引擎無法利

3、用索引。見如下例子:SELECT * FROM T1 WHERE NAME LIKE %L%SELECT * FROM T1 WHERE SUBSTING(NAME,2,1=LSELECT * FROM T1 WHERE NAME LIKE L%即使NAME字段建有索引,前兩個(gè)查詢依然無法利用索引完成加快操作,引擎不得不對(duì)全表所有數(shù)據(jù)逐條操作來完成任務(wù)。而第三個(gè)查詢能夠使用索引來加快操作。6. 必要時(shí)強(qiáng)制查詢優(yōu)化器使用某個(gè)索引,如在 where 子句中使用參數(shù),也會(huì)導(dǎo)致全表掃描。因?yàn)镾QL只有在運(yùn)行時(shí)才會(huì)解析局部變量,但優(yōu)化程序不能將訪問計(jì)劃的選擇推遲到運(yùn)行時(shí);它必須在編譯時(shí)進(jìn)行選擇。然而,如

4、果在編譯時(shí)建立訪問計(jì)劃,變量的值還是未知的,因而無法作為索引選擇的輸入項(xiàng)。如下面語句將進(jìn)行全表掃描:select id from t where num=num可以改為強(qiáng)制查詢使用索引:select id from t with(index(索引名 where num=num7.應(yīng)盡量避免在 where 子句中對(duì)字段進(jìn)行表達(dá)式操作,這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。如:SELECT * FROM T1 WHERE F1/2=100應(yīng)改為:SELECT * FROM T1 WHERE F1=100*2SELECT * FROM RECORD WHERE SUBSTRING(CARD_NO,

5、1,4=5378應(yīng)改為:SELECT * FROM RECORD WHERE CARD_NO LIKE 5378%SELECT member_number, first_name, last_name FROM membersWHERE DATEDIFF(yy,datofbirth,GETDATE( 21應(yīng)改為:SELECT member_number, first_name, last_name FROM membersWHERE dateofbirth =2005-11-30 andcreatedate0SELECT SUM(T1.C1 FROM T1WHERE EXISTS(SELECT

6、 * FROM T2 WHERE T2.C2=T1.C2兩者產(chǎn)生相同的結(jié)果,但是后者的效率顯然要高于前者。因?yàn)楹笳卟粫?huì)產(chǎn)生大量鎖定的表掃描或是索引掃描。如果你想校驗(yàn)表里是否存在某條紀(jì)錄,不要用count(*那樣效率很低,而且浪費(fèi)服務(wù)器資源?？梢杂肊XISTS代替。如:IF (SELECT COUNT(* FROM table_name WHERE column_name = xxx可以寫成:IF EXISTS (SELECT * FROM table_name WHERE column_name = xxx經(jīng)常需要寫一個(gè)T_SQL語句比較一個(gè)父結(jié)果集和子結(jié)果集,從而找到是否存在在父結(jié)果集中有而

7、在子結(jié)果集中沒有的記錄,如:SELECT a.hdr_key FROM hdr_tbl a- tbl a 表示tbl用別名a代替WHERE NOT EXISTS (SELECT * FROM dtl_tbl b WHERE a.hdr_key = b.hdr_key SELECT a.hdr_key FROM hdr_tbl aLEFT JOIN dtl_tbl b ON a.hdr_key = b.hdr_key WHERE b.hdr_key IS NULLSELECT hdr_key FROM hdr_tblWHERE hdr_key NOT IN (SELECT hdr_key FRO

8、M dtl_tbl三種寫法都可以得到同樣正確的結(jié)果,但是效率依次降低。12.盡量使用表變量來代替臨時(shí)表。如果表變量包含大量數(shù)據(jù),請(qǐng)注意索引非常有限(只有主鍵索引。13.避免頻繁創(chuàng)建和刪除臨時(shí)表,以減少系統(tǒng)表資源的消耗。14.臨時(shí)表并不是不可使用,適當(dāng)?shù)厥褂盟鼈兛梢允鼓承├谈行?例如,當(dāng)需要重復(fù)引用大型表或常用表中的某個(gè)數(shù)據(jù)集時(shí)。但是,對(duì)于一次性事件,最好使用導(dǎo)出表。15.在新建臨時(shí)表時(shí),如果一次性插入數(shù)據(jù)量很大,那么可以使用 select into 代替 create table,避免造成大量 log ,以提高速度;如果數(shù)據(jù)量不大,為了緩和系統(tǒng)表的資源,應(yīng)先create table,然后i

9、nsert。16.如果使用到了臨時(shí)表,在存儲(chǔ)過程的最后務(wù)必將所有的臨時(shí)表顯式刪除,先truncate table ,然后 drop table ,這樣可以避免系統(tǒng)表的較長(zhǎng)時(shí)間鎖定。17.在所有的存儲(chǔ)過程和觸發(fā)器的開始處設(shè)置 SET NOCOUNT ON ,在結(jié)束時(shí)設(shè)置SET NOCOUNT OFF 。無需在執(zhí)行存儲(chǔ)過程和觸發(fā)器的每個(gè)語句后向客戶端發(fā)送DONE_IN_PROC 消息。18.盡量避免大事務(wù)操作,提高系統(tǒng)并發(fā)能力。19.盡量避免向客戶端返回大數(shù)據(jù)量,若數(shù)據(jù)量過大,應(yīng)該考慮相應(yīng)需求是否合理。20. 避免使用不兼容的數(shù)據(jù)類型。例如float和int、char和varchar、binary

10、 和varbinary是不兼容的。數(shù)據(jù)類型的不兼容可能使優(yōu)化器無法執(zhí)行一些本來可以進(jìn)行的優(yōu)化操作。例如:SELECT name FROM employee WHERE salary 60000在這條語句中,如salary字段是money型的,則優(yōu)化器很難對(duì)其進(jìn)行優(yōu)化,因?yàn)?0000是個(gè)整型數(shù)。我們應(yīng)當(dāng)在編程時(shí)將整型轉(zhuǎn)化成為錢幣型,而不要等到運(yùn)行時(shí)轉(zhuǎn)化。21.充分利用連接條件,在某種情況下,兩個(gè)表之間可能不只一個(gè)的連接條件,這時(shí)在 WHERE 子句中將連接條件完整的寫上,有可能大大提高查詢速度。例:SELECT SUM(A.AMOUNT FROM ACCOUNT A,CARD B WHERE A

11、.CARD_NO = B.CARD_NO SELECT SUM(A.AMOUNT FROM ACCOUNT A,CARD B WHERE A.CARD_NO = B.CARD_NO AND A.ACCOUNT_NO=B.ACCOUNT_NO第二句將比第一句執(zhí)行快得多。22、使用視圖加速查詢把表的一個(gè)子集進(jìn)行排序并創(chuàng)建視圖,有時(shí)能加速查詢。它有助于避免多重排序操作,而且在其他方面還能簡(jiǎn)化優(yōu)化器的工作。例如:SELECT ,rcvbles.balance,other columnsFROM cust,rcvblesWHERE cust.customer_id = rcvlbes.

12、customer_idAND rcvblls.balance0AND cust.postcode“98000”O(jiān)RDER BY 如果這個(gè)查詢要被執(zhí)行多次而不止一次,可以把所有未付款的客戶找出來放在一個(gè)視圖中,并按客戶的名字進(jìn)行排序:CREATE VIEW DBO.V_CUST_RCVLBESASSELECT ,rcvbles.balance,other columnsFROM cust,rcvblesWHERE cust.customer_id = rcvlbes.customer_idAND rcvblls.balance0ORDER BY cust.na

13、me然后以下面的方式在視圖中查詢:SELECT * FROM V_CUST_RCVLBESWHERE postcode“98000”視圖中的行要比主表中的行少,而且物理順序就是所要求的順序,減少了磁盤I/O,所以查詢工作量可以得到大幅減少。23、能用DISTINCT的就不用GROUP BYSELECT OrderID FROM Details WHERE UnitPrice 10 GROUP BY OrderID 可改為:SELECT DISTINCT OrderID FROM Details WHERE UnitPrice 1024.能用UNION ALL就不要用UNIONUNION ALL

14、不執(zhí)行SELECT DISTINCT函數(shù),這樣就會(huì)減少很多不必要的資源35.盡量不要用SELECT INTO語句。SELECT INOT 語句會(huì)導(dǎo)致表鎖定,阻止其他用戶訪問該表。上面我們提到的是一些基本的提高查詢速度的注意事項(xiàng),但是在更多的情況下,往往需要反復(fù)試驗(yàn)比較不同的語句以得到最佳方案。最好的方法當(dāng)然是測(cè)試,看實(shí)現(xiàn)相同功能的SQL語句哪個(gè)執(zhí)行時(shí)間最少,但是數(shù)據(jù)庫(kù)中如果數(shù)據(jù)量很少,是比較不出來的,這時(shí)可以用查看執(zhí)行計(jì)劃,即:把實(shí)現(xiàn)相同功能的多條SQL語句考到查詢分析器,按CTRL+L看查所利用的索引,表掃描次數(shù)(這兩個(gè)對(duì)性能影響最大,總體上看詢成本百分比即可。今天在itput上看了一篇文章

15、,是討論一個(gè)語句的優(yōu)化:原貼地址:一,發(fā)現(xiàn)問題優(yōu)化的語句:請(qǐng)問以下語句如何優(yōu)化:CREATE TABLE aa_001( ip VARCHAR2(28,name VARCHAR2(10,password VARCHAR2(30 select * from aa_001 where ip in (1,2,3 order by name desc;-目前表中記錄有一千多萬條左右,而且in中的值個(gè)數(shù)是不確定的。以上就是優(yōu)化的需要優(yōu)化的語句和情況。不少人在后面跟帖:有的說沒辦法優(yōu)化,有的說將IN該為EXISTS,有的說在ip上建立索引復(fù)合索引(ip,name等等。二,提出問題那這樣的情況,能優(yōu)化嗎,如

16、何優(yōu)化?今天就來討論這個(gè)問題。三,分析問題 1,數(shù)據(jù)量1千萬多條。2,in中的值個(gè)數(shù)是不確定3.1 分析數(shù)據(jù)分布這里作者沒有提到ip列的數(shù)據(jù)的分布情況,目前ip列的數(shù)據(jù)分布可能有以下幾種:1,ip列(數(shù)據(jù)唯一,或者數(shù)據(jù)重復(fù)的概率很小2,ip列 (數(shù)據(jù)不均勻,可能有些數(shù)據(jù)重復(fù)多,有些重復(fù)少3,ip列(數(shù)據(jù)分布比較均勻,數(shù)據(jù)大量重復(fù),主要就是一些同樣的數(shù)據(jù)(可能只有上萬級(jí)別不同的ip數(shù)據(jù)等解決問題:1,對(duì)于第一種數(shù)據(jù)分布情況,只要在ip列建立一個(gè)索引即可。這時(shí)不管表有多少行, in個(gè)數(shù)是不確定的情況下,都很快。2,對(duì)應(yīng)第二中數(shù)據(jù)分布情況,在ip列建立索引,效果不好。因?yàn)閿?shù)據(jù)分布不均勻,可能有些快,

17、有些慢3,對(duì)應(yīng)第三種數(shù)據(jù)分布情況,在ip列建立索引,速度肯定慢。注意:這里的 order by name desc 是在取出數(shù)據(jù)后再排序的。而不是取數(shù)據(jù)前排序?qū)τ?,3兩個(gè)情況,因?yàn)槎际强赡苄枰〕龃罅康臄?shù)據(jù),優(yōu)化器就采用表掃描(table scan,而不是索引查找(index seek ,速度很慢,因?yàn)檫@時(shí)表掃描效率要優(yōu)于索引查找,特別是高并發(fā)情況下,效率很低。那對(duì)應(yīng)2,3中情況,如何處理。是將in改成exists。其實(shí)在sql server 2005和oracle里的優(yōu)化器在in后面數(shù)據(jù)少時(shí),效率是一樣的。這時(shí)采用一般的索引效率很低。這時(shí)如果在ip列上建立聚集索引,效率會(huì)比較高。我們?cè)赟Q

18、L server 2005中做個(gè)測(cè)試。表:dbo.中有約200萬條數(shù)據(jù)。包含列Userid, id, Ruleid 等列。按照上面的情況查詢一下類似語句:select * from dbo. whereuserid in(402881410ca47925010cb329c7670ffb,402881ba0d5dc94e010d5dced05a0008 ,4028814111a735e90111a77fa8e30384 order by Ruleid desc我們先看userid的數(shù)據(jù)分布情況,執(zhí)行下面語句:select userid,count(* from dbo. group by userid order by 2這時(shí)我們看看數(shù)據(jù)分布:總共有379條數(shù)據(jù),數(shù)據(jù)兩從1到15萬都有,數(shù)據(jù)分布傾斜嚴(yán)重。下圖是其中一部分。 這時(shí)如果在 ip 上建立非聚集索引，效率很低，而且就是強(qiáng)行索引掃描 強(qiáng)行索引掃描，效率也 強(qiáng)行索引掃描 很低，會(huì)發(fā)現(xiàn) IO 次數(shù)比表掃描還高。這時(shí)只能在 ip 上建立聚集索引。這時(shí)看看 結(jié)果。 這時(shí)發(fā)現(xiàn)，搜索采用了（clustered index seek）聚集搜索掃描。 在看看查詢返回的結(jié)果： (156603 行受影響 表 。 掃描計(jì)數(shù) 8， 邏輯讀取 5877 次， 物理讀取 0 次， 預(yù)讀 0 次，