هل تتذكر كيف بدأ كل شيء. كل شيء كان لأول مرة ومرة ​​أخرى

حول كيفية التعامل مع تحسين استعلام PostgreSQL وما نتج عن كل هذا.
لماذا كان عليك؟ نعم ، لأن السنوات الأربع الماضية كان كل شيء يعمل بهدوء وهدوء ، كما لو كانت الساعة تدق.
كنقش.

مستوحى من أحداث حقيقية.
تم تغيير كل الأسماء ، الصدف عشوائية.

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

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

قد يكون من المثير للاهتمام إعادة إنشاء سلسلة الأحداث السابقة.
احتفظ التاريخ بتاريخ البدء الدقيق - 2018-09-10 18:02:48.
أيضا ، في القصة هناك طلب بدأ منه كل شيء:
طلب مشكلةاختر
ص "PARAMETER_ID" كمعرّف_معامل ،
pd. "PD_NAME" AS pd_name ،
pd. "CUSTOMER_PARTNUMBER" AS customer_partnumber ،
w. "LRM" AS LRM ،
ث. "LOTID" كما لوطيد ،
ث. "RTD_VALUE" مثل RTD_value ،
w. "LOWER_SPEC_LIMIT" AS low_spec_limit ،
w. "UPPER_SPEC_LIMIT" AS حد_حد أقصى ،
ص "TYPE_CALCUL" AS type_calcul ،
s. "SPENT_NAME" AS الاسم_المستهلك ،
ق. "SPENT_DATE" AS تاريخ انقضاء ،
استخراج (سنة من "SPENT_DATE") AS سنة ،
استخراج (شهر من "SPENT_DATE") كشهر ،
s. "REPORT_NAME" AS report_name ،
ص "STPM_NAME" AS stpm_name ،
ص "CUSTOMERPARAM_NAME" AS customerparam_name
من وداتاو
قضى ق ،
pmtrp ،
أنفقت_pdsp ،
بي دي
أين. "SPENT_ID" = w. "SPENT_ID"
و p. "PARAMETER_ID" = w. "PARAMETER_ID"
و "SPENT_ID" = sp. "SPENT_ID"
و pd. "PD_ID" = sp. "PD_ID"
و s. "SPENT_DATE"> = '2018-07-01' AND s. "SPENT_DATE" <= '2018-09-30'
و s. "SPENT_DATE" = (SELECT MAX (s2. "SPENT_DATE")
من قضى s2 ،
wdata w2
أين s2. "SPENT_ID" = w2. "SPENT_ID"
و w2. ​​"LRM" = w. "LRM") ؛

وصف المشكلة ، المعيار المتوقع - "كل شيء سيء. قل لي ما هي المشكلة ".
تذكرت على الفور نكتة من أوقات محركات الأقراص 3 ونصف بوصة:

الأعرج يأتي إلى الهاكر.
- لا شيء يناسبني ، أخبرني أين تكمن المشكلة.
-في الحمض النووي ...

لكن ، بالطبع ، ليست هذه هي الطريقة لحل حوادث الأداء. "قد لا نفهم" (مع). بحاجة لمعرفة ذلك.
حسنًا ، دعنا نحفر. ربما سيتراكم هذا نتيجة لذلك.

بدأ الاستثمار

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

الأكثر إثارة للاهتمام وفائدة ، كالعادة ، في البداية والنهاية.
حلقة متداخلة (التكلفة = 935.84..479763226.18 صفًا = 3322 عرض = 135) (الوقت الفعلي = 31.536..8220420.295 صفًا = 8111656 حلقات = 1)
وقت التخطيط: 3.807 مللي ثانية
وقت التنفيذ: 8222351.640 مللي ثانية
وقت التنفيذ أكثر من ساعتين.

فرضيات خاطئة استغرقت وقتًا

الفرضية 1- المحسن خاطئ ، يبني الخطة الخاطئة.

