集群索引:通往SQL Server索引級(jí)別3的階梯
David Durant,2013/01/25(第一次出版:2011/06/22)
該系列
本文是樓梯系列的一部分:SQL Server索引的階梯
索引是數(shù)據(jù)庫(kù)設(shè)計(jì)的基礎(chǔ)，并告訴開發(fā)人員使用數(shù)據(jù)庫(kù)非常了解設(shè)計(jì)器的意圖。不幸的是，當(dāng)性能問題出現(xiàn)時(shí)，索引常常被添加到事后。這里最后是一個(gè)簡(jiǎn)單的系列文章，它應(yīng)該能讓任何數(shù)據(jù)庫(kù)專業(yè)人員快速“跟上”他們的步伐
這個(gè)階梯的前面的級(jí)別提供了一般的索引和非聚集索引的概述。它總結(jié)了關(guān)于SQL Server索引的關(guān)鍵概念。當(dāng)請(qǐng)求到達(dá)數(shù)據(jù)庫(kù)時(shí)，無論是SELECT語句還是INSERT、UPDATE或DELETE語句，SQL Server只有三種可能的方法來訪問語句中引用的表的數(shù)據(jù):
訪問非聚集索引，避免訪問表。只有在索引包含該查詢請(qǐng)求的該表的所有數(shù)據(jù)時(shí)才有可能
使用search key(s)訪問索引，然后使用選中的書簽(s)來訪問表的各個(gè)行。
忽略索引并搜索請(qǐng)求行的表。
這個(gè)層次的開始是關(guān)注上面列表中的第三個(gè)選擇;搜索表。這又將引導(dǎo)我們討論集群索引;在第2級(jí)中提到的，但未涉及的主題。
我們將在這個(gè)級(jí)別使用的主要AdventureWorks數(shù)據(jù)庫(kù)表是SalesOrderDetail表。在121317行中，它足夠大，足以說明在表上有聚集索引的一些好處。而且，有兩個(gè)外鍵，它非常復(fù)雜，足以說明您必須對(duì)集群索引做出的設(shè)計(jì)決策。
示例數(shù)據(jù)庫(kù)
雖然我們已經(jīng)在第1級(jí)討論了示例數(shù)據(jù)庫(kù)，但它在這個(gè)時(shí)候重復(fù)。在整個(gè)樓梯中，我們將使用例子來闡明概念。這些示例基于Microsoft AdventureWorks示例數(shù)據(jù)庫(kù)。我們專注于銷售訂單。5個(gè)表將提供事務(wù)性和非事務(wù)性數(shù)據(jù)的良好組合;客戶，銷售人員，產(chǎn)品，銷售訂單，和銷售細(xì)節(jié)。為了保持注意力集中，我們使用了列的一個(gè)子集。因?yàn)锳dventureWorks是一種標(biāo)準(zhǔn)化的，銷售人員信息被分解為三個(gè)表:銷售人員、員工和聯(lián)系人。
在整個(gè)樓梯中，我們使用以下兩個(gè)術(shù)語，指的是“行項(xiàng)目”和“訂單細(xì)節(jié)”。前者是更常見的商業(yè)術(shù)語;后者出現(xiàn)在AdventureWorks表的名稱中。
完整的表集以及它們之間的關(guān)系如圖1所示。

圖1:在這個(gè)樓梯的例子中使用的表
注:
在這個(gè)階梯級(jí)別顯示的所有TSQL代碼都可以連同文章一起下載。
聚集索引
我們首先提出以下問題:如果不使用非聚集索引，那么在表中找到一行(s)需要多少工作?是否為被請(qǐng)求的行搜索表，意味著掃描無序表中的每一行?或者，SQL Server可以永久地對(duì)表中的行進(jìn)行排序，以便能夠快速地通過搜索鍵訪問它們，就像它通過搜索鍵快速訪問非聚集索引的條目一樣?答案取決于您是否指示SQL服務(wù)器在表上創(chuàng)建集群索引。
與非聚集索引不同，它們是單獨(dú)的對(duì)象，占用它們自己的空間，集群索引和表是相同的。通過創(chuàng)建集群索引，您可以指示SQL Server將表的行排序?yàn)樗饕I序列，并在以后的數(shù)據(jù)修改中維護(hù)該序列。即將到來的級(jí)別將查看生成的內(nèi)部數(shù)據(jù)結(jié)構(gòu)來完成此任務(wù)。但是現(xiàn)在，把聚集索引看作是可排序的表。給定一個(gè)行的索引鍵值，SQL Server可以快速訪問該行;然后可以按順序從這一行開始。
出于演示的目的，我們創(chuàng)建了示例表的兩個(gè)副本，SalesOrderDetail;一個(gè)沒有索引，一個(gè)有聚集索引。對(duì)于索引的鍵列，我們做出了與AdventureWorksdatabase的設(shè)計(jì)者相同的選擇:SalesOrderID / SalesOrderDetailID。清單1中的代碼制作了SalesOrderDetail表的副本。我們可以在任何時(shí)候重新運(yùn)行這段代碼，我們希望以“干凈的slate”開始。

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:創(chuàng)建SalesOrderDetail表的副本
因此，假設(shè)SalesOrderDetail表在創(chuàng)建集群索引之前是這樣的:

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

