無論解決甚麼問題事先預防總是比事後彌補來的好,所以SQL效能也是,在開始設計資料庫時設計人員就應該對於資料型態非常了解,應該要分析出哪個資料表效能會最差,哪個欄位搜尋會非常頻繁,這樣才能預先做出處裡,由於目前討論的議題是效能調教,所以不會對於開資料庫與設計資料表做太多著墨,本篇也不會討論到正規畫相關
1.預存程序
其實在於微軟效能調教相關書籍都有建議,盡量不要在程式端下T-SQL指令,第一點當然是權限的問題,工程師應該沒有權限可以看到客戶資料,第二點方便SQL指令管理,所有SQL指定都在 預存程序 內,所以要調整SQL指令只要在預存程序就可修改,第三點因為每次下T-SQL指令SQL Server會把指定給最佳化,但是假如把指令都寫成預存程序,其實SQL Server只會最佳化一次,大部分的時候會取暫存區的最佳化結果直接使用,所以效能會比較佳,但是有幾點要注意,假如第一次最佳化是比較差的結果,之後都會是使用較差大結果,但是這問題通常只要設對索引,最佳化結果都是最好的,還有不要把所以SQL指令都寫成一個大預存程序利用參數去判斷要使用哪個SQL,因為預存程序只要大於8K就不會暫存最佳化了,而且最佳化解果也不會好
2.索引
索引可以幫助我們快速取的我們所需要的資料,但是不是每個欄位都加索引效能就會好,有時還會有反效果,而且索引是要維護,所以索引一定要設到正確的欄位上,通常是常常搜尋的那些欄位或一些常用來欄位
設索引的方法:
先在SAMPLE資料庫中加上一個產品資料表(Product)語法如下
CREATE TABLE [dbo].[Product](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [nvarchar](50) NOT NULL,
[Price] [int] NOT NULL,
[amount] [int] NOT NULL,
[information] [nvarchar](500) NOT NULL,
[feature] [nvarchar](500) NOT NULL,
[CreateDate] [datetime] NOT NULL,
[CreateIP] [nvarchar](16) NOT NULL,
[CreateID] [nvarchar](10) NOT NULL,
[UpdateDate] [datetime] NULL,
[UpdateIP] [nvarchar](16) NULL,
[UpdateID] [nvarchar](10) NULL,
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
再新增一些資料,10000筆依樣的資料
declare @i int=1
declare @id int=0
WHILE (@i<=10000)
begin
set @i =@i+ 1
INSERT INTO [Product]
([name]
,[Price]
,[amount]
,[information]
,[feature]
,[CreateDate]
,[CreateIP]
,[CreateID])
VALUES
('氣動磁力張力器'
,1000003
,150
,'MTA20:
¢0.03-0.07mm(4.0-40g)
MTA100:
¢0.04-0.14mm(10-120g)
MTA300:
¢0.08-0.25mm(50-400g)
MTA600:
¢0.12-0.35mm(80-600g)
MTA800:
¢0.14-0.40mm(100-800g) ˚
'
,'漆包線:0.1-0.6mm
鐵氟龍:#22-#30'
,getdate()
,'10.23.4.4'
,'8888'
)
end
目前因為有下主鍵,所以裡面已經有一個主索引鍵了
我們先測試搜尋全部產品名稱效能
SELECT name FROM Product
where name='氣動磁力張力器'
執行計畫:子樹成本為4.74,叢集索引掃描
我們再把name 的索引加上去,索引位置按右鍵,按下新增索引,選取所需新增的索引產品名稱(name)
我們執行搜尋全部產品名稱指令
SELECT name FROM Product
where name='氣動磁力張力器'
執行計畫:子樹成本為0.34,索引掃描
加上索引後效能變好了
索引維護方法:
長期新增修改資料會導致索引連續性破碎,只要使用SQL Server介面方法就可以觀察資料的連續性與重建或重組
首先我們先大量修改資料導致資料連續信破碎
UPDATE [Product]
SET [name] = [name]+'A'
在該索引案右鍵,再按屬性,選擇到片段分頁,我們看到 片段總計 為28.51 %,表示索引之葉層分頁中失序之分頁有28.51 %,通常 頁面飽和度 也會隨之降低
我們可以在重新組織索引鍵,或在該索引鍵上按右鍵點擊重建或重新組織,但是這會佔據效能所以建議在離峰時間做
3.全文檢索
全文檢索其實跟LIKE模糊搜尋很像,但是全文檢索是利用關聯度去查詢,效能會比整個資料表全部都要掃過一次的模糊搜尋還來的好,下面是一個簡單的建立全文檢索的範例
建立全文檢索
在該資料表按右鍵移動到 全文檢索索引,選取 定義全文檢索索引,下一步至選取資料表資料行並選取需要全文檢索欄位,選擇語系,假如是台灣繁體中文請選Traditional Chinese
下一步並選取完成最後成功後關閉
比較看看兩個語法的效能
select name from Product
where [information] like '%MTA300:%'
顯示估計執行計畫結果:估計子數成本約為4.74
select name from Product
WHERE CONTAINS([information], 'MTA300:')
顯示估計執行計畫結果:估計子數成本約為1.97
所以全文檢索可以大幅增加模糊查詢的效能
當然全文檢索還有很多函式可以使用,有需要可以自行再去查查看
4.主索引鍵的選擇
主索引鍵為資料的識別欄位,這欄位對於搜尋效能影響很大,索引鍵的選擇也相對重要,依照微軟相關書籍說法,索引鍵盡量越短越好,數字優於字串,也盡量不要用複合主索引鍵,因為在資料搜尋時,SQL會將多個欄位串成一個大索引鍵,對效能比較不好,所以其實最好的主索引鍵是SQL Server提供的自動編號
留言列表
