از استفاده از OFFSET و LIMIT در جستارهای صفحه بندی شده خودداری کنید

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

اگر برای مدت طولانی برنامه های کاربردی یا پایگاه داده طراحی می کنید، احتمالاً کدی برای اجرای پرس و جوهای صفحه بندی شده نوشته اید. به عنوان مثال، مانند این:

SELECT * FROM table_name LIMIT 10 OFFSET 40

اینطوری که هست؟

اما اگر صفحه بندی خود را اینگونه انجام دادید، متاسفم که بگویم به کارآمدترین روش این کار را انجام ندادید.

آیا می خواهید به من اعتراض کنید؟ میتوانی هیچ خرج کردن زمان. شل, Shopify и میکس آنها در حال حاضر از تکنیک هایی استفاده می کنند که امروز می خواهم در مورد آنها صحبت کنم.

حداقل یک توسعه دهنده باطن را نام ببرید که هرگز استفاده نکرده است OFFSET и LIMIT برای انجام پرس و جوهای صفحه بندی شده در MVP (حداقل محصول قابل دوام) و در پروژه هایی که از داده های کمی استفاده می شود، این رویکرد کاملاً قابل اجرا است. به اصطلاح "فقط کار می کند".

اما اگر نیاز به ایجاد سیستم های قابل اعتماد و کارآمد از ابتدا دارید، باید از قبل به کارایی پرس و جو از پایگاه های داده مورد استفاده در چنین سیستم هایی توجه کنید.

امروز ما در مورد مشکلات پیاده سازی متداول (خیلی بد) موتورهای پرس و جو صفحه بندی شده و نحوه دستیابی به عملکرد بالا در هنگام اجرای چنین پرس و جوهایی صحبت خواهیم کرد.

OFFSET و LIMIT چه اشکالی دارد؟

همانطور که قبلاً گفته شد ، OFFSET и LIMIT آنها در پروژه هایی که نیازی به کار با حجم زیاد داده ندارند، عملکرد خوبی دارند.

مشکل زمانی ایجاد می شود که پایگاه داده به اندازه ای بزرگ می شود که دیگر در حافظه سرور جای نمی گیرد. با این حال، هنگام کار با این پایگاه داده، باید از کوئری های صفحه بندی شده استفاده کنید.

برای اینکه این مشکل خود را نشان دهد، باید وضعیتی وجود داشته باشد که در آن DBMS به یک عملیات اسکن جدول کامل ناکارآمد در هر جستار صفحه‌بندی شده متوسل شود (در حالی که عملیات درج و حذف ممکن است رخ دهد، و ما به داده‌های قدیمی نیاز نداریم!).

«اسکن جدول کامل» (یا «اسکن جدول متوالی»، اسکن متوالی) چیست؟ این عملیاتی است که در طی آن DBMS به طور متوالی هر ردیف از جدول، یعنی داده های موجود در آن را می خواند و آنها را برای مطابقت با یک شرط مشخص بررسی می کند. این نوع اسکن جدول به عنوان کندترین شناخته شده است. واقعیت این است که وقتی اجرا می شود، عملیات ورودی/خروجی زیادی انجام می شود که زیرسیستم دیسک سرور را شامل می شود. این وضعیت با تأخیر مرتبط با کار با داده های ذخیره شده روی دیسک ها بدتر می شود و این واقعیت که انتقال داده ها از دیسک به حافظه عملیاتی با منابع فشرده است.

به عنوان مثال، شما رکورد 100000000 کاربر دارید و یک پرس و جو با ساختار اجرا می کنید. OFFSET 50000000. این بدان معناست که DBMS باید همه این رکوردها را بارگیری کند (و ما حتی به آنها نیازی نداریم!)، آنها را در حافظه قرار دهد و پس از آن، مثلاً 20 نتیجه گزارش شده در LIMIT.

فرض کنید ممکن است به این شکل باشد: "ردیف‌های 50000 تا 50020 را از 100000 انتخاب کنید". یعنی سیستم ابتدا باید 50000 سطر را برای تکمیل پرس و جو بارگذاری کند. می بینی چقدر کار غیر ضروری باید انجام دهد؟

اگر باور نمی کنید، به نمونه ای که با استفاده از ویژگی ها ایجاد کردم نگاه کنید db-fiddle.com. 

مثال در db-fiddle.com

