KanĂ« kaluar kohĂ«t kur nuk ishte e nevojshme tĂ« shqetĂ«soheshe pĂ«r optimizimin e performancĂ«s sĂ« bazave tĂ« tĂ« dhĂ«nave. Koha ecĂ«n pĂ«rpara. Ădo sipĂ«rmarrĂ«s i ri nĂ« fushĂ«n e teknologjisĂ« synon tĂ« krijojĂ« Facebook-un e radhĂ«s, duke u pĂ«rpjekur njĂ«kohĂ«sisht tĂ« mbledhĂ« tĂ« gjitha tĂ« dhĂ«nat qĂ« mund tĂ« arrijĂ«. KĂ«to tĂ« dhĂ«na i duhen biznesit pĂ«r tĂ« trajnuar mĂ« mirĂ« modelet qĂ« ndihmojnĂ« nĂ« gjenerimin e tĂ« ardhurave. NĂ« kĂ«to kushte, programuesit duhet tĂ« ndĂ«rtojnĂ« API qĂ« mundĂ«sojnĂ« punĂ« tĂ« shpejtĂ« dhe tĂ« besueshme me vĂ«llime shumĂ« tĂ« mĂ«dha informacioni.
Nëse prej disa kohësh merreni me projektimin e pjesës server-side të aplikacioneve ose të bazave të të dhënave, me shumë gjasë keni shkruar kod për ekzekutimin e pyetjeve me paginim. Për shembull, diçka e tillë:
SELECT * FROM table_name LIMIT 10 OFFSET 40
Duket në rregull, apo jo?
Por nëse e keni zbatuar paginimin pikërisht në këtë mënyrë, me keqardhje duhet të them se nuk e keni bërë aspak në mënyrën më efikase.
Doni të më kundërshtoni? . , dhe tashmë përdorin qasjet për të cilat dua të flas sot.
PĂ«rmendni tĂ« paktĂ«n njĂ« zhvillues backend-i qĂ« nuk ka pĂ«rdorur kurrĂ« OFFSET dhe KUFIZO pĂ«r tĂ« ekzekutuar pyetje me paginim. NĂ« njĂ« MVP (Minimum Viable Product, produkt minimal i zbatueshĂ«m) dhe nĂ« projekte ku pĂ«rdoren vĂ«llime tĂ« vogla tĂ« dhĂ«nash, kjo qasje Ă«shtĂ« plotĂ«sisht e pranueshme. Mund tĂ« thuhet se âthjesht funksiononâ.
Por nëse duhet të ndërtohen nga e para sisteme të besueshme dhe efikase, ia vlen të mendoni që herët për efikasitetin e ekzekutimit të pyetjeve ndaj bazave të të dhënave që përdoren në sisteme të tilla.
Sot do të flasim për problemet që shoqërojnë zbatimet e përdorura gjerësisht (fatkeqësisht kështu është) të mekanizmave për ekzekutimin e pyetjeve me paginim dhe për mënyrën se si të arrihet performancë e lartë gjatë ekzekutimit të pyetjeve të tilla.
ĂfarĂ« nuk shkon me OFFSET dhe LIMIT?
Siç u përmend më sipër, OFFSET dhe KUFIZO japin rezultate të shkëlqyera në projekte ku nuk ka nevojë të punohet me vëllime të mëdha të dhënash.
Problemi lind kur baza e të dhënave rritet deri në atë pikë sa nuk futet më në memorien e serverit. Ndërkohë, gjatë punës me këtë bazë të të dhënave, duhet të përdoren edhe pyetje me paginim.
Që ky problem të shfaqet, duhet të krijohet një situatë në të cilën DBMS përdor një operacion joefikas të skanimit të plotë të tabelës (Full Table Scan) gjatë ekzekutimit të çdo kërkese me ndarje në faqe, ndërkohë që mund të ndodhin edhe 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 sekuencial i tabelĂ«s», Sequential Scan)? Kjo Ă«shtĂ« njĂ« operacion gjatĂ« tĂ« cilit DBMS lexon nĂ« mĂ«nyrĂ« tĂ« njĂ«pasnjĂ«shme çdo rresht tĂ« tabelĂ«s, pra tĂ« dhĂ«nat qĂ« pĂ«rmban ajo, dhe i kontrollon ato pĂ«r pĂ«rputhje me kushtin e caktuar. Dihet se ky lloj skanimi tabele Ă«shtĂ« mĂ« i ngadalshmi. Arsyeja Ă«shtĂ« se gjatĂ« tij kryhen shumĂ« operacione hyrje/dalje qĂ« ngarkojnĂ« nĂ«nsistemin e diskut tĂ« serverit. SituatĂ«n e pĂ«rkeqĂ«sojnĂ« vonesat qĂ« shoqĂ«rojnĂ« punĂ«n me tĂ« dhĂ«nat e ruajtura nĂ« disqe, si edhe fakti qĂ« transferimi i tĂ« dhĂ«nave nga disku nĂ« memorie Ă«shtĂ« njĂ« operacion qĂ« kĂ«rkon shumĂ« burime.
Për shembull, keni regjistrime për 100000000 përdorues dhe ekzekutoni një kërkesë me konstruktin OFFSET 50000000. Kjo do të thotë se DBMS do të duhet t'i ngarkojë të gjitha këto regjistrime (ndonëse as nuk na duhen!), t'i vendosë në memorie dhe vetëm pas kësaj të marrë, të themi, 20 rezultate, për të cilat flitet te KUFIZO.
Për shembull, kjo mund të duket kështu: «zgjidh rreshtat nga 50000 deri në 50020 nga 100000». Pra, që sistemi ta ekzekutojë kërkesën, fillimisht do t'i duhet të ngarkojë 50000 rreshta. E shihni sa shumë punë të panevojshme do t'i duhet të bëjë?
NĂ«se nuk besoni, hidhini njĂ« sy shembullit qĂ« kam krijuar duke pĂ«rdorur mundĂ«sitĂ« e .Â
Shembull në db-fiddle.com
Atje, majtas, në fushën Schema SQL, ndodhet kodi që fut në bazën e të dhënave 100000 rreshta, ndërsa djathtas, në fushën Query SQL, shfaqen dy kërkesa. E para, e ngadalta, duket kështu:
SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;
Ndërsa e dyta, që përfaqëson një zgjidhje efikase për të njëjtën detyrë, është kështu:
SELECT *
FROM `docs`
WHERE id > 85000
LIMIT 10;
PĂ«r t'i ekzekutuar kĂ«to kĂ«rkesa, mjafton tĂ« shtypni butonin Ekzekuto nĂ« pjesĂ«n e sipĂ«rme tĂ« faqes. Pasi ta bĂ«jmĂ« kĂ«tĂ«, le tĂ« krahasojmĂ« informacionin pĂ«r kohĂ«n e ekzekutimit tĂ« kĂ«rkesave. Rezulton se ekzekutimi i njĂ« kĂ«rkese joefikase kĂ«rkon tĂ« paktĂ«n 30 herĂ« mĂ« shumĂ« kohĂ« sesa ekzekutimi i sĂ« dytĂ«s (nga njĂ« nisje nĂ« tjetrĂ«n kjo kohĂ« ndryshon; pĂ«r shembull, sistemi mund tĂ« raportojĂ« se pĂ«r ekzekutimin e kĂ«rkesĂ«s sĂ« parĂ« u deshĂ«n 37 ms, ndĂ«rsa pĂ«r tĂ« dytĂ«n â 1 ms).
Dhe nëse të dhënat do të jenë më shumë, gjithçka do të duket edhe më keq (për t'u bindur për këtë, hidhni një sy te materiali im me 10 milionë rreshta).
Ajo që sapo diskutuam duhet t'ju japë një kuptim të caktuar se si, në të vërtetë, përpunohen kërkesat ndaj bazave të të dhënave.
Kini parasysh se sa mĂ« e madhe tĂ« jetĂ« vlera e OFFSET â aq mĂ« gjatĂ« do tĂ« ekzekutohet kĂ«rkesa.
ĂfarĂ« duhet pĂ«rdorur nĂ« vend tĂ« kombinimit OFFSET dhe LIMIT?
Në vend të kombinimit OFFSET dhe KUFIZO duhet përdorur një konstrukt i ndërtuar sipas kësaj skeme:
SELECT * FROM table_name WHERE id > 10 LIMIT 20
Ky është ekzekutim i kërkesës me faqezuar të bazuar në kursor (Cursor based pagination).
Në vend që të ruhen lokalisht vlerat aktuale të OFFSET dhe KUFIZO dhe të dërgohen me çdo kërkesë, duhet të ruhet çelësi primar i fundit i marrë (zakonisht është ID) dhe KUFIZO, dhe si rezultat do të merren kërkesa të ngjashme me atë të mësipërmen.
Pse? Sepse, duke treguar në mënyrë të drejtpërdrejtë identifikuesin e rreshtit të fundit të lexuar, i tregoni SGBD-së suaj se nga ku duhet të fillojë kërkimin e të dhënave të nevojshme. Për më tepër, falë përdorimit të çelësit, kërkimi do të kryhet në mënyrë efikase dhe sistemi nuk do të shpërqendrohet nga rreshtat që ndodhen jashtë intervalit të përcaktuar.
Le të shohim krahasimin e mëposhtëm të performancës së kërkesave të ndryshme. Ja një kërkesë joefikase.
Kërkesë e ngadaltë
Ndërsa kjo është versioni i optimizuar i kësaj kërkese.
Kërkesë e shpejtë
TĂ« dyja kĂ«rkesat kthejnĂ« saktĂ«sisht tĂ« njĂ«jtin volum tĂ« dhĂ«nash. Por e para kĂ«rkon 12,80 sekonda pĂ«r t'u ekzekutuar, ndĂ«rsa e dyta â 0,01 sekonda. E ndieni diferencĂ«n?
Probleme të mundshme
Për të siguruar funksionim efikas të metodës së propozuar për ekzekutimin e kërkesave, tabela duhet të përmbajë një kolonë (ose kolona) me indekse unike të renditura në mënyrë të njëpasnjëshme, si p.sh. një identifikues numerik. Në disa raste specifike, kjo mund të jetë vendimtare për suksesin e përdorimit të këtyre lloj kërkesave për të rritur shpejtësinë e punës me bazën e të dhënave.
Natyrisht, gjatĂ« ndĂ«rtimit tĂ« kĂ«rkesave duhet tĂ« merren parasysh veçoritĂ« e arkitekturĂ«s sĂ« tabelave dhe tĂ« zgjidhen mekanizmat qĂ« japin rezultatet mĂ« tĂ« mira nĂ« tabelat ekzistuese. PĂ«r shembull, nĂ«se nĂ« kĂ«rkesa duhet tĂ« punoni me vĂ«llime tĂ« mĂ«dha tĂ« dhĂ«nash tĂ« ndĂ«rlidhura, mund tâju duket interesante artikull.
Nëse përballemi me problemin e mungesës së një çelësi parësor, për shembull kur kemi një tabelë me marrëdhënie «shumë-me-shumë», atëherë qasja tradicionale, që parashikon përdorimin e OFFSET dhe KUFIZO, do të na përshtatet me siguri. Por përdorimi i saj mund të çojë në ekzekutimin e kërkesave potencialisht të ngadalta. Në raste të tilla, do të rekomandoja përdorimin e një çelësi parësor me auto-increment, edhe nëse nevojitet vetëm për organizimin e ekzekutimit të kërkesave me ndarje në faqe.
NĂ«se ju intereson kjo temĂ« â , dhe â disa materiale tĂ« dobishme.
PĂ«rfundime
Përfundimi kryesor që mund të nxjerrim është se, pavarësisht madhësisë së bazës së të dhënave, duhet analizuar gjithmonë shpejtësia e ekzekutimit të kërkesave. Në kohën tonë, shkallëzueshmëria e zgjidhjeve është jashtëzakonisht e rëndësishme dhe, nëse që në fillim të punës mbi një sistem gjithçka projektohet siç duhet, kjo në të ardhmen mund ta kursejë zhvilluesin nga shumë probleme.
Si i analizoni dhe i optimizoni kërkesat ndaj bazave të të dhënave?
Burimi: habr.com