After creating the clustered index shown above, the resulting table / clustered index would look like this:

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

當(dāng)您查看上面顯示的示例數(shù)據(jù)時(shí)，您可能會(huì)注意到每個(gè)SalesOrderDetailID值都是惟一的。不要混淆;SalesOrderDetailID并不是表的主鍵。SalesOrderID / SalesOrderDetailID的組合是表的主鍵;以及聚集索引的索引鍵。
了解集群索引的基礎(chǔ)知識(shí)
集群索引鍵可以由您選擇的任何列組成;它不必基于主鍵。在我們的示例中，最重要的是，鍵的左大部分列是外鍵，SalesOrderID值。因此，銷售訂單的所有行項(xiàng)都在SalesOrderDetail表中連續(xù)出現(xiàn)。
請(qǐng)記住這些關(guān)于SQL Server集群索引的附加點(diǎn):
由于聚集索引的條目是表的行，在集群索引條目中沒有書簽值。當(dāng)SQL Server已經(jīng)在一行時(shí)，它不需要一個(gè)信息來告訴它在哪里找到這一行。
集群索引總是覆蓋查詢。由于索引和表是相同的，所以表的每一列都在索引中。
在表上有集群索引不會(huì)影響您在該表上創(chuàng)建非聚集索引的選項(xiàng)。
選擇聚集索引鍵列(s)
每個(gè)表最多只能有一個(gè)集群索引。表的行只能在一個(gè)序列中。你需要決定哪個(gè)序列，如果有的話，對(duì)每個(gè)表都是最好的;并且，如果可能的話，在表填充數(shù)據(jù)之前創(chuàng)建聚集索引。在做這個(gè)決定的時(shí)候，要記住，排序不僅意味著排序，還意味著分組;按銷售順序分組。
這就是為什么AdventureWorksdatabase的設(shè)計(jì)者在SalesOrderID中選擇SalesOrderDetailID作為SalesOrderDetail表的序列;這是線條項(xiàng)目的自然順序。
例如，如果用戶請(qǐng)求一個(gè)訂單的行項(xiàng)，他們通常會(huì)請(qǐng)求該訂單的所有行項(xiàng)。一個(gè)典型的銷售訂單表格告訴我們，訂單的打印副本總是包含所有的行項(xiàng)目。按銷售訂單的順序排列，是銷售訂單業(yè)務(wù)的性質(zhì)。倉(cāng)庫(kù)可能偶爾會(huì)有請(qǐng)求，希望通過產(chǎn)品而不是銷售訂單來查看產(chǎn)品;但是大部分的請(qǐng)求;例如銷售人員、客戶或打印發(fā)票的程序，或計(jì)算每個(gè)訂單總價(jià)值的查詢;對(duì)于任何給定的銷售訂單，將需要所有的行項(xiàng)目。
然而，僅用戶需求并不能確定什么是最好的集群索引。本系列的未來水平將涵蓋索引的內(nèi)部;因?yàn)樗饕哪承﹥?nèi)部方面也會(huì)影響到集群索引列的選擇。
堆
如果表中沒有聚集索引，那么表稱為堆。每個(gè)表要么是堆，要么是聚集索引。因此，盡管我們經(jīng)常聲明每個(gè)索引屬于兩種類型之一，集群或非集群;同樣重要的是要注意，每個(gè)表都屬于兩種類型之一;它是一個(gè)聚集索引，或者是堆。開發(fā)人員經(jīng)常說，表“有”或“沒有”聚集索引，但更有意義的是，表“是”或“不是”聚集索引。
在尋找行(不包括使用非聚集索引)時(shí)，SQL Server只有一種方法可以搜索堆(不包括使用非聚集索引)，這將從表的第一行開始，然后遍歷表，直到所有的行都被讀取。沒有序列，就沒有搜索鍵，也無法快速導(dǎo)航到特定的行。
將聚集索引與堆進(jìn)行比較
為了評(píng)估集群索引與堆的性能，清單1列出了兩份SalesOrderDetailtable的副本。一個(gè)副本是堆版本，另一個(gè)副本是在原始表上創(chuàng)建相同的集群索引(SalesOrderID、SalesOrderDetailID)。兩個(gè)表都沒有任何非聚集索引。
我們將對(duì)表的每個(gè)版本運(yùn)行相同的三個(gè)查詢;一個(gè)檢索單個(gè)行，一個(gè)為單個(gè)訂單檢索所有行，一個(gè)為單個(gè)產(chǎn)品檢索所有行。我們將在下面顯示的表中顯示SQL和每個(gè)執(zhí)行的結(jié)果。
我們的第一個(gè)查詢檢索單個(gè)行，執(zhí)行細(xì)節(jié)如表1所示。