لتصور خطة التنفيذ ، سوف نستخدم الموقع https://explain.depesz.com/. ومع ذلك ، فإن الموقع لم يظهر أي شيء ممتع أو مفيد. للوهلة الأولى والثانية - لا شيء يمكن أن يساعد حقًا. ما لم يكن - المسح الكامل ضئيلاً. تفضل.

الفرضية 2 - التأثير على القاعدة من جانب الفراغ التلقائي ، تحتاج إلى التخلص من الفرامل.

ولكن ، الشياطين ذاتية التفريغ تتصرف بشكل جيد ، ولا توجد عمليات معلقة منذ فترة طويلة. أي حمولة خطيرة - لا. بحاجة للبحث عن شيء آخر.

الفرضية 3 - الإحصائيات قديمة ، تحتاج إلى إعادة حساب كل شيء الذباب

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

البدء في التحسين

الجدول الرئيسي "wdata" ليس صغيرا بالتأكيد ، ما يقرب من 3 ملايين سجل.
وعلى هذا الجدول يذهب المسح الكامل.

شرط التجزئة: ((w. "SPENT_ID" = s. "SPENT_ID") AND ((SubPlan 1) = s. "SPENT_DATE"))
-> مسح التسلسل على wdata w (التكلفة = 0.00..574151.49 صفوف = 26886249 العرض = 46) (الوقت الفعلي = 0.005..8153.565 صفوف = 26873950 حلقات = 1)
نحن نعمل كمعيار: "لنصنع فهرسًا وكل شيء يطير".
عمل فهرس في الحقل "SPENT_ID"
النتيجة:
خطة تنفيذ الاستعلام باستخدام فهرس

حسنا ، هل ساعدت؟
كان: 8 222 351.640 مللي ثانية (ما يزيد قليلاً عن ساعتين)
أصبح: 6 985 مللي ثانية (حوالي ساعتين)
بشكل عام ، نفس التفاح ، منظر جانبي.
لنتذكر الكلاسيكيات:
"هل لديك نفس الجناح ولكن بدون أجنحة؟ سوف تسعى".

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

والآن الشيء الأكثر إثارة للاهتمام - نستمر في التحسين ، وسنقوم بصقل الاستعلام

الخطوة الأولى - استخدم JOIN

الاستعلام المعاد كتابته ، يبدو الآن مثل هذا (جيدا على الأقل أجمل):
الاستعلام باستخدام JOINاختر
ص "PARAMETER_ID" كمعرّف_معامل ،
pd. "PD_NAME" AS pd_name ،
pd. "CUSTOMER_PARTNUMBER" AS customer_partnumber ،
w. "LRM" AS LRM ،
ث. "LOTID" كما لوطيد ،
ث. "RTD_VALUE" مثل RTD_value ،
w. "LOWER_SPEC_LIMIT" AS low_spec_limit ،
w. "UPPER_SPEC_LIMIT" AS حد_حد أقصى ،
ص "TYPE_CALCUL" AS type_calcul ،
s. "SPENT_NAME" AS الاسم_المستهلك ،
ق. "SPENT_DATE" AS تاريخ انقضاء ،
استخراج (سنة من "SPENT_DATE") AS سنة ،
استخراج (شهر من "SPENT_DATE") كشهر ،
s. "REPORT_NAME" AS report_name ،
ص "STPM_NAME" AS stpm_name ،
ص "CUSTOMERPARAM_NAME" AS customerparam_name
من بيانات wdata ، قضى INNER JOIN ثوانٍ في التشغيل. "SPENT_ID" = s. "SPENT_ID"
INNER JOIN pmtr p ON p. "PARAMETER_ID" = w. "PARAMETER_ID"
INNER JOIN الإنفاق_pd sp ON s. "SPENT_ID" = sp. "SPENT_ID"
INNER JOIN pd pd ON pd. "PD_ID" = sp. "PD_ID"
WHERE
s. "SPENT_DATE"> = '2018-07-01' AND s. "SPENT_DATE" <= '2018-09-30'AND
ق. "SPENT_DATE" = (حدد MAX (ثانية 2. "SPENT_DATE")
من wdata w2 ، قضى INNER JOIN ثانية 2 في W2. "SPENT_ID" = s2. "SPENT_ID"
الانضمام الداخلي wdata ث
تشغيل w2. "LRM" = w. "LRM") ؛
وقت التخطيط: 2.486 مللي ثانية
وقت التنفيذ: 1223680.326 مللي ثانية

