فصل ۷ — بهینه‌سازی جدول‌ها

در چرخه عمر برنامه‌های مبتنی بر داده، جدول‌ها ممکن است برای نگهداری بهتر و افزایش کارایی به پارتیشن‌بندی، فشرده‌سازی یا انتقال به ساختارهای حافظه‌محور نیاز داشته باشند. این بخش با پارتیشن‌بندی جدول آغاز می‌شود؛ روشی که یک جدول منطقی را به چند بخش فیزیکی تقسیم می‌کند، بدون آن‌که برنامه کاربردی مجبور باشد با چند جدول مستقل کار کند.

مفاهیم پارتیشن‌بندی و Partitioning Key

پارتیشن‌بندی بر مبنای یک ستون به‌عنوان کلید پارتیشن انجام می‌شود. کلید پارتیشن باید از نوع داده‌ای مناسب و قابل مقایسه باشد و در صورت وجود ایندکس خوشه‌ای یکتا، باید جزئی از کلید آن باشد. انواعی مانند TEXT، NTEXT، IMAGE، XML، TIMESTAMP، VARCHAR(MAX)، NVARCHAR(MAX)، VARBINARY(MAX) و برخی انواع CLR برای کلید پارتیشن مناسب نیستند. ستون محاسباتی Persisted می‌تواند در شرایط مناسب به‌کار رود. تاریخ، شناسه ترتیبی و مقادیر عددی از انتخاب‌های متداول‌اند.

Partition Function، Partition Scheme و هم‌ترازی ایندکس

Partition Function نقاط مرزی را تعریف می‌کند و مشخص می‌سازد هر مقدار در کدام پارتیشن قرار گیرد. RANGE LEFT مقدار مرزی را در پارتیشن سمت چپ و RANGE RIGHT آن را در پارتیشن سمت راست قرار می‌دهد. Partition Scheme هر پارتیشن منطقی را به Filegroup متناظر نگاشت می‌کند؛ بنابراین تعداد مقصدها همواره یک عدد بیش از تعداد Boundaryها است. ایندکس‌ها می‌توانند aligned یا nonaligned باشند. برای عملیات سریع SWITCH، هم‌ترازی ایندکس‌ها با طرح پارتیشن اهمیت دارد.

سلسله‌مراتب پارتیشن‌بندی

چند جدول می‌توانند از یک Partition Scheme استفاده کنند و چند Scheme نیز می‌توانند به یک Partition Function وابسته باشند. این سلسله‌مراتب جداسازی منطق مرزبندی از محل ذخیره‌سازی را ممکن می‌کند. نتیجه آن است که DBA می‌تواند سیاست زمانی یا عددی ثابتی را میان چند جدول به اشتراک بگذارد و فقط نگاشت Filegroupها را بر حسب نیاز تغییر دهد.

ایجاد Partition Function

نمونه کتاب یک پایگاه داده برای فصل ایجاد کرده و سپس تابع پارتیشن‌بندی تاریخ را با دو مرز می‌سازد. در RANGE LEFT، تاریخ دقیق Boundary متعلق به پارتیشن سمت چپ همان مرز است.

USE Master;
CREATE DATABASE Chapter7;
GO
USE Chapter7;
GO
CREATE PARTITION FUNCTION PartFunc(Date)
AS RANGE LEFT
FOR VALUES ('2017-01-01','2019-01-01');

--- PDF PAGE 226 ---
209
Implementing Partitioning
Implementing partitioning involves creating the partition function and partition scheme 
and then creating the table on the partition scheme. If the table already exists, then you 
will need to drop and re-create the table’s clustered index. These tasks are discussed in 
the following sections.
Creating the Partitioning Objects
The first object that you will need to create is the partition function. This can be created 
using the CREATE PARTITION FUNCTION statement, as demonstrated in Listing 7-1. This 
script creates a database called Chapter7 and then creates a partition function called 
PartFunc. The function specifies a data type for partitioning keys of DATE and sets 
boundary points for 1st Jan 2019 and 1st Jan 2017. Table 7-1 details how dates will be 
distributed between partitions.
Listing 7-1.  Creating the Partition Function
USE Master
GO
--Create Database Chapter7 using default settings from Model
CREATE DATABASE Chapter7 ;
GO
USE Chapter7
GO
--Create Partition Function
Table 7-1.  Distribution of Dates
Date
Partition
Notes
6th June 2015
1
1st Jan 2016
1
If we had used RANGE RIGHT, this value would be in Partition 2.
11th October 2017
2
1st Jan 2018
2
If we had used RANGE RIGHT, this value would be in Partition 3.
9th May 2019
3
Chapter 7  Table Optimizations

