一個(gè)十幾年經(jīng)驗(yàn)DBA的寶貴經(jīng)驗(yàn)財(cái)富

什么是執(zhí)行計(jì)劃？執(zhí)行計(jì)劃是依賴(lài)于什么信息。 執(zhí)行計(jì)劃是數(shù)據(jù)庫(kù)根據(jù)SQL語(yǔ)句和相關(guān)表的統(tǒng)計(jì)信息作出的一個(gè)查詢(xún)方案，這個(gè)方案是由查詢(xún)優(yōu)化器自動(dòng)分析產(chǎn)生的，比如一條SQL語(yǔ)句如果用來(lái)從一個(gè)10萬(wàn)條記錄的表中查1條記錄，那查詢(xún)優(yōu)化器會(huì)選擇“索引查找”方式，

什么是執(zhí)行計(jì)劃？執(zhí)行計(jì)劃是依賴(lài)于什么信息。

執(zhí)行計(jì)劃是數(shù)據(jù)庫(kù)根據(jù)SQL語(yǔ)句和相關(guān)表的統(tǒng)計(jì)信息作出的一個(gè)查詢(xún)方案，這個(gè)方案是由查詢(xún)優(yōu)化器自動(dòng)分析產(chǎn)生的，比如一條SQL語(yǔ)句如果用來(lái)從一個(gè)10萬(wàn)條記錄的表中查1條記錄，那查詢(xún)優(yōu)化器會(huì)選擇“索引查找”方式，如果該表進(jìn)行了歸檔，當(dāng)前只剩下5000條記錄了，那查詢(xún)優(yōu)化器就會(huì)改變方案，采用“全表掃描”方式。

可見(jiàn)，執(zhí)行計(jì)劃并不是固定的，它是“個(gè)性化的”。產(chǎn)生一個(gè)正確的“執(zhí)行計(jì)劃”有兩點(diǎn)很重要：

統(tǒng)一SQL語(yǔ)句的寫(xiě)法減少解析開(kāi)銷(xiāo)

對(duì)于以下兩句SQL語(yǔ)句，程序員認(rèn)為是相同的，數(shù)據(jù)庫(kù)查詢(xún)優(yōu)化器可能認(rèn)為是不同的。

select * from dual
SELECT * FROM dual

其實(shí)就是大小寫(xiě)不同，查詢(xún)分析器就認(rèn)為是兩句不同的SQL語(yǔ)句，必須進(jìn)行兩次解析。生成2個(gè)執(zhí)行計(jì)劃。所以作為程序員，應(yīng)該保證相同的查詢(xún)語(yǔ)句在任何地方都一致，多一個(gè)空格都不行！

減少SQL語(yǔ)句的嵌套

我經(jīng)常看到，從數(shù)據(jù)庫(kù)中捕捉到的一條SQL語(yǔ)句打印出來(lái)有2張A4紙這么長(zhǎng)。一般來(lái)說(shuō)這么復(fù)雜的語(yǔ)句通常都是有問(wèn)題的。我拿著這2頁(yè)長(zhǎng)的SQL語(yǔ)句去請(qǐng)教原作者，結(jié)果他說(shuō)時(shí)間太長(zhǎng)，他一時(shí)也看不懂了。可想而知，連原作者都有可能看糊涂的SQL語(yǔ)句，數(shù)據(jù)庫(kù)也一樣會(huì)看糊涂。

一般，將一個(gè)Select語(yǔ)句的結(jié)果作為子集，然后從該子集中再進(jìn)行查詢(xún)，這種一層嵌套語(yǔ)句還是比較常見(jiàn)的，但是根據(jù)經(jīng)驗(yàn)，超過(guò)3層嵌套，查詢(xún)優(yōu)化器就很容易給出錯(cuò)誤的執(zhí)行計(jì)劃。因?yàn)樗焕@暈了。像這種類(lèi)似人工智能的東西，終究比人的分辨力要差些，如果人都看暈了，我可以保證數(shù)據(jù)庫(kù)也會(huì)暈的。

另外，執(zhí)行計(jì)劃是可以被重用的，越簡(jiǎn)單的SQL語(yǔ)句被重用的可能性越高。而復(fù)雜的SQL語(yǔ)句只要有一個(gè)字符發(fā)生變化就必須重新解析，然后再把這一大堆垃圾塞在內(nèi)存里。可想而知，數(shù)據(jù)庫(kù)的效率會(huì)何等低下。

