Izogibajte se uporabi OFFSET in LIMIT v paginiranih poizvedbah

Minili so časi, ko vam ni bilo treba skrbeti za optimizacijo zmogljivosti baze podatkov. Čas ne miruje. Vsak nov tehnološki podjetnik želi ustvariti naslednji Facebook, hkrati pa poskuša zbrati vse podatke, ki jih lahko dobijo. Podjetja te podatke potrebujejo za boljše usposabljanje modelov, ki jim pomagajo služiti denar. V takih razmerah morajo programerji ustvariti API-je, ki jim omogočajo hitro in zanesljivo delo z ogromnimi količinami informacij.

Če že dalj časa načrtujete ozadja aplikacije ali zbirke podatkov, ste verjetno že napisali kodo za izvajanje poizvedb s strani. Na primer takole:

SELECT * FROM table_name LIMIT 10 OFFSET 40

Kakor je?

Toda če ste tako naredili paginacijo, mi je žal, da tega niste naredili na najbolj učinkovit način.

Ali mi želite ugovarjati? Lahko ne porabiti čas. Slack, Shopify и mixmax Že uporabljajo tehnike, o katerih želim govoriti danes.

Navedite vsaj enega zalednega razvijalca, ki še nikoli ni uporabljal OFFSET и LIMIT za izvajanje paginiranih poizvedb. Pri MVP (Minimum Viable Product) in pri projektih, kjer se uporabljajo majhne količine podatkov, je ta pristop zelo uporaben. Tako rekoč »preprosto deluje«.

Če pa morate ustvariti zanesljive in učinkovite sisteme iz nič, morate vnaprej poskrbeti za učinkovitost poizvedovanja po bazah podatkov, ki se uporabljajo v takih sistemih.

Danes bomo govorili o težavah s pogosto uporabljenimi (škoda) implementacijami paginiranih poizvedovalnih mehanizmov in o tem, kako doseči visoko zmogljivost pri izvajanju takih poizvedb.

Kaj je narobe z OFFSET in LIMIT?

Kot že rečeno, OFFSET и LIMIT Dobro se obnesejo pri projektih, pri katerih ni treba delati z velikimi količinami podatkov.

Težava nastane, ko baza podatkov naraste do te mere, da ne gre več v pomnilnik strežnika. Vendar pa morate pri delu s to zbirko podatkov uporabiti ostranjene poizvedbe.

Da bi se ta težava pokazala, mora obstajati situacija, v kateri se DBMS zateče k neučinkoviti operaciji iskanja celotne tabele pri vsaki poizvedbi s strani (medtem ko lahko pride do operacij vstavljanja in brisanja in ne potrebujemo zastarelih podatkov!).

Kaj je »pregled celotne tabele« (ali »zaporedno pregledovanje tabele«, Sequential Scan)? To je operacija, med katero DBMS zaporedno prebere vsako vrstico tabele, to je podatke, ki jih vsebuje, in jih preveri glede skladnosti z danim pogojem. Ta vrsta skeniranja tabele je znana kot najpočasnejša. Dejstvo je, da se ob njegovem izvajanju izvede veliko vhodno/izhodnih operacij, ki vključujejo diskovni podsistem strežnika. Stanje poslabšata zakasnitev, povezana z delom s podatki, shranjenimi na diskih, in dejstvo, da je prenos podatkov z diska v pomnilnik operacija, ki zahteva veliko virov.

Na primer, imate zapise o 100000000 uporabnikih in zaženete poizvedbo s konstrukcijo OFFSET 50000000. To pomeni, da bo DBMS moral naložiti vse te zapise (in jih niti ne potrebujemo!), jih shraniti v pomnilnik in nato prevzeti, recimo, 20 rezultatov, ki jih poroča v LIMIT.

Recimo, da je videti takole: "izberi vrstice od 50000 do 50020 od 100000". To pomeni, da bo moral sistem najprej naložiti 50000 vrstic za dokončanje poizvedbe. Ali vidite, koliko nepotrebnega dela bo morala opraviti?

Če mi ne verjamete, si oglejte primer, ki sem ga ustvaril z uporabo funkcij db-fiddle.com. 

Primer na db-fiddle.com

