一、開門見山，問(wèn)題所在

sql語(yǔ)句性能達(dá)不到你的要求，執(zhí)行效率讓你忍無(wú)可忍，一般會(huì)時(shí)下面幾種情況。

• 網(wǎng)速不給力，不穩(wěn)定。
• 服務(wù)器內(nèi)存不夠，或者SQL 被分配的內(nèi)存不夠。
• sql語(yǔ)句設(shè)計(jì)不合理
• 沒有相應(yīng)的索引，索引不合理
• 沒有有效的索引視圖
• 表數(shù)據(jù)過(guò)大沒有有效的分區(qū)設(shè)計(jì)
• 數(shù)據(jù)庫(kù)設(shè)計(jì)太2，存在大量的數(shù)據(jù)冗余
• 索引列上缺少相應(yīng)的統(tǒng)計(jì)信息，或者統(tǒng)計(jì)信息過(guò)期
• ....

那么我們?nèi)绾谓o找出來(lái)導(dǎo)致性能慢的的原因呢？

• 首先你要知道是否跟sql語(yǔ)句有關(guān)，確保不是機(jī)器開不開機(jī)，服務(wù)器硬件配置太差，沒網(wǎng)你說(shuō)p啊
• 接著你使用我上一篇文章中提到的2柯南sql性能檢測(cè)工具--sql server profiler，分析出sql慢的相關(guān)語(yǔ)句，就是執(zhí)行時(shí)間過(guò)長(zhǎng)，占用系統(tǒng)資源，cpu過(guò)多的
• 然后是這篇文章要說(shuō)的，sql優(yōu)化方法跟技巧，避免一些不合理的sql語(yǔ)句，取暫優(yōu)sql
• 再然后判斷是否使用啦，合理的統(tǒng)計(jì)信息。sql server中可以自動(dòng)統(tǒng)計(jì)表中的數(shù)據(jù)分布信息，定時(shí)根據(jù)數(shù)據(jù)情況，更新統(tǒng)計(jì)信息，是很有必要的
• 確認(rèn)表中使用啦合理的索引，這個(gè)索引我前面博客中也有提過(guò)，不過(guò)那篇博客之后，還要進(jìn)一步對(duì)索引寫篇文章
• 數(shù)據(jù)太多的表，要分區(qū)，縮小查找范圍

二、分析比較執(zhí)行時(shí)間計(jì)劃讀取情況

select * from dbo.Product

執(zhí)行上面語(yǔ)句一般情況下只給你返回結(jié)果和執(zhí)行行數(shù)，那么你怎么分析呢，怎么知道你優(yōu)化之后跟沒有優(yōu)化的區(qū)別呢。

下面給你說(shuō)幾種方法。

1.查看執(zhí)行時(shí)間和cpu占用時(shí)間

set statistics time on
select * from dbo.Product
set statistics time off

打開你查詢之后的消息里面就能看到啦。

2.查看查詢對(duì)I/0的操作情況

set statistics io on
select * from dbo.Product
set statistics io off

執(zhí)行之后

• 掃描計(jì)數(shù)：索引或表掃描次數(shù)
• 邏輯讀取：數(shù)據(jù)緩存中讀取的頁(yè)數(shù)
• 物理讀取：從磁盤中讀取的頁(yè)數(shù)
• 預(yù)讀：查詢過(guò)程中，從磁盤放入緩存的頁(yè)數(shù)
• lob邏輯讀取：從數(shù)據(jù)緩存中讀取，image，text，ntext或大型數(shù)據(jù)的頁(yè)數(shù)
• lob物理讀取：從磁盤中讀取，image，text，ntext或大型數(shù)據(jù)的頁(yè)數(shù)
• lob預(yù)讀：查詢過(guò)程中，從磁盤放入緩存的image，text，ntext或大型數(shù)據(jù)的頁(yè)數(shù)

如果物理讀取次數(shù)和預(yù)讀次說(shuō)比較多，可以使用索引進(jìn)行優(yōu)化。

