旷世神医,盗墓笔记第二季

主頁(yè) > 知識(shí)庫(kù) > SQL Server 數(shù)據(jù)庫(kù)索引其索引的小技巧

SQL Server 數(shù)據(jù)庫(kù)索引其索引的小技巧

一、什么是索引

減少磁盤I/O和邏輯讀次數(shù)的最佳方法之一就是使用【索引】
索引允許SQL Server在表中查找數(shù)據(jù)而不需要掃描整個(gè)表。

1.1、索引的好處：

當(dāng)表沒(méi)有聚集索引時(shí)，成為【堆或堆表】
【堆】是一堆未加工的數(shù)據(jù)，以行標(biāo)識(shí)符作為指向存儲(chǔ)位置的指針。表數(shù)據(jù)沒(méi)有順序，也不能搜索，除非逐行遍歷。這個(gè)過(guò)程稱為【掃描】。當(dāng)存在聚集索引時(shí)，非聚集索引的指針由聚集索引所定義的值組成，所以聚集索引變得非常重要。
因?yàn)轫?yè)面大小固定，所以列越少，所能存儲(chǔ)的行就越多。由于非聚集索引通常不包含所有列，所以一般一個(gè)頁(yè)面包含有更多的非聚集索引。所以SQLServer能從一個(gè)非聚集索引的頁(yè)面中讀到比包含該列的表也頁(yè)面更多的值。
非聚集索引的另一個(gè)好處：獨(dú)立于數(shù)據(jù)表的結(jié)構(gòu)，可以放到不同的文件組，使用不同的I/O。
索引使用B-樹作為存儲(chǔ)結(jié)構(gòu)，所以查詢特定行所需的操作被最小化。

1.2、索引開銷：

索引過(guò)多會(huì)引起（INSERT/UPDATE/DELETE/CRUD中的CUD部分）花費(fèi)更長(zhǎng)的時(shí)間。
在設(shè)計(jì)索引時(shí)，要從兩個(gè)角度進(jìn)行：
對(duì)現(xiàn)有的生產(chǎn)系統(tǒng)，需要測(cè)量索引的總體影響，應(yīng)保證性能帶來(lái)的好處超過(guò)處理資源的額外成本?？梢允褂肞rofiler工具進(jìn)行整體工作負(fù)載優(yōu)化。
當(dāng)專注與索引立刻帶來(lái)的好處時(shí)，可以使用DMV查看：
Sys.dm_db_index_operational_stats或sys.dm_db_index_usage_stats
Sys.dm_db_index_operational_stats：顯示正在使用的一個(gè)索引的低級(jí)活動(dòng)，比如I/O和鎖。
Sys.dm_db_index_usage_stats：隨時(shí)發(fā)生咋一個(gè)索引中的各種操作的統(tǒng)計(jì)數(shù)字。
雖然對(duì)于DML，維護(hù)索引所需要的開銷會(huì)增加，但是，SQLServer在更新或刪除之前必須首先找到一行，所以索引對(duì)使用復(fù)雜的where子句的update和delete語(yǔ)句可能有幫助。

二、索引設(shè)計(jì)建議

索引設(shè)計(jì)建議如下：
l 檢查where子句和連接條件列；
l 使用窄索引；
l 檢查列的唯一性；
l 檢查列的數(shù)據(jù)類型；
l 考慮列順序；
l 考慮索引類型（聚集索引VS 非聚集索引）

2.1、檢查where子句和連接條件列：
當(dāng)一個(gè)查詢提交到SQLServer時(shí)，優(yōu)化器會(huì)做以下步驟：
1) 優(yōu)化器識(shí)別WHERE子句和連接條件中包含的列。
2) 接著優(yōu)化器檢查這些列上的索引。
3) 優(yōu)化器通過(guò)從索引上維護(hù)的統(tǒng)計(jì)確定子句的選擇性（也就是返回多少行）評(píng)估每個(gè)索引的有效性。
4) 最終，優(yōu)化器根據(jù)前面幾個(gè)步驟中的收集信息，估計(jì)讀取所限定的行開銷最低的方法。
當(dāng)沒(méi)有合適的where和連接列時(shí)，優(yōu)化器會(huì)做全表掃描。
建議：在where子句或連接條件中頻繁使用的列上建索引，以避免表掃描。當(dāng)一個(gè)表的數(shù)據(jù)總量非常小以至可以放入一個(gè)單獨(dú)的頁(yè)面（8KB）時(shí)，表掃描可能比索引查找工作得更好。