使用“臨時(shí)表”暫存中間結(jié)果

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

OLTP系統(tǒng)SQL語(yǔ)句必須采用綁定變量

select * from orderheader where changetime > ‘2010-10-20 00:00:01’
select * from orderheader where changetime > ‘2010-09-22 00:00:01’

以上兩句語(yǔ)句，查詢(xún)優(yōu)化器認(rèn)為是不同的SQL語(yǔ)句，需要解析兩次。如果采用綁定變量：

select * from orderheader where changetime > @chgtime

@chgtime變量可以傳入任何值，這樣大量的類(lèi)似查詢(xún)可以重用該執(zhí)行計(jì)劃了，這可以大大降低數(shù)據(jù)庫(kù)解析SQL語(yǔ)句的負(fù)擔(dān)。一次解析，多次重用，是提高數(shù)據(jù)庫(kù)效率的原則。

傾斜字段的綁定變量窺測(cè)問(wèn)題

事物都存在兩面性，綁定變量對(duì)大多數(shù)OLTP處理是適用的，但是也有例外。比如在where條件中的字段是“傾斜字段”的時(shí)候。

“傾斜字段”指該列中的絕大多數(shù)的值都是相同的，比如一張人口調(diào)查表，其中“民族”這列，90%以上都是漢族。那么如果一個(gè)SQL語(yǔ)句要查詢(xún)30歲的漢族人口有多少，那“民族”這列必然要被放在where條件中。這個(gè)時(shí)候如果采用綁定變量@nation會(huì)存在很大問(wèn)題。

試想如果@nation傳入的第一個(gè)值是“漢族”，那整個(gè)執(zhí)行計(jì)劃必然會(huì)選擇表掃描。然后，第二個(gè)值傳入的是“布依族”，按理說(shuō)“布依族”占的比例可能只有萬(wàn)分之一，應(yīng)該采用索引查找。但是，由于重用了第一次解析的“漢族”的那個(gè)執(zhí)行計(jì)劃，那么第二次也將采用表掃描方式。這個(gè)問(wèn)題就是著名的“綁定變量窺測(cè)”，建議對(duì)于“傾斜字段”不要采用綁定變量。

begin tran的事務(wù)要盡量地小

SQL Server中一句SQL語(yǔ)句默認(rèn)就是一個(gè)事務(wù)，在該語(yǔ)句執(zhí)行完成后也是默認(rèn)commit的。其實(shí)，這就是begin tran的一個(gè)最小化的形式，好比在每句語(yǔ)句開(kāi)頭隱含了一個(gè)begin tran，結(jié)束時(shí)隱含了一個(gè)commit。

有些情況下，我們需要顯式聲明begin tran，比如做“插、刪、改”操作需要同時(shí)修改幾個(gè)表，要求要么幾個(gè)表都修改成功，要么都不成功。begin tran 可以起到這樣的作用，它可以把若干SQL語(yǔ)句套在一起執(zhí)行，最后再一起commit。好處是保證了數(shù)據(jù)的一致性，但任何事情都不是完美無(wú)缺的。Begin tran付出的代價(jià)是在提交之前，所有SQL語(yǔ)句鎖住的資源都不能釋放，直到commit掉。

可見(jiàn)，如果Begin tran套住的SQL語(yǔ)句太多，那數(shù)據(jù)庫(kù)的性能就糟糕了。在該大事務(wù)提交之前，必然會(huì)阻塞別的語(yǔ)句，造成block很多。

Begin tran使用的原則是，在保證數(shù)據(jù)一致性的前提下，begin tran 套住的SQL語(yǔ)句越少越好！有些情況下可以采用觸發(fā)器同步數(shù)據(jù)，不一定要用begin tran。

一些SQL查詢(xún)語(yǔ)句應(yīng)加上nolock

在SQL語(yǔ)句中加nolock是提高SQL Server并發(fā)性能的重要手段，在oracle中并不需要這樣做，因?yàn)閛racle的結(jié)構(gòu)更為合理，有undo表空間保存“數(shù)據(jù)前影”，該數(shù)據(jù)如果在修改中還未commit，那么你讀到的是它修改之前的副本，該副本放在undo表空間中。這樣，oracle的讀、寫(xiě)可以做到互不影響，這也是oracle廣受稱(chēng)贊的地方。SQL Server 的讀、寫(xiě)是會(huì)相互阻塞的，為了提高并發(fā)性能，對(duì)于一些查詢(xún)，可以加上nolock，這樣讀的時(shí)候可以允許寫(xiě)，但缺點(diǎn)是可能讀到未提交的臟數(shù)據(jù)。使用nolock有3條原則。

