🥇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: หรือ/และโซ่

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

มิฉะนั้นสามารถรับได้ทั้งสองอย่าง EXISTS จะเป็นจริงแต่ ทั้งสองจะถูกประหารชีวิต.

แต่ถ้าเรารู้แน่นอนว่าหนึ่งในนั้น "จริง" บ่อยกว่ามาก (หรือ "เท็จ" - สำหรับ AND-chains) - เป็นไปได้ไหมที่จะ "เพิ่มลำดับความสำคัญ" เพื่อให้อันที่สองไม่ถูกดำเนินการอีกครั้ง?

ปรากฎว่าเป็นไปได้ - วิธีการอัลกอริทึมนั้นใกล้เคียงกับหัวข้อของบทความ PostgreSQL Antipatterns: รายการหายากถึงกลาง JOIN.

ลอง "ดันภายใต้ 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-operator เปรียบเทียบบันทึกทั้งหมดในครั้งเดียว:

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
( {... неравенства} )

อ๊ะ... ความจริงแล้วกลายเป็นว่าในกรณีที่ความจริงข้อใดข้อหนึ่งใน XNUMX ข้อแรก เงื่อนไขทั้งหมดกลายเป็น 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...

ที่มา: will.com

PostgreSQL Antipatterns: การประเมินเงื่อนไขใน SQL