2.2、使用窄索引：
為了最好的性能，盡量在索引中使用較少的列。還應(yīng)當(dāng)避免寬數(shù)據(jù)類型的列。
窄索引可以在8KB的索引頁(yè)面中容納比寬索引更多的行，可以達(dá)到以下效果：
l 減少I/O數(shù)量（讀取更少的8KB頁(yè)面）
l 使用數(shù)據(jù)庫(kù)緩存更有效，因?yàn)镾QLServer可以緩存更少的索引頁(yè)面，減少內(nèi)存中索引頁(yè)面所需的邏輯讀操作。
l 減少數(shù)據(jù)庫(kù)存儲(chǔ)空間。

2.3、檢查列的唯一性：
在一個(gè)很小范圍的可能值的列（如性別）上創(chuàng)建索引對(duì)性能沒(méi)有好處。因?yàn)閮?yōu)化器不能使用索引有效地減少返回的行。因?yàn)樾》秶闹悼赡芤稹救頀呙琛炕蛘摺揪奂饕龗呙琛?。使where子句中的列具有大量的唯一行（或者高選擇性）以限制訪問(wèn)的行數(shù)始終是首選的方案。應(yīng)該在這些列上創(chuàng)建索引幫助訪問(wèn)小的結(jié)果集。
另外，對(duì)于創(chuàng)建在多個(gè)列上的索引時(shí)，順序是有關(guān)系的。在某些情況下，使用最有選擇性的列將是索引更有效。

2.4、檢查列數(shù)據(jù)類型：
對(duì)數(shù)值型建索引會(huì)很快，因?yàn)槌叽缧?，算術(shù)操縱很容易。但是字符型尺寸大，且需要字符串匹配操作，通常開銷更大。

2.5、考慮列順序：
復(fù)合索引中，列順序是索引效率的重要因素：
l 列唯一性；
l 列寬度；
l 列數(shù)據(jù)類型；
查詢利用了索引的前沿來(lái)執(zhí)行查找操作以檢索數(shù)據(jù)。把最有效的索引放到前沿，能盡快篩選數(shù)據(jù)。減少數(shù)據(jù)量。

2.6、考慮索引類型：
聚集索引和非聚集索引都以B-樹存儲(chǔ)數(shù)據(jù)。下面將詳細(xì)介紹

三、聚集索引（聚簇索引）

聚簇索引的葉子頁(yè)面和表的數(shù)據(jù)頁(yè)面相同。因此表行物理上按照聚簇索引列排序，因?yàn)閺奈锪ι现荒苡幸环N物理順序，所以只有一個(gè)聚簇索引。

