3.查看執(zhí)行計(jì)劃
首先我這個(gè)例子的語句太過簡(jiǎn)單，你整個(gè)復(fù)雜的，包涵啊。
分析：鼠標(biāo)放在圖標(biāo)上會(huì)顯示此步驟執(zhí)行的詳細(xì)內(nèi)容，每個(gè)表下面都顯示一個(gè)開銷百分比，分析站百分比多的的一塊，可以根據(jù)重新設(shè)計(jì)數(shù)據(jù)結(jié)構(gòu)，或這重寫sql語句，來對(duì)此進(jìn)行優(yōu)化。如果存在掃描表，或者掃描聚集索引，這表示在當(dāng)前查詢中你的索引是不合適的，是沒有起到作用的，那么你就要修改完善優(yōu)化你的索引，具體怎么做，你可以根據(jù)我上一篇文章中的sql優(yōu)化利器--數(shù)據(jù)庫(kù)引擎優(yōu)化顧問對(duì)索引進(jìn)行分析優(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)，給查詢帶來優(yōu)化效果。
但是查詢字段很多的情況下使用，則會(huì)大大降低查詢效率。
由這個(gè)圖，分析下:
很明顯帶distinct的語句cpu時(shí)間和占用時(shí)間都高于不帶distinct的語句。原因是當(dāng)查詢很多字段時(shí)，如果使用distinct，數(shù)據(jù)庫(kù)引擎就會(huì)對(duì)數(shù)據(jù)進(jìn)行比較，過濾掉重復(fù)數(shù)據(jù)，然而這個(gè)比較，過濾的過程則會(huì)毫不客氣的占用系統(tǒng)資源，cpu時(shí)間。
3.慎用union關(guān)鍵字
此關(guān)鍵字主要功能是把各個(gè)查詢語句的結(jié)果集合并到一個(gè)結(jié)果集中返回給你。用法
<select 語句1>
union
<select 語句2>
union
<select 語句3>
...
滿足union的語句必須滿足：1.列數(shù)相同。 2.對(duì)應(yīng)列數(shù)的數(shù)據(jù)類型要保持兼容。
執(zhí)行過程：
依次執(zhí)行select語句-->>合并結(jié)果集--->>對(duì)結(jié)果集進(jìn)行排序，過濾重復(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選過啦 ----------------------------------對(duì)比上下兩個(gè)語句----------------------------------------- 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)行排序，過濾重復(fù)記錄。就能看出不是什么好鳥。然而不對(duì)結(jié)果集排序過濾，顯然效率是比union高的，那么不排序過濾的關(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秒
分析說明：insert into select批量插入，明顯提升效率。所以以后盡量避免一個(gè)個(gè)循環(huán)插入。
優(yōu)化修改刪除語句
如果你同時(shí)修改或刪除過多數(shù)據(jù)，會(huì)造成cpu利用率過高從而影響別人對(duì)數(shù)據(jù)庫(kù)的訪問。
如果你刪除或修改過多數(shù)據(jù)，采用單一循環(huán)操作，那么會(huì)是效率很低，也就是操作時(shí)間過程會(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)的訪問熱度來定奪，關(guān)鍵你要明白什么樣的語句是什么樣的效果。
總結(jié)：優(yōu)化，最重要的是在于你平時(shí)設(shè)計(jì)語句，數(shù)據(jù)庫(kù)的習(xí)慣，方式。如果你平時(shí)不在意，匯總到一塊再做優(yōu)化，你就需要耐心的分析，然而分析的過程就看你的悟性，需求，知識(shí)水平啦。