1. 查詢(xún)的結(jié)果用于“插、刪、改”的不能加nolock ！
2. 查詢(xún)的表屬于頻繁發(fā)生頁(yè)分裂的，慎用nolock ！
3. 使用臨時(shí)表一樣可以保存“數(shù)據(jù)前影”，起到類(lèi)似oracle的undo表空間的功能。

能采用臨時(shí)表提高并發(fā)性能的，不要用nolock 。

加nolock后查詢(xún)經(jīng)常發(fā)生頁(yè)分裂的表，容易產(chǎn)生跳讀或重復(fù)讀

加nolock后可以在“插、刪、改”的同時(shí)進(jìn)行查詢(xún)，但是由于同時(shí)發(fā)生“插、刪、改”，在某些情況下，一旦該數(shù)據(jù)頁(yè)滿(mǎn)了，那么頁(yè)分裂不可避免，而此時(shí)nolock的查詢(xún)正在發(fā)生，比如在第100頁(yè)已經(jīng)讀過(guò)的記錄，可能會(huì)因?yàn)轫?yè)分裂而分到第101頁(yè)，這有可能使得nolock查詢(xún)?cè)谧x101頁(yè)時(shí)重復(fù)讀到該條數(shù)據(jù)，產(chǎn)生“重復(fù)讀”。同理，如果在100頁(yè)上的數(shù)據(jù)還沒(méi)被讀到就分到99頁(yè)去了，那nolock查詢(xún)有可能會(huì)漏過(guò)該記錄，產(chǎn)生“跳讀”。

上面提到的哥們，在加了nolock后一些操作出現(xiàn)報(bào)錯(cuò)，估計(jì)有可能因?yàn)閚olock查詢(xún)產(chǎn)生了重復(fù)讀，2條相同的記錄去插入別的表，當(dāng)然會(huì)發(fā)生主鍵沖突。

聚集索引沒(méi)有建在表的順序字段上，該表容易發(fā)生頁(yè)分裂

比如訂單表，有訂單編號(hào)orderid，也有客戶(hù)編號(hào)contactid，那么聚集索引應(yīng)該加在哪個(gè)字段上呢？對(duì)于該表，訂單編號(hào)是順序添加的，如果在orderid上加聚集索引，新增的行都是添加在末尾，這樣不容易經(jīng)常產(chǎn)生頁(yè)分裂。然而，由于大多數(shù)查詢(xún)都是根據(jù)客戶(hù)編號(hào)來(lái)查的，因此，將聚集索引加在contactid上才有意義。而contactid對(duì)于訂單表而言，并非順序字段。

比如“張三”的“contactid”是001，那么“張三”的訂單信息必須都放在這張表的第一個(gè)數(shù)據(jù)頁(yè)上，如果今天“張三”新下了一個(gè)訂單，那該訂單信息不能放在表的最后一頁(yè)，而是第一頁(yè)！如果第一頁(yè)放滿(mǎn)了呢？很抱歉，該表所有數(shù)據(jù)都要往后移動(dòng)為這條記錄騰地方。

SQL Server的索引和Oracle的索引是不同的，SQL Server的聚集索引實(shí)際上是對(duì)表按照聚集索引字段的順序進(jìn)行了排序，相當(dāng)于oracle的索引組織表。SQL Server的聚集索引就是表本身的一種組織形式，所以它的效率是非常高的。也正因?yàn)榇耍迦胍粭l記錄，它的位置不是隨便放的，而是要按照順序放在該放的數(shù)據(jù)頁(yè)，如果那個(gè)數(shù)據(jù)頁(yè)沒(méi)有空間了，就引起了頁(yè)分裂。所以很顯然，聚集索引沒(méi)有建在表的順序字段上，該表容易發(fā)生頁(yè)分裂。

