مفهوم Data Partitioning | راهنمای کامل پارتیشن بندی داده‌ها

مفهوم Data Partitioning، پارتیشن نمودن جداول مزایای خاصی را به دنبال دارد مانند Partition Switch. همان طور که می دانیم Rebuild کلاستر ایندکس یک جدول بزرگ عملیات بسیار سنگینی می باشد، اما با استفاده از امکان Data Partitioning می توانیم فقط یک یا چند پارتیشن خاص را در مدت زمان کوتاهی Rebuild نمائیم. اگر Partition Key در شرط کوئری یا شرط Join مورد استفاده قرار گیرد، سرعت اجرای کوئری می تواند افزایش یابد. با استفاده از Data Partitioning بحث فشرده سازی را به ازای پارتیشن های جاری می توان انجام نداد و فقط پارتیشن های قدیمی را فشرده نمود. Data Partitioning مزایای دیگری نیز دارد. با همه این مزایا، پارتیشن نمودن جداول معایبی نیز دارد که در این مقاله قصد داریم به بررسی مهمترین این موارد بپردازیم. شما می‌توانید علاوه‌ بر مطالعه این مطلب، با رجوع به آموزش SQL Server ، سایر مباحث مهم مربوطه را به زبان ساده و با جزئیات بیاموزید.

برای نشان دادن این موضوع جدول زیر را ایجاد و با داده های فرضی پر می نمائیم:

Use Test
GO
CREATE TABLE dbo.DATA
(
  ID INT NOT NULL,
  DATECREATED DATETIME NOT NULL
  CONSTRAINT DEF_DATA_DATECREATED DEFAULT GETUTCDATE(),
  DATEMODIFIED DATETIME NOT NULL
  CONSTRAINT DEF_DATA_DATEMODIFIED DEFAULT GETUTCDATE(),
  PLACEHOLDER CHAR(500) NULL
);
DECLARE @STARTDATE DATETIME = '2016-01-01';
;WITH N1(C) AS (SELECT 0 UNION ALL SELECT 0) -- 2 ROWS
,N2(C) AS (SELECT 0 FROM N1 AS T1 CROSS JOIN N1 AS T2) -- 4 ROWS
,N3(C) AS (SELECT 0 FROM N2 AS T1 CROSS JOIN N2 AS T2) -- 16 ROWS
,N4(C) AS (SELECT 0 FROM N3 AS T1 CROSS JOIN N3 AS T2) -- 256 ROWS
,N5(C) AS (SELECT 0 FROM N4 AS T1 CROSS JOIN N4 AS T2) -- 65,536 ROWS
,N6(C) AS (SELECT 0 FROM N5 AS T1 CROSS JOIN N2 AS T2 CROSS JOIN N1 AS T3) -- 524,288
,IDS(ID) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM N6)
INSERT INTO dbo.DATA WITH (TABLOCK)(ID, DATECREATED, DATEMODIFIED)
SELECT ID, DATEADD(SECOND,35 * ID,@STARTDATE),
CASE
WHEN ID % 10 = 0
THEN DATEADD(SECOND, 24 * 60 * 60 * (ID % 31) + 11200 + ID % 59 + 35 * ID,
@STARTDATE)
ELSE DATEADD(SECOND,35 * ID,@STARTDATE)
END
FROM IDS;
GO

 دستور زیر یک Unique Clustered Index و همچنین یک NonClustered Index غیر یکتا را بر روی جدول Data ایجاد می نماید:

CREATE UNIQUE CLUSTERED INDEX IDXDATAID
ON dbo.DATA(DATECREATED, ID)
WITH (DATA_COMPRESSION = PAGE);
CREATE NONCLUSTERED INDEX IDXDATADATEMODIFIED
ON dbo.DATA(DATEMODIFIED)
WITH (DATA_COMPRESSION = PAGE);

اکنون پروسیجر زیر را ایجاد و اجرا می نمائیم :

CREATE PROCEDURE USP_TEST
(@TOP TINYINT, @LASTDATEMODIFIED DATETIME)
AS
SELECT TOP (@TOP) ID, DATECREATED, DATEMODIFIED, PLACEHOLDER
FROM dbo.DATA
WHERE DATEMODIFIED > @LASTDATEMODIFIED
ORDER BY DATEMODIFIED
GO
EXEC USP_TEST 100, '2016-08-25 00:00:00.000'

