5 الخارقة الحياة لتحسين استعلامات SQL في Greenplum

أي عمليات متعلقة بقاعدة البيانات ، تواجه عاجلاً أم آجلاً مشاكل مع أداء الاستعلامات في قاعدة البيانات هذه.

تم بناء مستودع بيانات Rostelecom على Greenplum ، حيث يتم تنفيذ معظم الحسابات (التحويل) بواسطة استعلامات sql ، والتي تبدأ (أو تنشئ وتبدأ) آلية ETL. DBMS لها الفروق الدقيقة التي تؤثر بشكل كبير على الأداء. هذه المقالة هي محاولة لتسليط الضوء على الجوانب الأكثر أهمية للعمل مع Greenplum من حيث الأداء وتبادل الخبرات.


علاوة على ذلك ، سيكون هناك العديد من الإشارات إلى خطة الطلب. معلومات عن Greenplum متاحة هنا .

كيف تكتب استفسارات جيدة عن Greenplum (جيدًا ، أو على الأقل ليست حزينة جدًا)

نظرًا لأننا نتعامل مع قاعدة بيانات موزعة ، فمن المهم ليس فقط كيفية كتابة استعلام sql ، ولكن أيضًا كيفية تخزين البيانات.

1. التوزيع

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

بناء الجملة (عند إنشاء جدول):

DISTRIBUTED BY (some_field) 

أو هكذا:

 DISTRIBUTED RANDOMLY 

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

يُفضل أن يكون نوع الحقل عددًا صحيحًا. يستخدم الحقل لربط الجداول. تعد Hash join واحدة من أفضل الطرق لربط الجداول (من حيث تنفيذ الاستعلام) ، وتعمل بشكل أفضل مع نوع البيانات هذا.

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

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

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


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

2. التقسيم

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

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

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

حان الوقت للذهاب ، في الواقع ، إلى الطلبات. سيتم تنفيذ الطلب على شرائح وفقًا لخطة محددة:

3. محسن

لدى Greenplum محسّنان ، المحسن القديم المدمج ومحسن Orca من جهة خارجية: GPORCA - Orca - Pivotal Query Optimizer.

تمكين GPORCA عند الطلب:

 set optimizer = on; 

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

يقوم بتعديل خطة الاستعلام قليلاً ، على سبيل المثال ، يعرض الأقسام الممسوحة ضوئيًا:

محسن قياسي:



Orca:



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

بغض النظر عن مدى جودة المُحسِّن ، لن يمتد استعلام مكتوب بشكل سيء Orca:

4. التلاعب مع الحقول في حيث كتلة أو شروط الانضمام

من المهم أن تتذكر أن الوظيفة المطبقة على حقل المرشح أو يتم تطبيق شروط الصلة على كل سجل.

في حالة حقل التقسيم (على سبيل المثال ، date_trunc إلى حقل التقسيم - تاريخ) ، حتى GPORCA لا يمكنها العمل بشكل صحيح في هذه الحالة ، لن تعمل أقسام القطع .

 --     set optimizer = on; explain select * from edw_ods.t_000045_bills c where date_trunc('month',tech_dt) between to_date('20180101', 'YYYYMMDD') and to_date('20180101', 'YYYYMMDD') + interval '1 month - 1 second' ; 

 --     set optimizer = on; explain select * from edw_ods.t_000045_bills c where tech_dt between to_date('20180101', 'YYYYMMDD') and to_date('20180101', 'YYYYMMDD') + interval '1 month - 1 second' 

أود أيضا أن ألفت الانتباه إلى عرض الأقسام. يعرض المُحسّن المدمج أقسامًا في قائمة:



قم بتطبيق الوظائف بدقة على الثوابت الموجودة في نفس عوامل تصفية القسم. مثال على ذلك هو date_trunc نفسه:

 date_trunc('month',to_date($p_some_dt, 'YYYYMMDD')) 

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

 date_trunc('month',to_date($p_some_dt, 'YYYYMMDD'))::timestamp without time zone 

وإذا تم كل شيء خطأ؟

5. الحركات

هناك نوع آخر من العمليات التي يمكن ملاحظتها في خطة الاستعلام وهي الاقتراحات. حركات البيانات الملحوظة بين القطاعات:

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

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

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

أعد هذا المقال فريق Rostelecom لإدارة البيانات