إرشادات تصميم الجداول الموزعة باستخدام تجمع SQL مخصص في Azure Synapse Analytics

تحتوي هذه المقالة على توصيات لتصميم جداول التجزئة الموزعة والجداول الموزعة بطريقة راوند روبن في تجمعات SQL مخصصة.

تفترض هذه المقالة أنك على دراية بمفاهيم توزيع البيانات وحركة البيانات في تجمع SQL المخصص. لمزيد من المعلومات، راجع بنية Azure Synapse Analytics.

ما هو الجدول الموزع؟

يظهر الجدول الموزع كجدول واحد، ولكن يتم تخزين الصفوف فعليًا عبر 60 توزيعًا. يتم توزيع الصفوف باستخدام خوارزمية تجزئة أو خوارزمية راوند روبن.

يعمل توزيع التجزئة على تحسين أداء الاستعلام على جداول الحقائق الكبيرة، وهو محور تركيز هذه المقالة. التوزيع بطريقة رواند روبن مفيد لتحسين سرعة التحميل. تتمتع خيارات التصميم هذه بتأثير كبير على تحسين أداء الاستعلام والتحميل.

يتمثَّل الخيار الآخر لتخزين جدول في تكرار جدول صغير عبر جميع عقد الحوسبة. لمزيد من المعلومات، راجع إرشادات التصميم للجداول المنسوخة. للاختيار بسرعة من بين الخيارات الثلاثة، راجع الجداول الموزعة في نظرة عامة على الجداول.

كجزء من تصميم الجدول، افهم قدر الإمكان عن بياناتك وكيفية الاستعلام عنها. على سبيل المثال، ضع في اعتبارك الأسئلة التالية:

• ما هو حجم الجدول؟
• كم مرة يتم تحديث الجدول؟
• هل لديَّ جداول الحقائق والأبعاد في تجمع SQL مخصص؟

جدول التجزئة الموزع

يوزع جدول التجزئة الموزع صفوف الجدول عبر عقد الحوسبة باستخدام دالة تجزئة محددة لتعيين كل صف إلى توزيع واحد.

نظرًا إلى أن القيم المتطابقة دائمًا ما تتم تجزئتها إلى نفس التوزيع، تمتلك خدمة SQL Analytics معرفة مضمنة بمواقع الصفوف. في تجمع SQL المخصص، تُستخدم هذه المعرفة للتقليل إلى أدنى حد من حركة البيانات في أثناء الاستعلامات، ما يحسن أداء الاستعلام.

تعمل جداول التجزئة الموزعة بشكل جيد لجداول الحقائق الكبيرة في مخطط نجمي. يمكن أن تحتوي على أعداد كبيرة جدًا من الصفوف ولا تزال تحقق أداءً عاليًا. هناك بعض اعتبارات التصميم التي تساعدك في الحصول على الأداء الذي صمم النظام الموزع لتوفيره. يعد اختيار عمود أو أعمدة توزيع جيدة أحد الاعتبارات الموضحة في هذه المقالة.

ضع في اعتبارك استخدام جدول التجزئة الموزع في الحالات الآتية:

• حجم الجدول على القرص أكثر من 2 غيغابايت.
• يحتوي الجدول على عمليات إدراج وتحديث وحذف متكررة.

الجدول الموزع بطريقة راوند روبن

يوزع الجدول الموزع بطريقة راوند روبن صفوف الجدول بالتساوي عبر جميع التوزيعات. يكون تعيين الصفوف للتوزيعات عشوائيًا. بخلاف جداول التجزئة الموزعة، لا يمكن ضمان تعيين الصفوف ذات القيم المتساوية لنفس التوزيع.

ونتيجةً لذلك، يحتاج النظام في بعض الأحيان إلى استدعاء عملية حركة البيانات لتنظيم بياناتك بشكل أفضل قبل أن يتمكن من حل استعلام. يمكن أن تؤدي هذه الخطوة الإضافية إلى إبطاء استعلاماتك. على سبيل المثال، عادةً ما يتطلب ربط جدول راوند روبن إعادة ترتيب الصفوف، ما يؤثر سلبًا على الأداء.

ضع في اعتبارك استخدام توزيع راوند روبن لجدولك في السيناريوهات التالية:

• عند البدء كنقطة بداية بسيطة لأنها الخيار الافتراضي
• إذا لم يكن هناك مفتاح ربط واضح
• إذا لم يكن هناك عمود مرشح جيد لتجزئة توزيع الجدول
• إذا كان الجدول لا يشترك في مفتاح ربط مشترك مع جداول أخرى
• إذا كانت عملية الربط أقل أهمية من عمليات الربط الأخرى في الاستعلام
• عندما يكون الجدول جدولاً مرحليًا مؤقتًا

