ProHoster > وبلاگ > اداره > Antipatterns PostgreSQL: پیمایش در رجیستری

Antipatterns PostgreSQL: پیمایش در رجیستری

امروزه هیچ مورد پیچیده و الگوریتم های پیچیده ای در 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 رکورد برگردانده شود، آخرین مورد است.

یا همین عکس:
Antipatterns PostgreSQL: پیمایش در رجیستری

چون الان داریم نمونه هیچ "آغاز" خاصی ندارد، پس هیچ چیز مانع از "گسترش" این درخواست در جهت مخالف و اجرای بارگذاری پویا بلوک های داده از "نقطه مرجع" در هر دو جهت - پایین و بالا - نمی شود.

توجه داشته باشید

  1. بله، در این مورد ما دو بار به ایندکس دسترسی داریم، اما همه چیز "صرفاً توسط شاخص" است. بنابراین، یک استعلام فرعی فقط نتیجه خواهد داد به یک فهرست اضافی فقط اسکن.
  2. کاملاً بدیهی است که این تکنیک تنها زمانی قابل استفاده است که مقادیری داشته باشید ts می تواند تنها به طور تصادفی عبور کند، و تعداد زیادی از آنها وجود ندارد. اگر مورد معمول شما «یک میلیون رکورد در ساعت 00:00:00.000» است، نباید این کار را انجام دهید. منظورم این است که شما نباید اجازه دهید چنین موردی رخ دهد. اما اگر این اتفاق افتاد، از گزینه با شاخص توسعه یافته استفاده کنید.

منبع: www.habr.com

اضافه کردن نظر