Избегавајте коришћење ОФФСЕТ и ЛИМИТ у упитима са страницама

Прошли су дани када нисте морали да бринете о оптимизацији перформанси базе података. Време не стоји мирно. Сваки нови технолошки предузетник жели да креира следећи Фацебоок, покушавајући да прикупи све податке до којих може доћи. Предузећима су потребни ови подаци да би боље обучили моделе који им помажу да зараде новац. У таквим условима, програмери треба да креирају АПИ-је који им омогућавају да брзо и поуздано раде са огромним количинама информација.

Ако сте дизајнирали позадину апликације или базе података већ дуже време, вероватно сте написали код за покретање упита са страницама. На пример, овако:

SELECT * FROM table_name LIMIT 10 OFFSET 40

Начин је; решење је?

Али ако сте овако урадили своју пагинацију, жао ми је што морам да кажем да то нисте урадили на најефикаснији начин.

Хоћеш да ми приговориш? Можеш ли не потрошити време. Слацк, Схопифи и Микмак Они већ користе технике о којима желим да причам данас.

Наведите барем једног бацкенд програмера који никада није користио OFFSET и LIMIT за обављање пагинираних упита. У МВП (Минимум Виабле Продуцт) и у пројектима где се користе мале количине података, овај приступ је прилично применљив. То "само ради", да тако кажем.

Али ако треба да креирате поуздане и ефикасне системе од нуле, требало би унапред да водите рачуна о ефикасности испитивања база података које се користе у таквим системима.

Данас ћемо разговарати о проблемима са често коришћеним (превише лошим) имплементацијама пагинираних механизама упита и како постићи високе перформансе приликом извршавања таквих упита.

Шта није у реду са ОФФСЕТ-ом и ЛИМИТ-ом?

Као што је већ речено, OFFSET и LIMIT Они добро раде у пројектима који не морају да раде са великим количинама података.

Проблем настаје када база података нарасте до такве величине да више не стане у меморију сервера. Међутим, када радите са овом базом података, морате да користите пагиниране упите.

Да би се овај проблем манифестовао, мора постојати ситуација у којој ДБМС прибегава неефикасној операцији скенирања целе табеле на сваком пагинираном упиту (док се операције уметања и брисања могу појавити, а нама нису потребни застарели подаци!).

Шта је „скенирање целе табеле“ (или „секвенцијално скенирање табеле“, секвенцијално скенирање)? Ово је операција током које ДБМС секвенцијално чита сваки ред табеле, односно податке садржане у њој, и проверава да ли су у складу са датим условом. Познато је да је ова врста скенирања табеле најспорија. Чињеница је да када се изврши, извршавају се многе улазно/излазне операције које укључују дисковни подсистем сервера. Ситуацију погоршава кашњење повезано са радом са подацима ускладиштеним на дисковима, као и чињеница да је пренос података са диска на меморију операција која захтева много ресурса.

На пример, имате евиденцију о 100000000 корисника и покрећете упит са конструкцијом OFFSET 50000000. То значи да ће ДБМС морати да учита све ове записе (а нама чак ни не требају!), да их стави у меморију и након тога узме, рецимо, 20 резултата пријављених у LIMIT.

Рецимо да би то могло изгледати овако: "одаберите редове од 50000 до 50020 од 100000". То јест, систем ће прво морати да учита 50000 редова да би завршио упит. Видите ли колико ће она морати да обави непотребни посао?

Ако ми не верујете, погледајте пример који сам направио користећи функције дб-фиддле.цом. 

Пример на дб-фиддле.цом

Тамо, лево, у пољу 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 Вреди користити структуру изграђену према следећој шеми:

SELECT * FROM table_name WHERE id > 10 LIMIT 20

Ово је извршење упита са пагинацијом заснованом на курсору.

Уместо да тренутне чувате локално OFFSET и LIMIT и преносите их са сваким захтевом, потребно је да сачувате последњи примљени примарни кључ (обично је то ID) и LIMIT, као резултат ће се добити упити слични горе наведеним.

Зашто? Поента је у томе да експлицитним навођењем идентификатора последњег прочитаног реда кажете свом ДБМС-у где треба да почне да тражи потребне податке. Штавише, захваљујући употреби кључа, систем неће морати да буде ометан линијама изван наведеног опсега.

Хајде да погледамо следеће поређење перформанси различитих упита. Ево неефикасног упита.

Споро захтев

А ево и оптимизоване верзије овог захтева.

Брзи захтев

Оба упита враћају потпуно исту количину података. Али за прву је потребно 12,80 секунди, а за другу 0,01 секунду. Да ли осећате разлику?

Могући проблеми

Да би предложени метод упита функционисао ефикасно, табела мора да има колону (или колоне) која садржи јединствене, секвенцијалне индексе, као што је целобројни идентификатор. У неким специфичним случајевима, ово може одредити успех коришћења таквих упита за повећање брзине рада са базом података.

Наравно, када конструишете упите, морате узети у обзир специфичну архитектуру табела и изабрати оне механизме који ће најбоље функционисати на постојећим табелама. На пример, ако треба да радите на упитима са великим количинама повезаних података, можда ће вам бити занимљиво ово чланак.

Ако смо суочени са проблемом недостатка примарног кључа, на пример, ако имамо табелу са релацијом више-према-много, онда традиционални приступ коришћења OFFSET и LIMIT, гарантовано нам одговара. Али његова употреба може довести до потенцијално спорих упита. У случајевима као што је овај, препоручио бих коришћење примарног кључа са аутоматским повећањем, чак и ако вам је потребан само за руковање упитима са страницама.

Ако сте заинтересовани за ову тему - овде, овде и овде - неколико корисних материјала.

Резултати

Главни закључак који можемо извући је да је, без обзира о којој величини база података реч, увек потребно анализирати брзину извршавања упита. Данас је скалабилност решења изузетно важна, а ако је све исправно пројектовано од самог почетка рада на одређеном систему, то у будућности може спасити програмера од многих проблема.

Како анализирате и оптимизујете упите у бази података?

Извор: ввв.хабр.цом