ایجاد Partition Scheme و جدول پارتیشن‌شده

Scheme در مثال همه پارتیشن‌ها را روی PRIMARY قرار می‌دهد؛ این کار برای نمایش منطق پارتیشن‌بندی است، هرچند در محیط واقعی معمولاً Filegroupهای جدا یا سیاست ذخیره‌سازی مشخص‌تری استفاده می‌شود.

CREATE PARTITION SCHEME PartScheme
AS PARTITION PartFunc
ALL TO ([PRIMARY]);

CREATE TABLE dbo.Orders
(
  OrderNumber int NOT NULL,
  OrderDate date NOT NULL,
  CustomerID int NOT NULL,
  ProductID int NOT NULL,
  Quantity int NOT NULL,
  NetAmount money NOT NULL,
  TaxAmount money NOT NULL,
  InvoiceAddressID int NOT NULL,
  DeliveryAddressID int NOT NULL,
  DeliveryDate date NULL
) ON PartScheme(OrderDate);

کلید خوشه‌ای و جدول ExistingOrders

برای هم‌ترازی، کلید پارتیشن در کلید ایندکس خوشه‌ای لحاظ می‌شود. سپس کتاب یک جدول عادی ExistingOrders می‌سازد و با داده آزمایشی پر می‌کند تا انتقال یک جدول موجود به Scheme نشان داده شود. این نکته مهم است که مهاجرت یک جدول موجود لزوماً به کپی‌کردن رکوردها نیاز ندارد؛ می‌توان با بازسازی ایندکس خوشه‌ای، جای‌گذاری فیزیکی را تغییر داد.

داده آزمایشی با CTE اعداد و انتخاب تصادفی مقادیر تولید می‌شود. هدف داده‌ها نمایش رفتار توزیع در مرزهای تاریخی است، نه طراحی یک مدل سفارش واقعی. ستون OrderDate همان کلیدی است که در مرحله بعد برای پارتیشن‌بندی استفاده می‌شود.

در ادامه درج داده تکمیل می‌شود. هنگام آزمایش پارتیشن‌بندی باید دقت کرد دامنه داده با Boundaryها هم‌خوان باشد؛ اگر تمام داده در یک بازه کوچک قرار گیرد، ممکن است همه رکوردها در یک پارتیشن بیفتند و مزیت مورد انتظار حاصل نشود.

انتقال جدول موجود روی Partition Scheme

برای انتقال جدول ExistingOrders، Primary Key خوشه‌ای فعلی حذف می‌شود و همان کلید به‌صورت aligned روی PartScheme(OrderDate) بازسازی می‌شود. این کار ساختار ذخیره‌سازی جدول را به Scheme منتقل می‌کند.

ALTER TABLE dbo.ExistingOrders DROP CONSTRAINT PK_ExistingOrders;
-- سپس Primary Key خوشه‌ای مجدداً روی PartScheme(OrderDate) ایجاد می‌شود.

پس از بازسازی، Properties جدول نشان می‌دهد که جدول اکنون روی Scheme پارتیشن‌بندی شده قرار گرفته است. در ایندکس یکتا، قرار داشتن OrderDate در کلید ضروری است تا یکتایی در کل مجموعه پارتیشن‌ها قابل تضمین بماند.

پایش جدول‌های پارتیشن‌شده

تابع سیستمی $PARTITION شماره پارتیشن متناظر با یک مقدار را برمی‌گرداند و می‌توان با آن توزیع داده را گروه‌بندی کرد. این روش سریعاً مشخص می‌کند آیا مرزبندی انتخاب‌شده داده را به‌شکل متعادل یا معنادار توزیع کرده است.

SELECT
    $PARTITION.PartFunc(OrderDate) AS PartitionNumber,
    COUNT(*) AS RowCount