曾經(jīng)碰到過(guò)一個(gè)情況，一位哥們的某張表重建索引后，插入的效率大幅下降了。估計(jì)情況大概是這樣的。該表的聚集索引可能沒(méi)有建在表的順序字段上，該表經(jīng)常被歸檔，所以該表的數(shù)據(jù)是以一種稀疏狀態(tài)存在的。比如張三下過(guò)20張訂單，而最近3個(gè)月的訂單只有5張，歸檔策略是保留3個(gè)月數(shù)據(jù)，那么張三過(guò)去的15張訂單已經(jīng)被歸檔，留下15個(gè)空位，可以在insert發(fā)生時(shí)重新被利用。在這種情況下由于有空位可以利用，就不會(huì)發(fā)生頁(yè)分裂。但是查詢(xún)性能會(huì)比較低，因?yàn)椴樵?xún)時(shí)必須掃描那些沒(méi)有數(shù)據(jù)的空位。

重建聚集索引后情況改變了，因?yàn)橹亟ň奂饕褪前驯碇械臄?shù)據(jù)重新排列一遍，原來(lái)的空位沒(méi)有了，而頁(yè)的填充率又很高，插入數(shù)據(jù)經(jīng)常要發(fā)生頁(yè)分裂，所以性能大幅下降。

對(duì)于聚集索引沒(méi)有建在順序字段上的表，是否要給與比較低的頁(yè)填充率？是否要避免重建聚集索引？是一個(gè)值得考慮的問(wèn)題！

使用復(fù)合索引提高多個(gè)where條件的查詢(xún)速度

復(fù)合索引通常擁有比單一索引更好的選擇性。而且，它是特別針對(duì)某個(gè)where條件所設(shè)立的索引，它已經(jīng)進(jìn)行了排序，所以查詢(xún)速度比單索引更快。復(fù)合索引的引導(dǎo)字段必須采用“選擇性高”的字段。比如有3個(gè)字段：日期，性別，年齡。大家看，應(yīng)該采用哪個(gè)字段作引導(dǎo)字段？顯然應(yīng)該采用“日期”作為引導(dǎo)字段。日期是3個(gè)字段中選擇性最高的字段。

這里有一個(gè)例外，如果日期同時(shí)也是聚集索引的引導(dǎo)字段，可以不建復(fù)合索引，直接走聚集索引，效率也是比較高的。

不要把聚集索引建成“復(fù)合索引”，聚集索引越簡(jiǎn)單越好，選擇性越高越好！聚集索引包括2個(gè)字段尚可容忍。但是超過(guò)2個(gè)字段，應(yīng)該考慮建1個(gè)自增字段作為主鍵，聚集索引可以不做主鍵。

使用like進(jìn)行模糊查詢(xún)時(shí)應(yīng)注意盡量不要使用前%

有的時(shí)候會(huì)需要進(jìn)行一些模糊查詢(xún)比如

Select * from nowamagic where username like ‘%gonn%’

關(guān)鍵詞%gonn%，由于yue前面用到了“%”，因此該查詢(xún)必然走全表掃描，除非必要，否則不要在關(guān)鍵詞前加%。

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í)際掃過(guò)的行數(shù)相當(dāng)于A(yíng)結(jié)果集行數(shù)x B結(jié)果集行數(shù)。所以如果兩個(gè)結(jié)果集都很大，那Join的結(jié)果很糟糕。

SQL Server 2005新增了Merge Join，如果A表和B表的連接字段正好是聚集索引所在字段，那么表的順序已經(jīng)排好，只要兩邊拼上去就行了，這種join的開(kāi)銷(xiāo)相當(dāng)于A(yíng)表的結(jié)果集行數(shù)加上B表的結(jié)果集行數(shù)，一個(gè)是加，一個(gè)是乘，可見(jiàn)merge join 的效果要比Nested Loop Join好多了。

如果連接的字段上沒(méi)有索引，那SQL2000的效率是相當(dāng)?shù)偷模鳶QL2005提供了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的連接字段都缺少索引，而你還在用SQL2000，干緊升級(jí)吧。

Row_number 會(huì)導(dǎo)致表掃描，用臨時(shí)表分頁(yè)更好

ROW_Number分頁(yè)的測(cè)試結(jié)果：

ROW_Number實(shí)現(xiàn)是基于order by的，排序?qū)Σ樵?xún)的影響顯而易見(jiàn)。

其他

諸如有的寫(xiě)法會(huì)限制使用索引

Select * from tablename where chgdate +7 < sysdate

Select * from tablename where chgdate < sysdate -7

聲明：本網(wǎng)頁(yè)內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問(wèn)題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com