最近公司來一個(gè)非常虎的dba? 10幾年的經(jīng)驗(yàn) 這里就稱之為蔡老師吧?在征得我們蔡老同意的前提下? 我們來分享一下蔡老給我們帶來的寶貴財(cái)富 歡迎其他的dba來拍磚
?目錄
1、什么是執(zhí)行計(jì)劃？執(zhí)行計(jì)劃是依賴于什么信息。
?什么是執(zhí)行計(jì)劃？執(zhí)行計(jì)劃是依賴于什么信息。
執(zhí)行計(jì)劃是數(shù)據(jù)庫根據(jù)SQL語句和相關(guān)表的統(tǒng)計(jì)信息作出的一個(gè)查詢方案，這個(gè)方案是由查詢優(yōu)化器自動分析產(chǎn)生的，比如一條SQL語句如果用來從一個(gè)10萬條記錄的表中查1條記錄，那查詢優(yōu)化器會選擇“索引查找”方式，如果該表進(jìn)行了歸檔，當(dāng)前只剩下5000條記錄了，那查詢優(yōu)化器就會改變方案，采用“全表掃描”方式。
可見，執(zhí)行計(jì)劃并不是固定的，它是“個(gè)性化的”。產(chǎn)生一個(gè)正確的“執(zhí)行計(jì)劃”有兩點(diǎn)很重要：
統(tǒng)一SQL語句的寫法減少解析開銷
對于以下兩句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è)空格都不行！
減少SQL語句的嵌套
我經(jīng)常看到，從數(shù)據(jù)庫中捕捉到的一條SQL 語句打印出來有 2 張 A4 紙這么長。一般來說這么復(fù)雜的語句通常都是有問題的。我拿著這 2 頁長的 SQL 語句去請教原作者，結(jié)果他說時(shí)間太長，他一時(shí)也看不懂了。可想而知，連原作者都有可能看糊涂的 SQL 語句，數(shù)據(jù)庫也一樣會看糊涂。
一般，將一個(gè)Select 語句的結(jié)果作為子集，然后從該子集中再進(jìn)行查詢，這種一層嵌套語句還是比較常見的，但是根據(jù)經(jīng)驗(yàn)，超過 3 層嵌套，查詢優(yōu)化器就很容易給出錯(cuò)誤的執(zhí)行計(jì)劃。因?yàn)樗焕@暈了。像這種類似人工智能的東西，終究比人的分辨力要差些，如果人都看暈了，我可以保證數(shù)據(jù)庫也會暈的。
另外，執(zhí)行計(jì)劃是可以被重用的，越簡單的SQL 語句被重用的可能性越高。而復(fù)雜的 SQL 語句只要有一個(gè)字符發(fā)生變化就必須重新解析，然后再把這一大堆垃圾塞在內(nèi)存里。可想而知，數(shù)據(jù)庫的效率會何等低下。
使用“臨時(shí)表”暫存中間結(jié)果
?簡化SQL語句的重要方法就是采用臨時(shí)表暫存中間結(jié)果，但是，臨時(shí)表的好處遠(yuǎn)遠(yuǎn)不止這些，將臨時(shí)結(jié)果暫存在臨時(shí)表，后面的查詢就在tempdb中了，這可以避免程序中多次掃描主表，也大大減少了程序執(zhí)行中“共享鎖”阻塞“更新鎖”，減少了阻塞，提高了并發(fā)性能。
OLTP系統(tǒng)SQL語句必須采用綁定變量
select * from orderheader where changetime > ‘2010-10-20 00:00:01’
?傾斜字段的綁定變量窺測問題
事物都存在兩面性，綁定變量對大多數(shù)OLTP處理是適用的，但是也有例外。比如在where條件中的字段是“傾斜字段”的時(shí)候。
“傾斜字段”指該列中的絕大多數(shù)的值都是相同的，比如一張人口調(diào)查表，其中“民族”這列，90%以上都是漢族。那么如果一個(gè)SQL語句要查詢30歲的漢族人口有多少，那“民族”這列必然要被放在where條件中。這個(gè)時(shí)候如果采用綁定變量@nation會存在很大問題。
試想如果@nation傳入的第一個(gè)值是“漢族”，那整個(gè)執(zhí)行計(jì)劃必然會選擇表掃描。然后，第二個(gè)值傳入的是“布依族”，按理說“布依族”占的比例可能只有萬分之一，應(yīng)該采用索引查找。但是，由于重用了第一次解析的“漢族”的那個(gè)執(zhí)行計(jì)劃，那么第二次也將采用表掃描方式。這個(gè)問題就是著名的“綁定變量窺測”，建議對于“傾斜字段”不要采用綁定變量。
begin tran的事務(wù)要盡量地小
SQL Server中一句SQL語句默認(rèn)就是一個(gè)事務(wù)，在該語句執(zhí)行完成后也是默認(rèn)commit的。其實(shí)，這就是begin tran的一個(gè)最小化的形式，好比在每句語句開頭隱含了一個(gè)begin tran，結(jié)束時(shí)隱含了一個(gè)commit。
一些SQL查詢語句應(yīng)加上nolock
在SQL 語句中加 nolock 是提高 SQL?Server 并發(fā)性能的重要手段，在 oracle 中并不需要這樣做，因?yàn)?/span>oracle 的結(jié)構(gòu)更為合理，有 undo 表空間保存“數(shù)據(jù)前影”，該數(shù)據(jù)如果在修改中還未 commit ，那么你讀到的是它修改之前的副本，該副本放在 undo 表空間中。這樣， oracle 的讀、寫可以做到互不影響，這也是 oracle 廣受稱贊的地方。 SQL?Server? 的讀、寫是會相互阻塞的，為了提高并發(fā)性能，對于一些查詢，可以加上 nolock ，這樣讀的時(shí)候可以允許寫，但缺點(diǎn)是可能讀到未提交的臟數(shù)據(jù)。使用 nolock 有 3 條原則。
(1)?查詢的結(jié)果用于“插、刪、改”的不能加nolock? ！
(2)?查詢的表屬于頻繁發(fā)生頁分裂的，慎用nolock? ！
(3)?使用臨時(shí)表一樣可以保存“數(shù)據(jù)前影”，起到類似oracle 的 undo 表空間的功能，
能采用臨時(shí)表提高并發(fā)性能的，不要用nolock? 。
加nolock后查詢經(jīng)常發(fā)生頁分裂的表，容易產(chǎn)生跳讀或重復(fù)讀
加nolock 后可以在“插、刪、改”的同時(shí)進(jìn)行查詢，但是由于同時(shí)發(fā)生“插、刪、改”，在某些情況下，一旦該數(shù)據(jù)頁滿了，那么頁分裂不可避免，而此時(shí) nolock 的查詢正在發(fā)生，比如在第 100 頁已經(jīng)讀過的記錄，可能會因?yàn)轫摲至讯值降?/span>101 頁，這有可能使得 nolock 查詢在讀 101 頁時(shí)重復(fù)讀到該條數(shù)據(jù)，產(chǎn)生“重復(fù)讀”。同理，如果在 100 頁上的數(shù)據(jù)還沒被讀到就分到 99 頁去了，那 nolock 查詢有可能會漏過該記錄，產(chǎn)生“跳讀”。
?上面提到的哥們，在加了nolock 后一些操作出現(xiàn)報(bào)錯(cuò)，估計(jì)有可能因?yàn)?/span>nolock 查詢產(chǎn)生了重復(fù)讀， 2 條相同的記錄去插入別的表，當(dāng)然會發(fā)生主鍵沖突。
聚集索引沒有建在表的順序字段上，該表容易發(fā)生頁分裂
比如訂單表，有訂單編號orderid ，也有客戶編號 contactid ，那么聚集索引應(yīng)該加在哪個(gè)字段上呢？對于該表，訂單編號是順序添加的，如果在 orderid 上加聚集索引，新增的行都是添加在末尾，這樣不容易經(jīng)常產(chǎn)生頁分裂。然而，由于大多數(shù)查詢都是根據(jù)客戶編號來查的，因此，將聚集索引加在 contactid 上才有意義。而 contactid 對于訂單表而言，并非順序字段。
比如“張三”的“contactid ”是 001 ，那么“張三”的訂單信息必須都放在這張表的第一個(gè)數(shù)據(jù)頁上，如果今天“張三”新下了一個(gè)訂單，那該訂單信息不能放在表的最后一頁，而是第一頁！如果第一頁放滿了呢？很抱歉，該表所有數(shù)據(jù)都要往后移動為這條記錄騰地方。
SQL?Server的索引和 Oracle 的索引是不同的， SQL?Server 的聚集索引實(shí)際上是對表按照聚集索引字段的順序進(jìn)行了排序，相當(dāng)于 oracle 的索引組織表。 SQL?Server 的聚集索引就是表本身的一種組織形式，所以它的效率是非常高的。也正因?yàn)榇?#xff0c;插入一條記錄，它的位置不是隨便放的，而是要按照順序放在該放的數(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í)重新被利用。在這種情況下由于有空位可以利用，就不會發(fā)生頁分裂。但是查詢性能會比較低，因?yàn)椴樵儠r(shí)必須掃描那些沒有數(shù)據(jù)的空位。
重建聚集索引后情況改變了，因?yàn)橹亟ň奂饕褪前驯碇械臄?shù)據(jù)重新排列一遍，原來的空位沒有了，而頁的填充率又很高，插入數(shù)據(jù)經(jīng)常要發(fā)生頁分裂，所以性能大幅下降。
對于聚集索引沒有建在順序字段上的表，是否要給與比較低的頁填充率？是否要避免重建聚集索引？是一個(gè)值得考慮的問題！
使用復(fù)合索引提高多個(gè)where條件的查詢速度
復(fù)合索引通常擁有比單一索引更好的選擇性。而且，它是特別針對某個(gè)where 條件所設(shè)立的索引，它已經(jīng)進(jìn)行了排序，所以查詢速度比單索引更快。復(fù)合索引的引導(dǎo)字段必須采用“選擇性高”的字段。比如有 3 個(gè)字段：日期，性別，年齡。大家看，應(yīng)該采用哪個(gè)字段作引導(dǎo)字段？顯然應(yīng)該采用“日期”作為引導(dǎo)字段。日期是 3 個(gè)字段中選擇性最高的字段。
這里有一個(gè)例外，如果日期同時(shí)也是聚集索引的引導(dǎo)字段，可以不建復(fù)合索引，直接走聚集索引，效率也是比較高的。
不要把聚集索引建成“復(fù)合索引”，聚集索引越簡單越好，選擇性越高越好！聚集索引包括2 個(gè)字段尚可容忍。但是超過 2 個(gè)字段，應(yīng)該考慮建 1 個(gè)自增字段作為主鍵，聚集索引可以不做主鍵。
使用like進(jìn)行模糊查詢時(shí)應(yīng)注意盡量不要使用前%
有的時(shí)候會需要進(jìn)行一些模糊查詢比如
?Select?*?from?contact?where?username?like?‘%yue%’
關(guān)鍵詞%yue% ，由于 yue 前面用到了“ % ”，因此該查詢必然走全表掃描，除非必要，否則不要在關(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í)際掃過的行數(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ù)偷?#xff0c;而 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 的連接字段都缺少索引，而你還在用 SQL2000 ，干緊升級吧 .
Row_number 會導(dǎo)致表掃描，用其他方式例如top方案并且將大表存進(jìn)臨時(shí)表會更好
ROW_Number分頁的測試結(jié)果：
ROW_Number實(shí)現(xiàn)是基于order by的，排序?qū)Σ樵兊挠绊戯@而易見。
其他
諸如 有的寫法會限制使用索引?
Select?*?from?tablename?where?chgdate?+7?<?sysdate
Select?*?from?tablename?where?chgdate?<?sysdate?-7
前者會抑制chgdate列上的索引 后者不會
本篇先到此?歡迎有愛的同學(xué)拍磚
本文出自 “關(guān)注互聯(lián)網(wǎng)” 博客，請務(wù)必保留此出處http://dubing.blog.51cto.com/3911153/739690
總結(jié)
以上是生活随笔 為你收集整理的走向DBA[MSSQL篇] 从SQL语句的角度 提高数据库的访问性能 的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
如果覺得生活随笔 網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔 推薦給好友。