3.1、堆表：
沒(méi)有聚簇索引的表叫堆表。數(shù)據(jù)列沒(méi)有任何順序，連接到表的相鄰頁(yè)面。與訪問(wèn)非堆表相比，無(wú)組織的結(jié)構(gòu)增大了訪問(wèn)的開銷。
3.2、與非聚簇索引的關(guān)系：
非聚簇索引的一個(gè)索引行包含指向表的對(duì)應(yīng)數(shù)據(jù)行的指針。這個(gè)指針被稱為【行定位器（row locator）】。它的值取決于數(shù)據(jù)頁(yè)是保存在堆當(dāng)中還是被聚合。對(duì)于非聚簇索引，行定位器指向堆中數(shù)據(jù)行的RID的指針。對(duì)于聚簇索引，行定位器是聚簇索引的索引鍵值。當(dāng)有新數(shù)據(jù)行進(jìn)入時(shí)，可能導(dǎo)致非聚簇索引重定位、分頁(yè)等等，影響性能。
3.3、聚簇索引建議：
1) 首先創(chuàng)建聚簇索引：
因?yàn)樗蟹蔷鄞厮饕谄渌饕猩媳４婢鄞厮饕I值，所以創(chuàng)建順序非常重要。為了最好的性能，建議在創(chuàng)建任何非聚簇索引前創(chuàng)建聚簇索引。
2) 保持窄索引：
應(yīng)保持聚簇索引總體的長(zhǎng)度盡可能小。因?yàn)榫鄞厮饕L(zhǎng)度太大，那么非聚簇索引也會(huì)跟著增大。因此，大的聚簇索引鍵值不僅影響本身寬度，而且擴(kuò)大表上的所有非聚簇索引，增加索引頁(yè)面數(shù)量，增加邏輯讀和磁盤I/O。
3) 一步重建聚簇索引：
由于聚簇索引和非聚簇索引關(guān)聯(lián)，所以使用DROP INDEX再CREATE INDEX將導(dǎo)致非聚簇索引建立兩次，此時(shí)可以使用CREATE INDEX 語(yǔ)句的DROP_EXISTING子句在一個(gè)單獨(dú)的原子步驟中重建聚簇索引，相似地可以在非聚簇索引中使用。
4) 何時(shí)使用一個(gè)聚簇索引：
a) 檢索一定范圍的數(shù)據(jù)：
由于聚簇索引是按物理順序建立，索引合理利用能減少磁頭的移動(dòng)，減少物理I/O量。
b) 讀取預(yù)先排序的數(shù)據(jù)：
對(duì)于需要排序的數(shù)據(jù)，聚簇索引非常有效，能減少數(shù)據(jù)讀取后的排序開銷。
對(duì)于讀取大范圍行和/或排序輸出的查詢，聚簇索引通常是比非聚簇索引更有效的選擇。
5) 何時(shí)不使用聚簇索引：
在某些情況下最好不要使用聚簇索引：
a) 頻繁更新的列：
如果列更新頻繁，將導(dǎo)致非聚簇索引重新定位，增加相關(guān)操作查詢的開銷。還將阻塞這段時(shí)間引用相同部分和非聚簇索引的其他查詢，從而影響數(shù)據(jù)并行性。
b) 寬的關(guān)鍵字：前面已經(jīng)說(shuō)明原因
c) 太多并行的順序插入：
如果想并行插入新行，那么把它們分布在多個(gè)頁(yè)面中會(huì)更好，有聚簇索引的話，所有插入都會(huì)集中在最后一頁(yè)，形成巨大的“熱點(diǎn)”，可以通過(guò)創(chuàng)建另一列上的索引（該索引不會(huì)將行按照新行相同的順序來(lái)排序）來(lái)將插入操作隨機(jī)分布在整個(gè)表，這個(gè)問(wèn)題只在大量的同時(shí)插入時(shí)發(fā)生。如果磁盤熱點(diǎn)成為性能瓶頸，那么可以通過(guò)降低表的填充因子來(lái)容納到中間頁(yè)面。這樣熱的頁(yè)面將在內(nèi)存中，也有利于性能。

四、非聚簇索引

非聚簇索引不影響表頁(yè)面中數(shù)據(jù)的順序，對(duì)于堆表，行定位器指向數(shù)據(jù)行的RID的指針。對(duì)于非堆表，指向聚簇索引的索引鍵。

4.1、非聚簇索引維護(hù)：
為優(yōu)化維護(hù)開銷，SQLServer添加一個(gè)指向舊數(shù)據(jù)頁(yè)的指針，以在頁(yè)面分割之后指向新的數(shù)據(jù)頁(yè)面，而不是更新所有相關(guān)非聚簇索引的行定位器。將聚簇索引作為行定位器降低了非聚簇索引相關(guān)的開銷。
4.2、定義書簽查找：
當(dāng)查詢請(qǐng)求不是優(yōu)化器選擇的非聚簇索引一部分時(shí)，需要一個(gè)查找，這對(duì)一個(gè)聚簇索引來(lái)說(shuō)是一個(gè)關(guān)鍵字查找，對(duì)堆表來(lái)說(shuō)是一個(gè)RID查找。成為：書簽查找。
這種查找根據(jù)索引行的行定位器值，從表中讀取對(duì)應(yīng)的數(shù)據(jù)行，除了索引頁(yè)面上的邏輯讀操作以外，還需要一個(gè)數(shù)據(jù)頁(yè)面的邏輯讀。但是如果查詢需要列中的索引，那么不需要訪問(wèn)數(shù)據(jù)頁(yè)面，這種叫做【覆蓋索引】，這些書簽查找是大結(jié)果集最好使用聚簇索引的原因。聚簇索引不需要書簽查找，因?yàn)槿~子頁(yè)面和數(shù)據(jù)頁(yè)面相同。
4.3、非聚簇索引建議：
1. 何時(shí)使用非聚簇索引：
在需要從一個(gè)大表中讀取少量行時(shí)最有效。隨著行數(shù)增多，書簽查找的開銷成比例增加。索引列應(yīng)該有很高的選擇性。
有一些索引需求不適合于聚簇索引：
l 頻繁更新的列
l 寬關(guān)鍵字
2. 何時(shí)不使用非聚簇索引：
非聚簇索引不適合檢索大量行的查詢。此時(shí)使用聚簇索引更好。因?yàn)椴恍枰獑为?dú)的書簽查找來(lái)檢索數(shù)據(jù)行。如果需要從表上讀取大量的結(jié)果集，那么在過(guò)濾和連接條件中的非聚簇索引沒(méi)有幫助，除非使用非聚簇索引——覆蓋索引。

