Hæ allir! Ég er bakendi verktaki sem skrifar örþjónustur í Java + Spring. Ég vinn í einu af innri vöruþróunarteymi Tinkoff.
Í teyminu okkar vaknar oft spurningin um að fínstilla fyrirspurnir í DBMS. Þú vilt alltaf vera aðeins hraðari, en þú getur ekki alltaf komist af með hugsi smíðaðar vísitölur - þú verður að leita að einhverjum lausnum. Á einu af þessum flakki um vefinn í leit að skynsamlegri hagræðingu þegar unnið er með gagnagrunna fann ég , höfundur SQL Performance Explained. Þetta er þessi sjaldgæfa tegund af bloggi þar sem þú getur lesið allar greinar í röð.
Mig langar að þýða stutta grein eftir Marcus fyrir þig. Það má að einhverju leyti kalla það stefnuskrá sem leitast við að vekja athygli á gamla, en samt viðeigandi vandamáli um frammistöðu offsetaðgerðarinnar samkvæmt SQL staðlinum.
Sums staðar mun ég bæta við höfund með skýringum og athugasemdum. Ég mun vísa til allra slíkra staða sem "u.þ.b. fyrir meiri skýrleika
Lítil kynning
Ég held að margir viti hversu erfið og hæg vinna með síðuval í gegnum offset er. Vissir þú að það er auðvelt að skipta um það með skilvirkari hönnun?
Svo, offset lykilorðið segir gagnagrunninum að sleppa fyrstu n færslunum í beiðninni. Hins vegar þarf gagnagrunnurinn enn að lesa þessar fyrstu n færslur af diski, í tiltekinni röð (ath. beita flokkun ef það er tilgreint), og þá fyrst verður hægt að skila færslum frá n+1 og áfram. Það áhugaverðasta er að vandamálið er ekki í sérstakri útfærslu í DBMS, heldur í upprunalegri skilgreiningu samkvæmt staðlinum:
…línurnar eru fyrst flokkaðar í samræmi við og síðan takmarkaðar með því að sleppa fjölda lína sem tilgreindar eru í frá upphafi…
-SQL:2016, Part 2, 4.15.3 Afleiddar töflur (athugið: sem stendur mest notaði staðallinn)
Lykilatriðið hér er að offset tekur eina færibreytu - fjölda færslur til að sleppa, og það er það. Eftir þessari skilgreiningu getur DBMS aðeins sótt allar færslur og síðan hent þeim óþarfa. Augljóslega neyðir þessi skilgreining á móti okkur til að vinna aukavinnu. Og það skiptir ekki einu sinni máli hvort það er SQL eða NoSQL.
Bara aðeins meiri sársauki
Vandamálin með offset enda ekki þar, og hér er ástæðan. Ef, á milli þess að lesa tvær blaðsíður af gögnum af diski, setur önnur aðgerð inn nýja skrá, hvað mun gerast í þessu tilfelli?
Þegar offset er notað til að sleppa skrám af fyrri síðum, í þeim aðstæðum að bæta við nýrri skrá á milli lestra mismunandi síðna, færðu líklega afrit (ath. þetta er mögulegt þegar við lesum síðu fyrir síðu með því að nota röð eftir smíði, þá í miðju framleiðslu okkar gæti það fengið nýja færslu).
Myndin sýnir þetta ástand greinilega. Grunnurinn les fyrstu 10 færslurnar, eftir það er ný færslu sett inn, sem vegur allar lesnar færslur um 1. Þá tekur grunnurinn nýja síðu úr næstu 10 færslum og byrjar ekki á þeirri 11., eins og ætti að gera, heldur frá 10., afrit þessa plötu. Það eru önnur frávik tengd notkun þessarar tjáningar, en þetta er algengast.
Eins og við höfum þegar komist að, eru þetta ekki vandamál tiltekins DBMS eða útfærslur þeirra. Vandamálið er að skilgreina blaðsíðugerð samkvæmt SQL staðlinum. Við segjum DBMS hvaða síðu á að sækja eða hversu mörgum færslum á að sleppa. Gagnagrunnurinn er einfaldlega ekki fær um að hagræða slíkri beiðni þar sem of litlar upplýsingar eru til fyrir þetta.
Það er líka rétt að skýra að þetta er ekki vandamál með tiltekið leitarorð, heldur með merkingarfræði fyrirspurnarinnar. Það eru nokkrir fleiri setningafræði sem eru eins í vandræðum eðli sínu:
- Offset lykilorðið er eins og fyrr segir.
- Samsetning tveggja leitarorða takmarka [á móti] (þó að takmörk sjálft sé ekki svo slæmt).
- Sía eftir lægri mörkum, byggt á röð númera (til dæmis, row_number(), rownum, osfrv.).
Allar þessar tjáningar segja þér einfaldlega hversu mörgum línum þú átt að sleppa, engar viðbótarupplýsingar eða samhengi.
Síðar í þessari grein er offset leitarorðið notað sem samantekt á öllum þessum valkostum.
Líf án OFFSET
Nú skulum við ímynda okkur hvernig heimurinn okkar væri án allra þessara vandamála. Það kemur í ljós að lífið án mótvægis er ekki svo erfitt: með vali geturðu valið aðeins þær línur sem við höfum ekki enn séð (athugið: það er þær sem voru ekki á fyrri síðu), með því að nota ástand þar sem.
Í þessu tilviki byrjum við á þeirri staðreynd að val er framkvæmt á skipulögðu setti (gamla góða röð eftir). Þar sem við erum með pantað sett getum við notað frekar einfalda síu til að fá aðeins gögnin sem eru á bak við síðustu skráningu fyrri síðu:
SELECT ...
FROM ...
WHERE ...
AND id < ?last_seen_id
ORDER BY id DESC
FETCH FIRST 10 ROWS ONLYÞað er meginreglan í þessari nálgun. Auðvitað verða hlutirnir skemmtilegri þegar flokkað er eftir mörgum dálkum, en hugmyndin er samt sú sama. Það er mikilvægt að hafa í huga að þessi hönnun á við um marga -ákvarðanir.
Þessi nálgun er kölluð leitaraðferð eða lyklasett blaðsíðugerð. Það leysir fljótandi niðurstöðu vandamálið (athugið: ástandið með að skrifa á milli lestra blaðsíðna sem lýst var áðan) og auðvitað, það sem við elskum öll, það virkar hraðar og stöðugra en klassískt offset. Stöðugleiki felst í því að vinnslutími beiðna eykst ekki í hlutfalli við fjölda umbeðinna töflu (athugið: ef þú vilt fræðast meira um vinnu mismunandi aðferða við blaðsíðugerð geturðu . Þú getur líka fundið samanburðarviðmið fyrir mismunandi aðferðir þar).
Ein af glærunum að blaðsíðuskipun eftir lyklum er auðvitað ekki almáttug - hún hefur sínar takmarkanir. Mikilvægast er að hún hefur ekki getu til að lesa tilviljanakenndar síður (ath. ósamræmi). Hins vegar, á tímum endalausrar skrununar (athugið: á framendanum), er þetta ekki svo vandamál. Að tilgreina blaðsíðunúmer til að smella á er slæm ákvörðun í hönnun HÍ hvort sem er (ath: skoðun höfundar greinarinnar).
Hvað með verkfærin?
Síðuskipting á lyklum hentar oft ekki vegna skorts á hljóðfærastuðningi fyrir þessa aðferð. Flest þróunarverkfæri, þar á meðal ýmsar rammar, leyfa þér ekki að velja nákvæmlega hvernig síðuskipun verður framkvæmd.
Ástandið versnar af þeirri staðreynd að aðferðin sem lýst er krefst enda-til-enda stuðning í tækninni sem notuð er - frá DBMS til framkvæmdar á AJAX beiðni í vafranum með endalausri skrun. Í stað þess að tilgreina bara blaðsíðunúmerið þarftu nú að tilgreina lyklasett fyrir allar síðurnar í einu.
Hins vegar fjölgar smám saman fjölda ramma sem styðja blaðsíðusetningu á lyklum. Hér er það sem við höfum í augnablikinu:
- fyrir Java;
- fyrir Ruby;
- и fyrir Django;
- fyrir Python;
- — API viðmiðunarskilmála fyrir JPA útfærslur;
- fyrir Perl;
- , kortlagningarmaður fyrir Node.js .
(Athugið: sumir tenglar voru fjarlægðir vegna þess að við þýðingar höfðu sum bókasöfn ekki verið uppfærð síðan 2017-2018. Ef þú hefur áhuga geturðu skoðað upprunalegu heimildina.)
Það er á þessari stundu sem þín er þörf. Ef þú þróar eða styður ramma sem notar blaðsíðugerð á nokkurn hátt, þá bið ég, ég hvet þig til að veita innfæddan stuðning við blaðsíðusetningu á lyklum. Ef þú hefur spurningar eða þarft hjálp, mun ég vera fús til að hjálpa (, , ) (ath. af reynslu minni af Marcus, get ég sagt að hann er mjög áhugasamur um að dreifa þessu efni).
Ef þú notar tilbúnar lausnir sem þú telur þess virði að hafa stuðning við blaðsíðuskipun með lyklum, búðu til beiðni eða bjóðu jafnvel upp á tilbúna lausn, ef mögulegt er. Þú getur líka tengt við þessa grein.
Ályktun
Ástæðan fyrir því að svo einföld og gagnleg nálgun eins og blaðsíðuskipun eftir lyklum er ekki útbreidd er ekki sú að það er erfitt í framkvæmd tæknilega eða krefst mikillar fyrirhafnar. Ástæðan er fyrst og fremst sú að margir eru vanir að sjá og vinna með offset - þessi nálgun er ræðst af staðlinum sjálfum.
Fyrir vikið hugsa fáir um að breyta nálguninni á blaðsíðugerð og af þeim sökum þróast stuðningur frá umgjörðum og bókasöfnum illa. Þess vegna, ef hugmyndin og markmiðið með offsetlausri blaðsíðusetningu er nálægt þér, hjálpaðu þér að dreifa henni!
Heimild:
Höfundur: Markus Winand
Heimild: www.habr.com