Koha janĂ« kaluar ditĂ«t kur nuk duheshin bĂ«rĂ« kujdes pĂ«r optimizimin e performancĂ«s sĂ« bazave tĂ« tĂ« dhĂ«nave. Koha nuk qĂ«ndron nĂ« njĂ« vend. Ădo biznesmen i ri nĂ« fushĂ«n e teknologjisĂ« dĂ«shiron tĂ« krijojĂ« Facebook-un tjetĂ«r, duke u pĂ«rpjekur tĂ« grumbullojĂ« tĂ« gjitha tĂ« dhĂ«nat qĂ« mund tĂ« arrijĂ«. KĂ«to tĂ« dhĂ«na janĂ« tĂ« nevojshme pĂ«r biznesin pĂ«r tĂ« mĂ«suar mĂ« mirĂ« modelet qĂ« ndihmojnĂ« nĂ« fitim. NĂ« kĂ«to kushte, programuesit duhet tĂ« krijojnĂ« API qĂ« lejojnĂ« tĂ« punojnĂ« shpejt dhe sigurt me njĂ« sasi tĂ« madhe informacioni.
Nëse keni qënë duke u marrë me projektimin e pjesëve serverike të aplikacioneve ose bazave të të dhënave për një kohë, ndoshta keni shkruar kod për të bërë kërkesa me ndarjen në faqe. Për shembull, si ky:
SELECT * FROM table_name LIMIT 10 OFFSET 40
A është kështu?
Por nëse keni bërë ndarjen në faqe në këtë mënyrë, më vjen keq të them se nuk e keni bërë atë në mënyrën më efikase.
Doni të më tregoni ndryshe? . , dhe tani po përdorin teknikave që do të flas sot.
Përmendni një zhvillues backend që nuk ka përdorur kurrë OFFSET dhe LIMIT për të bërë kërkesa me ndarjen në faqe. Në MVP (Minimum Viable Product, produkti minimal i qëndrueshëm) dhe në projekte ku përdoren sasi të vogla të dhënash, ky qasje është mjaft e aplikueshme. Ajo, siç thuhet, "thjesht funksionon".
Por nëse duhet të krijoni nga e para sisteme të besueshme dhe efikase, duhet të mendoni paraprakisht për efikasitetin e ekzekutimit të kërkesave në bazën e të dhënave që përdoren në këto sisteme.
Sot do të flasim për problemet që lidhen me realizimet e zakonshme (për fat të keq) të mekanizmave të ekzekutimit të kërkesave me ndarjen në faqe, dhe si të arrijmë performancë të lartë duke ekzekutuar kërkesa të tillë.
ĂfarĂ« nuk shkon me OFFSET dhe LIMIT?
Siç është përmendur, OFFSET dhe LIMIT janë të shkëlqyera në projekte ku nuk nevojitet të punoni me sasi të mëdha të të dhënave.
Problemi shfaqet kur baza e të dhënave rritet në një përmasë që nuk mund të përballohet në memorien e serverit. Por gjatë punës me këtë bazë të dhënash, nevojiten kërkesa me ndarjen në faqe.
Për të siguruar që ky problem të shfaqet, duhet të ndodhë një situatë në të cilën SGBD-ja (Sistemi i Menaxhimit të Bazës së Dhënave) arrin në një operacion të paefikshëm të skanimit të plotë të tabelës (Full Table Scan) gjatë çdo kërkese me ndarjen në faqe (në të njëjtën kohë mund të ndodhin operacione të futjes dhe fshirjes së të dhënave, dhe të dhënat e vjetruara nuk na duhen!).
ĂfarĂ« Ă«shtĂ« "skanimi i plotĂ« i tabelĂ«s" (ose "shikimi i renditshĂ«m tĂ« tabelĂ«s", Sequential Scan)? Kjo Ă«shtĂ« njĂ« operacion nĂ« tĂ« cilin SGBD-ja lexon çdo rresht tĂ« tabelĂ«s nĂ« rend, domethĂ«nĂ«, tĂ« dhĂ«nat qĂ« pĂ«rmban dhe i kontrollon ato nĂ« pĂ«rputhje me kushtin e caktuar. TĂ« gjithĂ« e dinĂ« se ky lloj skanimi i tabelave Ă«shtĂ« mĂ« i ngadalti. Kjo ndodh pasi gjatĂ« ekzekutimit tĂ« tij kryhen shumĂ« operacione hyrĂ«se/dalĂ«se, duke angazhuar sistemin e diskut tĂ« serverit. Situata pĂ«rkeqĂ«sohet nga vonesat qĂ« lidhen me punĂ«n me tĂ« dhĂ«nat tĂ« ruajtura nĂ« disqet, dhe se transferimi i tĂ« dhĂ«nave nga disku nĂ« memorie Ă«shtĂ« njĂ« operacion qĂ« kĂ«rkon burime.
Për shembull, keni të dhëna për 100000000 përdorues, dhe ju ekzekutoni një kërkesë me ndihmën e OFFSET 50000000. Kjo do të thotë se SGBD-ja do të jetë e detyruar të ngarkojë të gjitha këto të dhëna (dhe ne as nuk na nevojiten ato!), t'i vendosë ato në memorie, dhe vetëm pastaj të marrë, le të themi, 20 rezultate për të cilat është raportuar në LIMIT.
Le të themi, kjo mund të duket kështu: "zgjedh rreshtat nga 50000 deri në 50020 nga 100000". Domethënë, për ekzekutimin e kërkesës sistemi duhet së pari të ngarkojë 50000 rreshta. E shihni sa shumë punë të panevojshme duhet të bëjë?
NĂ«se nuk e besoni - shikoni shembullin qĂ« kam krijuar duke pĂ«rdorur mundĂ«sitĂ« e .Â
Shembuj në db-fiddle.com
Atje, në anën e majtë, në fushën Schema SQL, ka një kod që kryen fiksimin në bazën e të dhënave 100000 rreshta, ndërsa në anën e djathtë, në fushën Query SQL, janë përshkruar dy kërkesa. E para, e ngadaltë, duket kështu:
SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;
Dhe e dyta, e cila përbën një zgjidhje efikase të të njëjtës detyrë, është kështu:
SELECT *
FROM `docs`
WHERE id > 85000
LIMIT 10;
Për të ekzekutuar këto kërkesa, mjafton të klikoni në butonin Ekzekuto në pjesën e sipërme të faqes. Kur ta bëni këtë, do të krahasoni informacionin mbi kohën e ekzekutimit të kërkesave. Del që ekzekutimi i kërkesës joefikase merr të paktën 30 herë më shumë kohë sesa ai i dyti (nga një fillim në tjetrin kjo kohë ndryshon, për shembull, sistemi mund të raportojë se për ekzekutimin e kërkesës së parë ka kaluar 37 ms, kurse për të dytën - 1 ms).
Dhe nëse të dhënat do të ishin më shumë, gjithçka do të dukej edhe më keq (për ta konfirmuar këtë, shikoni në me 10 milion rreshta).
Ajo që sapo diskutuam duhet t'ju japë njëfarë kuptimi se si, në të vërtetë, përpunohen kërkesat në bazat e të dhënave.
Konsideroni se sa më e madhe të jetë vlera OFFSET aq më gjatë do të zgjasë realizimi i kërkesës.
ĂfarĂ« duhet tĂ« pĂ«rdoret nĂ« vend tĂ« kombinimit OFFSET dhe LIMIT?
Në vend të kombinimit OFFSET dhe LIMIT duhet të përdoret një strukturë që është e ndërtuar sipas këtij skemë:
SELECT * FROM table_name WHERE id > 10 LIMIT 20
Kjo është realizimi i kërkesës me ndarje në faqe, e bazuar në kursorin (Cursor based pagination).
Në vend që të ruani lokalisht aktualet OFFSET dhe LIMIT dhe t'i transmetoni ato me çdo kërkesë, duhet të ruani çelësin e fundit të marrë të parë (zakonisht, është ID) dhe LIMIT, dhe në rezultat do të përfshihen kërkesa që ngjajnë me ato më sipër.
Pse? Kjo është për shkak se duke specifikuar në mënyrë eksplicite identifikuesin e rreshtit të fundit të lexuar, ju i njoftoni sistemit tuaj të bazës së të dhënave për vendin ku duhet të fillojë të kërkojë të dhënat e nevojshme. Dhe, këtu, kërkimi, falë përdorimit të çelësit, do të realizohet me efikasitet, sistemi nuk do të duhet të shqetësohet për rreshtat që janë jashtë gamës së specifikuar.
Le të shohim krahasimin e mëposhtëm të performancës së kërkesave të ndryshme. Këtu është një kërkesë joefikase.
Kërkesë e ngadaltë
Dhe këtu është një version i optimizuar i kësaj kërkese.
Një kërkesë e shpejtë
Të dyja kërkesat kthejnë saktësisht të njëjtin volum të të dhënave. Por për realizimin e së parës nevojiten 12.80 sekonda, ndërsa për të dytën - 0.01 sekonda. E ndjeni dallimin?
Problemet e mundshme
Për të siguruar funksionimin efikas të metodës së propozuar të realizimit të kërkesave, është e nevojshme që në tabelë të ketë një kolonë (apo kolona) që përmban indekse unikë, të vendosur në mënyrë të renditur, si identifikues integer. Në disa raste specifike kjo mund të përcaktojë suksesin e përdorimit të tillë të kërkesave për të përmirësuar shpejtësinë e punës me bazën e të dhënave.
Natyrisht, duke ndërtuar kërkesa, duhet të keni parasysh karakteristikat e arkitekturës së tabelave dhe të zgjidhni mekanizmat që do të tregojnë performancën më të mirë në tabelat ekzistuese. Për shembull, nëse duhet të punoni me kërkesa që përfshijnë volume të mëdha të të dhënave të lidhura, mund t'ju duket e dobishme artikulli.
Nëse na paraqitet problemi i mungesës së çelësit të parë, për shembull, nëse ekziston një tabelë me një lidhje "mijëra me mijëra", atëherë qasja tradicionale, që parashikon përdorimin OFFSET dhe LIMIT, do të jetë e përshtatshme për ne. Por përdorimi i saj mund të çojë në realizimin e kërkesave të mundshme të ngadalta. Në këso raste, do t'ju rekomandoja të përdorni një çelës të parë me autoinkrementim, edhe nëse ai nevojitet vetëm për organizimin e realizimit të kërkesave me ndarje në faqe.
Nëse jeni të interesuar për këtë temë - , dhe - disa materiale të dobishme.
PĂ«rfundimet
Pika kryesore që mund të nxjerrim është se gjithmonë, pavarësisht nga madhësia e bazave të të dhënave, duhet të analizojmë shpejtësinë e realizimit të kërkesave. Në kohën tonë, skalabiliteti i zgjidhjeve është jashtëzakonisht i rëndësishëm, dhe nëse që në fillim të punës mbi një sistem projektohet gjithçka siç duhet, kjo në të ardhmen mund ta shpëtojë zhvilluesin nga shumë probleme.
Si analizoni dhe optimizoni kërkesat në bazat e të dhënave?
Burimi: habr.com
