Zimkile iintsuku apho ungakhange ube nexhala malunga nokuphucula ukusebenza kwesiseko sedatha. Ixesha alimi ngxi. Wonke usomashishini omtsha we-tech ufuna ukwenza i-Facebook elandelayo, ngelixa uzama ukuqokelela yonke idatha abanokuyifumana ezandleni zabo. Amashishini adinga le datha ukuze aqeqeshe imodeli engcono ebanceda benze imali. Kwiimeko ezinjalo, abadwelisi benkqubo kufuneka benze ii-APIs ezibavumela ukuba basebenze ngokukhawuleza nangokuthembekileyo ngenani elikhulu lolwazi.
Ukuba ubuyila usetyenziso okanye i-backends yedatha kulo naliphi na ubude bexesha, mhlawumbi ubhale ikhowudi yokuqhuba imibuzo enephepha. Umzekelo, njengale:
SELECT * FROM table_name LIMIT 10 OFFSET 40
Indlela eyiyo?
Kodwa ukuba le yindlela owenze ngayo ubuhedeni bakho, ndiyaxolisa ukuthi awuyenzanga ngeyona ndlela isebenzayo.
Uyafuna ukundiphikisa? . , ΠΈ Sele besebenzisa iindlela endifuna ukuthetha ngazo namhlanje.
Xela nokuba mnye umphuhlisi ongasemva ongazange asebenzise OFFSET ΠΈ LIMIT ukwenza imibuzo yamaphepha. Kwi-MVP (Ubuncinane beMveliso eSebenzayo) nakwiiprojekthi apho amanani amancinane edatha asetyenziswayo, le ndlela iyasebenza kakhulu. βIsebenza nje,β ngokungathi kunjalo.
Kodwa ukuba ufuna ukudala iinkqubo ezithembekileyo nezisebenzayo ukusuka ekuqaleni, kufuneka unakekele kwangaphambili malunga nokusebenza kakuhle kokubuza iinkcukacha ezisetyenziswa kwiinkqubo ezinjalo.
Namhlanje siza kuthetha ngeengxaki ezisetyenziswa ngokuqhelekileyo (ezimbi kakhulu) ukuphunyezwa kweenjini zombuzo ezinamaphepha, kunye nendlela yokuphumeza ukusebenza okuphezulu xa uqhuba imibuzo enjalo.
Yintoni engalunganga nge-OFFSET kunye ne-LIMIT?
Njengoko sele kutshiwo, OFFSET ΠΈ LIMIT Baqhuba kakuhle kwiiprojekthi ezingadingi ukusebenza ngeemali ezininzi zedatha.
Ingxaki ivela xa i-database ikhula ukuya kubukhulu obunjalo ukuba ayisangeni kwimemori yomncedisi. Nangona kunjalo, xa usebenza nale database, kufuneka usebenzise imibuzo enephepha.
Ukuze le ngxaki ibonakale, kufuneka kubekho imeko apho i-DBMS ibhenela kwi-Full Table Scan engasebenziyo kumbuzo ngamnye owenziwe ngamaphepha (ngelixa ukufakwa kunye nokucima imisebenzi inokwenzeka, kwaye asiyidingi idatha yakudala!).
Yintoni "ukuskena kwetafile epheleleyo" (okanye "ukuskena kwetafile ngokulandelelanayo", Ukuskena ngokulandelelanayo)? Lo ngumsebenzi apho i-DBMS ifunda ngokulandelelana umqolo ngamnye wetafile, oko kukuthi, idatha equlethwe kuyo, kwaye ihlolisise ukuthotyelwa kwemeko ethile. Olu hlobo lokuskena kwetafile lwaziwa njengolona lucothayo. Inyani yeyokuba xa isenziwa, imisebenzi emininzi yegalelo/yemveliso iyenziwa ebandakanya inkqubo esezantsi yediski yomncedisi. Imeko yenziwa ibe yimbi ngakumbi yi-latency ehambelana nokusebenza kunye nedatha egcinwe kwiidiski, kunye nenyaniso yokuba ukudlulisa idatha kwidiski ukuya kwimemori ngumsebenzi onzima wobutyebi.
Umzekelo, uneerekhodi zabasebenzisi abayi-100000000 kwaye uqhuba umbuzo ngolwakhiwo. OFFSET 50000000. Oku kuthetha ukuba i-DBMS iya kufuneka ilayishe zonke ezi rekhodi (kwaye asizifuni!), Zibeke kwimemori, kwaye emva koko thatha, zithi, iziphumo ze-20 zichazwe LIMIT.
Masithi inokubonakala ngolu hlobo: "khetha imiqolo ukusuka kwi-50000 ukuya kwi-50020 ukusuka kwi-100000". Oko kukuthi, inkqubo iya kufuna kuqala ukulayisha imiqolo engama-50000 ukugqiba umbuzo. Uyayibona na ukuba mngakanani na umsebenzi ongeyomfuneko aya kuwenza?
Ukuba awundikholelwa, jonga kumzekelo endiwudalileyo usebenzisa iimpawu .
Umzekelo kwi-db-fiddle.com
Apho, ngasekhohlo, ebaleni Schema SQL, kukho ikhowudi efaka 100000 imiqolo kwisiseko sedatha, kwaye ekunene, ebaleni. Query SQL, imibuzo emibini ibonisiwe. Eyokuqala, ecothayo, ibonakala ngolu hlobo:
SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;
Kwaye okwesibini, esisisisombululo esisebenzayo kwingxaki efanayo, ifana nale:
SELECT *
FROM `docs`
WHERE id > 85000
LIMIT 10;
Ukuze ufezekise ezi zicelo, nqakraza nje iqhosha Run phezulu kwephepha. Emva kokuba senze oku, sithelekisa ulwazi malunga nexesha lokwenziwa kombuzo. Kuyavela ukuba ukuphumeza umbuzo ongasebenziyo kuthatha ixesha elingaphezulu lokuphinda-phindwe ngama-30 kunokuphumeza elesibini (ngeli xesha liyahluka ukusuka kusetyenziso ukuya kwintsebenzo; umzekelo, inkqubo inokuxela ukuba umbuzo wokuqala uthathe 37 ms ukugqibezela, kodwa ukuphunyezwa okwesibini - 1 ms).
Kwaye ukuba kukho idatha engaphezulu, ke yonke into iya kujongeka ngakumbi (ukuqinisekiswa koku, jonga kwam kunye ne-10 yezigidi zemiqolo).
Le nto sigqiba kuthetha ngayo kufuneka ikunike ingqiqo malunga nokuba imibuzo yesiseko sedata isetyenzwa njani na.
Nceda uqaphele ukuba ixabiso eliphezulu OFFSET β okukhona isicelo siya kuthatha ixesha elide ukugqitywa.
Yintoni endifanele ndiyisebenzise endaweni yokudibanisa i-OFFSET kunye ne-LIMIT?
Endaweni yokudibanisa OFFSET ΠΈ LIMIT Kufanelekile ukusebenzisa isakhiwo esakhiwe ngokwesikimu esilandelayo:
SELECT * FROM table_name WHERE id > 10 LIMIT 20
Oku kukuphunyezwa kombuzo ngekhesa esekwe kuphegination.
Endaweni yokugcina ezangoku ekuhlaleni OFFSET ΠΈ LIMIT kwaye uzidlulisele ngesicelo ngasinye, kufuneka ugcine isitshixo esingundoqo sokugqibela esifunyenweyo (ngesiqhelo oku ID) kunye LIMIT, ngenxa yoko, imibuzo efana nale ingasentla iya kufunyanwa.
Ngoba? Ingongoma kukuba ngokuchaza ngokucacileyo isazisi somqolo wokugqibela ofundiweyo, uxelela i-DBMS yakho apho kufuneka iqalise ukukhangela idatha efunekayo. Ngaphezu koko, ukukhangela, ngenxa yokusetyenziswa kwesitshixo, kuya kwenziwa ngokufanelekileyo; inkqubo ayiyi kuphazanyiswa yimigca engaphandle koluhlu oluchaziweyo.
Makhe sijonge kolu thelekiso lomsebenzi lulandelayo lwemibuzo eyahlukeneyo. Nanku umbuzo ongasebenziyo.
Isicelo esicothayo
Kwaye nalu uguqulelo olulungisiweyo lwesi sicelo.
Isicelo esikhawulezayo
Yomibini le mibuzo ibuyisela inani elifanayo ledatha. Kodwa eyokuqala ithatha imizuzwana eyi-12,80 ukuyigqiba, kwaye eyesibini ithatha imizuzwana eyi-0,01. Ngaba uyawuva umahluko?
Iingxaki ezikhoyo
Ukuze indlela yombuzo ecetywayo isebenze ngokufanelekileyo, itheyibhile kufuneka ibe nomhlathi (okanye iikholamu) eziqulethe izalathisi ezikhethekileyo, ezilandelelanayo, njengesichongi esipheleleyo. Kwezinye iimeko ezithile, oku kunokugqiba impumelelo yokusebenzisa imibuzo enjalo ukunyusa isantya sokusebenza kunye nedathabheyisi.
Ngokwemvelo, xa usenza imibuzo, kufuneka uthathele ingqalelo i-architecture ethile yeetafile kwaye ukhethe ezo ndlela eziza kusebenza kakuhle kwiitafile ezikhoyo. Umzekelo, ukuba ufuna ukusebenza kwimibuzo enemithamo emikhulu yedatha ehambelanayo, unokuyifumana inomdla inqaku.
Ukuba sijongene nengxaki yokuphosa isitshixo esiphambili, umzekelo, ukuba sinetafile enobudlelwane obuninzi ukuya kwabaninzi, ngoko indlela yemveli yokusebenzisa. OFFSET ΠΈ LIMIT, iqinisekisiwe ukuba iya kusifanela. Kodwa ukusetyenziswa kwayo kunokubangela imibuzo enokucotha. Kwiimeko ezinjalo, ndingacebisa ukuba kusetyenziswe isitshixo esisisiseko sokunyuselwa ngokuzenzekelayo, nokuba sifuneka kuphela ukujongana nemibuzo enephepha.
Ukuba unomdla kwesi sihloko - , ΠΈ - izixhobo ezininzi eziluncedo.
Iziphumo
Isigqibo esibalulekileyo esinokusenza kukuba, kungakhathaliseki ukuba yiyiphi i-database yolwazi esithetha ngayo, kuhlale kuyimfuneko ukuhlalutya isantya sokwenza umbuzo. Kule mihla, i-scalability yezisombululo ibaluleke kakhulu, kwaye ukuba yonke into iyilwe ngokuchanekileyo ukususela ekuqaleni kokusebenza kwinkqubo ethile, oku, kwixesha elizayo, kunokusindisa umphuhlisi kwiingxaki ezininzi.
Uyihlalutya njani kwaye uyisebenzise njani imibuzo yedatabase?
umthombo: www.habr.com