تصویر زیر Actual Execution Plan مربوط به اجرای پروسیجر را نمایش می دهد:

معایب پارتیشن نمودن جداول

همان طور که در تصویر مشاهده می نمائید SQL Server از NonClustered Index ایجاد شده بر روی جدول به خوبی استفاده نموده است.
می خواهیم جدول را پارتیشن نموده و مجدد پروسیجر را اجرا نمائیم.
دستورات زیر یک FileGroup به دیتابیس تست اضافه نموده و سپس Partition Function و Partition Scheme را ایجاد می نمایند:

ALTER DATABASE TEST ADD FILEGROUP FG1
ALTER DATABASE TEST ADD FILE
(NAME = F1, FILENAME = N'D:DATABASESDATATESTF1.NDF', SIZE = 1024 MB, FILEGROWTH = 256 MB) TO FILEGROUP FG1
CREATE PARTITION FUNCTION PFDATA(DATETIME)
AS RANGE RIGHT FOR VALUES
('۲۰۱۶-۰۲-۰۱', '۲۰۱۶-۰۳-۰۱','۲۰۱۶-۰۴-۰۱','۲۰۱۶-۰۵-۰۱','۲۰۱۶-۰۶-۰۱','۲۰۱۶-۰۷-۰۱','۲۰۱۶-۰۸-۰۱');
CREATE PARTITION SCHEME PSDATA AS PARTITION PFDATA ALL TO ([FG1]);

 دستورات زیر نیز جدول را پارتیشن و NonClustered Index آن را اصطلاحا Aligned می نمایند:

CREATE UNIQUE CLUSTERED INDEX IDXDATAID
ON DBO.DATA(DATECREATED, ID)
WITH (DATA_COMPRESSION = PAGE, DROP_EXISTING = ON)
ON PSDATA(DATECREATED);
CREATE NONCLUSTERED INDEX IDXDATADATEMODIFIED
ON DBO.DATA(DATEMODIFIED)
WITH (DATA_COMPRESSION = PAGE, DROP_EXISTING = ON)
ON PSDATA(DATECREATED);
GO

 مجدد پروسیجر را اجرا می نمائیم:

EXEC USP_TEST 100, '2016-08-25 00:00:00.000'

تصویر زیر پلن اجرایی کوئری را نشان می دهد:همان طور که مشاهده می نمائید پلن اجرایی پروسیجر تغییر یافته و Clustered Index Scan رخ داده است.

راه حل ها

حل مسئله مطرح شده می تواند یک Trade Off باشد. به عنوان یک راه حل می توان NonClustered Index را به صورت Aligned شده ایجاد نمود و پروسیجر را به صورت زیر بازنویسی کرد:

CREATE OR ALTER PROCEDURE USP_TEST
(@TOP TINYINT, @LASTDATEMODIFIED DATETIME)
AS
DECLARE
@BOUNDARYVALUESCOUNT INT
CREATE TABLE #PARTITIONS(PARTITIONNUM SMALLINT NOT NULL PRIMARY KEY);
-- GETTING NUMBER OF BOUNDARY VALUES IN PARTITION FUNCTION
SELECT @BOUNDARYVALUESCOUNT = MAX(BOUNDARY_ID)
FROM SYS.PARTITION_FUNCTIONS PF JOIN SYS.PARTITION_RANGE_VALUES PRF ON
PF.FUNCTION_ID = PRF.FUNCTION_ID
WHERE PF.NAME = 'PFDATA';
;WITH PARTITIONS(PARTITIONNUM)
AS
(
SELECT 1
UNION ALL
SELECT PARTITIONNUM + 1
FROM PARTITIONS
WHERE PARTITIONNUM <= @BOUNDARYVALUESCOUNT
)
INSERT INTO #PARTITIONS(PARTITIONNUM)
SELECT PARTITIONNUM FROM PARTITIONS;
;WITH STEPS1AND2(ID, DATECREATED, DATEMODIFIED)
AS
(
SELECT TOP 100 PARTDATA.ID, PARTDATA.DATECREATED, PARTDATA.DATEMODIFIED
FROM #PARTITIONS P
CROSS APPLY
(
SELECT TOP 100 ID, DATECREATED, DATEMODIFIED
FROM dbo.DATA
WHERE
DATEMODIFIED > @LASTDATEMODIFIED AND
$PARTITION.PFDATA(DATECREATED) = P.PARTITIONNUM
ORDER BY DATEMODIFIED
) PARTDATA
ORDER BY PARTDATA.DATEMODIFIED
)
-- STEP 3 - CI SEEK AS KEY LOOKUP OPERATION
SELECT S.ID, S.DATECREATED, S.DATEMODIFIED, D.PLACEHOLDER
FROM STEPS1AND2 S JOIN DBO.DATA D ON
D.ID = S.ID AND S.DATECREATED = D.DATECREATED
ORDER BY S.DATEMODIFIED

