🥇PostgreSQL Antipatterns: SQL дэх нөхцөл байдлын үнэлгээ

SQL нь C++ биш, JavaScript ч биш. Тиймээс логик илэрхийллийн үнэлгээ нь өөр бөгөөд энэ нь ижил зүйл биш юм.

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

PostgreSQL асуулгын гүйцэтгэлийн төлөвлөгөөг оновчтой болгохын зэрэгцээ тэнцэх нөхцлүүдийг дур мэдэн "дахин зохицуулж" болно, тэдгээрийн аль нэгийг нь хувийн бүртгэлд тооцож болохгүй, ашигласан индексийн нөхцөлийг харна уу ... Товчхондоо, хамгийн хялбар арга бол та удирдаж чадахгүй тэдгээрийн дараалал (мөн тэдгээрийг огт тооцох эсэх) тэнцүү нэр томъёо.

Тиймээс, хэрэв та тэргүүлэх чиглэлийг удирдахыг хүсч байгаа бол бүтцийн хувьд хийх хэрэгтэй Эдгээр нөхцлийг тэгш бус болгох нөхцөлтэй илэрхийлэл и операторууд.

Өгөгдөл ба тэдэнтэй ажиллах нь үндэс суурь юм манай VLSI цогцолборын, тиймээс тэдгээрт хийсэн үйлдлүүдийг зөвхөн зөв төдийгүй үр дүнтэй гүйцэтгэх нь бидний хувьд маш чухал юм. Илэрхийллийн үнэлгээнд алдаа гаргаж болох тодорхой жишээнүүдийг авч үзье, тэдгээрийн үр ашгийг дээшлүүлэх нь зүйтэй.

№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-гинж) - хоёр дахь нь дахин гүйцэтгэхгүйн тулд ямар нэгэн байдлаар "түүний ач холбогдлыг нэмэгдүүлэх" боломжтой юу?

Энэ нь боломжтой болох нь харагдаж байна - алгоритмын арга нь нийтлэлийн сэдэвтэй ойрхон байна PostgreSQL Antipatterns: Ховор оруулга нь JOIN-ийн дунд хүрдэг.

Эдгээр нөхцөлүүдийн аль алиныг нь "CASE дор түлхэцгээе":

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

Энэ тохиолдолд бид тодорхойлоогүй байна ELSE-утга, өөрөөр хэлбэл хоёр нөхцөл худал бол 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: bool давуу байдал

Эх сурвалжийг форматлацгаая:

{... 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-Нөхцөл байдал №1-д үзүүлсэн шиг...

Эх сурвалж: www.habr.com

PostgreSQL Antipatterns: SQL дэх нөхцөл байдлын үнэлгээ