إشعار
يتطلب الوصول إلى هذه الصفحة تخويلاً. يمكنك محاولة تسجيل الدخول أو تغيير الدلائل.
يتطلب الوصول إلى هذه الصفحة تخويلاً. يمكنك محاولة تغيير الدلائل.
توفر طرق العرض الفعلية لمجموعات SQL المخصصة في Azure Synapse طريقة صيانة منخفضة للاستعلامات التحليلية المعقدة للحصول على أداء سريع دون أي تغيير في الاستعلام. تتناول هذه المقالة الإرشادات العامة حول استخدام طرق العرض المُجسّدة.
العروض المجسدة مقابل العروض القياسية
يدعم تجمع SQL المخصص في Azure Synapse طرق العرض القياسية والواقعية. وتعتبر كل منهما جداول ظاهرية تم إنشاؤها باستخدام تعبيرات التحديد وعرضها على الاستعلامات باعتبارها جداول منطقية. تغلف طرق العرض مدى تعقيد حساب البيانات الشائعة وتضيف طبقة تجريدية إلى تغييرات الحساب بحيث لا تكون هناك حاجة لإعادة كتابة الاستعلامات.
يقوم العرض القياسي بحساب بياناته في كل مرة عند استخدام العرض. لا تتوفر بيانات مخزنة على القرص. يستخدم الأشخاص عادةً طرق العرض القياسية كأداة تساعد في تنظيم الكائنات والاستعلامات المنطقية في مجموعة SQL مخصصة. لاستخدام طريقة عرض قياسية، يجب أن يشير الاستعلام مباشرةً إليها.
تقوم طريقة العرض المجسدة بحساب بياناتها مسبقاً وتخزينها والاحتفاظ بها في مجموعة SQL مخصصة مثل الجدول تماماً. ليست هناك حاجة لإجراء إعادة الحساب في كل مرة يتم فيها استخدام طريقة عرض مجسدة. ولهذا السبب يمكن أن تتسم الاستعلامات التي تستخدم كل البيانات أو مجموعة فرعية منها في طرق عرض مجسدة بأداء أسرع. والأفضل من ذلك، يمكن أن تستخدم الاستعلامات عرضًا ملموسًا دون الرجوع إليه بطريقة مباشرة، لذلك ليست هناك حاجة لتغيير رمز التطبيق.
لا تزال معظم المتطلبات في العرض القياسي تنطبق على العرض الفعلي. للحصول على تفاصيل بشأن بنية العرض الفعلية والمتطلبات الأخرى، ارجع إلىإنشاء طريقة العرض كما محدد
| المقارنة | عرض | طريقة العرض الفعلي |
|---|---|---|
| تعريف طريقة العرض | مخزنة في تجمع SQL مخصص. | مخزنة في تجمع SQL مخصص. |
| محتوى طريقة العرض | يتم إنشاؤه في كل مرة يتم فيها استخدام طريقة العرض. | تمت معالجتها مسبقًا وتخزينها في تجمع SQL مخصص أثناء إنشاء العرض. ويتم تحديثه مع إضافة البيانات إلى الجداول الأساسية. |
| تحديث البيانات | يتم تحديثها دائماً | يتم تحديثها دائماً |
| سرعة استرداد بيانات طريقة العرض من الاستعلامات المعقدة | بطيء | سريع |
| التخزين الإضافي | لا | نعم |
| بناء الجملة | إنشاء طريقة العرض | إنشاء طريقة العرض كما محدد |
فوائد استخدام العروض المجسدة
توفر طريقة العرض المجسدة المصممة جيداً المزايا التالية:
- تقليل وقت التنفيذ للاستعلامات المعقدة باستخدام وظائف التجميع والصلات. كلما كان الاستعلام أكثر تعقيداً، زادت إمكانية توفير وقت التنفيذ. يتحقق أقصى قدر من الفائدة عندما تكون تكلفة حساب استعلام ما مرتفعة ومجموعة البيانات الناتجة صغيرة.
- يمكن للمحسن في تجمع SQL المخصص استخدام طرق العرض المادية المنشورة تلقائيًا لتحسين خطط تنفيذ الاستعلام. هذه العملية شفافة للمستخدمين الذين يقدمون أداء استعلام أسرع ولا تتطلب من الاستعلامات الرجوع المباشر إلى طرق العرض المجسدة.
- تتطلب صيانة طفيفة على طرق العرض. تتم إضافة كافة تغييرات البيانات المتزايدة من الجداول الأساسية تلقائيًا إلى طرق العرض المحققة بطريقة متزامنة، مما يعني أنه يتم تحديث كل من الجداول الأساسية ووجهات النظر الفعلية في نفس المعاملة. يسمح هذا التصميم بالاستعلام عن طرق العرض المجسدة لإرجاع نفس البيانات مثل الاستعلام عن الجداول الأساسية مباشرة.
- يمكن توزيع البيانات في طريقة العرض المجسدة بصورة مختلفة عن الجداول الأساسية.
- تتمتع البيانات الواردة في طرق العرض المجسدة بنفس مزايا المرونة والتوافر الكبير التي تتمتع بها البيانات الواردة في الجداول العادية.
توفر العروض الفعلية التي يتم تنفيذها في مجموعة SQL المخصصة أيضًا الفوائد التالية:
مقارنةً بموفري مستودعات البيانات الآخرين، فإن العروض الفعلية التي يتم تنفيذها في تجمع SQL المخصص توفر أيضًا الفوائد التالية:
- الدعم واسع النطاق لوظيفة التجميع. راجع إنشاء طريقة عرض مجسدة كما محدد (Transact-SQL).
- دعم توصية طريقة العرض المجسدة الخاصة بالاستعلام. راجع شرح (Transact-SQL).
- تحديث البيانات التلقائي والمتزامن بتغييرات البيانات في الجداول الأساسية. ولا يلزم القيام بأي إجراء من قبل المستخدم.
السيناريوهات الشائعة
تُستخدم الآراء المحسوسة عادةً في السيناريوهات الآتية:
الحاجة لتحسين أداء الاستعلامات التحليلية المعقدة مقابل البيانات كبيرة الحجم
عادةً ما تستخدم الاستعلامات التحليلية المعقدة المزيد من الوظائف التجميعية والوصلات للجدول، مما يتسبب في المزيد من العمليات الحسابية الثقيلة مثل الخلط والصلات في تنفيذ الاستعلام. بسبب ذلك تستغرق الاستعلامات التحليلية المعقدة وقتًا أطول لإكمالها، خاصة على الجداول الكبيرة.
يمكن للمستخدمين إنشاء طرق عرض محسوسة للبيانات التي يتم إرجاعها من الحسابات الشائعة للاستعلامات، لذلك ليس هناك حاجة لإعادة الحساب عندما تكون هذه البيانات مطلوبة من خلال الاستعلامات، مما يسمح بتكلفة حساب أقل واستجابة أسرع للاستعلام.
تحتاج لأداء أسرع مع عدم وجود تغييرات في الاستعلام أو الحد الأدنى من التغييرات
عادةً ما يتم الاحتفاظ بتغييرات المخطط والاستعلام في تجمعات SQL المخصصة إلى الحد الأدنى لدعم عمليات ETL العادية وإعداد التقارير. يمكن للأشخاص استخدام طرق العرض المحسوسة لضبط أداء الاستعلام، إذا كان من الممكن تعويض التكلفة التي تكبدتها طرق العرض من خلال تحقيق مكاسب في أداء الاستعلام.
بالمقارنة مع خيارات الضبط الأخرى مثل إدارة القياس والإحصاءات، فإنه يعد تغييرًا أقل تأثيرًا في الإنتاج لإنشاء عرض محقق والمحافظة عليه، كما أن مكاسب الأداء المحتملة أعلى أيضًا.
- لا يؤثر إنشاء طرق عرض محسوسة أو الاحتفاظ بها على الاستعلامات المشغلة مقابل الجداول الأساسية.
- يمكن لمحسن الاستعلام استخدام طرق العرض المادية المنشورة تلقائيًا بدون مرجع العرض المباشر للاستعلام. تقوم هذه الإمكانية بالتقليل من الحاجة إلى تغيير الاستعلام في ضبط الأداء.
تحتاج لاستراتيجية توزيع بيانات مختلفة لأداء استعلام أسرع
تجمع SQL المخصص هو نظام معالجة الاستعلام الموزع. توزع البيانات الموجودة في SQL عبر 60 عقدة باستخدام واحدة من ثلاثاستراتيجيات توزيع(التجزئة أو round_robin أو منسوخة).
يتم تحديد توزيع البيانات في وقت إنشاء الجدول ويظل بدون تغيير حتى يسقط الجدول. العرض المتحقق، كونه جدولًا افتراضيًا على القرص، يدعم توزيعات بيانات التجزئة و round_robin. يمكن للمستخدمين اختيار توزيع للبيانات يختلف عن الجداول الأساسية ولكنه مثالي لأداء الاستعلامات التي تستخدم طرق العرض.
إرشادات التصميم
فيما يلي إرشادات عامة بشأن استخدام طرق العرض الفعلية لتحسين أداء الاستعلام:
تصميم لحمل عملك
قبل البدء في إنشاء طرق عرض محسوسة، من المهم أن يكون لديك فهم عميق لأعباء عملك من حيث أنماط الاستعلام والأهمية والتكرار وحجم البيانات الناتجة.
يمكن للمستخدمين تشغيلEXPLAIN WITH_RECOMMENDATIONS <SQL_statement>طرق العرض materializeed الموصى بها من قِبل محسن الاستعلام. ونظراً لأن هذه التوصيات محددة الاستعلامات، فإن طريقة العرض المجسدة التي تُفيد استعلاماً واحداً ربما لا تكون مثالية للاستعلامات الأخرى في حمل العمل ذاته.
تقييم هذه التوصيات مع وضع احتياجات عبء العمل في الاعتبار. وجهات النظر المثالية التي تتحقق هي تلك التي تفيد أداء حمل العمل.
كن حذراً بالمفاضلة بين الاستعلامات الأسرع والتكلفة
لكل عرض محقق، توجد تكلفة لتخزين البيانات وتكلفة للحفاظ على العرض. مع تغير البيانات في الجداول الأساسية، فيزداد حجم العرض المتحقق ويتغير هيكله المادي أيضًا. لتجنب تدهور أداء الاستعلام يتم الاحتفاظ بكل عرض ملموس بشكل منفصل من قِبل محرك SQL.
يزداد عبء عمل الصيانة عند زيادة عدد المشاهدات الفعلية وتغييرات الجدول الأساسي. يجب على المستخدمين التحقق مما إذا كانت التكلفة المتكبدة من كافة طرق العرض الفعلية يمكن تعويضها من خلال مكاسب أداء الاستعلام.
يمكنك تشغيل هذا الاستعلام لإنشاء قائمة بالوجهات المادية في تجمع SQL مخصص:
SELECT V.name as materialized_view, V.object_id
FROM sys.views V
JOIN sys.indexes I ON V.object_id= I.object_id AND I.index_id < 2;
خيارات من أجل تقليل عدد المشاهدات المحققة:
حدد مجموعات البيانات الشائعة التي تستخدمها الاستعلامات المعقدة في حمل العمل لديك بشكل متكرر. إنشاء عروض فعلية لتخزين مجموعات البيانات هذه حتى يتمكن المحسن من استخدامها كأجزاء أساسية عند إنشاء خطط التنفيذ.
إسقاط العروض المجسدة ذات الاستخدام المنخفض أو التي لم تعد هناك حاجة إليها. لا يتم الحفاظ على العرض المادي المعطل ولكنه ما زال يتحمل تكلفة التخزين.
قم بدمج طرق العرض المادية التي تم إنشاؤها على نفس الجداول الأساسية أو ما شابهها حتى لو لم تتداخل بياناتها. قد يؤدي الدمج بين طرق العرض المحسوسة إلى عرض أكبر في الحجم من مجموع العروض المنفصلة، ومع ذلك يجب تقليل تكلفة صيانة العرض. على سبيل المثال:
-- Query 1 would benefit from having a materialized view created with this SELECT statement
SELECT A, SUM(B)
FROM T
GROUP BY A
-- Query 2 would benefit from having a materialized view created with this SELECT statement
SELECT C, SUM(D)
FROM T
GROUP BY C
-- You could create a single materialized view of this form
SELECT A, C, SUM(B), SUM(D)
FROM T
GROUP BY A, C
ليس كل ضبط للأداء يحتاج تغيير الاستعلام
يمكن لمحسن استعلام SQL استخدام طرق العرض المادية المنشورة تلقائيًا لتحسين أداء الاستعلام. يطبق هذا الدعم بشفافية على الاستعلامات التي لا تشير إلى وجهات النظر والاستعلامات التي تستخدم مجاميع غير مدعومة في إنشاء طرق العرض المادية. لا توجد حاجة لتغيير الاستعلام. يمكنك التحقق من خطة التنفيذ المقدرة للاستعلام من أجل تأكيد ما إذا كان يتم استخدام العرض الفعلي.
راقب طرق العرض المجسدة
يتم تخزين العرض المادي في تجمع SQL المخصص تمامًا مثل الجدول الذي يحتوي على فهرس مخزن أعمدة متفاوت (CCI). تشمل قراءة البيانات من وجهة نظر فعلية مسح شرائح مؤشر CCI وتطبيق أي تغييرات تدريجية من الجداول الأساسية. عندما يكون عدد التغييرات المتزايدة مرتفعًا جدًا، يمكن أن يستغرق حل استعلام من طريقة عرض ملموسة وقتًا أطول من الاستعلام المباشر عن الجداول الأساسية.
لتجنب تدهور أداء الاستعلام، إنها ممارسة جيدة لتشغيلDBCC PDW_SHOWMATERIALIZEDVIEWOVERHEAD لمراقبة قيمة العرض الزائد (total_rows / max(1, base_view_row)). يجب على المستخدمين إعادة إنشاء العرض الفعلي إذا كانت نسبة النفقات العامة عالية جدًا.
العرض المادي والتخزين المؤقت المتعلق بمجموعة النتائج
تستخدم هاتين الميزتين في تجمع SQL المخصص لضبط أداء الاستعلام. يتم استخدام التخزين المؤقت لمجموعة النتائج للحصول على التزامن العالي والاستجابة السريعة من الاستعلامات المتكررة مقابل البيانات الثابتة.
لاستخدام النتيجة المخزنة مؤقتًا، يجب أن يتطابق نموذج طلب البحث لذاكرة التخزين المؤقت مع الاستعلام الذي أنتج ذاكرة التخزين المؤقت. بالإضافة إلى ذلك، يجب أن تنطبق النتيجة المخبأة على الاستعلام بالكامل.
تسمح طرق العرض الفعلية بتغيير البيانات للجداول الأساسية. يمكن تطبيق البيانات الموجودة في طرق العرض الفعلية على جزء من الاستعلام. يسمح هذا الدعم باستخدام نفس طرق العرض الفعلية من قِبل استعلامات مختلفة تشترك في بعض الحسابات للحصول على أداء أسرع.
مثال
يستخدم هذا المثال استعلامًا يشبه TPCDS يبحث عن العملاء الذين ينفقون أموالاً أكثر من خلال الكتالوج مقارنة بالمتاجر، وتحديد العملاء المفضلين وبلدهم/ منطقتهم الأصلية. يشمل الاستعلام تحديد أهم 100 سجل من الاتحاد المكون من ثلاث عبارات sub-SELECT تشمل SUM () وGROUP BY.
WITH year_total AS (
SELECT c_customer_id customer_id
,c_first_name customer_first_name
,c_last_name customer_last_name
,c_preferred_cust_flag customer_preferred_cust_flag
,c_birth_country customer_birth_country
,c_login customer_login
,c_email_address customer_email_address
,d_year dyear
,sum(isnull(ss_ext_list_price-ss_ext_wholesale_cost-ss_ext_discount_amt+ss_ext_sales_price, 0)/2) year_total
,'s' sale_type
FROM customer
,store_sales
,date_dim
WHERE c_customer_sk = ss_customer_sk
AND ss_sold_date_sk = d_date_sk
GROUP BY c_customer_id
,c_first_name
,c_last_name
,c_preferred_cust_flag
,c_birth_country
,c_login
,c_email_address
,d_year
UNION ALL
SELECT c_customer_id customer_id
,c_first_name customer_first_name
,c_last_name customer_last_name
,c_preferred_cust_flag customer_preferred_cust_flag
,c_birth_country customer_birth_country
,c_login customer_login
,c_email_address customer_email_address
,d_year dyear
,sum(isnull(cs_ext_list_price-cs_ext_wholesale_cost-cs_ext_discount_amt+cs_ext_sales_price, 0)/2) year_total
,'c' sale_type
FROM customer
,catalog_sales
,date_dim
WHERE c_customer_sk = cs_bill_customer_sk
AND cs_sold_date_sk = d_date_sk
GROUP BY c_customer_id
,c_first_name
,c_last_name
,c_preferred_cust_flag
,c_birth_country
,c_login
,c_email_address
,d_year
UNION ALL
SELECT c_customer_id customer_id
,c_first_name customer_first_name
,c_last_name customer_last_name
,c_preferred_cust_flag customer_preferred_cust_flag
,c_birth_country customer_birth_country
,c_login customer_login
,c_email_address customer_email_address
,d_year dyear
,sum(isnull(ws_ext_list_price-ws_ext_wholesale_cost-ws_ext_discount_amt+ws_ext_sales_price, 0)/2) year_total
,'w' sale_type
FROM customer
,web_sales
,date_dim
WHERE c_customer_sk = ws_bill_customer_sk
AND ws_sold_date_sk = d_date_sk
GROUP BY c_customer_id
,c_first_name
,c_last_name
,c_preferred_cust_flag
,c_birth_country
,c_login
,c_email_address
,d_year
)
SELECT TOP 100
t_s_secyear.customer_id
,t_s_secyear.customer_first_name
,t_s_secyear.customer_last_name
,t_s_secyear.customer_birth_country
FROM year_total t_s_firstyear
,year_total t_s_secyear
,year_total t_c_firstyear
,year_total t_c_secyear
,year_total t_w_firstyear
,year_total t_w_secyear
WHERE t_s_secyear.customer_id = t_s_firstyear.customer_id
AND t_s_firstyear.customer_id = t_c_secyear.customer_id
AND t_s_firstyear.customer_id = t_c_firstyear.customer_id
AND t_s_firstyear.customer_id = t_w_firstyear.customer_id
AND t_s_firstyear.customer_id = t_w_secyear.customer_id
AND t_s_firstyear.sale_type = 's'
AND t_c_firstyear.sale_type = 'c'
AND t_w_firstyear.sale_type = 'w'
AND t_s_secyear.sale_type = 's'
AND t_c_secyear.sale_type = 'c'
AND t_w_secyear.sale_type = 'w'
AND t_s_firstyear.dyear+0 = 1999
AND t_s_secyear.dyear+0 = 1999+1
AND t_c_firstyear.dyear+0 = 1999
AND t_c_secyear.dyear+0 = 1999+1
AND t_w_firstyear.dyear+0 = 1999
AND t_w_secyear.dyear+0 = 1999+1
AND t_s_firstyear.year_total > 0
AND t_c_firstyear.year_total > 0
AND t_w_firstyear.year_total > 0
AND CASE WHEN t_c_firstyear.year_total > 0 THEN t_c_secyear.year_total / t_c_firstyear.year_total ELSE NULL END
> CASE WHEN t_s_firstyear.year_total > 0 THEN t_s_secyear.year_total / t_s_firstyear.year_total ELSE NULL END
AND CASE WHEN t_c_firstyear.year_total > 0 THEN t_c_secyear.year_total / t_c_firstyear.year_total ELSE NULL END
> CASE WHEN t_w_firstyear.year_total > 0 THEN t_w_secyear.year_total / t_w_firstyear.year_total ELSE NULL END
ORDER BY t_s_secyear.customer_id
,t_s_secyear.customer_first_name
,t_s_secyear.customer_last_name
,t_s_secyear.customer_birth_country
OPTION ( LABEL = 'Query04-af359846-253-3');
تحقق من الخطة التنفيذية المقدرة للاستعلام. توجد 18 عملية خلط و17 عملية انضمام، والتي تستغرق وقتًا أطول للتنفيذ. الآن لننشئ عرضًا ملموسًا واحدًا لكل من عبارات SELECT الفرعية الثلاثة.
CREATE materialized view nbViewSS WITH (DISTRIBUTION=HASH(customer_id)) AS
SELECT c_customer_id customer_id
,c_first_name customer_first_name
,c_last_name customer_last_name
,c_preferred_cust_flag customer_preferred_cust_flag
,c_birth_country customer_birth_country
,c_login customer_login
,c_email_address customer_email_address
,d_year dyear
,sum(isnull(ss_ext_list_price-ss_ext_wholesale_cost-ss_ext_discount_amt+ss_ext_sales_price, 0)/2) year_total
, count_big(*) AS cb
FROM dbo.customer
,dbo.store_sales
,dbo.date_dim
WHERE c_customer_sk = ss_customer_sk
AND ss_sold_date_sk = d_date_sk
GROUP BY c_customer_id
,c_first_name
,c_last_name
,c_preferred_cust_flag
,c_birth_country
,c_login
,c_email_address
,d_year
GO
CREATE materialized view nbViewCS WITH (DISTRIBUTION=HASH(customer_id)) AS
SELECT c_customer_id customer_id
,c_first_name customer_first_name
,c_last_name customer_last_name
,c_preferred_cust_flag customer_preferred_cust_flag
,c_birth_country customer_birth_country
,c_login customer_login
,c_email_address customer_email_address
,d_year dyear
,sum(isnull(cs_ext_list_price-cs_ext_wholesale_cost-cs_ext_discount_amt+cs_ext_sales_price, 0)/2) year_total
, count_big(*) as cb
FROM dbo.customer
,dbo.catalog_sales
,dbo.date_dim
WHERE c_customer_sk = cs_bill_customer_sk
AND cs_sold_date_sk = d_date_sk
GROUP BY c_customer_id
,c_first_name
,c_last_name
,c_preferred_cust_flag
,c_birth_country
,c_login
,c_email_address
,d_year
GO
CREATE materialized view nbViewWS WITH (DISTRIBUTION=HASH(customer_id)) AS
SELECT c_customer_id customer_id
,c_first_name customer_first_name
,c_last_name customer_last_name
,c_preferred_cust_flag customer_preferred_cust_flag
,c_birth_country customer_birth_country
,c_login customer_login
,c_email_address customer_email_address
,d_year dyear
,sum(isnull(ws_ext_list_price-ws_ext_wholesale_cost-ws_ext_discount_amt+ws_ext_sales_price, 0)/2) year_total
, count_big(*) AS cb
FROM dbo.customer
,dbo.web_sales
,dbo.date_dim
WHERE c_customer_sk = ws_bill_customer_sk
AND ws_sold_date_sk = d_date_sk
GROUP BY c_customer_id
,c_first_name
,c_last_name
,c_preferred_cust_flag
,c_birth_country
,c_login
,c_email_address
,d_year
التحقق من خطة تنفيذ الاستعلام الأصلي مرة أخرى. حالياً يتغير عدد الصلات من 17 إلى 5 ولا يوجد تبديل عشوائي. حدد رمز عملية التصفية في الخطة، حيث تعرض قائمة المخرجات الخاصة بها البيانات تتم قراءتها من طرق العرض الفعلية بدلاً من الجداول الأساسية.
باستخدام طرق العرض المحققة، يُشغل نفس الاستعلام بشكل أسرع دون تغيير التعليمات البرمجية.
الخطوات التالية
لمزيد من تلميحات التطوير، انظرنظرة عامة على تطوير تجمع SQL المخصص.