本文章是由機器翻譯。

SQL Server

Don Mackenzie

業務系統通常包括具有挑戰性的報告要求。企業用戶需要訪問歷史交易記錄和當前的活動。他還需要用很多方式查看資料。使用者將如作出具體要求：

• 按月由客戶去年美元
• 單位產品的最後六個月一周
• 單位和美元的訂單和產品 （包括今天和現在） 在過去的 10 天

滿足這些不同的請求可以是系統設計師，尤其對於高交易量與企業面臨的一個挑戰。考克斯數位解決方案 （CDS） 是一個很好的例子。該公司處理業務量約達 20000 次每秒。

CD 我系統設計支援向上到小時報告一年的歷史在一個SQL Server資料庫表的更新每 10 分鐘一班。溶液共混物兩個SQL Server功能 — — 分區和 columnstore 索引 — — 以實現此回應時間查詢大量資料時。

第一印象

CD 提供的互聯網廣告服務。由廣告支援的"自由"的電視節目和"自由"的 Web 網站支援的顯示這些頁面上的廣告。CDS 的説明網站出版商正確顯示廣告。該公司記錄了每個顯示幕 （稱為印象） 每個廣告的資訊。它還記錄其他資訊 （如點擊有關的廣告。每一天，這將生成近 20 億的記錄 （1.5 tb 壓縮後容量）。

每個印象或交易記錄，包括許多元件。有兩個客戶 — — 廣告商和網站出版商。它還涉及的產品，就是廣告。廣告客戶下訂單，因此也是訂單 id 作為記錄的一部分。從廣告客戶收集的價格，並向出版商支付的價格。交易記錄包含很多欄位，反映出許多小的細節，對這項交易。

超過一年的交易被歸檔，以便使用者可以比較本個月至一年以前或感恩節 （一家大型廣告天） 今年到去年。CDS 會生成報告，帳單廣告商和出版商支付。這些報告還説明工作人員監控廣告訂單交貨，他們説明監察成效，他們的廣告，廣告商和出版商可以跟蹤活動和收入。

企業用戶往往不能等待每個新的一天的資料。由於廣告顯示了一整天，他們會讓調整和更新。這導致了我將描述在這裡，你能適應那裡的新的連續資料流程被添加到已經很龐大的資料集的許多應用程式的設計。

資料超載

你不會希望把 5000 億行 （和解壓縮的 500 TB）SQL Server表中。原始交易記錄都保存在 Hadoop 分散式檔案系統 (HDFS)。Hadoop 是一個好的工具，為存儲和分析大量的資料，但有可怕的回應時間 （瞭解更多在 hadoop.apache.org) 查詢。

對此資料的查詢需要五分鐘至 20 小時，當然，取決於資料量、 時間視窗和查詢的複雜性。直接對 HDFS 的查詢是為 Web 應用程式使用者的速度太慢。

大多數使用者需要可以會見聚合資料。總結按小時 （和廣告、 網站、 廣告和其他關鍵屬性） 交易並將其放置在SQL Server資料庫表中。時間很重要，因為廣告活動是完全不同于 2 上午比中午 此過程概述了向 50,000 行每小時 7000 萬交易。

這些行放在一個名為收入的SQL Server表中。一個互動式的 Web 應用程式使使用者可以查詢此表來檢查對性能的一項命令，看到一則廣告在不同網站上的表現如何，審查預算，等等。更新此表，一小時資料 （50,000 行） 取代每 10 分鐘一班。

將一個SQL Server表分區

SQL Server2005年引入了對分區表，一直在進行後續版本好級別支援。分區將一個大表分成幾個 （或多個） 的規模較小的內部表。SQL Server存儲和索引的每個小表或分區，分別 （見圖 A)。

圖SQL Server中的分區的表

分區函數 （本質上的不同的磁碟分割的邊界值的清單） 告訴SQL Server如何將表劃分為分區。經常是 datetime 列 （如我收入表），可用於分離的分區。表每月分區的資料超過一個月大、 日常分區為較新的資料超過一個星期歲，並且每小時對非常最近的資料分區。所有這些小的分區有一個單個表的名字，一個單一的架構和似乎是一個大表到應用程式。

