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-chains) - ມັນເປັນໄປໄດ້ແນວໃດທີ່ຈະ "ເພີ່ມຄວາມສໍາຄັນຂອງມັນ" ເພື່ອວ່າອັນທີສອງບໍ່ໄດ້ຖືກປະຕິບັດອີກເທື່ອຫນຶ່ງບໍ?

ມັນ turns ໃຫ້ເຫັນວ່າມັນເປັນໄປໄດ້ - ວິທີການ algorithmic ແມ່ນໃກ້ຊິດກັບຫົວຂໍ້ຂອງບົດຄວາມ PostgreSQL Antipatterns: ບັນທຶກທີ່ຫາຍາກຈະໄປຮອດກາງຂອງການເຂົ້າຮ່ວມ.

ໃຫ້ພຽງແຕ່ "shove" ທັງສອງເງື່ອນໄຂເຫຼົ່ານີ້ພາຍໃຕ້ກໍລະນີ:

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-fields ມີ​ຄວາມ​ຫມາຍ​ 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 ...

ແລະຖ້າທ່ານພິຈາລະນາວ່າຟັງຊັນ trigger ນີ້ສາມາດໃຊ້ໄດ້ພຽງແຕ່ໃນ UPDATE- ຜົນ​ກະ​ທົບ​ເນື່ອງ​ຈາກ​ມີ​ OLD/NEW ຢູ່ໃນສະພາບລະດັບເທິງ, ໂດຍທົ່ວໄປແລ້ວສະພາບການນີ້ສາມາດຖືກວາງໄວ້ໃນ WHEN- ສະ​ພາບ​ການ​, ດັ່ງ​ທີ່​ສະ​ແດງ​ໃຫ້​ເຫັນ​ໃນ #1 ...

ແຫຼ່ງຂໍ້ມູນ: www.habr.com