إذن ها هي النتيجة الأولى.
كان: 6 985 مللي ثانية (حوالي ساعتين).
أصبح: 1 223 مللي ثانية (ما يزيد قليلاً عن 680.326 دقيقة).
نتيجة جيدة. من حيث المبدأ ، مرة أخرى ، سيكون من الممكن التوقف عند هذا الحد. لكن غير مثير للاهتمام ، لا يمكنك التوقف.
إلى عن على

الخطوة الثانية - تخلص من الاستعلام الفرعي المرتبط

نص الطلب الذي تم تغييره:
لا يوجد استعلام فرعي مرتبطاختر
ص "PARAMETER_ID" كمعرّف_معامل ،
pd. "PD_NAME" AS pd_name ،
pd. "CUSTOMER_PARTNUMBER" AS customer_partnumber ،
w. "LRM" AS LRM ،
ث. "LOTID" كما لوطيد ،
ث. "RTD_VALUE" مثل RTD_value ،
w. "LOWER_SPEC_LIMIT" AS low_spec_limit ،
w. "UPPER_SPEC_LIMIT" AS حد_حد أقصى ،
ص "TYPE_CALCUL" AS type_calcul ،
s. "SPENT_NAME" AS الاسم_المستهلك ،
ق. "SPENT_DATE" AS تاريخ انقضاء ،
استخراج (سنة من "SPENT_DATE") AS سنة ،
استخراج (شهر من "SPENT_DATE") كشهر ،
s. "REPORT_NAME" AS report_name ،
ص "STPM_NAME" AS stpm_name ،
ص "CUSTOMERPARAM_NAME" AS customerparam_name
من بيانات wdata ، قضى INNER JOIN ثوانٍ. "SPENT_ID" = w. "SPENT_ID"
INNER JOIN pmtr p ON p. "PARAMETER_ID" = w. "PARAMETER_ID"
INNER JOIN الإنفاق_pd sp ON s. "SPENT_ID" = sp. "SPENT_ID"
INNER JOIN pd pd ON pd. "PD_ID" = sp. "PD_ID"
INNER JOIN (حدد w2. "LRM" ، MAX (ثانية 2. "SPENT_DATE")
من قضى s2 INNER JOIN wdata w2 ON s2. "SPENT_ID" = w2. "SPENT_ID"
تجميع حسب w2.LRM
) md على w. "LRM" = md. "LRM"
WHERE
s. "SPENT_DATE"> = '2018-07-01' AND s. "SPENT_DATE" <= '2018-09-30'؛
وقت التخطيط: 2.291 مللي ثانية
وقت التنفيذ: 165021.870 مللي ثانية

كان: 1 223 مللي ثانية (ما يزيد قليلاً عن 680.326 دقيقة).
أصبح: 165 021.870 مللي ثانية (ما يزيد قليلاً عن دقيقتين).
هذا بالفعل جيد جدا.
ومع ذلك ، كما يقول الإنجليز ،ولكن ، هناك دائمًا لكن". نتيجة جيدة جدًا يجب أن تثير الشك تلقائيًا. هناك شيء خاطئ هنا.

