Kungani udinga usekelo lwezinsimbi ukuze uthole ukuhlukaniswa kokhiye?

Sanibonani nonke! Ngingunjiniyela ongemuva obhala ama-microservices ku-Java + Spring. Ngisebenza kwelinye lamaqembu okuthuthukisa umkhiqizo wangaphakathi e-Tinkoff.

Eqenjini lethu, umbuzo wokuthuthukisa imibuzo ku-DBMS uvame ukuvela. Ngaso sonke isikhathi ufuna ukushesha kancane, kodwa awukwazi njalo ukudlula ngezinkomba ezakhelwe kahle—kufanele ubheke ezinye izindlela zokulungisa. Ngesinye salokhu kuzulazula kuwebhu ngifuna ukulungiselelwa okuphusile lapho ngisebenza nesizindalwazi, ngithole Ibhulogi ka-Marcus Wynand ewusizo ngokungapheli, umbhali we-SQL Performance Explained. Lolu yilolo hlobo lwebhulogi olungavamile lapho ungafunda khona zonke izindatshana zilandelana.

Ngingathanda ukuhumushela isihloko esifushane sika-Marcus. Ingabizwa ngezinga elithile ngokuthi i-manifesto efuna ukudonsela ukunaka komdala, kodwa inkinga efanelekile yokusebenza komsebenzi we-offset ngokwezinga le-SQL.

Kwezinye izindawo ngizongezelela umbhali ngezincazelo nokuphawula. Ngizobhekisela kuzo zonke izindawo ezinjalo ngokuthi “approx.” ukuze uthole ukucaca okwengeziwe

Isingeniso esincane

Ngicabanga ukuthi abantu abaningi bayazi ukuthi kuyinkinga futhi kuhamba kancane kangakanani ukusebenza ngekhasi elikhethiwe nge-offset. Ubuwazi ukuthi ingashintshwa kalula ngomklamo osebenza kahle kakhudlwana?

Ngakho-ke, igama elingukhiye le-offset litshela isizindalwazi ukuthi seqe amarekhodi n wokuqala esicelweni. Nokho, isizindalwazi sisadinga ukufunda lawa marekhodi n wokuqala kudiski, ngendlela enikeziwe (inothi: sebenzisa ukuhlunga uma kucacisiwe), futhi yilapho kuphela lapho kuyokwenzeka khona ukubuyisela amarekhodi kusukela ku-n+1 kuye phambili. Okuthakazelisa kakhulu ukuthi inkinga ayikho ekusetshenzisweni okuqondile ku-DBMS, kodwa encazelweni yokuqala ngokuya ngokwendinganiso:

…imigqa ihlelwa kuqala ngokwe- bese ikhawulelwa ngokulahla inani lemigqa eshiwo ku- kusukela ekuqaleni…
-SQL:2016, Ingxenye 2, 4.15.3 Amathebula asuselwe (inothi: okwamanje okuyindinganiso esetshenziswa kakhulu)

Iphuzu elibalulekile lapha ukuthi i-offset ithatha ipharamitha eyodwa - inani lamarekhodi okufanele weqe, futhi yilokho. Ukulandela le ncazelo, i-DBMS ingakwazi ukubuyisa wonke amarekhodi bese ilahla lawo angadingekile. Ngokusobala, le ncazelo ye-offset isiphoqa ukuthi senze umsebenzi owengeziwe. Futhi akusho lutho noma ngabe i-SQL noma i-NoSQL.

Ubuhlungu obuthe xaxa

Izinkinga nge-offset azigcini lapho, futhi nakhu ukuthi kungani. Uma, phakathi kokufunda amakhasi amabili edatha kudiski, omunye umsebenzi ufaka irekhodi elisha, kuzokwenzekani kuleli cala?

Uma i-offset isetshenziselwa ukweqa amarekhodi asuka emakhasini adlule, esimweni sokwengeza irekhodi elisha phakathi kokufundwa kwamakhasi ahlukene, cishe uzothola okuphindiwe (qaphela: lokhu kungenzeka uma sifunda ikhasi nekhasi sisebenzisa i-oda ngokwakha, bese phakathi kokukhipha kwethu kungase kungene okusha).

