តើអ្នកចាំពីរបៀបដែលវាបានចាប់ផ្តើម។ អ្វីគ្រប់យ៉ាងគឺជាលើកដំបូងហើយម្តងទៀត

អំពីរបៀបដែលខ្ញុំត្រូវបង្កើនប្រសិទ្ធភាពសំណួរ PostgreSQL និងអ្វីដែលចេញពីវាទាំងអស់។
ហេតុអ្វីបានជាអ្នកត្រូវ? មែនហើយ ព្រោះកាលពី ៤ ឆ្នាំមុន អ្វីៗដំណើរការយ៉ាងស្ងប់ស្ងាត់ ស្ងប់ស្ងាត់ ដូចជានាឡិការលាក់។
ជា epigraph មួយ។

ផ្អែកលើព្រឹត្តិការណ៍ពិត។
ឈ្មោះទាំងអស់ត្រូវបានផ្លាស់ប្តូរ ការចៃដន្យគឺចៃដន្យ។

នៅពេលអ្នកសម្រេចបានលទ្ធផលជាក់លាក់មួយ វាតែងតែគួរឱ្យចាប់អារម្មណ៍ក្នុងការចងចាំនូវអ្វីដែលជាកម្លាំងរុញច្រានសម្រាប់ការចាប់ផ្តើម ដែលវាទាំងអស់បានចាប់ផ្តើម។

ដូច្នេះ អ្វី​ដែល​បាន​កើត​ឡើង​ជា​លទ្ធផល​ត្រូវ​បាន​ពិពណ៌នា​យ៉ាង​ខ្លី​នៅ​ក្នុង​អត្ថបទ "ការសំយោគជាវិធីសាស្រ្តមួយដើម្បីកែលម្អការអនុវត្ត 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" ដូចជា lotid,
w.“RTD_VALUE” AS RTD_value,
w.“LOWER_SPEC_LIMIT” AS lower_spec_limit,
w.“UPPER_SPEC_LIMIT” AS upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS ចំណាយ_ឈ្មោះ,
s.“SPENT_DATE” AS កាលបរិច្ឆេទចំណាយ,
ស្រង់ចេញ(ឆ្នាំពី "SPENT_DATE") AS ឆ្នាំ,
ស្រង់ចេញ(ខែពី "SPENT_DATE") ជាខែ
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
ពី wdata w,
បានចំណាយ s,
pmtr ទំ,
ចំណាយ_pd sp,
pd pd
WHERE s.“SPENT_ID” = w.“SPENT_ID”
AND p."PARAMETER_ID" = w.PARAMETER_ID"
AND s.“SPENT_ID” = sp.“SPENT_ID”
AND pd."PD_ID" = sp.PD_ID"
និង s.“SPENT_DATE” >= '2018-07-01' និង s.“SPENT_DATE” <= '2018-09-30'
និង s.“SPENT_DATE” = (SELECT MAX(s2.“SPENT_DATE”)
ពីចំណាយ s2,
wdata w2
WHERE s2.“SPENT_ID” = w2.“SPENT_ID”
និង w2.“LRM” = w.“LRM”);

ការពិពណ៌នាអំពីបញ្ហាគឺជាស្តង់ដារព្យាករណ៍ - "អ្វីគ្រប់យ៉ាងគឺអាក្រក់។ ប្រាប់​ខ្ញុំ​ថា​មាន​បញ្ហា​អ្វី»។
ខ្ញុំ​នឹក​ឃើញ​រឿង​ខ្លី​មួយ​ភ្លាម​ៗ​ពី​ដង​នៃ​ការ​បើកបរ​៣​អ៊ីញ​កន្លះ៖

ជនខិលខូចមករកអ្នកលួចចូល។
- គ្មានអ្វីល្អសម្រាប់ខ្ញុំទេ ប្រាប់ខ្ញុំពីកន្លែងដែលបញ្ហា។
- នៅក្នុង DNA...

ប៉ុន្តែជាការពិត នេះមិនមែនជាវិធីដើម្បីដោះស្រាយឧប្បត្តិហេតុនៃការអនុវត្តនោះទេ។ “ពួកគេប្រហែលជាមិនយល់ពីយើងទេ។" (ជាមួយ) ។ យើងត្រូវដោះស្រាយវា។
ចូរយើងជីក។ ប្រហែលជាអ្វីមួយនឹងកកកុញជាលទ្ធផល។

ការស៊ើបអង្កេតបានចាប់ផ្តើម