五、聚簇索引VS 非聚簇索引

選擇聚簇索引或非聚簇索引主要考慮因素：
l 檢索的行數(shù)量；
l 數(shù)據(jù)排序需求；
l 索引鍵寬度；
l 列更新頻度；
l 書簽開銷；
l 任何磁盤熱點(diǎn)；

5.1、聚簇索引相對(duì)非聚簇索引的好處：
在沒(méi)有索引的表上選擇索引的類型時(shí)，聚簇索引通常是首選。
盡量使用具有高選擇性的列讀取小的結(jié)果集是該列上創(chuàng)建非聚簇索引很好的啟示，但在同意列上的聚簇索引可能同樣有利甚至更好。
注意：盡管許多數(shù)據(jù)檢索中聚簇索引勝過(guò)非聚簇索引，但是一個(gè)表只有一個(gè)聚簇索引，因此，應(yīng)當(dāng)將聚簇索引保留在最有力的情況下。
5.2、非聚簇索引相對(duì)聚簇索引的好處：
非聚簇索引在以下情況優(yōu)先于聚簇索引：
l 索引鍵尺寸很大。
l 為了避免聚簇索引重建時(shí)需要重建所有非聚簇索引的相關(guān)開銷。
l 是數(shù)據(jù)庫(kù)讀取程序工作于非聚簇索引頁(yè)面上，同時(shí)寫入程序?qū)?shù)據(jù)頁(yè)面中的其他列（不包括非聚簇索引中）進(jìn)行修改以避免阻塞。
l 當(dāng)查詢所有引用列（來(lái)自一個(gè)表）可以安全地容納非聚簇索引中時(shí)。
在不需要跳轉(zhuǎn)到數(shù)據(jù)行的情況下，非聚簇索引的性能應(yīng)該和聚簇索引一樣好（甚至更好）。非聚簇索引鍵包含所有表中需要的列是有可能的。

六、高級(jí)索引技術(shù)

l 覆蓋索引：
l 索引交叉：使用多個(gè)非聚簇索引以滿足查詢的所有列需求（來(lái)自一個(gè)表）
l 索引連接：使用索引交叉和覆蓋索引技術(shù)來(lái)避免觸及基本表。
l 過(guò)濾索引：為了能夠索引具有零散數(shù)據(jù)分布的字段或者稀疏的列，可以在索引上應(yīng)用過(guò)濾，這樣它只索引一些數(shù)據(jù)。
l 索引視圖：在磁盤上將視圖輸出實(shí)體化

