主页 / dba / 问题 / 74102
Accepted
dbafromthecold
Asked: 2014-08-16 06:31:56 +0800 CST

分区查询

  • 772

我有几个关于分区时表的物理布局的问题。我一直在研究这个，但仍然有点不确定。

假设我有一个现有的表：-

    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);
sql-server performance

1 个回答

  1. Best Answer

    好的，这里有一个简单的示例来说明原因 - 在您的大多数操作（报告查询、归档操作、分区切换等）将按日期识别行范围的情况下 - 您最好在分区列上进行聚类。让我们有一个简单的基于日期的分区方案和函数：

    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 工作。

    • 7

相关问题