如果你不想使用sql語(yǔ)句命令來(lái)查看這些內(nèi)容，方法也是有的，哥教你更簡(jiǎn)單的。

查詢--->>查詢選項(xiàng)--->>高級(jí)

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

3.查看執(zhí)行計(jì)劃，執(zhí)行計(jì)劃詳解

選中查詢語(yǔ)句，點(diǎn)擊

然后看消息里面，會(huì)出現(xiàn)下面的圖例

首先我這個(gè)例子的語(yǔ)句太過(guò)簡(jiǎn)單，你整個(gè)復(fù)雜的，包涵啊。

分析：鼠標(biāo)放在圖標(biāo)上會(huì)顯示此步驟執(zhí)行的詳細(xì)內(nèi)容，每個(gè)表下面都顯示一個(gè)開銷百分比，分析站百分比多的的一塊，可以根據(jù)重新設(shè)計(jì)數(shù)據(jù)結(jié)構(gòu)，或這重寫sql語(yǔ)句，來(lái)對(duì)此進(jìn)行優(yōu)化。如果存在掃描表，或者掃描聚集索引，這表示在當(dāng)前查詢中你的索引是不合適的，是沒有起到作用的，那么你就要修改完善優(yōu)化你的索引。

三、select查詢藝術(shù)

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

盡量避免select * 的存在，使用具體的列代替*，避免多余的列

使用where限定具體要查詢的數(shù)據(jù)，避免多余的行

使用top，distinct關(guān)鍵字減少多余重復(fù)的行

2.慎用distinct關(guān)鍵字

distinct在查詢一個(gè)字段或者很少字段的情況下使用，會(huì)避免重復(fù)數(shù)據(jù)的出現(xiàn)，給查詢帶來(lái)優(yōu)化效果。

但是查詢字段很多的情況下使用，則會(huì)大大降低查詢效率。

由這個(gè)圖，分析下:

很明顯帶distinct的語(yǔ)句cpu時(shí)間和占用時(shí)間都高于不帶distinct的語(yǔ)句。原因是當(dāng)查詢很多字段時(shí)，如果使用distinct，數(shù)據(jù)庫(kù)引擎就會(huì)對(duì)數(shù)據(jù)進(jìn)行比較，過(guò)濾掉重復(fù)數(shù)據(jù)，然而這個(gè)比較，過(guò)濾的過(guò)程則會(huì)毫不客氣的占用系統(tǒng)資源，cpu時(shí)間。

3.慎用union關(guān)鍵字

此關(guān)鍵字主要功能是把各個(gè)查詢語(yǔ)句的結(jié)果集合并到一個(gè)結(jié)果集中返回給你。用法

<select 語(yǔ)句1>
union
<select 語(yǔ)句2>
union
<select 語(yǔ)句3>
...

滿足union的語(yǔ)句必須滿足：1.列數(shù)相同。 2.對(duì)應(yīng)列數(shù)的數(shù)據(jù)類型要保持兼容。

執(zhí)行過(guò)程：

依次執(zhí)行select語(yǔ)句-->>合并結(jié)果集--->>對(duì)結(jié)果集進(jìn)行排序，過(guò)濾重復(fù)記錄。

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 < 10000
union
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 < 20000 and p.id >= 10000
union
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 > 20000-- -這里可以寫p.id > 100 結(jié)果一樣， 因?yàn)樗Y選過(guò)啦
 -- -- -- -對(duì)比上下兩個(gè)語(yǔ)句-- -- -- --
 select * from((orde o left join orderproduct op on o.orderNum = op.orderNum) inner join product p on op.proNum = p.productnum)

由此可見效率確實(shí)低，所以不是在必要情況下避免使用。其實(shí)有他執(zhí)行的第三部：對(duì)結(jié)果集進(jìn)行排序，過(guò)濾重復(fù)記錄。就能看出不是什么好鳥。然而不對(duì)結(jié)果集排序過(guò)濾，顯然效率是比union高的，那么不排序過(guò)濾的關(guān)鍵字有嗎？答，有，他是union all，使用union all能對(duì)union進(jìn)行一定的優(yōu)化。。

