مشاركة عبر

إرشادات التصميم لاستخدام الجداول المنسوخة في تجمع Synapse SQL

  • مقالة

تقدم هذه المقالة توصيات لتصميم جداول منسوخة في مخطط تجمع Synapse SQL. استخدم هذه التوصيات لتحسين أداء الاستعلام عن طريق تقليل حركة البيانات وتعقيد الاستعلام.

المتطلبات الأساسية

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

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

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

ما الجدول المكرر؟

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

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

Replicated table

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

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

  • حجم الجدول على القرص أقل من 2 غيغابايت، بغض النظر عن عدد الصفوف. للعثور على حجم الجدول، يمكنك استخدام الأمر DBCC PDW_SHOWSPACEUSED : DBCC PDW_SHOWSPACEUSED('ReplTableCandidate').
  • يتم استخدام الجدول في الصلات التي قد تتطلب حركة البيانات بخلاف ذلك. عند ضم الجداول التي لم يتم توزيعها في نفس العمود، مثل جدول موزع التجزئة إلى جدول ترتيب دوري، يلزم نقل البيانات لإكمال الاستعلام. إذا كان أحد الجداول صغيراً، ففكر في جدول مكرر. نوصي باستخدام الجداول المنسوخة بدلاً من الجداول المستديرة في معظم الحالات. لعرض عمليات نقل البيانات في خطط الاستعلام، استخدم sys.dm_pdw_request_steps. إن عملية BroadcastMoveOperation هي عملية نقل البيانات النموذجية التي يمكن التخلص منها باستخدام جدول منسوخ.

قد لا تقدم الجداول المنسوخة أفضل أداء للاستعلام عندما:

  • يحتوي الجدول على عمليات إدراج وتحديث وحذف متكررة. تتطلب عمليات لغة معالجة البيانات (DML) إعادة إنشاء الجدول المنسوخ نسخًا متماثلاً. يمكن أن تؤدي إعادة البناء بشكل متكرر إلى أداء أبطأ.
  • يتم تغيير حجم تجمع SQL بشكل متكرر. يؤدي تغيير حجم تجمع SQL إلى تغيير عدد عقد الحساب، مما يؤدي إلى إعادة إنشاء الجدول المنسوخ نسخًا متماثلاً.
  • يحتوي الجدول على عدد كبير من الأعمدة، ولكن عمليات البيانات عادة ما تصل إلى عدد صغير فقط من الأعمدة. في هذا السيناريو، بدلاً من النسخ المتماثل للجدول بأكمله، قد يكون توزيع الجدول أكثر فعالية، ثم إنشاء فهرس على الأعمدة التي يتم الوصول إليها بشكل متكرر. عندما يتطلب الاستعلام نقل البيانات، يقوم تجمع SQL بنقل البيانات للأعمدة المطلوبة فقط.

تلميح

لمزيد من الإرشادات حول الفهرسة والجداول المنسوخة نسخا متماثلا، راجع ورقة المعلومات المرجعية لتجمع SQL المخصص (المعروف سابقا ب SQL DW) في Azure Synapse Analytics.

استخدم جداول منسوخة مع مسندات استعلام بسيطة

قبل أن تختار توزيع جدول أو نسخه، فكر في أنواع الاستعلامات التي تخطط لتشغيلها مقابل الجدول. متى أمكن،

  • استخدم الجداول المنسوخة للاستعلامات ذات المسندات الخاصة بالاستعلام البسيط، مثل المساواة أو عدم المساواة.
  • استخدم الجداول الموزعة للاستعلامات ذات المسندات المعقدة للاستعلام، مثل LIKE أو NOT LIKE.

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

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

SELECT EnglishProductName
FROM DimProduct
WHERE EnglishDescription LIKE '%frame%comfortable%';

تحويل الجداول المستديرة الموجودة إلى جداول منسوخة

