Molweni nonke! Ndingumphuhlisi osemva obhala ii-microservices kwiJava + Spring. Ndisebenza kwelinye lamaqela ophuhliso lwangaphakathi eTinkoff.
Kwiqela lethu, umbuzo wokwenza ngcono imibuzo kwi-DBMS uhlala uvela. Uhlala ufuna ukukhawuleza kancinci, kodwa awukwazi ukusoloko uphumelela ngezalathisi ezakhelwe ngocoselelo-kuya kufuneka ujonge ezinye iindlela zokusebenza. Ngexesha lokubhadula kwiwebhu kukhangelwa ukulungiswa okufanelekileyo xa usebenza nogcino-lwazi, ndifumene , umbhali we SQL Performance Explained. Olu lolo hlobo lunqabileyo lwebhlog apho unokufunda khona onke amanqaku ngokulandelelana.
Ndingathanda ukuguqulela inqaku elifutshane likaMarcus ukwenzela wena. Inokuthiwa kumlinganiselo othile i-manifesto efuna ukutsala ingqalelo endala, kodwa ingxaki echaphazelekayo yokusebenza komsebenzi we-offset ngokomgangatho we-SQL.
Kwezinye iindawo ndiya kongezelela umbhali ngeengcaciso kunye nezimvo. Ndiza kubhekisa kuzo zonke iindawo ezinje ngokuthi “malunga.” ukuze kucace ngakumbi
Intshayelelo emfutshane
Ndicinga ukuba abantu abaninzi bayayazi ukuba yingxaki kwaye icotha kangakanani ukusebenza ngephepha elikhethiweyo nge-offset. Ubusazi ukuba inokutshintshwa ngokulula ngoyilo olusebenza ngakumbi?
Ke, igama elingundoqo le-offset lixelela isiseko sedatha ukuba sitsibe iirekhodi zokuqala ze-n kwisicelo. Nangona kunjalo, i-database isafuna ukufunda ezi rekhodi zokuqala kwi-disk, ngokomyalelo onikiweyo (qaphela: sebenzisa ukuhlelwa ukuba kucacisiwe), kwaye kuphela kuya kuba nako ukubuyisela iirekhodi ukusuka ku-n + 1 ukuya phambili. Eyona nto inomdla kakhulu kukuba ingxaki ayikho ekuphunyezweni okuthe ngqo kwi-DBMS, kodwa kwinkcazo yokuqala ngokomgangatho:
…imiqolo ihlelwa ngokwe kwaye emva koko kunqunyelwe ngokulahla inani lemiqolo ekhankanyiweyo kwi ukusukela ekuqaleni...
-SQL:2016, Part 2, 4.15.3 Derived tables (qaphela: ngoku eyona migangatho isetyenziswayo)
Inqaku eliphambili apha kukuba i-offset ithatha iparameter enye - inani leerekhodi ukutsiba, kwaye yiloo nto. Ukulandela le nkcazo, i-DBMS inokubuyisela kuphela zonke iirekhodi kwaye ilahle ezo zingeyomfuneko. Ngokucacileyo, ukuchaza i-offset ngale ndlela kubangela umsebenzi ongeyomfuneko. Kwaye akukhathaliseki nokuba yiSQL okanye iNoSQL.
Intlungu nje encinci
Iingxaki nge-offset azipheli apho, kwaye nantsi isizathu. Ukuba, phakathi kokufunda amaphepha amabini edatha kwidiski, omnye umsebenzi ufaka irekhodi entsha, kuya kwenzeka ntoni kule meko?
Xa i-offset isetyenziselwa ukutsiba iirekhodi kumaphepha angaphambili, kwimeko yokongeza irekhodi entsha phakathi kokufundwa kwamaphepha ahlukeneyo, uya kufumana okuphindiweyo (qaphela: oku kunokwenzeka xa sifunda iphepha ngephepha sisebenzisa iodolo ngokwakha, emva koko embindini wemveliso yethu inokufumana ungeno olutsha).
Umfanekiso ubonisa ngokucacileyo le meko. Isiseko sifunda iirekhodi ze-10 zokuqala, emva koko irekhodi elitsha lifakwe, elikhupha zonke iirekhodi ezifundwayo nge-1. Emva koko isiseko sithatha iphepha elitsha kwiirekhodi ze-10 ezilandelayo kwaye ziqala kungekhona kwi-11, njengoko kufanelekile, kodwa ukusuka kwi-10. XNUMX, ukuphinda le rekhodi. Kukho ezinye izinto ezingaqhelekanga ezinxulumene nokusetyenziswa kweli binzana, kodwa le yeyona nto ixhaphakileyo.
Njengoko sele sifumene, ezi azikho iingxaki ze-DBMS ethile okanye ukuphunyezwa kwayo. Ingxaki isekuchazeni i-pagination ngokomgangatho we-SQL. Sixelela i-DBMS ukuba leliphi iphepha emazilande okanye zingaphi iirekhodi ekufuneka uzitsibe. Uvimba weenkcukacha awunako ukwenza isicelo esinjalo, kuba kukho ulwazi oluncinane kakhulu lwale nto.
Kukwafanelekile ukucacisa ukuba oku akuyongxaki ngegama elithile elingundoqo, kodwa kunesemantics yombuzo. Kukho ezinye iisintaksi ezininzi ezifanayo kwimeko yazo eyingxaki:
- Igama elingundoqo le-offset lichazwe ngaphambili.
- Ulwakhiwo lwamagama angundoqo amabini umda [offset] (nangona umda ngokwawo ungembi kangako).
- Ukuhluza ngemida esezantsi, ngokusekwe kumanani omqolo (umzekelo, inombolo yomqolo_yenombolo (), i-rownum, njl. njl.).
Zonke ezi ntetho zikuxelela ngokulula ukuba mingaphi na imigca ekufuneka uyitsibe, akukho lwazi olongezelelweyo okanye umxholo.
Kamva kweli nqaku, igama elingundoqo le-offset lisetyenziswa njengesishwankathelo sazo zonke ezi zikhetho.
Ubomi ngaphandle kwe-OFFSET
Ngoku makhe sicinge ukuba ihlabathi lethu belinokuba njani ngaphandle kwazo zonke ezi ngxaki. Kuyavela ukuba ubomi ngaphandle kwe-offset abukho nzima kakhulu: ngokukhetha, ungakhetha kuphela loo miqolo esingekayiboni (qaphela: oko kukuthi, abo babengekho kwiphepha elidlulileyo), usebenzisa imeko apho.
Kule meko, siqala kwinto yokuba ukhetho lwenziwa kwisethi eyalelweyo (i-oda elidala elihle nge). Kuba sineseti emiselweyo, sinokusebenzisa isihluzo esilula ukufumana kuphela idatha esemva kwerekhodi yokugqibela yephepha elidlulileyo:
SELECT ...
FROM ...
WHERE ...
AND id < ?last_seen_id
ORDER BY id DESC
FETCH FIRST 10 ROWS ONLYLo ngumgaqo uphela wale ndlela. Ewe kunjalo, izinto ziba mnandi ngakumbi xa uhlela ngeekholamu ezininzi, kodwa umbono usafana. Kubalulekile ukuqaphela ukuba olu yilo lusebenza kwabaninzi -izigqibo.
Le ndlela ibizwa ngokuba yindlela yokufuna okanye i-keyset pagination. Isombulula ingxaki yesiphumo esidadayo (qaphela: imeko ngokubhala phakathi kwephepha elifundwayo elichazwe ngaphambili) kwaye, ngokuqinisekileyo, into esiyithandayo sonke, isebenza ngokukhawuleza kwaye izinzile kune-offset yakudala. Uzinzo lulele kwinto yokuba ixesha lokwenziwa kwesicelo alinyuki ngokwenani letheyibhile eceliweyo (qaphela: ukuba ufuna ukufunda ngakumbi ngomsebenzi weendlela ezahlukeneyo zokwenziwa kwamagama, unga . Unokufumana kwakhona amanqaku othelekiso kwiindlela ezahlukeneyo apho).
Esinye sezilayidi ukuba ubuhedeni ngezitshixo, kunjalo, ayinamandla onke - inemida yayo. Eyona nto ibalulekileyo kukuba akakwazi ukufunda amaphepha angaqhelekanga (qaphela: ngokungangqinelani). Nangona kunjalo, kwixesha lokuskrola okungapheliyo (qaphela: kwisiphelo sangaphambili), oku akuyona ingxaki enjalo. Ukuchaza inombolo yephepha ngokucofa sisigqibo esibi kuyilo lwe-UI nakanjani (qaphela: uluvo lombhali wenqaku).
Kuthekani ngezixhobo?
I-Pagination kwizitshixo isoloko ingafanelekanga ngenxa yokungabikho kwenkxaso yezixhobo zale ndlela. Uninzi lwezixhobo zophuhliso, kubandakanywa nezakhelo ezahlukeneyo, azikuvumeli ukuba ukhethe ngokuthe ngqo ukuba i-pagination izakwenziwa njani.
Imeko iyancipha kukuba indlela echazwe ngayo ifuna inkxaso yokuphela-ukuphela kwiiteknoloji ezisetyenzisiweyo - ukusuka kwi-DBMS ukuya ekuphunyezweni kwesicelo se-AJAX kwisikhangeli kunye nokuskrola okungapheliyo. Endaweni yokuchaza inombolo yephepha kuphela, ngoku kufuneka uchaze uluhlu lwezitshixo zawo onke amaphepha ngaxeshanye.
Nangona kunjalo, inani lezakhelo ezixhasa i-pagination kwizitshixo zikhula ngokuthe ngcembe. Nantsi into esinayo okwangoku:
- yeJava;
- yeRuby;
- и ye-Django;
- yePython;
- - I-API yomgaqo-nkqubo wokuphunyezwa kweJPA;
- yePerl;
- , imapper yeNode.js .
(Qaphela: amanye amakhonkco asusiwe ngenxa yokuba ngexesha loguqulelo amanye amathala eencwadi awazange ahlaziywe ukususela ngo-2017-2018. Ukuba unomdla, ungajonga kumthombo wokuqala.)
Kungalo mzuzu uncedo lwakho luyafuneka. Ukuba uphuhlisa okanye uxhasa isakhelo esenza naluphi na ukusetyenziswa kobuhedeni, ke ndiyabuza, ndiyabongoza, ndiyakubongoza ukuba unikeze inkxaso yemveli yokwenziwa kobuhedeni kwizitshixo. Ukuba unemibuzo okanye ufuna uncedo, ndingakuvuyela ukukunceda (, , ) (qaphela: kumava am noMarcus, ndingatsho ukuba unomdla wokusasaza esi sihloko).
Ukuba usebenzisa izisombululo esele zilungisiwe ocinga ukuba zifanelekile ukuba ube nenkxaso ye-pagination ngezitshixo, yenza isicelo okanye unike isisombululo esele senziwe, ukuba kunokwenzeka. Unako kwakhona ikhonkco kweli nqaku.
isiphelo
Isizathu sokuba kutheni le ndlela ilula kwaye iluncedo njenge-pagination ngezitshixo ayisasazwanga kukuba kunzima ukuyiphumeza ngokobuchwephesha okanye ifuna nayiphi na inzame enkulu. Isizathu esona sizathu kukuba abaninzi bajwayele ukubona kunye nokusebenza kunye ne-offset - le ndlela ichazwe ngumgangatho ngokwawo.
Ngenxa yoko, abantu abambalwa bacinga malunga nokutshintsha indlela yokwenza i-pagination, kwaye ngenxa yoko, inkxaso yezixhobo ezivela kwizicwangciso kunye namathala eencwadi aphuhliswa kakubi. Ke ngoko, ukuba umbono kunye nenjongo ye-offset-free pagination isondele kuwe, nceda usasaze!
umthombo:
Umbhali: uMarkus Winand
umthombo: www.habr.com
