Kwapera mazuva apo iwe waisafanira kunetseka nezve optimize dhizaini mashandiro. Nguva haina kumira. Wese mutsva muzvinabhizimusi wetekinoroji anoda kugadzira iyo inotevera Facebook, achiedza kuunganidza data rese ravanogona kuisa maoko avo. Mabhizinesi anoda iyi data kuti ave nani ekudzidzisa modhi inovabatsira kuita mari. Mumamiriro ezvinhu akadai, vanogadzira zvirongwa vanofanirwa kugadzira maAPI anovabvumidza kuti vashande nekukurumidza uye nekuvimbika nehuwandu hukuru hweruzivo.
Kana wanga uchigadzira application kana database backends kune chero hurefu hwenguva, iwe ungangove wakanyora kodhi yekumhanyisa mibvunzo ine paginated. Semuenzaniso, seizvi:
SELECT * FROM table_name LIMIT 10 OFFSET 40
Nzira iyo?
Asi kana ariwo maitiro awakaita pagination yako, ndine hurombo kutaura kuti hauna kuzviita nenzira inoshanda.
Unoda kundipikisa here? . , ΠΈ Vari kutoshandisa nzira dzandiri kuda kutaura nezvadzo nhasi.
Doma kamwechete backend mugadziri asina kumbobvira ashandisa OFFSET ΠΈ LIMIT kuita mibvunzo yepeji. MuMVP (Minimum Viable Product) uye mumapurojekiti umo huwandu hudiki hwe data hunoshandiswa, nzira iyi inoshanda chaizvo. βZvinongoshanda,β sokunge zvakadaro.
Asi kana iwe uchida kugadzira akavimbika uye anoshanda masisitimu kubva pakutanga, iwe unofanirwa kuchenjerera pachine nguva nezve kugona kwekubvunza dhatabhesi rinoshandiswa mumasitimu akadaro.
Nhasi tichataura nezve matambudziko anowanzo shandiswa (zvakanyanya kuipa) mashandisirwo einjini yemubvunzo, uye maitiro ekuita basa repamusoro paunenge uchiita mibvunzo yakadai.
Chii chakashata neOFFSET neLIMIT?
Sezvatotaurwa, OFFSET ΠΈ LIMIT Ivo vanoita zvakanaka mumapurojekiti asingade kushanda nehuwandu hwakawanda hwe data.
Dambudziko rinomuka kana dhatabhesi inokura kusvika pakukura zvekuti haichakwane mundangariro dzeseva. Nekudaro, kana uchishanda neiyi dhatabhesi, unofanirwa kushandisa mibvunzo ine paginated.
Kuti dambudziko iri rizviratidze, panofanirwa kuve nemamiriro ezvinhu apo iyo DBMS inoenda kune isingaite Yakazara Tafura Scan oparesheni pamubvunzo wega wega wepeji (apo kuisa nekudzima mabasa kuchiitika, uye isu hatidi data rechinyakare!).
Chii chinonzi "full table scan" (kana "sequential table scan", Sequential Scan)? Uku kuvhiya panguva iyo DBMS inoverenga zvakateerana mutsara wega wega wetafura, ndiko kuti, iyo data irimo, uye inovatarisa kuti vatevedzere nemamiriro akapihwa. Iyi mhando yetafura yekuongorora inozivikanwa kuva inononoka. Icho chokwadi ndechekuti kana ichinge yaitwa, akawanda ekuisa / kubuda mabasa anoitwa anosanganisira server's disk subsystem. Mamiriro ezvinhu anowedzera kuipa nekunonoka kunobatanidza nekushanda nedheta yakachengetwa pa disks, uye chokwadi chokuti kuendesa data kubva ku diski kuenda kundangariro ndeyekushandisa-yakanyanya kushanda.
Semuenzaniso, une marekodhi e100000000 vashandisi uye unomhanyisa mubvunzo nekuvaka. OFFSET 50000000. Izvi zvinoreva kuti DBMS ichafanirwa kurodha zvese izvi zvinyorwa (uye isu hatitombozvida!), Zviise mundangariro, uye mushure meizvozvo tora, toti, makumi maviri mhedzisiro yakataurwa mukati. LIMIT.
Ngatitii zvingaite seizvi: "sarudza mitsara kubva pa50000 kusvika pa50020 kubva pa100000". Ndiko kuti, sisitimu inozoda kutanga yaisa zviuru makumi mashanu mitsara kuti ipedze mubvunzo. Unoona here kuti ibasa risiri madikanwa rakawanda sei raachafanira kuita?
Kana musinganditendi, tarisai muenzaniso wandakagadzira ndichishandisa maficha .
Muenzaniso pa db-fiddle.com
Ikoko, kuruboshwe, mumunda Schema SQL, pane kodhi inoisa mitsara zana mudhatabhesi, uye kurudyi, mumunda. Query SQL, mibvunzo miviri inoratidzwa. Yekutanga, inononoka, inoita seizvi:
SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;
Uye yechipiri, inova mhinduro inoshanda kune rimwe dambudziko, yakaita seizvi:
SELECT *
FROM `docs`
WHERE id > 85000
LIMIT 10;
Kuti uzadzise zvikumbiro izvi, ingobaya bhatani Run kumusoro kwepeji. Kana taita izvi, tinofananidza ruzivo nezve nguva yekubvunza mibvunzo. Zvinoitika kuti kuita mubvunzo usingashande kunotora kanokwana ka30 kureba kupfuura kuita wechipiri (ino nguva inosiyana kubva pakumhanya kuenda kunomhanya; semuenzaniso, sisitimu inogona kutaura kuti mubvunzo wekutanga wakatora 37 ms kuti upedze, asi kuitwa kwe yechipiri - 1 ms).
Uye kana paine data rakawanda, saka zvese zvinozotaridzika zvakanyanya (kuve nechokwadi neizvi, tarisa zvangu ine mitsara yemamiriyoni gumi).
Zvatichangobva kukurukura zvinofanirwa kukupa muono wekuti mibvunzo yedatabase inogadziriswa sei.
Ndapota cherechedza kuti yakakwirira kukosha OFFSET - iyo nguva yakareba chikumbiro chichatora kupedzisa.
Chii chandinofanira kushandisa pane kusanganiswa kweOFFSET uye LIMIT?
Panzvimbo pemusanganiswa OFFSET ΠΈ LIMIT Zvakakodzera kushandisa chimiro chakavakwa maererano neinotevera chirongwa:
SELECT * FROM table_name WHERE id > 10 LIMIT 20
Uku kuita kwemibvunzo ine cursor based pagination.
Panzvimbo pokuchengeta dzazvino uno munharaunda OFFSET ΠΈ LIMIT uye uzvitumire nechikumbiro chega chega, iwe unofanirwa kuchengeta yekupedzisira yakagamuchirwa kiyi yekutanga (kazhinji izvi ndizvo ID) uye LIMIT, nekudaro, mibvunzo yakafanana neiri pamusoro ichawanikwa.
Sei? Chiripo ndechekuti nekunyatso tsanangura identifier yemutsara wekupedzisira kuverenga, unoudza DBMS yako painoda kutanga kutsvaga data rinodiwa. Uyezve, kutsvaga, nekuda kwekushandiswa kwekiyi, kuchaitwa nemazvo; iyo sisitimu haifanirwe kukanganiswa nemitsetse iri kunze kweiyo yakatarwa.
Ngatitarisei kunotevera kwekuita kuenzanisa kweakasiyana mibvunzo. Heino muvhunzo usingashande.
Chikumbiro chinononoka
Uye heino optimized vhezheni yechikumbiro ichi.
Chikumbiro chekukurumidza
Yese mibvunzo inodzosa huwandu hwakafanana hwe data. Asi yekutanga inotora 12,80 seconds kuti ipedze, uye yechipiri inotora 0,01 seconds. Unonzwa musiyano here?
Zvinetso zvinogona kuitika
Kuti nzira yekubvunza ishande nemazvo, tafura inofanirwa kunge iine koramu (kana makoramu) ine akasiyana, anotevedzana indekisi, senge nhamba identifier. Mune zvimwe zviitiko, izvi zvinogona kuona kubudirira kwekushandisa mibvunzo yakadaro kuwedzera kukurumidza kwekushanda ne database.
Nomuzvarirwo, kana uchigadzira mibvunzo, iwe unofanirwa kufunga nezve chaiyo dhizaini yematafura uye sarudza iwo maitiro anozoshanda zvakanyanya pamatafura aripo. Semuenzaniso, kana iwe uchida kushanda mumibvunzo ine mavhoriyamu makuru e data rakabatana, unogona kuzviwana zvichinakidza article.
Kana takatarisana nedambudziko rekupotsa kiyi yekutanga, semuenzaniso, kana tiine tafura ine hukama hwakawanda-kune-vakawanda, saka nzira yechinyakare yekushandisa. OFFSET ΠΈ LIMIT, inovimbiswa kuti inokodzera isu. Asi kushandiswa kwayo kunogona kukonzera mibvunzo inononoka. Muzviitiko zvakaita seizvi, ini ndinokurudzira kushandisa auto-incrementing primary kiyi, kunyangwe iwe uchingoda chete kubata mibvunzo ine paginated.
Kana uchifarira nyaya iyi - , ΠΈ - akati wandei anobatsira zvinhu.
Migumisiro
Mhedziso huru yatinogona kutora ndeyekuti, zvisinei nehukuru hwemadhatabheti atiri kutaura nezvawo, zvinogara zvichidikanwa kuti tiongorore kumhanya kwemubvunzo wekuita. Mazuva ano, scalability yemhinduro yakakosha zvakanyanya, uye kana zvese zvakagadzirirwa nemazvo kubva pakutanga kwekushanda pane imwe system, izvi, mune ramangwana, zvinogona kuchengetedza mugadziri kubva kumatambudziko mazhinji.
Iwe unoongorora sei uye nekugonesa mibvunzo yedatabase?
Source: www.habr.com