Umfanekiso ubonisa ngokucacile lesi simo. Isisekelo sifunda amarekhodi okuqala angu-10, ngemva kwalokho kufakwa irekhodi elisha, elisusa wonke amarekhodi afundiwe ngo-1. Khona-ke isisekelo sithatha ikhasi elisha kumarekhodi angu-10 alandelayo futhi asiqali kusukela ku-11, njengoba kufanele, kodwa kusukela ku-10. XNUMX, ukuphinda leli rekhodi. Kukhona okunye okudidayo okuhlotshaniswa nokusetshenziswa kwale nkulumo, kodwa lokhu kuvame kakhulu.

Njengoba sesivele sitholile, lezi akuzona izinkinga ze-DBMS ethile noma ukusetshenziswa kwayo. Inkinga isekuchazeni ukuqamba ngokwezinga le-SQL. Sitshela i-DBMS ukuthi iliphi ikhasi okufanele lilande noma ukuthi mangaki amarekhodi okufanele weqe. Isizindalwazi asikwazi nje ukukhulisa isicelo esinjalo, njengoba kunolwazi oluncane kakhulu lwalokhu.

Kuyafaneleka futhi ukucacisa ukuthi lokhu akuyona inkinga ngegama elingukhiye elithile, kodwa nge-semantics yombuzo. Kunama-syntaxes amaningana afanayo ngesimo sawo esiyinkinga:

• Igama elingukhiye le-offset kushiwo ngaphambili.
• Ukwakhiwa komkhawulo wamagama angukhiye amabili [offset] (yize umkhawulo wona ungemubi kangako).
• Ukuhlunga ngemingcele ephansi, ngokusekelwe ekubaleni izinombolo zemigqa (isibonelo, inombolo_yemigqa(), inombolo yomugqa, njll.).

Zonke lezi zinkulumo zivele zikutshele ukuthi mingaki imigqa okufanele yeqe, alukho ulwazi olwengeziwe noma umongo.

Kamuva kulesi sihloko, igama elingukhiye le-offset lisetshenziswa njengesifinyezo sazo zonke lezi zinketho.

Impilo ngaphandle kwe-OFFSET

Manje ake sicabange ukuthi umhlaba wethu ubuyoba njani ngaphandle kwazo zonke lezi zinkinga. Kuvela ukuthi impilo ngaphandle kwe-offset akunzima kangako: ngokukhetha, ungakhetha kuphela leyo migqa esingakayiboni (inothi: okungukuthi, leyo ebingekho ekhasini eledlule), usebenzisa isimo lapho.

Kulokhu, siqala eqinisweni lokuthi okukhethiwe kwenziwa kusethi eyaliwe (ukuhleleka okuhle kwakudala nge). Njengoba sinesethi e-oda, singasebenzisa isihlungi esilula ukuze sithole kuphela idatha engemuva kwerekhodi lokugcina lekhasi langaphambilini:

    SELECT ...
    FROM ...
    WHERE ...
    AND id < ?last_seen_id
    ORDER BY id DESC
    FETCH FIRST 10 ROWS ONLY

Yilokho umgomo wonke wale ndlela. Yebo, izinto ziba mnandi kakhulu lapho zihlungwa ngamakholomu amaningi, kodwa umbono usafana. Kubalulekile ukuqaphela ukuthi lo mklamo usebenza kwabaningi NoSQL-izinqumo.

Le ndlela ibizwa ngokuthi i-search method noma i-keyset pagination. Ixazulula inkinga yemiphumela entantayo (inothi: isimo ngokubhala phakathi kwekhasi elifundiwe esichazwe ngaphambili) futhi, yiqiniso, esikuthandayo sonke, kusebenza ngokushesha futhi kuzinzile kune-offset yakudala. Ukuzinza kulele eqinisweni lokuthi isikhathi sokucubungula isicelo asikhuphuki ngokulingana nenani letafula eliceliwe (qaphela: uma ufuna ukufunda okwengeziwe ngomsebenzi wezindlela ezahlukene zokudweba, ungakwazi bheka isethulo sombhali. Ungathola futhi amabhentshimakhi wokuqhathanisa wezindlela ezahlukene lapho).

