مشاركة عبر

ضبط الأداء مع فهرس تخزين الأعمدة المجمعة مرتب في Azure Synapse Analytics

  • مقالة

ينطبق على: تجمعات SQL المخصصة ل Azure Synapse Analytics

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

إشعار

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

مؤشر تخزين الأعمدة العنقودي المرتب مقابل غير المرتب

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

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

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

SELECT o.name, pnp.index_id,
cls.row_count, pnp.data_compression_desc,
pnp.pdw_node_id, pnp.distribution_id, cls.segment_id,
cls.column_id,
cls.min_data_id, cls.max_data_id,
cls.max_data_id-cls.min_data_id as difference
FROM sys.pdw_nodes_partitions AS pnp
   JOIN sys.pdw_nodes_tables AS Ntables ON pnp.object_id = NTables.object_id AND pnp.pdw_node_id = NTables.pdw_node_id
   JOIN sys.pdw_table_mappings AS Tmap  ON NTables.name = TMap.physical_name AND substring(TMap.physical_name,40, 10) = pnp.distribution_id
   JOIN sys.objects AS o ON TMap.object_id = o.object_id
   JOIN sys.pdw_nodes_column_store_segments AS cls ON pnp.partition_id = cls.partition_id AND pnp.distribution_id  = cls.distribution_id
JOIN sys.columns as cols ON o.object_id = cols.object_id AND cls.column_id = cols.column_id
WHERE o.name = '<Table Name>' and cols.name = '<Column Name>'  and TMap.physical_name  not like '%HdTable%'
ORDER BY o.name, pnp.distribution_id, cls.min_data_id;

إشعار

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

أداء الاستعلام

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

عادةً ما تعمل الاستعلامات التي تحتوي على كل هذه الأنماط بشكل أسرع باستخدام CCI المطلوبة.

  1. تحتوي الاستعلامات على مساواة أو عدم مساواة أو مسندات النطاق
  2. أعمدة المسند وأعمدة CCI المرتبة هي نفسها.

في هذا المثال، يحتوي الجدول T1 على مؤشر تخزين الأعمدة متفاوت المرتبة بترتيب تسلسل Col_C و Col_B وCol_A.

CREATE CLUSTERED COLUMNSTORE INDEX MyOrderedCCI ON  T1
ORDER (Col_C, Col_B, Col_A);

يمكن أن يستفيد أداء الاستعلام 1 والاستعلام 2 من CCI مرتبة أكثر من الاستعلامات الأخرى، لأنها تشير إلى جميع أعمدة CCI مرتبة.

-- Query #1:

SELECT * FROM T1 WHERE Col_C = 'c' AND Col_B = 'b' AND Col_A = 'a';

-- Query #2

SELECT * FROM T1 WHERE Col_B = 'b' AND Col_C = 'c' AND Col_A = 'a';

-- Query #3
SELECT * FROM T1 WHERE Col_B = 'b' AND Col_A = 'a';

-- Query #4
SELECT * FROM T1 WHERE Col_A = 'a' AND Col_C = 'c';

أداء تحميل البيانات

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

فيما يلي مثال على مقارنة الأداء لتحميل البيانات في جداول ذات مخططات مختلفة.

رسم بياني شريطي يعرض مقارنة أداء تحميل البيانات في جداول بمخططات مختلفة.

فيما يلي مثال على مقارنة أداء الاستعلام بين CCI وCCI مرتب.

رسم بياني شريطي يقارن الأداء أثناء data_loading. مؤشر تخزين الأعمدة المجمعة مرتب له مدة أقل.

تخفيض تداخل المقطع

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

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

  • إنشاء CCI مرتب باستخدام OPTION (MAXDOP = 1). يعمل كل مؤشر ترابط مستخدم لإنشاء CCI مرتب على مجموعة فرعية من البيانات ويفرزها محليًا. لا يوجد فرز عام عبر البيانات التي تم فرزها حسب سلاسل الرسائل المختلفة. يمكن أن يؤدي استخدام المحادثة النصية إلى تقليل الوقت اللازم لإنشاء CCI مرتب، ولكنه سيؤدي إلى إنشاء مقاطع متداخلة أكثر من استخدام مؤشر ترابط واحد. يوفر استخدام عملية مترابطة واحدة أعلى جودة ضغط. على سبيل المثال:

CREATE TABLE Table1 WITH (DISTRIBUTION = HASH(c1), CLUSTERED COLUMNSTORE INDEX ORDER(c1) )
AS SELECT * FROM ExampleTable
OPTION (MAXDOP 1);

إشعار

