تقسيم الجداول في تجمع مخصص SQL
توصيات وأمثلة بشأن استخدام أقسام الجدول في تجمع SQL المخصص.
ما هي أقسام الجدول؟
تمكنك أقسام الجدول من تقسيم بياناتك إلى مجموعات أصغر من البيانات. في معظم الحالات، يتم إنشاء أقسام الجدول في عمود تاريخ. يتم دعم التقسيم على جميع أنواع جداول تجمع SQL المخصصة؛ بما في ذلك مخزن الأعمدة المتفاوت المسافات والفهرس المتفاوت المسافات وكومة الذاكرة المؤقتة. يتم دعم التقسيم أيضًا على جميع أنواع التوزيع، بما في ذلك كل من التجزئة أو الترتيب الدوري الموزع.
يمكن أن يفيد التقسيم في صيانة البيانات وأداء الاستعلام. يعتمد ما إذا كان يفيد كلاهما أو واحد فقط على كيفية تحميل البيانات وما إذا كان يمكن استخدام العمود نفسه لكلا الغرضين، حيث لا يمكن إجراء التقسيم إلا على عمود واحد.
فوائد الأحمال
الفائدة الأساسية للتقسيم في تجمع SQL المخصص هو تحسين كفاءة وأداء تحميل البيانات باستخدام حذف القسم والتبديل والدمج. في معظم الحالات يتم تقسيم البيانات على عمود تاريخ مرتبط ارتباطًا وثيقًا بالترتيب الذي يتم تحميل البيانات به في تجمع SQL. واحدة من أكبر فوائد استخدام الأقسام للحفاظ على البيانات هي تجنب تسجيل المعاملات. في حين أن إدراج البيانات أو تحديثها أو حذفها ببساطة يمكن أن يكون النهج الأكثر وضوحًا، مع القليل من التفكير والجهد، يمكن أن يؤدي استخدام التقسيم أثناء عملية التحميل إلى تحسين الأداء بشكل كبير.
يمكن استخدام تبديل الأقسام لإزالة مقطع من الجدول أو استبداله بسرعة. على سبيل المثال، قد يحتوي جدول حقائق المبيعات على بيانات فقط للأشهر الـ 36 الماضية. في نهاية كل شهر، يتم حذف أقدم شهر من بيانات المبيعات من الجدول. يمكن حذف هذه البيانات باستخدام عبارة حذف لحذف البيانات لأقدم شهر.
ومع ذلك، يمكن أن يستغرق حذف كمية كبيرة من البيانات صفًا تلو الآخر مع عبارة حذف وقتًا طويلاً، بالإضافة إلى إنشاء مخاطر المعاملات الكبيرة التي تستغرق وقتًا طويلاً للتراجع إذا حدث خطأ ما. النهج الأمثل هو إسقاط أقدم قسم من البيانات. حيث قد يستغرق حذف الصفوف الفردية ساعات، قد يستغرق حذف قسم بأكمله ثوان.
فوائد الاستعلامات
يمكن أيضًا استخدام التقسيم لتحسين أداء الاستعلام. يمكن أن يقصر الاستعلام الذي يطبق عامل تصفية على البيانات المقسمة الفحص على الأقسام المؤهلة فقط. يمكن أن تتجنب طريقة التصفية هذه فحص الجدول بالكامل وتمسح مجموعة فرعية أصغر فقط من البيانات. مع إدخال فهارس تخزين الأعمدة المجمعة، تكون فوائد أداء إزالة التقييم أقل فائدة، ولكن في بعض الحالات يمكن أن تكون هناك فائدة للاستعلامات.
على سبيل المثال، إذا تم تقسيم جدول حقائق المبيعات إلى 36 شهرًا باستخدام حقل تاريخ المبيعات، يمكن للاستعلامات التي تقوم بالتصفية في تاريخ البيع تخطي البحث في أقسام لا تتطابق مع عامل التصفية.
تغيير حجم القسم
بينما يمكن استخدام التقسيم لتحسين أداء بعض وحدات السيناريو، إنشاء جدول مع أقسام كثيرة جدًا يمكن أن يضر الأداء في بعض الحالات. هذه المخاوف صحيحة بشكل خاص لجداول تخزين الأعمدة المجمعة.
لكي يكون التقسيم مفيدًا، من المهم فهم وقت استخدام التقسيم وعدد الأقسام المراد إنشاؤها. لا توجد قاعدة سريعة صعبة فيما يتعلق بعدد الأقسام الكثيرة جدًا، ويعتمد ذلك على بياناتك وعدد الأقسام التي تقوم بتحميلها في وقت واحد. عادة ما يحتوي مخطط التقسيم الناجح على عشرات إلى مئات الأقسام، وليس الآلاف.
عند إنشاء أقسام على جداول مخزن الأعمدة متفاوتة المسافات، من المهم مراعاة عدد الصفوف التي تنتمي إلى كل قسم. للحصول على أفضل ضغط وأداء جداول الأعمدة متفاوتة المسافات، هناك حاجة إلى ما لا يقل عن 1 مليون صف لكل توزيع وقسم. قبل إنشاء الأقسام، يقوم تجمع SQL المخصص بالفعل بتقسيم كل جدول إلى 60 توزيعًا.
أي تقسيم تمت إضافته إلى جدول بالإضافة إلى التوزيعات التي تم إنشاؤها خلف الكواليس. باستخدام هذا المثال، إذا كان جدول حقائق المبيعات يحتوي على 36 قسمًا شهريًا، ونظرًا إلى أن تجمع SQL مخصص لديه 60 توزيعًا، من ثمَّ جدول حقائق المبيعات يجب أن يحتوي على 60 مليون صف شهريًا، أو 2.1 مليار صف عند تعبئة كافة الأشهر. إذا كان جدول يحتوي على أقل من الحد الأدنى الموصى به لعدد الصفوف لكل قسم، ففكر في استخدام أقسام أقل لزيادة عدد الصفوف لكل قسم.
لمزيد من المعلومات، راجع مقالة الفهرسة، التي تتضمن الاستعلامات التي يمكنها تقييم جودة فهارس تخزين أعمدة نظام المجموعة.
الاختلافات في بناء الجملة من SQL Server
يقدم تجمع SQL المخصص طريقة لتعريف الأقسام التي هي أبسط من SQL Server. لا يتم استخدام أنظمة ووظائف التقسيم في تجمع SQL المخصص كما هو الحال في SQL Server. بدلاً من ذلك، كل ما عليك القيام به هو تحديد العمود المقسم ونقاط الحدود.
في حين أن بناء جملة التقسيم قد يكون مختلفًا قليلاً عن SQL Server، فإن المفاهيم الأساسية هي نفسها. تدعم SQL Server وتجمع SQL المخصص عمود قسم واحد لكل جدول، والذي يمكن أن يكون قسم النطاقات. لمعرفة المزيد حول التقسيم، راجع الجداول والفهارس المقسمة.
يستخدم المثال التالي عبارة CREATE TABLE لتقسيم الجدول FactInternetSales على العمود OrderDateKey:
CREATE TABLE [dbo].[FactInternetSales]
(
[ProductKey] int NOT NULL
, [OrderDateKey] int NOT NULL
, [CustomerKey] int NOT NULL
, [PromotionKey] int NOT NULL
, [SalesOrderNumber] nvarchar(20) NOT NULL
, [OrderQuantity] smallint NOT NULL
, [UnitPrice] money NOT NULL
, [SalesAmount] money NOT NULL
)
WITH
( CLUSTERED COLUMNSTORE INDEX
, DISTRIBUTION = HASH([ProductKey])
, PARTITION ( [OrderDateKey] RANGE RIGHT FOR VALUES
(20000101,20010101,20020101
,20030101,20040101,20050101
)
)
);
ترحيل الأقسام من SQL Server
لترحيل تعريفات أقسام SQL Server إلى تجمع SQL المخصص ببساطة:
- تخلص من نظام تقسيم SQL Server.
- أضف تعريف دالة القسم إلى CREATE TABLE.
إذا كنت تقوم بترحيل جدول مقسم من مثيل SQL Server، يمكن أن تساعدك SQL التالية في معرفة عدد الصفوف في كل قسم. ضع في اعتبارك أنه إذا تم استخدام نفس نقاوة التقسيم في تجمع SQL المخصص، ينخفض عدد الصفوف لكل قسم بمقدار عامل 60.
-- Partition information for a SQL Server Database
SELECT s.[name] AS [schema_name]
, t.[name] AS [table_name]
, i.[name] AS [index_name]
, p.[partition_number] AS [partition_number]
, SUM(a.[used_pages]*8.0) AS [partition_size_kb]
, SUM(a.[used_pages]*8.0)/1024 AS [partition_size_mb]
, SUM(a.[used_pages]*8.0)/1048576 AS [partition_size_gb]
, p.[rows] AS [partition_row_count]
, rv.[value] AS [partition_boundary_value]
, p.[data_compression_desc] AS [partition_compression_desc]
FROM sys.schemas s
JOIN sys.tables t ON t.[schema_id] = s.[schema_id]
JOIN sys.partitions p ON p.[object_id] = t.[object_id]
JOIN sys.allocation_units a ON a.[container_id] = p.[partition_id]
JOIN sys.indexes i ON i.[object_id] = p.[object_id]
AND i.[index_id] = p.[index_id]
JOIN sys.data_spaces ds ON ds.[data_space_id] = i.[data_space_id]
LEFT JOIN sys.partition_schemes ps ON ps.[data_space_id] = ds.[data_space_id]
LEFT JOIN sys.partition_functions pf ON pf.[function_id] = ps.[function_id]
LEFT JOIN sys.partition_range_values rv ON rv.[function_id] = pf.[function_id]
AND rv.[boundary_id] = p.[partition_number]
WHERE p.[index_id] <=1
GROUP BY s.[name]
, t.[name]
, i.[name]
, p.[partition_number]
, p.[rows]
, rv.[value]
, p.[data_compression_desc];
تبديل القسم
يدعم تجمع SQL المخصص تقسيم الأقسام ودمجها والتبديل. يتم تنفيذ كل من هذه الدالات باستخدام عبارة ALTER TABLE.
لتبديل الأقسام بين جدولين، يجب التأكد من محاذاة الأقسام على الحدود الخاصة بها وتطابق تعريفات الجدول. نظرًا لعدم توفر قيود التحقق لفرض نطاق القيم في جدول، يجب أن يحتوي الجدول المصدر على نفس حدود القسم مثل الجدول الهدف. إذا لم تكن حدود القسم هي نفسها، فسيفشل مفتاح القسم حيث لن تتم مزامنة بيانات تعريف القسم.
يتطلب تقسيم القسم أن يكون القسم المعني (وليس بالضرورة الجدول بأكمله) فارغًا إذا كان الجدول يحتوي على فهرس مخزن أعمدة متفاوت المسافات (CCI). يمكن أن تحتوي الأقسام الأخرى في نفس الجدول على بيانات. لا يمكن تقسيم القسم الذي يحتوي على بيانات، سيؤدي ذلك إلى خطأ: ALTER PARTITION statement failed because the partition is not empty. Only empty partitions can be split in when a columnstore index exists on the table. Consider disabling the columnstore index before issuing the ALTER PARTITION statement, then rebuilding the columnstore index after ALTER PARTITION is complete. كحل بديل لتقسيم قسم يحتوي على بيانات، راجع كيفية تقسيم قسم يحتوي على بيانات.
كيفية تقسيم قسم يحتوي على بيانات
الطريقة الأكثر فعالية لتقسيم قسم يحتوي بالفعل على بيانات هي استخدام عبارة CTAS. إذا كان الجدول المقسم مخزن أعمدة متفاوت المسافات، يجب أن يكون قسم الجدول فارغًا قبل أن يمكن تقسيمه.
ينشئ المثال التالي جدول مخزن أعمدة مقسمًا. يدرج صفا واحدًا في كل قسم:
CREATE TABLE [dbo].[FactInternetSales]
(
[ProductKey] int NOT NULL
, [OrderDateKey] int NOT NULL
, [CustomerKey] int NOT NULL
, [PromotionKey] int NOT NULL
, [SalesOrderNumber] nvarchar(20) NOT NULL
, [OrderQuantity] smallint NOT NULL
, [UnitPrice] money NOT NULL
, [SalesAmount] money NOT NULL
)
WITH
( CLUSTERED COLUMNSTORE INDEX
, DISTRIBUTION = HASH([ProductKey])
, PARTITION ( [OrderDateKey] RANGE RIGHT FOR VALUES
(20000101
)
)
);
INSERT INTO dbo.FactInternetSales
VALUES (1,19990101,1,1,1,1,1,1);
INSERT INTO dbo.FactInternetSales
VALUES (1,20000101,1,1,1,1,1,1);
يبحث الاستعلام التالي عن عدد الصفوف باستخدام طريقة عرض الكتالوج sys.partitions :
SELECT QUOTENAME(s.[name])+'.'+QUOTENAME(t.[name]) as Table_name
, i.[name] as Index_name
, p.partition_number as Partition_nmbr
, p.[rows] as Row_count
, p.[data_compression_desc] as Data_Compression_desc
FROM sys.partitions p
JOIN sys.tables t ON p.[object_id] = t.[object_id]
JOIN sys.schemas s ON t.[schema_id] = s.[schema_id]
JOIN sys.indexes i ON p.[object_id] = i.[object_Id]
AND p.[index_Id] = i.[index_Id]
WHERE t.[name] = 'FactInternetSales';
يتلقى الأمر المنقسم التالي رسالة خطأ:
ALTER TABLE FactInternetSales SPLIT RANGE (20010101);
Msg 35346, Level 15, State 1, Line 44
SPLIT clause of ALTER PARTITION statement failed because the partition is not empty. Only empty partitions can be split in when a columnstore index exists on the table. Consider disabling the columnstore index before issuing the ALTER PARTITION statement, then rebuilding the columnstore index after ALTER PARTITION is complete.
ومع ذلك، يمكنك استخدام CTAS لإنشاء جدول جديد للاحتفاظ بالبيانات.
CREATE TABLE dbo.FactInternetSales_20000101
WITH ( DISTRIBUTION = HASH(ProductKey)
, CLUSTERED COLUMNSTORE INDEX
, PARTITION ( [OrderDateKey] RANGE RIGHT FOR VALUES
(20000101
)
)
)
AS
SELECT *
FROM FactInternetSales
WHERE 1=2;
عند محاذاة حدود القسم، يسمح بالتبديل. سيؤدي ذلك إلى ترك الجدول المصدر مع قسم فارغ يمكنك تقسيمه لاحقًا.
ALTER TABLE FactInternetSales SWITCH PARTITION 2 TO FactInternetSales_20000101 PARTITION 2;
ALTER TABLE FactInternetSales SPLIT RANGE (20010101);
كل ما تبقى هو محاذاة البيانات إلى حدود القسم الجديدة باستخدام CTAS، ثم تبديل البيانات مرة أخرى إلى الجدول الرئيسي.
CREATE TABLE [dbo].[FactInternetSales_20000101_20010101]
WITH ( DISTRIBUTION = HASH([ProductKey])
, CLUSTERED COLUMNSTORE INDEX
, PARTITION ( [OrderDateKey] RANGE RIGHT FOR VALUES
(20000101,20010101
)
)
)
AS
SELECT *
FROM [dbo].[FactInternetSales_20000101]
WHERE [OrderDateKey] >= 20000101
AND [OrderDateKey] < 20010101;
ALTER TABLE dbo.FactInternetSales_20000101_20010101 SWITCH PARTITION 2 TO dbo.FactInternetSales PARTITION 2;
بمجرد الانتهاء من نقل البيانات، من الأفضل تحديث الإحصائيات في الجدول الهدف. يضمن تحديث الإحصائيات أن تعكس الإحصائيات بدقة التوزيع الجديد للبيانات في الأقسام الخاصة بكل منها.
UPDATE STATISTICS [dbo].[FactInternetSales];
وأخيرًا، في حالة تبديل القسم لمرة واحدة لنقل البيانات، يمكنك إسقاط الجداول التي تم إنشاؤها لتبديل القسم، FactInternetSales_20000101_20010101 وFactInternetSales_20000101. بدلاً من ذلك، قد تحتاج إلى الاحتفاظ بجداول فارغة لمفاتيح تبديل الأقسام العادية المؤتمتة.
تحميل بيانات جديدة في أقسام تحتوي على بيانات في خطوة واحدة
يعد تحميل البيانات في أقسام مع تبديل الأقسام طريقة ملائمة لتنظيم بيانات جديدة في جدول غير مرئي للمستخدمين. قد يكون من الصعب على الأنظمة المزدحمة التعامل مع خلاف التأمين المرتبط بتبديل القسم.
لمسح البيانات الموجودة في قسم، ALTER TABLE يتم استخدامه ليتم طلبه لتبديل البيانات. ثم طلب ALTER TABLE آخر للتبديل في البيانات الجديدة.
في تجمع SQL المخصص، يتم دعم الخيار TRUNCATE_TARGET في الأمر ALTER TABLE. مع TRUNCATE_TARGET، الأمر ALTER TABLE يحل محل البيانات الموجودة في القسم ببيانات جديدة. فيما يلي مثال يستخدم CTAS لإنشاء جدول جديد مع البيانات الموجودة، وإدراج بيانات جديدة، ثم تبديل جميع البيانات مرة أخرى إلى الجدول الهدف، والكتابة فوق البيانات الموجودة.
CREATE TABLE [dbo].[FactInternetSales_NewSales]
WITH ( DISTRIBUTION = HASH([ProductKey])
, CLUSTERED COLUMNSTORE INDEX
, PARTITION ( [OrderDateKey] RANGE RIGHT FOR VALUES
(20000101,20010101
)
)
)
AS
SELECT *
FROM [dbo].[FactInternetSales]
WHERE [OrderDateKey] >= 20000101
AND [OrderDateKey] < 20010101
;
INSERT INTO dbo.FactInternetSales_NewSales
VALUES (1,20000101,2,2,2,2,2,2);
ALTER TABLE dbo.FactInternetSales_NewSales SWITCH PARTITION 2 TO dbo.FactInternetSales PARTITION 2 WITH (TRUNCATE_TARGET = ON);
التحكم في مصدر تقسيم الجدول
ملاحظة
إذا لم يتم تكوين أداة التحكم بالمصادر لتجاهل مخططات الأقسام، فقد يؤدي تغيير مخطط جدول لتحديث الأقسام إلى إسقاط جدول وإعادة إنشائه كجزء من التوزيع، والذي قد يكون غير قابل للتطبيق. قد يكون من الضروري إيجاد حل مخصص لتنفيذ مثل هذا التغيير، كما هو موضح أدناه. تحقق من أن أداة التكامل/النشر المستمر (CI/CD) تسمح بذلك. في SQL Server Data Tools (SSDT)، ابحث عن إعدادات النشر المتقدمة "تجاهل أنظمة الأقسام" لتجنب برنامج نصي تم إنشاؤه يؤدي إلى إسقاط جدول وإعادة إنشائه.
هذا المثال مفيد عند تحديث مخططات أقسام جدول فارغ. لنشر تغييرات القسم باستمرار على جدول مع البيانات، اتبع الخطوات الواردة في كيفية تقسيم قسم يحتوي على بيانات إلى جانب التوزيع لنقل البيانات مؤقتًا من كل قسم قبل تطبيق تقسيم نطاق القسم. هذا ضروري لأن أداة CI/CD ليست على دراية بالأقسام التي تحتوي على بيانات.
لتجنب الصدأ في تعريف الجدول الخاص بك في نظام التحكم بالمصادر، قد ترغب في التفكير في النهج التالي:
إنشاء الجدول كجدول مقسم ولكن بدون قيم أقسام
CREATE TABLE [dbo].[FactInternetSales] ( [ProductKey] int NOT NULL , [OrderDateKey] int NOT NULL , [CustomerKey] int NOT NULL , [PromotionKey] int NOT NULL , [SalesOrderNumber] nvarchar(20) NOT NULL , [OrderQuantity] smallint NOT NULL , [UnitPrice] money NOT NULL , [SalesAmount] money NOT NULL ) WITH ( CLUSTERED COLUMNSTORE INDEX , DISTRIBUTION = HASH([ProductKey]) , PARTITION ( [OrderDateKey] RANGE RIGHT FOR VALUES () ) );SPLITالجدول كجزء من عملية التوزيع:-- Create a table containing the partition boundaries CREATE TABLE #partitions WITH ( LOCATION = USER_DB , DISTRIBUTION = HASH(ptn_no) ) AS SELECT ptn_no , ROW_NUMBER() OVER (ORDER BY (ptn_no)) as seq_no FROM ( SELECT CAST(20000101 AS INT) ptn_no UNION ALL SELECT CAST(20010101 AS INT) UNION ALL SELECT CAST(20020101 AS INT) UNION ALL SELECT CAST(20030101 AS INT) UNION ALL SELECT CAST(20040101 AS INT) ) a; -- Iterate over the partition boundaries and split the table DECLARE @c INT = (SELECT COUNT(*) FROM #partitions) , @i INT = 1 --iterator for while loop , @q NVARCHAR(4000) --query , @p NVARCHAR(20) = N'' --partition_number , @s NVARCHAR(128) = N'dbo' --schema , @t NVARCHAR(128) = N'FactInternetSales' --table; WHILE @i <= @c BEGIN SET @p = (SELECT ptn_no FROM #partitions WHERE seq_no = @i); SET @q = (SELECT N'ALTER TABLE '+@s+N'.'+@t+N' SPLIT RANGE ('+@p+N');'); -- PRINT @q; EXECUTE sp_executesql @q; SET @i+=1; END -- Code clean-up DROP TABLE #partitions;
باستخدام هذا الأسلوب، تظل التعليمات البرمجية في التحكم بالمصادر ثابتة ويسمح لقيم حدود التقسيم بأن تكون ديناميكية؛ تتطور مع تجمع SQL بمرور الوقت.
الخطوات التالية
لمزيد من المعلومات حول تطوير الجداول، راجع المقالات الموجودة في نظرة عامة على الجدول.