یادت میاد چطوری شروع شد همه چیز برای اولین بار و دوباره بود

در مورد اینکه چگونه باید با بهینه سازی پرس و جو PostgreSQL کنار بیایم و از همه اینها چه نتیجه ای حاصل شد.
چرا مجبور شدی؟ بله، چون 4 سال قبل همه چیز بی سر و صدا و آرام کار می کرد، مثل یک ساعت.
به عنوان اپیگراف

بر اساس وقایع واقعی.
همه نام ها تغییر کرده اند، تصادفات تصادفی هستند.

هنگامی که یک نتیجه خاص به دست می آید، همیشه جالب است که به یاد بیاوریم که انگیزه شروع چیست، چگونه همه چیز شروع شد.

بنابراین، آنچه در نتیجه اتفاق افتاد به طور خلاصه در مقاله توضیح داده شده است.سنتز به عنوان یکی از روش های بهبود عملکرد PostgreSQL'.

احتمالاً بازآفرینی زنجیره رویدادهای قبلی جالب خواهد بود.
تاریخ تاریخ دقیق شروع را حفظ کرد - 2018-09-10 18:02:48.
همچنین، در داستان درخواستی وجود دارد که همه چیز از آن شروع شد:
درخواست مشکلانتخاب کنید
p. "PARAMETER_ID" به عنوان parameter_id،
pd. "PD_NAME" AS pd_name،
pd. "CUSTOMER_PARTNUMBER" AS customer_partnumber،
w"LRM" AS LRM،
w. "LOTID" AS lotid،
w. "RTD_VALUE" به عنوان RTD_value،
w. "LOWER_SPEC_LIMIT" AS کمترین_spec_limit،
w. "UPPER_SPEC_LIMIT" به عنوان حد_spec_بالا،
p"TYPE_CALCUL" AS type_calcul،
s"SPENT_NAME" AS spent_name،
s"SPENT_DATE" AS spent_date،
استخراج (سال از "SPENT_DATE") به عنوان سال،
استخراج (ماه از "SPENT_DATE") به عنوان ماه،
s"REPORT_NAME" AS report_name،
p. "STPM_NAME" AS stpm_name،
p."CUSTOMERPARAM_NAME" AS customerparam_name
از wdataw،
صرف s،
pmtrp،
spent_pdsp،
پی دی پی دی
WHERE s."SPENT_ID" = w."SPENT_ID"
و p."PARAMETER_ID" = w"PARAMETER_ID"
AND s."SPENT_ID" = sp."SPENT_ID"
AND PD "PD_ID" = sp. "PD_ID"
AND s."SPENT_DATE" >= '2018-07-01' و s."SPENT_DATE" <= '2018-09-30'
و s."SPENT_DATE" = (انتخاب MAX(s2."SPENT_DATE")
از s2 صرف شده،
wdata w2
WHERE s2."SPENT_ID" = w2."SPENT_ID"
AND w2."LRM" = w"LRM");

شرح مشکل، قابل پیش بینی استاندارد - "همه چیز بد است. به من بگو مشکل چیست."
بلافاصله یاد یک جوک از زمان های درایوهای 3 و نیم اینچی افتادم:

لمر به سراغ هکر می آید.
- هیچی به من نمیرسه، بگو مشکل از کجاست.
-در DNA...

اما، البته، این راه حل حوادث عملکرد نیست. "شاید درک نشویم" (با). نیاز به کشف آن است.
خوب، بیایید حفاری کنیم. شاید در نتیجه انباشته شود.

سرمایه گذاری آغاز شد

بنابراین، آنچه را می توان بلافاصله با چشم غیر مسلح دید، حتی بدون توسل به کمک توضیح.
1) JOIN استفاده نمی شود. این بد است، به خصوص اگر تعداد اتصالات بیش از یک باشد.
2) اما آنچه حتی بدتر است - یک پرسش فرعی همبسته، علاوه بر این، با تجمع. این خیلی بد است.
این البته بد است. اما این فقط از یک طرف است. از طرف دیگر، این بسیار خوب است، زیرا مشکل به وضوح راه حل دارد و درخواست را می توان بهبود بخشید.
پیش فالگیر (C) نرو.
طرح پرس و جو چندان پیچیده نیست، اما کاملاً نشان دهنده است:
طرح اجرا

