【轉】sql語句的最佳化分析

標籤：複數   date()   .com   where   條件   --   copy   使用   count   

開門見山，問題所在

sql語句效能達不到你的要求，執行效率讓你忍無可忍，一般會時下面幾種情況。

• 網速不給力，不穩定。
• 伺服器記憶體不夠，或者SQL 被分配的記憶體不夠。
• sql語句設計不合理
• 沒有相應的索引，索引不合理
• 沒有有效索引檢視表
• 表資料過大沒有有效分區設計
• 資料庫設計太2，存在大量的資料冗餘
• 索引列上缺少相應的統計資訊，或者統計資訊到期
• ....

那麼我們如何給找出來導致效能慢的的原因呢？

• 首先你要知道是否跟sql語句有關，確保不是機器開不開機，伺服器硬體設定太差，沒網你說p啊
• 接著你使用我上一篇文章中提到的2柯南sql效能偵查工具--sql server profiler，分析出sql慢的相關語句，就是執行時間過長，佔用系統資源，cpu過多的
• 然後是這篇文章要說的，sql最佳化方法跟技巧，避免一些不合理的sql語句，取暫優sql
• 再然後判斷是否使用啦，合理的統計資訊。sql server中可以自動統計表中的資料分布資訊，定時根據資料情況，更新統計資料，是很有必要的
• 確認表中使用啦合理的索引，這個索引我前面部落格中也有提過，不過那篇部落格之後，還要進一步對索引寫篇文章
• 資料太多的表，要分區，縮小尋找範圍

分析比較執行時間計劃讀取情況

select * from dbo.Product

執行上面語句一般情況下只給你返回結果和執行行數，那麼你怎麼分析呢，怎麼知道你最佳化之後跟沒有最佳化的區別呢。

下面給你說幾種方法。

1.查看執行時間和cpu佔用時間

set statistics time onselect * from dbo.Productset statistics time off

開啟你查詢之後的訊息裡面就能看到啦。

2.查看查詢對I/0的操作情況

set statistics io onselect * from dbo.Productset statistics io off

執行之後

 

掃描計數：索引或表掃描次數

邏輯讀取：資料緩衝中讀取的頁數

物理讀取：從磁碟中讀取的頁數

預讀：查詢過程中，從磁碟放入緩衝的頁數

lob邏輯讀取：從資料緩衝中讀取，image，text，ntext或大型資料的頁數

lob物理讀取：從磁碟中讀取，image，text，ntext或大型資料的頁數

lob預讀：查詢過程中，從磁碟放入緩衝的image，text，ntext或大型資料的頁數

 

如果物理讀取次數和預讀次說比較多，可以使用索引進行最佳化。

如果你不想使用sql語句命令來查看這些內容，方法也是有的，哥教你更簡單的。

查詢--->>查詢選項--->>進階

被紅圈套上的2個選上，去掉sql語句中的set statistics io/time on/off 試試效果。哦也，你成功啦。。

3.查看執行計畫，執行計畫詳解

選中查詢語句，點擊然後看訊息裡面，會出現下面的圖例

首先我這個例子的語句太過簡單，你整個複雜的，包涵啊。

分析：滑鼠放在表徵圖上會顯示此步驟執行的詳細內容，每個表下面都顯示一個開銷百分比，分析站百分比多的的一塊，可以根據重新設計資料結構，或這重寫sql語句，來對此進行最佳化。如果存在掃描表，或者掃描叢集索引，這表示在當前查詢中你的索引是不合適的，是沒有起到作用的，那麼你就要修改完善最佳化你的索引，具體怎麼做，你可以根據我上一篇文章中的sql最佳化利器--Database Engine Tuning Advisor對索引進行分析最佳化。

select查詢藝術

1.保證不查詢多餘的列與行。

• 盡量避免select * 的存在，使用具體的列代替*，避免多餘的列
• 使用where限定具體要查詢的資料，避免多餘的行
• 使用top，distinct關鍵字減少多餘重複的行

2.慎用distinct關鍵字

distinct在查詢一個欄位或者很少欄位的情況下使用，會避免重複資料的出現，給查詢帶來最佳化效果。

但是查詢欄位很多的情況下使用，則會大大降低查詢效率。

由這個圖，分析下:

很明顯帶distinct的語句cpu時間和佔用時間都高於不帶distinct的語句。原因是當查詢很多欄位時，如果使用distinct，資料庫引擎就會對資料進行比較，過濾掉重複資料，然而這個比較，過濾的過程則會毫不客氣的佔用系統資源，cpu時間。

3.慎用union關鍵字

此關鍵字主要功能是把各個查詢語句的結果集合并到一個結果集中返回給你。用法

<select 語句1>union<select 語句2>union<select 語句3>...

