執(zhí)行計(jì)劃是數(shù)據(jù)庫根據(jù)SQL語句和相關(guān)表的統(tǒng)計(jì)信息作出的一個(gè)查詢方案,這個(gè)方案是由查詢優(yōu)化器自動(dòng)分析產(chǎn)生的,比如一條SQL語句如果用來從一個(gè) 10萬條記錄的表中查1條記錄,那查詢優(yōu)化器會(huì)選擇“索引查找”方式,如果該表進(jìn)行了歸檔,當(dāng)前只剩下5000條記錄了,那查詢優(yōu)化器就會(huì)改變方案,采用 “全表掃描”方式。
1、 首先要搞明白什么叫執(zhí)行計(jì)劃?
執(zhí)行計(jì)劃是數(shù)據(jù)庫根據(jù)SQL語句和相關(guān)表的統(tǒng)計(jì)信息作出的一個(gè)查詢方案,這個(gè)方案是由查詢優(yōu)化器自動(dòng)分析產(chǎn)生的,比如一條SQL語句如果用來從一個(gè) 10萬條記錄的表中查1條記錄,那查詢優(yōu)化器會(huì)選擇“索引查找”方式,如果該表進(jìn)行了歸檔,當(dāng)前只剩下5000條記錄了,那查詢優(yōu)化器就會(huì)改變方案,采用 “全表掃描”方式。
可見,執(zhí)行計(jì)劃并不是固定的,它是“個(gè)性化的”。產(chǎn)生一個(gè)正確的“執(zhí)行計(jì)劃”有兩點(diǎn)很重要:
(1) SQL語句是否清晰地告訴查詢優(yōu)化器它想干什么?
(2) 查詢優(yōu)化器得到的數(shù)據(jù)庫統(tǒng)計(jì)信息是否是最新的、正確的?
2、 統(tǒng)一SQL語句的寫法
對(duì)于以下兩句SQL語句,程序員認(rèn)為是相同的,數(shù)據(jù)庫查詢優(yōu)化器認(rèn)為是不同的。
select*from dual
select*From dual
其實(shí)就是大小寫不同,查詢分析器就認(rèn)為是兩句不同的SQL語句,必須進(jìn)行兩次解析。生成2個(gè)執(zhí)行計(jì)劃。所以作為程序員,應(yīng)該保證相同的查詢語句在任何地方都一致,多一個(gè)空格都不行!
3、 不要把SQL語句寫得太復(fù)雜
我經(jīng)常看到,從數(shù)據(jù)庫中捕捉到的一條SQL語句打印出來有2張A4紙這么長。一般來說這么復(fù)雜的語句通常都是有問題的。我拿著這2頁長的SQL語句去請(qǐng)教原作者,結(jié)果他說時(shí)間太長,他一時(shí)也看不懂了??上攵?,連原作者都有可能看糊涂的SQL語句,數(shù)據(jù)庫也一樣會(huì)看糊涂。
一般,將一個(gè)Select語句的結(jié)果作為子集,然后從該子集中再進(jìn)行查詢,這種一層嵌套語句還是比較常見的,但是根據(jù)經(jīng)驗(yàn),超過3層嵌套,查詢優(yōu)化器就很容易給出錯(cuò)誤的執(zhí)行計(jì)劃。因?yàn)樗焕@暈了。像這種類似人工智能的東西,終究比人的分辨力要差些,如果人都看暈了,我可以保證數(shù)據(jù)庫也會(huì)暈的。
另外,執(zhí)行計(jì)劃是可以被重用的,越簡單的SQL語句被重用的可能性越高。而復(fù)雜的SQL語句只要有一個(gè)字符發(fā)生變化就必須重新解析,然后再把這一大堆垃圾塞在內(nèi)存里??上攵?,數(shù)據(jù)庫的效率會(huì)何等低下。
4、 使用“臨時(shí)表”暫存中間結(jié)果
簡化SQL語句的重要方法就是采用臨時(shí)表暫存中間結(jié)果,但是,臨時(shí)表的好處遠(yuǎn)遠(yuǎn)不止這些,將臨時(shí)結(jié)果暫存在臨時(shí)表,后面的查詢就在tempdb中了,這可以避免程序中多次掃描主表,也大大減少了程序執(zhí)行中“共享鎖”阻塞“更新鎖”,減少了阻塞,提高了并發(fā)性能。
5、 OLTP系統(tǒng)SQL語句必須采用綁定變量
select*from orderheader where changetime >'2010-10-20 00:00:01'
select*from orderheader where changetime >'2010-09-22 00:00:01'
以上兩句語句,查詢優(yōu)化器認(rèn)為是不同的SQL語句,需要解析兩次。如果采用綁定變量
select*from orderheader where changetime >@chgtime
@chgtime變量可以傳入任何值,這樣大量的類似查詢可以重用該執(zhí)行計(jì)劃了,這可以大大降低數(shù)據(jù)庫解析SQL語句的負(fù)擔(dān)。一次解析,多次重用,是提高數(shù)據(jù)庫效率的原則。
6、 綁定變量窺測
事物都存在兩面性,綁定變量對(duì)大多數(shù)OLTP處理是適用的,但是也有例外。比如在where條件中的字段是“傾斜字段”的時(shí)候。
“傾斜字段”指該列中的絕大多數(shù)的值都是相同的,比如一張人口調(diào)查表,其中“民族”這列,90%以上都是漢族。那么如果一個(gè)SQL語句要查詢30歲的漢族人口有多少,那“民族”這列必然要被放在where條件中。這個(gè)時(shí)候如果采用綁定變量@nation會(huì)存在很大問題。
試想如果@nation傳入的第一個(gè)值是“漢族”,那整個(gè)執(zhí)行計(jì)劃必然會(huì)選擇表掃描。然后,第二個(gè)值傳入的是“布依族”,按理說“布依族”占的比例可能只有萬分之一,應(yīng)該采用索引查找。但是,由于重用了第一次解析的“漢族”的那個(gè)執(zhí)行計(jì)劃,那么第二次也將采用表掃描方式。這個(gè)問題就是著名的“綁定變量窺測”,建議對(duì)于“傾斜字段”不要采用綁定變量。
7、 只在必要的情況下才使用begin tran
SQL Server中一句SQL語句默認(rèn)就是一個(gè)事務(wù),在該語句執(zhí)行完成后也是默認(rèn)commit的。其實(shí),這就是begin tran的一個(gè)最小化的形式,好比在每句語句開頭隱含了一個(gè)begin tran,結(jié)束時(shí)隱含了一個(gè)commit。
有些情況下,我們需要顯式聲明begin tran,比如做“插、刪、改”操作需要同時(shí)修改幾個(gè)表,要求要么幾個(gè)表都修改成功,要么都不成功。begin tran 可以起到這樣的作用,它可以把若干SQL語句套在一起執(zhí)行,最后再一起commit。好處是保證了數(shù)據(jù)的一致性,但任何事情都不是完美無缺的。Begin tran付出的代價(jià)是在提交之前,所有SQL語句鎖住的資源都不能釋放,直到commit掉。
可見,如果Begin tran套住的SQL語句太多,那數(shù)據(jù)庫的性能就糟糕了。在該大事務(wù)提交之前,必然會(huì)阻塞別的語句,造成block很多。
Begin tran使用的原則是,在保證數(shù)據(jù)一致性的前提下,begin tran 套住的SQL語句越少越好!有些情況下可以采用觸發(fā)器同步數(shù)據(jù),不一定要用begin tran。
8、 一些SQL查詢語句應(yīng)加上nolock
在SQL語句中加nolock是提高SQL Server并發(fā)性能的重要手段,在oracle中并不需要這樣做,因?yàn)閛racle的結(jié)構(gòu)更為合理,有undo表空間保存“數(shù)據(jù)前影”,該數(shù)據(jù)如果在修改中還未commit,那么你讀到的是它修改之前的副本,該副本放在undo表空間中。這樣,oracle的讀、寫可以做到互不影響,這也是oracle 廣受稱贊的地方。SQL Server 的讀、寫是會(huì)相互阻塞的,為了提高并發(fā)性能,對(duì)于一些查詢,可以加上nolock,這樣讀的時(shí)候可以允許寫,但缺點(diǎn)是可能讀到未提交的臟數(shù)據(jù)。使用 nolock有3條原則。
(1) 查詢的結(jié)果用于“插、刪、改”的不能加nolock !
(2) 查詢的表屬于頻繁發(fā)生頁分裂的,慎用nolock !
(3) 使用臨時(shí)表一樣可以保存“數(shù)據(jù)前影”,起到類似oracle的undo表空間的功能,
能采用臨時(shí)表提高并發(fā)性能的,不要用nolock 。
9、 聚集索引沒有建在表的順序字段上,該表容易發(fā)生頁分裂
比如訂單表,有訂單編號(hào)orderid,也有客戶編號(hào)contactid,那么聚集索引應(yīng)該加在哪個(gè)字段上呢?對(duì)于該表,訂單編號(hào)是順序添加的,如果在orderid上加聚集索引,新增的行都是添加在末尾,這樣不容易經(jīng)常產(chǎn)生頁分裂。然而,由于大多數(shù)查詢都是根據(jù)客戶編號(hào)來查的,因此,將聚集索引加在contactid上才有意義。而contactid對(duì)于訂單表而言,并非順序字段。
比如“張三”的“contactid”是001,那么“張三”的訂單信息必須都放在這張表的第一個(gè)數(shù)據(jù)頁上,如果今天“張三”新下了一個(gè)訂單,那該訂單信息不能放在表的最后一頁,而是第一頁!如果第一頁放滿了呢?很抱歉,該表所有數(shù)據(jù)都要往后移動(dòng)為這條記錄騰地方。
SQL Server的索引和Oracle的索引是不同的,SQL Server的聚集索引實(shí)際上是對(duì)表按照聚集索引字段的順序進(jìn)行了排序,相當(dāng)于oracle的索引組織表。SQL Server的聚集索引就是表本身的一種組織形式,所以它的效率是非常高的。也正因?yàn)榇?,插入一條記錄,它的位置不是隨便放的,而是要按照順序放在該放的數(shù)據(jù)頁,如果那個(gè)數(shù)據(jù)頁沒有空間了,就引起了頁分裂。所以很顯然,聚集索引沒有建在表的順序字段上,該表容易發(fā)生頁分裂。
曾經(jīng)碰到過一個(gè)情況,一位哥們的某張表重建索引后,插入的效率大幅下降了。估計(jì)情況大概是這樣的。該表的聚集索引可能沒有建在表的順序字段上,該表經(jīng)常被歸檔,所以該表的數(shù)據(jù)是以一種稀疏狀態(tài)存在的。比如張三下過20張訂單,而最近3個(gè)月的訂單只有5張,歸檔策略是保留3個(gè)月數(shù)據(jù),那么張三過去的 15張訂單已經(jīng)被歸檔,留下15個(gè)空位,可以在insert發(fā)生時(shí)重新被利用。在這種情況下由于有空位可以利用,就不會(huì)發(fā)生頁分裂。但是查詢性能會(huì)比較低,因?yàn)椴樵儠r(shí)必須掃描那些沒有數(shù)據(jù)的空位。
重建聚集索引后情況改變了,因?yàn)橹亟ň奂饕褪前驯碇械臄?shù)據(jù)重新排列一遍,原來的空位沒有了,而頁的填充率又很高,插入數(shù)據(jù)經(jīng)常要發(fā)生頁分裂,所以性能大幅下降。
對(duì)于聚集索引沒有建在順序字段上的表,是否要給與比較低的頁填充率?是否要避免重建聚集索引?是一個(gè)值得考慮的問題!
10、加nolock后查詢經(jīng)常發(fā)生頁分裂的表,容易產(chǎn)生跳讀或重復(fù)讀
加nolock后可以在“插、刪、改”的同時(shí)進(jìn)行查詢,但是由于同時(shí)發(fā)生“插、刪、改”,在某些情況下,一旦該數(shù)據(jù)頁滿了,那么頁分裂不可避免,而此時(shí)nolock的查詢正在發(fā)生,比如在第100頁已經(jīng)讀過的記錄,可能會(huì)因?yàn)轫摲至讯值降?01頁,這有可能使得nolock查詢在讀101頁時(shí)重復(fù)讀到該條數(shù)據(jù),產(chǎn)生“重復(fù)讀”。同理,如果在100頁上的數(shù)據(jù)還沒被讀到就分到99頁去了,那nolock查詢有可能會(huì)漏過該記錄,產(chǎn)生“跳讀”。
上面提到的哥們,在加了nolock后一些操作出現(xiàn)報(bào)錯(cuò),估計(jì)有可能因?yàn)閚olock查詢產(chǎn)生了重復(fù)讀,2條相同的記錄去插入別的表,當(dāng)然會(huì)發(fā)生主鍵沖突。
11、使用like進(jìn)行模糊查詢時(shí)應(yīng)注意
有的時(shí)候會(huì)需要進(jìn)行一些模糊查詢比如
select*from contact where username like ‘%yue%’
關(guān)鍵詞%yue%,由于yue前面用到了“%”,因此該查詢必然走全表掃描,除非必要,否則不要在關(guān)鍵詞前加%,
12、數(shù)據(jù)類型的隱式轉(zhuǎn)換對(duì)查詢效率的影響
sql server2000的數(shù)據(jù)庫,我們的程序在提交sql語句的時(shí)候,沒有使用強(qiáng)類型提交這個(gè)字段的值,由sql server 2000自動(dòng)轉(zhuǎn)換數(shù)據(jù)類型,會(huì)導(dǎo)致傳入的參數(shù)與主鍵字段類型不一致,這個(gè)時(shí)候sql server 2000可能就會(huì)使用全表掃描。Sql2005上沒有發(fā)現(xiàn)這種問題,但是還是應(yīng)該注意一下。
13、SQL Server 表連接的三種方式
(1) Merge Join
(2) Nested Loop Join
(3) Hash Join
SQL Server 2000只有一種join方式——Nested Loop Join,如果A結(jié)果集較小,那就默認(rèn)作為外表,A中每條記錄都要去B中掃描一遍,實(shí)際掃過的行數(shù)相當(dāng)于A結(jié)果集行數(shù)x B結(jié)果集行數(shù)。所以如果兩個(gè)結(jié)果集都很大,那Join的結(jié)果很糟糕。
SQL Server 2005新增了Merge Join,如果A表和B表的連接字段正好是聚集索引所在字段,那么表的順序已經(jīng)排好,只要兩邊拼上去就行了,這種join的開銷相當(dāng)于A表的結(jié)果集行數(shù)加上B表的結(jié)果集行數(shù),一個(gè)是加,一個(gè)是乘,可見merge join 的效果要比Nested Loop Join好多了。
如果連接的字段上沒有索引,那SQL2000的效率是相當(dāng)?shù)偷?,而SQL2005提供了Hash join,相當(dāng)于臨時(shí)給A,B表的結(jié)果集加上索引,因此SQL2005的效率比SQL2000有很大提高,我認(rèn)為,這是一個(gè)重要的原因。
總結(jié)一下,在表連接時(shí)要注意以下幾點(diǎn):
(1) 連接字段盡量選擇聚集索引所在的字段
(2) 仔細(xì)考慮where條件,盡量減小A、B表的結(jié)果集
(3) 如果很多join的連接字段都缺少索引,而你還在用SQL Server 2000,趕緊升級(jí)吧。