چطور paginate کنیم، آب زیرمون نره؟

صفحه‌بندی رکوردهای ذخیره شده در دیتابیس، بخش جدایی ناپذیر هر پروژه نرم افزاری، به خصوص از نوع وبشه. راهکار پیش فرض و دم دستی هم برای انجام این کار در دیتابیس‌های SQL استفاده از کلیدواژه‌های OFFSET و LIMIT هست. حتی چند ORMئی هم که من بررسی کردم، همه متد paginate رو با استفاده از همین دو کلیدواژه ترجمه می‌کردند.
استفاده از LIMIT و OFFSET خیلی راحت و قابل فهمه و زمانی که تعداد رکوردها کم باشه یا به کاربر اجازه گردش عمیق در صفحات (یعنی رفتن به شماره صفحات خیلی بالا) رو ندیم، آپشن کاملا مناسبیه. اما این نکته رو باید در نظر داشته باشیم که وقتی ما مثلا از OFFSET 1000 استفاده میکنیم، اینطور نیست که MySQL از روی 1000 رکورد اول بپره و مستقیم بره سراغ 1001 و از اونجا شروع کنه دیتا رو بگیره. بلکه به اندازه مجموع اعداد آفست و لیمیت رکوردها رو واکشی میکنه و داخل مموری میاره، بعدش اون تعدادی که با آفست مشخص شده رو دور میریزه و بقیه‌اش رو به اپ ما تحویل میده.
بنابراین اگر جدولی با مثلا یک میلیون رکورد دارید، تا زمانی که عدد آفست کوچیک باشه، مشکل خاصی ایجاد نمیشه. اما با افزایش آفست زمان پاسخ دهی به صورت خطی افزایش پیدا میکنه و اختلاف قابل ملاحظه‌ای رو خواهید دید که باید مراقبش باشید.

مشکل اصلی استفاده از OFFSET در کوئری اصلی

وقتی ما «مستقیما» از OFFSET در کوئری اصلی استفاده میکنیم، تعداد زیادی ردیف از جدول مورد نظر که ما در واقع قصد نادیده گرفتنشون رو داشتیم، ابتدا لود میشن توی مموری و قبل از خروجی دادن دور ریخته میشن. 🚮

چه زمانی این مشکل تشدید میشه؟
‫1- زمانی که تعداد رکوردهای جدول خیلی زیاد باشه
‫2- زمانی که جدول ستون‌های زیاد یا حجیمی داشته باشه

بنابراین، بدیهیه که برای بهبود اوضاع، یکی از 2 راه حل زیر رو پیش رو داریم:
‫1- حداقل تعداد «ردیف» رو لود کنیم
‫2- حداقل تعداد «ستون‌» رو لود کنیم

اول بریم سراغ مورد دوم، چون توضیح و پیاده‌سازیش ساده‌تره.

روش Deferred Join

به منظور لود کردن حداقل تعداد ستون ممکن در مموری، میتونیم از تکنیکی به نام Deferred Join استفاده کنیم. روش کار به این صورته که به جای استفاده «مستقیم» از آفست روی کوئری اصلی، باید آفست و لیمیت رو در یک «ساب‌کوئری» روی زیرمجموعه‌ای از جدول مورد نظر با حداقل تعداد ستون ممکن اعمال کنید و سپس اون رو با INNER JOIN به کوئری اصلی که در نظر داشتید الحاق بزنید. به مثال زیر توجه کنید:

SELECT * FROM products
INNER JOIN (
    SELECT id FROM products ORDER BY name, id LIMIT 100 OFFSET 500000
) AS tmp USING (id)
ORDER BY name, id

اتفاقی که میفته اینه که برای اون 500 هزار رکورد ناخواسته‌ای که توی مموری لود میشه، فقط دیتای ستون‌های درگیر در مرتب‌سازی لود میشه و دیتای تمام ستون‌ها فقط برای 100 رکورد مورد نیاز بیرون کشیده میشه.

همین تکنیک ساده، زمان لود دیتا رو حتی برای جدول‌هایی که تعداد ستون کمی دارند (مثلا 5 تا ستون سبک) حدودا 3 برابر کاهش میده. طبیعتا وقتی تعداد ستون‌ها زیاد باشه، این اختلاف میتونه چند ده برابر هم بشه.

دلیل استفاده از واژه deferred به معنای «به تعویق افتاده» هم به این دلیله که انجام عمل الحاق تا بعد از عمل pagination به تعویق میفته.

