3.查看執(zhí)行計劃
首先我這個例子的語句太過簡單，你整個復雜的，包涵啊。
分析：鼠標放在圖標上會顯示此步驟執(zhí)行的詳細內(nèi)容，每個表下面都顯示一個開銷百分比，分析站百分比多的的一塊，可以根據(jù)重新設計數(shù)據(jù)結構，或這重寫sql語句，來對此進行優(yōu)化。如果存在掃描表，或者掃描聚集索引，這表示在當前查詢中你的索引是不合適的，是沒有起到作用的，那么你就要修改完善優(yōu)化你的索引，具體怎么做，你可以根據(jù)我上一篇文章中的sql優(yōu)化利器--數(shù)據(jù)庫引擎優(yōu)化顧問對索引進行分析優(yōu)化。
select查詢藝術
1.保證不查詢多余的列與行。
盡量避免select * 的存在，使用具體的列代替*，避免多余的列
使用where限定具體要查詢的數(shù)據(jù)，避免多余的行
使用top，distinct關鍵字減少多余重復的行
2.慎用distinct關鍵字
distinct在查詢一個字段或者很少字段的情況下使用，會避免重復數(shù)據(jù)的出現(xiàn)，給查詢帶來優(yōu)化效果。
但是查詢字段很多的情況下使用，則會大大降低查詢效率。
由這個圖，分析下:
很明顯帶distinct的語句cpu時間和占用時間都高于不帶distinct的語句。原因是當查詢很多字段時，如果使用distinct，數(shù)據(jù)庫引擎就會對數(shù)據(jù)進行比較，過濾掉重復數(shù)據(jù)，然而這個比較，過濾的過程則會毫不客氣的占用系統(tǒng)資源，cpu時間。
3.慎用union關鍵字
此關鍵字主要功能是把各個查詢語句的結果集合并到一個結果集中返回給你。用法
<select 語句1>
union
<select 語句2>
union
<select 語句3>
...
滿足union的語句必須滿足：1.列數(shù)相同。 2.對應列數(shù)的數(shù)據(jù)類型要保持兼容。
執(zhí)行過程：
依次執(zhí)行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<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 結果一樣，因為他篩選過啦 ----------------------------------對比上下兩個語句----------------------------------------- select * from (( orde o left join orderproduct op on o.orderNum=op.orderNum ) inner join product p on op.proNum=p.productnum) |
由此可見效率確實低，所以不是在必要情況下避免使用。其實有他執(zhí)行的第三部：對結果集進行排序，過濾重復記錄。就能看出不是什么好鳥。然而不對結果集排序過濾，顯然效率是比union高的，那么不排序過濾的關鍵字有嗎？答，有，他是union all，使用union all能對union進行一定的優(yōu)化。。
4.判斷表中是否存在數(shù)據(jù)
select count(*) from product
select top(1) id from product
很顯然下面完勝
5.連接查詢的優(yōu)化
首先你要弄明白你想要的數(shù)據(jù)是什么樣子的，然后再做出決定使用哪一種連接，這很重要。
各種連接的取值大小為：
內(nèi)連接結果集大小取決于左右表滿足條件的數(shù)量
左連接取決與左表大小，右相反。
完全連接和交叉連接取決與左右兩個表的數(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)建臨時表
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 |
我這里運行時間是51秒
--創(chuàng)建臨時表
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 |
我這里運行時間大概是20秒
分析說明：insert into select批量插入，明顯提升效率。所以以后盡量避免一個個循環(huán)插入。
優(yōu)化修改刪除語句
如果你同時修改或刪除過多數(shù)據(jù)，會造成cpu利用率過高從而影響別人對數(shù)據(jù)庫的訪問。
如果你刪除或修改過多數(shù)據(jù)，采用單一循環(huán)操作，那么會是效率很低，也就是操作時間過程會很漫長。
這樣你該怎么做呢？
折中的辦法就是，分批操作數(shù)據(jù)。
delete product where id<1000
delete product where id>=1000 and id<2000
delete product where id>=2000 and id<3000
.....
當然這樣的優(yōu)化方式不一定是最優(yōu)的選擇，其實這三種方式都是可以的，這要根據(jù)你系統(tǒng)的訪問熱度來定奪，關鍵你要明白什么樣的語句是什么樣的效果。
總結：優(yōu)化，最重要的是在于你平時設計語句，數(shù)據(jù)庫的習慣，方式。如果你平時不在意，匯總到一塊再做優(yōu)化，你就需要耐心的分析，然而分析的過程就看你的悟性，需求，知識水平啦。