RiCo技術農場

資料庫世界中，改善查詢效能最快的方法，

我想大概就屬針對查詢特性建立相對應索引類型了，

但什麼時機使用索引效能才能最大化呢?那莫過於大海撈針時，

在說的更明白一點，索引用於龐大資料表中搜尋極少數資料時，

下面我模擬一個前幾天碰到的問題，我想大家應該會暸解。

 

--creat table
create table mytest
(
c1 int identity(1,1) ,
c2 varchar(10),
c3 date
)

--insert 10000 rows
set NOCOUNT on
DECLARE @step int
DECLARE @mysql nvarchar(4000)
set @step=1;
WHILE(@step <=10000)
begin
set @mysql='insert into mytest values(''rico'',getdate())'
exec sp_executesql @mysql 
set @step=@step+1  
end

--create nonclustered index
CREATE index idx_1 on mytest(c2)
include(c3)

--query 
SELECT c2,c3
from dbo.mytest 
where c2='rico'

I/O 和 Time統計 。

 

Query Cost:0.042 。

 

你可以看到我針對上面查詢語法建立涵蓋索引，

而SQL Server 查詢最佳化程式也果真使用索引搜尋作業，

一般人大概看到索引搜尋作業，幾乎認定這是查詢最佳路徑，

我不能說他完全錯，但也不是完全對，

因為我曾說過，索引效能最大化莫過於大海撈針時，

資料庫術語就是索引鍵值選擇性要高，

回頭來看資料表 c2 資料內容幾乎一模一樣(選擇性太低)，

所以根本就不需要為了該查詢建立非叢集索引，

因為建立無效索引不僅浪費硬碟空間(也浪費記憶體)，

也影響delete、insert、update…等效能，

所以針對索引鍵值選擇性過低的查詢語法，不如使用Full Table Scan來的好，

下面我將刪除該索引並証明我的假設是對的。

 

DROP INDEX idx_1 on mytest 

--query 
SELECT c2,c3
from dbo.mytest 
where c2='rico'

I/O和time統計 。

 

Query Cost:0.037 。

 

你可以看到我刪除非叢集索引後，I/O減少為32、查詢成本減少為0.037、經過時間減少為117 ms。

後來這位朋友問我，是否可以推薦索引設計相關書本，

我說書本是其次，IT這行只有多思考、多嘗試、多測試唯有自己親身走過才是真的。