حاليا، في تجمعات SQL المخصصة في Azure Synapse Analytics، يتم دعم خيار MAXDOP فقط في إنشاء جدول CCI مرتب باستخدام CREATE TABLE AS SELECT الأمر . لا يدعم إنشاء CCI مرتب عبر CREATE INDEX أو CREATE TABLE الأوامر خيار MAXDOP. لا ينطبق هذا القيد على SQL Server 2022 والإصدارات الأحدث، حيث يمكنك تحديد MAXDOP مع CREATE INDEX الأوامر أو CREATE TABLE .

  • فرز البيانات مسبقًا حسب مفتاح (مفاتيح) الفرز قبل تحميلها في الجداول.

فيما يلي مثال لتوزيع جدول CCI مرتب يحتوي على صفر مقطع متداخل باتباع التوصيات أعلاه. يتم إنشاء جدول CCI مرتب في قاعدة بيانات DWU1000c عبر CTAS من جدول كومة الذاكرة المؤقتة 20 غيغابايت باستخدام MAXDOP 1 و xlargerc. يتم ترتيب CCI على عمود عدد صحيح كبير بدون تكرارات.

لقطة شاشة لبيانات نصية تظهر عدم تداخل أي مقطع.

إنشاء CCI مرتب في جداول كبيرة

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

  1. إنشاء أقسام في الجدول الكبير الهدف (باسمTable_A).
  2. إنشاء جدول CCI مرتب فارغ (باسم Table_B) في نفس الجدول ومخطط القسم مثل الجدول Table_A.
  3. قم باستبدال قسم واحد من Table_A إلى Table_B.
  4. قم بتشغيل ALTER INDEX <Ordered_CCI_Index> ON <Table_B> REBUILD PARTITION = <Partition_ID> لإعادة إنشاء القسم الذي تم تبديله على Table_B.
  5. كرّر الخطوة 3 و4 لكل قسم في الجدول Table_A.
  6. بمجرد تبديل جميع الأقسام من Table_A إلى Table_B وإعادة إنشائها، قم بإسقاط Table_A، وأعد تسمية Table_B إلى Table_A.

تلميح

بالنسبة لجدول تجمع SQL مخصص مع CCI مرتب، سيقوم ALTER INDEX REBUILD بإعادة فرز البيانات باستخدام tempdb. قم بمراقبة tempdbأثناء عمليات إعادة البناء. إذا كنت بحاجة إلى مساحة إضافية في tempdb، قم بتغيير حجم التجمع. قم بتغيير السعة مرة أخرى بمجرد اكتمال إعادة إنشاء الفهرس.

وبالنسبة لجدول تجمع SQL مخصص مع CCI مرتب، سيقوم ALTER INDEX REORGANIZE بإعادة فرز البيانات باستخدام tempdb. لاستعادة البيانات، استخدم ALTER INDEX REBUILD.

لمزيد من المعلومات حول صيانة CCI مرتب، راجع تحسين مؤشرات تخزين الأعمدة المجمعة.

اختلافات مزايا قدرات SQL Server 2022

قدّم SQL Server 2022 (16.x) مؤشرات تخزين الأعمدة مجمعة ومرتبة تشبه ميزة لدى تجمعات SQL المخصصة ل Azure Synapse.

  • حاليا، يدعم فقط SQL Server 2022 (16.x) والإصدارات الأحدث قدرات استبعاد مقطع تخزين الأعمدة المحسنة لسلسلة وأنواع البيانات الثنائية والموجهة ونوع بيانات datetimeoffset للمقياس الأكبر من اثنين. في السابق، ينطبق استبعاد المقطع هذا على أنواع البيانات الرقمية والتاريخ والوقت ونوع البيانات datetimeoffset مع مقياس أقل من أو يساوي اثنين.
  • حاليا، يدعم فقط SQL Server 2022 (16.x) والإصدارات الأحدث استبعاد مجموعة صفوف تخزين الأعمدة المجمعة لبادئة LIKE البادئات، على سبيل المثال column LIKE 'string%'. إزالة المقطع غير معتمدة للاستخدام غير البادئة ل LIKE مثل column LIKE '%string'.

لمزيد من المعلومات، راجع مستجدات مؤشرات تخزين الأعمدة.

الأمثلة

أ. لفحص وجود أعمدة مرتبة وترتيب منظم:

SELECT object_name(c.object_id) table_name, c.name column_name, i.column_store_order_ordinal
FROM sys.index_columns i
JOIN sys.columns c ON i.object_id = c.object_id AND c.column_id = i.column_id
WHERE column_store_order_ordinal <>0;

ب. لتغيير ترتيب العمود، أضف أعمدة أو أزلها من قائمة الترتيب، أو للتغيير من CCI عادي إلى CCI مرتب:

CREATE CLUSTERED COLUMNSTORE INDEX InternetSales ON dbo.InternetSales
ORDER (ProductKey, SalesAmount)
WITH (DROP_EXISTING = ON);

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