إذا كان لديك بالفعل جداول روبن مستديرة، نوصي بتحويلها إلى جداول منسوخة إذا كانت تفي بالمعايير الموضحة في هذه المقالة. تعمل الجداول المنسوخة على تحسين الأداء عبر الجداول المستديرة لأنها تلغي الحاجة إلى نقل البيانات. يتطلب جدول round-robin دائماً حركة البيانات من أجل الصلات.

يستخدم هذا المثال CTAS to change the DimSalesTerritory إلى جدول منسوخ. يعمل هذا المثال بغض النظر عما إذا كان DimSalesTerritory موزعاً تجزئة أم دائرياً.

CREATE TABLE [dbo].[DimSalesTerritory_REPLICATE]
WITH
  (
    HEAP,  
    DISTRIBUTION = REPLICATE  
  )  
AS SELECT * FROM [dbo].[DimSalesTerritory]
OPTION  (LABEL  = 'CTAS : DimSalesTerritory_REPLICATE')

-- Switch table names
RENAME OBJECT [dbo].[DimSalesTerritory] to [DimSalesTerritory_old];
RENAME OBJECT [dbo].[DimSalesTerritory_REPLICATE] TO [DimSalesTerritory];

DROP TABLE [dbo].[DimSalesTerritory_old];

مثال على أداء الاستعلام لـ round-robin مقابل النسخ المتماثل

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

على سبيل المثال، في الاستعلام التالي مقابل مخطط AdventureWorks schema, the FactInternetSales بطريقة التجزئة. يعد الجدولان DimDate and DimSalesTerritory جداول أبعاد أصغر. يُرجع هذا الاستعلام إجمالي المبيعات في أمريكا الشمالية للسنة المالية 2004:

SELECT [TotalSalesAmount] = SUM(SalesAmount)
FROM dbo.FactInternetSales s
INNER JOIN dbo.DimDate d
  ON d.DateKey = s.OrderDateKey
INNER JOIN dbo.DimSalesTerritory t
  ON t.SalesTerritoryKey = s.SalesTerritoryKey
WHERE d.FiscalYear = 2004
  AND t.SalesTerritoryGroup = 'North America'

لقد أعدنا إنشاء DimDate وDimSalesTerritory كطاولات دائرية. نتيجة لذلك، أظهر الاستعلام خطة الاستعلام التالية، والتي تحتوي على عمليات نقل بث متعددة:

Round-robin query plan

أعدنا إنشاء DimDate وDimSalesTerritory كجداول منسوخة، وقمنا بتشغيل الاستعلام مرة أخرى. خطة الاستعلام الناتجة أقصر بكثير ولا تحتوي على أي حركات بث.

Replicated query plan

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

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

يتم تشغيل عمليات إعادة البناء غير المتزامنة بواسطة الاستعلام الأول مقابل الجدول المنسوخ بعد:

  • يتم تحميل البيانات أو تعديلها
  • يتم تحجيم مثيل Synapse SQL إلى مستوى مختلف
  • تم تحديث تعريف الجدول

لا يلزم إعادة البناء بعد:

  • وقفة العملية
  • استئناف العملية

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

استخدم الفهارس بحذر

تنطبق ممارسات الفهرسة القياسية على الجداول المنسوخة. SQL pool يعيد بناء كل فهرس جدول منسوخ كجزء من إعادة البناء. استخدم الفهارس فقط عندما يفوق مكاسب الأداء تكلفة إعادة بناء الفهارس.

تحميل البيانات دفعة

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

على سبيل المثال، يقوم نمط التحميل هذا بتحميل البيانات من أربعة مصادر ويستدعي أربع عمليات إعادة بناء.

  • تحميل من المصدر 1.
  • حدد بيان المشغلات إعادة بناء 1.
  • تحميل من المصدر 2.
  • حدد بيان المشغلات إعادة بناء 2.
  • تحميل من المصدر 3.
  • حدد بيان المشغلات إعادة بناء 3.
  • تحميل من المصدر 4.
  • حدد بيان المشغلات إعادة بناء 4.

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

  • تحميل من المصدر 1.
  • تحميل من المصدر 2.
  • تحميل من المصدر 3.
  • تحميل من المصدر 4.
  • حدد بيان المشغلات إعادة البناء.

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