يقدم البرنامج التعليمي تحميل بيانات سيارة الأجرة في نيويورك مثالاً على تحميل البيانات في جدول مرحلي بطريقة راوند روبن.

اختيار عمود التوزيع

يحتوي جدول التجزئة الموزع على عمود توزيع أو مجموعة أعمدة تمثل مفتاح التجزئة. على سبيل المثال، تُنشئ التعليمة البرمجية التالية جدول تجزئة موزع مع ProductKey بصفتها عمود توزيع.

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])
);

يمكن تطبيق توزيع التجزئة على أعمدة متعددة لتوزيع أكثر التساوي للجدول الأساسي. يسمح لك التوزيع متعدد الأعمدة باختيار ما يصل إلى ثمانية أعمدة للتوزيع. لا يقلل هذا من انحراف البيانات بمرور الوقت فحسب، بل يحسن أيضا أداء الاستعلام. على سبيل المثال:

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],   [OrderDateKey],   [CustomerKey] ,   [PromotionKey])
);

إشعار

يمكن تمكين التوزيع متعدد الأعمدة في Azure Synapse Analytics عن طريق تغيير مستوى توافق قاعدة البيانات إلى 50 مع هذا الأمر. ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = 50; لمزيد من المعلومات حول تعيين مستوى توافق قاعدة البيانات، راجع ALTER DATABASE SCOPED CONFIGURATION. لمزيد من المعلومات حول التوزيعات متعددة الأعمدة، راجع إنشاء طريقة عرض مجسدة أو إنشاء جدول أو إنشاء جدول AS SELECT.

يمكن تحديث البيانات المخزنة في أعمدة التوزيع. يمكن أن تؤدي تحديثات البيانات في أعمدة التوزيع إلى عملية تبديل البيانات.

اختيار أعمدة التوزيع هو قرار تصميم مهم لأن القيم في أعمدة التجزئة تحدد كيفية توزيع الصفوف. يعتمد الخيار الأفضل على عدة عوامل، وعادة ما يتضمن المفاضلات. لا يمكنك تغيير عمود التوزيع أو مجموعة الأعمدة بمجرد اختيارها. إذا لم تخن أفضل الأعمدة في المرة الأولى، يمكنك استخدام CREATE TABLE AS SELECT (CTAS) لإعادة إنشاء الجدول باستخدام مفتاح تجزئة التوزيع المطلوب.

اختر عمود توزيع بالبيانات التي توزع بالتساوي

للحصول على أفضل أداء، يجب أن تحتوي جميع التوزيعات على نفس عدد الصفوف تقريبًا. عندما يكون لواحد أو أكثر من التوزيعات عدد غير متناسب من الصفوف، فإن بعض التوزيعات تُنهي الجزء الخاص بها من الاستعلام الموازي قبل البعض الآخر. نظرًا إلى أن الاستعلام لا يمكن أن يكتمل حتى تنتهي جميع التوزيعات من المعالجة، فإن كل استعلام يكون فقط بنفس سرعة أبطأ توزيع.

• انحراف البيانات يعني أن البيانات لا يتم توزيعها بالتساوي عبر التوزيعات
• يعني انحراف المعالجة أن بعض التوزيعات تستغرق وقتًا أطول من غيرها عند تشغيل استعلامات متوازية. يمكن أن يحدث هذا عندما تكون البيانات منحرفة.

لموازنة المعالجة المتوازية، حدد عمود التوزيع أو مجموعة الأعمدة التي:

• يحتوي على العديد من القيم الفريدة. يمكن أن يحتوي عمود توزيع واحد أو أكثر على قيم مكررة. يتم تعيين نفس التوزيع لجميع الصفوف التي لها نفس القيمة. نظرا لوجود 60 توزيعا، يمكن أن تحتوي بعض التوزيعات على > قيم فريدة بينما يمكن أن ينتهي البعض الآخر بقيم صفرية.
• لا يحتوي على NULL، أو يحتوي على عدد قليل فقط من NULL. على سبيل المثال، إذا كانت جميع القيم في أعمدة التوزيع فارغة، يتم تعيين كافة الصفوف إلى نفس التوزيع. نتيجة لذلك، تنحرف معالجة الاستعلام إلى توزيع واحد، ولا تستفيد من المعالجة المتوازية.
• ليس عمود تاريخ. تُوجد كافة البيانات لنفس التاريخ في نفس التوزيع، أو ستُجمع السجلات حسب التاريخ. إذا كان العديد من المستخدمين يقومون بالتصفية في نفس التاريخ (مثل تاريخ اليوم)، فإن توزيع واحد فقط من بين 60 توزيعا يقوم بجميع أعمال المعالجة.

