SqlServer 叢集索引真的是最好了嗎?,
--先類比環境,後面說明:USE [Temp]GO--DROP TABLE [TestTab]TRUNCATE TABLE [TestTab]CREATE TABLE [dbo].[TestTab]([UserAcount][varchar](50) NOT NULL,[UserName][varchar](50) NOT NULL,[crdatetime][datetime] NOT NULL,[value][numeric](18, 4) NULL,[Info][varchar](50) NULL,CONSTRAINT [PK_TestTab] PRIMARY KEY CLUSTERED ([UserAcount] ASC,[UserName] ASC)) ON [PRIMARY]GO--DROP VIEW [VTestTab]CREATE VIEW [dbo].[VTestTab]ASSELECT [UserAcount],[UserName],[crdatetime],[value],[Info]FROM [dbo].[TestTab]WHERE [UserAcount] = CURRENT_USERGO--插入測試資料: 2538 行INSERT INTO [TestTab]([UserAcount],[UserName],[crdatetime],[value],[Info])SELECT CURRENT_USER,name,MAX(crdate),FLOOR(RAND(ABS(CHECKSUM(NEWID())))*1000),NULLFROM master.sys.sysobjectsWHERE LEN(name)>1 AND LEN(name)<50GROUP BY name
實際環境:
上面插入的資料是一個使用者(CURRENT_USER)的資料,表中還存在更多使用者.
由於是按使用者劃分管理(分庫分區等)的,所以列名稱[UserAcount]作為叢集索引首先放在第一列
因此,使用者都是訪問視圖[VTestTab],查看到的只有使用者自己的資料.
現有以下這個查詢:
SELECT * FROM [VTestTab]WHERE [crdatetime] = '2011-06-17 03:18:08.647'
看執行計畫,查詢是走索引的.因為視圖將轉變為對錶的查詢,如下:
SELECT * FROM [TestTab]WHERE [UserAcount] = CURRENT_USERAND [crdatetime] = '2011-06-17 03:18:08.647'
開始以為,這個使用叢集索引已無法最佳化了.但是想想,按叢集索引的原理,使用叢集索引尋找,表中[UserAcount] = CURRENT_USER 的資料將全部符合,也就是叢集索引將會把目前使用者的資料全部查詢一遍,即按主鍵列[UserAcount]尋找一遍,並沒有準確定位到時間點'2011-06-17 03:18:08.647'這行
所以,現在測試看看到底叢集索引到底查詢了多少行!
--使用序列化查看,在事務結束前查看鎖情況。SET TRANSACTION ISOLATION LEVEL SERIALIZABLEBEGIN TRANSELECT * FROM [VTestTab]WHERE [crdatetime] = '2011-06-17 03:18:08.647'select resource_type,resource_description,request_mode,request_status,request_type,request_lifetime from sys.dm_tran_locks where resource_database_id=DB_ID() and request_session_id=@@SPID COMMIT TRAN
可以看到,整個雜湊鍵都尋找了一遍.有頁鎖(PAGE:IS),關鍵範圍鎖定(KEY:RangeS-S)。把符合索引鍵[UserAcount]的使用者到查詢出來了!但最終只返回一行。
如果表中的資料達到了鎖定擴大要求(表行數五六千以上),對錶將升級為共用鎖定!
既然不是最好的,那就考慮另外的索引定位了!
當前叢集索引鍵列為:([UserAcount],[UserName])
現考慮4種索引建立方法,到底哪種比較較好!~
--DROP INDEX IX_TestTab ON [TestTab]CREATE NONCLUSTERED INDEX IX_TestTab ON [TestTab]([UserAcount],[crdatetime])CREATE NONCLUSTERED INDEX IX_TestTab ON [TestTab]([crdatetime])CREATE NONCLUSTERED INDEX IX_TestTab ON [TestTab]([crdatetime],[UserAcount])CREATE NONCLUSTERED INDEX IX_TestTab ON [TestTab]([crdatetime])INCLUDE([value],[Info])
--第一種:CREATE NONCLUSTERED INDEX IX_TestTab ON [TestTab]([UserAcount],[crdatetime])
這種還是使用叢集索引,並沒有用到新的索引,因為新的索引第一個鍵列為 [UserAcount],與叢集索引一樣,所以只要有[UserAcount] ,查詢就使用叢集索引了!這個索引加上去也是白加。
--第二種:CREATE NONCLUSTERED INDEX IX_TestTab ON [TestTab]([crdatetime])
這個查詢使用了索引尋找,直接按列 [crdatetime] 查詢,但是其他不包含在索引的列,使用了鍵尋找。還得在索引子頁中尋找非索引列。並且鎖少了很多!!
--第三種:CREATE NONCLUSTERED INDEX IX_TestTab ON [TestTab]([crdatetime],[UserAcount])
這種查詢的執行計畫與上面的一致,因為緩衝計划進行了參數化,兩者執行語句一樣,使用的索引不影響執行計畫。而且鎖定資源幾乎一樣。這個索引的另一列 [UserAcount] 其實是多餘了,因為非叢集索引中都會包含叢集索引的鍵列。所以這個索引的第二列[UserAcount]可以去掉了。
--第四種:CREATE NONCLUSTERED INDEX IX_TestTab ON [TestTab]([crdatetime])INCLUDE([value],[Info])
這個鎖定資源就比較直接了!~完全使用非叢集索引尋找,查詢直接定位到資料行!~
所以對於叢集索引,應盡量使用唯一列作為叢集索引,或者最為鍵列的資料盡量不要重複,這樣才能以最快速度定位到行。若沒有唯一列,像上面的例子中,叢集索引和另一個比較有效列作為複合式索引叢集索引!~