SQL Server 列儲存效能調優(翻譯)

標籤：

原文地址：http://social.technet.microsoft.com/wiki/contents/articles/4995.sql-server-columnstore-performance-tuning.aspx

SQL Server 的資料行存放區索引是SQL Server 2012 release版本新增的內容，用於提高資料倉儲的查詢效能，本篇文章闡述列儲存的效能調優。

　　資料行存放區索引效能的基本原則

　　在相同的硬碟和資料量時，列儲存能夠明顯提高部分查詢的速度。致使列儲存查詢效率高的因素有以下幾點：

　　1.  資料行存放區索引採用高度壓縮的形式來儲存資料，並且每一列都儲存到不同的頁分組上。由於資料倉儲中的大部分表包含30列以上，但是一般的查詢可能只需要5、6列，並且僅查詢展示的列需要從磁碟中讀取，這樣，採用列儲存會大大的減少資料倉儲的查詢I/O。對於查詢頻繁的列在記憶體中會進行緩衝，包含資料備份的聚集B-樹或堆經常用來構建列索引和執行大部分的查詢。在查詢的周期內，他會週期性釋放記憶體空間和資源。

      2.  批處理 是一種更高效、基於向量查詢並且運用資料行存放區索引的方法。一批是包含1000行的一個集合。相比於基於行的舊查詢方式，批處理能夠降低7%-40%的cup消耗。有效向量運算允許批處理降低基本過濾、運算式預估、執行計畫、聯集查詢的CPU花費。

      3.  塊分割能夠跳過大部分資料來加快資料掃描。在資料行存放區索引裡的每一部分達到1000行會單獨分割成一塊，每一塊都有儲存著該塊內每一列最大值、最小值的中繼資料。儲存引擎通過中繼資料來檢測過濾條件。如果引擎檢測到塊內沒有符合要求的資料，那麼它會跳過該塊資料，並且這塊資料不會進行磁碟的讀寫。

      4.  儲存引擎能夠在查詢之前去除部分資料，這樣能夠提高查詢的響應速度。

      SQL Server 已經完善了資料行存放區索引和批處理查詢這種模式。一個部分查詢可以執行為一個批處理查詢模式，一個標準行查詢模式或者一個混合了批處理和標準行查詢模式。擷取最佳效能的關鍵是確認查詢流程的大部分資料是在批處理模式。即使大批的查詢不能在批處理模式下執行，使用資料行存放區索引也能通過減少磁碟I/O，儲存引擎的預過濾來獲得重要的效能提升。

　　通過介面上的【顯示執行計畫】，將滑鼠放到消耗昂貴的掃描操作上，查看提示。它會顯示預估和實際執行模式是行模式或者批處理模式。

 

　　什麼情況下使用資料行存放區索引

　　1.  將資料行存放區索引僅應用到大資料量的表上。應將資料行存放區索引建立在資料倉儲的實際花費表上，而不是一個定義表。如果你有一個包含至少百萬級的資料量的定義表，那麼也可以考慮將資料行存放區索引應用到這個定義表上。

     2. 用資料行存放區索引包含表的每一列。如果不這樣做，查詢不在資料行存放區索引中的列將不會享受資料行存放區索引帶來的效率。

     3. 結構化查詢，避免關聯兩個或更多個大表，通過一個大的實際花費表關聯多個小的定義表來提高效率。

     什麼情況下不建議使用資料行存放區索引

     1. 避免直接的在資料行存放區索引的列上進行字串過濾和通過字串列進行表的關聯。對字串過濾不能使用列索引進行掃面，關聯欄位為字串不如使用數實值型別關聯更有效率。對數實值型別和時間類型的過濾都可以使用資料行存放區索引進行掃描，在實際環境的大表中可以考慮使用整形的編碼來代替字串類型，並將字串類型轉移到一個小的定義表，通過整形編碼進行關聯，這樣查詢效率會更高。

     2. 避免在資料行存放區索引的列上使用外串連(out join)，外串連不能應用查詢的批量處理，相反的，是通過低版本的行查詢進行處理。

     3. 避免在資料行存放區索引的列上使用不包含語句(not in)，原因同第2條。

     4. 避免在資料行存放區索引的列上使用Union All語句來關聯其他表。原因同上。

 

 由於英語能力有限，翻譯的語句可能存在歧義，如不理解，請閱讀原文。

SQL Server 列儲存效能調優(翻譯)