ProHoster > Blog > İdarə > PostgreSQL Antipatterns: SQL-də Vəziyyətin Qiymətləndirilməsi

PostgreSQL Antipatterns: SQL-də Vəziyyətin Qiymətləndirilməsi

SQL nə C++, nə də JavaScript. Buna görə də məntiqi ifadələrin qiymətləndirilməsi fərqlidir və bu heç də eyni şey deyil:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

PostgreSQL sorğusunun icra planını optimallaşdırarkən ekvivalent şərtləri özbaşına “yenidən təşkil” edə bilər, fərdi qeydlər üçün bunların heç birini hesablamayın, tətbiq olunan indeksin vəziyyətinə baxın ... Bir sözlə, ən asan yol güman etməkdir ki, siz idarə edə bilmir onların hansı qaydada olacağı (və ümumiyyətlə hesablanacaqmı) bərabərdir şərtləri.

Buna görə də, hələ də prioriteti idarə etmək istəyirsinizsə, struktur olaraq etməlisiniz bu şərtləri qeyri-bərabər edin şərti ilə ifadələri и operatorlar.

PostgreSQL Antipatterns: SQL-də Vəziyyətin Qiymətləndirilməsi
Məlumat və onlarla işləmək əsasdır VLSI kompleksimizin, buna görə də onlar üzərində əməliyyatların təkcə düzgün deyil, həm də səmərəli şəkildə aparılması bizim üçün çox vacibdir. İfadə qiymətləndirməsində səhvlərə yol verilə biləcəyi və onların səmərəliliyini artırmağa dəyər olduğu konkret nümunələrə baxaq.

# 0: RTFM

Başlanır sənədlərdən nümunə:

Qiymətləndirmə sırası vacib olduqda, konstruksiya ilə düzəldilə bilər CASE. Məsələn, bir cümlədə sıfıra bölünmənin qarşısını almaq üçün bu üsul WHERE etibarsız:

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

Təhlükəsiz seçim:

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

İstifadə olunan tikinti CASE ifadəni optimallaşdırmadan qoruyur, ona görə də yalnız lazım olduqda istifadə edilməlidir.

# 1: tetikleme vəziyyəti

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

Hər şey yaxşı görünür, amma... Heç kim söz vermir ki, sərmayə qoyulur SELECT birinci şərt yalan olarsa icra olunmayacaq. ilə düzəldin yuvalanmış IF:

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

İndi diqqətlə baxaq - trigger funksiyasının bütün bədəni "bükülmüş" oldu IF. Və bu o deməkdir ki, heç bir şey bizə bu vəziyyəti istifadə edərək prosedurdan çıxarmağa mane olmur WHEN-şərtlər:

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

Bu yanaşma, şərt yanlış olarsa, zəmanətlə server resurslarına qənaət etməyə imkan verir.

# 2: OR/AND zəncir

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

Əks halda, hər ikisi əldə edilə bilər EXISTS doğru olacaq, amma hər ikisi edam olunacaq.

Ancaq onlardan birinin daha tez-tez "doğru" olduğunu dəqiq bilsək (və ya "yalan" - üçün AND-zəncirlər) - ikincinin bir daha icra olunmaması üçün hansısa şəkildə "öz prioritetini artırmaq" mümkündürmü?

Belə çıxır ki, bu mümkündür - alqoritmik yanaşma məqalənin mövzusuna yaxındır PostgreSQL Antipatterns: Nadir giriş JOIN-in ortasına çatır.

Gəlin bu şərtlərin hər ikisini "CASE altında itələyək":

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

Bu vəziyyətdə biz müəyyən etmədik ELSE-dəyər, yəni hər iki şərt yalan olarsa CASE geri dönəcək NULL, kimi şərh olunur FALSE в WHERE- şərtlər.

Bu nümunə başqa bir şəkildə birləşdirilə bilər - dadmaq və rəngləmək üçün:

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

# 3: şərtləri necə yazmaq olmaz

Bu tetikleyicinin "qəribə" işə salınmasının səbəblərini təhlil etməyə iki gün sərf etdik - görək niyə.

Mənbə:

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 ...

Problem №1: Bərabərsizlik NULL-u nəzərə almır

Tutaq ki, hər şey OLD- sahələr önəmlidi NULL. Nə olacaq?

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

Və şəraitin işlənməsi baxımından NULL ekvivalent FALSE, yuxarıda qeyd edildiyi kimi.

qərar: operatordan istifadə edin IS DISTINCT FROM etibarən ROW- bütün qeydləri bir anda müqayisə edən operator:

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

Problem № 2: eyni funksionallığın fərqli həyata keçirilməsi

Müqayisə edək:

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

Niyə əlavə investisiyalar var SELECT? Bir funksiya to_regclass? Niyə fərqlidir...

Gəlin düzəldək:

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

Problem №3: bool üstünlüyü

Mənbəni formatlaşdıraq:

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

Vay... Əslində, məlum oldu ki, ilk iki şərtdən hər hansı birinin həqiqəti olduğu halda, bütün şərt belə olur. TRUE, bərabərsizlikləri nəzərə almadan. Və bu heç də bizim istədiyimiz deyil.

Gəlin düzəldək:

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

Problem №4 (kiçik): bir sahə üçün mürəkkəb VƏ ya şərt

Əslində 3-cü yerdə məhz üç şərt olduğu üçün problemlər yaşadıq. Ancaq bunların əvəzinə mexanizmdən istifadə edərək, biri ilə əldə edə bilərsiniz coalesce ... IN:

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

Elə biz də NULL "tutmaq" və mürəkkəbdir OR Mötərizələrlə məşğul olmaq lazım deyil.

Ümumi

Əldə etdiyimiz şeyi düzəldək:

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

Və bu trigger funksiyasının yalnız istifadə edilə biləcəyini nəzərə alsaq UPDATEmövcudluğuna görə tətik OLD/NEW yuxarı səviyyəli vəziyyətdə, o zaman bu vəziyyət ümumiyyətlə çıxarıla bilər WHEN-1-də göstərildiyi kimi vəziyyət...

Mənbə: www.habr.com

Добавить комментарий