بخشی از پلن اجرایی پروسیجر بعد از بازنویسی:

تاثیرات Align نمودن

در این مقاله به بررسی نمونه دیگری از تاثیر Align نمودن مبحث Nonclustered Index ها می‌پردازیم. جهت یادآوری Aligned Nonclustered Index یک ایندکس است که مانند Clustered ایندکس جدول پارتیشن شده است. ابتدا با استفاده از کد زیر یک Database و یک Table ایجاد می نمائیم:

Use master
GO

Create Database Test
GO

Use Test
GO
CREATE TABLE Numbers
(
  Number INT NOT NULL,
  CONSTRAINT PK_Numbers 
    PRIMARY KEY CLUSTERED (Number)
)
GO

جدول ایجاد شده را با استفاده از داده‌های زیر پر می‌نمائیم:

INSERT INTO Numbers
SELECT
  (a.Number * 256) + b.Number AS Number
FROM 
  (
    SELECT number
    FROM master..spt_values
    WHERE 
      type = 'P'
      AND number <= 255
  ) a (Number),
  (
    SELECT number
    FROM master..spt_values
    WHERE 
      type = 'P'
      AND number <= 255
  ) b (Number)
GO

نکته: از جدول ایجاد شده فقط جهت Load دیتا استفاده خواهد شد.

دستورات زیر Partition Scheme و Partition Function را ایجاد می نمایند:

CREATE PARTITION FUNCTION PF (integer) 
AS RANGE RIGHT
FOR VALUES 
  (
  ۱۰۰۰۰, ۲۰۰۰۰, ۳۰۰۰۰, ۴۰۰۰۰, ۵۰۰۰۰,
  ۶۰۰۰۰, ۷۰۰۰۰, ۸۰۰۰۰, ۹۰۰۰۰, ۱۰۰۰۰۰,
  ۱۱۰۰۰۰, ۱۲۰۰۰۰, ۱۳۰۰۰۰, ۱۴۰۰۰۰, ۱۵۰۰۰۰
  );
GO

CREATE PARTITION SCHEME PS 
AS PARTITION PF 
ALL TO ([PRIMARY]);
GO

سپس یک جدول به شکل پارتیشن شده ایجاد و آن را با یکسری داده پر می‌نمائیم:

CREATE TABLE dbo.PartitionedTbl
(
    RowID       integer IDENTITY NOT NULL,
    UserID      integer NOT NULL,
    SessionID   integer NOT NULL,
    LocationID  integer NOT NULL,
 
    CONSTRAINT PK_PartitionedTbl
    PRIMARY KEY CLUSTERED (RowID)
    ON PS (RowID)
);
GO
INSERT dbo.PartitionedTbl WITH (TABLOCKX)
    (UserID, SessionID, LocationID)
SELECT
    ABS(CHECKSUM(NEWID())) % 50,
    ABS(CHECKSUM(NEWID())) % 30,
    ABS(CHECKSUM(NEWID())) % 10
FROM dbo.Numbers AS N
WHERE
    N.Number BETWEEN 1 AND 150000;
GO

فرض کنید می خواهیم کوئری زیر را بر روی جدول PartitionedTbl اجرا نمائیم:

SELECT LocationID, UserID, SessionID, COUNT_BIG(*)
FROM dbo.PartitionedTbl
GROUP BY LocationID, UserID, SessionID;
GO

جهت افزایش سرعت اجرای این کوئری یک Nonclustered Index به شکل زیر ایجاد می‌کنیم:

CREATE NONCLUSTERED INDEX IXNCUserIDSessionIDLocationID
ON dbo.PartitionedTbl (UserID, SessionID, LocationID)
ON [Primary];
GO

این ایندکس به شکل Align نشده ایجاد گردید. تصویر زیر Plan اجرایی کوئری را نشان می دهد:

همان طور که در تصویر مشاهده می‌نمائید Query Optimizer از اپراتور Stream Aggregate استفاده نموده است. حالا Nonclustered Index جدول را اصطلاحا Align می‌نمائیم:

CREATE NONCLUSTERED INDEX IXNCUserIDSessionIDLocationID
ON dbo.PartitionedTbl (UserID, SessionID, LocationID)
With (Drop_Existing = On)
ON PS (RowID);
GO

تصویر زیر Plan اجرایی کوئری را نمایش می‌دهد:

تصویر نشان می‌دهد که اپراتور Stream Aggregate به اپراتور Hash Match Aggregate تغییر یافته است. همچنین افراد علاقه‌مند می‌توانند با مطالعه مقاله پرکاربردترین دستورات SQL Server، دانش خود را در زمینه کوئری‌نویسی گسترش دهند.

مشکلات اپراتور Hash Match Aggregate 

• اصطلاحا یک Blocking Operator است. یعنی تا همه ردیف ها را از اپراتور قبلی دریافت نکند هیچ ردیفی را به کاربر برنمی گرداند.
• این اپراتور جهت نگهداری Hash Table به Memory نیاز دارد.

تا اینجای کار هدف ما فقط نمایش دادن یک مشکل بود. اصولا Data Partitioning زمانی جهت بهبود Execution Plan می تواند مفید باشد که Partition Elimination رخ دهد. به بیان ساده همه پارتیشن ها درگیر اجرای کوئری نشوند.

$Partition در واقع یک Function است که از طریق آن می توان صراحتا حذف یکسری از پارتیشن ها را در کوئری انجام داد. همچنین در این مثال ما نیاز داریم که ترتیب ستون های شرکت کننده در قسمت Group By را تغییر دهیم. این ستون ها دقیقا باید مطابق با ترتیبی که در ایندکس قرار گرفته اند در قسمت Group By استفاده شوند.با توجه به توضیحات ارائه شده کوئری مورد بحث را به شکل زیر بازنویسی می‌نمائیم:

SELECT LocationID, UserID, SessionID, COUNT_BIG(*)
FROM dbo.PartitionedTbl 
WHERE $PARTITION.PF(RowID) = 1
GROUP BY UserID, SessionID, LocationID;

تصویر زیر Plan اجرایی کوئری را نمایش می‌دهد:

سخن پایانی

مفهوم Data Partitioning، هدف مقاله این است که، صرفا ایجاد ایندکس بر روی شرط کوئری باعث بهبود Performance نمی شود، همواره و با توجه به شرایط باید Plan اجرای کوئری مورد بررسی قرار گیرد که آیا SQL Server از ایندکس ایجاد شده به شکل مطلوبی استفاده می نماید یا خیر. ضمن اینکه بسته به شرایط سیستم و نیازها می توان از یکسری امکانات ارائه شده در SQL Server استفاده نمود. این گونه نیست که همه Solution ها برای همه سیستم ها مناسب باشند.

 همچنین مفهوم Data Partitioning، همان طور که در تصویر مشاهده می نمائید Query Optimizer از اپراتور Stream Aggregate استفاده نموده است. هدف مقاله این است که نشان دهد Align نمودن Nonclustered Index‌ها ممکن است باعث کاهش Performance برخی از کوئری‌ها گردد، که ما باید آن کوئری ها را بازنویسی نمائیم یا بسیار آگاهانه Nonclustered Indexها را Align نکنیم. واقعا این موضوع می‌تواند Trade off باشد.

ما در نیک آموز منتظر نظرات ارزشمند شما درباره این مقاله هستیم.