Tam, levo, na polju Schema SQL, je koda, ki v bazo podatkov vstavi 100000 vrstic, na desni pa v polje Query SQL, sta prikazani dve poizvedbi. Prvi, počasni, izgleda takole:

SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;

In drugi, ki je učinkovita rešitev za isti problem, je takšen:

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

Če želite izpolniti te zahteve, samo kliknite na gumb Run na vrhu strani. Ko to storimo, primerjamo podatke o času izvedbe poizvedbe. Izkazalo se je, da izvedba neučinkovite poizvedbe traja vsaj 30-krat dlje kot izvedba druge (ta čas se razlikuje od zagona do zagona; na primer, sistem lahko poroča, da je prva poizvedba trajala 37 ms, vendar je izvedba sekunda - 1 ms).

In če bo podatkov več, potem bo vse videti še slabše (da se o tem prepričate, poglejte moj Primer z 10 milijoni vrstic).

To, o čemer smo pravkar razpravljali, bi vam moralo dati nekaj vpogleda v to, kako se poizvedbe po bazi podatkov dejansko obdelujejo.

Upoštevajte, da višja kot je vrednost OFFSET — dlje bo trajalo dokončanje zahteve.

Kaj naj uporabim namesto kombinacije OFFSET in LIMIT?

Namesto kombinacije OFFSET и LIMIT Vredno je uporabiti strukturo, zgrajeno po naslednji shemi:

SELECT * FROM table_name WHERE id > 10 LIMIT 20

To je izvedba poizvedbe z ostranjevanjem na podlagi kazalca.

Namesto da bi trenutne shranili lokalno OFFSET и LIMIT in jih posredujete z vsako zahtevo, morate shraniti zadnji prejeti primarni ključ (običajno je to ID) In LIMIT, posledično bodo pridobljene poizvedbe, podobne zgornjim.

Zakaj? Bistvo je v tem, da z eksplicitno določitvijo identifikatorja zadnje prebrane vrstice svojemu DBMS poveste, kje naj začne iskati potrebne podatke. Poleg tega bo iskanje, zahvaljujoč uporabi ključa, potekalo učinkovito; sistemu ne bo treba motiti črt zunaj določenega obsega.

Oglejmo si naslednjo primerjavo zmogljivosti različnih poizvedb. Tukaj je neučinkovita poizvedba.

Počasna zahteva

In tukaj je optimizirana različica te zahteve.

Hitra zahteva

Obe poizvedbi vrneta popolnoma enako količino podatkov. Toda dokončanje prvega traja 12,80 sekunde, drugo pa 0,01 sekunde. Ali čutite razliko?

Možni problemi

Da bi predlagana metoda poizvedbe delovala učinkovito, mora imeti tabela stolpec (ali stolpce), ki vsebuje edinstvene, zaporedne indekse, kot je identifikator celega števila. V nekaterih posebnih primerih lahko to vpliva na uspešnost uporabe takih poizvedb za povečanje hitrosti dela z bazo podatkov.

Seveda morate pri sestavljanju poizvedb upoštevati specifično arhitekturo tabel in izbrati tiste mehanizme, ki bodo najbolje delovali na obstoječih tabelah. Na primer, če morate delati v poizvedbah z velikimi količinami povezanih podatkov, se vam bo morda zdelo zanimivo to Članek.

Če se soočamo s problemom manjkajočega primarnega ključa, na primer, če imamo tabelo z razmerjem mnogo proti mnogo, potem tradicionalni pristop uporabe OFFSET и LIMIT, nam garantirano ustreza. Toda njegova uporaba lahko povzroči potencialno počasne poizvedbe. V takšnih primerih priporočam uporabo samodejnega povečanja primarnega ključa, tudi če je potreben samo za obravnavanje paginiranih poizvedb.

Če vas ta tema zanima - glej, glej и glej - več uporabnih materialov.

Rezultati

Glavna ugotovitev, ki jo lahko potegnemo, je, da je ne glede na to, o kakšni velikosti podatkovnih zbirk govorimo, vedno treba analizirati hitrost izvajanja poizvedb. V današnjem času je razširljivost rešitev izjemno pomembna in če je vse pravilno zasnovano že od samega začetka dela na določenem sistemu, lahko to v prihodnosti reši razvijalca marsikatere težave.

Kako analizirate in optimizirate poizvedbe v bazi podatkov?

Vir: www.habr.com