Bok svima! Ja sam backend programer koji piše mikroservise u Java + Springu. Radim u jednom od internih timova za razvoj proizvoda u Tinkoffu.
U našem timu često se postavlja pitanje optimizacije upita u DBMS-u. Uvijek želite biti malo brži, ali ne možete uvijek proći s promišljeno konstruiranim indeksima - morate tražiti neka zaobilazna rješenja. Tijekom jednog od tih lutanja webom u potrazi za razumnim optimizacijama pri radu s bazama podataka, pronašao sam , autor knjige SQL Performance Explained. Ovo je ona rijetka vrsta bloga u kojoj možete čitati sve članke za redom.
Htio bih vam prevesti kratki Marcusov članak. Može se donekle nazvati manifestom koji želi skrenuti pozornost na stari, ali još uvijek aktualan problem izvedbe offset operacije prema SQL standardu.
Na nekim mjestima ću autora dopuniti objašnjenjima i komentarima. Sva takva mjesta nazivat ću "približno". za veću jasnoću
Mali uvod
Mislim da mnogi ljudi znaju koliko je problematičan i spor rad s odabirom stranica putem offseta. Jeste li znali da se može vrlo lako zamijeniti učinkovitijim dizajnom?
Dakle, ključna riječ offset govori bazi podataka da preskoči prvih n zapisa u zahtjevu. Međutim, baza podataka i dalje treba pročitati ovih prvih n zapisa s diska, zadanim redoslijedom (napomena: primijeni 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:
…retci su najprije razvrstani prema , a zatim ograničeni ispuštanjem broja redaka navedenih u od početka...
-SQL:2016, 2. dio, 4.15.3 Izvedene tablice (napomena: trenutno najčešće korišteni standard)
Ključna točka ovdje je da offset uzima jedan parametar - broj zapisa za preskakanje, i to je to. Slijedeći ovu definiciju, DBMS može samo dohvatiti sve zapise i zatim odbaciti nepotrebne. Očito nas ova definicija offseta tjera na dodatni posao. I nije čak ni važno je li SQL ili NoSQL.
Još samo malo boli
Problemi s offsetom tu ne završavaju, a evo i zašto. Ako, između čitanja dvije stranice podataka s diska, druga operacija umetne novi zapis, što će se dogoditi u ovom slučaju?
Kada se offset koristi za preskakanje zapisa s prethodnih stranica, u situaciji dodavanja novog zapisa između čitanja različitih stranica, najvjerojatnije ćete dobiti duplikate (napomena: ovo je moguće kada čitamo stranicu po stranicu korištenjem redoslijeda prema konstrukciji, tada u sredini našeg izlaza može dobiti novi unos).
Slika jasno prikazuje ovu situaciju. Baza čita prvih 10 zapisa, nakon čega se umeće novi zapis, koji sve pročitane zapise ofsetira za 1. Tada baza uzima novu stranicu od sljedećih 10 zapisa i ne počinje od 11., kako bi trebala, već od 10., umnožavanje ovog zapisa. Postoje i druge anomalije povezane s upotrebom ovog izraza, ali ova je najčešća.
Kao što smo već saznali, ovo nisu problemi konkretnog DBMS-a ili njihove implementacije. Problem je u definiranju paginacije prema SQL standardu. Mi kažemo DBMS-u koju stranicu da dohvati ili koliko zapisa da preskoči. Baza podataka jednostavno nije u mogućnosti optimizirati takav zahtjev, jer ima premalo informacija za to.
Također je vrijedno pojasniti da ovo nije problem s određenom ključnom riječi, već sa semantikom upita. Postoji još nekoliko sintaksi koje su identične po svojoj problematičnoj prirodi:
- Ključna riječ offset je kao što je ranije spomenuto.
- Konstrukcija dviju ključnih riječi limit [offset] (iako limit sam po sebi nije tako loš).
- Filtriranje prema donjim granicama, na temelju numeriranja redaka (na primjer, row_number(), rownum, itd.).
Svi ovi izrazi jednostavno vam govore koliko redaka trebate preskočiti, bez dodatnih informacija ili konteksta.
Kasnije u ovom članku, ključna riječ offset koristi se kao sažetak svih ovih opcija.
Život bez OFFSET-a
Sada zamislimo kakav bi naš svijet bio bez svih ovih problema. Ispada da život bez pomaka nije tako težak: pomoću odabira možete odabrati samo one retke koje još nismo vidjeli (napomena: to jest, one koji nisu bili na prethodnoj stranici), koristeći uvjet u gdje.
U ovom slučaju polazimo od činjenice da se odabiri izvršavaju na uređenom skupu (dobri stari order by). Budući da imamo uređen skup, možemo upotrijebiti prilično jednostavan filtar kako bismo dobili 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 cijelo načelo ovog pristupa. Naravno, stvari postaju zabavnije razvrstavanjem po više stupaca, ali ideja je i dalje ista. Važno je napomenuti da je ovaj dizajn primjenjiv na mnoge - odluke.
Ovaj pristup se naziva metoda traženja ili paginacija skupa ključeva. Rješava problem plutajućeg rezultata (napomena: ranije opisana situacija s pisanjem između čitanja stranice) 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 usporedne referentne vrijednosti za različite metode).
Jedan od slajdova to paginiranje po ključevima, naravno, nije svemoćno - ono ima svoja ograničenja. Najznačajnije je to što ona nema sposobnost čitanja nasumičnih stranica (napomena: nedosljedno). Međutim, u eri beskrajnog skrolanja (napomena: na prednjoj strani), to i nije toliki problem. Određivanje broja stranice za klikanje ionako je loša odluka u dizajnu korisničkog sučelja (napomena: mišljenje autora članka).
Što je s alatima?
Označavanje stranica na ključevima često nije prikladno zbog nedostatka instrumentalne podrške za ovu metodu. Većina razvojnih alata, uključujući razne okvire, ne dopuštaju vam da odaberete kako će se točno izvoditi paginacija.
Situaciju pogoršava činjenica da opisana metoda zahtijeva end-to-end podršku u korištenim tehnologijama - od DBMS-a do izvršavanja AJAX zahtjeva u pregledniku s beskonačnim pomicanjem. Umjesto da navedete samo broj stranice, sada morate navesti skup ključeva za sve stranice odjednom.
Međutim, broj okvira koji podržavaju paginaciju na ključevima postupno raste. Evo što trenutno imamo:
- za Javu;
- za Ruby;
- и za Django;
- za Python;
- — API kriterija za JPA implementacije;
- za Perl;
- , maper za Node.js .
(Napomena: neke poveznice su uklonjene zbog činjenice da u vrijeme prijevoda neke biblioteke nisu bile ažurirane od 2017-2018. Ako ste zainteresirani, možete pogledati izvorni izvor.)
Upravo je u ovom trenutku potrebna vaša pomoć. Ako razvijate ili podržavate okvir koji na bilo koji način koristi paginaciju, onda vas molim, potičem, preklinjem da osigurate izvornu podršku za paginaciju na ključevima. Ako imate pitanja ili trebate pomoć, rado ću pomoći (, , ) (napomena: iz mog iskustva s Marcusom, mogu reći da je on zaista entuzijastičan oko širenja ove teme).
Ako koristite gotova rješenja za koja mislite da zaslužuju podršku za paginaciju po ključevima, napravite zahtjev ili čak ponudite gotovo rješenje, ako je moguće. Možete se povezati i na ovaj članak.
Zaključak
Razlog zašto tako jednostavan i koristan pristup kao što je paginacija po ključevima nije široko rasprostranjen nije taj što ga je tehnički teško implementirati ili zahtijeva neki veliki napor. Glavni razlog je taj što su mnogi navikli gledati i raditi s offsetom - ovaj pristup diktira sam standard.
Kao rezultat toga, malo ljudi razmišlja o promjeni pristupa paginaciji, a zbog toga se instrumentalna podrška okvira i knjižnica slabo razvija. Stoga, ako vam je bliska ideja i cilj offset-free paginacije, pomozite je proširiti!
Izvor:
Autor: Markus Winand
Izvor: www.habr.com