Pewani kugwiritsa ntchito OFFSET ndi LIMIT pamafunso apagulu

Anapita masiku omwe simunade nkhawa ndi kukhathamiritsa magwiridwe antchito a database. Nthawi siyiima. Aliyense wamalonda watsopano waukadaulo akufuna kupanga Facebook yotsatira, pomwe akuyesera kusonkhanitsa zonse zomwe angakwanitse. Mabizinesi amafunikira data iyi kuti aphunzitse bwino zitsanzo zomwe zimawathandiza kupanga ndalama. Zikatero, opanga mapulogalamu ayenera kupanga ma API omwe amawalola kuti azigwira ntchito mwachangu komanso modalirika ndi zidziwitso zambiri.

Ngati mwakhala mukupanga ma application kapena database backends kwa nthawi yayitali, mwina mwalembapo ma code kuti mufunse mafunso. Mwachitsanzo, monga chonchi:

SELECT * FROM table_name LIMIT 10 OFFSET 40

Momwe ziriri?

Koma ngati umu ndi momwe munachitira pagination yanu, ndikupepesa kunena kuti simunachite m'njira yabwino kwambiri.

Mukufuna kunditsutsa? Mungathe osati gwiritsa ntchito время. lochedwa, Sungani и Sakanizani Akugwiritsa ntchito kale njira zomwe ndikufuna kunena lero.

Tchulani m'modzi wopanga kumbuyo yemwe sanagwiritsepo ntchito OFFSET и LIMIT kuyankha mafunso olakwika. Mu MVP (Minimum Viable Product) komanso m'mapulojekiti omwe deta yaying'ono ikugwiritsidwa ntchito, njirayi imagwira ntchito. Izo “zimagwira ntchito,” titero kunena kwake.

Koma ngati mukufuna kupanga machitidwe odalirika komanso ogwira mtima kuyambira pachiyambi, muyenera kusamala pasadakhale za momwe mungafufuzire nkhokwe zomwe zimagwiritsidwa ntchito pamakinawa.

Lero tikambirana za zovuta zomwe zimagwiritsidwa ntchito kwambiri (zoyipa kwambiri) zama injini zamafunso, komanso momwe mungakwaniritsire magwiridwe antchito apamwamba poyankha mafunso otere.

Cholakwika ndi chiyani ndi OFFSET ndi LIMIT?

Monga tanenera kale, OFFSET и LIMIT Amagwira ntchito bwino pama projekiti omwe safunikira kugwira ntchito ndi data yambiri.

Vuto limabwera pamene database ikukula mpaka kukula kotero kuti sikulowanso mu kukumbukira kwa seva. Komabe, mukamagwira ntchito ndi database iyi, muyenera kugwiritsa ntchito mafunso apaganizidwe.

Kuti vutoli liwonekere lokha, payenera kukhala nthawi yomwe DBMS imagwiritsa ntchito kusanthula kwa Full Table Scan pafunso lililonse lokhala ndi pepala (pamene kuyika ndi kufufuta kungachitike, ndipo sitifuna deta yakale!).

Kodi "kujambula patebulo lonse" (kapena "kujambula motsatizana", Sequential Scan) ndi chiyani? Iyi ndi ntchito yomwe DBMS imawerengera motsatizana mzere uliwonse wa tebulo, ndiko kuti, deta yomwe ili mmenemo, ndikuyang'ana kuti ikutsatira chikhalidwe choperekedwa. Kujambula patebulo kwamtunduwu kumadziwika kuti ndikochedwa kwambiri. Chowonadi ndi chakuti ikachitidwa, ntchito zambiri zolowetsa / zotulutsa zimachitika zomwe zimaphatikizapo gawo la disk la seva. Zinthu zimaipiraipira kwambiri chifukwa cha latency yokhudzana ndi kugwira ntchito ndi data yosungidwa pa disks, komanso kuti kusamutsa deta kuchokera ku diski kupita ku kukumbukira ndi ntchito yofunika kwambiri.

Mwachitsanzo, muli ndi mbiri ya ogwiritsa ntchito 100000000 ndipo mumafunsa funso ndikumanga. OFFSET 50000000. Izi zikutanthauza kuti DBMS iyenera kukweza zolemba zonsezi (ndipo sitikuzifuna nkomwe!), Ziyikeni pamtima, ndipo pambuyo pake tengani, nenani, zotsatira za 20 zomwe zafotokozedwa mu LIMIT.

Tinene kuti zitha kuwoneka motere: "sankhani mizere kuchokera 50000 mpaka 50020 kuchokera 100000". Ndiye kuti, dongosololi lidzafunika kaye kukweza mizere 50000 kuti amalize kufunsa. Kodi mukuwona kuchuluka kwa ntchito zosafunikira zomwe adzagwire?

Ngati simundikhulupirira, yang'anani chitsanzo chomwe ndidapanga pogwiritsa ntchito mawonekedwe db-fiddle.com. 

Chitsanzo pa db-fiddle.com

Kumeneko, kumanzere, m'munda Schema SQL, pali code yomwe imayika mizere 100000 mu database, ndipo kumanja, m'munda. Query SQL, mafunso awiri akuwonetsedwa. Woyamba, wodekha, amawoneka motere:

SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;

Ndipo yachiwiri, yomwe ndi njira yabwino yothetsera vuto lomwelo, ili motere:

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

Kuti mukwaniritse zopempha izi, ingodinani pa batani Run pamwamba pa tsamba. Tikachita izi, tikufanizira zambiri za nthawi yofunsa mafunso. Zikuoneka kuti kufunsa funso losagwira ntchito kumatenga nthawi yayitali ya 30 kuposa kuchita yachiwiri (nthawi ino imasiyana kuchokera pakuthamanga mpaka kuthamanga; mwachitsanzo, dongosolo likhoza kunena kuti funso loyamba lidatenga 37 ms kuti amalize, koma kuchita kachiwiri - 1 ms).

Ndipo ngati pali deta yambiri, ndiye kuti zonse zidzawoneka zoipitsitsa (kuti mutsimikizire izi, yang'anani zanga chitsanzo ndi mizere 10 miliyoni).

Zomwe takambiranazi zikuyenera kukupatsani chidziwitso cha momwe mafunso a database amasankhidwira.

Chonde dziwani kuti mtengowo ndi wapamwamba kwambiri OFFSET - pempho litenga nthawi yayitali kuti likwaniritsidwe.

Ndiyenera kugwiritsa ntchito chiyani m'malo mophatikiza OFFSET ndi LIMIT?

M'malo mwa kuphatikiza OFFSET и LIMIT Ndikoyenera kugwiritsa ntchito kapangidwe komangidwa molingana ndi dongosolo ili:

SELECT * FROM table_name WHERE id > 10 LIMIT 20

Uku ndikufunsa mafunso okhala ndi cholozera pagination.

M’malo mosunga zamakono m’dera lanu OFFSET и LIMIT ndikuwatumiza ndi pempho lililonse, muyenera kusunga kiyi yomaliza yolandila (nthawi zambiri izi ndi ID) ndi LIMIT, chifukwa chake, mafunso ofanana ndi omwe ali pamwambawa adzapezedwa.

Chifukwa chiyani? Mfundo ndi yakuti mwa kufotokoza momveka bwino chizindikiritso cha mzere womaliza wowerengedwa, mumauza DBMS yanu kumene ikufunika kuyamba kufufuza deta yofunikira. Kuphatikiza apo, kusaka, chifukwa chogwiritsa ntchito kiyi, kuchitidwa bwino; dongosolo siliyenera kusokonezedwa ndi mizere kunja kwa mndandanda womwe watchulidwa.

Tiyeni tiwone kufananiza kotsatiraku kwamafunso osiyanasiyana. Nali funso losathandiza.

Pempho lapang'onopang'ono

Ndipo apa pali mtundu wokometsedwa wa pempholi.

Pempho lachangu

Mafunso onsewa amabweretsanso kuchuluka kwa data komweko. Koma yoyamba imatenga masekondi 12,80 kuti ithe, ndipo yachiwiri imatenga masekondi 0,01. Kodi mukumva kusiyana kwake?

Mavuto angakhalepo

Kuti njira yofunsidwayo igwire bwino ntchito, tebulo liyenera kukhala ndi gawo (kapena mizati) yokhala ndi milozera yapadera, yotsatizana, monga chizindikiritso chonse. Nthawi zina, izi zitha kuwonetsa kupambana kogwiritsa ntchito mafunso otere kuti muwonjezere liwiro logwira ntchito ndi nkhokwe.

Mwachilengedwe, popanga mafunso, muyenera kuganizira kapangidwe kake ka matebulo ndikusankha njira zomwe zingagwire ntchito bwino pamagome omwe alipo. Mwachitsanzo, ngati mukufuna kuyankha mafunso okhala ndi zambiri zokhudzana ndi data, mutha kuwona kuti ndizosangalatsa izi nkhani.

Ngati tikukumana ndi vuto losowa makiyi oyamba, mwachitsanzo, ngati tili ndi tebulo lokhala ndi maubwenzi ambiri, ndiye kuti njira yachikhalidwe yogwiritsira ntchito. OFFSET и LIMIT, zatsimikizika kuti zitikwanira. Koma kugwiritsa ntchito kwake kumatha kubweretsa mafunso ocheperako. Zikatero, ndingapangire kugwiritsa ntchito kiyi yoyambira yokha-yowonjezera, ngakhale ikufunika kuthana ndi mafunso opanda pake.

Ngati mumakonda mutuwu - tawonani, tawonani и tawonani - zipangizo zingapo zothandiza.

Zotsatira

Mfundo yaikulu yomwe tingatenge ndi yakuti, ziribe kanthu kukula kwa nkhokwe zomwe tikukamba, nthawi zonse ndizofunikira kufufuza liwiro la mafunso. Masiku ano, scalability wa mayankho n'kofunika kwambiri, ndipo ngati zonse zakonzedwa molondola kuyambira pachiyambi ntchito pa dongosolo linalake, izi, m'tsogolo, akhoza kupulumutsa mapulogalamu ku mavuto ambiri.

Kodi mumasanthula ndi kukonza bwanji mafunso a database?

Source: www.habr.com