Esinye sezilayidi ukhuluma ngalokhoukuthi ukuqanjwa ngokhiye, vele, akunamandla onke - kunemikhawulo yakho. Okubaluleke kakhulu ukuthi akanalo ikhono lokufunda amakhasi angahleliwe (inothi: ngokungahambisani). Kodwa-ke, ngenkathi yokuskrola okungapheli (inothi: ekuqaleni), akuyona inkinga enjalo. Ukucacisa inombolo yekhasi yokuchofoza kuyisinqumo esibi ekwakhiweni kwe-UI noma kunjalo (inothi: umbono wombhali we-athikili).

Kuthiwani ngamathuluzi?

Ukufakwa kwekhasi kokhiye ngokuvamile akufanelekile ngenxa yokushoda kwezinsimbi zale ndlela. Amathuluzi amaningi okuthuthukisa, okuhlanganisa nezinhlaka ezihlukahlukene, awakuvumeli ukuthi ukhethe kahle ukuthi ukuphequlula kuzokwenziwa kanjani.

Isimo sibhebhethekiswa iqiniso lokuthi indlela echaziwe idinga ukusekelwa kokuphela kuye ekupheleni kubuchwepheshe obusetshenziswa - kusukela ku-DBMS kuya ekufezeni isicelo se-AJAX esipheqululini ngokuskrola okungapheli. Esikhundleni sokucacisa inombolo yekhasi kuphela, manje kufanele ucacise isethi yokhiye kuwo wonke amakhasi ngesikhathi esisodwa.

Kodwa-ke, inani lezinhlaka ezisekela ukwenziwa kwekhasi kokhiye liyakhula kancane kancane. Nakhu esinakho okwamanje:

• jOOQ okweJava;
• oda_umbuzo ngoRuby;
• i-chunkator и I-Django Infinite Scroll Pagination kwe-Django;
• Isethi ye-SQL Alchemy sqlakeyset okwePython;
• ukuvutha - ukuphikelela - I-API yemibandela yokusetshenziswa kwe-JPA;
• I-DBIx::Ikilasi::I-Wrapper ngoba Perl;
• I-Massive.js, imephu ye-Node.js I-Keyset Documentation.

(Qaphela: ezinye izixhumanisi zisusiwe ngenxa yokuthi ngesikhathi sokuhumusha amanye amalabhulali ayengakabuyekezwa kusukela ngo-2017-2018. Uma uthanda, ungabheka umthombo wangempela.)

Kungalesi sikhathi lapho usizo lwakho ludingeka khona. Uma uthuthukisa noma usekela uhlaka olusebenzisa ukuqanjwa kwamagama, ngiyakucela, ngiyakunxusa, ngiyakuncenga ukuthi unikeze ukwesekwa komdabu kokuqanjwa kokhiye. Uma unemibuzo noma udinga usizo, ngingakujabulela ukukusiza (inkundla, Twitter, ifomu lokuxhumana) (inothi: kokuhlangenwe nakho kwami ​​no-Marcus, ngingasho ukuthi ushisekela ngempela ukusabalalisa lesi sihloko).

Uma usebenzisa izixazululo ezenziwe ngomumo ocabanga ukuthi zifanele ukwesekwa kokufakwa kwamagama ngokhiye, dala isicelo noma unikeze nesixazululo esenziwe ngomumo, uma kungenzeka. Ungakwazi futhi ukuxhuma lesi sihloko.

isiphetho

Isizathu sokuthi kungani indlela elula futhi ewusizo njengokuhlukanisa ngokhiye ingasakazeki akukhona ukuthi kunzima ukuyisebenzisa ngobuchwepheshe noma kudinga umzamo omkhulu. Isizathu esiyinhloko ukuthi abaningi bajwayele ukubona nokusebenza nge-offset - le ndlela iqondiswa yizinga ngokwalo.

Ngenxa yalokho, bambalwa abantu abacabanga ngokushintsha indlela yokwenza i-pagination, futhi ngenxa yalokhu, ukusekelwa kwezinsimbi okuvela kuzinhlaka nemitapo yolwazi kukhula kabi. Ngakho-ke, uma umbono kanye nenhloso ye-offset-free pagination iseduze nawe, siza ukusabalalisa!

Source: https://use-the-index-luke.com/no-offset
Umbhali: Markus Winand

Source: www.habr.com