لضمان تناسق أوقات تنفيذ الاستعلام، ضع في اعتبارك فرض إنشاء الجداول المنسوخة بعد تحميل الدُفعات. وإلا، فسيظل الاستعلام الأول يستخدم حركة البيانات لإكمال الاستعلام.

يمكن تنفيذ عملية "إنشاء ذاكرة التخزين المؤقت للجداول المنسوخة نسخا متماثلا" ما يصل إلى عمليتين في وقت واحد. على سبيل المثال، إذا حاولت إعادة إنشاء ذاكرة التخزين المؤقت لخمسة جداول، فسيستخدم النظام staticrc20 (الذي لا يمكن تعديله) لإنشاء جدولين في نفس الوقت. لذلك، يوصى بتجنب استخدام جداول كبيرة منسوخة نسخا متماثلا تتجاوز 2 غيغابايت، حيث قد يؤدي ذلك إلى إبطاء إعادة إنشاء ذاكرة التخزين المؤقت عبر العقد وزيادة الوقت الإجمالي.

يستخدم هذا الاستعلام sys.pdw_replicated_table_cache_state DMV لسرد الجداول المنسوخة التي تم تعديلها، ولكن لم يتم إعادة بنائها.

SELECT SchemaName = SCHEMA_NAME(t.schema_id)
 , [ReplicatedTable] = t.[name]
 , [RebuildStatement] = 'SELECT TOP 1 * FROM ' + '[' + SCHEMA_NAME(t.schema_id) + '].[' + t.[name] +']'
FROM sys.tables t 
JOIN sys.pdw_replicated_table_cache_state c 
  ON c.object_id = t.object_id
JOIN sys.pdw_table_distribution_properties p
  ON p.object_id = t.object_id
WHERE c.[state] = 'NotReady'
AND p.[distribution_policy_desc] = 'REPLICATE'

لتشغيل إعادة بناء، قم بتشغيل العبارة التالية على كل جدول في الإخراج السابق.

SELECT TOP 1 * FROM [ReplicatedTable]

إشعار

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

مثال: ابدأ ب UPDATE STATISTICS، ثم قم بتشغيل إعادة إنشاء ذاكرة التخزين المؤقت. في الأمثلة التالية، يقوم النموذج الصحيح بتحديث الإحصائيات ثم يؤدي إلى إعادة إنشاء ذاكرة التخزين المؤقت.

-- Incorrect sequence. Ensure that the rebuild operation is the last statement within the batch.
BEGIN
SELECT TOP 1 * FROM [ReplicatedTable]

UPDATE STATISTICS [ReplicatedTable]
END

-- Correct sequence. Ensure that the rebuild operation is the last statement within the batch.
BEGIN
UPDATE STATISTICS [ReplicatedTable]

SELECT TOP 1 * FROM [ReplicatedTable]
END

لمراقبة عملية إعادة البناء، يمكنك استخدام sys.dm_pdw_exec_requests، حيث command سيبدأ ب "BuildReplicatedTableCache". على سبيل المثال:

-- Monitor Build Replicated Cache
SELECT *
FROM sys.dm_pdw_exec_requests
WHERE command like 'BuildReplicatedTableCache%'

تلميح

يمكن استخدام استعلامات حجم الجدول للتحقق من الجدول (الجداول) الذي يحتوي على نهج توزيع منسوخ نسخا متماثلا وأكبر من 2 غيغابايت.

الخطوات التالية

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

للحصول على نظرة عامة حول الجداول الموزعة، راجع الجداول الموزعة.