4.判斷表中是否存在數(shù)據(jù)

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

很顯然下面完勝

5.連接查詢的優(yōu)化

首先你要弄明白你想要的數(shù)據(jù)是什么樣子的，然后再做出決定使用哪一種連接，這很重要。

各種連接的取值大小為：

內(nèi)連接結(jié)果集大小取決于左右表滿足條件的數(shù)量

左連接取決與左表大小，右相反。

完全連接和交叉連接取決與左右兩個(gè)表的數(shù)據(jù)總數(shù)量

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

由此可見減少連接表的數(shù)據(jù)數(shù)量可以提高效率。

四、insert插入優(yōu)化

--創(chuàng)建臨時(shí)表
create table# tb1
 (
 id int,
 name nvarchar(30),
 createTime datetime
 )
declare@ i int
declare@ sql varchar(1000)
set@ i = 0
while (@i < 100000) --循環(huán)插入10w條數(shù)據(jù)
begin
set@ i = @i + 1
set@ sql = ' insert into #tb1 values ('+convert(varchar(10),@i)+', ''
 erzi '+
 convert(nvarchar(30), @i) + ''
 ','
 '' +
 convert(nvarchar(30), getdate()) + ''
 ')'
 exec(@sql) end

我這里運(yùn)行時(shí)間是51秒

--創(chuàng)建臨時(shí)表
create table# tb2
 (
 id int,
 name nvarchar(30),
 createTime datetime
 )
declare@ i int
declare@ sql varchar(8000)
declare@ j int
set@ i = 0
while (@i < 10000) --循環(huán)插入10w條數(shù)據(jù)
begin
set@ j = 0
set@ sql = ' insert into #tb2 select ' +
 convert(varchar(10), @i * 100 + @j) + ','
'erzi' + convert(nvarchar(30), @i * 100 + @j) + ''
','
'' + convert(varchar(50), getdate()) + ''
''
set@ i = @i + 1
while (@j < 10)
 begin
set@ sql = @sql + ' union all select ' +
 convert(varchar(10), @i * 100 + @j) + ','
'
erzi '+convert(nvarchar(30),@i*100+@j)+'
'',
''
'+convert(varchar(50),getdate())+'
''
'
set@ j = @j + 1
end
exec(@sql)
end
drop table# tb2
select count(1) from# tb2

我這里運(yùn)行時(shí)間大概是20秒

分析說(shuō)明：insert into select批量插入，明顯提升效率。所以以后盡量避免一個(gè)個(gè)循環(huán)插入。

五、優(yōu)化修改刪除語(yǔ)句

如果你同時(shí)修改或刪除過(guò)多數(shù)據(jù)，會(huì)造成cpu利用率過(guò)高從而影響別人對(duì)數(shù)據(jù)庫(kù)的訪問(wèn)。

如果你刪除或修改過(guò)多數(shù)據(jù)，采用單一循環(huán)操作，那么會(huì)是效率很低，也就是操作時(shí)間過(guò)程會(huì)很漫長(zhǎng)。

這樣你該怎么做呢？

折中的辦法就是，分批操作數(shù)據(jù)。

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

當(dāng)然這樣的優(yōu)化方式不一定是最優(yōu)的選擇，其實(shí)這三種方式都是可以的，這要根據(jù)你系統(tǒng)的訪問(wèn)熱度來(lái)定奪，關(guān)鍵你要明白什么樣的語(yǔ)句是什么樣的效果。

總結(jié)：優(yōu)化，最重要的是在于你平時(shí)設(shè)計(jì)語(yǔ)句，數(shù)據(jù)庫(kù)的習(xí)慣，方式。如果你平時(shí)不在意，匯總到一塊再做優(yōu)化，你就需要耐心的分析，然而分析的過(guò)程就看你的悟性，需求，知識(shí)水平啦。