儲存方式對空間使用的影響和效能分析

從儲存方式上比較叢集索引(clustered)和非叢集索引(nonclustered)-->>TravyLee產生測試資料:以下所有表格中的資料量均為19329條(需要注意這並不是一個大資料量的表)那麼我們現在在建立一張表ORDERS_C，結構砼前兩張表一樣，不使用任何索引，

使用堆來儲存IF OBJECT_ID('ORDERS_C') IS NOT NULLDROP TABLE ORDERS_CGOCREATE TABLE ORDERS_C(ID INT IDENTITY(1,1),UserId VARCHAR(5) NOT NULL,OrdersId VARCHAR(8) NOT NULL,CreateDate DATETIME,ProductsId VARCHAR(5) DEFAULT('A0001'),ProductName VARCHAR(255) DEFAULT('小F姐姐'),Amounts INT,Others VARCHAR(255) DEFAULT('無')) ON [PRIMARY]GOINSERT ORDERS_C(UserId,OrdersId,CreateDate,Amounts)SELECT UserId,OrdersId,CreateDate,Amounts FROM ORDERSGODBCC SHOWCONTIG(ORDERS_C)/*DBCC SHOWCONTIG 正在掃描 'ORDERS_C' 表...表: 'ORDERS_C' (39671189)；索引 ID: 0，資料庫 ID: 1已執行 TABLE 層級的掃描。- 掃描頁數................................: 185- 掃描區數..............................: 27- 區切換次數..............................: 26- 每個區的平均頁數........................: 6.9- 掃描密度 [最佳計數:實際計數].......: 88.89% [24:27]- 區掃描片段 ..................: 11.11%- 每頁的平均可用位元組數.....................: 62.5- 平均頁密度(滿).....................: 99.23%DBCC 執行完畢。如果 DBCC 輸出了錯誤資訊，請與系統管理員聯絡。*/--從上述結果中可以發現這個表格使用了185個頁面 27個區--建立一個帶有叢集索引的ORDERS_A表，資料內容同orders_c一樣IF OBJECT_ID('ORDERS_A') IS NOT NULLDROP TABLE ORDERS_AGOCREATE TABLE ORDERS_A(ID INT IDENTITY(1,1),UserId VARCHAR(5) NOT NULL,OrdersId VARCHAR(8) NOT NULL,CreateDate DATETIME,ProductsId VARCHAR(5) DEFAULT('A0001'),ProductName VARCHAR(255) DEFAULT('小F姐姐'),Amounts INT,Others VARCHAR(255) DEFAULT('無'),CONSTRAINT PK_UserId_OrdersId_A PRIMARY KEY CLUSTERED(UserId ASC,OrdersId ASC) ON [PRIMARY]) ON [PRIMARY]GOINSERT ORDERS_A(UserId,OrdersId,CreateDate,Amounts)SELECT  UserId,OrdersId,CreateDate,Amounts FROM ORDERS--使用DBCC SHOWCONTIG命令查看這個表的儲存空間情況DBCC SHOWCONTIG(ORDERS_A)WITH ALL_INDEXES/*DBCC SHOWCONTIG 正在掃描 'ORDERS_A' 表...表: 'ORDERS_A' (103671417)；索引 ID: 1，資料庫 ID: 1已執行 TABLE 層級的掃描。- 掃描頁數................................: 185- 掃描區數..............................: 26- 區切換次數..............................: 25- 每個區的平均頁數........................: 7.1- 掃描密度 [最佳計數:實際計數].......: 92.31% [24:26]- 邏輯掃描片段 ..................: 2.70%- 區掃描片段 ..................: 7.69%- 每頁的平均可用位元組數.....................: 62.5- 平均頁密度(滿).....................: 99.23%DBCC 執行完畢。如果 DBCC 輸出了錯誤資訊，請與系統管理員聯絡。*/--從上述結果中可以發現這個表格使用了185個頁面 26個區--下面我建立同樣結構的一個表,但是主鍵列在非叢集索引上,他的儲存結構是一個堆加B樹IF OBJECT_ID('ORDERS_B') IS NOT NULLDROP TABLE ORDERS_BGOCREATE TABLE ORDERS_B(ID INT IDENTITY(1,1),UserId VARCHAR(5) NOT NULL,OrdersId VARCHAR(8) NOT NULL,CreateDate DATETIME,ProductsId VARCHAR(5) DEFAULT('A0001'),ProductName VARCHAR(255) DEFAULT('小F姐姐'),Amounts INT,Others VARCHAR(255) DEFAULT('無'),CONSTRAINT PK_UserId_OrdersId_B PRIMARY KEY NONCLUSTERED(UserId ASC,OrdersId ASC) ON [PRIMARY]) ON [PRIMARY]GOINSERT ORDERS_B(UserId,OrdersId,CreateDate,Amounts)SELECT UserId,OrdersId,CreateDate,Amounts FROM ORDERS--使用DBCC SHOWCONTIG命令查看這個表的儲存空間情況DBCC SHOWCONTIG(ORDERS_B) WITH ALL_INDEXES/*DBCC SHOWCONTIG 正在掃描 'ORDERS_B' 表...表: 'ORDERS_B' (183671702)；索引 ID: 0，資料庫 ID: 1已執行 TABLE 層級的掃描。- 掃描頁數................................: 185- 掃描區數..............................: 29- 區切換次數..............................: 28- 每個區的平均頁數........................: 6.4- 掃描密度 [最佳計數:實際計數].......: 82.76% [24:29]- 區掃描片段 ..................: 55.17%- 每頁的平均可用位元組數.....................: 62.5- 平均頁密度(滿).....................: 99.23%DBCC SHOWCONTIG 正在掃描 'ORDERS_B' 表...表: 'ORDERS_B' (183671702)；索引 ID: 2，資料庫 ID: 1已執行 LEAF 層級的掃描。- 掃描頁數................................: 103- 掃描區數..............................: 19- 區切換次數..............................: 18- 每個區的平均頁數........................: 5.4- 掃描密度 [最佳計數:實際計數].......: 68.42% [13:19]- 邏輯掃描片段 ..................: 6.80%- 區掃描片段 ..................: 78.95%- 每頁的平均可用位元組數.....................: 47.4- 平均頁密度(滿).....................: 99.41%DBCC 執行完畢。如果 DBCC 輸出了錯誤資訊，請與系統管理員聯絡。*/--從上面的結果可以看出表格ORDERS_B使用頁面數量=185+103=288  使用區數量=29+19=48--下面我把對這三張表進行DBCC SHOWCONTIG操作後的資料進行以下匯總對比/*-------------------------------------------------------------------------  儲存方式-使用頁面數量-使用區數量-------------------------------------------------------------------------沒有任何索引-185-27-------------------------------------------------------------------------有叢集索引-185-26-------------------------------------------------------------------------有非叢集索引-288-48-------------------------------------------------------------------------*/--從對這個表格反映出來的資料對比我們能夠更直觀的發現--建立叢集索引並沒有增加表格的空間的大小--而建立非叢集索引則增加了不小的空間--那麼這三種儲存方式在執行查詢的時候效率究竟如何呢？接下來我們來看一看--首先提出一種觀點:當一個表格經常發生變化時，如果在這張表格上建立叢集索引，--會容易遇到頁面分割。所以建立叢集索引會影響到效能。基於這種考慮，很多資料庫--設計者不願意在表格上建立叢集索引。但是一張不見索引的表效能又不能接受，所--以他們又加上一個非叢集索引，以期得到好的效能--SQL Server這種堆和樹的儲存方式決定了建立非叢集索引這種設計是一個既浪費空--間，效能也不一定是最好的。前面的分析已經說明了它的浪費儲存空間，接下來以--一個最直觀的查詢來分析它的效能SELECT * FROM ORDERS_C--無任何索引SELECT * FROM ORDERS_A--有叢集索引SELECT * FROM ORDERS_B--又費叢集索引--下面我們篩選出userid=1001 and OrdersId=0112321的訂單--查詢一SELECTa.ID,a.UserId,b.UserName,a.OrdersId,a.CreateDate,a.ProductName,a.AmountsFROM ORDERS_C ainner joinUSERS bONa.UserId=b.UserIdwhere a.UserId='10018' and OrdersId='0118198'--無任何索引/*SQL Server 分析和編譯時間:    CPU 時間 = 0 毫秒，佔用時間 = 0 毫秒。                                                                                                                                                                                                                                                        4表 'ORDERS_C'。掃描計數 1，邏輯讀取 185 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。表 'USERS'。掃描計數 1，邏輯讀取 1 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。 SQL Server 執行時間:   CPU 時間 = 15 毫秒，佔用時間 = 7 毫秒。*/