روش Cursor-based pagination

برای این که حداقل تعداد ردیف ممکن رو لود کنیم، چاره‌ای نداریم جز این که OFFSET رو به کلی کنار بذاریم و یه کاری کنیم که دیتابیس عزیزمون مستقیم بره سر اصل مطلب. حالا وقتی برای مشخص کردن رکوردهای مورد نظرمون دیگه آفست رو نداریم، چی برامون میمونه؟ آفرین، WHERE. برخلاف OFFSET ،WHERE اصلا دورریز نداره. یعنی میره دقیقا همون میزان دیتایی که میخوایم رو میاره توی مموری، نه بیشتر.

حالا برای فیلتر کردن با WHERE که به شرط نیاز داریم. شرطش چی باشه؟!

جواب ما رو دوستانی که روش Cursor-based pagination رو ابداع کردند، دادند. در این روش، ما از یک cursor یعنی نشانه یا مکان‌نما برای تعیین صفحه بعد نتایج استفاده می ‌کنیم.

لازمه تاکید کنم که این cursor که ازش صحبت می ‌کنیم، با database cursor که مفهوم کاملا متفاوتی هست فرق داره.

بذارید با یه مثال خیلی ساده مفهومش رو براتون شرح بدم.

SELECT * FROM products ORDER BY id LIMIT 10;

کوئری بالا که داره محصولات رو بر اساس id مرتب میکنه و 10 تای اول رو برمیگردونه. فرض کنید آی ‌دی آخرین رکوردی که خروجی داده 10 بوده که برای ما عدد مهمیه. چون باید حتما این عدد رو توی جوابی که به فرانت میدیم قرار بدیم. مثلا میتونیم جواب رو با چنین فرمتی بفرستیم:

{
    &quotnext_page&quot: &quot(id=10)&quot,
    &quotrecords&quot: [ 
        // ...
    ]
}

در مرحله بعد، فرانت باید برای گرفتن نتایج صفحه بعد، توی درخواست بعدی مقدار next_page ای که دریافت کرده رو به سرور بفرسته. کوئری اجرا شده بعد از تفسیر مقدار دریافت شده، در این مرحله به این شکل خواهد بود:

SELECT * FROM products WHERE id > 10 ORDER BY id LIMIT 10;

حالا اگه علاوه بر ستون id، محصولات رو ابتدا با ستون "نام" مرتب کرده باشیم چی؟

به مثال زیر توجه کنید:

SELECT * FROM products ORDER BY name, id LIMIT 10;

در این صورت، هم مقدار next_page متفاوت میشه، هم شرط WHERE کوئری کمی پیچیده ‌تر میشه. فرض کنید آخرین رکوردی که از کوئری بالا به دست میاد، نامش "NoteBook" و آی ‌دیش "309" باشه. در این صورت مقدار next_page و کوئری به ترتیب اینطور خواهند بود:

‭next_page = (name=NoteBook, id=309)

SELECT * FROM products WHERE
   (
       (name > 'NoteBook')
       OR
       (name = 'NoteBook' AND id > 309)
   )
ORDER BY name, id LIMIT 10;

همونطور که مشاهده میکنید، توی کوئری 2 تا شرط داریم که با "یا" به هم متصل شدند که به این صورت ترجمه میشه:

رکوردهایی که نامشون بعد از NoteBook هست یا نامشون مساوی با NoteBook هست ولی id بزرگتر از 309 دارند رو بهم بده.

خب، این هم Cursor-based Pagination بود که خیلی خلاصه خدمتتون توضیح دادم. مزیت این روش به طور واضح اینه که فقط دیتای مورد نیاز رو داخل رم لود میکنه. بزرگترین ایرادش هم اینه که قابلیت رفتن به یه شماره صفحه خاص مثلا صفحه 15 رو از دست میدیم. از این رو، استفاده از این روش، بیشتر برای لیست ‌هایی که به صورت اسکرول نمایش داده میشن توصیه میشه.

گفتن این نکته هم خالی از لطف نیست که معمولا مقدار next_page رو با کد گذاریbase64 رد و بدل میکنند که ما توی مثال ‌هامون برای ساده ‌سازی ازش صرف نظر کردیم.

جمع بندی

ممنون بابت وقتی که برای خوندن این نوشته گذاشتید. اگر نظری دارید یا راه‌های دیگه‌ای میشناسید، خوشحال میشم که همین جا یا در صفحه لینکدین من، با من در میون بذارید.