Не използвайте OFFSET и LIMIT в пагинирани заявки

Отминаха дните, когато не трябваше да се притеснявате за оптимизиране на производителността на базата данни. Времето не стои неподвижно. Всеки нов технологичен предприемач иска да създаде следващия Facebook, като същевременно се опитва да събере всички данни, до които може да се докопа. Бизнесът се нуждае от тези данни, за да обучава по-добре модели, които им помагат да правят пари. В такива условия програмистите трябва да създадат API, които им позволяват бързо и надеждно да работят с огромни количества информация.

Ако сте проектирали бекенд на приложение или база данни за определен период от време, вероятно сте писали код за изпълнение на пагинирани заявки. Например така:

SELECT * FROM table_name LIMIT 10 OFFSET 40

Начинът, по който е?

Но ако това е начинът, по който сте направили пагинацията си, съжалявам да кажа, че не сте го направили по най-ефективния начин.

Искаш ли да ми възразиш? Можете да не харча време. Застой, Shopify и Миксмакс Те вече използват техниките, за които искам да говоря днес.

Посочете поне един бекенд разработчик, който никога не е използвал OFFSET и LIMIT за извършване на странирани заявки. В MVP (Minimum Viable Product) и в проекти, където се използват малки количества данни, този подход е доста приложим. Това „просто работи“, така да се каже.

Но ако трябва да създадете надеждни и ефективни системи от нулата, трябва да се погрижите предварително за ефективността на заявките към базите данни, използвани в такива системи.

Днес ще говорим за проблемите с често използваните (много лоши) имплементации на пагинирани машини за заявки и как да постигнем висока производителност при изпълнение на такива заявки.

Какво не е наред с OFFSET и LIMIT?

Както вече казахме, OFFSET и LIMIT Те се представят добре в проекти, които не трябва да работят с големи количества данни.

Проблемът възниква, когато базата данни нарасне до такъв размер, че вече не се побира в паметта на сървъра. Въпреки това, когато работите с тази база данни, трябва да използвате пагинирани заявки.

За да се прояви този проблем, трябва да има ситуация, в която СУБД прибягва до неефективна операция за пълно сканиране на таблица при всяка пагинирана заявка (докато може да възникнат операции за вмъкване и изтриване и ние не се нуждаем от остарели данни!).

Какво е „пълно сканиране на таблици“ (или „последователно сканиране на таблици“, Sequential Scan)? Това е операция, по време на която СУБД последователно чете всеки ред от таблицата, т.е. данните, съдържащи се в нея, и ги проверява за съответствие с дадено условие. Известно е, че този тип сканиране на таблици е най-бавният. Факт е, че когато се изпълнява, се извършват много входно-изходни операции, които включват дисковата подсистема на сървъра. Ситуацията се влошава от латентността, свързана с работата с данни, съхранявани на дискове, и факта, че прехвърлянето на данни от диск към памет е ресурсоемка операция.

Например, имате записи за 100000000 XNUMX XNUMX потребители и изпълнявате заявка с конструкцията OFFSET 50000000. Това означава, че СУБД ще трябва да зареди всички тези записи (а ние дори не се нуждаем от тях!), да ги постави в паметта и след това да вземе, да речем, 20 резултата, отчетени в LIMIT.

Да кажем, че може да изглежда така: „изберете редове от 50000 50020 до 100000 50000 от XNUMX XNUMX“. Тоест системата първо ще трябва да зареди XNUMX XNUMX реда, за да завърши заявката. Виждате ли колко ненужна работа ще трябва да свърши?

Ако не ми вярвате, вижте примера, който създадох с помощта на функциите db-fiddle.com. 

Пример в db-fiddle.com

Там, вляво, в полето Schema SQL, има код, който вмъква 100000 XNUMX реда в базата данни, а отдясно в полето Query SQL, се показват две заявки. Първият, бавен, изглежда така:

SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;

А второто, което е ефективно решение на същия проблем, е така:

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

За да изпълните тези заявки, просто щракнете върху бутона Run в горната част на страницата. След като направим това, сравняваме информацията за времето за изпълнение на заявката. Оказва се, че изпълнението на неефективна заявка отнема поне 30 пъти повече време от изпълнението на втората (това време варира от изпълнение до изпълнение; например системата може да отчете, че първото запитване е отнело 37 ms, за да завърши, но изпълнението на секунда - 1 ms).

И ако има повече данни, тогава всичко ще изглежда още по-зле (за да се убедите в това, погледнете моя пример с 10 милиона реда).

Това, което току-що обсъдихме, трябва да ви даде известна представа за това как действително се обработват заявките към базата данни.

Моля, имайте предвид, че колкото по-висока е стойността OFFSET — толкова повече време ще отнеме изпълнението на заявката.

Какво трябва да използвам вместо комбинацията от OFFSET и LIMIT?

Вместо комбинация OFFSET и LIMIT Струва си да използвате структура, изградена по следната схема:

SELECT * FROM table_name WHERE id > 10 LIMIT 20

Това е изпълнение на заявка с пагинация, базирана на курсора.

Вместо да съхранявате текущите локално OFFSET и LIMIT и да ги предавате с всяка заявка, трябва да съхраните последния получен първичен ключ (обикновено това е ID) И LIMIT, в резултат на което ще се получат заявки, подобни на горните.

Защо? Въпросът е, че като изрично посочите идентификатора на последния прочетен ред, вие казвате на вашата СУБД откъде трябва да започне да търси необходимите данни. Освен това търсенето, благодарение на използването на ключа, ще се извършва ефективно; системата няма да трябва да се разсейва от линии извън определения диапазон.

Нека да разгледаме следното сравнение на производителността на различни заявки. Ето една неефективна заявка.

Бавно искане

А ето и оптимизирана версия на тази заявка.

Бърза заявка

И двете заявки връщат абсолютно същото количество данни. Но първият отнема 12,80 секунди, а вторият отнема 0,01 секунди. Усещате ли разликата?

Възможни проблеми

За да работи ефективно предложеният метод на заявка, таблицата трябва да има колона (или колони), съдържащи уникални, последователни индекси, като например целочислен идентификатор. В някои специфични случаи това може да определи успеха на използването на такива заявки за увеличаване на скоростта на работа с базата данни.

Естествено, когато конструирате заявки, трябва да вземете предвид специфичната архитектура на таблиците и да изберете тези механизми, които ще работят най-добре на съществуващите таблици. Например, ако трябва да работите в заявки с големи обеми свързани данни, може да ви се стори интересно това статия.

Ако сме изправени пред проблема с липсата на първичен ключ, например, ако имаме таблица с връзка много към много, тогава традиционният подход за използване OFFSET и LIMIT, гарантирано ще ни подхожда. Но използването му може да доведе до потенциално бавни заявки. В такива случаи бих препоръчал използването на автоматично нарастващ първичен ключ, дори ако е необходим само за обработка на пагинирани заявки.

Ако се интересувате от тази тема - тук, тук и тук - няколко полезни материала.

Резултати от

Основният извод, който можем да направим е, че независимо за какъв размер бази данни говорим, винаги е необходимо да се анализира скоростта на изпълнение на заявките. В днешно време скалируемостта на решенията е изключително важна и ако всичко е проектирано правилно от самото начало на работа върху дадена система, това в бъдеще може да спести разработчика от много проблеми.

Как анализирате и оптимизирате заявките към базата данни?

Източник: www.habr.com