Gwema ukusebenzisa i-OFFSET kanye ne-LIMIT emibuzweni enamapheshana

Sezadlula izinsuku lapho ungazange ukhathazeke ngokuthuthukisa ukusebenza kwesizindalwazi. Isikhathi asimile. Wonke usomabhizinisi omusha wezobuchwepheshe ufuna ukudala i-Facebook elandelayo, ngenkathi ezama ukuqoqa yonke idatha abangayithola. Amabhizinisi adinga le datha ukuze aqeqeshe amamodeli angcono awasiza enze imali. Ezimweni ezinjalo, abahleli bezinhlelo badinga ukudala ama-API abavumela ukuthi basebenze ngokushesha nangokuthembekile ngenani elikhulu lolwazi.

Uma ubukade uklama i-backends yohlelo lokusebenza noma isizindalwazi nganoma isiphi ubude besikhathi, cishe ubhale ikhodi ukuze uphendule imibuzo ebhalwe phansi. Ngokwesibonelo, kanje:

SELECT * FROM table_name LIMIT 10 OFFSET 40

Indlela okuyiyo?

Kodwa uma le yindlela owenze ngayo ukuqanjwa kwakho, ngiyaxolisa ukusho ukuthi awenzanga ngendlela ephumelela kakhulu.

Ingabe ufuna ukungiphikisa? Ungakwazi hhayi chitha время. ukuxega, Thenga и I-Mixmax Sebevele basebenzisa amasu engifuna ukukhuluma ngawo namuhla.

Yisho okungenani unjiniyela oyedwa ongemuva ongakaze asebenzise OFFSET и LIMIT ukwenza imibuzo ephepheni. Ku-MVP (Minimum Viable Product) nakumaphrojekthi lapho kusetshenziswa amanani amancane edatha, le ndlela iyasebenza impela. “Kusebenza nje,” ngomqondo ongokomfanekiso.

Kodwa uma udinga ukudala amasistimu athembekile nasebenza kahle kusukela ekuqaleni, kufanele unakekele kusenesikhathi mayelana nokusebenza kahle kokubuza imininingwane yolwazi esetshenziswa kumasistimu anjalo.

Namuhla sizokhuluma ngezinkinga ngokusetshenziswa okuvame ukusetshenziswa (okubi kakhulu) kwezinjini zemibuzo ezinamaphepha, nokuthi ungafinyelela kanjani ukusebenza okuphezulu lapho uphendula imibuzo enjalo.

Yini engalungile nge-OFFSET kanye ne-LIMIT?

Njengoba sekushiwo, OFFSET и LIMIT Basebenza kahle kumaphrojekthi angadingi ukusebenza ngenani elikhulu ledatha.

Inkinga ivela lapho i-database ikhula ibe usayizi kangangokuthi ayisangeni kumemori yeseva. Nokho, lapho usebenza nale database, udinga ukusebenzisa imibuzo enamakhasi.

Ukuze le nkinga izibonakalise, kufanele kube nesimo lapho i-DBMS iphendukela ekusebenzeni kwe-Full Table Scan engasebenzi kahle kumbuzo ngamunye onamakhasi (ngenkathi ukufakwa nokususa kungase kwenzeke , futhi asiyidingi idatha ephelelwe yisikhathi!).

Kuyini “ukuskena kwethebula eligcwele” (noma “ukuskena kwetafula okulandelanayo”, Ukuskena Okulandelanayo)? Lona umsebenzi lapho i-DBMS ifunda ngokulandelana umugqa ngamunye wethebula, okungukuthi, idatha equkethwe kuyo, futhi ibahlole ukuthi bayahambisana nombandela othile. Lolu hlobo lokuskena ithebula lwaziwa njengolunensa kakhulu. Iqiniso liwukuthi lapho isenziwa, imisebenzi eminingi yokufaka/yokukhiphayo yenziwa ehlanganisa isistimu engaphansi yediski yeseva. Isimo senziwa sibi kakhulu ngokubambezeleka okuhlotshaniswa nokusebenza nedatha egcinwe kuma-disks, kanye neqiniso lokuthi ukudlulisa idatha kusuka kudiski kuya enkumbulweni kuwumsebenzi odinga izinsiza.

Isibonelo, unamarekhodi abasebenzisi abangu-100000000 futhi uqhuba umbuzo ngokwakhiwa OFFSET 50000000. Lokhu kusho ukuthi i-DBMS kuzodingeka ilayishe wonke lawa marekhodi (futhi asiwadingi ngisho!), Wabeke enkumbulweni, futhi ngemva kwalokho thatha, uthi, imiphumela ye-20 ebikiwe LIMIT.

Ake sithi kungase kubukeke kanje: "khetha imigqa kusuka ku-50000 kuya ku-50020 kusukela ku-100000". Okusho ukuthi, isistimu izodinga kuqala ukulayisha imigqa engu-50000 ukuze iqedele umbuzo. Uyabona ukuthi ungakanani umsebenzi ongadingekile azowenza?

Uma ungangikholwa, bheka isibonelo engisidalile ngisebenzisa izici db-fiddle.com. 

Isibonelo ku-db-fiddle.com

Lapho, ngakwesobunxele, ensimini Schema SQL, kunekhodi efaka imigqa engu-100000 kusizindalwazi, futhi kwesokudla, ensimini Query SQL, imibuzo emibili iyaboniswa. Eyokuqala, ehamba kancane, ibonakala kanje:

SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;

