PostgreSQL Antipatterns: ניווט ברישום

היום לא יהיו מקרים מורכבים ואלגוריתמים מתוחכמים ב-SQL. הכל יהיה פשוט מאוד, ברמה של קפטן ברור - בואו נעשה את זה צפייה במרשם האירועים ממוין לפי זמן.

כלומר, יש שלט במסד הנתונים 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", אתה לא צריך לעשות זאת. כלומר, אתה לא צריך לאפשר למקרה כזה לקרות. אבל אם זה קורה, השתמש באפשרות עם אינדקס מורחב.

מקור: www.habr.com