فرضية تصحيح الاستعلام للتخلص من الاستعلام الفرعي المرتبط صحيحة. لكنها تحتاج إلى القليل من التغيير والتبديل للحصول على النتيجة النهائية الصحيحة.
نتيجة لذلك ، النتيجة الوسيطة الأولى:
استعلام محرر بدون استعلام فرعي مرتبطاختر
ص "PARAMETER_ID" كمعرّف_معامل ،
pd. "PD_NAME" AS pd_name ،
pd. "CUSTOMER_PARTNUMBER" AS customer_partnumber ،
w. "LRM" AS LRM ،
ث. "LOTID" كما لوطيد ،
ث. "RTD_VALUE" مثل RTD_value ،
w. "LOWER_SPEC_LIMIT" AS low_spec_limit ،
w. "UPPER_SPEC_LIMIT" AS حد_حد أقصى ،
ص "TYPE_CALCUL" AS type_calcul ،
s. "SPENT_NAME" AS الاسم_المستهلك ،
ق. "SPENT_DATE" AS تاريخ انقضاء ،
استخراج (سنة من s. "SPENT_DATE") AS سنة ،
استخراج (شهر من s. "SPENT_DATE") كشهر ،
s. "REPORT_NAME" AS report_name ،
ص "STPM_NAME" AS stpm_name ،
ص "CUSTOMERPARAM_NAME" AS customerparam_name
من بيانات wdata ، قضى INNER JOIN ثوانٍ. "SPENT_ID" = w. "SPENT_ID"
INNER JOIN pmtr p ON p. "PARAMETER_ID" = w. "PARAMETER_ID"
INNER JOIN الإنفاق_pd sp ON s. "SPENT_ID" = sp. "SPENT_ID"
INNER JOIN pd pd ON pd. "PD_ID" = sp. "PD_ID"
INNER JOIN (حدد w2. "LRM" ، MAX (ثانية 2. "SPENT_DATE") كـ "SPENT_DATE"
من قضى s2 INNER JOIN wdata w2 ON s2. "SPENT_ID" = w2. "SPENT_ID"
تجميع حسب w2.LRM
) md ON md. "SPENT_DATE" = s. "SPENT_DATE" و md. "LRM" = w. "LRM"
WHERE
s. "SPENT_DATE"> = '2018-07-01' AND s. "SPENT_DATE" <= '2018-09-30'؛
وقت التخطيط: 3.192 مللي ثانية
وقت التنفيذ: 208014.134 مللي ثانية

لذا فإن ما حصلنا عليه بالنتيجة هو أول نتيجة مقبولة ، ولا نخجل من إظهارها للعميل:
بدأت مع: 8 مللي ثانية (أكثر من ساعتين)
تم الإنجاز: 1،223،680.326 مللي ثانية (ما يزيد قليلاً عن 20 دقيقة).
النتيجة (متوسط): 208 014.134 مللي ثانية (ما يزيد قليلاً عن 3 دقائق).

نتيجة ممتازة.

مجموع

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

لذلك ، حان الوقت لإعادة تصميم قاعدة البيانات. لم يعد من الممكن تحسين بنية الطلب نفسها (على الرغم من أنه ، كما اتضح لاحقًا ، هناك خيار لكل شيء للطيران بالفعل). ولكن الآن للقيام بتحسين وتطوير تصميم قاعدة البيانات ، فهذه بالفعل فكرة واعدة للغاية. والأهم من ذلك مثير للاهتمام. مرة أخرى ، تذكر الشباب. بعد كل شيء ، لم أصبح DBA على الفور ، لقد نشأت من المبرمجين (الأساسي ، المجمع ، si ، si مرتين ، oracle ، plsql). موضوع مثير للاهتمام ، بالطبع ، لمذكرات منفصلة ؛-).
ومع ذلك ، دعونا لا نستطرد.

وهكذا،

وربما سيساعدنا التقسيم؟
Spoiler - "نعم ، لقد ساعدت ، وفي تحسين الأداء ، بما في ذلك."

لكن هذه قصة مختلفة تمامًا ...

يتبع…

المصدر: www.habr.com