جالب ترین و مفیدترین، طبق معمول، در ابتدا و انتها.
حلقه تودرتو (هزینه=935.84..479763226.18 ردیف=3322 عرض=135) (زمان واقعی=31.536..8220420.295 ردیف=8111656 حلقه=1)
زمان برنامه ریزی: 3.807 میلی ثانیه
زمان اجرا: 8222351.640ms
زمان اجرا بیش از 2 ساعت است.

فرضیه های نادرستی که زمان برد

فرضیه 1- بهینه ساز اشتباه می کند، طرح اشتباهی می سازد.

برای تجسم طرح اجرا از سایت استفاده می کنیم https://explain.depesz.com/. با این حال، سایت هیچ چیز جالب و مفیدی را نشان نداد. در نگاه اول و دوم - چیزی که واقعاً می تواند کمک کند. مگر اینکه - اسکن کامل حداقل باشد. برو جلو.

فرضیه 2- ضربه به پایه از سمت اتوواکیوم، باید از ترمز خلاص شوید.

اما، شیاطین اتوواکیوم به خوبی رفتار می کنند، هیچ فرآیند طولانی مدتی وجود ندارد. هر بار جدی - خیر. باید دنبال چیز دیگری بود

فرضیه 3-آمار منسوخ شده است، شما باید همه چیز را دوباره محاسبه کنید

باز هم، نه آن. آمار به روز است. که با توجه به عدم وجود مشکل در اتووکیوم، تعجب آور نیست.

شروع بهینه سازی

جدول اصلی 'wdata' قطعا کوچک نیست، تقریبا 3 میلیون رکورد.
و روی این میز است که Full Scan می رود.

حالت هش: ((w."SPENT_ID" = s."SPENT_ID") و ((Subplan 1) = s."SPENT_DATE"))
-> Seq Scan در wdata w (هزینه = 0.00..574151.49 ردیف = 26886249 عرض = 46) (زمان واقعی = 0.005.. 8153.565 ردیف = 26873950 حلقه = 1)
ما به عنوان استاندارد عمل می کنیم: "بیایید یک شاخص ایجاد کنیم و همه چیز پرواز می کند".
یک نمایه در فیلد "SPENT_ID" ایجاد کرد
در نتیجه:
طرح اجرای پرس و جو با استفاده از شاخص

خوب کمکی کرد؟
این بود: 8 222 351.640 ms (کمی بیش از 2 ساعت)
تبدیل شده است: 6 985 431.575 ms (تقریباً 2 ساعت)
به طور کلی، همان سیب، نمای جانبی.
بیایید کلاسیک ها را به یاد بیاوریم:
«آیا شما هم همین را دارید، اما بدون بال؟ جستجو خواهد کرد».

در اصل، این را می توان نتیجه خوب نامید، خوب، خوب نیست، اما قابل قبول است. حداقل، یک گزارش بزرگ به مشتری ارائه دهید که در آن توضیح دهید چقدر انجام شده است و چرا کاری که انجام شده خوب است.
با این حال، تصمیم نهایی هنوز بسیار دور است. خیلی دور.

و اکنون جالب ترین چیز - ما به بهینه سازی ادامه می دهیم، پرس و جو را جلا می دهیم

مرحله اول - از JOIN استفاده کنید

