Sei uchida rubatsiro rwekushandisa pagination pamakiyi?

Mhoroi mose! Ini ndiri backend developer kunyora microservices muJava + Spring. Ini ndinoshanda mune chimwe chemukati chekuvandudza chigadzirwa zvikwata kuTinkoff.

Muchikwata chedu, mubvunzo wekugadzirisa mibvunzo muDBMS inowanzomuka. Iwe unogara uchida kukurumidza kukurumidza, asi haugone kugara uchipfuura nemaindex akagadzirwa zvine hungwaru-iwe unofanirwa kutsvaga mamwe maworkaround. Panguva yeimwe yeiyi yekudzungaira pawebhu kutsvaga zvine musoro optimizations kana ndichishanda nemadatabase, ndakawana Marcus Wynand's isingaperi inobatsira blog, munyori weSQL Performance Inotsanangurwa. Iyi ndiyo mhando isingawanzo bhurogu yaunogona kuverenga zvese zvinyorwa mumutsara.

Ndinoda kushandura chinyorwa chipfupi chaMarcus kwauri. Inogona kunzi kune imwe nhanho manifesto inotsvaga kukwevera kutarisa kune yekare, asi ichiri yakakosha dambudziko rekuita kweiyo offset oparesheni zvinoenderana neSQL standard.

Kune dzimwe nzvimbo ndichawedzera munyori netsananguro nemacomments. Ndichareva nzvimbo dzese dzakadai se "approx." kuti uwane kujeka

Sumo diki

Ini ndinofunga vanhu vazhinji vanoziva kunetsa uye kunonoka kushanda nepeji inosarudza kuburikidza neoffset. Wanga uchiziva here kuti inogona kutsiviwa zviri nyore nedhizaini inoshanda?

Saka, iyo offset kiyi kiyi inoudza dhatabhesi kuti isvetuke yekutanga n marekodhi muchikumbiro. Nekudaro, iyo dhatabhesi ichiri kuda kuverenga aya ekutanga n marekodhi kubva kudhisiki, mune yakapihwa kurongeka (chinyorwa: shandisa kuronga kana yatsanangurwa), uye ipapo chete zvinozokwanisika kudzorera marekodhi kubva n + 1 zvichienda mberi. Chinhu chinonyanya kufadza ndechekuti dambudziko harisi mukuita kwakananga muDBMS, asi mune tsananguro yepakutanga zvinoenderana nechiyero:

...mitsetse inotanga kurongwa maererano ne uye wozoganhurirwa nekudonhedza nhamba yemitsara yakataurwa mu kubva pakutanga...
-SQL:2016, Chikamu 2, 4.15.3 Derived tables (chinyorwa: parizvino ndiyo inonyanya kushandiswa)

Chinhu chakakosha apa ndechekuti offset inotora parameter imwe chete - nhamba yemarekodhi ekusvetuka, uye ndizvozvo. Kutevera tsanangudzo iyi, DBMS inogona kungotora zvinyorwa zvose uye zvino kurasa zvisingakoshi. Zviripachena, iyi tsananguro yeoffset inotimanikidza kuita rimwe basa. Uye hazvina basa kana iri SQL kana NoSQL.

Kungowedzera kurwadziwa zvishoma

Matambudziko neoffset haagumire ipapo, uye heino chikonzero. Kana, pakati pekuverenga mapeji maviri e data kubva ku diski, imwe oparesheni inoisa rekodhi nyowani, chii chichaitika mune iyi kesi?

Kana offset ichishandiswa kusvetuka marekodhi kubva pamapeji apfuura, mumamiriro ekuwedzera rekodhi nyowani pakati pekuverenga kwemapeji akasiyana, iwe unogona kuwana zvakapetwa (ona: izvi zvinogoneka kana isu tichiverenga peji nepeji tichishandisa kurongeka nekuvaka, ipapo. pakati pekubuda kwedu inogona kuwana nyowani yekupinda).