當SQL Server處理查詢的 WHERE 子句包括日期列，它確定哪些分區不需要，而忽略其餘。所有查詢的 WHERE 子句中包括的日期。這可以提高查詢性能，通過限制到一個或多個較小的資料存儲的查詢。

舉辦一個大型的資料庫表

這仍然是一個巨大的表，有 5 億行和消費 60 GB 的存儲空間。那麼大一個普通表將會緩慢更新和慢的查詢。添加索引可以説明，但是他們進一步使更新變慢。當表變得更大，它能產生倍數層的索引 （索引深度），從而降低了他們樂於助人。

多維 OLAP 多維資料集是一種方法查詢大量的資料，但他們需要大量的設計和部署之前規劃。並在部署之後，您將無法更新它們。每天，或甚至更少，通常都會重新生成 OLAP 多維資料集。

我的方法來管理這個龐大的表相結合兩種技術：分區和 columnstore 索引。Columnstore 索引表SQL Server2012年中的一大局限是，你不能更新這些索引處於活動狀態時。你必須禁用索引更新的過程中，完全更新後重建它。這是一個繁瑣的過程，當處理大型表格。我將描述為克服這種限制的技術。在SQL Server2014 （請參見"分區SQL Server表"） 已經放鬆這些限制。

各分區大小 — — 月和天和小時 — — 匹配的企業用戶常見的查詢模式。它們也符合常見的更新和維護模式。您的應用程式可能需要不同的分區大小和"日期"是最有用的分區以外的值。在其他應用程式中，可能是更好地保持只是每小時或每天的分區。SQL Server2012年支援高達 15,000 的分區，每個表，每小時分區允許為 20 個月。在SQL Server文檔，請參閱 bit.ly/1mtZkfl 的分區更權威、 更深入的解釋。

Columnstore 索引

最令人興奮的新功能SQL Server2012 介紹是 columnstore 指標。他們是強大的工具，用於提高查詢性能。對於像我這樣的大規模表，見過 100 倍的性能提高。

在SQL Server2012年中的嚴重問題是 columnstore 索引的表，像一個 OLAP 多維資料集為唯讀。您將無法更新它沒有滴 （或至少禁用） 索引和重建後的更新。經過驗證的性能改進，我尋找一種方式來克服的唯讀模式的局限性和發現分區是答案。

雖然我認為作為進 （基於時間在我的例子） 的水準切片切片表分區的我想按列成垂直的薄片切片表 columnstore 指標。像所有的非叢集索引，該索引從主表資料分開存儲。每個 columnstore 索引的列單獨存儲在其自己的內部的 blob 容器中。這是相當不同的從具有多個索引或包含列的複合索引在一起。

因為基礎資料表為唯讀模式，columnstore blob 中的資料不需要處理更新，他們可以壓縮使用由SQL Server選擇的幾種演算法之一來匹配的資料。所以，索引通常是大大小於原始資料 （請參閱圖 1)。

圖 1 使用 Columnstore 索引來管理大型資料存儲區

我的 columnstore 指數包括表中的每一列。每個列分別存儲。引用只有幾列的查詢只會看那些列，而忽略其餘。查詢不需要讀取基表中的行。所有資料值在索引中，每個索引中的列的資料壓縮以減少磁片讀取。

記住我的表也被分區，所以每個分區和分區中的每一列有其自己的存儲空間 （見圖 2)。SQL Server是聰明到可以只閱讀它需要 （分區和列） 的一個特定的查詢與令人印象深刻的查詢回應時間的改進相比，掃描"正常"的表行的行，或甚至通過正常指標所需的資料檢索的資料。

圖 2 使用分區和 Columnstore 索引來解析資料

一個重要的積極副作用的這一切都是有非正常化表沒有性能損失。前執行遭受因為針對收入表通常包含前奏的查詢聯接到其他表以獲得次要屬性。在那個時候，想要保持苗條的表中的行，並避免不必要的資料從磁片中讀取，每次有人問，這張大桌子。

有時聯接導致的要做一個全資料表掃描的查詢中，尤其是當輔助屬性是在 where 子句中的SQL Server。現在，許多這些次要 （和一些專上） 的屬性存儲在寬得多的收入表知道使用 columnstore 索引，他們不會被訪問，除非他們需要由該特定的查詢。我已經設計了收入表有 25 列。在SQL Server文檔，請參閱 bit.ly/1zbsju1 columnstore 指標更權威、 更深入的瞭解。