表1:檢索一行
我們的第二個(gè)查詢?yōu)閱蝹€(gè)銷售訂單檢索所有行，您可以看到表2中的執(zhí)行細(xì)節(jié)。

sql ? ? select *
從SalesOrderDetail
在SalesOrderID = 43671
堆(s)(11行受影響)
表“SalesOrderDetail_noindex”。掃描計(jì)數(shù)1，邏輯讀1495。
群集索引(11行(s)受影響)
表“SalesOrderDetail_noindex”。掃描計(jì)數(shù)1，邏輯讀3。
將群集索引io的影響從1495讀到3讀。
與前面的查詢相同的統(tǒng)計(jì)信息。堆仍然需要一個(gè)表掃描，而群集索引將請(qǐng)求的訂單的11個(gè)細(xì)節(jié)行組合在一起，以便能夠檢索11行，就像檢索一行的IO所需要的一樣。一個(gè)即將到來的級(jí)別將詳細(xì)解釋為什么不需要額外的讀取來檢索額外的10行。

表2:為單個(gè)SalesOrder檢索所有行
第三個(gè)查詢檢索單個(gè)產(chǎn)品的所有行，執(zhí)行結(jié)果如表3所示。
SQL
SELECT *
從SalesOrderDetail
在ProductID = 755
堆
(228行受影響)
表“SalesOrderDetail_noindex”。掃描計(jì)數(shù)1，邏輯讀1495。
群集索引(228行(s)受影響)
表“SalesOrderDetail_index”。掃描計(jì)數(shù)1，邏輯讀取1513。
對(duì)聚集索引版本的集群索引io的影響稍微大一些;1513閱讀與1495讀。
在ProductID列上沒有非聚集索引的注釋幫助查找單個(gè)產(chǎn)品的行，這兩個(gè)版本都需要掃描。由于有集群索引的開銷，集群索引版本是稍微大一點(diǎn)的表;因此，掃描它需要更多的讀取，而不是掃描堆。

表3:檢索單個(gè)產(chǎn)品的所有行
我們的前兩個(gè)查詢大大受益于聚集索引的存在;第三個(gè)是近似相等的。是否有時(shí)聚類索引是一種損害?答案是肯定的，它主要與插入、更新和刪除行有關(guān)。就像在這些早期的層次中遇到的許多其他的索引一樣，它也是一個(gè)將在更高層次上更詳細(xì)地討論的主題。
一般來說，檢索收益大于維護(hù)損失;使聚集索引更適合堆。如果您在Azure數(shù)據(jù)庫(kù)中創(chuàng)建表，則沒有選擇;每個(gè)表都必須是一個(gè)聚集索引。
結(jié)論
集群索引是一個(gè)排序表，它的序列由您在創(chuàng)建索引時(shí)指定，并由SQL Server維護(hù)。該表中的任何一行都可以通過其鍵值快速訪問。在索引鍵序列中，任何一組行，都可以快速訪問，因?yàn)樗鼈兊逆I的范圍很廣。
每個(gè)表只能有一個(gè)集群索引。哪個(gè)列應(yīng)該是聚集索引鍵列的決定是您將為任何表做出的最重要的索引決定。
在第4級(jí)，我們將重點(diǎn)從邏輯轉(zhuǎn)移到物理上，引入頁面和區(qū)段，并檢查索引的物理結(jié)構(gòu)。

?

?

轉(zhuǎn)載于:https://www.cnblogs.com/yyyz516/p/7942324.html

總結(jié)

以上是生活随笔為你收集整理的翻译：集群索引:通往SQL Server索引级别3的阶梯的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。