Moni nonse! Ndine wopanga kumbuyo ndikulemba ma microservices ku Java + Spring. Ndimagwira ntchito m'modzi mwamagulu opanga zinthu zamkati ku Tinkoff.
Mu gulu lathu, funso lokonzekera mafunso mu DBMS nthawi zambiri limabuka. Nthawi zonse mumafuna kukhala othamanga pang'ono, koma simungathe kupitilira ndi ma index opangidwa mwanzeru - muyenera kuyang'ana njira zina zogwirira ntchito. M'modzi mwamaulendo awa pa intaneti posaka kukhathamiritsa koyenera mukamagwira ntchito ndi nkhokwe, ndapeza , wolemba wa SQL Performance Explained. Uwu ndiye mtundu wosowa wabulogu womwe mutha kuwerenga zolemba zonse motsatana.
Ndikufuna kumasulira nkhani yaifupi ya Marcus kwa inu. Itha kutchedwa kuti ndi manifesto yomwe ikufuna kukopa chidwi zakale, komabe vuto lofunikira la magwiridwe antchito molingana ndi muyezo wa SQL.
M'malo ena ndidzawonjezera wolemba ndi mafotokozedwe ndi ndemanga. Nditchula malo onsewa kuti "pafupifupi." kuti mumve zambiri
Mawu oyamba ochepa
Ndikuganiza kuti anthu ambiri amadziwa momwe zimavutira komanso pang'onopang'ono kugwira ntchito ndi tsamba lomwe limasankha kudzera pa offset. Kodi mumadziwa kuti itha kusinthidwa mosavuta ndi kapangidwe koyenera?
Chifukwa chake, mawu osakira amauza nkhokwe kuti mudumphe zolemba zoyamba za pempholo. Komabe, nkhokweyo ikufunikabe kuwerenga zolemba zoyamba za n kuchokera ku disk, mu dongosolo lomwe laperekedwa (zindikirani: gwiritsani ntchito kusanja ngati zatchulidwa), ndipo pokhapokha pamene zidzatheka kubwezeretsa zolemba kuchokera ku n + 1 kupita mtsogolo. Chochititsa chidwi kwambiri ndi chakuti vuto siliri mu kukhazikitsidwa kwapadera mu DBMS, koma mu tanthauzo loyambirira molingana ndi muyezo:
β¦mizere imasanjidwa molingana ndi ndiyeno kuchepetsedwa poponya chiwerengero cha mizere yotchulidwa mu kuyambira pachiyambiβ¦
-SQL:2016, Part 2, 4.15.3 Derived tables (zindikirani: panopa amagwiritsidwa ntchito kwambiri)
Mfundo yofunikira apa ndikuti kuchotsera kumatenga gawo limodzi - kuchuluka kwa zolemba kuti mulumphe, ndi momwemo. Potsatira tanthauzo ili, DBMS ikhoza kungotenga zolemba zonse ndikutaya zosafunikira. Mwachiwonekere, kutanthauzira uku kwa offset kumatikakamiza kuchita ntchito yowonjezera. Ndipo zilibe kanthu kaya ndi SQL kapena NoSQL.
Kupweteka pang'ono chabe
Mavuto ndi offset samatha pamenepo, ndipo chifukwa chake. Ngati, pakati pa kuwerenga masamba awiri a data kuchokera ku disk, ntchito ina imayika mbiri yatsopano, chidzachitike ndi chiyani pamenepa?
Pamene offset imagwiritsidwa ntchito kudumpha zolemba kuchokera pamasamba am'mbuyomu, powonjezera mbiri yatsopano pakati pa zowerengedwa zamasamba osiyanasiyana, mutha kupeza zobwereza (zindikirani: izi ndizotheka tikamawerenga tsamba ndi tsamba pogwiritsa ntchito dongosolo pomanga, ndiye mkati mwazotulutsa zathu zitha kupeza cholowa chatsopano).
Chithunzichi chikuwonetseratu izi. Maziko amawerenga zolemba zoyamba za 10, pambuyo pake zolemba zatsopano zimayikidwa, zomwe zimachotsa zolemba zonse zowerengedwa ndi 1. Kenaka maziko amatenga tsamba latsopano kuchokera ku zolemba za 10 ndipo sayamba kuchokera ku 11, monga momwe ziyenera kukhalira, koma kuchokera ku 10, kubwereza zolemba izi. Palinso zolakwika zina zomwe zimagwirizanitsidwa ndi kugwiritsa ntchito mawuwa, koma izi ndizofala kwambiri.
Monga tadziwira kale, izi sizovuta za DBMS kapena kukhazikitsa kwawo. Vuto ndi kufotokozera zamitundu molingana ndi muyezo wa SQL. Timauza DBMS kuti ndi tsamba liti loti mutenge kapena marekodi angati oti mulumphe. Nawonsokasi sikutha kukhathamiritsa pempho loterolo, chifukwa pali chidziwitso chochepa pa izi.
Ndikoyeneranso kufotokozera kuti ili si vuto ndi mawu osakira, koma ndi semantics ya funsolo. Pali ma syntaxes enanso angapo omwe ali ofanana muzovuta zawo:
- Mawu osakira a offset ndi monga tanena kale.
- Kupanga malire a mawu osakira awiri [kuchepetsa] (ngakhale malirewo siwoyipa kwambiri).
- Kusefa ndi malire otsika, kutengera manambala amizere (mwachitsanzo, row_nambala(), rownum, etc.).
Mawu onsewa amangokuuzani mizere ingati yomwe muyenera kudumpha, popanda zina zowonjezera kapena nkhani.
Pambuyo pake m'nkhaniyi, mawu ofunikira a offset amagwiritsidwa ntchito ngati chidule cha zosankha zonsezi.
Moyo wopanda OFFSET
Tsopano tiyeni tiyerekeze kuti dziko lathu likanakhala lotani popanda mavuto onsewa. Zikuwonekeratu kuti moyo wopanda malire siwovuta kwambiri: ndikusankha mutha kusankha mizere yomwe sitinayiwonepo (zindikirani: ndiye kuti, zomwe sizinali patsamba lapitalo), pogwiritsa ntchito momwe zilili.
Pachifukwa ichi, tikuyamba kuchokera ku mfundo yakuti zosankhidwa zimachitidwa pa ndondomeko yolamulidwa (dongosolo lakale lakale). Popeza tili ndi seti yoyitanitsa, titha kugwiritsa ntchito fyuluta yosavuta kuti tingopeza zomwe zili kumbuyo kwa mbiri yomaliza yatsamba lapitalo:
SELECT ...
FROM ...
WHERE ...
AND id < ?last_seen_id
ORDER BY id DESC
FETCH FIRST 10 ROWS ONLYNdilo mfundo yonse ya njira iyi. Zoonadi, zinthu zimakhala zosangalatsa kwambiri posankha ndi mizati yambiri, koma lingaliro likadali lomwelo. Ndikofunika kuzindikira kuti mapangidwe awa akugwiritsidwa ntchito kwa ambiri -zisankho.
Njira imeneyi imatchedwa search method kapena keyset pagination. Imathetsa vuto la zotsatira zoyandama (zindikirani: zomwe zili ndi zolemba pakati pa tsamba zomwe zawerengedwa kale) ndipo, zowona, zomwe tonse timakonda, zimagwira ntchito mwachangu komanso mokhazikika kuposa momwe zimakhalira. Kukhazikika kwagona pa mfundo yakuti nthawi yopempha yopempha sikuwonjezeka molingana ndi chiwerengero cha tebulo lomwe lafunsidwa (chidziwitso: ngati mukufuna kuphunzira zambiri za ntchito ya njira zosiyanasiyana zapagination, mukhoza . Mukhozanso kupeza zizindikiro zofananira za njira zosiyanasiyana kumeneko).
Chimodzi mwazithunzi kuti pagination ndi makiyi, ndithudi, si wamphamvuyonse - ili ndi malire ake. Chofunikira kwambiri ndichakuti alibe luso lowerenga masamba osasintha (chidziwitso: mosagwirizana). Komabe, m'nthawi ya kupukusa kosatha (chidziwitso: kutsogolo), izi sizovuta. Kutchula nambala yatsamba kuti mudina ndi chisankho cholakwika mu kapangidwe ka UI (chidziwitso: malingaliro a wolemba nkhaniyo).
Nanga zida?
Pagination pa makiyi nthawi zambiri sikoyenera chifukwa chosowa zida zothandizira njira iyi. Zida zambiri zachitukuko, kuphatikiza magawo osiyanasiyana, sizikulolani kuti musankhe ndendende momwe kusaka kwamitundu kudzachitikira.
Zinthu zimakulitsidwa chifukwa chakuti njira yofotokozedwayo imafuna chithandizo chakumapeto-kumapeto mu matekinoloje omwe amagwiritsidwa ntchito - kuchokera ku DBMS kupita kukuchita pempho la AJAX mu msakatuli ndi kupukusa kosatha. M'malo mongotchula nambala yatsamba, tsopano muyenera kufotokoza makiyi amasamba onse nthawi imodzi.
Komabe, kuchuluka kwa ma framework omwe amathandizira pagination pamakiyi akukula pang'onopang'ono. Nazi zomwe tili nazo pakadali pano:
- kwa Java;
- kwa Ruby;
- ΠΈ kwa Django;
- za Python;
- - API yofunikira pakukhazikitsa kwa JPA;
- kwa Perl;
- , mapper a Node.js .
(Zindikirani: maulalo ena adachotsedwa chifukwa pa nthawi yomasulira malaibulale ena anali asanasinthidwe kuyambira 2017-2018. Ngati mukufuna, mutha kuyang'ana koyambira.)
Ndi panthawiyi pomwe thandizo lanu likufunika. Ngati mupanga kapena kuthandizira chimango chomwe chimagwiritsa ntchito mtundu uliwonse wamitundu, ndiye ndikupempha, ndikupemphani, ndikupemphani kuti mupereke chithandizo chamtundu wamitundu pamakiyi. Ngati muli ndi mafunso kapena mukufuna thandizo, ndidzakhala wokondwa kukuthandizani (, , ) (chidziwitso: kuchokera ku zomwe ndinakumana nazo ndi Marcus, ndinganene kuti ali wokondwa kwambiri kufalitsa nkhaniyi).
Ngati mugwiritsa ntchito mayankho okonzeka omwe mukuganiza kuti ndi oyenera kuthandizidwa ndi makiyi a pagination, pangani pempho kapena perekani yankho lokonzekera, ngati kuli kotheka. Mukhozanso kulumikiza nkhaniyi.
Pomaliza
Chifukwa chomwe njira yosavuta komanso yothandiza ngati kuyika makiyi ndi makiyi sikufalikira sikuti ndizovuta kukhazikitsa mwaukadaulo kapena kumafuna khama lililonse. Chifukwa chachikulu ndikuti ambiri amazolowera kuwona ndikugwira ntchito ndi offset - njira iyi imayendetsedwa ndi muyezo womwewo.
Chotsatira chake, ndi anthu ochepa omwe amalingalira za kusintha njira yachikunja, ndipo chifukwa cha izi, thandizo la zida kuchokera kumapangidwe ndi malaibulale zikukula bwino. Choncho, ngati lingaliro ndi cholinga cha offset-free pagination chiri pafupi ndi inu, thandizani kufalitsa!
Source:
Wolemba: Markus Winand
Source: www.habr.com