資料倉儲中如何使用索引

資料倉儲的索引是個棘手的問題。如果索引太多，資料插入很快但是查詢響應就會很慢。如果太多索引，資料匯入就很慢並且資料儲存空間更大，但是查詢響應更快。資料庫中索引的作用就是加快查詢速度，不論是傳統資料庫還是資料倉儲。尤其是對於大資料量的表以及設計表連線的複雜查詢。之前接觸資料倉儲比較少，這裡只是介紹一點小經驗。

當然，在建立資料倉儲索引的時候需要考慮一些引數比如資料倉儲型別、維度表和事實表大小、是否分割槽、是否AD hoc等等。這些引數決定了你的索引結構。本篇主要介紹如何對資料倉儲中的關係表建立索引，注意是在關聯式資料庫中的關係表，而不是SSAS中的資料表。

維度索引

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

維度表包含一個自然或者業務鍵（例如交易編碼或者ID），我們稱之為業務鍵是來自於業務系統的。儘管業務鍵可能不是唯一的，但是對於緩慢漸變的維度表而言，在標識列上建立索引是比較好的（如使用者ID等），如下圖：

使用者和產品的維度表中聚集索引建立在業務鍵上，通過這樣的索引，能強化查詢速度尤其是where語句中使用了這些鍵的。通常where 表示式中經常會使用這個鍵值來查詢維度資料。

通過業務鍵建立聚集索引可以避免鎖升級（例如，行鎖到表鎖，意圖排它到排它），因為在ETL過程中如果代理鍵上有非聚集索引並且所有的行都被新增到檔案末尾就有可能發生鎖升級，如果排它鎖從行鎖升級到表鎖，那麼就會引起其他讀取或者ETL或者通用操作的阻塞甚至死鎖，最終程式timeout。

在上圖中，Date維度和Time維度有沒外部的資料來源或者業務鍵。考慮使用YYYYMMDD 和HHMMSSSSS 格式作為兩個表的主鍵，並建立聚集索引。這個值保證了索引順序，在事實表中也簡化了範圍查詢，並且這個鍵值也包含了日期或者時間，不再需要具體時間。

對於大型的緩慢漸變維度表（例如這裡需要鍵入新的資料），或許可以建立一個由四部分組成的非聚集索引包括業務鍵、記錄開始時間、記錄結束時間和代理鍵。為了效率並且阻止儲存增大，使用Include來包含記錄結束時間和代理鍵，如下所示:

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

如果在維度表中有其他用於查詢、排序、分組的列，也可以建立非聚集索引，就如同你在事務性資料庫中一樣。如果在維度表中有一個嵌入層級，例如類-子類-產品ID的層級關係在產品維度表中，考慮在層次結構的鍵值上建立索引，會顯著提高資料查詢並且不會影響資料匯入。

在事實表上建立索引

與在維度表建索引相似，當然需要考慮分割槽等條件。可以在日期列或者混合日期+時間的列上建立聚集索引。因為BI分析總是會使用日期/時間元件，事實表包含date或者datetime列，並且這裡使用聚集索引會幫助構建cube。也因為這個原因，資料記錄也是按照date或者datetime的順序儲存。對於歷史的查詢是有其優勢的。如果事實表有多個這樣的列，那就需要在查詢或者構建cube最為頻繁的列上建立索引。

如果在date列上分割槽，可以使用聚集索引在該列上。當發現用來建立分割槽和聚集索引在同一列上並且在儲存分割槽事實表的檔案組上建立了索引，那麼SQLServer 將自動用事實表分割槽來分割槽索引（例如，索引會有和事實表相同的的分割槽函式和列）。當索引按照事實表分割槽後，這個表和他的索引自動對齊，尤其當你建立分割槽或者頻繁切換分割槽開關時，這樣就方便的多了。

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

改善索引架構

隨著時間變化，資料倉儲會發生改變來適應組織結構的變化，並且必須要改變索引結構。大多數資料倉儲或者BI系統是直接連線關係表的，因此可以使用經過關係表調優的方法進行索引修改，例如評估查詢和資料混合來相應地調整索引。如果關係資料倉儲只用來表現SSAS結構，那麼可能不需要我們之前討論的索引。SSAS更傾向於反覆使用相同的查詢，因此可以使用索引優化嚮導或者對查詢進行精確調優。開始單純嚴謹徹底地評估以便在資料倉儲中建立索引。

總結

本篇只是簡單介紹了一般資料倉儲的關係資料表如何建立索引，但是很多時候要根據實際請款來建立索引，甚至有時候不能使用索引。兼顧消耗和時間效率等多個方面，還是要不斷通過生產環境的要求來變化的。