SQL語句執(zhí)行效率

1、SQL 語句執(zhí)行效率SQL 語句中， IN、EXISTS、NOT IN 、NOT EXISTS 的效率較低，尤其是后兩種語句，當(dāng)數(shù) 據(jù)量較大時， 更常給人一種死機(jī)般的感覺。 本文提供一種使用連接的方法代替以上的四種語 句，可大副提高 SQL 語句的運(yùn)行效率。以 NOT IN 為例，當(dāng)數(shù)據(jù)量達(dá)到一萬時，效率可提 高 20 倍，數(shù)據(jù)量越大，效率提高的幅度也就越大。1) SELECT 語句中的中的效率提高方法SQL 語句如下：CREATE TABLE TAB1(COL1 V ARCHAR(20) NOT NULL,COL2 INTEGER ，PRIMARY KEY(COL1);CREATE TABL

2、E TAB2(COL1 V ARCHAR(20) NOT NULL,PRIMARY KEY(COL1);CREATE TABLE TAB3(COL1 V ARCHAR(20) NOT NULL,PRIMARY KEY(COL1);下面，我們將舉 2 個例子來具體說明使用連接替換IN、NOT IN 、 EXISTS 、NOT EXISTS的方法。讀取表 1中第 2 列( COL2 )數(shù)據(jù)的總和，且其第 1 列數(shù)據(jù)存在于表 2的第 1 列中。1. 使用 IN 的 SQL 語句：SELECT SUM(COL2) FROM TAB1 WHERE COL1 IN(SELECT COL1 FROM TAB

3、2)2. 使用 EXISTS 的 SQL 語句：SELECT SUM(COL2) FROM TAB1 WHERE EXISTS(SELECT * FROM TAB2 WHERE TAB1.COL1=TAB2.COL1)3. 使用連接的 SQL 語句：SELECT SUM(A.COL2) FROM TAB1 A,TAB2 BWHERE A.COL1=B.COL1讀取表 1中第 2 列( COL2 )數(shù)據(jù)的總和，且其第 1 列數(shù)據(jù)不存在于表 2 的第 1 列中。1. 使用 NOT IN 的 SQL 語句：SELECT SUM(COL2) FROM TAB1 WHERE COL1 NOT IN(SE

4、LECT COL1 FROM TAB2)2. 使用 NOT EXISTS 的 SQL 語句：SELECT SUM(COL2) FROM TAB1 WHERE NOT EXISTS(SELECT * FROM TAB2 WHERETAB1.COL1=TAB2.COL1)3. 使用外連接的 SQL 語句：SELECT SUM(A.COL2) FROM TAB1 A,TAB2 B WHERE A.COL1=B.COL1(+) AND B.COL1 IS NULL2) DELETE 語句中的效率提高方法SQL 語句如下：CREATE TABLE TA(CA INT)CREATE TABLE TB(CA

5、 INT)CREATE TABLE TC(CA INT)1 用 IN 的 SQL 語句：DELETE FROM TA WHERE TA.CA IN (SELECT CA FROM TB)2 用 EXISTS 的 SQL 語句：DELETE FROM TA WHERE EXISTS (SELECT * FROM TB WHERE TB.CA=TA.CA)3 使用連接的 SQL 語句：DELETE TA FROM TA,TB WHERE TA.CA=TB.CA刪除表 A 中表 A 存在但表 B 中不存在的數(shù)據(jù)1. 使用 IN 的 SQL 語句：DELETE FROM TA WHERE TA.CA

6、NOT IN (SELECT CA FROM TB)2. 使用 EXISTS 的 SQL 語句：DELETE FROM TA WHERE NOT EXISTS (SELECT CA FROM TB WHERE TB.CA=TA.CA)3. 使用連接的 SQL 語句：DELETE TA FROM TA LEFT OUTER JOIN TB ON TA.CA=TB.CA WHERE TB.CA IS NULL3) UPDATE 語句中的效率提高方法 更新表 A 中表 A 和表 B 相同的數(shù)據(jù)1. 使用 IN 的 SQL 語句：UPDATE TA SET CA=CA+10000 WHERE CA I

7、N (SELECT CA FROM TB)2. 使用 EXISTS 的 SQL 語句：UPDATE TA SET CA=CA+10000 WHERE EXISTS (SELECT CA FROM TB WHERE TB.CA=TA.CA)3. 使用連接的 SQL 語句：UPDATE TA SET TA.CA=TA.CA+10000 FROM TA,TB WHERE TA.CA=TB.CA更新表 A 中表 A 存在但表 B 中不存在的數(shù)據(jù)1. 使用 IN 的 SQL 語句：UPDATE TA SET CA=CA+10000 WHERE CA NOT IN (SELECT CA FROM TB)2

8、. 使用 EXISTS 的 SQL 語句：UPDATE TA SET CA=CA+10000 WHERE NOT EXISTS (SELECT CA FROM TB WHERE TB.CA=TA.CA)3. 使用連接的 SQL 語句：UPDATE TA SET TA.CA=TA.CA+10000 FROM TA LEFT OUTER JOIN TB ON TA.CA=TB.CA WHERE TB.CA IS NULL方法一、盡量使用復(fù)雜的 SQL 來代替簡單的一堆 SQL.同樣的事務(wù)，一個復(fù)雜的 SQL 完成的效率高于一堆簡單 SQL 完成的效率。有多個查詢時， 要善于使用 JOIN 。oRs

9、=oConn.Execute(SELECT * FROM Books)while not oRs.EofstrSQL = SELECT * FROM Authors WHERE AuthorID=&oRs(AuthorID) oRs2=oConn.Execute(strSQL)Response.write oRs(Title)&oRs2(Name)&q uot;oRs.MoveNext()wend要比下面的代碼慢：strSQL=SELECT Books.Title,Authors.Name FROM Books JOIN Authors ON Authors.AuthorID=Books.Au

10、thorIDoRs=oConn.Execute(strSQL)while not oRs.EofResponse.write oRs(Title)&oRs(Name)&qu ot; oRs.MoveNext()wend方法二、盡量避免使用可更新 RecordsetoRs=oConn.Execute(SELECT * FROM Authors WHERE AuthorID=17,3,3)oRs(Name)=DarkManoRs.Update()要比下面的代碼慢：strSQL = UPDATE Authors SET Name=DarkMan WHERE AuthorID=17 oConn.Exe

11、cute strSQL 方法三、更新數(shù)據(jù)庫時，盡量采用批處 理更新 將所有的 SQL 組成一個大的批處理 SQL ，并一次運(yùn)行；這比一個一個地更新數(shù)據(jù)要有效率 得多。這樣也更加滿足你進(jìn)行事務(wù)處理 的需要： strSQL=Orders(OrdID,CustID,OrdDat)OrderRows(OrdID,OrdRow,Item,Qty)OrderRows(OrdID,OrdRow,Item,Qty)strSQL=strSQL&SET XACT_ABORT ONn; strSQL=strSQL&BEGIN TRANSACTIONn; strSQL=strSQL&INSERT INTO VALUE

12、S(9999,1234,GETDA TE()n; strSQL=strSQL&INSERTINTOVALUES(9999,01,G4385,5)n; strSQL=strSQL&INSERTINTOVALUES(9999,02,G4726,1)n; strSQL=strSQL&COMM99v TRANSACTIONn; strSQL=strSQL&SET XACT_ABORT OFFn;oConn.Execute(strSQL);其中，SET XACT_ABORT OFF語句告訴SQL Server，如果下面的事務(wù)處理過程中，如果 遇到錯誤，就取消已經(jīng)完成的事務(wù)。方法四、數(shù)據(jù)庫索引那些將在 W

13、here 子句中出現(xiàn)的字段，你應(yīng)該首先考慮建立索引；那些需要排序的字段，也 應(yīng)該在考慮之列 。在 MS Access 中建立索引的方法：在 Access 里面選擇需要索引的表，點擊“設(shè)計” ，然后設(shè) 置相應(yīng)字段的索引 .在MS SQL Server中建立索引的方法：在 SQL Server管理器中，選擇相應(yīng)的表，然后“設(shè) 計表”，點擊右鍵，選擇“ Properties”，選擇“ indexes/keys”方法五、避免使 Text 字段太大當(dāng)字符串的值大小不固定時，用 varchar 比用 char 的效果要好 些。我曾經(jīng)看到一個例子程 序，字段被定義為 TEXT(255)，但是他的取值經(jīng)常只有

14、20個字符。這個數(shù)據(jù)表有 50k個記錄，從而使這慢 人們在使用 SQL 時往往會陷入一個誤區(qū)，即太關(guān)注于所得的結(jié)果是否正確，而忽略了 不同的實現(xiàn)方法之間可能存在的性能差異， 這種性能差異在大型的或是復(fù)雜的數(shù)據(jù)庫環(huán)境中 (如聯(lián)機(jī)事務(wù)處理 OLTP或決策支持系統(tǒng)DSS)中表現(xiàn)得尤為明顯。筆者在工作實踐中發(fā)現(xiàn)， 不良的 SQL 往往來自于不恰當(dāng)?shù)乃饕O(shè)計、不充份的連接條件和不可優(yōu)化的where 子句。在對它們進(jìn)行適當(dāng)?shù)膬?yōu)化后，總結(jié)：其運(yùn)行速度有了明顯地提高！ 下面我將從這三個方面分別進(jìn)行-為了更直觀地說明問題，所有實例中的 SQL運(yùn)行時間均經(jīng)過測試，不超過1秒的均表 示為( 19991201 and

15、 date 2000 (25 秒 )select date,sum(amount) from record group by date(55 秒)select count(*) from record where date 19990901 and place in (BJ,SH) (27 秒) 分析：date 上有大量的重復(fù)值，在非群集索引下，數(shù)據(jù)在物理上隨機(jī)存放在數(shù)據(jù)頁上，在范圍 查找時，必須執(zhí)行一次表掃描才能找到這一范圍內(nèi)的全部行。 2.在 date 上的一個群集索引select count(*) from record where date 19991201 and date 2000

16、 (14 秒)select date,sum(amount) from record group by date(28 秒)select count(*) from record where date 19990901 and place in (BJ,SH) (14 秒) 分析： 在群集索引下，數(shù)據(jù)在物理上按順序在數(shù)據(jù)頁上，重復(fù)值也排列在一起，因而在范圍查 找時，可以先找到這個范圍的起末點，且只在這個范圍內(nèi)掃描數(shù)據(jù)頁，避免了大范圍掃描， 提高了查詢速度。 3. 在 place，date，amount 上的組合索引select count(*) from record where date 1

17、9991201 and date 2000 (26 秒)select date,sum(amount) from record group by date(27 秒)select count(*) from record where date 19990901 and place in (BJ, SH) (19991201 and date 2000(19990901 and place in (BJ,SH) (,=, =)和 order by、group by 發(fā)生的列，可考慮建立群集索引； .經(jīng)常同時存取多列， 且每列都含有重復(fù)值可考慮建立組合索引；-組合索引要盡量使關(guān)鍵查詢形成索引覆蓋，

18、其前導(dǎo)列一定是使用最頻繁的列。二、不充份的連接條件： 例：表 card 有 7896 行，在 card_no 上有一個非聚集索引，表 account 有 191122 行，在 account_no 上有一個非聚集索引， 試看在不同的表連接條件下， 兩個 SQL 的執(zhí)行情況：select sum(a.amount) from account a,card b where a.card_no = b.card_no (20 秒) 將 SQL 改為：select sum(a.amount) from account a,card b where a.card_no = b.card_no and a

19、.account_no=b.account_no ( 1 秒) 分析： 在第一個連接條件下，最佳查詢方案是將 account 作外層表， card 作內(nèi)層表，利用 card 上的索引，其 I/O 次數(shù)可由以下公式估算為： 外層表 account 上的 22541 頁+(外層表第一行所要查找的 3頁) =595907次 I/Oaccount 的 191122 行* 內(nèi)層表 card 上對應(yīng)外層表 在第二個連接條件下， 最佳查詢方案是將 上的索引，其 I/O 次數(shù)可由以下公式估算為：card 作外層表， account 作內(nèi)層表， 利用 account-外層表card上的1944頁+(外層表car

20、d的7896行*內(nèi)層表account上對應(yīng)外層表每一行所要查找的 4 頁) = 33528 次 I/O 可見，只有充份的連接條件，真正的最佳方案才會被執(zhí)行。 總結(jié)： 1.多表操作在被實際執(zhí)行前，查詢優(yōu)化器會根據(jù)連接條件，列出幾組可能的連接方案并 從中找出系統(tǒng)開銷最小的最佳方案。 連接條件要充份考慮帶有索引的表、 行數(shù)多的表； 內(nèi)外 表的選擇可由公式：外層表中的匹配行數(shù) * 內(nèi)層表中每一次查找的次數(shù)確定，乘積最小為最 一 2.查看執(zhí)行方案的方法-用set showplanon，打開showplan選項，就可以看到連接順序、 使用何種索引的信息；想看更詳細(xì)的信息，需用sa角色執(zhí)行dbcc（3604

21、,310,302）。三、不可優(yōu)化的 where 子句 1 .例：下列 SQL 條件語句中的列都建有恰當(dāng)?shù)乃饕?但執(zhí)行速度卻非常慢：select * from record wheresubstring（card_no,1,4）=5378（13 秒）select * from record whereamount/30 1000（11 秒）select * from record whereconvert（char（1 0）,date,1 12）=19991 201 （ 1 0秒） 分析： where 子句中對列的任何操作結(jié)果都是在 SQL 運(yùn)行時逐列計算得到的，因此它不得不進(jìn) 行表搜索， 而

22、沒有使用該列上面的索引； 如果這些結(jié)果在查詢編譯時就能得到， 那么就可以 被 SQL 優(yōu)化器優(yōu)化， 使用索引， 避免表搜索， 因此將 SQL 重寫成下面這樣：select * from record where card_no like5378% （ 1 秒）select * from record where amount 1000*30（ 1 秒）select * from record where date= 1999/12/01（ 1 秒） 你會發(fā)現(xiàn) SQL 明顯快起來！ 2. 例：表 stuff 有 200000 行， id_no 上有非群集索引， 請看下面這個 SQL：select

23、 count（*） from stuff where id_no in（0,1）（23 秒） 分析：一 where條件中的in在邏輯上相當(dāng)于or，所以語法分析器會將in （0,1）轉(zhuǎn)化為id_no =0 or id_no=1來執(zhí)行。我們期望它會根據(jù)每個or子句分別查找，再將結(jié)果相加，這樣可以利用id_no上的索引；但實際上（根據(jù) showplan），它卻采用了 OR策略，即先取出滿足每個or子句的行， 存入臨時數(shù)據(jù)庫的工作表中， 再建立唯一索引以去掉重復(fù)行， 最后從這個臨時表 中計算結(jié)果。因此，實際過程沒有利用 id_no 上索引，并且完成時間還要受 tempdb 數(shù)據(jù)庫 性能的影響。 實踐證明，表的行數(shù)越多，工作表的性能就越差，當(dāng) stuff 有 620000 行時，執(zhí)行時間竟 達(dá)到 220 秒！還不如將 or