Foarby binne de dagen dat jo gjin soargen hoege te meitsjen oer it optimalisearjen fan databaseprestaasjes. De tiid stiet net stil. Elke nije tech-ûndernimmer wol de folgjende Facebook meitsje, wylst se besykje alle gegevens te sammeljen dy't se har hannen kinne krije. Bedriuwen hawwe dizze gegevens nedich om modellen better te trenen dy't har helpe om jild te meitsjen. Yn sokke betingsten moatte programmeurs API's oanmeitsje dy't har tastean om fluch en betrouber te wurkjen mei enoarme hoemannichten ynformaasje.
As jo applikaasje- of database-backends foar elke tiid hawwe ûntwerpen, hawwe jo wierskynlik koade skreaun om pagineare queries út te fieren. Bygelyks, lykas dit:
SELECT * FROM table_name LIMIT 10 OFFSET 40
Hoe is it?
Mar as dit is hoe't jo jo paginering dien hawwe, it spyt my om te sizzen dat jo it net op 'e meast effisjinte manier dien hawwe.
Wolle jo tsjin my beswier meitsje? . , и Se brûke al de techniken dêr't ik hjoed oer prate wol.
Neam op syn minst ien backend-ûntwikkelder dy't nea brûkt hat OFFSET и LIMIT om pagineare fragen út te fieren. Yn MVP (Minimum Viable Product) en yn projekten dêr't lytse hoemannichten gegevens wurde brûkt, dizze oanpak is frij fan tapassing. It "wurket gewoan", sa te sizzen.
Mar as jo betroubere en effisjinte systemen fanôf it begjin moatte oanmeitsje, moatte jo foarôf soargje foar de effisjinsje fan it besykjen fan de databases dy't yn sokke systemen brûkt wurde.
Hjoed sille wy prate oer de problemen mei gewoan brûkte (te min) ymplemintaasjes fan paginearre query-motoren, en hoe't jo hege prestaasjes kinne berikke by it útfieren fan sokke queries.
Wat is der mis mei OFFSET en LIMIT?
Lykas al sein, OFFSET и LIMIT Se prestearje goed yn projekten dy't net hoege te wurkjen mei grutte hoemannichten gegevens.
It probleem ûntstiet as de databank sa'n grutte groeit dat it net mear past yn it ûnthâld fan de tsjinner. As jo lykwols wurkje mei dizze databank, moatte jo pagineare queries brûke.
Foar dit probleem om himsels te manifestearjen, moat d'r in situaasje wêze wêryn't de DBMS taflecht ta in net effisjinte folsleine tabelscan-operaasje op elke pagineare query (wylst ynfoegje- en wiskjen operaasjes kinne foarkomme, en wy hawwe gjin ferâldere gegevens nedich!).
Wat is in "folsleine tabel scan" (of "sekwinsjele tabel scan", Sequential Scan)? Dit is in operaasje wêryn't de DBMS sequentially elke rige fan 'e tabel lêst, dat is de gegevens dy't deryn binne, en kontrolearret se op neilibjen fan in bepaalde betingst. It is bekend dat dit type tafelscan de stadichste is. It feit is dat as it wurdt útfierd, in protte ynfier / útfier operaasjes wurde útfierd dy't belûke de tsjinner syn skiif subsysteem. De situaasje wurdt slimmer makke troch de latency ferbûn mei it wurkjen mei gegevens opslein op skiven, en it feit dat it oerdragen fan gegevens fan skiif nei ûnthâld is in boarne-yntinsive operaasje.
Jo hawwe bygelyks records fan 100000000 brûkers en jo rinne in query mei de konstruksje OFFSET 50000000. Dit betsjut dat de DBMS al dizze records sil moatte laden (en wy hawwe se net iens nedich!), set se yn it ûnthâld, en dêrnei nimme, sis, 20 resultaten rapportearre yn LIMIT.
Litte wy sizze dat it der sa útsjen kin: "selektearje rigen fan 50000 oant 50020 fan 100000". Dat is, it systeem sil earst 50000 rigen moatte laden om de query te foltôgjen. Sjogge jo hoefolle ûnnedich wurk se dwaan sil?
As jo my net leauwe, sjoch dan ris nei it foarbyld dat ik makke mei de funksjes .
Foarbyld op db-fiddle.com
Dêr, links, yn it fjild Schema SQL, d'r is koade dy't 100000 rigen ynfoeget yn 'e databank, en rjochts, yn it fjild Query SQL, wurde twa fragen werjûn. De earste, stadige, sjocht der sa út:
SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;
En de twadde, dy't in effektive oplossing is foar itselde probleem, is sa:
SELECT *
FROM `docs`
WHERE id > 85000
LIMIT 10;
Om dizze oanfragen te ferfoljen, klikje jo gewoan op de knop Run oan de boppekant fan de side. Nei't wy dit dien hawwe, fergelykje wy ynformaasje oer de útfieringstiid fan 'e query. It docht bliken dat it útfieren fan in net-effektive query op syn minst 30 kear langer duorret as it útfieren fan de twadde (dizze tiid ferskilt fan run oant run; it systeem kin bygelyks melde dat de earste query 37 ms duorre om te foltôgjen, mar de útfiering fan de twadde - 1 ms).
En as d'r mear gegevens binne, dan sil alles noch slimmer útsjen (om hjirfan oertsjûge te wurden, sjoch ris nei myn mei 10 miljoen rigen).
Wat wy krekt hawwe besprutsen, soe jo wat ynsjoch jaan moatte yn hoe't databankfragen eins wurde ferwurke.
Tink derom dat hoe heger de wearde OFFSET - hoe langer it fersyk sil nimme om te foltôgjen.
Wat moat ik brûke ynstee fan de kombinaasje fan OFFSET en LIMIT?
Yn stee fan in kombinaasje OFFSET и LIMIT It is it wurdich om in struktuer te brûken, boud neffens it folgjende skema:
SELECT * FROM table_name WHERE id > 10 LIMIT 20
Dit is query-útfiering mei paginering basearre op rinnerke.
Yn stee fan it opslaan fan aktuele lokaal OFFSET и LIMIT en stjoer se mei elk fersyk, jo moatte de lêste ûntfongen primêre kaai opslaan (meastal is dit ID) en LIMIT, as gefolch, queries fergelykber mei de boppesteande wurde krigen.
Wêrom? It punt is dat troch eksplisyt de identifier fan 'e lêste reade rige oan te jaan, jo jo DBMS fertelle wêr't it moat begjinne te sykjen nei de nedige gegevens. Boppedat sil it sykjen, troch it gebrûk fan 'e kaai, effisjint wurde útfierd, it systeem sil net moatte wurde ôfliede troch rigels bûten it opjûne berik.
Litte wy nei de folgjende prestaasjesfergeliking fan ferskate fragen besjen. Hjir is in net effektyf query.
Stadich fersyk
En hjir is in optimalisearre ferzje fan dit fersyk.
Fluch fersyk
Beide query's jouwe krekt deselde hoemannichte gegevens werom. Mar de earste duorret 12,80 sekonden om te foltôgjen, en de twadde nimt 0,01 sekonden. Fielsto it ferskil?
Mooglike problemen
Foar de foarstelde query-metoade om effektyf te wurkjen, moat de tabel in kolom (of kolommen) hawwe mei unike, opfolgjende yndeksen, lykas in hiel getal identifier. Yn guon spesifike gefallen kin dit it sukses bepale fan it brûken fan sokke fragen om de snelheid fan wurkjen mei de databank te fergrutsjen.
Fansels moatte jo by it konstruearjen fan queries rekken hâlde mei de spesifike arsjitektuer fan 'e tabellen en kieze dy meganismen dy't it bêste sille wurkje op' e besteande tabellen. As jo bygelyks moatte wurkje yn queries mei grutte folumes oan relatearre gegevens, kinne jo it miskien ynteressant fine lidwurd.
As wy te krijen hawwe mei it probleem fan it ûntbrekken fan in primêre kaai, bygelyks as wy in tabel hawwe mei in protte-to-in protte relaasje, dan is de tradisjonele oanpak fan it brûken fan OFFSET и LIMIT, is garandearre te passe ús. Mar it gebrûk kin resultearje yn potinsjeel trage fragen. Yn sokke gefallen soe ik oanrikkemandearje om in auto-inkrementearjende primêre kaai te brûken, sels as it allinich nedich is om pagineare fragen te behanneljen.
As jo ynteressearre binne yn dit ûnderwerp - , и - ferskate nuttige materialen.
Resultaten
De wichtichste konklúzje dy't wy kinne lûke is dat, nettsjinsteande de grutte fan databases wêr't wy it oer hawwe, it altyd needsaaklik is om de snelheid fan query-útfiering te analysearjen. Tsjintwurdich is de skalberens fan oplossingen ekstreem wichtich, en as alles goed is ûntwurpen fan it begjin fan wurkjen oan in bepaald systeem, kin dit yn 'e takomst de ûntwikkelder fan in protte problemen rêde.
Hoe analysearje en optimalisearje jo databankfragen?
Boarne: www.habr.com