Помина времето кога не требаше да се грижите за оптимизирање на перформансите на базата на податоци. Времето не застанува. Секој нов технолошки претприемач сака да го создаде следниот Фејсбук, додека се обидува да ги собере сите податоци до кои може да дојде. На бизнисите им се потребни овие податоци за подобро да обучуваат модели кои им помагаат да заработат пари. Во такви услови, програмерите треба да создадат API кои ќе им овозможат брзо и сигурно да работат со огромни количини на информации.
Ако сте дизајнирале позадини на апликации или бази на податоци долго време, веројатно сте напишале код за извршување на страницирани прашања. На пример, вака:
SELECT * FROM table_name LIMIT 10 OFFSET 40
Како што е?
Но, ако вака си го правел твојата страница, жал ми е што можам да кажам дека не си го направил тоа на најефикасен начин.
Сакаш да ми приговараш? . , и Тие веќе ги користат техниките за кои сакам да зборувам денес.
Наведете барем еден развивач на задниот дел кој никогаш не користел OFFSET и LIMIT за извршување на странични барања. Во MVP (Minimum Viable Product) и во проекти каде што се користат мали количини на податоци, овој пристап е доста применлив. Тоа „само функционира“, така да се каже.
Но, ако треба да креирате сигурни и ефикасни системи од нула, треба однапред да се грижите за ефикасноста на пребарувањето на базите на податоци што се користат во таквите системи.
Денес ќе зборуваме за проблемите со најчесто користените (многу лошо) имплементации на страницирани пребарувачи и како да постигнете високи перформанси при извршување на такви барања.
Што не е во ред со OFFSET и LIMIT?
Како што веќе рековме, OFFSET и LIMIT Тие работат добро во проекти кои не треба да работат со големи количини на податоци.
Проблемот се јавува кога базата на податоци расте до таква големина што повеќе не се вклопува во меморијата на серверот. Меѓутоа, кога работите со оваа база на податоци, треба да користите странични барања.
За овој проблем да се манифестира, мора да има ситуација во која DBMS прибегнува кон неефикасна операција за скенирање на целосна табела на секое странично барање (додека може да се појават операции за вметнување и бришење, а не ни требаат застарени податоци!).
Што е „скенирање на целосна маса“ (или „секвенцијално скенирање на табели“, Секвенцијално скенирање)? Ова е операција за време на која DBMS последователно го чита секој ред од табелата, односно податоците содржани во неа и ги проверува дали се усогласени со дадениот услов. Познато е дека овој тип на скенирање на табелата е најбавно. Факт е дека кога се извршува, се вршат многу влезно/излезни операции кои го вклучуваат потсистемот на дискот на серверот. Ситуацијата се влошува поради латентноста поврзана со работата со податоци зачувани на дискови и фактот дека преносот на податоци од диск во меморија е операција која бара ресурси.
На пример, имате записи од 100000000 корисници и извршувате барање со конструкцијата OFFSET 50000000. Ова значи дека DBMS ќе мора да ги вчита сите овие записи (а не ни требаат!), да ги стави во меморија и после тоа да земе, да речеме, 20 резултати пријавени во LIMIT.
Да речеме дека може да изгледа вака: „изберете редови од 50000 до 50020 од 100000“. Односно, системот прво ќе треба да вчита 50000 редови за да го заврши барањето. Гледате ли колку непотребна работа ќе треба да направи?
Ако не ми верувате, погледнете го примерот што го создадов користејќи ги функциите .
Пример на 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 ms за да се заврши, но извршувањето на второ - 1 ms).
И ако има повеќе податоци, тогаш сè ќе изгледа уште полошо (за да се уверите во ова, погледнете го мојот со 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