ដូច្នេះ អ្វី​ដែល​អាច​មើល​ឃើញ​ភ្លាមៗ​ដោយ​ភ្នែក​ទទេ ដោយ​មិន​បាច់​ប្រើ​ពាក្យ​ពន្យល់។
1) JOINs មិនត្រូវបានប្រើទេ។ នេះគឺអាក្រក់ជាពិសេសប្រសិនបើចំនួននៃការតភ្ជាប់មានច្រើនជាងមួយ។
2) ប៉ុន្តែអ្វីដែលកាន់តែអាក្រក់នោះគឺ សំណួររងដែលទាក់ទងគ្នា លើសពីនេះទៅទៀតជាមួយនឹងការប្រមូលផ្តុំ។ នេះគឺអាក្រក់ណាស់។
នេះពិតជាអាក្រក់ណាស់។ ប៉ុន្តែនេះគ្រាន់តែនៅលើដៃម្ខាងប៉ុណ្ណោះ។ ម្យ៉ាងវិញទៀត នេះគឺល្អណាស់ ព្រោះបញ្ហាច្បាស់ជាមានដំណោះស្រាយ និងសំណូមពរដែលអាចកែលម្អបាន។
កុំទៅជួបគ្រូទាយ (C)។
ផែនការ​សំណួរ​មិន​ស្មុគស្មាញ​នោះ​ទេ ប៉ុន្តែ​វា​បង្ហាញ​យ៉ាង​ច្បាស់៖
ផែនការប្រតិបត្តិ

គួរឱ្យចាប់អារម្មណ៍និងមានប្រយោជន៍បំផុតដូចធម្មតាគឺនៅដើមនិងចុងបញ្ចប់។
Nested Loop (តម្លៃ=935.84..479763226.18 rows=3322 width=135) (ពេលវេលាពិត=31.536..8220420.295 rows=8111656 loops=1)
ពេលវេលាធ្វើផែនការ៖ 3.807 ms
ពេលវេលាប្រតិបត្តិ៖ 8222351.640 ms
ពេលវេលាបញ្ចប់គឺច្រើនជាង 2 ម៉ោង។

សម្មតិកម្មមិនពិតដែលបានចំណាយពេល

សម្មតិកម្ម 1 - អ្នកបង្កើនប្រសិទ្ធភាពធ្វើខុស ហើយបង្កើតផែនការខុស។

ដើម្បីមើលឃើញផែនការប្រតិបត្តិ យើងនឹងប្រើគេហទំព័រ https://explain.depesz.com/. ទោះជាយ៉ាងណាក៏ដោយ គេហទំព័រនេះមិនបានបង្ហាញពីអ្វីដែលគួរឱ្យចាប់អារម្មណ៍ ឬមានប្រយោជន៍នោះទេ។ នៅ glance ដំបូង និងទីពីរ គ្មានអ្វីដែលអាចជួយបាននោះទេ។ តើវាអាចទៅរួចទេដែលថា Full Scan មានតិចតួចបំផុត។ ទៅ​ពេល​ខាង​មុខ។

សម្មតិកម្ម 2- ផលប៉ះពាល់លើមូលដ្ឋានពីចំហៀង autovacuum អ្នកត្រូវកម្ចាត់ហ្វ្រាំង។

ប៉ុន្តែដេមិន autovacuum មានឥរិយាបទល្អ មិនមានដំណើរការព្យួរយូរទេ។ មិនមានបន្ទុកធ្ងន់ធ្ងរទេ។ យើងត្រូវរកមើលអ្វីផ្សេងទៀត។

សម្មតិកម្មទី 3 - ស្ថិតិហួសសម័យ អ្វីៗត្រូវគណនាឡើងវិញ

ម្តងទៀត មិនមែនអញ្ចឹងទេ។ ស្ថិតិគឺទាន់សម័យ។ ដែលផ្តល់ឱ្យកង្វះនៃបញ្ហាជាមួយ autovacuum គឺមិនគួរឱ្យភ្ញាក់ផ្អើលនោះទេ។

ចូរចាប់ផ្តើមធ្វើឱ្យប្រសើរ

តារាងសំខាន់ 'wdata' ពិតជាមិនតូចទេ ស្ទើរតែ 3 លានកំណត់ត្រា។
ហើយវាគឺជាតារាងនេះដែល Full Scan ធ្វើតាម។

