PostgreSQL Antipatterns: การนำทางรีจิสทรี

วันนี้จะไม่มีกรณีที่ซับซ้อนและอัลกอริธึมที่ซับซ้อนใน SQL ทุกอย่างจะง่ายมากในระดับ Captain Obvious - มาทำกัน กำลังดูรีจิสทรีเหตุการณ์ เรียงตามเวลา

นั่นคือมีสัญญาณอยู่ในฐานข้อมูล eventsและเธอก็มีทุ่งนา ts - เวลาที่เราต้องการแสดงบันทึกเหล่านี้อย่างเป็นระเบียบ:

CREATE TABLE events(
  id
    serial
      PRIMARY KEY
, ts
    timestamp
, data
    json
);

CREATE INDEX ON events(ts DESC);

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

#0. “ฉันเป็นผู้ฆ่าล้างเผ่าพันธุ์ของแม่ฉัน”

cur.execute("SELECT * FROM events;")
rows = cur.fetchall();
rows.sort(key=lambda row: row.ts, reverse=True);
limit = 26
print(rows[offset:offset+limit]);

เกือบจะไม่ใช่เรื่องตลก - หายาก แต่พบได้ในป่า บางครั้ง หลังจากทำงานกับ ORM แล้ว การเปลี่ยนไปใช้งาน "โดยตรง" กับ SQL อาจเป็นเรื่องยาก

แต่มาดูปัญหาที่พบบ่อยและชัดเจนน้อยลงกันดีกว่า

#1. ชดเชย

SELECT
  ...
FROM
  events
ORDER BY
  ts DESC
LIMIT 26 OFFSET $1; -- 26 - записей на странице, $1 - начало страницы

หมายเลข 26 มาจากไหน? นี่คือจำนวนรายการโดยประมาณที่จะกรอกหนึ่งหน้าจอ แม่นยำยิ่งขึ้นคือ บันทึกที่แสดง 25 รายการบวก 1 รายการเป็นการส่งสัญญาณว่ามีอย่างอื่นเพิ่มเติมอยู่ในตัวอย่างและสมเหตุสมผลที่จะดำเนินการต่อ

แน่นอนว่าค่านี้ไม่สามารถ "เย็บ" ลงในเนื้อหาของคำขอได้ แต่ส่งผ่านพารามิเตอร์ แต่ในกรณีนี้ ตัวกำหนดเวลา PostgreSQL จะไม่สามารถพึ่งพาความรู้ที่ว่าควรมีบันทึกค่อนข้างน้อย และจะเลือกแผนที่ไม่ได้ผลได้อย่างง่ายดาย

และในขณะที่อยู่ในอินเทอร์เฟซแอปพลิเคชัน การดูรีจิสทรีจะถูกนำไปใช้เป็นการสลับระหว่าง "เพจ" ที่มองเห็นได้ ไม่มีใครสังเกตเห็นสิ่งที่น่าสงสัยเป็นเวลานาน จนกระทั่งถึงช่วงเวลาที่ต้องดิ้นรนเพื่อความสะดวก UI/UX ตัดสินใจที่จะสร้างอินเทอร์เฟซใหม่ให้เป็น "การเลื่อนที่ไม่มีที่สิ้นสุด" นั่นคือรายการรีจิสตรีทั้งหมดจะถูกวาดไว้ในรายการเดียวที่ผู้ใช้สามารถเลื่อนขึ้นและลงได้

ดังนั้นในระหว่างการทดสอบครั้งถัดไป คุณจะถูกจับได้ การทำสำเนาบันทึก ในรีจิสทรี เพราะเหตุใดตารางจึงมีดัชนีปกติ (ts)คำถามของคุณขึ้นอยู่กับอะไร?

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

อันที่จริงมีปัญหาที่สองซ่อนอยู่ที่นี่ด้วย ซึ่งสังเกตได้ยากกว่ามาก - บางรายการจะไม่แสดง เลย! ท้ายที่สุดแล้ว บันทึกที่ "ซ้ำกัน" ก็เข้ามาแทนที่ของคนอื่น มีคำอธิบายโดยละเอียดพร้อมภาพที่สวยงาม อ่านที่นี่.

การขยายดัชนี

นักพัฒนาที่มีไหวพริบเข้าใจว่าคีย์ดัชนีจะต้องไม่ซ้ำกัน และวิธีที่ง่ายที่สุดคือการขยายด้วยฟิลด์ที่ไม่ซ้ำใครอย่างเห็นได้ชัด ซึ่ง PK เหมาะสำหรับ:

CREATE UNIQUE INDEX ON events(ts DESC, id DESC);

และคำขอก็กลายพันธุ์:

SELECT
  ...
ORDER BY
  ts DESC, id DESC
LIMIT 26 OFFSET $1;

#2. เปลี่ยนเป็น "เคอร์เซอร์"

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

SELECT
  ...
WHERE
  (ts, id) < ($1, $2) -- последние полученные на предыдущем шаге значения
ORDER BY
  ts DESC, id DESC
LIMIT 26;

คุณถอนหายใจโล่งอกจนมาถึง...

#3. ดัชนีการทำความสะอาด

เพราะวันหนึ่ง DBA ของคุณอ่าน บทความเกี่ยวกับการค้นหาดัชนีที่ไม่มีประสิทธิภาพ และตระหนักว่า การประทับเวลา "ไม่ล่าสุด" ไม่ดี. และฉันก็กลับมาหาคุณอีกครั้ง - ตอนนี้ด้วยความคิดว่าดัชนีนั้นจะยังคงกลับกลายเป็น (ts DESC).

แต่จะทำอย่างไรกับปัญหาเริ่มแรกของการบันทึก "การกระโดด" ระหว่างหน้าต่างๆ?.. และทุกอย่างก็ง่าย - คุณต้องเลือกบล็อกที่มีจำนวนระเบียนที่ไม่คงที่!

โดยทั่วไปแล้วใครห้ามไม่ให้เราอ่านไม่ใช่ "26 พอดี" แต่ "ไม่น้อยกว่า 26"? เช่นเพื่อที่จะได้มีบล็อกต่อไป บันทึกที่มีความหมายต่างกันอย่างเห็นได้ชัด ts - ถ้าอย่างนั้นจะไม่มีปัญหากับการบันทึก "กระโดด" ระหว่างบล็อก!

ต่อไปนี้เป็นวิธีดำเนินการ:

SELECT
  ...
WHERE
  ts < $1 AND
  ts >= coalesce((
    SELECT
      ts
    FROM
      events
    WHERE
      ts < $1
    ORDER BY
      ts DESC
    LIMIT 1 OFFSET 25
  ), '-infinity')
ORDER BY
  ts DESC;

เกิดอะไรขึ้นที่นี่?

1. เราขั้นตอนที่ 25 บันทึก "ลง" และรับค่า "ขอบเขต" ts.
2. หากไม่มีสิ่งใดอยู่แล้วให้แทนที่ค่า NULL ด้วย -infinity.
3. เราลบส่วนของค่าทั้งหมดระหว่างค่าที่ได้รับ ts และพารามิเตอร์ $1 ที่ส่งผ่านจากอินเทอร์เฟซ (ค่าที่แสดงผล "สุดท้าย" ก่อนหน้า)
4. ถ้าบล็อกถูกส่งคืนโดยมีระเบียนน้อยกว่า 26 รายการ จะเป็นบล็อกสุดท้าย

หรือภาพเดียวกัน:

เพราะตอนนี้เรามี กลุ่มตัวอย่างไม่มี "จุดเริ่มต้น" ที่เฉพาะเจาะจงใดๆจากนั้นไม่มีอะไรป้องกันเราจากการ "ขยาย" คำขอนี้ในทิศทางตรงกันข้ามและใช้งานการโหลดบล็อกข้อมูลจาก "จุดอ้างอิง" แบบไดนามิกในทั้งสองทิศทาง - ทั้งขึ้นและลง

คำพูด

1. ใช่ ในกรณีนี้ เราเข้าถึงดัชนีสองครั้ง แต่ทุกอย่าง "อิงตามดัชนีล้วนๆ" ดังนั้นแบบสอบถามย่อยจะส่งผลเท่านั้น ไปยังการสแกนดัชนีเพิ่มเติมอีกหนึ่งรายการเท่านั้น.
2. เห็นได้ชัดว่าเทคนิคนี้สามารถใช้ได้เมื่อคุณมีค่าเท่านั้น ts สามารถข้ามได้โดยบังเอิญเท่านั้นและ มีไม่มาก. หากกรณีทั่วไปของคุณคือ "หนึ่งล้านบันทึกในเวลา 00:00:00.000" คุณไม่ควรทำสิ่งนี้ ฉันหมายความว่าคุณไม่ควรปล่อยให้กรณีเช่นนี้เกิดขึ้น แต่หากสิ่งนี้เกิดขึ้น ให้ใช้ตัวเลือกที่มีดัชนีแบบขยาย

ที่มา: will.com