[[169896]]

數(shù)據(jù)倉庫中如何使用索引

數(shù)據(jù)倉庫的索引是個棘手的問題。如果索引太多，數(shù)據(jù)插入很快但是查詢響應就會很慢。如果太多索引，數(shù)據(jù)導入就很慢并且數(shù)據(jù)存儲空間更大，但是查詢響應更快。數(shù)據(jù)庫中索引的作用就是加快查詢速度，不論是傳統(tǒng)數(shù)據(jù)庫還是數(shù)據(jù)倉庫。尤其是對于大數(shù)據(jù)量的表以及設計表連接的復雜查詢。之前接觸數(shù)據(jù)倉庫比較少，這里只是介紹一點小經(jīng)驗。

當然，在創(chuàng)建數(shù)據(jù)倉庫索引的時候需要考慮一些參數(shù)比如數(shù)據(jù)倉庫類型、維度表和事實表大小、是否分區(qū)、是否AD hoc等等。這些參數(shù)決定了你的索引結構。本篇主要介紹如何對數(shù)據(jù)倉庫中的關系表建立索引，注意是在關系數(shù)據(jù)庫中的關系表，而不是SSAS中的數(shù)據(jù)表。

維度索引

如果打算在維度表的主鍵上建立索引，而該鍵是一個代理鍵，不是一個自然或者業(yè)務鍵(例如用戶名稱或者ID)。注意不要在維度表的代理鍵或者變現(xiàn)漸變的列上建立聚集索引。

維度表包含一個自然或者業(yè)務鍵(例如交易編碼或者ID)，我們稱之為業(yè)務鍵是來自于業(yè)務系統(tǒng)的。盡管業(yè)務鍵可能不是***的，但是對于緩慢漸變的維度表而言，在標識列上建立索引是比較好的(如用戶ID等)，如下圖：

用戶和產(chǎn)品的維度表中聚集索引建立在業(yè)務鍵上，通過這樣的索引，能強化查詢速度尤其是where語句中使用了這些鍵的。通常where 表達式中經(jīng)常會使用這個鍵值來查詢維度數(shù)據(jù)。

通過業(yè)務鍵建立聚集索引可以避免鎖升級(例如，行鎖到表鎖，意圖排它到排它)，因為在ETL過程中如果代理鍵上有非聚集索引并且所有的行都被添加到文件末尾就有可能發(fā)生鎖升級，如果排它鎖從行鎖升級到表鎖，那么就會引起其他讀取或者ETL或者通用操作的阻塞甚至死鎖，最終程序timeout。

在上圖中，Date維度和Time維度有沒外部的數(shù)據(jù)源或者業(yè)務鍵?？紤]使用YYYYMMDD 和HHMMSSSSS 格式作為兩個表的主鍵，并建立聚集索引。這個值保證了索引順序，在事實表中也簡化了范圍查詢，并且這個鍵值也包含了日期或者時間，不再需要具體時間。

對于大型的緩慢漸變維度表(例如這里需要鍵入新的數(shù)據(jù))，或許可以創(chuàng)建一個由四部分組成的非聚集索引包括業(yè)務鍵、記錄開始時間、記錄結束時間和代理鍵。為了效率并且阻止存儲增大，使用Include來包含記錄結束時間和代理鍵，如下所示:

3CREATE NONCLUSTERED INDEX MyDim_CoveringIndex ON (NaturalKEY, RecordStartDate) 
 
INCLUDE ( RecordEndDate, SurrogateKEY); 

這個索引在ETL的過程中對于歷史數(shù)據(jù)的查詢和操作是很有效的，通過非聚集索引減少列從而減少了沒必要的存儲空間。關系數(shù)據(jù)庫引擎能直接從索引獲取數(shù)據(jù)而不需要直接訪問維度數(shù)據(jù)，減少了IO提高了查詢速度。

如果在維度表中有其他用于查詢、排序、分組的列，也可以創(chuàng)建非聚集索引，就如同你在事務性數(shù)據(jù)庫中一樣。如果在維度表中有一個嵌入層級，例如類-子類-產(chǎn)品ID的層級關系在產(chǎn)品維度表中，考慮在層次結構的鍵值上建立索引，會顯著提高數(shù)據(jù)查詢并且不會影響數(shù)據(jù)導入。

在事實表上建立索引

與在維度表建索引相似，當然需要考慮分區(qū)等條件?？梢栽谌掌诹谢蛘呋旌先掌?時間的列上建立聚集索引。因為BI分析總是會使用日期/時間組件，事實表包含date或者datetime列，并且這里使用聚集索引會幫助構建cube。也因為這個原因，數(shù)據(jù)記錄也是按照date或者datetime的順序存儲。對于歷史的查詢是有其優(yōu)勢的。如果事實表有多個這樣的列，那就需要在查詢或者構建cube最為頻繁的列上建立索引。

如果在date列上分區(qū)，可以使用聚集索引在該列上。當發(fā)現(xiàn)用來創(chuàng)建分區(qū)和聚集索引在同一列上并且在保存分區(qū)事實表的文件組上創(chuàng)建了索引，那么SQLServer 將自動用事實表分區(qū)來分區(qū)索引(例如，索引會有和事實表相同的的分區(qū)函數(shù)和列)。當索引按照事實表分區(qū)后，這個表和他的索引自動對齊，尤其當你創(chuàng)建分區(qū)或者頻繁切換分區(qū)開關時，這樣就方便的多了。

下一步，創(chuàng)建非聚集索引在每個事實表的外鍵上，并且考慮混合外鍵和日期鍵，如圖1所示可以見建立類似用CustomerKEY + DateKEY 的索引。使用相同的外鍵值查詢將帶有時間排序，這回提高查詢速度。注意，處理外鍵時要考慮保持關系完整性。

改善索引架構

隨著時間變化，數(shù)據(jù)倉庫會發(fā)生改變來適應組織結構的變化，并且必須要改變索引結構。大多數(shù)數(shù)據(jù)倉庫或者BI系統(tǒng)是直接連接關系表的，因此可以使用經(jīng)過關系表調(diào)優(yōu)的方法進行索引修改，例如評估查詢和數(shù)據(jù)混合來相應地調(diào)整索引。如果關系數(shù)據(jù)倉庫只用來表現(xiàn)SSAS結構，那么可能不需要我們之前討論的索引。SSAS更傾向于反復使用相同的查詢，因此可以使用索引優(yōu)化向?qū)Щ蛘邔Σ樵冞M行精確調(diào)優(yōu)。開始單純嚴謹徹底地評估以便在數(shù)據(jù)倉庫中建立索引。

總結

本篇只是簡單介紹了一般數(shù)據(jù)倉庫的關系數(shù)據(jù)表如何建立索引，但是很多時候要根據(jù)實際請款來建立索引，甚至有時候不能使用索引。兼顧消耗和時間效率等多個方面，還是要不斷通過生產(chǎn)環(huán)境的要求來變化的。