Mufananidzo wacho unonyatsoratidza mamiriro ezvinhu aya. Nheyo yacho inoverenga zvinyorwa zvekutanga zve 10, mushure mokunge zvinyorwa zvitsva zvinoiswa, izvo zvinobvisa zvinyorwa zvose zvakaverengwa ne 1. Zvadaro nheyo inotora peji itsva kubva kune zvinyorwa zve10 zvinotevera uye inotanga kwete kubva ku11, sezvainofanira, asi kubva 10, kudzokorora rekodhi iyi. Pane zvimwe zvisizvo zvine chekuita nekushandiswa kweshoko iri, asi izvi ndizvo zvakajairika.

Sezvatakatoona, aya haasi matambudziko eiyo chaiyo DBMS kana kuita kwavo. Dambudziko nderekutsanangura pagination zvinoenderana neSQL standard. Isu tinoudza iyo DBMS peji rekutora kana kuti marekodhi mangani ekusvetuka. Iyo dhatabhesi haingokwanisi kukwirisa chikumbiro chakadaro, sezvo paine ruzivo rushoma pane izvi.

Izvo zvakakoshawo kujekesa kuti iri harisi dambudziko neizwi rakakosha, asi neiyo semantics yemubvunzo. Kune akati wandei mamwe masyntaxes akafanana mudambudziko rawo:

• The offset keyword yakambotaurwa.
• Kuvakwa kwemazwi maviri akakosha muganho [offset] (kunyangwe muganho pachawo hauna kushata).
• Kusefa nemiganhu yakaderera, zvichienderana nenhamba dzemitsara (semuenzaniso, row_number(), rownum, nezvimwewo).

Ese aya mataurirwo anongokuudza kuti mitsetse mingani yekusvetuka, hapana rumwe ruzivo kana mamiriro.

Gare gare mune ino chinyorwa, iyo offset kiyi kiyi inoshandiswa sepfupiso yezvese sarudzo idzi.

Hupenyu husina OFFSET

Zvino ngatimbofungidzira kuti nyika yedu ingadai yakaita sei pasina matambudziko ese aya. Zvinoitika kuti hupenyu husina offset hauna kunyanya kuoma: nekusarudza, unogona kusarudza chete mitsara iyo yatisati tamboona (chinyorwa: kureva, iyo yakanga isiri pane peji yapfuura), uchishandisa mamiriro mune iyo.

Muchiitiko ichi, tinotanga kubva pane izvo zvinosarudzwa zvinoitwa pane yakarongerwa seti (yakanaka yekare kurongeka ne). Sezvo isu tine seti yakarairwa, tinogona kushandisa sefa iri nyore kuwana chete iyo data iri kuseri kwerekodhi rekupedzisira peji yapfuura:

    SELECT ...
    FROM ...
    WHERE ...
    AND id < ?last_seen_id
    ORDER BY id DESC
    FETCH FIRST 10 ROWS ONLY

Ndiyo yose musimboti nzira iyi. Ehe, zvinhu zvinowedzera kunakidza pakurongedza nemakoramu mazhinji, asi pfungwa yacho ichiri yakafanana. Zvakakosha kuziva kuti chigadzirwa ichi chinoshanda kune vakawanda NoSQL-zvisarudzo.

Iyi nzira inonzi search method kana keyset pagination. Inogadzirisa dambudziko rinoyangarara remhedzisiro (chinyorwa: mamiriro nekunyora pakati pepeji inoverengwa yakatsanangurwa kare) uye, hongu, izvo zvatinoda isu tese, zvinoshanda nekukurumidza uye zvakatsiga kupfuura iyo yekare offset. Kugadzikana kuri muchokwadi chekuti chikumbiro chekugadzirisa nguva haina kuwedzera mukuenzana nenhamba yetafura yakakumbirwa (chinyorwa: kana iwe uchida kudzidza zvakawanda nezve basa remaitiro akasiyana epagination, unogona. tarisa kuburikidza nemharidzo yemunyori. Iwe unogona zvakare kuwana mabhenji ekufananidza enzira dzakasiyana ipapo).

