PostgreSQL Antipatterns: "ต้องมีเพียงอันเดียว!"

ใน SQL คุณอธิบายว่า "อะไร" ที่คุณต้องการบรรลุ ไม่ใช่ "อย่างไร" ที่ควรดำเนินการ ดังนั้นปัญหาในการพัฒนาแบบสอบถาม SQL ในลักษณะ "ตามที่ได้ยินคือวิธีการเขียน" จึงเข้ามาแทนที่อย่างมีเกียรติพร้อมกับ คุณสมบัติของเงื่อนไขการคำนวณใน SQL.

วันนี้ มาดูตัวอย่างง่ายๆ กันดีกว่าว่าสิ่งนี้สามารถนำไปสู่อะไรในบริบทของการใช้งาน GROUP/DISTINCT и LIMIT กับพวกเขา.

ตอนนี้ถ้าคุณเขียนในคำขอ “ก่อนอื่นให้เชื่อมต่อป้ายเหล่านี้แล้วโยนที่ซ้ำกันทั้งหมดออกไป ควรจะเหลือเพียงอันเดียวเท่านั้น อินสแตนซ์สำหรับแต่ละคีย์" - นี่คือวิธีการทำงานอย่างแน่นอน แม้ว่าจะไม่จำเป็นต้องเชื่อมต่อเลยก็ตาม

และบางครั้งคุณก็โชคดีและมันก็ "ใช้งานได้" บางครั้งมันก็ส่งผลเสียต่อประสิทธิภาพการทำงาน และบางครั้งก็ให้เอฟเฟกต์ที่ไม่คาดคิดเลยจากมุมมองของนักพัฒนา

อาจจะไม่น่าตื่นเต้นนัก แต่...

“คู่รักแสนหวาน”: เข้าร่วม + แตกต่าง

SELECT DISTINCT
  X.*
FROM
  X
JOIN
  Y
    ON Y.fk = X.pk
WHERE
  Y.bool_condition;

มันจะชัดเจนว่าพวกเขาต้องการอะไร เลือกบันทึก X ซึ่งมีบันทึกใน Y ที่เกี่ยวข้องกับเงื่อนไขที่ปฏิบัติตาม. เขียนคำขอผ่านทาง JOIN — ได้รับค่า pk หลายครั้ง (จำนวนรายการที่เหมาะสมที่ปรากฏใน Y) วิธีการลบ? แน่นอน DISTINCT!

เป็นเรื่องที่ “น่ายินดี” เป็นพิเศษเมื่อบันทึก X แต่ละรายการมีบันทึก Y ที่เกี่ยวข้องหลายร้อยรายการ จากนั้นรายการที่ซ้ำกันจะถูกลบออกอย่างกล้าหาญ...

จะแก้ไขอย่างไร? เริ่มต้นด้วยการตระหนักว่าปัญหาสามารถแก้ไขได้ “เลือกบันทึก X ซึ่งใน Y มีอย่างน้อยหนึ่งรายการที่เกี่ยวข้องกับเงื่อนไขที่ครบถ้วน” - สุดท้ายแล้ว เราไม่ต้องการอะไรจากบันทึก Y เลย

ซ้อนกันอยู่

SELECT
  *
FROM
  X
WHERE
  EXISTS(
    SELECT
      NULL
    FROM
      Y
    WHERE
      fk = X.pk AND
      bool_condition
    LIMIT 1
  );

PostgreSQL บางเวอร์ชันเข้าใจว่าใน EXISTS การค้นหารายการแรกๆ ที่ปรากฏขึ้นก็เพียงพอแล้ว ส่วนรายการเก่าๆ ไม่พบ ดังนั้นฉันจึงชอบที่จะระบุเสมอ LIMIT 1 ภายใน EXISTS.

เข้าร่วมด้านข้าง

SELECT
  X.*
FROM
  X
, LATERAL (
    SELECT
      Y.*
    FROM
      Y
    WHERE
      fk = X.pk AND
      bool_condition
    LIMIT 1
  ) Y
WHERE
  Y IS DISTINCT FROM NULL;

ตัวเลือกเดียวกันนี้จะช่วยให้สามารถส่งคืนข้อมูลบางส่วนจากบันทึก Y ที่เกี่ยวข้องได้ทันที หากจำเป็น มีการกล่าวถึงตัวเลือกที่คล้ายกันในบทความ "PostgreSQL Antipatterns: บันทึกที่หายากจะไปถึงตรงกลางของ JOIN".

