PostgreSQL Antipatterns៖ ការវាយតម្លៃស្ថានភាពនៅក្នុង SQL

SQL មិនមែនជា C ++ ហើយក៏មិនមែនជា JavaScript ដែរ។ ដូច្នេះ ការវាយតម្លៃនៃការបញ្ចេញមតិឡូជីខលគឺខុសគ្នា ហើយនេះមិនមែនជារឿងដូចគ្នាទាល់តែសោះ៖

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

ខណៈពេលដែលការបង្កើនប្រសិទ្ធភាពផែនការប្រតិបត្តិនៃសំណួរ PostgreSQL អាច "រៀបចំឡើងវិញ" តាមអំពើចិត្តនូវលក្ខខណ្ឌសមមូល, កុំគណនាណាមួយនៃពួកវាសម្រាប់កំណត់ត្រាបុគ្គល, យោងទៅលក្ខខណ្ឌនៃលិបិក្រមដែលបានអនុវត្ត ... និយាយឱ្យខ្លី វិធីងាយស្រួលបំផុតគឺសន្មត់ថាអ្នក មិនអាចគ្រប់គ្រងបានទេ។ លំដាប់ដែលពួកគេនឹងត្រូវបាន (និងថាតើពួកគេនឹងត្រូវបានគេគណនាទាំងអស់) ស្មើ លក្ខខណ្ឌ។

ដូច្នេះហើយ បើអ្នកនៅតែចង់គ្រប់គ្រងអាទិភាព អ្នកត្រូវរៀបចំរចនាសម្ព័ន្ធ ធ្វើឱ្យលក្ខខណ្ឌទាំងនេះមិនស្មើគ្នា ជាមួយនឹងលក្ខខណ្ឌ កន្សោម и ប្រតិបត្តិករ.

ទិន្នន័យ និងការធ្វើការជាមួយពួកគេគឺជាមូលដ្ឋាន នៃ VLSI complex របស់យើង។ដូច្នេះវាមានសារៈសំខាន់ខ្លាំងណាស់សម្រាប់ពួកយើងដែលប្រតិបត្តិការលើពួកវាត្រូវបានអនុវត្តមិនត្រឹមតែត្រឹមត្រូវប៉ុណ្ណោះទេ ប៉ុន្តែថែមទាំងមានប្រសិទ្ធភាពទៀតផង។ សូមក្រឡេកមើលឧទាហរណ៍ជាក់ស្តែងដែលកំហុសក្នុងការវាយតម្លៃការបញ្ចេញមតិអាចត្រូវបានធ្វើឡើង និងកន្លែងដែលវាមានតម្លៃក្នុងការបង្កើនប្រសិទ្ធភាពរបស់ពួកគេ។

# 0: RTFM

ចាប់ផ្តើម ឧទាហរណ៍ពីឯកសារ:

នៅពេលដែលលំដាប់នៃការវាយតម្លៃមានសារៈសំខាន់ វាអាចត្រូវបានជួសជុលជាមួយនឹងការសាងសង់ CASE. ឧទាហរណ៍ វិធីនេះដើម្បីជៀសវាងការបែងចែកដោយសូន្យក្នុងប្រយោគមួយ។ WHERE មិនគួរឱ្យទុកចិត្ត៖

SELECT ... WHERE x > 0 AND y/x > 1.5;

ជម្រើសសុវត្ថិភាព៖

SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;

សំណង់ដែលបានប្រើ CASE ការពារកន្សោមពីការបង្កើនប្រសិទ្ធភាព ដូច្នេះវាគួរតែត្រូវបានប្រើនៅពេលចាំបាច់ប៉ុណ្ណោះ។

លេខ 1: ស្ថានភាពកេះ

BEGIN
  IF cond(NEW.fld) AND EXISTS(SELECT ...) THEN
    ...
  END IF;
  RETURN NEW;
END;

អ្វីគ្រប់យ៉ាងហាក់ដូចជាល្អ ប៉ុន្តែ... គ្មាននរណាម្នាក់សន្យាថាការវិនិយោគនោះទេ។ SELECT នឹងមិនត្រូវបានប្រតិបត្តិទេ ប្រសិនបើលក្ខខណ្ឌទីមួយមិនពិត។ ជួសជុលវាជាមួយ សំបុក IF:

BEGIN
  IF cond(NEW.fld) THEN
    IF EXISTS(SELECT ...) THEN
      ...
    END IF;
  END IF;
  RETURN NEW;