6.1、覆蓋索引：
在所有為滿足SQL查詢不用到達(dá)基礎(chǔ)表所需的列上建立非聚簇索引。如果查詢遇到一個(gè)索引并且完全不需要引用底層數(shù)據(jù)表，那么該索引可以被認(rèn)為是覆蓋索引。使用INCLUDE操作符使索引編程覆蓋索引，浙江存儲(chǔ)數(shù)據(jù)和索引而不需要修改索引結(jié)構(gòu)本身。
覆蓋索引本身對(duì)于減少邏輯讀是一種游泳的技術(shù)。在以下情況使用最好：
l 你不希望增加索引鍵的大小，但仍然希望有一個(gè)覆蓋索引；
l 你打算索引一種不能被索引的數(shù)據(jù)類型（除了文本、ntext和圖像）；
l 你已經(jīng)超過(guò)了一個(gè)索引的關(guān)鍵字列的最大數(shù)量（但是最好避免這個(gè)問(wèn)題）。
1、偽聚簇索引（Pseudoclustered index）：
覆蓋索引物理上順序地組織所有索引列。從I/O角度看，沒(méi)有使用包含列的覆蓋索引編程一種聚簇索引，用于所有完全滿足于覆蓋索引中列的查詢。如果查詢結(jié)果集需要排序，那么覆蓋索引可以用于物理地按照結(jié)果集所需的順序維護(hù)列數(shù)據(jù)。
2、建議：
利用覆蓋索引，要注意SELECT語(yǔ)句中的列清單。應(yīng)盡可能使用較少的列來(lái)保持小的覆蓋索引鍵尺寸。如果索引中所有列的字節(jié)數(shù)相比表的單個(gè)數(shù)據(jù)行來(lái)說(shuō)較小，而且確定利用覆蓋索引的查詢經(jīng)常執(zhí)行，那么覆蓋索引是有效的。
在建立許多覆蓋索引之前，考慮SQLServer如何有效和自動(dòng)地使用索引交叉為查詢即時(shí)創(chuàng)建覆蓋索引。

6.2、索引交叉：
如果一個(gè)表有很多索引，那么SQLServer可以使用多個(gè)索引來(lái)執(zhí)行一個(gè)查詢。根據(jù)每個(gè)索引選擇小的數(shù)據(jù)子集，然后執(zhí)行兩個(gè)子集的交叉（即只返回滿足所有條件的那些行）
但在現(xiàn)實(shí)世界中，修改現(xiàn)有索引時(shí)要考慮以下問(wèn)題：
l 因?yàn)楦鞣N原因，可能不允許修改現(xiàn)有索引；
l 現(xiàn)有非聚簇索引鍵可能已經(jīng)相當(dāng)寬；
l 使用現(xiàn)有索引的查詢開銷將被這個(gè)修改所影響。
為了增進(jìn)一個(gè)查詢的性能，SQLServer可以在表上使用多個(gè)索引，因此，考慮創(chuàng)建多個(gè)窄索引代替寬的索引鍵。
有時(shí)候，可能必須為以下原因創(chuàng)建一個(gè)單獨(dú)的非聚簇索引：
l 重新排列現(xiàn)有索引中的列不被允許；
l 覆蓋索引所需要的一些列不能被包含在現(xiàn)有的非聚簇索引中；
l 兩個(gè)現(xiàn)有非聚簇索引中的總列數(shù)可能多余覆蓋索引所需要的列數(shù)；
在這些情況下，可以在剩下的列上創(chuàng)建非聚簇索引。

6.3、索引連接：
索引連接是索引交叉的變種，將覆蓋索引技術(shù)應(yīng)用到索引交叉。如果沒(méi)有單個(gè)覆蓋查詢的索引而多個(gè)索引一齊可以覆蓋該查詢。SQLServer可以使用索引連接完全滿足查詢而不需要轉(zhuǎn)到基本表。

6.4、過(guò)濾索引：
是使用過(guò)濾器的非聚簇索引，基本上上一個(gè)where子句。用倆在可能沒(méi)有很好選擇性的一個(gè)或多個(gè)列上創(chuàng)建一個(gè)高選擇性的關(guān)鍵字組。對(duì)于大量null值時(shí)比較適用。
過(guò)濾索引在許多方面帶來(lái)回報(bào)：
l 減少索引尺寸從而增進(jìn)查詢效率。
l 建立更小的索引降低存儲(chǔ)開銷；
l 因?yàn)槌叽鐪p少，降低了索引維護(hù)的成本。
過(guò)濾索引需要在訪問(wèn)或者創(chuàng)建時(shí)的一組特殊ANSI設(shè)置：
ON:ANSI_NULLS,ANSI_PADDING,ANSI_WARNINGS,ARITHABORT,CONCAT_NULL_YIELDS_NULL,QUOTED_IDENTIFIER
OFF:NUMERIC_ROUNDABORT