Hash Cond៖ ((w.SPENT_ID" = s."SPENT_ID") AND ((SubPlan 1) = s."SPENT_DATE"))
-> ស្កេន Seq នៅលើ wdata w (cost=0.00..574151.49 rows=26886249 width=46) (ពេលវេលាពិត=0.005..8153.565 rows=26873950 loops=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" ដូចជា lotid,
w.“RTD_VALUE” AS RTD_value,
w.“LOWER_SPEC_LIMIT” AS lower_spec_limit,
w.“UPPER_SPEC_LIMIT” AS upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS ចំណាយ_ឈ្មោះ,
s.“SPENT_DATE” AS កាលបរិច្ឆេទចំណាយ,
ស្រង់ចេញ(ឆ្នាំពី "SPENT_DATE") AS ឆ្នាំ,
ស្រង់ចេញ(ខែពី "SPENT_DATE") ជាខែ
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
ពី wdata w INNER JOIN បានចំណាយលើ w.“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”
ទីកន្លែង
s.“SPENT_DATE” >= '2018-07-01' និង s.“SPENT_DATE” <= '2018-09-30'AND
s.“SPENT_DATE” = (SELECT MAX(s2.“SPENT_DATE”)
ពី wdata w2 INNER JOIN បានចំណាយ s2 ON w2.“SPENT_ID”=s2.“SPENT_ID”
ខាងក្នុងចូលរួម wdata w
បើក w2.“LRM” = w.“LRM” );
ពេលវេលាធ្វើផែនការ៖ 2.486 ms
ពេលវេលាប្រតិបត្តិ៖ 1223680.326 ms

ដូច្នេះលទ្ធផលដំបូង។
គឺ៖ 6 ms (ជិត 985 ម៉ោង) ។
បានក្លាយជា៖ 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" ដូចជា lotid,
w.“RTD_VALUE” AS RTD_value,
w.“LOWER_SPEC_LIMIT” AS lower_spec_limit,
w.“UPPER_SPEC_LIMIT” AS upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS ចំណាយ_ឈ្មោះ,
s.“SPENT_DATE” AS កាលបរិច្ឆេទចំណាយ,
ស្រង់ចេញ(ឆ្នាំពី "SPENT_DATE") AS ឆ្នាំ,
ស្រង់ចេញ(ខែពី "SPENT_DATE") ជាខែ
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
ពី wdata w. INNER JOIN បានចំណាយលើ s.“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”
ការចូលរួមខាងក្នុង (ជ្រើសរើស w2.“LRM”, MAX(s2.“SPENT_DATE”)
ពីការចំណាយ s2 ខាងក្នុងចូលរួម wdata w2 ON s2.“SPENT_ID” = w2.“SPENT_ID”
ក្រុមដោយ w2.“LRM”
) md នៅលើ w.“LRM” = md.“LRM”
ទីកន្លែង
s."SPENT_DATE" >= '2018-07-01' AND s."SPENT_DATE" <= '2018-09-30';
ពេលវេលាធ្វើផែនការ៖ 2.291 ms
ពេលវេលាប្រតិបត្តិ៖ 165021.870 ms

គឺ៖ 1 223 680.326 ms (គ្រាន់តែជាង 20 នាទី) ។
បានក្លាយជា៖ 165 021.870 ms (គ្រាន់តែជាង 2 នាទី) ។
នេះគឺល្អណាស់រួចទៅហើយ។
ទោះយ៉ាងណាក៏ដោយ ដូចជនជាតិអង់គ្លេសនិយាយប៉ុន្តែ វាតែងតែមាន" លទ្ធផលដែលល្អពេក គួរតែធ្វើឱ្យមានការសង្ស័យដោយស្វ័យប្រវត្តិ។ មានអ្វីមួយខុសនៅទីនេះ។

សម្មតិកម្មអំពីការកែសំណួរដើម្បីកម្ចាត់សំណួររងដែលទាក់ទងគឺត្រឹមត្រូវ។ ប៉ុន្តែអ្នកត្រូវកែប្រែវាបន្តិចដើម្បីឱ្យលទ្ធផលចុងក្រោយត្រឹមត្រូវ។
ជាលទ្ធផលលទ្ធផលមធ្យមដំបូង៖
សំណួរដែលបានកែសម្រួលដោយគ្មានសំណួររងដែលទាក់ទងជ្រើសរើស
p.“PARAMETER_ID” ជា parameter_id,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS customer_partnumber,
w.LRM" AS LRM,
w. "LOTID" ដូចជា lotid,
w.“RTD_VALUE” AS RTD_value,
w.“LOWER_SPEC_LIMIT” AS lower_spec_limit,
w.“UPPER_SPEC_LIMIT” AS upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS ចំណាយ_ឈ្មោះ,
s.“SPENT_DATE” AS កាលបរិច្ឆេទចំណាយ,
ស្រង់ចេញ(ឆ្នាំពី s.“SPENT_DATE”) ជាឆ្នាំ,
ស្រង់ចេញ(ខែពី s.“SPENT_DATE”) ជាខែ
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
ពី wdata w. INNER JOIN បានចំណាយលើ s.“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”
ការចូលរួមខាងក្នុង ( SELECT w2.“LRM”, MAX(s2.“SPENT_DATE”) ជា “SPENT_DATE”
ពីការចំណាយ 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' AND s."SPENT_DATE" <= '2018-09-30';
ពេលវេលាធ្វើផែនការ៖ 3.192 ms
ពេលវេលាប្រតិបត្តិ៖ 208014.134 ms

ដូច្នេះ អ្វី​ដែល​យើង​បញ្ចប់​គឺ​លទ្ធផល​ដំបូង​ដែល​អាច​ទទួល​យក​បាន ដែល​មិន​មែន​ជា​ការ​អៀន​ខ្មាស​ក្នុង​ការ​បង្ហាញ​ដល់​អតិថិជន​ឡើយ៖
ចាប់ផ្តើមជាមួយ៖ 8 222 351.640 ms (ច្រើនជាង 2 ម៉ោង)
យើងអាចសម្រេចបាន៖ 1 ms (ច្រើនជាង 223 នាទី)។
លទ្ធផល (បណ្តោះអាសន្ន)៖ 208 014.134 ms (គ្រាន់តែជាង 3 នាទី) ។

លទ្ធផលដ៏អស្ចារ្យ។

លទ្ធផល

យើងអាចឈប់នៅទីនោះ។
ប៉ុន្តែ…
ចំណង់អាហារកើតឡើងជាមួយនឹងការញ៉ាំ។ អ្នក​ដែល​ដើរ​នឹង​ធ្វើ​ជា​ម្ចាស់​ផ្លូវ។ លទ្ធផលណាមួយគឺកម្រិតមធ្យម។ ឈប់ហើយស្លាប់។ ល។
តោះបន្តការបង្កើនប្រសិទ្ធភាព។
គំនិតដ៏អស្ចារ្យ។ ជាពិសេស​គិត​ថា​អតិថិជន​មិន​បាន​គិត​អ្វី​ទាំងអស់​។ ហើយសូម្បីតែខ្លាំងសម្រាប់វា។

ដូច្នេះ វាដល់ពេលហើយសម្រាប់ការរចនាមូលដ្ឋានទិន្នន័យឡើងវិញ។ រចនាសម្ព័នសំណួរខ្លួនឯងមិនអាចធ្វើឱ្យប្រសើរឡើងបានទៀតទេ (ទោះបីជាវាបានប្រែក្លាយនៅពេលក្រោយក៏ដោយ មានជម្រើសដើម្បីធានាថាអ្វីៗទាំងអស់ពិតជាបរាជ័យ)។ ប៉ុន្តែដើម្បីចាប់ផ្តើមបង្កើនប្រសិទ្ធភាព និងអភិវឌ្ឍការរចនាមូលដ្ឋានទិន្នន័យគឺជាគំនិតដ៏ជោគជ័យរួចទៅហើយ។ ហើយសំខាន់បំផុតគួរឱ្យចាប់អារម្មណ៍។ ជាថ្មីម្តងទៀត ចងចាំយុវវ័យរបស់អ្នក។ យ៉ាងណាមិញ ខ្ញុំមិនបានក្លាយជា DBA ភ្លាមៗនោះទេ ខ្ញុំធំឡើងជាអ្នកសរសេរកម្មវិធី (BASIC, assembler, C, double-plus C, Oracle, plsql)។ ប្រធានបទគួរឱ្យចាប់អារម្មណ៍ ពិតណាស់សម្រាប់ការចងចាំដាច់ដោយឡែកមួយ ;-) ។
ទោះ​ជា​យ៉ាង​ណា សូម​កុំ​ឲ្យ​មាន​ការ​រំខាន។

ហើយដូច្នេះ,

ឬប្រហែលជាការបែងចែកនឹងជួយយើង?
Spoiler - "បាទ វាបានជួយ រួមទាំងការបង្កើនប្រសិទ្ធភាពប្រតិបត្តិការ។"

ប៉ុន្តែវាជារឿងខុសគ្នាទាំងស្រុង...

នៅ​មាន​ជា​បន្ត​ទៀត…

ប្រភព: www.habr.com