Evitați utilizarea OFFSET și LIMIT în interogările paginate

Au trecut vremurile în care nu trebuia să-ți faci griji cu privire la optimizarea performanței bazei de date. Timpul nu sta pe loc. Fiecare nou antreprenor în tehnologie vrea să creeze următorul Facebook, încercând în același timp să colecteze toate datele pe care le pot pune mâna. Companiile au nevoie de aceste date pentru a pregăti mai bine modele care să le ajute să câștige bani. În astfel de condiții, programatorii trebuie să creeze API-uri care să le permită să lucreze rapid și fiabil cu cantități uriașe de informații.

Dacă ați proiectat aplicații sau baze de date pentru o perioadă de timp, probabil că ați scris cod pentru a rula interogări paginate. De exemplu, așa:

SELECT * FROM table_name LIMIT 10 OFFSET 40

Cum este?

Dar dacă așa ți-ai făcut paginarea, îmi pare rău să spun că nu ai făcut-o în cel mai eficient mod.

Vrei să mă opui? Putea nu petrece timp. Moale, Shopify и MixMax Ei folosesc deja tehnicile despre care vreau să vorbesc astăzi.

Numiți cel puțin un dezvoltator backend care nu a folosit niciodată OFFSET и LIMIT pentru a efectua interogări paginate. În MVP (Minimum Viable Product) și în proiectele în care sunt utilizate cantități mici de date, această abordare este destul de aplicabilă. „Doar funcționează”, ca să spunem așa.

Dar dacă trebuie să creați sisteme fiabile și eficiente de la zero, ar trebui să aveți grijă în prealabil de eficiența interogării bazelor de date utilizate în astfel de sisteme.

Astăzi vom vorbi despre problemele cu implementările utilizate în mod obișnuit (păcat de rău) ale motoarelor de interogări paginate și despre cum să obținem performanțe ridicate atunci când executați astfel de interogări.

Ce este în neregulă cu OFFSET și LIMIT?

După cum sa spus deja, OFFSET и LIMIT Ei performează bine în proiecte care nu trebuie să lucreze cu cantități mari de date.

Problema apare atunci când baza de date crește la o astfel de dimensiune încât nu mai încape în memoria serverului. Cu toate acestea, atunci când lucrați cu această bază de date, trebuie să utilizați interogări paginate.

Pentru ca această problemă să se manifeste, trebuie să existe o situație în care SGBD să recurgă la o operațiune ineficientă de Scanare a tabelului complet la fiecare interogare paginată (în timp ce pot apărea operațiuni de inserare și ștergere și nu avem nevoie de date învechite!).

Ce este o „scanare completă a tabelului” (sau „scanare secvențială a tabelului”, Scanare secvențială)? Aceasta este o operațiune în timpul căreia SGBD citește secvențial fiecare rând al tabelului, adică datele conținute în acesta, și le verifică dacă respectă o anumită condiție. Acest tip de scanare a tabelului este cunoscut a fi cel mai lentă. Cert este că atunci când este executat, sunt efectuate multe operațiuni de intrare/ieșire care implică subsistemul de disc al serverului. Situația este agravată de latența asociată cu lucrul cu datele stocate pe discuri și de faptul că transferul de date de pe disc în memorie este o operațiune care necesită mult resurse.

De exemplu, aveți înregistrări de 100000000 de utilizatori și rulați o interogare cu constructul OFFSET 50000000. Aceasta înseamnă că SGBD va trebui să încarce toate aceste înregistrări (și nici nu avem nevoie de ele!), să le pună în memorie și, după aceea, să ia, să zicem, 20 de rezultate raportate în LIMIT.

Să presupunem că ar putea arăta astfel: „select rows from 50000 to 50020 from 100000”. Adică, sistemul va trebui mai întâi să încarce 50000 de rânduri pentru a finaliza interogarea. Vezi câtă muncă inutilă va trebui să facă?

Dacă nu mă credeți, aruncați o privire la exemplul pe care l-am creat folosind funcțiile db-fiddle.com. 

Exemplu la db-fiddle.com

Acolo, în stânga, în câmp Schema SQL, există cod care inserează 100000 de rânduri în baza de date, iar în dreapta, în câmp Query SQL, sunt afișate două interogări. Primul, lent, arată astfel:

SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;

Iar a doua, care este o soluție eficientă la aceeași problemă, este astfel:

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

Pentru a îndeplini aceste solicitări, trebuie doar să faceți clic pe butonul Run În partea de sus a paginii. Făcând acest lucru, comparăm informații despre timpul de execuție a interogării. Se pare că executarea unei interogări ineficiente durează de cel puțin 30 de ori mai mult decât executarea celei de-a doua (acest timp variază de la o rulare la alta; de exemplu, sistemul poate raporta că prima interogare a durat 37 ms, dar execuția interogării). secundă - 1 ms).

Și dacă există mai multe date, atunci totul va arăta și mai rău (pentru a fi convins de asta, aruncați o privire la mine exemplu cu 10 milioane de rânduri).

Ceea ce tocmai am discutat ar trebui să vă ofere o perspectivă asupra modului în care interogările bazei de date sunt de fapt procesate.

Vă rugăm să rețineți că, cu cât valoarea este mai mare OFFSET — cu atât solicitarea va dura mai mult pentru a se finaliza.

Ce ar trebui să folosesc în loc de combinația de OFFSET și LIMIT?

În loc de o combinație OFFSET и LIMIT Merită să folosiți o structură construită conform următoarei scheme:

SELECT * FROM table_name WHERE id > 10 LIMIT 20

Aceasta este execuția interogării cu paginare bazată pe cursor.

În loc să le stocați pe cele actuale la nivel local OFFSET и LIMIT și transmiteți-le cu fiecare solicitare, trebuie să stocați ultima cheie primară primită (de obicei, aceasta este ID) Și LIMIT, ca urmare, se vor obține interogări similare celor de mai sus.

De ce? Ideea este că, specificând în mod explicit identificatorul ultimului rând citit, spuneți DBMS-ului dvs. unde trebuie să înceapă să caute datele necesare. Mai mult, căutarea, datorită utilizării cheii, va fi efectuată eficient, sistemul nu va trebui să fie distras de linii în afara intervalului specificat.

Să aruncăm o privire la următoarea comparație a performanței diferitelor interogări. Iată o interogare ineficientă.

Cerere lentă

Și iată o versiune optimizată a acestei solicitări.

Cerere rapidă

Ambele interogări returnează exact aceeași cantitate de date. Dar primul durează 12,80 secunde pentru a se finaliza, iar al doilea durează 0,01 secunde. Simți diferența?

Posibile probleme

Pentru ca metoda de interogare propusă să funcționeze eficient, tabelul trebuie să aibă o coloană (sau coloane) care să conțină indecși unici, secvențiali, cum ar fi un identificator întreg. În unele cazuri specifice, acest lucru poate determina succesul utilizării unor astfel de interogări pentru a crește viteza de lucru cu baza de date.

Desigur, atunci când construiți interogări, trebuie să țineți cont de arhitectura specifică a tabelelor și să alegeți acele mecanisme care vor funcționa cel mai bine pe tabelele existente. De exemplu, dacă trebuie să lucrați în interogări cu volume mari de date asociate, s-ar putea să vi se pară interesant acest articol.

Dacă ne confruntăm cu problema lipsei unei chei primare, de exemplu, dacă avem un tabel cu o relație multi-la-mulți, atunci abordarea tradițională a utilizării OFFSET и LIMIT, este garantat că ni se potrivește. Dar utilizarea sa poate duce la interogări potențial lente. În astfel de cazuri, aș recomanda utilizarea unei chei primare cu incrementare automată, chiar dacă este necesară doar pentru a gestiona interogări paginate.

Daca esti interesat de acest subiect - aici, aici и aici - mai multe materiale utile.

Rezultatele

Principala concluzie pe care o putem trage este că, indiferent de dimensiunea bazelor de date despre care vorbim, este întotdeauna necesar să se analizeze viteza de execuție a interogărilor. În zilele noastre, scalabilitatea soluțiilor este extrem de importantă, iar dacă totul este proiectat corect încă de la începutul lucrului pe un anumit sistem, acest lucru, în viitor, poate salva dezvoltatorul de multe probleme.

Cum analizați și optimizați interogările bazei de date?

Sursa: www.habr.com