滿足union的語句必須滿足：1.列數相同。 2.對應列數的資料類型要保持相容。

執行過程：

依次執行select語句-->>合并結果集--->>對結果集進行排序，過濾重複記錄。

select * from (( orde o  left join orderproduct op on o.orderNum=op.orderNum )inner join product p on op.proNum=p.productnum)  where p.id<10000unionselect * from (( orde o  left join orderproduct op on o.orderNum=op.orderNum )inner join product p on op.proNum=p.productnum)  where p.id<20000 and p.id>=10000unionselect * from (( orde o  left join orderproduct op on o.orderNum=op.orderNum )inner join product p on op.proNum=p.productnum)  where p.id>20000   ---這裡可以寫p.id>100 結果一樣，因為他篩選過啦----------------------------------對比上下兩個語句-----------------------------------------select * from (( orde o  left join orderproduct op on o.orderNum=op.orderNum )inner join product p on op.proNum=p.productnum) 

 

由此可見效率確實低，所以不是在必要情況下避免使用。其實有他執行的第三部：對結果集進行排序，過濾重複記錄。就能看出不是什麼好鳥。然而不對結果集排序過濾，顯然效率是比union高的，那麼不排序過濾的關鍵字有嗎？答，有，他是union all，使用union all能對union進行一定的最佳化。。

4.判斷表中是否存在資料

select count(*) from product select top(1) id from product

很顯然下面完勝

5.串連查詢的最佳化

首先你要弄明白你想要的資料是什麼樣子的，然後再做出決定使用哪一種串連，這很重要。

各種串連的取值大小為：

• 內串連結果集大小取決於左右表滿足條件的數量
• 左串連取決與左表大小，右相反。
• 完全串連和交叉串連取決與左右兩個表的資料總數量

select * from ( (select * from orde where OrderId>10000) o  left join orderproduct op on o.orderNum=op.orderNum )select * from ( orde o left join orderproduct op on o.orderNum=op.orderNum ) where o.OrderId>10000

 

由此可見減少串連表的資料數量可以提高效率。

insert插入最佳化

--建立暫存資料表create table #tb1( id int, name nvarchar(30), createTime datetime)declare @i intdeclare @sql varchar(1000)set @i=0while (@i<100000)  --迴圈插入10w條資料begin  set @[email protected]+1  set @sql=‘ insert into #tb1 values(‘+convert(varchar(10),@i)+‘,‘‘erzi‘+convert(nvarchar(30),@i)+‘‘‘,‘‘‘+convert(nvarchar(30),getdate())+‘‘‘)‘  exec(@sql)end

我這裡已耗用時間是51秒

--建立暫存資料表create table #tb2( id int, name nvarchar(30), createTime datetime)declare @i intdeclare @sql varchar(8000)declare @j intset @i=0while (@i<10000)  --迴圈插入10w條資料begin  set @j=0 set @sql=‘ insert into #tb2 select ‘+convert(varchar(10),@i*[email protected])+‘,‘‘erzi‘+convert(nvarchar(30),@i*[email protected])+‘‘‘,‘‘‘+convert(varchar(50),getdate())+‘‘‘‘ set @[email protected]+1 while(@j<10) begin      set @[email protected]+‘ union all select ‘+convert(varchar(10),@i*[email protected])+‘,‘‘erzi‘+convert(nvarchar(30),@i*[email protected])+‘‘‘,‘‘‘+convert(varchar(50),getdate())+‘‘‘‘   set @[email protected]+1 end  exec(@sql)enddrop table #tb2select count(1) from #tb2

我這裡已耗用時間大概是20秒

分析說明：insert into select批量插入，明顯提升效率。所以以後盡量避免一個個迴圈插入。

最佳化修改刪除語句

如果你同時修改或刪除過多資料，會造成cpu利用率過高從而影響別人對資料庫的訪問。

如果你刪除或修改過多資料，採用單一迴圈操作，那麼會是效率很低，也就是操作時間過程會很漫長。

這樣你該怎麼做呢？

折中的辦法就是，分批操作資料。

delete product where id<1000delete product where id>=1000 and id<2000delete product where id>=2000 and id<3000.....

當然這樣的最佳化方式不一定是最優的選擇，其實這三種方式都是可以的，這要根據你系統的訪問熱度來定奪，關鍵你要明白什麼樣的語句是什麼樣的效果。

總結：最佳化，最重要的是在於你平時設計語句，資料庫的習慣，方式。如果你平時不在意，匯總到一塊再做最佳化，你就需要耐心的分析，然而分析的過程就看你的悟性，需求，知識水平啦。

原部落格連結：http://www.cnblogs.com/knowledgesea/p/3686105.html

【轉】sql語句的最佳化分析