FROM dbo.ExistingOrders
GROUP BY $PARTITION.PartFunc(OrderDate)
ORDER BY PartitionNumber;

اگر مرزهای سالانه برای داده‌ای با دامنه محدود مناسب نباشند، می‌توان Partition Function دیگری با Boundaryهای هفتگی ساخت و دوباره با $PARTITION توزیع را بررسی کرد. اصل مهم این است که طرح پارتیشن باید از الگوی واقعی داده و نیازهای نگهداری پیروی کند، نه صرفاً از تقسیم‌بندی ظاهراً منظم تقویمی.

Sliding Window

Sliding Window روشی برای نگهداری داده‌های زمان‌محور است که در آن قدیمی‌ترین پارتیشن خارج و یک پارتیشن جدید برای دوره بعدی اضافه می‌شود. سه عملیات اصلی عبارت‌اند از SPLIT برای افزودن Boundary، MERGE برای حذف Boundary و SWITCH برای جابه‌جایی یک پارتیشن میان دو جدول/پارتیشن به‌صورت متادیتایی و بسیار سریع.

کتاب جدول دائمی OldOrdersStaging را با ساختار سازگار می‌سازد، پایین‌ترین و بالاترین Boundary را از Metadata استخراج می‌کند و برای آماده‌سازی پنجره لغزان از آن استفاده می‌کند. جدول Staging باید از نظر Filegroup، ستون‌ها، ایندکس‌ها و Constraintهای مؤثر با مقصد سازگار باشد؛ Temporary Table در TempDB برای این عملیات مناسب نیست.

SELECT TOP (1) @LowestBoundaryPoint = CAST(value AS date)
FROM sys.partition_range_values
ORDER BY boundary_id;

SELECT TOP (1) @HighestBoundaryPoint = CAST(value AS date)
FROM sys.partition_range_values
ORDER BY boundary_id DESC;

ترتیب معمول عملیات این است: SWITCH پارتیشن قدیمی به جدول Staging، سپس MERGE مرز قدیمی و در پایان SPLIT برای مرز جدید. اگر پارتیشن‌ها روی Filegroupهای متفاوت باشند، MERGE/SPLIT ممکن است به جابه‌جایی فیزیکی داده نیاز پیدا کند؛ روی مقصد یکسان معمولاً عملیات سبک‌تر و متادیتایی‌تر است.

ALTER TABLE ExistingOrders
SWITCH PARTITION 1 TO OldOrdersStaging PARTITION 2;

ALTER PARTITION FUNCTION PartFuncWeek() MERGE RANGE (@LowestBoundaryPoint);
ALTER PARTITION FUNCTION PartFuncWeek() SPLIT RANGE (DATEADD(day,7,@HighestBoundaryPoint));

Partition Elimination

Partition Elimination یعنی Optimizer فقط پارتیشن‌هایی را بخواند که بر اساس Predicate واقعاً می‌توانند رکورد مطلوب داشته باشند. این قابلیت یکی از اصلی‌ترین مزیت‌های پارتیشن‌بندی برای Queryهای محدوده‌ای است. Execution Plan و Properties عملگر Scan/Seek نشان می‌دهد چند پارتیشن در اجرا لمس شده‌اند.

در Query نخست، فیلتر مستقیم روی OrderDate با همان نوع داده باعث حذف پارتیشن‌های غیرضروری می‌شود. در نمونه دوم، تبدیل ستون به DATETIME2 باعث می‌شود Optimizer نتواند همان شکل از Elimination را انجام دهد و پارتیشن‌های بیشتری اسکن شوند. بنابراین SARGability و هم‌نوع بودن عبارت جست‌وجو با Partitioning Key اهمیت دارد.

SELECT OrderNumber, OrderDate
FROM dbo.ExistingOrders
WHERE OrderDate BETWEEN '2019-03-01' AND '2019-03-07';

-- تبدیل روی ستون می‌تواند Partition Elimination را از بین ببرد
SELECT OrderNumber, OrderDate
FROM dbo.ExistingOrders
WHERE CAST(OrderDate AS datetime2) BETWEEN '2019-03-01' AND '2019-03-07';