Salutare tuturor! Sunt un dezvoltator backend care scriu microservicii în Java + Spring. Lucrez într-una dintre echipele interne de dezvoltare de produse de la Tinkoff.
În echipa noastră, se pune adesea problema optimizării interogărilor într-un SGBD. Întotdeauna vrei să fii puțin mai rapid, dar nu te poți descurca întotdeauna cu indecși construiți cu atenție - trebuie să cauți câteva soluții. În timpul uneia dintre aceste rătăciri pe web în căutarea unor optimizări rezonabile atunci când lucrez cu baze de date, am găsit , autorul cărții SQL Performance Explained. Acesta este acel tip rar de blog în care poți citi toate articolele la rând.
Aș dori să vă traduc un scurt articol de Marcus. Poate fi numit într-o oarecare măsură un manifest care urmărește să atragă atenția asupra veche, dar încă relevantă problemă a performanței operației de offset conform standardului SQL.
Pe alocuri voi completa autorul cu explicații și comentarii. Mă voi referi la toate astfel de locuri ca „aprox.” pentru mai multă claritate
Mică introducere
Cred că mulți oameni știu cât de problematic și lent este lucrul cu selectările de pagini prin offset. Știați că poate fi înlocuit destul de ușor cu un design mai eficient?
Deci, cuvântul cheie offset îi spune bazei de date să ignore primele n înregistrări din cerere. Cu toate acestea, baza de date mai trebuie să citească aceste prime n înregistrări de pe disc, în ordinea dată (notă: aplicați sortarea dacă este specificată) și numai atunci va fi posibilă returnarea înregistrărilor de la n+1 încolo. Cel mai interesant lucru este că problema nu este în implementarea specifică în SGBD, ci în definiția originală conform standardului:
…rândurile sunt mai întâi sortate în funcție de și apoi limitat prin eliminarea numărului de rânduri specificat în de la inceput...
-SQL:2016, Partea 2, 4.15.3 Tabele derivate (notă: în prezent, cel mai folosit standard)
Punctul cheie aici este că offset-ul ia un singur parametru - numărul de înregistrări de sărit și asta este tot. Urmând această definiție, SGBD poate prelua doar toate înregistrările și apoi le poate arunca pe cele inutile. Evident, această definiție a offset-ului ne obligă să facem o muncă suplimentară. Și nici măcar nu contează dacă este SQL sau NoSQL.
Doar puțin mai multă durere
Problemele cu offset-ul nu se termină aici și iată de ce. Dacă, între citirea a două pagini de date de pe disc, o altă operație introduce o nouă înregistrare, ce se va întâmpla în acest caz?
Când offset este folosit pentru a sări peste înregistrări din paginile anterioare, în situația adăugării unei noi înregistrări între citirile diferitelor pagini, cel mai probabil veți obține duplicate (notă: acest lucru este posibil când citim pagină cu pagină folosind ordinea după construct, apoi în mijlocul ieșirii noastre poate primi o nouă intrare).
Figura descrie clar această situație. Baza citește primele 10 înregistrări, după care se inserează o înregistrare nouă, care compensează toate înregistrările citite cu 1. Apoi baza ia o nouă pagină din următoarele 10 înregistrări și începe nu din a 11-a, așa cum ar trebui, ci de la 10, duplicarea acestei înregistrări. Există și alte anomalii asociate cu utilizarea acestei expresii, dar aceasta este cea mai comună.
După cum am aflat deja, acestea nu sunt probleme ale unui anumit SGBD sau ale implementărilor acestora. Problema este în definirea paginației conform standardului SQL. Spunem DBMS ce pagină să preia sau câte înregistrări să omite. Baza de date pur și simplu nu este capabilă să optimizeze o astfel de solicitare, deoarece există prea puține informații pentru aceasta.
De asemenea, merită să clarificăm că aceasta nu este o problemă cu un anumit cuvânt cheie, ci mai degrabă cu semantica interogării. Există mai multe sintaxe care sunt identice în natura lor problematică:
- Cuvântul cheie offset este așa cum am menționat mai devreme.
- O construcție a două cuvinte cheie limit [offset] (deși limita în sine nu este atât de rea).
- Filtrarea după limite inferioare, pe baza numerotării rândurilor (de exemplu, row_number(), rownum etc.).
Toate aceste expresii vă spun pur și simplu câte rânduri să săriți, fără informații suplimentare sau context.
Mai târziu în acest articol, cuvântul cheie offset este folosit ca un rezumat al tuturor acestor opțiuni.
Viața fără OFFSET
Acum să ne imaginăm cum ar fi lumea noastră fără toate aceste probleme. Se pare că viața fără compensare nu este atât de dificilă: cu o selectie, puteți selecta doar acele rânduri pe care nu le-am văzut încă (notă: adică cele care nu erau pe pagina anterioară), folosind o condiție în care.
În acest caz, pornim de la faptul că selectările sunt executate pe un set ordonat (good old order by). Deoarece avem un set ordonat, putem folosi un filtru destul de simplu pentru a obține doar datele care se află în spatele ultimei înregistrări din pagina anterioară:
SELECT ...
FROM ...
WHERE ...
AND id < ?last_seen_id
ORDER BY id DESC
FETCH FIRST 10 ROWS ONLYAcesta este întregul principiu al acestei abordări. Desigur, lucrurile devin mai distractive atunci când sortați după mai multe coloane, dar ideea rămâne aceeași. Este important de reținut că acest design este aplicabil pentru mulți -decizii.
Această abordare se numește metoda de căutare sau paginarea seturilor de taste. Rezolvă problema rezultatului flotant (notă: situația cu scrierea între citirile paginii descrisă mai devreme) și, desigur, ceea ce ne place cu toții, funcționează mai rapid și mai stabil decât offset-ul clasic. Stabilitatea constă în faptul că timpul de procesare a cererii nu crește proporțional cu numărul tabelului solicitat (notă: dacă doriți să aflați mai multe despre munca diferitelor abordări ale paginarii, puteți . Puteți găsi, de asemenea, repere comparative pentru diferite metode acolo).
Unul dintre diapozitive acea paginare prin taste, desigur, nu este atotputernică – are limitările ei. Cel mai semnificativ este că nu are capacitatea de a citi pagini aleatorii (notă: inconsecvent). Cu toate acestea, în era defilării fără sfârșit (notă: pe partea frontală), aceasta nu este o astfel de problemă. Specificarea unui număr de pagină pentru clic este oricum o decizie proastă în designul UI (notă: opinia autorului articolului).
Dar instrumentele?
Paginarea pe taste nu este adesea potrivită din cauza lipsei suportului instrumental pentru această metodă. Majoritatea instrumentelor de dezvoltare, inclusiv diverse cadre, nu vă permit să alegeți exact cum va fi efectuată paginarea.
Situația este agravată de faptul că metoda descrisă necesită suport end-to-end în tehnologiile utilizate - de la DBMS până la executarea unei solicitări AJAX în browser cu derulare fără sfârșit. În loc să specificați doar numărul paginii, acum trebuie să specificați un set de chei pentru toate paginile simultan.
Cu toate acestea, numărul de cadre care acceptă paginarea pe taste crește treptat. Iată ce avem în acest moment:
- pentru Java;
- pentru Ruby;
- и pentru Django;
- pentru Python;
- — criterii API pentru implementările JPA;
- pentru Perl;
- , mapper pentru Node.js .
(Notă: unele link-uri au fost eliminate din cauza faptului că la momentul traducerii unele biblioteci nu fuseseră actualizate din 2017-2018. Dacă sunteți interesat, vă puteți uita la sursa originală.)
În acest moment este nevoie de ajutorul tău. Dacă dezvoltați sau susțineți un cadru care folosește paginarea, atunci vă rog, vă îndemn, vă implor să oferiți suport nativ pentru paginarea pe taste. Dacă aveți întrebări sau aveți nevoie de ajutor, vă voi ajuta cu plăcere (, , ) (notă: din experiența mea cu Marcus, pot spune că este foarte entuziasmat de răspândirea acestui subiect).
Dacă utilizați soluții gata făcute pe care le considerați demne de a avea suport pentru paginarea prin taste, creați o cerere sau chiar oferiți o soluție gata făcută, dacă este posibil. Puteți, de asemenea, să faceți un link către acest articol.
Concluzie
Motivul pentru care o abordare atât de simplă și utilă precum paginarea prin taste nu este larg răspândită nu este că este dificil de implementat din punct de vedere tehnic sau necesită vreun efort mare. Motivul principal este că mulți sunt obișnuiți să vadă și să lucreze cu offset - această abordare este dictată de standardul însuși.
Drept urmare, puțini oameni se gândesc la schimbarea abordării paginației și, din această cauză, suportul instrumental din cadre și biblioteci se dezvoltă slab. Prin urmare, dacă ideea și scopul de paginare fără offset vă sunt aproape, ajutați-l la răspândire!
Sursa:
Autor: Markus Winand
Sursa: www.habr.com