پرس و جو بازنویسی شده، اکنون به این شکل است (خوب حداقل زیباتر):
با استفاده از JOIN پرس و جو کنیدانتخاب کنید
p. "PARAMETER_ID" به عنوان parameter_id،
pd. "PD_NAME" AS pd_name،
pd. "CUSTOMER_PARTNUMBER" AS customer_partnumber،
w"LRM" AS LRM،
w. "LOTID" AS lotid،
w. "RTD_VALUE" به عنوان RTD_value،
w. "LOWER_SPEC_LIMIT" AS کمترین_spec_limit،
w. "UPPER_SPEC_LIMIT" به عنوان حد_spec_بالا،
p"TYPE_CALCUL" AS type_calcul،
s"SPENT_NAME" AS spent_name،
s"SPENT_DATE" AS spent_date،
استخراج (سال از "SPENT_DATE") به عنوان سال،
استخراج (ماه از "SPENT_DATE") به عنوان ماه،
s"REPORT_NAME" AS report_name،
p. "STPM_NAME" AS stpm_name،
p."CUSTOMERPARAM_NAME" AS customerparam_name
FROM wdata w JOIN INNER در ON خرج شد."SPENT_ID"=s."SPENT_ID"
JOIN داخلی pmtr p روی p."PARAMETER_ID" = w"PARAMETER_ID"
JOIN داخلی spent_pd sp ON s."SPENT_ID" = sp."SPENT_ID"
JOIN داخلی pd pd ON pd."PD_ID" = sp."PD_ID"
مکانی که در آن
s."SPENT_DATE" >= '2018-07-01' و s."SPENT_DATE" <= '2018-09-30'AND
s."SPENT_DATE" = (انتخاب MAX(s2."SPENT_DATE")
از wdata w2 JOIN داخلی s2 صرف شده در w2."SPENT_ID"=s2."SPENT_ID"
پیوستن داخلی wdata w
ON w2."LRM" = w"LRM" );
زمان برنامه ریزی: 2.486 میلی ثانیه
زمان اجرا: 1223680.326ms

بنابراین در اینجا اولین نتیجه است.
این بود: 6 985 431.575 ms (تقریباً 2 ساعت).
تبدیل شده است: 1 223 680.326 ms (کمی بیش از 20 دقیقه).
نتیجه خوب. در اصل، دوباره، می توان در آنجا متوقف شد. اما خیلی جالب نیست، نمی توانید متوقف شوید.
برای

مرحله دوم - از شر سوال فرعی مرتبط خلاص شوید

متن درخواست تغییر کرد:
هیچ پرسش فرعی مرتبطی وجود نداردانتخاب کنید
p. "PARAMETER_ID" به عنوان parameter_id،
pd. "PD_NAME" AS pd_name،
pd. "CUSTOMER_PARTNUMBER" AS customer_partnumber،
w"LRM" AS LRM،
w. "LOTID" AS lotid،
w. "RTD_VALUE" به عنوان RTD_value،
w. "LOWER_SPEC_LIMIT" AS کمترین_spec_limit،
w. "UPPER_SPEC_LIMIT" به عنوان حد_spec_بالا،
p"TYPE_CALCUL" AS type_calcul،
s"SPENT_NAME" AS spent_name،
s"SPENT_DATE" AS spent_date،
استخراج (سال از "SPENT_DATE") به عنوان سال،
استخراج (ماه از "SPENT_DATE") به عنوان ماه،
s"REPORT_NAME" AS report_name،
p. "STPM_NAME" AS stpm_name،
p."CUSTOMERPARAM_NAME" AS customerparam_name
FROM wdata w JOIN INNER صرف s ON s."SPENT_ID" = w."SPENT_ID"
JOIN داخلی pmtr p روی p."PARAMETER_ID" = w"PARAMETER_ID"
JOIN داخلی spent_pd sp ON s."SPENT_ID" = sp."SPENT_ID"
JOIN داخلی pd pd ON pd."PD_ID" = sp."PD_ID"
پیوستن داخلی (انتخاب w2."LRM"، MAX(s2."SPENT_DATE")
FROM s2 صرف شده داخلی به wdata w2 ON s2 بپیوندید."SPENT_ID" = w2."SPENT_ID"
گروه توسط w2.LRM
) md on w. "LRM" = md. "LRM"
مکانی که در آن
s."SPENT_DATE" >= '2018-07-01' و s."SPENT_DATE" <= '2018-09-30';
زمان برنامه ریزی: 2.291 میلی ثانیه
زمان اجرا: 165021.870ms

این بود: 1 223 680.326 ms (کمی بیش از 20 دقیقه).
تبدیل شده است: 165 021.870 میلی‌ثانیه (کمی بیش از 2 دقیقه).
این در حال حاضر بسیار خوب است.
با این حال، همانطور که انگلیسی ها می گویند،اما، همیشه یک اما وجود دارد". نتیجه خیلی خوب باید به طور خودکار باعث ایجاد شک شود. اینجا چیزی اشتباه است.