6.5、索引視圖：
SQLServer可以在視圖上創(chuàng)建唯一的聚簇索引來(lái)磁盤上實(shí)體化。這樣的索引成為索引視圖或?qū)嶓w化視圖。在創(chuàng)建以后可以創(chuàng)建非聚簇索引。
1、好處：
l 聚合可以預(yù)先計(jì)算并被保存在索引視圖中，以在查詢執(zhí)行期間最小化昂貴的計(jì)算；
l 表可以預(yù)先連接，結(jié)果集可以實(shí)物化；
l 連接或聚合的組成可以被實(shí)物化。

2、開銷：
l 基本表中的任何修改必須執(zhí)行事務(wù)的select語(yǔ)句反映到索引視圖中；
l 對(duì)索引視圖定義的基本表上的任何修改可能發(fā)起索引視圖的非聚簇索引中的修改，如果聚簇鍵被更新，聚簇索引也將必須更新；
l 索引視圖增加數(shù)據(jù)庫(kù)的維護(hù)開銷；
l 數(shù)據(jù)庫(kù)中需要更多的存儲(chǔ)；
創(chuàng)建索引視圖包括如下限制：
l 視圖的第一個(gè)索引必須是唯一聚簇索引。
l 索引視圖上的非聚簇索引只可以在唯一聚簇索引創(chuàng)建之后創(chuàng)建。
l 視圖定義必須是確定性的——即，它對(duì)一個(gè)給定的查詢只能返回一個(gè)可能的結(jié)果；
l 索引視圖必須只引用相同數(shù)據(jù)庫(kù)中的基本表，而不是其他視圖；
l 索引視圖可以包含浮點(diǎn)列但是這樣的列不能包含在聚簇索引鍵中；
l 索引視圖必須是綁定到列所引用表的一個(gè)架構(gòu)，以免表架構(gòu)的修改；
l 視圖定義的語(yǔ)法有很多限制
l 必須確定的SET選項(xiàng)列表：
ON:ARITHABORT,CONCAT_NULL_YIELDS_NULL,ANSI_NULLS,ANSI_PADDING和ANSI_WARNING
OFF:NUMERIC_ROUNDABORT

3、使用環(huán)境：
OLAP能從索引視圖中獲益，OLTP就比較難從中獲益。

6.6、索引壓縮：
從2008引入。壓縮索引能造成重大性能改進(jìn)，但是也會(huì)造成CPU和內(nèi)存開銷。不是適合所有索引的方案。
默認(rèn)情況下，索引不會(huì)被壓縮。必須明確地在創(chuàng)建索引時(shí)要求索引被壓縮。分為行級(jí)和頁(yè)級(jí)壓縮。索引中的非葉子頁(yè)面不接受頁(yè)面類型下的壓縮。

七、特殊索引類型

7.1、全文索引：
對(duì)文本型的字段索引
7.2、空間索引：
對(duì)于空間類型的數(shù)據(jù)進(jìn)行索引
7.3、XML：
從2005引入XML后，對(duì)XML類型

八、索引的附件特性

8.1、不同的列排序順序：
可對(duì)一個(gè)索引中的不同列進(jìn)行升降序排列。
8.2、在計(jì)算列上的索引：
可以在計(jì)算列上創(chuàng)建索引，只要計(jì)算列的表達(dá)式符合一定的限制，比如來(lái)源表是確定的。
8.3、BIT數(shù)據(jù)類型列上的索引：
創(chuàng)建在BIT數(shù)據(jù)列上的索引本身不是很好的優(yōu)點(diǎn)，但是對(duì)于覆蓋索引，當(dāng)涵蓋了BIT列時(shí)就很有用。
8.4、作為一個(gè)查詢處理的CREATE INDEX語(yǔ)句：

8.5、并行索引創(chuàng)建：
可以在max degree of parallelism配置參數(shù)來(lái)控制CREATE INDEX語(yǔ)句中的處理器數(shù)量，也可以使用exec sp_configure ‘maxdegree of parallelism'
8.6、在線索引創(chuàng)建：
可以在創(chuàng)建索引時(shí)減少鎖的機(jī)會(huì)。
8.7、考慮數(shù)據(jù)庫(kù)引擎調(diào)整顧問(wèn)

九、小結(jié)

為了決定特殊查詢的索引鍵列，需要評(píng)估查詢的WHERE子句和連接條件。像列選擇性、寬度、數(shù)據(jù)類型和列順序這些因素。因?yàn)樗饕饕菫榱藱z索少量行，所以索引選擇性必須非常高。
為了獲得更好性能，嘗試使用覆蓋索引完全覆蓋查詢。