“ทำไมต้องจ่ายมากกว่านี้”: ความแตกต่าง [เปิด] + จำกัด 1

ประโยชน์เพิ่มเติมของการแปลงคิวรีดังกล่าวคือความสามารถในการจำกัดการค้นหาเรกคอร์ดได้อย่างง่ายดาย หากจำเป็นต้องใช้เพียงหนึ่งหรือสองสามรายการ ดังในกรณีต่อไปนี้:

SELECT DISTINCT ON(X.pk)
  *
FROM
  X
JOIN
  Y
    ON Y.fk = X.pk
LIMIT 1;

ตอนนี้เราอ่านคำขอแล้วและพยายามทำความเข้าใจว่า DBMS เสนอให้ทำอะไร:

• เชื่อมต่อสัญญาณ
• มีเอกลักษณ์เฉพาะโดย X.pk
• จากรายการที่เหลือ ให้เลือกหนึ่งรายการ

แล้วคุณได้อะไร? "เพียงหนึ่งรายการ" จากตัวที่ไม่เหมือนใคร - แล้วถ้าเราเอาตัวที่ไม่ซ้ำตัวนี้มา ผลลัพธ์จะเปลี่ยนไปบ้างไหม.. “แล้วถ้าไม่มีส่วนต่างจะจ่ายเพิ่มทำไม?”

SELECT
  *
FROM
  (
    SELECT
      *
    FROM
      X
    -- сюда можно подсунуть подходящих условий
    LIMIT 1 -- +1 Limit
  ) X
JOIN
  Y
    ON Y.fk = X.pk
LIMIT 1;

และหัวข้อเดียวกันกับ GROUP BY + LIMIT 1.

“ฉันแค่ต้องถาม”: โดยนัย GROUP + LIMIT

สิ่งที่คล้ายกันเกิดขึ้นที่ต่างกัน การตรวจสอบไม่ว่างเปล่า ลงนามหรือ CTE เมื่อมีการร้องขอ:

...
CASE
  WHEN (
    SELECT
      count(*)
    FROM
      X
    LIMIT 1
  ) = 0 THEN ...

ฟังก์ชันรวม (count/min/max/sum/...) ดำเนินการได้สำเร็จกับทั้งชุด แม้ว่าจะไม่มีคำสั่งที่ชัดเจนก็ตาม GROUP BY. เท่านั้นด้วย LIMIT พวกเขาไม่เป็นมิตรมากนัก

นักพัฒนาสามารถคิดได้ “ถ้ามีบันทึกอยู่ที่นั่น ฉันก็ต้องการไม่เกิน LIMIT”. แต่อย่าทำอย่างนั้น! เพราะสำหรับฐานมันคือ:

• นับสิ่งที่พวกเขาต้องการ ตามบันทึกทั้งหมด
• ให้สายมากเท่าที่พวกเขาถาม

ขึ้นอยู่กับเงื่อนไขเป้าหมาย เหมาะสมที่จะทำการทดแทนอย่างใดอย่างหนึ่งต่อไปนี้:

• (count + LIMIT 1) = 0 บน NOT EXISTS(LIMIT 1)
• (count + LIMIT 1) > 0 บน EXISTS(LIMIT 1)
• count >= N บน (SELECT count(*) FROM (... LIMIT N))

“ เท่าไหร่ที่จะแขวนเป็นกรัม”: DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

นักพัฒนาที่ไร้เดียงสาอาจเชื่ออย่างจริงใจว่าคำขอจะหยุดดำเนินการ ทันทีที่เราพบ $1 ของค่าต่าง ๆ แรกที่เจอ.

ในอนาคตสิ่งนี้อาจและจะได้ผลด้วยโหนดใหม่ ดัชนีข้ามการสแกนซึ่งขณะนี้กำลังดำเนินการอยู่ แต่ยังไม่ได้ดำเนินการ

สำหรับตอนนี้ก่อน บันทึกทั้งหมดจะถูกเรียกคืนไม่ซ้ำกัน และเฉพาะจำนวนเงินที่ร้องขอเท่านั้นที่จะถูกส่งคืน เป็นเรื่องน่าเศร้าอย่างยิ่งหากเราต้องการอะไรแบบนี้ $ 1 = 4และในตารางก็มีบันทึกนับแสนรายการ...

เพื่อไม่ให้เสียใจอย่างเปล่าประโยชน์ ลองใช้แบบสอบถามแบบเรียกซ้ำกัน "DISTINCT มีไว้เพื่อคนจน" จาก PostgreSQL Wiki:

ที่มา: will.com