فرضیه تصحیح پرس و جو برای خلاص شدن از زیر استعلام همبسته صحیح است. اما برای رسیدن به نتیجه نهایی نیاز به کمی اصلاح دارد.
در نتیجه، اولین نتیجه میانی:
پرس و جو ویرایش شده بدون درخواست فرعی مرتبطانتخاب کنید
p. "PARAMETER_ID" به عنوان parameter_id،
pd. "PD_NAME" AS pd_name،
pd. "CUSTOMER_PARTNUMBER" AS customer_partnumber،
w"LRM" AS LRM،
w. "LOTID" AS lotid،
w. "RTD_VALUE" به عنوان RTD_value،
w. "LOWER_SPEC_LIMIT" AS کمترین_spec_limit،
w. "UPPER_SPEC_LIMIT" به عنوان حد_spec_بالا،
p"TYPE_CALCUL" AS type_calcul،
s"SPENT_NAME" AS spent_name،
s"SPENT_DATE" AS spent_date،
استخراج (سال از s. "SPENT_DATE") به عنوان سال،
استخراج (ماه از s. "SPENT_DATE") به عنوان ماه،
s"REPORT_NAME" AS report_name،
p. "STPM_NAME" AS stpm_name،
p."CUSTOMERPARAM_NAME" AS customerparam_name
FROM wdata w JOIN INNER صرف s ON s."SPENT_ID" = w."SPENT_ID"
JOIN داخلی pmtr p روی p."PARAMETER_ID" = w"PARAMETER_ID"
JOIN داخلی spent_pd sp ON s."SPENT_ID" = sp."SPENT_ID"
JOIN داخلی pd pd ON pd."PD_ID" = sp."PD_ID"
پیوستن داخلی (انتخاب w2."LRM"، MAX(s2."SPENT_DATE") به عنوان "SPENT_DATE"
FROM s2 صرف شده داخلی به wdata w2 ON s2 بپیوندید."SPENT_ID" = w2."SPENT_ID"
گروه توسط w2.LRM
) md ON md."SPENT_DATE" = s."SPENT_DATE" و md."LRM" = w"LRM"
مکانی که در آن
s."SPENT_DATE" >= '2018-07-01' و s."SPENT_DATE" <= '2018-09-30';
زمان برنامه ریزی: 3.192 میلی ثانیه
زمان اجرا: 208014.134ms

پس آنچه در نتیجه داریم اولین نتیجه قابل قبول است که از نشان دادن آن به مشتری شرم نداریم:
شروع شده با: 8 222 351.640 ms (بیش از 2 ساعت)
به دست آمده: 1 ms (کمی بیش از 223 دقیقه).
نتیجه (متوسط): 208 014.134 ms (کمی بیش از 3 دقیقه).

نتیجه عالی

مجموع

این می توانست متوقف شود.
ولی…
اشتها با خوردن می آید. جاده با پیاده روی تسلط پیدا می کند. هر نتیجه ای متوسط ​​است. مرده متوقف شد و غیره.
بیایید به بهینه سازی ادامه دهیم.
ایده عالی به خصوص با توجه به اینکه مشتری حتی مخالف آن نبود. و حتی به شدت - برای.

بنابراین، زمان طراحی مجدد پایگاه داده است. خود ساختار پرس و جو دیگر نمی تواند بهینه شود (اگرچه، همانطور که بعدا مشخص شد، گزینه ای برای همه چیز وجود دارد که واقعاً پرواز کنند). اما اکنون برای بهینه سازی و توسعه طراحی پایگاه داده، این یک ایده بسیار امیدوارکننده است. و مهمتر از همه جالب باز هم یاد جوانی. از این گذشته ، من بلافاصله DBA نشدم ، من از برنامه نویسان بزرگ شدم (basic, assembler, si, si two plused, oracle, plsql). یک موضوع جالب البته برای خاطرات جداگانه ;-).
با این حال، بیایید منحرف نشویم.

بنابراین،

و شاید تقسیم بندی به ما کمک کند؟
اسپویلر - "بله، کمک کرد، و در بهینه سازی عملکرد، از جمله."

اما این یک داستان کاملا متفاوت است ...

ادامه دارد…

منبع: www.habr.com