SQL HowTo: ترحيل المؤشر مع الفرز غير المناسب

وُلد هذا المنشور كإجابة موسعة لمشكلة المضاربة الموضحة في مقالة Chronicle of Paging .



لنفترض أن لدينا سجلًا للمستندات التي يعمل معها المشغلون أو المحاسبون في VLSI ، مثل هذا: تقليديًا ، في مثل هذا العرض ، إما الفرز المباشر (جديد من الأسفل) أو العكسي (جديد من أعلاه) حسب التاريخ والمعرف الترتيبي المعين عند إنشاء مستند يُستخدم - أو ... لقد ناقشت بالفعل المشكلات النموذجية التي تظهر في هذه الحالة في مقالة PostgreSQL Antipatterns: التنقل في السجل . ولكن ماذا لو أراد المستخدم لسبب ما "غير نمطي" - على سبيل المثال ، فرز حقل "مثل هذا" وآخر "مثل هذا" -







ORDER BY dt, idORDER BY dt DESC, id DESC



ORDER BY dt, id DESC؟ لكننا لا نريد إنشاء الفهرس الثاني ، لأنه يبطئ الإدراج والحجم الإضافي في قاعدة البيانات.



هل يمكن حل هذه المشكلة بشكل فعال باستخدام الفهرس فقط (dt, id)؟



دعنا نرسم أولاً كيفية ترتيب فهرسنا:







لاحظ أن الترتيب الذي يتم إنشاء إدخالات المعرف به لا يتطابق بالضرورة مع ترتيب dt ، لذلك لا يمكننا الاعتماد عليه ، وعلينا اختراع شيء ما.



لنفترض الآن أننا عند النقطة (أ ، 2) ونريد قراءة المدخلات الستة "التالية" بالفرز : آها! لقد اخترنا بعض "القطعة" من العقدة الأولى ، و "قطعة" أخرى من العقدة الأخيرة وجميع السجلات من العقد بينهما ( ). تتم قراءة كل كتلة من هذه الكتل بنجاح من خلال الفهرس ، على الرغم من الترتيب غير المناسب تمامًا. دعنا نحاول إنشاء استعلام مثل هذا:ORDER BY dt, id DESC







ACB(dt, id)

• نقرأ أولاً من الكتلة A "إلى اليسار" من سجل البداية - نحصل على Nالسجلات
• نقرأ كذلك L - N"على اليمين" للقيمة أ
• ابحث في الكتلة الأخيرة عن المفتاح الأقصى C
• قم بتصفية جميع السجلات من التحديد السابق باستخدام هذا المفتاح وطرحه "على اليمين"

الآن دعنا نحاول تصوير الكود والتحقق من النموذج:

CREATE TABLE doc(
  id
    serial
, dt
    date
);
CREATE INDEX ON doc(dt, id); --  

--  ""    
INSERT INTO doc(dt)
SELECT
  now()::date - (random() * 365)::integer
FROM
  generate_series(1, 10000);

من أجل عدم حساب عدد السجلات التي تمت قراءتها بالفعل والفرق بينه وبين الرقم المستهدف ، سنجبر PostgreSQL على القيام بذلك باستخدام "hack" UNION ALLو LIMIT:

(
  ... LIMIT 100
)
UNION ALL
(
  ... LIMIT 100
)
LIMIT 100

الآن دعنا نجمع السجلات المائة التالية مع الفرز المستهدف من آخر قيمة معروفة:(dt, id DESC)

WITH src AS (
  SELECT
    '{"dt" : "2019-09-07", "id" : 2331}'::json -- "" 
)
, pre AS (
  (
    ( --    100  ""     ""  A
      SELECT
        *
      FROM
        doc
      WHERE
        dt = ((TABLE src) ->> 'dt')::date AND
        id < ((TABLE src) ->> 'id')::integer
      ORDER BY
        dt DESC, id DESC
      LIMIT 100
    )
    UNION ALL
    ( --   100  ""    ""  A -> B, C
      SELECT
        *
      FROM
        doc
      WHERE
        dt > ((TABLE src) ->> 'dt')::date
      ORDER BY
        dt, id
      LIMIT 100
    )
  )
  LIMIT 100
)
--     C  ,  
, maxdt AS (
  SELECT
    max(dt)
  FROM
    pre
  WHERE
    dt > ((TABLE src) ->> 'dt')::date
)
( --  ""    C
  SELECT
    *
  FROM
    pre
  WHERE
    dt <> (TABLE maxdt)
  ORDER BY
    dt, id DESC --   ,    B       
  LIMIT 100
)
UNION ALL
( --  ""    C  100 
  SELECT
    *
  FROM
    doc
  WHERE
    dt = (TABLE maxdt)
  ORDER BY
    dt, id DESC
  LIMIT 100
)
LIMIT 100;

دعونا نرى ما حدث من حيث:





[انظر إلى الشرح. tensor.ru]

• لذلك ، A = '2019-09-07'قرأنا 3 سجلات باستخدام المفتاح الأول .
• انتهوا من قراءة 97 آخر عن طريق Bو Cيرجع ذلك إلى ضرب المحدد في Index Scan.
• من بين جميع السجلات ، تم ترشيح 18 بواسطة مفتاح الحد الأقصى C.
• قرأنا 23 سجلاً (بدلاً من 18 بحثًا عنها Bitmap Scan) باستخدام مفتاح الحد الأقصى.
• كل إعادة فرز وتقليص الهدف 100 سجل.
• ... وكل ذلك استغرق أقل من ميلي ثانية!

الطريقة ، بالطبع ، ليست عالمية ومع وجود الفهارس في عدد أكبر من الحقول ، فإنها ستتحول إلى شيء فظيع للغاية ، ولكن إذا كنت تريد حقًا منح المستخدم فرزًا "غير قياسي" دون "طي" القاعدة في Seq Scanجدول كبير ، فيمكنك استخدامه بعناية.