Sql-Server

分區查詢

  • May 5, 2019

我有幾個關於分區時表的物理佈局的問題。我一直在研究這個，但仍然有點不確定。

假設我有一個現有的表：-

   CREATE TABLE dbo.[ExampleTable]
    (ID INT IDENTITY(1,1),
     Col1 SYSNAME,
     Col2 SYSNAME,
     CreatedDATE DATE) ON [DATA];

    ALTER TABLE dbo.[ExampleData] ADD CONSTRAINT [PK_ExampleTable] PRIMARY KEY CLUSTERED
    ( [ID] ASC )
    GO

我想在 CreatedDate 列上對這個表進行分區（在這個例子中，所有分區都在同一個文件組中），但是我不能把這個列作為主鍵。所以我將 CreatedDate 列添加到主鍵：-

   ALTER TABLE dbo.[ExampleTable] DROP CONSTRAINT PRIMARY KEY

   ALTER TABLE dbo.[ExampleTable] ADD CONSTRAINT [PK_ExampleTable] PRIMARY KEY CLUSTERED
   ( [ID] ASC, [CreatedDate] ASC ) ON PartitionScheme(CreatedDate)
   GO

我的問題是如何對數據進行排序？數據是否會按 CreatedDate 列物理拆分為分區，然後按 ID 列排序？還是分區是邏輯的並且數據仍然按 ID 列排序？

另外，如果 ID 列是 GUID 會發生什麼？數據是否會在分區中，然後在這些分區中嚴重碎片化？

任何建議將不勝感激，謝謝。

安德魯

編輯：- 添加分區方案和功能：-

   DECLARE @CurrentDate DATETIME;

   CREATE PARTITION FUNCTION PF_Example (DATETIME)
   AS RANGE RIGHT 
   FOR VALUES (@CurrentDate+7,@CurrentDate+6,@CurrentDate+5,@CurrentDate+4,
               @CurrentDate+3,@CurrentDate+2,@CurrentDate+1,@CurrentDate,
               @CurrentDate-1,@CurrentDate-2,@CurrentDate-3,@CurrentDate-4,
               @CurrentDate-5,@CurrentDate-6,@CurrentDate-7,@CurrentDate-8);

  CREATE PARTITION SCHEME PS_Example
  AS PARTITION PF_Example
  ALL TO (Data);

好的，這裡有一個簡單的範例來說明原因 - 在您的大多數操作（報告查詢、歸檔操作、分區切換等）將按日期辨識行範圍的情況下 - 您最好在分區列上進行分群。讓我們有一個簡單的基於日期的分區方案和函式：

CREATE PARTITION FUNCTION DateRange (DATE)
AS RANGE RIGHT FOR VALUES ('20150101');
GO

CREATE PARTITION SCHEME DateRangeScheme
AS PARTITION DateRange ALL TO ([PRIMARY]);
GO

然後是兩個表 - 一個在 ID、Date 上具有聚群 PK，在 Date 上具有非聚群索引，另一個在 ID、Date 上具有非聚群 PK，在 Date 上具有聚群索引。

CREATE TABLE dbo.PKClustered
(
 ID INT, 
 dt DATE, 
 filler CHAR(4000)
  CONSTRAINT df_filler_c DEFAULT '' NOT NULL, 
 CONSTRAINT pk_clust PRIMARY KEY CLUSTERED (ID,dt)
);
CREATE INDEX dt ON dbo.PKClustered(dt) ON DateRangeScheme(dt);

CREATE TABLE dbo.PKNonClustered
(
 ID INT, 
 dt DATE, 
 filler CHAR(4000)
  CONSTRAINT df_filler_nc DEFAULT '' NOT NULL, 
 CONSTRAINT pk_nonclust PRIMARY KEY NONCLUSTERED (ID,dt)
);
CREATE CLUSTERED INDEX dt ON dbo.PKNonClustered(dt) ON DateRangeScheme(dt);

現在用一些數據填充它們：

INSERT dbo.PKClustered(ID, dt) SELECT TOP (100) Number, '20141231'
 FROM master.dbo.spt_values WHERE [type] = N'P' ORDER BY Number;

INSERT dbo.PKClustered(ID, dt) SELECT TOP (50) Number, '20150101'
 FROM master.dbo.spt_values WHERE [type] = N'P' ORDER BY Number DESC;

INSERT dbo.PKNonClustered(ID, dt) SELECT ID, dt FROM dbo.PKClustered;

所以我們應該在分區 1 中有 100 行，在分區 2 中有 50 行，對吧？sys.partitions確認：

SELECT [table] = o.name, [index] = i.name, 
 p.partition_number, p.[rows]
FROM sys.tables AS o
INNER JOIN sys.indexes AS i
ON o.[object_id] = i.[object_id]
INNER JOIN sys.partitions AS p
ON i.[object_id] = p.[object_id]
AND i.index_id = p.index_id
WHERE o.name LIKE N'PK%Clustered'
ORDER BY o.name, i.name;

結果：

在此處輸入圖像描述

請注意，在這兩種情況下，PK 中的數據都儲存在單個分區中。這對查詢有何影響？好吧，考慮這四個，它們可能是典型的（除了SELECT *，僅用於簡潔）：