اختيار عمود توزيع يقلل إلى أدنى حد من حركة البيانات

للحصول على نتيجة الاستعلام الصحيحة، قد تنقل الاستعلامات البيانات من عقدة حوسبة إلى أخرى. تحدث حركة البيانات بشكل شائع عندما تحتوي الاستعلامات على عمليات ربط وتجميعات في الجداول الموزعة. يعد اختيار عمود التوزيع أو مجموعة الأعمدة التي تساعد في تقليل حركة البيانات أحد أهم الاستراتيجيات لتحسين أداء تجمع SQL المخصص.

لتقليل حركة البيانات، حدد عمود التوزيع أو مجموعة الأعمدة التي:

• يتم استخدامه في العبارات JOIN وGROUP BY وDISTINCT وOVER وHAVING. عندما يكون لجدولي حقائق كبيرين عمليات ربط متكررة، يتحسن أداء الاستعلام عند توزيع كلا الجدولين على أحد أعمدة الربط. عندما لا يُستخدم جدول في الصلات، فيُرجى مراعاة توزيع الجدول على عمود موجود أو مجموعة أعمدة بشكل متكرر في عبارة GROUP BY.
• لا يتم استخدامه في العبارة WHERE. عندما تكون عبارة استعلام WHERE وأعمدة توزيع الجدول في نفس العمود، قد يواجه الاستعلام انحرافا كبيرا في البيانات، مما يؤدي إلى انخفاض التحميل في توزيعات قليلة فقط. يؤثر هذا على أداء الاستعلام، ومن الناحية المثالية، تشترك العديد من التوزيعات في تحميل المعالجة.
• لا يُعد عمود تاريخ. غالبًا ما تتم تصفية عبارات WHERE حسب التاريخ. عند حدوث ذلك، يمكن تشغيل جميع المعالجات على عدد قليل من التوزيعات التي تؤثر على أداء الاستعلام. من الناحية المثالية، تشترك العديد من التوزيعات في تحميل المعالجة.

بمجرد تصميم جدول تجزئة موزع، تكون الخطوة التالية هي تحميل البيانات في الجدول. للحصول على إرشادات التحميل، راجع نظرة عامة على التحميل.

كيف تعرف ما إذا كان التوزيع هو الاختيار المناسب

بعد تحميل البيانات في جدول تجزئة موزع، تحقق لمعرفة كيفية توزيع الصفوف بالتساوي عبر 60 توزيعًا. يمكن أن تختلف الصفوف لكل توزيع حتى 10% بدون تأثير ملحوظ على الأداء.

ضع في اعتبارك الطرق التالية لتقييم أعمدة التوزيع.

تحديد ما إذا كان الجدول يحتوي على انحراف في البيانات

تتمثل طريقة سريعة للتحقق من انحراف البيانات في استخدام DBCC PDW_SHOWSPACEUSED. تعمل تعليمة SQL البرمجية التالية على إرجاع عدد صفوف الجدول المخزنة في كل من التوزيعات البالغة 60. وللحصول على أداء متوازن، ينبغي مد الصفوف في جدولك الموزع بالتساوي عبر جميع التوزيعات.

-- Find data skew for a distributed table
DBCC PDW_SHOWSPACEUSED('dbo.FactInternetSales');

لتحديد الجداول التي تحتوي على أكثر من 10% من الانحراف في البيانات:

1. أنشئ العرض dbo.vTableSizes الموضح في مقالة نظرة عامة على الجداول.
2. قم بتشغيل الاستعلام التالي:

select *
from dbo.vTableSizes
where two_part_name in
    (
    select two_part_name
    from dbo.vTableSizes
    where row_count > 0
    group by two_part_name
    having (max(row_count * 1.000) - min(row_count * 1.000))/max(row_count * 1.000) >= .10
    )
order by two_part_name, row_count;

التحقق من خطط الاستعلام لحركة البيانات

تُمكّن مجموعة أعمدة توزيع جيدة عمليات الصلات والتجميعات من الحصول على الحد الأدنى من حركة البيانات. يؤثر هذا على طريقة كتابة عمليات الربط. للحصول على الحد الأدنى من حركة البيانات للصلة على جدولين موزعين على التجزئة، يجب أن يكون أحد أعمدة الصلة في عمود التوزيع أو الأعمدة. عند ربط جدولي تجزئة موزعين في عمود توزيع من نفس نوع البيانات، لا تتطلب عملية الربط حركة البيانات. يمكن أن تستخدم عمليات الربط أعمدة إضافية دون تكبد حركة البيانات.

لتجنب حركة البيانات في أثناء عملية الربط:

• يجب توزيع جداول التجزئة المضمنة في عملية الربط على واحد من الأعمدة المشاركة في عملية الربط.
• يجب أن تتطابق أنواع بيانات أعمدة الربط بين كلا الجدولين.
• يجب ربط الأعمدة بعامل تشغيل مساوٍ.
• لا يمكن أن يكون نوع الصلة .CROSS JOIN

لمعرفة ما إذا كانت الاستعلامات تواجه حركة البيانات، يمكنك إلقاء نظرة على خطة الاستعلام.

حل مشكلة عمود التوزيع

ليس من الضروري حل جميع حالات انحراف البيانات. يُعد توزيع البيانات مسألة إيجاد التوازن الصحيح بين التقليل إلى أدنى حد من انحراف البيانات وحركة البيانات. ليس من الممكن دائمًا التقليل إلى أدنى حد من كل من انحراف البيانات وحركة البيانات. في بعض الأحيان قد تفوق فائدة وجود الحد الأدنى من حركة البيانات تأثير انحراف البيانات.

لتحديد ما إذا كان يجب عليك حل انحراف البيانات في جدول، يجب أن تفهم قدر الإمكان أحجام البيانات والاستعلامات في حمل العمل لديك. يمكنك استخدام الخطوات الواردة في مقالة مراقبة الاستعلام لمراقبة تأثير الانحراف على أداء الاستعلام. على وجه التحديد، ابحث عن المدة التي تستغرقها الاستعلامات الكبيرة للإكمال في التوزيعات الفردية.

نظرا لأنه لا يمكنك تغيير أعمدة التوزيع في جدول موجود، فإن الطريقة النموذجية لحل انحراف البيانات هي إعادة إنشاء الجدول بأعمدة توزيع مختلفة.

إعادة إنشاء الجدول باستخدام مجموعة أعمدة توزيع جديدة

يستخدم هذا المثال CREATE TABLE AS SELECT لإعادة إنشاء جدول بأعمدة توزيع تجزئة مختلفة.

استخدم أولًا CREATE TABLE AS SELECT (CTAS) للجدول الجديد مع المفتاح الجديد. ثم أعد إنشاء الإحصائيات وأخيرا، قم بتبديل الجداول عن طريق إعادة تسميتها.

CREATE TABLE [dbo].[FactInternetSales_CustomerKey]
WITH (  CLUSTERED COLUMNSTORE INDEX
     ,  DISTRIBUTION =  HASH([CustomerKey])
     ,  PARTITION       ( [OrderDateKey] RANGE RIGHT FOR VALUES (   20000101, 20010101, 20020101, 20030101
                                                                ,   20040101, 20050101, 20060101, 20070101
                                                                ,   20080101, 20090101, 20100101, 20110101
                                                                ,   20120101, 20130101, 20140101, 20150101
                                                                ,   20160101, 20170101, 20180101, 20190101
                                                                ,   20200101, 20210101, 20220101, 20230101
                                                                ,   20240101, 20250101, 20260101, 20270101
                                                                ,   20280101, 20290101
                                                                )
                        )
    )
AS
SELECT  *
FROM    [dbo].[FactInternetSales]
OPTION  (LABEL  = 'CTAS : FactInternetSales_CustomerKey')
;

--Create statistics on new table
CREATE STATISTICS [ProductKey] ON [FactInternetSales_CustomerKey] ([ProductKey]);
CREATE STATISTICS [OrderDateKey] ON [FactInternetSales_CustomerKey] ([OrderDateKey]);
CREATE STATISTICS [CustomerKey] ON [FactInternetSales_CustomerKey] ([CustomerKey]);
CREATE STATISTICS [PromotionKey] ON [FactInternetSales_CustomerKey] ([PromotionKey]);
CREATE STATISTICS [SalesOrderNumber] ON [FactInternetSales_CustomerKey] ([SalesOrderNumber]);
CREATE STATISTICS [OrderQuantity] ON [FactInternetSales_CustomerKey] ([OrderQuantity]);
CREATE STATISTICS [UnitPrice] ON [FactInternetSales_CustomerKey] ([UnitPrice]);
CREATE STATISTICS [SalesAmount] ON [FactInternetSales_CustomerKey] ([SalesAmount]);

--Rename the tables
RENAME OBJECT [dbo].[FactInternetSales] TO [FactInternetSales_ProductKey];
RENAME OBJECT [dbo].[FactInternetSales_CustomerKey] TO [FactInternetSales];

المحتوى ذو الصلة

لإنشاء جدول موزع، استخدم إحدى العبارات التالية:

• CREATE TABLE (تجمع SQL مخصص)
• CREATE TABLE AS SELECT (تجمع SQL مخصص)