END;

ឥឡូវនេះសូមមើលដោយប្រុងប្រយ័ត្ន - តួទាំងមូលនៃមុខងារកេះបានប្រែទៅជា "រុំ" IF. ហើយនេះមានន័យថាគ្មានអ្វីរារាំងយើងពីការដកលក្ខខណ្ឌនេះចេញពីនីតិវិធីដោយប្រើ WHEN- លក្ខខណ្ឌ:

BEGIN
  IF EXISTS(SELECT ...) THEN
    ...
  END IF;
  RETURN NEW;
END;
...
CREATE TRIGGER ...
  WHEN cond(NEW.fld);

វិធីសាស្រ្តនេះអនុញ្ញាតឱ្យអ្នករក្សាទុកធនធានម៉ាស៊ីនមេជាមួយនឹងការធានាប្រសិនបើលក្ខខណ្ឌមិនពិត។

# 2: OR/AND ខ្សែសង្វាក់

SELECT ... WHERE EXISTS(... A) OR EXISTS(... B)

បើមិនដូច្នោះទេវាអាចទទួលបានទាំងពីរ EXISTS នឹងក្លាយជាការពិត ប៉ុន្តែ ទាំងពីរនឹងត្រូវបានប្រតិបត្តិ.

ប៉ុន្តែប្រសិនបើយើងដឹងច្បាស់ថាមួយក្នុងចំណោមពួកគេគឺ "ពិត" ច្រើនជាញឹកញាប់ (ឬ "មិនពិត" - សម្រាប់ AND-chains) - តើវាអាចទៅរួចដោយរបៀបណាដើម្បី "បង្កើនអាទិភាពរបស់វា" ដូច្នេះថាទីពីរមិនត្រូវបានប្រតិបត្តិម្តងទៀតទេ?

វាប្រែថាវាអាចទៅរួច - វិធីសាស្រ្តនៃក្បួនដោះស្រាយគឺនៅជិតប្រធានបទនៃអត្ថបទ PostgreSQL Antipatterns៖ ធាតុដ៏កម្រឈានដល់ពាក់កណ្តាលនៃ JOIN.

ចូរយើងគ្រាន់តែ "shove under CASE" លក្ខខណ្ឌទាំងពីរនេះ៖

SELECT ...
WHERE
  CASE
    WHEN EXISTS(... A) THEN TRUE
    WHEN EXISTS(... B) THEN TRUE
  END

ក្នុងករណីនេះយើងមិនបានកំណត់ទេ។ ELSE-value នោះគឺប្រសិនបើលក្ខខណ្ឌទាំងពីរមិនពិត CASE និ​ឹ​ង​ត្រឡប់មកវិញ NULLដែលត្រូវបានបកស្រាយថាជា FALSE в WHERE- លក្ខខណ្ឌ។

ឧទាហរណ៍នេះអាចត្រូវបានផ្សំនៅក្នុងវិធីមួយផ្សេងទៀត - ដើម្បីភ្លក្សរសជាតិនិងពណ៌:

SELECT ...
WHERE
  CASE
    WHEN NOT EXISTS(... A) THEN EXISTS(... B)
    ELSE TRUE
  END

# 3: របៀបសរសេរលក្ខខណ្ឌ

យើងបានចំណាយពេលពីរថ្ងៃលើការវិភាគអំពីហេតុផលសម្រាប់ការកេះ "ចម្លែក" នៃគន្លឹះនេះ - តោះមើលមូលហេតុ។

ប្រភព៖

IF( NEW."Документ_" is null or NEW."Документ_" = (select '"Комплект"'::regclass::oid) or NEW."Документ_" = (select to_regclass('"ДокументПоЗарплате"')::oid)
     AND (   OLD."ДокументНашаОрганизация" <> NEW."ДокументНашаОрганизация"
          OR OLD."Удален" <> NEW."Удален"
          OR OLD."Дата" <> NEW."Дата"
          OR OLD."Время" <> NEW."Время"
          OR OLD."ЛицоСоздал" <> NEW."ЛицоСоздал" ) ) THEN ...

បញ្ហាទី 1៖ វិសមភាពមិនរាប់បញ្ចូល NULL ទេ។

ចូរសន្មតថាអ្វីៗទាំងអស់។ OLD- វាលសំខាន់ NULL. តើនឹងមានអ្វីកើតឡើង?