Imwe yemasiraidhi anotaura nezvazvokuti pagination nemakiyi, hongu, haasi ane masimba ese - ane painogumira. Chinonyanya kukosha ndechekuti haakwanise kuverenga mapeji asina kurongeka (chinyorwa: zvisingaenderani). Zvisinei, munguva yekusingaperi kupeta (chinyorwa: pamucheto wepamberi), iyi haisi dambudziko rakadaro. Kudoma nhamba yepeji yekudzvanya isarudzo yakaipa muUI dhizaini zvakadaro (chinyorwa: maonero emunyori wechinyorwa).

Zvakadini nemidziyo?

Pagination pamakiyi kazhinji haina kukodzera nekuda kwekushaikwa kwerutsigiro rwechishandiso cheiyi nzira. Mazhinji maturusi ekusimudzira, kusanganisira akasiyana masisitimu, haakubvumidze iwe kuti usarudze chaizvo kuti pagination ichaitwa sei.

Mamiriro acho akawedzerwa nenyaya yekuti nzira yakatsanangurwa inoda kutsigirwa kwekupedzisira-kusvika-kumagumo mumatekinoroji anoshandiswa - kubva kuDBMS kusvika pakuitwa kwechikumbiro cheAJAX mubrowser nekupuruzira kusingaperi. Panzvimbo pekutsanangura nhamba yepeji chete, iwe zvino unofanirwa kutsanangura seti yemakiyi emapeji ese kamwechete.

Nekudaro, iyo nhamba yezvimiro zvinotsigira pagination pamakiyi iri kukura zvishoma nezvishoma. Hezvino izvo zvatinazvo panguva ino:

• jOOQ yeJava;
• order_query for Ruby;
• chunkator и Django Infinite Scroll Pagination for Django;
• SQL Alchemy sqlakeyset zvePython;
• kupisa-kuramba - API maitiro ekuita kweJPA;
• DBIx::Kirasi::Wrapper for Perl;
• Massive.js, mepu yeNode.js Keyset Documentation.

(Cherechedza: mamwe malink akabviswa nekuda kwekuti panguva yekushandura mamwe maraibhurari akange asati avandudzwa kubva 2017-2018. Kana uchifarira, unogona kutarisa kwakabva.)

Panguva ino ndipo panodiwa rubatsiro rwako. Kana iwe ukagadzira kana kutsigira chimiro chinoita chero kushandiswa kwepeji, saka ini ndinobvunza, ndinokukurudzira, ndinokuteterera kuti upe rutsigiro rwekuzvarwa pagination pamakiyi. Kana uine mibvunzo kana uchida rubatsiro, ndichafara kukubatsira (forum, Twitter, contact form) (chinyorwa: kubva pane zvakaitika kwandiri naMarcus, ndinogona kutaura kuti anonyatsofarira kuparadzira nyaya iyi).

Kana iwe ukashandisa yakagadzirira-yakagadzirwa mhinduro iwe yaunofunga kuti yakakodzera kuve nerutsigiro rwepagination nemakiyi, gadzira chikumbiro kana kutopa mhinduro yakagadzirira, kana zvichibvira. Iwe unogona zvakare kubatana nechinyorwa ichi.

mhedziso

Chikonzero nei nzira yakapfava uye inobatsira senge kupeta nemakiyi isina kupararira hakusi kuti zvakaoma kuita zvehunyanzvi kana kuti zvinoda chero kuedza kukuru. Chikonzero chikuru ndechekuti vazhinji vakajaira kuona nekushanda neoffset - iyi nzira inorairwa nechiyero pachayo.

Nekuda kweizvozvo, vashoma vanhu vanofunga nezve kushandura nzira yekuenda kune mhedziso, uye nekuda kweizvi, rubatsiro rwechishandiso kubva kuhurongwa nemaraibhurari huri kukura zvisina kunaka. Naizvozvo, kana iyo pfungwa uye chinangwa che offset-yemahara pagination chiri padyo newe, batsira kuparadzira!

Source: https://use-the-index-luke.com/no-offset
Munyori: Markus Winand

Source: www.habr.com