更新

分區和 columnstore 索引的組合已經大規模表分成便於管理的中等規模的網段。然而，由於 columnstore 指數，更新在SQL Server2012年禁止。 儘管每個分區單獨存儲的它們都視為一個表中，所以禁用、 刪除或重新生成索引涉及整個表。重建 columnstore 指數可要花上一個半小時，在那段時間，這個桌子還沒有可用於查詢。表需要每隔 10 分鐘用新資料更新，並且可用的時間。

解決這一問題的關鍵是分區相關的語句：更改表......交換器分區...此 DDL 語句將資料分區從一個表移到另一個。它不會複製資料，但只是重新排列內部架構資訊，所以用現在屬於一個表分區存儲屬於一個不同的表。有一些規則這一進程，但它們是可控。TechNet 庫閱讀文章，"傳輸資料有效地通過使用分區切換"（bit.ly/1ts04Xv)，有關的分區切換徹底說明。

讓我們再次回到原來的問題。還有大量湧入的資料 （業務量達 20000 次/秒） 從廣告服務機器去 Hadoop 24 x 7。我可以使用 Hadoop 來匯總當前小時資料到約 50,000SQL Server行那一個小時。Hadoop 查詢需要大約 5 分鐘。我不能更新我主的收入表，但我可以把那些行入新表中 （分區和 columnstore 索引），我打電話給 RevenueIn。它具有相同的架構作為收入表中，但它是空的禁用其 columnstore 索引，這樣我就可以插入行。

插入後小時行，我可以重建 columnstore RevenueIn 表上的索引。一個小時的資料精確的吻合到一個分區。所以，少於一分鐘，我只索引 50,000 行。現在我可以使用交換器分區移動那個分區，已建立了索引，撥入政府的主要收入表和它的立即可用於報告查詢。注意交換器分區語句僅適用是否目標分區為空。為了解決這個問題，我使用了稱為 RevenueOut，它是空的第三個表。

現有的資料，從收入分區切換到 RevenueOut （保留收入分區為空），然後將 RevenueIn 分區 （包含新的資料和索引） 切換到收入。兩個 SWITCH 語句在我的環境中採取小於 5 毫秒。RevenueOut 中的舊資料然後被截斷 （請參見圖 3)。這是一個變體所述的滑動視窗劃分問題 bit.ly/1wgPVkR。

圖 3 刷新分區中的資料

詳細資訊詳細資訊

總是有詳細資訊。應用程式重複此過程六次或以上每小時 （一樣快，可以運行 Hadoop 查詢）。早在小時，有只有幾分鐘的資料，每次更新時替換為新的資料。不久後的小時頂部，還有了整整一個小時的巨大的收入表中的資料，它可以在下一小時開始。使用者在他的指尖是容易和快速查詢到的一個表中有奢侈的新鮮的、 當前的資料和超過一年的歷史。

開發商也贏得與單個資料庫表。以前的解決方案用於"當前"資料，另一個用於歷史資料的一個表。C# 代碼必須決定哪些表使用並結合資料庫查詢的結果，如果使用兩個表的查詢。單個表和聯接 （因為由 columnstore 索引啟用而不受處罰的非正常化的列） 的最小使用簡化了資料庫查詢以用於報告。

另一個細節合併。我把較舊的資料在每月的分區。這簡化了維護，因為我可以丟棄超出了要求一個月。更少的分區還簡化SQL Server內部的邏輯，來選擇分區的查詢，因為大多數查詢為舊資料包括整月。Columnstore 指數也獲得了一些具有較大的分區，由於其壓縮演算法的效率­模擬。我用一種類似于插入資料匯總小分區成大分區沒有重建資料。

還有一些SQL Server水暖涉及。在一篇網上文章 msdn.microsoft.com/magazine/dn800596 為所有必需的SQL Server資料庫物件提供了分步說明和示例的 T-SQL。看看那篇文章，從那裡複製代碼。

Don Mackenzie 是軟體體系結構在考克斯數位化解決方案，考克斯傳媒集團與考克斯企業互聯網廣告子公司董事。他喜歡將新技術應用到商務應用程式。聯繫到他在 don@coxds.com。

感謝以下的微軟技術專家對本文的審閱：萊姆斯 Rusanu