SQL Server數(shù)據(jù)庫(kù)優(yōu)化其索引的小技巧

關(guān)于索引的常識(shí)：影響到數(shù)據(jù)庫(kù)性能的最大因素就是索引。由于該問(wèn)題的復(fù)雜性，我只可能簡(jiǎn)單的談?wù)勥@個(gè)問(wèn)題，不過(guò)關(guān)于這方面的問(wèn)題，目前有好幾本不錯(cuò)的書籍可供你參閱。我在這里只討論兩種SQL Server索引，即clustered索引和nonclustered索引。當(dāng)考察建立什么類型的索引時(shí)，你應(yīng)當(dāng)考慮數(shù)據(jù)類型和保存這些數(shù)據(jù)的column。同樣，你也必須考慮數(shù)據(jù)庫(kù)可能用到的查詢類型以及使用的最為頻繁的查詢類型。

索引的類型
如果column保存了高度相關(guān)的數(shù)據(jù)，并且常常被順序訪問(wèn)時(shí)，最好使用clustered索引，這是因?yàn)槿绻褂胏lustered索引，SQL Server會(huì)在物理上按升序（默認(rèn)）或者降序重排數(shù)據(jù)列，這樣就可以迅速的找到被查詢的數(shù)據(jù)。同樣，在搜尋控制在一定范圍內(nèi)的情況下，對(duì)這些column也最好使用clustered索引。這是因?yàn)橛捎谖锢砩现嘏艛?shù)據(jù)，每個(gè)表格上只有一個(gè)clustered索引。

與上面情況相反，如果columns包含的數(shù)據(jù)相關(guān)性較差，你可以使用nonculstered索引。你可以在一個(gè)表格中使用高達(dá)249個(gè)nonclustered索引——盡管我想象不出實(shí)際應(yīng)用場(chǎng)合會(huì)用的上這么多索引。

當(dāng)表格使用主關(guān)鍵字（primary keys），默認(rèn)情況下SQL Server會(huì)自動(dòng)對(duì)包含該關(guān)鍵字的column(s)建立一個(gè)獨(dú)有的cluster索引。很顯然，對(duì)這些column(s)建立獨(dú)有索引意味著主關(guān)鍵字的唯一性。當(dāng)建立外關(guān)鍵字（foreign key）關(guān)系時(shí)，如果你打算頻繁使用它，那么在外關(guān)鍵字cloumn上建立nonclustered索引不失為一個(gè)好的方法。如果表格有clustered索引，那么它用一個(gè)鏈表來(lái)維護(hù)數(shù)據(jù)頁(yè)之間的關(guān)系。相反，如果表格沒(méi)有clustered索引，SQL Server將在一個(gè)堆棧中保存數(shù)據(jù)頁(yè)。

數(shù)據(jù)頁(yè)
當(dāng)索引建立起來(lái)的時(shí)候，SQLServer就建立數(shù)據(jù)頁(yè)（datapage），數(shù)據(jù)頁(yè)是用以加速搜索的指針。當(dāng)索引建立起來(lái)的時(shí)候，其對(duì)應(yīng)的填充因子也即被設(shè)置。設(shè)置填充因子的目的是為了指示該索引中數(shù)據(jù)頁(yè)的百分比。隨著時(shí)間的推移，數(shù)據(jù)庫(kù)的更新會(huì)消耗掉已有的空閑空間，這就會(huì)導(dǎo)致頁(yè)被拆分。頁(yè)拆分的后果是降低了索引的性能，因而使用該索引的查詢會(huì)導(dǎo)致數(shù)據(jù)存儲(chǔ)的支離破碎。當(dāng)建立一個(gè)索引時(shí)，該索引的填充因子即被設(shè)置好了，因此填充因子不能動(dòng)態(tài)維護(hù)。

