Sveiki visi! Esu užpakalinės programos kūrėjas, rašantis mikro paslaugas Java + Spring. Dirbu vienoje iš Tinkoff vidinių produktų kūrimo komandų.
Mūsų komandoje dažnai kyla klausimas, kaip optimizuoti užklausas DBVS. Visada norisi būti šiek tiek greitesnis, bet ne visada gali išsiversti su apgalvotai sudarytais indeksais – reikia ieškoti tam tikrų sprendimų. Per vieną iš šių klajonių po žiniatinklį, ieškodamas pagrįstų optimizacijų dirbant su duomenų bazėmis, radau , knygos „SQL Performance Explained“ autorius. Tai tas retas tinklaraštis, kuriame galite skaityti visus straipsnius iš eilės.
Norėčiau išversti jums trumpą Markuso straipsnį. Jį tam tikru mastu galima pavadinti manifestu, kuriuo siekiama atkreipti dėmesį į seną, bet vis dar aktualią poslinkio operacijos atlikimo pagal SQL standartą problemą.
Kai kur papildysiu autorę paaiškinimais ir komentarais. Visas tokias vietas vadinsiu „apytiksliai“. kad būtų daugiau aiškumo
Maža įžanga
Manau, kad daugelis žmonių žino, koks problemiškas ir lėtas yra darbas su puslapių pasirinkimu per ofsetą. Ar žinojote, kad jį gana nesunkiai galima pakeisti efektyvesniu dizainu?
Taigi, poslinkio raktinis žodis nurodo duomenų bazei praleisti pirmuosius n įrašų užklausoje. Tačiau duomenų bazė vis tiek turi nuskaityti šiuos pirmuosius n įrašų iš disko, nurodyta tvarka (pastaba: taikyti rūšiavimą, jei jis nurodytas), ir tik tada bus galima grąžinti įrašus nuo n+1. Įdomiausia tai, kad problema yra ne konkrečiame DBVS įgyvendinime, o pradiniame apibrėžime pagal standartą:
…eilutės pirmiausia rūšiuojamos pagal o tada apribojamas atmetant nurodytą eilučių skaičių nuo pradžios...
-SQL:2016, 2 dalis, 4.15.3 Išvestinės lentelės (pastaba: šiuo metu dažniausiai naudojamas standartas)
Svarbiausia čia yra ta, kad poslinkis užima vieną parametrą – įrašų, kuriuos reikia praleisti, skaičių, ir viskas. Vadovaudamasi šiuo apibrėžimu, DBVS gali tik nuskaityti visus įrašus, o tada išmesti nereikalingus. Akivaizdu, kad šis kompensavimo apibrėžimas verčia mus atlikti papildomą darbą. Ir net nesvarbu, ar tai SQL, ar NoSQL.
Tik šiek tiek daugiau skausmo
Problemos dėl kompensavimo tuo nesibaigia ir štai kodėl. Jei tarp dviejų duomenų puslapių skaitymo iš disko kita operacija įterpia naują įrašą, kas atsitiks šiuo atveju?
Kai poslinkis naudojamas įrašams iš ankstesnių puslapių praleisti, pridedant naują įrašą tarp skirtingų puslapių skaitymo, greičiausiai gausite dublikatus (pastaba: tai įmanoma, kai skaitome puslapį po puslapio naudodami tvarką pagal konstrukciją, tada mūsų išvesties viduryje jis gali gauti naują įrašą).
Paveikslėlyje aiškiai pavaizduota ši situacija. Bazė nuskaito pirmuosius 10 įrašų, po to įterpiamas naujas įrašas, kuris visus nuskaitytus įrašus kompensuoja 1. Tada bazė paima naują puslapį iš kitų 10 įrašų ir pradeda ne nuo 11, kaip turėtų, o nuo 10, dubliuojantis šį įrašą. Yra ir kitų anomalijų, susijusių su šio posakio vartojimu, tačiau tai yra dažniausia.
Kaip jau išsiaiškinome, tai nėra konkrečios DBVS ar jų diegimo problemos. Problema yra apibrėžiant puslapių skaičių pagal SQL standartą. Mes nurodome DBVS, kurį puslapį gauti arba kiek įrašų praleisti. Duomenų bazė tiesiog negali optimizuoti tokios užklausos, nes tam yra per mažai informacijos.
Taip pat verta paaiškinti, kad tai ne konkretaus raktinio žodžio, o užklausos semantikos problema. Yra dar kelios sintaksės, kurios savo probleminiu pobūdžiu yra identiškos:
- Poslinkio raktinis žodis yra toks, kaip minėta anksčiau.
- Dviejų raktinių žodžių konstrukcija limit [offset] (nors pats limitas nėra toks blogas).
- Filtravimas pagal apatines ribas, remiantis eilučių numeracija (pavyzdžiui, eilutės_numeris(), rownum ir kt.).
Visos šios išraiškos tiesiog nurodo, kiek eilučių reikia praleisti, jokios papildomos informacijos ar konteksto.
Vėliau šiame straipsnyje poslinkio raktinis žodis naudojamas kaip visų šių parinkčių santrauka.
Gyvenimas be OFFSET
Dabar įsivaizduokime, koks būtų mūsų pasaulis be visų šių problemų. Pasirodo, gyvenimas be poslinkio nėra toks sunkus: pasirinkdami galite pasirinkti tik tas eilutes, kurių dar nematėme (pastaba: tai yra, kurios nebuvo ankstesniame puslapyje), naudodami sąlygą kur.
Šiuo atveju pradedame nuo to, kad atrankos vykdomos pagal užsakytą rinkinį (senas geras orderis pagal). Kadangi turime užsakytą rinkinį, galime naudoti gana paprastą filtrą, kad gautume tik tuos duomenis, kurie yra už paskutinio ankstesnio puslapio įrašo:
SELECT ...
FROM ...
WHERE ...
AND id < ?last_seen_id
ORDER BY id DESC
FETCH FIRST 10 ROWS ONLYTai yra visas šio požiūrio principas. Žinoma, viskas pasidaro smagiau rūšiuojant pagal daugybę stulpelių, bet idėja vis tiek ta pati. Svarbu pažymėti, kad šis dizainas tinka daugeliui - sprendimai.
Šis metodas vadinamas paieškos metodu arba raktų rinkinio puslapiais. Jis išsprendžia slankiojo rezultato problemą (pastaba: rašymo tarp puslapių skaitymo situacija, aprašyta anksčiau) ir, žinoma, tai, kas mums visiems patinka, veikia greičiau ir stabiliau nei klasikinis poslinkis. Stabilumas slypi tame, kad užklausos apdorojimo laikas nepailgėja proporcingai prašomos lentelės skaičiui (pastaba: jei norite sužinoti daugiau apie skirtingų puslapių puslapių spausdinimo metodų darbą, galite . Ten taip pat galite rasti lyginamuosius skirtingų metodų etalonus).
Viena iš skaidrių kad puslapių rūšiavimas pagal raktus, žinoma, nėra visagalis – jis turi savo apribojimus. Svarbiausia, kad ji neturi galimybės skaityti atsitiktinių puslapių (pastaba: nenuosekliai). Tačiau begalinio slinkimo eroje (pastaba: priekinėje dalyje) tai nėra tokia problema. Puslapio numerio nurodymas spustelėjimui bet kokiu atveju yra blogas sprendimas UI dizaine (pastaba: straipsnio autoriaus nuomonė).
O kaip su įrankiais?
Klavišų puslapių rašymas dažnai netinka, nes trūksta šio metodo instrumentinio palaikymo. Dauguma kūrimo įrankių, įskaitant įvairius karkasus, neleidžia tiksliai pasirinkti, kaip bus atliekamas puslapių keitimas.
Situaciją apsunkina tai, kad aprašytam metodui reikalingas visų naudojamų technologijų palaikymas nuo galo iki galo - nuo DBVS iki AJAX užklausos vykdymo naršyklėje su begaliniu slinkimu. Užuot nurodyę tik puslapio numerį, dabar turite nurodyti klavišų rinkinį visiems puslapiams vienu metu.
Tačiau pamažu auga sistemų, palaikančių puslapių kodavimą raktais, skaičius. Štai ką šiuo metu turime:
- „Java“;
- Ruby;
- и už Django;
- skirtas Python;
- — kriterijų API JPA įgyvendinimui;
- Perlui;
- , žemėlapių sudarytojas, skirtas Node.js .
(Pastaba: kai kurios nuorodos buvo pašalintos dėl to, kad vertimo metu kai kurios bibliotekos nebuvo atnaujintos nuo 2017–2018 m. Jei susidomėjote, galite pažiūrėti originalų šaltinį.)
Būtent šiuo metu reikalinga jūsų pagalba. Jei kuriate ar palaikote sistemą, kurioje naudojamas puslapių numeravimas, prašau, raginu, prašau jūsų suteikti vietinę palaikymą puslapių spausdinimui naudojant raktus. Jei turite klausimų ar reikia pagalbos, mielai padėsiu (, , ) (pastaba: iš savo patirties su Marcusu galiu pasakyti, kad jis tikrai entuziastingai skleidžia šią temą).
Jei naudojate paruoštus sprendimus, kurie, jūsų nuomone, yra verti puslapių numeravimo pagal raktus palaikymo, sukurkite užklausą ar net pasiūlykite paruoštą sprendimą, jei įmanoma. Taip pat galite pateikti nuorodą į šį straipsnį.
išvada
Priežastis, kodėl toks paprastas ir naudingas metodas, kaip puslapių numeravimas pagal raktus, nėra plačiai paplitęs, yra ne dėl to, kad jį būtų sunku įgyvendinti techniškai ar reikia didelių pastangų. Pagrindinė priežastis ta, kad daugelis yra įpratę matyti ir dirbti su ofsetu – tokį požiūrį padiktuoja pats standartas.
Dėl to mažai žmonių galvoja apie tai, kaip pakeisti puslapių puslapių spausdinimą, ir dėl to instrumentinis rėmų ir bibliotekų palaikymas prastai vystosi. Todėl, jei jums artima bekompensavimo puslapių spausdinimo idėja ir tikslas, padėkite ją skleisti!
Šaltinis:
Autorius: Markus Winand
Šaltinis: www.habr.com