SELECT * FROM dbo.PKClustered WHERE dt >= '20150101';
SELECT * FROM dbo.PKNonClustered WHERE dt >= '20150101';

DELETE dbo.PKClustered WHERE dt >= '20140101' AND dt < '20150101';
DELETE dbo.PKNonClustered WHERE dt >= '20140101' AND dt < '20150101';

以下是SQL Sentry Plan Explorer的一些結果：*

估計成本和實際執行時指標：

在此處輸入圖像描述

針對SELECT *非聚集 PK 執行有效的聚集索引查找，僅訪問單個分區：

在此處輸入圖像描述

在此處輸入圖像描述

當 PK 被聚集時，它決定改為執行聚集索引掃描，這意味著它無法消除分區，從而導致更多的讀取，從而導致更高的 I/O 成本。有趣的是，還沒有訂購掃描。

在此處輸入圖像描述

在此處輸入圖像描述

刪除也會發生類似的事情。在這兩種情況下，刪除操作中最昂貴的部分是聚集索引刪除；具有分區消除的好處使得非集群 PK 更適合支持此操作（即使最終所需的讀取和 up 大致相同）。

在此處輸入圖像描述

在此處輸入圖像描述

使用集群 PK 時，可以通過查找找到源行（您可能希望它更有效），但是大部分工作還是由後續刪除執行的，因此至少在這個大小下它不會產生太大影響全部：

在此處輸入圖像描述

在此處輸入圖像描述

現在，在更高的音量下，領先的掃描可能會導致天平向另一個方向傾斜，因此您將不得不進行測試。

當然，在這個低端，這對您通過 ID 辨識的單行查詢有負面影響，因為您通常會通過索引查找來辨識行，然後必須進行查找，而不是單個聚集索引查找。讓我們考慮這兩個查詢（同樣，關於SELECT *, 照我說的做，而不是照我做的）：

SELECT * FROM dbo.PKClustered WHERE ID = 2045;
SELECT * FROM dbo.PKNonClustered WHERE ID = 2045;

計劃資源管理器的結果：

在此處輸入圖像描述

第一個很簡單，它只需要一個聚集索引查找（因此不需要查找）：

在此處輸入圖像描述

但如前所述，第二個決定對 PK 進行非分區查找，而是分區鍵查找。在這種情況下，最終會變得更加昂貴，但可能並不總是，也可能並不總是優化器的選擇。

在此處輸入圖像描述

某些連接查詢可能會發生同樣的事情，具體取決於行數和連接的構造方式。

再一次，優化器在這裡的選擇通常是依賴於體積的。所以，最後：這取決於. 根據您提供的資訊，我的選擇是在分區鍵上集群並使用非集群 PK。在任何一種情況下，我都會強烈避免為這個 ID 使用 GUID——雖然如果你試圖每秒插入 80 億行，這種分佈可能有利於插入，但它對你正在做的任何其他事情都沒有幫助。

另一種選擇是先在 Date 上使用單個組合 PK，然後是 ID：

CREATE TABLE dbo.PKCombined
(
 ID INT, 
 dt DATE, 
 filler CHAR(4000)
  CONSTRAINT df_filler_comb DEFAULT '' NOT NULL, 
 CONSTRAINT pk_comb PRIMARY KEY CLUSTERED (dt,ID) ON DateRangeScheme(dt)
);

這顯然會導致更少的行儲存在更少的頁面上（例如，無需維護非聚集索引）：

SELECT [table] = o.name, 
 [rows] = SUM(row_count), 
 [pages] = SUM(used_page_count),
 [size_in_kb] = 8.192*SUM(used_page_count)
FROM sys.tables AS o
INNER JOIN sys.indexes AS i
ON o.[object_id] = i.[object_id]
INNER JOIN sys.dm_db_partition_stats AS p
ON i.[object_id] = p.[object_id]
AND i.index_id = p.index_id
WHERE o.name LIKE N'PK%'
GROUP BY o.name
ORDER BY o.name;

結果：

在此處輸入圖像描述

但它如何影響這些其他查詢？與非集群PK版本上的相同SELECT *；SELECT *一個簡單的聚集索引查找。然而DELETE，這是一個更簡單的計劃：

在此處輸入圖像描述

然而，單行查找最終變得更加昂貴：

在此處輸入圖像描述

您可能可以使用 ID 上的非聚集覆蓋索引來解決這個問題，這會將掃描轉換為查找（如果索引未覆蓋，則進行查找），但仍然不會從分區消除中受益。

* 免責聲明：我為 SQL Sentry 工作。

引用自：https://dba.stackexchange.com/questions/74102

相關問答

Sql-Server

表分區是否可以提高性能？這值得麼？

  • October 25, 2019
檢視問答
Sql-Server

查詢性能問題

  • September 29, 2019
檢視問答
Sql-Server

從 MS SQL 2012 中的特定分區刪除數據

  • May 17, 2018
檢視問答
Sql-Server

如何消除分區視圖中的表

  • March 10, 2018
檢視問答
Sql-Server

使用 $Partition 函式提高查詢性能

  • September 25, 2017
檢視問答
Sql-Server

出於性能原因的數據庫分區在虛擬化環境中是否有意義？

  • December 21, 2016
檢視問答