SqlServer 叢集索引真的是最好了嗎?,

來源:互聯網
上載者:User

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])


這個鎖定資源就比較直接了!~完全使用非叢集索引尋找,查詢直接定位到資料行!~



所以對於叢集索引,應盡量使用唯一列作為叢集索引,或者最為鍵列的資料盡量不要重複,這樣才能以最快速度定位到行。若沒有唯一列,像上面的例子中,叢集索引和另一個比較有效列作為複合式索引叢集索引!~





相關文章

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.