SELECT NULL <> 1 OR NULL <> 2;
-- NULL

ហើយតាមទស្សនៈនៃការបំពេញលក្ខខណ្ឌ NULL សមមូល FALSEដូចដែលបានរៀបរាប់ខាងលើ។

ការសម្រេចចិត្ត៖ ប្រើប្រតិបត្តិករ IS DISTINCT FROM ពី ROW- ប្រតិបត្តិករ ប្រៀបធៀបកំណត់ត្រាទាំងមូលក្នុងពេលតែមួយ៖

SELECT (NULL, NULL) IS DISTINCT FROM (1, 2);
-- TRUE

បញ្ហាទី 2៖ ការអនុវត្តផ្សេងគ្នានៃមុខងារដូចគ្នា។

ប្រៀបធៀប:

NEW."Документ_" = (select '"Комплект"'::regclass::oid)
NEW."Документ_" = (select to_regclass('"ДокументПоЗарплате"')::oid)

ហេតុអ្វីបានជាមានការវិនិយោគបន្ថែម SELECT? មុខងារមួយ។ to_regclass? ហេតុអ្វីខុសគ្នា...

តោះជួសជុល៖

NEW."Документ_" = '"Комплект"'::regclass::oid
NEW."Документ_" = '"ДокументПоЗарплате"'::regclass::oid

បញ្ហាទី 3៖ បូលនាំមុខ

តោះធ្វើទ្រង់ទ្រាយប្រភព៖

{... IS NULL} OR
{... Комплект} OR
{... ДокументПоЗарплате} AND
( {... неравенства} )

អូ៎… តាមពិតទៅ វាបានប្រែក្លាយថា ក្នុងករណីការពិតនៃលក្ខខណ្ឌទាំងពីរដំបូង លក្ខខណ្ឌទាំងមូលប្រែទៅជា TRUEដោយមិនយកចិត្តទុកដាក់លើវិសមភាព។ ហើយនេះមិនមែនជាអ្វីដែលយើងចង់បាននោះទេ។

តោះជួសជុល៖

(
  {... IS NULL} OR
  {... Комплект} OR
  {... ДокументПоЗарплате}
) AND
( {... неравенства} )

បញ្ហាទី 4 (តូច)៖ លក្ខខណ្ឌស្មុគស្មាញ OR សម្រាប់វាលមួយ។

តាមពិតទៅ យើងមានបញ្ហាក្នុងលេខ 3 យ៉ាងជាក់លាក់ ព្រោះមានលក្ខខណ្ឌបី។ ប៉ុន្តែជំនួសឱ្យពួកគេ អ្នកអាចទទួលបានដោយមួយដោយប្រើយន្តការ coalesce ... IN:

coalesce(NEW."Документ_"::text, '') IN ('', '"Комплект"', '"ДокументПоЗарплате"')

យើងក៏ដូចគ្នាដែរ។ NULL "ចាប់" និងស្មុគស្មាញ OR អ្នកមិនចាំបាច់រវល់ជាមួយវង់ក្រចកទេ។

សរុប

តោះជួសជុលអ្វីដែលយើងទទួលបាន៖

IF (
  coalesce(NEW."Документ_"::text, '') IN ('', '"Комплект"', '"ДокументПоЗарплате"') AND
  (
    OLD."ДокументНашаОрганизация"
  , OLD."Удален"
  , OLD."Дата"
  , OLD."Время"
  , OLD."ЛицоСоздал"
  ) IS DISTINCT FROM (
    NEW."ДокументНашаОрганизация"
  , NEW."Удален"
  , NEW."Дата"
  , NEW."Время"
  , NEW."ЛицоСоздал"
  )
) THEN ...

ហើយបានផ្តល់ឱ្យថាមុខងារកេះនេះអាចប្រើបានតែនៅក្នុង UPDATEកេះដោយសារតែវត្តមាន OLD/NEW នៅ​ក្នុង​លក្ខខណ្ឌ​កម្រិត​ខ្ពស់​, បន្ទាប់​មក​លក្ខខណ្ឌ​នេះ​ជា​ទូទៅ​អាច​ត្រូវ​បាន​យក​ចេញ​នៅ​ក្នុង​ WHEN-លក្ខខណ្ឌដូចបង្ហាញក្នុងលេខ១...

ប្រភព: www.habr.com