執行計畫圖:

--查詢二SELECTa.ID,a.UserId,b.UserName,a.OrdersId,a.CreateDate,a.ProductName,a.AmountsFROM ORDERS_A ainner joinUSERS bONa.UserId=b.UserIdwhere a.UserId='10018' and OrdersId='0118198'--有叢集索引/*SQL Server 分析和編譯時間:    CPU 時間 = 0 毫秒，佔用時間 = 0 毫秒。表 'USERS'。掃描計數 1，邏輯讀取 1 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。表 'ORDERS_A'。掃描計數 0，邏輯讀取 2 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。 SQL Server 執行時間:   CPU 時間 = 0 毫秒，佔用時間 = 0 毫秒。*/

執行計畫圖

--查詢三SELECTa.ID,a.UserId,b.UserName,a.OrdersId,a.CreateDate,a.ProductName,a.AmountsFROM ORDERS_B ainner joinUSERS bONa.UserId=b.UserIdwhere a.UserId='10018' and OrdersId='0118198'--又費叢集索引/*SQL Server 分析和編譯時間:    CPU 時間 = 0 毫秒，佔用時間 = 3 毫秒。表 'USERS'。掃描計數 1，邏輯讀取 1 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。表 'ORDERS_B'。掃描計數 0，邏輯讀取 3 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。 SQL Server 執行時間:   CPU 時間 = 0 毫秒，佔用時間 = 0 毫秒。*/

執行計畫圖

GO--結合上訴結果和執行計畫圖的分析不難看出:--不加任何索引的查詢CPU開銷和I/O開銷明顯多於加有索引的--而叢集索引和非叢集索引比較起來效能上存在差距--叢集索引的語句執行的邏輯讀取次數少於非叢集索引查詢的時候的邏輯讀取次數--再看執行計畫上的cpu開銷和i/o開銷，叢集索引少於非叢集索引的--以上僅僅是一個查詢的驗證，還可以用插入,update,delete驗證--索引會影響到insert的效能，那麼這兩種索引誰對效能的損失更--大呢？有興趣可以測試一下。

不知道怎麼傳圖片，文章裡面有執行計畫圖  

http://bbs.csdn.net/topics/390275646?page=1#post-392870017