聚簇索引：SQL Server索引層級3

標籤：date   樣本   ros   border   tables   很多   column   答案   結構   

本文是“Stairway系列：SQL Server索引的階梯”的一部分

索引是資料庫設計的基礎，並告訴開發人員使用資料庫關於設計者的意圖。不幸的是，當效能問題出現時，索引往往被添加為事後考慮。這裡最後是一個簡單的系列文章，應該使他們快速地使任何資料庫專業人員“快速”

這個階段的前面的層次提供了一般索引和非叢集索引的概述。它以下面關於SQL Server索引的關鍵概念結束。當請求到達您的資料庫時，無論是SELECT語句還是INSERT，UPDATE或DELETE語句，SQL Server都只有三種可能的方式來訪問語句中引用的表的資料：

?只訪問非叢集索引並避免訪問表。這隻能在索引包含查詢請求的這個表的所有資料時才有可能

?使用搜尋鍵訪問索引，然後使用選定的書籤訪問表中的單個行。

?忽略索引並在表中搜尋請求的行。

這個層級的重點是上面列表中的第三個選項。搜尋桌子。這反過來又會引導我們討論叢集索引。在第二級提到但沒有涉及的主題。

我們將在此層級使用的主要AdventureWorks資料庫表是SalesOrderDetail表。在121,317行，它足以說明在表上有叢集索引的一些好處。而且，有兩個外鍵，足以說明一些關於聚簇索引的設計決策。

樣本資料庫

儘管我們已經討論過一級的樣本資料庫，但是這個時候還是要重複的。 在整個這個階段，我們將用例子來說明概念。 這些樣本基於Microsoft AdventureWorks樣本資料庫。 我們專註於銷售訂單。 五張表將給我們交易和非交易資料的良好組合; Customer，SalesPerson，Product，SalesOrderHeader和SalesOrderDetail。 為了保持重點，我們使用列的一個子集。 由於AdventureWorks正常化很好，銷售人員資訊被分解為三個表：SalesPerson，Employee和Contact。

在整個階梯中，我們使用以下兩個術語來交換訂單上的單行：“訂單項”和“訂單明細”。 前者是更常見的業務術語; 後者出現在AdventureWorks表的名字內。

圖1顯示了一整套表格及其之間的關係。

圖1：這個Stairway的例子中使用的表

注意：

在這個樓梯層級顯示的所有TSQL代碼可以與文章一起下載。

叢集索引

我們首先提出以下問題：如果不使用非叢集索引，需要多少工作才能在表中找到一行？在表中搜尋請求的行意味著掃描無序表中的每一行嗎？或者，SQL Server可以永久性地對錶中的行進行排序，以便通過搜尋索引鍵快速存取它們，就像通過搜尋索引鍵快速存取非叢集索引的條目一樣？答案取決於您是否指示SQL Server在表上建立聚簇索引。

與非聚簇索引是一個獨立的對象並佔用他們自己的空間不同，聚簇索引和表是一樣的。通過建立叢集索引，可以指示SQL Server將表中的行排序為索引鍵序列，並在將來的資料修改期間維護該序列。即將到來的層級將查看產生的內部資料結構來完成此操作。但現在，把聚簇索引看作是一個有序表。給定一個行的索引索引值，SQL Server可以快速存取該行;並可以從該行按順序進行。

為了示範目的，我們建立了樣本表SalesOrderDetail的兩個副本;一個沒有索引，一個有叢集索引。關於索引的關鍵字段，我們做出與AdventureWorks資料庫的設計者做出相同的選擇：SalesOrderID / SalesOrderDetailID。清單1中的代碼建立了SalesOrderDetail表的副本。我們可以隨時重新運行這個代碼，我們希望從一個“乾淨的石板”開始。

IF EXISTS (SELECT * FROM sys.tables 

WHERE OBJECT_ID = OBJECT_ID(‘dbo.SalesOrderDetail_index‘))

DROP TABLE dbo.SalesOrderDetail_index;

GO

IF EXISTS (SELECT * FROM sys.tables 

WHERE OBJECT_ID = OBJECT_ID(‘dbo.SalesOrderDetail_noindex‘))

DROP TABLE dbo.SalesOrderDetail_noindex;

GO

 

 

SELECT * INTO dbo.SalesOrderDetail_index FROM Sales.SalesOrderDetail;

SELECT * INTO dbo.SalesOrderDetail_noindex FROM Sales.SalesOrderDetail;

GO

 

 

