Zdravo svima! Ja sam backend programer koji piše mikroservise u Java + Spring. Radim u jednom od internih timova za razvoj proizvoda u Tinkoff-u.
U našem timu se često postavlja pitanje optimizacije upita u DBMS-u. Uvek želite da budete malo brži, ali ne možete uvek da se snađete sa promišljeno konstruisanim indeksima – morate potražiti neka rešenja. Tokom jednog od ovih lutanja po webu u potrazi za razumnim optimizacijama pri radu sa bazama podataka, pronašao sam , autor knjige SQL Performance Explained. Ovo je ona rijetka vrsta bloga na kojoj možete čitati sve članke za redom.
Želio bih za vas prevesti kratak Marcusov članak. Može se donekle nazvati manifestom koji nastoji skrenuti pažnju na stari, ali još uvijek relevantan problem izvođenja ofset operacije prema SQL standardu.
Na nekim mjestima ću autora dopuniti objašnjenjima i komentarima. Nazvat ću sva takva mjesta kao "cca." radi veće jasnoće
Mali uvod
Mislim da mnogi ljudi znaju koliko je problematičan i spor rad sa odabirom stranica putem ofseta. Jeste li znali da se može prilično lako zamijeniti efikasnijim dizajnom?
Dakle, ključna riječ offset govori bazi podataka da preskoči prvih n zapisa u zahtjevu. Međutim, baza podataka i dalje treba da pročita ovih prvih n zapisa sa diska, u datom redosledu (napomena: primeni sortiranje ako je navedeno), i tek tada će biti moguće vratiti zapise od n+1 nadalje. Najzanimljivije je da problem nije u konkretnoj implementaciji u DBMS, već u originalnoj definiciji prema standardu:
…redovi se prvo sortiraju prema a zatim ograničen ispuštanjem broja redova navedenih u s početka...
-SQL:2016, 2. dio, 4.15.3 Izvedene tabele (napomena: trenutno najčešće korišteni standard)
Ključna stvar je da ofset uzima jedan parametar - broj zapisa koje treba preskočiti, i to je to. Slijedeći ovu definiciju, DBMS može samo dohvatiti sve zapise, a zatim odbaciti one nepotrebne. Očigledno, ova definicija ofseta nas tjera na dodatni posao. I nije ni bitno da li je SQL ili NoSQL.
Samo još malo bola
Problemi sa ofsetom ne završavaju tu, a evo i zašto. Ako, između čitanja dvije stranice podataka sa diska, druga operacija ubaci novi zapis, šta će se dogoditi u ovom slučaju?
Kada se offset koristi za preskakanje zapisa sa prethodnih stranica, u situaciji dodavanja novog zapisa između čitanja različitih stranica, najvjerovatnije ćete dobiti duplikate (napomena: ovo je moguće kada čitamo stranicu po stranicu koristeći redoslijed po konstrukciji, zatim u sredini našeg izlaza može dobiti novi unos).
Slika jasno prikazuje ovu situaciju. Baza čita prvih 10 zapisa, nakon čega se ubacuje novi zapis, koji sve pročitane zapise pomera za 1. Zatim baza preuzima novu stranicu od narednih 10 zapisa i počinje ne od 11. kako bi trebalo, već od 10., dupliranje ovog zapisa. Postoje i druge anomalije povezane s upotrebom ovog izraza, ali ovo je najčešće.
Kao što smo već saznali, to nisu problemi konkretnog DBMS-a ili njihove implementacije. Problem je u definiranju paginacije prema SQL standardu. Mi govorimo DBMS-u koju stranicu da preuzme ili koliko zapisa da preskoči. Baza podataka jednostavno nije u stanju optimizirati takav zahtjev, jer ima premalo informacija za to.
Također je vrijedno pojasniti da ovo nije problem sa specifičnom ključnom riječi, već sa semantikom upita. Postoji još nekoliko sintaksa koje su identične po svojoj problematičnoj prirodi:
- Ključna riječ offset je kao što je ranije spomenuto.
- Konstrukcija od dvije ključne riječi limit [offset] (iako ograničenje samo po sebi nije tako loše).
- Filtriranje prema donjim granicama, na osnovu numeracije redova (na primjer, row_number(), rownum, itd.).
Svi ovi izrazi vam jednostavno govore koliko redova da preskočite, bez dodatnih informacija ili konteksta.
Kasnije u ovom članku, ključna riječ offset se koristi kao sažetak svih ovih opcija.
Život bez OFFSET-a
Sada zamislimo kakav bi naš svijet izgledao bez svih ovih problema. Ispostavilo se da život bez pomaka nije tako težak: uz odabir možete odabrati samo one redove koje još nismo vidjeli (napomena: to jest, one koji nisu bili na prethodnoj stranici), koristeći uvjet gdje.
U ovom slučaju polazimo od činjenice da se odabiri izvršavaju na uređenom skupu (stari dobri red by). Pošto imamo uređen skup, možemo koristiti prilično jednostavan filter da dobijemo samo podatke koji se nalaze iza posljednjeg zapisa prethodne stranice:
SELECT ...
FROM ...
WHERE ...
AND id < ?last_seen_id
ORDER BY id DESC
FETCH FIRST 10 ROWS ONLYTo je cijeli princip ovog pristupa. Naravno, stvari postaju zabavnije kada se sortirate po više kolona, ali ideja je i dalje ista. Važno je napomenuti da je ovaj dizajn primjenjiv na mnoge -odluke.
Ovaj pristup se naziva metodom traženja ili paginacijom skupa ključeva. Rješava problem plutajućeg rezultata (napomena: ranije je opisana situacija sa pisanjem između čitanja stranica) i, naravno, ono što svi volimo, radi brže i stabilnije od klasičnog ofseta. Stabilnost leži u činjenici da se vrijeme obrade zahtjeva ne povećava proporcionalno broju tražene tablice (napomena: ako želite saznati više o radu različitih pristupa paginaciji, možete . Tamo također možete pronaći uporedna mjerila za različite metode).
Jedan od slajdova da paginacija po ključevima, naravno, nije svemoguća - ona ima svoja ograničenja. Najznačajnije je to što ona nema mogućnost čitanja nasumičnih stranica (napomena: nedosljedno). Međutim, u eri beskonačnog skrolovanja (napomena: na prednjem kraju), to nije toliki problem. Određivanje broja stranice za klik je ionako loša odluka u dizajnu korisničkog sučelja (napomena: mišljenje autora članka).
Šta je sa alatima?
Paginacija na ključevima često nije prikladna zbog nedostatka instrumentalne podrške za ovu metodu. Većina razvojnih alata, uključujući različite okvire, ne dozvoljavaju vam da odaberete kako će se tačno izvršiti paginacija.
Situaciju pogoršava činjenica da opisana metoda zahtijeva podršku s kraja na kraj u korištenim tehnologijama - od DBMS-a do izvršavanja AJAX zahtjeva u pretraživaču uz beskonačno pomicanje. Umjesto da navedete samo broj stranice, sada morate odrediti skup ključeva za sve stranice odjednom.
Međutim, broj okvira koji podržavaju paginaciju na ključevima postepeno raste. Evo šta imamo u ovom trenutku:
- za Javu;
- za Ruby;
- и za Django;
- za Python;
- — API kriterijuma za implementaciju JPA;
- za Perl;
- , maper za Node.js .
(Napomena: neki linkovi su uklonjeni zbog činjenice da u vrijeme prijevoda neke biblioteke nisu bile ažurirane od 2017-2018. Ako ste zainteresovani, možete pogledati izvorni izvor.)
Upravo u ovom trenutku vaša pomoć je potrebna. Ako razvijate ili podržavate okvir koji koristi paginaciju, onda vas molim, pozivam, preklinjem vas da pružite izvornu podršku za paginaciju na ključevima. Ako imate pitanja ili vam je potrebna pomoć, rado ću vam pomoći (, , ) (napomena: iz mog iskustva sa Marcusom, mogu reći da je on zaista oduševljen širenjem ove teme).
Ako koristite gotova rješenja za koja mislite da su vrijedna podrške za paginaciju po ključevima, kreirajte zahtjev ili čak ponudite gotovo rješenje, ako je moguće. Također možete povezati ovaj članak.
zaključak
Razlog zašto tako jednostavan i koristan pristup kao što je paginacija po ključevima nije široko rasprostranjen nije to što ga je tehnički teško implementirati ili zahtijeva veliki napor. Glavni razlog je to što su mnogi navikli da vide i rade sa ofsetom - ovaj pristup diktira sam standard.
Kao rezultat toga, malo ljudi razmišlja o promjeni pristupa paginaciji i zbog toga se instrumentalna podrška okvira i biblioteka slabo razvija. Stoga, ako su vam ideja i cilj paginacije bez pomaka bliski, pomozite u širenju!
izvor:
Autor: Markus Winand
izvor: www.habr.com