آنجا، در سمت چپ، در میدان Schema SQL، کدی وجود دارد که 100000 ردیف را در پایگاه داده و در سمت راست در فیلد قرار می دهد. Query SQL، دو پرس و جو نشان داده شده است. اولین، کند، شبیه به این است:

SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;

و دومی که راه حل موثری برای همین مشکل است، به این صورت است:

SELECT *
FROM `docs`
WHERE id > 85000
LIMIT 10;

برای انجام این درخواست ها، فقط روی دکمه کلیک کنید Run در بالای صفحه. پس از انجام این کار، اطلاعات مربوط به زمان اجرای پرس و جو را با هم مقایسه می کنیم. به نظر می رسد که اجرای یک پرس و جوی ناکارآمد حداقل 30 برابر بیشتر از اجرای دومی طول می کشد (این زمان از اجرا به اجرا متفاوت است؛ برای مثال، سیستم ممکن است گزارش دهد که تکمیل کوئری اول 37 میلی ثانیه طول کشیده است، اما اجرای دوم - 1 میلی ثانیه).

و اگر داده های بیشتری وجود داشته باشد، همه چیز حتی بدتر به نظر می رسد (برای متقاعد شدن در این مورد، نگاهی به من بیندازید مثال با 10 میلیون ردیف).

آنچه که ما اکنون در مورد آن بحث کردیم باید به شما بینشی در مورد نحوه پردازش پرس و جوهای پایگاه داده بدهد.

لطفا توجه داشته باشید که ارزش بالاتر است OFFSET - تکمیل درخواست بیشتر طول می کشد.

به جای ترکیب OFFSET و LIMIT از چه چیزی استفاده کنم؟

به جای ترکیب OFFSET и LIMIT ارزش استفاده از ساختاری را دارد که طبق طرح زیر ساخته شده است:

SELECT * FROM table_name WHERE id > 10 LIMIT 20

این اجرای پرس و جو با صفحه بندی مبتنی بر مکان نما است.

به جای اینکه موارد فعلی را به صورت محلی ذخیره کنید OFFSET и LIMIT و آنها را با هر درخواست انتقال دهید، باید آخرین کلید اولیه دریافتی را ذخیره کنید (معمولاً اینطور است ID) و LIMIT، در نتیجه پرس و جوهایی مشابه موارد فوق بدست می آید.

چرا؟ نکته این است که با مشخص کردن شناسه آخرین ردیف خوانده شده، به DBMS خود می گویید که کجا باید جستجوی داده های لازم را آغاز کند. علاوه بر این، جستجو، به لطف استفاده از کلید، به طور موثر انجام می شود؛ سیستم مجبور نخواهد بود با خطوط خارج از محدوده مشخص شده منحرف شود.

بیایید نگاهی به مقایسه عملکرد زیر در پرس و جوهای مختلف بیندازیم. در اینجا یک پرس و جو بی اثر است.

درخواست کند

و در اینجا یک نسخه بهینه از این درخواست است.

درخواست سریع

هر دو کوئری دقیقاً همان مقدار داده را برمی گرداند. اما اولی 12,80 ثانیه طول می کشد و دومی 0,01 ثانیه طول می کشد. آیا تفاوت را احساس می کنید؟

مشکلات احتمالی

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

به طور طبیعی، هنگام ساخت کوئری ها، باید معماری خاص جداول را در نظر بگیرید و مکانیسم هایی را انتخاب کنید که در جداول موجود بهترین عملکرد را دارند. به عنوان مثال، اگر شما نیاز به کار در پرس و جوهایی با حجم زیادی از داده های مرتبط دارید، ممکن است برای شما جالب باشد این مقاله.

اگر با مشکل از دست دادن یک کلید اولیه مواجه هستیم، برای مثال، اگر جدولی با رابطه چند به چند داشته باشیم، روش سنتی استفاده از OFFSET и LIMIT، تضمین شده است که مناسب ما باشد. اما استفاده از آن ممکن است منجر به جستجوهای بالقوه کند شود. در چنین مواردی، من استفاده از کلید اصلی افزایش خودکار را توصیه می‌کنم، حتی اگر این کلید فقط برای رسیدگی به پرس و جوهای صفحه‌بندی شده مورد نیاز باشد.

اگر به این موضوع علاقه مند هستید - اینجا, اینجا и اینجا - چندین ماده مفید

نمایش نتایج: از

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

چگونه پرس و جوهای پایگاه داده را تحلیل و بهینه می کنید؟

منبع: www.habr.com