Futhi okwesibili, okuyisixazululo esisebenzayo senkinga efanayo, kufana nalokhu:

SELECT *
FROM `docs`
WHERE id > 85000
LIMIT 10;

Ukuze ufeze lezi zicelo, mane uchofoze inkinobho Run phezulu ekhasini. Ngemva kokwenza lokhu, siqhathanisa ulwazi mayelana nesikhathi sokwenza umbuzo. Kuvela ukuthi ukwenza umbuzo ongasebenzi kuthatha okungenani izikhathi ezingu-30 isikhathi eside kunokusebenzisa owesibili (lokhu kuyahlukahluka kuye ngokuthi ugijima uye uqalise; isibonelo, isistimu ingase ibike ukuthi umbuzo wokuqala uthathe 37 ms ukuze uqedelwe, kodwa ukuqaliswa okwesibili - 1 ms).

Futhi uma kunedatha eyengeziwe, khona-ke konke kuzobukeka kubi nakakhulu (ukuqiniseka ngalokhu, bheka yami isibonelo ngemigqa eyizigidi eziyi-10).

Esisanda kuxoxa ngakho kufanele kukunikeze ukuqonda kokuthi imibuzo egciniwe icutshungulwa kanjani ngempela.

Sicela uqaphele ukuthi ukuphakama kwevelu OFFSET — uma isicelo sizothatha isikhathi eside ukuqedwa.

Yini okufanele ngiyisebenzise esikhundleni senhlanganisela ye-OFFSET ne-LIMIT?

Esikhundleni senhlanganisela OFFSET и LIMIT Kuyafaneleka ukusebenzisa isakhiwo esakhiwe ngokulandela uhlelo olulandelayo:

SELECT * FROM table_name WHERE id > 10 LIMIT 20

Lokhu ukwenza kombuzo ngokubhala ngekhesa okususelwe.

Esikhundleni sokugcina ezamanje endaweni OFFSET и LIMIT futhi uzidlulisele ngesicelo ngasinye, udinga ukugcina ukhiye wokugcina owamukelwe (imvamisa lokhu kuba ID) futhi LIMIT, ngenxa yalokho, kuzotholakala imibuzo efana nalokhu okungenhla.

Kungani? Iphuzu liwukuthi ngokucacisa ngokucacile isihlonzi somugqa wokugcina ofundiwe, utshela i-DBMS yakho lapho idinga ukuqala ukusesha idatha edingekayo. Ngaphezu kwalokho, ukusesha, ngenxa yokusetshenziswa kokhiye, kuzokwenziwa kahle; uhlelo ngeke kudingeke luphazanyiswe imigqa engaphandle kobubanzi obushiwo.

Ake sibheke ukuqhathanisa okulandelayo kokusebenza kwemibuzo ehlukahlukene. Nawu umbuzo ongasebenzi.

Isicelo esihamba kancane

Futhi nansi inguqulo elungiselelwe yalesi sicelo.

Isicelo esisheshayo

Yomibili imibuzo ibuyisela inani elifanayo ncamashi ledatha. Kodwa eyokuqala ithatha imizuzwana engu-12,80 ukuqeda, kanti eyesibili ithatha imizuzwana engu-0,01. Uyawuzwa umehluko?

Izinkinga ezingenzeka

Ukuze indlela yombuzo ohlongozwayo isebenze ngempumelelo, ithebula kufanele libe nekholomu (noma amakholomu) aqukethe izinkomba ezihlukile, ezilandelanayo, njengesihlonzi esiyinombolo. Kwezinye izimo ezithile, lokhu kungase kunqume impumelelo yokusebenzisa imibuzo enjalo ukukhulisa isivinini sokusebenza nesizindalwazi.

Ngokwemvelo, lapho wakha imibuzo, udinga ukucabangela ukwakheka okuqondile kwamatafula bese ukhetha lezo zindlela ezizosebenza kangcono kumatafula akhona. Isibonelo, uma udinga ukusebenza ngemibuzo ngamavolumu amakhulu edatha ehlobene, ungase ukuthole kuthakazelisa эта isihloko.

Uma sibhekene nenkinga yokushoda kokhiye oyinhloko, isibonelo, uma sinetafula elinobudlelwano bokuningi kuya kwabaningi, khona-ke indlela evamile yokusebenzisa. OFFSET и LIMIT, iqinisekisiwe ukuthi izovumelana nathi. Kodwa ukusetshenziswa kwayo kungase kubangele imibuzo enamandla yokunensa. Ezimweni ezinjalo, ngingancoma ukusebenzisa ukhiye oyinhloko ozikhulisayo ngokuzenzakalelayo, noma ngabe udingeka kuphela ukuphatha imibuzo enamakhasi.

Uma unentshisekelo kulesi sihloko - lapha, lapha и lapha - izinto eziningana eziwusizo.

Imiphumela

Isiphetho esiyinhloko esingasidonsela ukuthi ngaso sonke isikhathi kuyadingeka ukuhlaziya isivinini sokwenza imibuzo, kungakhathaliseki ukuthi yiziphi izingosi zolwazi esikhuluma ngazo. Namuhla, ukulinganisa kwezixazululo kubaluleke kakhulu, futhi uma yonke into iklanywe kahle kusukela ekuqaleni kokusebenza ohlelweni oluthile, lokhu, esikhathini esizayo, kungasindisa unjiniyela ezinkingeni eziningi.

Uyihlaziya futhi uyisebenzise kanjani imibuzo yesizindalwazi?

Source: www.habr.com