🥇أنماط PostgreSQL المضادة: التنقل في التسجيل

اليوم لن تكون هناك حالات معقدة وخوارزميات معقدة في 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) ويكون "مسرورًا" بطلباتك يقومون بتحميل الخادم مثل الجحيم بقواعد الإزاحة الخاصة بهموبشكل عام، حان الوقت للتبديل إلى التنقل من القيمة الأخيرة المعروضة. يتغير استعلامك مرة أخرى:

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;

ما الذي يحدث هنا؟

نخطو 25 سجلًا "لأسفل" ونحصل على قيمة "الحدود". ts.
إذا لم يكن هناك شيء هناك بالفعل، فاستبدل القيمة NULL بـ -infinity.
نقوم بطرح شريحة القيم بأكملها بين القيمة المستلمة ts وتم تمرير المعلمة $1 من الواجهة (القيمة المقدمة "الأخيرة" السابقة).
إذا تم إرجاع كتلة بأقل من 26 سجلاً، فهي الأخيرة.

أو نفس الصورة:

لأن الآن لدينا ليس للعينة أي "بداية" محددةإذن لا شيء يمنعنا من "توسيع" هذا الطلب في الاتجاه المعاكس وتنفيذ التحميل الديناميكي لكتل البيانات من "النقطة المرجعية" في كلا الاتجاهين - للأسفل وللأعلى.

تعليق

نعم، في هذه الحالة يمكننا الوصول إلى الفهرس مرتين، ولكن كل شيء يتم "بواسطة الفهرس فقط". لذلك، سيؤدي الاستعلام الفرعي فقط إلى إلى مسح فهرس إضافي واحد فقط.
من الواضح تمامًا أنه لا يمكن استخدام هذه التقنية إلا عندما تكون لديك قيم ts لا يمكن العبور إلا بالصدفة، و لا يوجد الكثير منهم. إذا كانت حالتك النموذجية هي "مليون سجل في 00:00:00.000"، فلا ينبغي عليك القيام بذلك. أعني أنه لا ينبغي أن تسمح بحدوث مثل هذه الحالة. ولكن إذا حدث ذلك، استخدم الخيار مع فهرس موسع.

المصدر: www.habr.com

أنماط PostgreSQL المضادة: التنقل في السجل