CREATE CLUSTERED INDEX IX_SalesOrderDetail

ON dbo.SalesOrderDetail_index (SalesOrderID, SalesOrderDetailID)

GO

清單1：建立SalesOrderDetail表的副本

因此，在建立叢集索引之前，假設SalesOrderDetail表如下所示：

SalesOrderID SalesOrderDetailID ProductID   OrderQty UnitPrice
69389    102201       864      3     38.10
56658    59519       711      1     34.99
59044    70000       956      2     1430.442
48299    22652       853      4     44.994
50218    31427       854      8     44.994
53713    50716        711      1     34.99
50299    32777       739      1     744.2727
45321    6303        775      6     2024.994
72644    115325       873      1     2.29
48306    22705       824      4     141.615
69134    101554       876      1     120.00
48361    23556        760      3     469.794
53605    50098       888      1     602.346
48317     22901        722      1     183.9382
66430    93291        872      1      8.99
65281    90265       889      2     602.346
52248    43812       871      1     9.99
47978    20189       794      2     1308.9375

在建立如上所示的叢集索引之後，產生的表/叢集索引將如下所示：

SalesOrderID SalesOrderDetailID ProductID   OrderQty UnitPrice
43668     106        722      3    178.58
43668     107        708      1    20.19
43668     108        733      3     356.90
43668     109        763      3    419.46
43669     110        747      1    714.70
43670     111        710      1    5.70
43670     112        709      2    5.70
43670     113        773      2    2,039.99
43670     114        776      1    2,024.99
43671     115        753       1    2,146.96
43671     116        714      2    28.84
43671     117        756      1    874.79
43671     118        768      2    419.46
43671     119        732      2    356.90
43671     120        763      2    419.46
43671     121        755      2    874.79
43671     122        764      2    419.46
43671     123        716      1    28.84
43671     124        711      1    20.19
43671      125        708          1     20.19
43672     126        709      6     5.70
43672     127        776      2    2,024.99
43672     128        774      1    2,039.99
43673     129        754      1    874.79
43673     130        715       3    28.84
43673     131        729      1    183.94

當您查看上面顯示的樣本資料時，您可能會注意到每個SalesOrderDetailID值都是唯一的。不要混淆; SalesOrderDetailID不是表的主鍵。 SalesOrderID / SalesOrderDetailID的組合是表的主鍵;以及聚簇索引的索引鍵。

瞭解群集索引的基礎知識

聚簇索引鍵可以由您選擇的任何列組成;它不必以主鍵為基礎。在我們的例子中，最重要的是最左邊的一列是一個外鍵，即SalesOrderID值。因此，銷售訂單的所有行項目都會在SalesOrderDetail表中連續出現。

請記住以下有關SQL Server聚簇索引的附加要點：

?由於聚簇索引的條目是表的行，聚簇索引條目中沒有書籤值。當SQL Server已經在一行時，它不需要一條資訊告訴它在哪裡找到那一行。

?聚簇索引始終覆蓋查詢。由於索引和表是一樣的，表的每一列都在索引中。

?在表上建立聚簇索引不會影響在該表上建立非聚簇索引的選項。

選擇叢集索引鍵列

每個表最多可以有一個聚簇索引。表格的行只能是一個序列。你需要決定什麼樣的順序，如果有的話，對每個表最好;並在可能的情況下在表格填充資料之前建立叢集索引。在做出這個決定時，要記住排序不僅意味著排序，而且意味著分組;如按銷售訂單對訂單項進行分組。

這就是為什麼AdventureWorks資料庫的設計者選擇SalesOrderID內的SalesOrderDetailID作為SalesOrderDetail表的順序的原因;這是訂單項的自然順序。

例如，如果使用者請求訂單的訂單項，則通常會請求該訂單的所有訂單項。一個典型的銷售訂單表單告訴我們，訂單的印刷版本總是包含所有的行項目。銷售訂單業務的性質是按銷售訂單對行項目進行分組。倉庫偶爾會要求按產品而不是銷售訂單查看訂單項，但大部分的要求;如銷售人員或客戶，列印發票的程式或計算每個訂單總價值的查詢;將需要所有銷售訂單的所有行項目。

然而，使用者需求本身並不能決定什麼是最好的叢集索引。本系列的未來層級將覆蓋指標的內部;因為索引的某些內部方面也會影響你對聚簇索引列的選擇。

堆