為了更新數(shù)據(jù)頁(yè)中的填充因子，我們可以停止舊有索引并重建索引，并重新設(shè)置填充因子（注意：這將影響到當(dāng)前數(shù)據(jù)庫(kù)的運(yùn)行，在重要場(chǎng)合請(qǐng)謹(jǐn)慎使用）。DBCC INDEXDEFRAG和DBCC DBREINDEX是清除clustered和nonculstered索引碎片的兩個(gè)命令。INDEXDEFRAG是一種在線操作（也就是說(shuō)，它不會(huì)阻塞其它表格動(dòng)作，如查詢），而DBREINDEX則在物理上重建索引。在絕大多數(shù)情況下，重建索引可以更好的消除碎片，但是這個(gè)優(yōu)點(diǎn)是以阻塞當(dāng)前發(fā)生在該索引所在表格上其它動(dòng)作為代價(jià)換取來(lái)得。當(dāng)出現(xiàn)較大的碎片索引時(shí)，INDEXDEFRAG會(huì)花上一段比較長(zhǎng)的時(shí)間，這是因?yàn)樵撁畹倪\(yùn)行是基于小的交互塊（transactional block）。

填充因子
當(dāng)你執(zhí)行上述措施中的任何一個(gè)，數(shù)據(jù)庫(kù)引擎可以更有效的返回編入索引的數(shù)據(jù)。關(guān)于填充因子（fillfactor）話題已經(jīng)超出了本文的范疇，不過(guò)我還是提醒你需要注意那些打算使用填充因子建立索引的表格。

在執(zhí)行查詢時(shí)，SQL Server動(dòng)態(tài)選擇使用哪個(gè)索引。為此，SQL Server根據(jù)每個(gè)索引上分布在該關(guān)鍵字上的統(tǒng)計(jì)量來(lái)決定使用哪個(gè)索引。值得注意的是，經(jīng)過(guò)日常的數(shù)據(jù)庫(kù)活動(dòng)（如插入、刪除和更新表格），SQL Server用到的這些統(tǒng)計(jì)量可能已經(jīng)“過(guò)期”了，需要更新。你可以通過(guò)執(zhí)行DBCC SHOWCONTIG來(lái)查看統(tǒng)計(jì)量的狀態(tài)。當(dāng)你認(rèn)為統(tǒng)計(jì)量已經(jīng)“過(guò)期”時(shí)，你可以執(zhí)行該表格的UPDATE STATISTICS命令，這樣SQL Server就刷新了關(guān)于該索引的信息了。

建立數(shù)據(jù)庫(kù)維護(hù)計(jì)劃
SQL Server提供了一種簡(jiǎn)化并自動(dòng)維護(hù)數(shù)據(jù)庫(kù)的工具。這個(gè)稱之為數(shù)據(jù)庫(kù)維護(hù)計(jì)劃向?qū)В―atabase Maintenance Plan Wizard ，DMPW）的工具也包括了對(duì)索引的優(yōu)化。如果你運(yùn)行這個(gè)向?qū)?，你?huì)看到關(guān)于數(shù)據(jù)庫(kù)中關(guān)于索引的統(tǒng)計(jì)量，這些統(tǒng)計(jì)量作為日志工作并定時(shí)更新，這樣就減輕了手工重建索引所帶來(lái)的工作量。如果你不想自動(dòng)定期刷新索引統(tǒng)計(jì)量，你還可以在DMPW中選擇重新組織數(shù)據(jù)和數(shù)據(jù)頁(yè)，這將停止舊有索引并按特定的填充因子重建索引。

您可能感興趣的文章:

MySQL數(shù)據(jù)庫(kù)優(yōu)化技術(shù)之配置技巧總結(jié)
Oracle數(shù)據(jù)庫(kù)中SQL語(yǔ)句的優(yōu)化技巧
檢測(cè)SqlServer數(shù)據(jù)庫(kù)是否能連接的小技巧
解析MySQL數(shù)據(jù)庫(kù)性能優(yōu)化的六大技巧
MySQL性能優(yōu)化的一些技巧幫助你的數(shù)據(jù)庫(kù)
數(shù)據(jù)庫(kù)之SQL技巧整理案例

標(biāo)簽：黃山濟(jì)寧中衛(wèi) 宜春金昌貸款群呼河源新余

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《SQL Server 數(shù)據(jù)庫(kù)索引其索引的小技巧》，本文關(guān)鍵詞 SQL,Server,數(shù)據(jù)庫(kù),索引,其,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題，煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無(wú)關(guān)。

濮阳杆衣贸易有限公司

SQL Server 數(shù)據(jù)庫(kù)索引其索引的小技巧