Kanë ikur ditët kur nuk duhet të shqetësoheshit për optimizimin e performancës së bazës së të dhënave. Koha nuk qëndron ende. Çdo sipërmarrës i ri i teknologjisë dëshiron të krijojë Facebook-un e radhës, ndërkohë që përpiqet të mbledhë të gjitha të dhënat që mund të marrin në dorë. Bizneset kanë nevojë për këto të dhëna për të trajnuar më mirë modelet që i ndihmojnë ata të fitojnë para. Në kushte të tilla, programuesit duhet të krijojnë API që i lejojnë ata të punojnë shpejt dhe me besueshmëri me sasi të mëdha informacioni.
Nëse keni projektuar aplikacione ose bazë të dhënash mbështetëse për një kohë të gjatë, me siguri keni shkruar kod për të ekzekutuar pyetje të faqezuara. Për shembull, si kjo:
SELECT * FROM table_name LIMIT 10 OFFSET 40
Si është?
Por nëse ju e keni bërë kështu faqetimin tuaj, më vjen keq të them që nuk e keni bërë në mënyrën më efikase.
Dëshiron të më kundërshtosh? . , и Ata tashmë po përdorin teknikat për të cilat dua të flas sot.
Emërtoni të paktën një zhvillues prapavijë që nuk e ka përdorur kurrë OFFSET и LIMIT për të kryer pyetje të faqezuara. Në MVP (Minimum Viable Product) dhe në projektet ku përdoren sasi të vogla të dhënash, kjo qasje është mjaft e zbatueshme. Ajo "thjesht funksionon", si të thuash.
Por nëse keni nevojë të krijoni sisteme të besueshme dhe efikase nga e para, duhet të kujdeseni paraprakisht për efikasitetin e kërkimit të bazave të të dhënave të përdorura në sisteme të tilla.
Sot do të flasim për problemet me implementimet e përdorura (shumë keq) të motorëve të pyetjeve me faqe dhe si të arrijmë performancë të lartë gjatë ekzekutimit të pyetjeve të tilla.
Çfarë nuk shkon me OFFSET dhe LIMIT?
Siç është thënë tashmë, OFFSET и LIMIT Ata performojnë mirë në projekte që nuk kanë nevojë të punojnë me sasi të mëdha të dhënash.
Problemi lind kur baza e të dhënave rritet në një madhësi të tillë që nuk përshtatet më në kujtesën e serverit. Megjithatë, kur punoni me këtë bazë të dhënash, duhet të përdorni pyetje të faqezuara.
Që ky problem të shfaqet, duhet të ekzistojë një situatë në të cilën DBMS përdor një operacion joefikas Skanimi i tabelës së plotë në çdo pyetje të faqezuar (ndërsa operacionet e futjes dhe fshirjes mund të ndodhin, dhe ne nuk kemi nevojë për të dhëna të vjetruara!).
Çfarë është një "skanim i plotë i tabelës" (ose "skanim i njëpasnjëshëm i tabelës", Skanim Sekuencial)? Ky është një operacion gjatë të cilit DBMS lexon në mënyrë sekuenciale çdo rresht të tabelës, domethënë të dhënat që gjenden në të, dhe i kontrollon ato për pajtueshmërinë me një kusht të caktuar. Ky lloj skanimi i tabelës njihet si më i ngadalshmi. Fakti është se kur ai ekzekutohet, kryhen shumë operacione hyrëse/dalëse që përfshijnë nënsistemin e diskut të serverit. Situata përkeqësohet nga vonesa e lidhur me punën me të dhënat e ruajtura në disqe dhe fakti që transferimi i të dhënave nga disku në memorie është një operacion intensiv me burime.
Për shembull, ju keni të dhëna me 100000000 përdorues dhe kryeni një pyetje me konstruktin OFFSET 50000000. Kjo do të thotë që DBMS do të duhet të ngarkojë të gjitha këto regjistrime (dhe as që na duhen!), t'i vendosë në memorie dhe pas kësaj të marrë, të themi, 20 rezultate të raportuara në LIMIT.
Le të themi se mund të duket kështu: "zgjidhni rreshtat nga 50000 në 50020 nga 100000". Kjo do të thotë, sistemi fillimisht do të duhet të ngarkojë 50000 rreshta për të përfunduar pyetjen. E shihni sa punë të panevojshme do t'i duhet të bëjë?
Nëse nuk më besoni, hidhini një sy shembullit që kam krijuar duke përdorur veçoritë .
Shembull në db-fiddle.com
Atje, në të majtë, në fushë Schema SQL, ka kod që fut 100000 rreshta në bazën e të dhënave, dhe në të djathtë, në fushë Query SQL, shfaqen dy pyetje. E para, e ngadaltë, duket kështu:
SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;
Dhe e dyta, e cila është një zgjidhje efektive për të njëjtin problem, është si kjo:
SELECT *
FROM `docs`
WHERE id > 85000
LIMIT 10;
Për të përmbushur këto kërkesa, thjesht klikoni në butonin Run në krye të faqes. Pasi e kemi bërë këtë, ne krahasojmë informacionin në lidhje me kohën e ekzekutimit të pyetjes. Rezulton se ekzekutimi i një pyetjeje joefektive zgjat të paktën 30 herë më shumë se ekzekutimi i të dytës (kjo kohë ndryshon nga ekzekutimi në ekzekutim; për shembull, sistemi mund të raportojë se pyetja e parë mori 37 ms për të përfunduar, por ekzekutimi i e dyta - 1 ms).
Dhe nëse ka më shumë të dhëna, atëherë gjithçka do të duket edhe më keq (për t'u bindur për këtë, hidhini një sy te mija me 10 milionë rreshta).
Ajo që sapo diskutuam duhet t'ju japë një pasqyrë se si përpunohen në të vërtetë kërkesat e bazës së të dhënave.
Ju lutemi vini re se sa më e lartë të jetë vlera OFFSET - aq më shumë do të duhet për të përfunduar kërkesën.
Çfarë duhet të përdor në vend të kombinimit të OFFSET dhe LIMIT?
Në vend të një kombinimi OFFSET и LIMIT Vlen të përdoret një strukturë e ndërtuar sipas skemës së mëposhtme:
SELECT * FROM table_name WHERE id > 10 LIMIT 20
Ky është ekzekutimi i pyetjes me faqezim të bazuar në kursor.
Në vend që të ruani ato aktuale në nivel lokal OFFSET и LIMIT dhe t'i transmetoni ato me secilën kërkesë, duhet të ruani çelësin parësor të fundit të marrë (zakonisht ky është ID) Dhe LIMIT, si rezultat, do të merren pyetje të ngjashme me sa më sipër.
Pse? Çështja është se duke specifikuar në mënyrë eksplicite identifikuesin e rreshtit të fundit të lexuar, ju tregoni DBMS-në tuaj se ku duhet të fillojë të kërkojë të dhënat e nevojshme. Për më tepër, kërkimi, falë përdorimit të çelësit, do të kryhet në mënyrë efikase; sistemi nuk do të duhet të shpërqendrohet nga linjat jashtë gamës së specifikuar.
Le të hedhim një vështrim në krahasimin e mëposhtëm të performancës së pyetjeve të ndryshme. Këtu është një pyetje joefektive.
Kërkesë e ngadaltë
Dhe këtu është një version i optimizuar i kësaj kërkese.
Kërkesë e shpejtë
Të dy pyetjet kthejnë saktësisht të njëjtën sasi të dhënash. Por i pari merr 12,80 sekonda për të përfunduar, dhe i dyti merr 0,01 sekonda. E ndjeni ndryshimin?
Probleme të mundshme
Që metoda e propozuar e pyetjes të funksionojë në mënyrë efektive, tabela duhet të ketë një kolonë (ose kolona) që përmbajnë indekse unike, të njëpasnjëshme, të tilla si një identifikues i plotë. Në disa raste specifike, kjo mund të përcaktojë suksesin e përdorimit të pyetjeve të tilla për të rritur shpejtësinë e punës me bazën e të dhënave.
Natyrisht, kur ndërtoni pyetje, duhet të merrni parasysh arkitekturën specifike të tabelave dhe të zgjidhni ato mekanizma që do të funksionojnë më mirë në tabelat ekzistuese. Për shembull, nëse ju duhet të punoni në pyetje me vëllime të mëdha të të dhënave të lidhura, mund t'ju duket interesante artikull.
Nëse përballemi me problemin e mungesës së një çelësi parësor, për shembull, nëse kemi një tabelë me një marrëdhënie shumë-me-shumë, atëherë qasja tradicionale e përdorimit OFFSET и LIMIT, garantohet se na përshtatet. Por përdorimi i tij mund të rezultojë në pyetje potencialisht të ngadalta. Në raste të tilla, unë do të rekomandoja përdorimin e një çelësi primar me rritje automatike, edhe nëse ai nevojitet vetëm për të trajtuar pyetjet me faqe.
Nëse jeni të interesuar për këtë temë - , и - disa materiale të dobishme.
Rezultatet e
Përfundimi kryesor që mund të nxjerrim është se, pavarësisht për çfarë madhësie të bazave të të dhënave po flasim, është gjithmonë e nevojshme të analizohet shpejtësia e ekzekutimit të pyetjeve. Në ditët e sotme, shkallëzueshmëria e zgjidhjeve është jashtëzakonisht e rëndësishme, dhe nëse gjithçka është projektuar në mënyrë korrekte që nga fillimi i punës në një sistem të caktuar, kjo, në të ardhmen, mund ta shpëtojë zhvilluesin nga shumë probleme.
Si i analizoni dhe optimizoni pyetjet e bazës së të dhënave?
Burimi: www.habr.com