如果表中沒有叢集索引，則該表稱為堆。每個表都是一個堆或一個叢集索引。所以，雖然我們經常說每一個指標都屬於聚類或非聚類兩種類型之一，同樣重要的是要注意，每張桌子都屬於兩種類型之一;它是一個叢集索引或它是一堆。開發人員經常說，一個表“有”或“沒有”叢集索引，但更有意義的說，表“是”或“不是”叢集索引。

SQL Server在尋找行（不包括使用非聚簇索引）時搜尋堆只有一種方法，這是從表的第一行開始，然後繼續執行表，直到讀取所有行。沒有序列，沒有搜尋鍵，也無法快速導航到特定的行。

比較聚簇索引和堆

為了評估聚簇索引與堆的效能，清單1建立了SalesOrderDetailtable的兩個副本。一個副本是堆版本，另一個是建立原始表（SalesOrderID，SalesOrderDetailID）上的同一個叢集索引。這兩個表都沒有任何非叢集索引。

我們將對每個版本的表執行相同的三個查詢;一個檢索單個行，一個檢索單個訂單的所有行，一個檢索單個產品的所有行。我們在下面的表格中給出了SQL和每個執行的結果。

我們的第一個查詢檢索單個行，執行細節顯示在表1中。

SQL	SELECT * FROM SalesOrderDetail WHERE SalesOrderID = 43671 AND SalesOrderDetailID = 120
Heap	(1 row(s) affected) Table ‘SalesOrderDetail_noindex‘. Scan count 1, logical reads 1495.
Clustered Index	(1 row(s) affected) Table ‘SalesOrderDetail_noindex‘. Scan count 1, logical reads 3.
Impact of having the Clustered Index	IO reduced from 1495 reads to 3 reads.
Comments	No surprise.  Table scanning 121,317 rows to find just one is not very efficient.

表1：檢索單行

我們的第二個查詢檢索單個銷售訂單的所有行，您可以在表2中看到執行的詳細資料。

SQL	SELECT * FROM SalesOrderDetail WHERE SalesOrderID = 43671
Heap	(11 row(s) affected) Table ‘SalesOrderDetail_noindex‘. Scan count 1, logical reads 1495.
Clustered Index	(11 row(s) affected) Table ‘SalesOrderDetail_noindex‘. Scan count 1, logical reads 3.
Impact of having the Clustered Index	IO reduced from 1495 reads to 3 reads.
Comments	Same statistics as the previous query.  The heap still required a table scan, while the clustered index grouped the 11 detail rows of the requested order sufficiently close together so that the IO required to retrieve 11 rows was the same as the IO required to retrieve one row.  An upcoming Level will explain in detail why no additional reads were required to retrieve the additional 10 rows.

表2：檢索單個SalesOrder的所有行

我們的第三個查詢檢索單個產品的所有行，執行結果如表3所示。

SQL	SELECT * FROM SalesOrderDetail WHERE ProductID = 755
Heap	(228 row(s) affected) Table ‘SalesOrderDetail_noindex‘. Scan count 1, logical reads 1495.
Clustered Index	(228 row(s) affected) Table ‘SalesOrderDetail_index‘. Scan count 1, logical reads 1513.
Impact of having the Clustered Index	IO slightly greater for the clustered index version; 1513 reads versus 1495 reads.
Comments	Without a nonclustered index on the ProductID column to help find the rows for a single Product, both versions had to be scanned.  Because of the overhead of having a clustered index, the clustered index version is the slightly larger table; therefore scanning it required a few more reads than scanning the heap.

表3：檢索單個產品的所有行

前兩個查詢大大受益於聚簇索引的存在;第三個是大致相等的。有時叢集索引是有害的嗎？答案是肯定的，主要與插入，更新和刪除行有關。像在這些早期階段遇到的索引的很多其他方面一樣，這也是一個更進階別更詳細的主題。

一般來說，檢索效益大於維護損害;使聚簇索引更適合堆。如果您要在Azure資料庫中建立表，則別無選擇。每個表都必須是聚簇索引。

結論

叢集索引是一個有序表，其順序由您在建立索引時指定，並由SQL Server維護。根據其關索引值，該表中的任何行都可以快速存取。在索引鍵序列中，任何一組行都可以通過鍵的範圍快速存取。

每個表只能有一個聚簇索引。哪些列應該是聚簇索引鍵列的決定是您將為任何錶格做出的最重要的索引決定。

在我們的四級中，我們將重點從邏輯轉向物理，介紹頁面和範圍，並檢查索引的物理結構。

可下載的代碼

聚簇索引：SQL Server索引層級3