Sql-Server

MS SQL Server 2016 - 單分區表與多重分隔表

  • June 18, 2017

我們需要在 MS SQL Server 2016 數據庫中儲存 1-1.5 年的大量日誌。收入:每月 50 Gb 的日誌(每月約 5000 萬行),我們將所有數據儲存在 6 個月內,並為舊記錄縮減 50% 的數據。先決條件:

  • 要求最後 1 個月、第 2 個月和其他的不同索引。
  • 超過 2 個月的大型 XML/文本數據所需的數據壓縮。
  • 目前 - 超過 6 個月的數據所需的數據縮減(只需從表中刪除 50​​-60% 的大數據(XML/文本)記錄),但最好隻大量壓縮行而不刪除它。

主要案例:通過某些 ID 或字元串過濾器以 SQL IN 方式(有時 LIKE)搜尋 100-200(有時 1000)行。

存在兩種分區方法:

  • “舊式” - 每個時期的分隔表(我認為在我們的例子中沒有共同的視圖),以月份為例(Log_0117,Log_0217,…)。

    • 優點:完全獨立的管理和模式(用於進化)、獨立的索引和查詢/計劃記憶體,正如預期的那樣——更高的性能和效率、易於實現、更少的魔力和更可控的。
    • 缺點:多對多表(每個週期 8 個),複雜的查詢(尤其是跨週期查詢)。
  • “現代風格” - 分區表和索引

    • 優點:易於管理,所有期間只需一張表,理論上更簡單的查詢。
    • 缺點(正如我在一些文章中發現的那樣):對分區索引的要求很高(通常,所有索引都必須包含分區列(因此需要更多記憶體),此類索引需要在分區切換後重建,很難更改不同時期的架構/索引,更神奇,更不易控制。

我對社區的問題:

  • 它真的是內置分區表/索引使開發更簡單嗎?
  • 它現在在 MS SQL Server 2016 中是否真的有不便的限制(作為企業,或者一切都還不錯?

UPD1: 列儲存索引- @DavidBrowne 的另一個有趣選項,但如 MSDN 所述:

列儲存索引為使用全表掃描的查詢提供了高性能增益,但不適合搜尋數據、搜尋特定值的查詢。

不幸的是,我們主要在日誌中搜尋 100-200 條記錄,而不是計算複雜的統計數據,但我將其保存在腦海中以備將來使用,也許會有幫助。

UPD2:@DanGuzman,當談到一些要求時,我的意思是對齊索引 - https://technet.microsoft.com/en-us/library/ms187526(v=sql.105).aspx

對非聚集索引進行分區

對唯一的非聚集索引進行分區時,索引鍵必須包含分區列。在對非唯一、非聚集索引進行分區時,SQL Server 預設將分區列添加為索引的非鍵(包含)列,以確保索引與基表對齊。如果分區列已經存在於索引中,SQL Server 不會將其添加到索引中。

還強烈建議不要使用非對齊索引 1)分區大表 - 索引,2)分區對齊索引的性能影響

這意味著 - 如果我們想讓上個月的查詢更快,我們需要:1)通過這個索引覆蓋所有表(但它需要更多的磁碟/記憶體)或 2)為分區切換和索引重建組織長維護視窗.

它真的是內置分區表/索引使開發更簡單嗎?

與針對沒有視圖抽象的獨立表進行開發相比,分區表更容易查詢,因為分區對應用程序查詢是透明的。

分區表滑動視窗維護涉及分區TRUNCATE(或SWITCH在 SQL 2016 之前的世界中)加上一個分區函式SPLIT/MERGE。維護期間可以壓縮較舊的數據/索引分區。還應該在TRUNCATE或之後更新分區統計資訊SWITCH。與動態創建/刪除/壓縮單獨的表和索引的腳本相比,這些操作的 DDL 腳本很容易實現自動化,而且複雜度更低。

它現在在 MS SQL Server 2016 中是否真的有不便的限制(作為企業,或者一切都還不錯?

表分區的最大限制是分區列必須是所有唯一索引和約束的一部分。只要您計劃對錶進行分區的表符合這些注意事項,分區就可以在不更改應用程式碼的情況下提供許多可管理性優勢。請注意,Microsoft 建議盡可能使用分區表而不是分區視圖。從 SQL Server 2016 SP1 開始,標準版中提供了分區表。

列儲存壓縮通常提供比行儲存上的頁或行壓縮更高的壓縮。您還可以在分區列儲存上創建分區非聚集索引(壓縮或未壓縮),以提高返回相對較少行的查詢的性能。我建議您同時考慮表分區和列儲存。

我不確定我是否完全理解您為什麼只想在上個月加快查詢速度。我猜你只是不希望不經常使用的數據索引的空間成本。鑑於現在的儲存成本很低,我不會費心引入額外的複雜性來節省一些索引空間。我懷疑壓縮,尤其是列儲存,將消除跳過這些循環的需要。

如果您仍然不確定前進的道路,我建議您開發候選設計的原型,並選擇最能滿足您的需求且複雜性最低的一種。

引用自:https://dba.stackexchange.com/questions/176539