Ripotilo likuwonetsa njira zina zomwe zimalola kuwunika momwe mafunso a SQL amagwirira ntchito pomwe pali mamiliyoni aiwo patsiku, ndipo pali mazana a maseva a PostgreSQL omwe amawunikidwa.
Ndi njira ziti zaukadaulo zomwe zimatilola kuti tizitha kukonza bwino kuchuluka kwa zidziwitso zotere, ndipo izi zimapangitsa moyo wa wopanga mapulogalamu kukhala wosavuta bwanji?
Ndani ali ndi chidwi? kusanthula mavuto enieni ndi njira zosiyanasiyana zokometsera Mafunso a SQL ndikuthetsa zovuta za DBA mu PostgreSQL - muthanso pa mutu uwu.
Dzina langa ndine Kirill Borovikov, ndikuyimira . Makamaka, ndimagwira ntchito ndi nkhokwe pakampani yathu.
Lero ndikuuzani momwe timakwaniritsira mafunso, pamene simukufunika "kusankha" momwe funso limodzi limagwirira ntchito, koma kuthetsa vuto lalikulu. Pamene pali mamiliyoni opempha, ndipo muyenera kupeza njira zothetsera vuto lalikulu ili.
Mwambiri, Tensor kwa miliyoni makasitomala athu ndi : malo ochezera a pa Intaneti, njira zoyankhulirana ndi mavidiyo, zoyendetsera zikalata zamkati ndi kunja, machitidwe owerengera ndalama ndi malo osungiramo katundu, ... Ndiko kuti, "mega-combine" yotereyi yoyendetsera bizinesi yophatikizika, momwe muli zosiyana ndi 100 ntchito zamkati.
Kuonetsetsa kuti onse akugwira ntchito ndikukula bwino, tili ndi malo 10 achitukuko m'dziko lonselo, okhala ndi zambiri 1000 opanga.
Takhala tikugwira ntchito ndi PostgreSQL kuyambira 2008 ndipo tasonkhanitsa zambiri zomwe timapanga - deta yamakasitomala, mawerengero, kusanthula, deta kuchokera kuzinthu zakunja - kuposa 400TB. Pali ma seva pafupifupi 250 omwe akupanga okha, ndipo palimodzi pali pafupifupi ma seva 1000 omwe timawayang'anira.
SQL ndi chilankhulo chofotokozera. Simumalongosola "momwe" chinachake chiyenera kugwira ntchito, koma "zomwe" mukufuna kukwaniritsa. DBMS imadziwa bwino momwe mungapangire JOIN - momwe mungalumikizire matebulo anu, zomwe mungakhazikitse, zomwe zingadutse mndandanda, zomwe sizingachitike ...
Ma DBMS ena amavomereza malingaliro: "Ayi, gwirizanitsani matebulo awiriwa pamzere wotere," koma PostgreSQL sangathe kuchita izi. Umu ndi momwe otsogolera otsogola amaganizira: "Tili bwino kumalizitsa zowongolera mafunso m'malo molola opanga kugwiritsa ntchito malingaliro amtundu wina."
Koma, ngakhale kuti PostgreSQL salola "kunja" kudzilamulira, imalola mwangwiro onani zimene zikuchitika mkati mwakemukamayendetsa funso lanu, komanso komwe kuli ndi zovuta.
Nthawi zambiri, ndizovuta zanji zomwe wopanga [ku DBA] nthawi zambiri amabwera nazo? “Apa tinakwaniritsa pempholo, ndipo zonse zili mochedwa ndi ife, zonse zikulendewera, chinachake chikuchitika... Vuto lina lake!”
Zifukwa zimakhala zofanana nthawi zonse:
- algorithm yamafunso osakwanira
Wopanga Mapulogalamu: "Tsopano ndikumupatsa matebulo 10 mu SQL kudzera JOIN ..." - ndipo akuyembekeza kuti mikhalidwe yake "idzamasulidwa" mozizwitsa ndipo adzapeza chilichonse mwachangu. Koma zozizwitsa sizichitika, ndipo machitidwe aliwonse omwe ali ndi kusiyana kotere (matebulo 10 mu FROM imodzi) amapereka nthawi zonse zolakwika. [] - ziwerengero zosafunikira
Mfundoyi ndiyofunika kwambiri makamaka kwa PostgreSQL, pamene "mutsanulira" deta yaikulu pa seva, pangani pempho, ndipo "amagonana" piritsi lanu. Chifukwa dzulo panali zolemba za 10 mmenemo, ndipo lero pali 10 miliyoni, koma PostgreSQL sinadziwebe za izi, ndipo tiyenera kunena za izo. [] - "plug" pa zothandizira
Mwayika database yayikulu komanso yodzaza kwambiri pa seva yofooka yomwe ilibe disk yokwanira, kukumbukira, kapena purosesa. Ndipo ndizo zonse ... Penapake pali denga la ntchito pamwamba lomwe simungathe kulumphanso. - kutseka
Iyi ndi mfundo yovuta, koma ndiyofunika kwambiri pamafunso osiyanasiyana osintha (INSERT, UPDATE, TELETE) - uwu ndi mutu waukulu wosiyana.
Kupeza dongosolo
...Ndi zina zonse ife ndikufunika dongosolo! Tiyenera kuwona zomwe zikuchitika mkati mwa seva.
Dongosolo lokonzekera mafunso a PostgreSQL ndi mtengo wamafunso ophatikizira pamawu oyimira. Ndizofanana ndi algorithm yomwe, chifukwa cha kusanthula kwa wokonza mapulani, idapezeka kuti ndiyothandiza kwambiri.
Node iliyonse yamtengo ndi ntchito: kubweza deta kuchokera patebulo kapena index, kupanga bitmap, kujowina matebulo awiri, kujowina, kudutsana, kapena kupatula zosankha. Kufunsa kumaphatikizapo kuyenda m'mbali mwa mtengo uwu.
Kuti mupeze dongosolo lamafunso, njira yosavuta ndiyo kuchita mawuwo EXPLAIN. Kuti mukhale ndi mawonekedwe enieni, ndiye kuti, kuyankha funso pamunsi - EXPLAIN (ANALYZE, BUFFERS) SELECT ....
Gawo loyipa: mukamayendetsa, zimachitika "pano ndi pano", ndiye kuti ndizoyenera kuwongolera komweko. Ngati mutenga seva yodzaza kwambiri yomwe ili pansi pakusintha kwamphamvu kwa data, ndipo mukuwona: "O! Pano tili ndi kuphedwa pang'onopang'onosya pempho." Theka la ola, ola lapitalo - pamene mukuyenda ndikupeza pempholi kuchokera ku zipika, ndikubwezeretsanso ku seva, deta yanu yonse ndi ziwerengero zinasintha. Mumayendetsa kuti mukonze - ndipo imayenda mwachangu! Ndipo inu simungakhoze kumvetsa chifukwa, chifukwa anali pang'onopang'ono.
Kuti mumvetsetse zomwe zidachitika ndendende panthawi yomwe pempholo lidaperekedwa pa seva, anthu anzeru adalemba . Imapezeka pafupifupi pafupifupi magawo onse a PostgreSQL omwe amapezeka, ndipo amatha kungoyatsidwa mufayilo yosinthira.
Ngati izindikira kuti pempho lina likuyenda motalika kuposa malire omwe mudawawuza, limatero "chithunzi" cha dongosolo la pempholi ndikuzilemba pamodzi mu chipikacho.
Chilichonse chikuwoneka bwino tsopano, timapita ku chipikacho ndikuwona kumeneko ... [textfootcloth]. Koma sitinganene chilichonse chokhudza izi, kupatula kuti ndi dongosolo labwino kwambiri chifukwa zidatenga 11ms kuti zitheke.
Chilichonse chikuwoneka bwino - koma palibe chomwe chimadziwika bwino chomwe chinachitika. Kupatula nthawi wamba, sitiwona chilichonse. Chifukwa kuyang'ana "mwanawankhosa" wotero wa mawu osavuta nthawi zambiri sikuwoneka.
Koma ngakhale sizowoneka bwino, ngakhale zitakhala zovuta, pali zovuta zambiri:
- Node ikuwonetsa kuchuluka kwazinthu zamtundu uliwonse pansi pa iye. Ndiye kuti, simungangodziwa kuti ndi nthawi yochuluka bwanji yomwe idagwiritsidwa ntchito pa Index Scan ngati pali zisa zake. Tiyenera kuyang'ana mwamphamvu kuti tiwone ngati pali "ana" ndi zosintha zokhazikika, ma CTE mkati - ndikuchotsa zonsezi "m'malingaliro athu".
- Mfundo yachiwiri: nthawi yomwe ikuwonetsedwa pa node ndi nthawi imodzi yochitira node. Ngati node iyi idachitidwa chifukwa cha, mwachitsanzo, kuzungulira patebulo kangapo kangapo, ndiye kuti kuchuluka kwa malupu-kuzungulira kwa node iyi-kuwonjezeka mu dongosolo. Koma nthawi ya kuphedwa kwa atomiki yokha imakhalabe chimodzimodzi malinga ndi dongosolo. Ndiko kuti, kuti mumvetse kutalika kwa mfundo imeneyi, muyenera kuchulukitsa chinthu chimodzi ndi chimzake - kachiwiri, "mumutu mwanu."
Zikatero, mvetsetsani "Kodi ulalo wofooka kwambiri ndi ndani?" pafupifupi zosatheka. Chifukwa chake, ngakhale opanga okha amalemba mu "bukhu" kuti "Kumvetsetsa dongosolo ndi luso lomwe liyenera kuphunziridwa, chidziwitso ...".
Koma tili ndi opanga 1000, ndipo simungathe kufotokozera izi kwa aliyense wa iwo. Ine, inu, akudziwa, koma wina kumeneko sakudziwanso. Mwina aphunzira, kapena ayi, koma akufunika kugwira ntchito tsopano - ndipo angachipeze kuti chokumana nacho ichi?
Konzani zowonera
Choncho, tinazindikira kuti kuti tithane ndi mavutowa, tifunika mawonekedwe abwino a dongosolo.
Choyamba tinadutsa "msika" - tiyeni tiwone pa intaneti kuti tiwone zomwe zilipo.
Koma zidapezeka kuti pali mayankho ochepa "amoyo" omwe akutukuka pang'ono - kwenikweni, imodzi yokha: ndi Hubert Lubaczewski. Mukalowa mugawo la "zakudya" mawu oyimira dongosolo, amakuwonetsani tebulo lomwe lili ndi data yosankhidwa:
- node nthawi yake yopangira
- nthawi yanthawi yonse ya subtree
- kuchuluka kwa zolemba zomwe zidatengedwa zomwe zimayembekezeredwa mowerengera
- node thupi lokha
Ntchitoyi ilinso ndi kuthekera kogawana maulalo zakale. Munaponya mapulani anu mmenemo ndipo munati: "Hei, Vasya, nayi ulalo, pali cholakwika pamenepo."
Koma palinso mavuto ang'onoang'ono.
Choyamba, kuchuluka kwakukulu kwa "copy-paste". Mumatenga chidutswa cha chipikacho, ndikuchiyika mmenemo, ndipo kachiwiri, ndi kachiwiri.
Chachiwiri, palibe kusanthula kuchuluka kwa deta yowerengedwa - ma buffer omwewo omwe amatuluka EXPLAIN (ANALYZE, BUFFERS), sitikuziwona apa. Iye sakudziwa kumasula iwo, kuwamvetsa ndi kugwira nawo ntchito. Mukamawerenga zambiri ndikuzindikira kuti mutha kugawa molakwika diski ndi posungira kukumbukira, chidziwitsochi ndi chofunikira kwambiri.
Mfundo yachitatu yolakwika ndi chitukuko chofooka kwambiri cha polojekitiyi. Zochita ndizochepa kwambiri, ndizabwino ngati kamodzi miyezi isanu ndi umodzi iliyonse, ndipo code ili ku Perl.
Koma zonsezi ndi "nyimbo", titha kukhala ndi izi mwanjira ina, koma pali chinthu chimodzi chomwe chidatichotsa pautumikiwu. Izi ndi zolakwika pakuwunika kwa Common Table Expression (CTE) ndi ma node osiyanasiyana monga InitPlan/SubPlan.
Ngati mukukhulupirira chithunzichi, ndiye kuti nthawi yonse yophatikizika ya node iliyonse ndi yayikulu kuposa nthawi yonse yofunsidwa. Ndi zophweka - nthawi yobadwa ya CTE iyi sinachotsedwe pa CTE Scan node. Chifukwa chake, sitikudziwanso yankho lolondola lautali wautali wa CTE womwe udatenga.
Kenako tinazindikira kuti inali nthawi yoti tilembe zathu - hurray! Wopanga aliyense amati: "Tsopano tilemba zathu, zikhala zophweka kwambiri!"
Tidatenga zofananira zamawebusayiti: maziko ozikidwa pa Node.js + Express, adagwiritsa ntchito Bootstrap ndi D3.js pazithunzi zokongola. Ndipo zoyembekeza zathu zinali zomveka bwino - tinalandira chitsanzo choyamba m'masabata a 2:
- makonda plan parser
Ndiye kuti, tsopano titha kusiyanitsa dongosolo lililonse kuchokera kwa omwe amapangidwa ndi PostgreSQL. - kusanthula kolondola kwa node zamphamvu - CTE Scan, InitPlan, SubPlan
- kusanthula kwa kugawa kwa ma buffers - kumene masamba a data amawerengedwa kuchokera pamtima, kuchokera ku cache yakomweko, komwe kuchokera ku disk
- ndinamvetsetsa
Kuti musakhale "kukumba" zonsezi mu chipika, koma kuti muwone "chiyanjano chofooka" pomwepo pachithunzichi.
Tili ndi chonga chonga ichi, ndikuwunikira kwa mawu akuphatikizidwa. Koma kawirikawiri opanga athu sagwiranso ntchito ndi chiwonetsero chonse cha dongosolo, koma ndi chachifupi. Kupatula apo, tagawa kale manambala onse ndikuponyera kumanzere ndi kumanja, ndipo pakati tidangosiya mzere woyamba, ndi node yamtundu wanji: CTE Scan, CTE generation kapena Seq Scan malinga ndi chizindikiro china.
Ichi ndiye choyimira chachidule chomwe timachitcha template ya plan.
Ndi chiyani chinanso chomwe chingakhale chothandiza? Zingakhale zabwino kuwona gawo la nthawi yathu yonse yomwe yaperekedwa kwa mfundo - ndikungo "kumamatira" kumbali. tchati cha pie.
Timaloza pa node ndikuwona - zidapezeka kuti Seq Scan idatenga nthawi yosachepera kotala la nthawi yonse, ndipo 3/4 yotsalayo idatengedwa ndi CTE Scan. Zowopsa! Ichi ndi cholembera chaching'ono chokhudza "mlingo wamoto" wa CTE Scan ngati muwagwiritsa ntchito pamafunso anu. Sali othamanga kwambiri - ndi otsika ngakhale kusanthula patebulo nthawi zonse.
Koma kawirikawiri zithunzi zoterezi zimakhala zosangalatsa, zovuta kwambiri, pamene nthawi yomweyo timaloza pagawo ndikuwona, mwachitsanzo, kuti nthawi yoposa theka la nthawi Seq Scan "adadya". Komanso, panali mtundu wina wa Zosefera mkati, zolemba zambiri zidatayidwa molingana ndi izo ... Mutha kuponyera mwachindunji chithunzichi kwa wopanga mapulogalamuwo ndikuti: "Vasya, zonse ndi zoyipa pano kwa inu! Zindikirani, onani - chinachake chalakwika!"
Mwachidziwitso, panali "ma rakes" ena okhudzidwa.
Chinthu choyamba chimene tinapeza chinali vuto lozungulira. Nthawi ya node ya munthu aliyense mu ndondomekoyi ikuwonetsedwa ndi kulondola kwa 1 μs. Ndipo kuchuluka kwa ma node kupitilira, mwachitsanzo, 1000 - pambuyo pophedwa PostgreSQL idagawika "molondola", ndiye tikamawerengera timapeza nthawi yonse "pakati pa 0.95ms ndi 1.05ms". Kuwerengera kumapita ku ma microseconds, zili bwino, koma pamene ili kale [mamilimita] masekondi, muyenera kuganizira izi pamene "mumasula" zothandizira ku mfundo za "amene adadya ndalama zingati".
Mfundo yachiwiri, yovuta kwambiri, ndikugawa kwazinthu (ma buffers) pakati pa ma node osinthika. Izi zidatiwonongera masabata awiri oyamba a prototype kuphatikiza milungu ina ina.
Ndikosavuta kupeza vuto lamtunduwu - timachita CTE ndipo timati timawerengapo china chake. M'malo mwake, PostgreSQL ndi "yanzeru" ndipo siwerenga chilichonse mwachindunji pamenepo. Ndiye ife timatenga zolembedwa zoyamba kuchokera kwa izo, ndipo kwa izo zana limodzi ndi loyamba kuchokera ku CTE yomweyo.
Timayang'ana dongosolo ndikumvetsetsa - ndizodabwitsa, tili ndi ma buffers 3 (masamba a data) "odyedwa" mu Seq Scan, 1 ina mu CTE Scan, ndi 2 enanso mu CTE Scan yachiwiri. Ndiko kuti, ngati tingophatikiza zonse, tipeza 6, koma pa piritsi timangowerenga 3! CTE Scan siwerenga chilichonse kulikonse, koma imagwira ntchito mwachindunji ndi kukumbukira. Ndiko kuti, pali chinachake cholakwika apa!
M'malo mwake, zikuwoneka kuti apa pali masamba onse a 3 a data omwe adafunsidwa ku Seq Scan, woyamba 1 adafunsa 1st CTE Scan, kenako 2nd, ndi enanso 2 adawerengedwa kwa iye. Masamba a 3 adawerengedwa, osati 6.
Ndipo chithunzichi chinatitsogolera ife kumvetsetsa kuti kuchitidwa kwa ndondomeko sikulinso mtengo, koma kungoti mtundu wina wa acyclic graph. Ndipo ife tiri ndi chithunzi chonga ichi, kotero kuti ife timvetse “chomwe chinachokera kuti poyamba.” Ndiko kuti, apa tinapanga CTE kuchokera pg_class, ndipo tinapempha kawiri, ndipo pafupifupi nthawi yathu yonse inathera panthambi pamene tinapempha nthawi ya 2. N'zoonekeratu kuti kuwerenga 101st kulowa ndi okwera mtengo kwambiri kuposa kungowerenga 1 kulowa piritsi.
Tinapumira kwakanthawi. Iwo anati: “Tsopano, Neo, ukudziwa kung fu! Tsopano zomwe takumana nazo zili pazenera lanu. Tsopano mutha kugwiritsa ntchito."
Kuphatikiza kwa log
Opanga athu 1000 adapumira m'malo. Koma tidamvetsetsa kuti tili ndi ma seva "olimbana" mazana ambiri, ndipo zonsezi "copy-paste" kwa omanga sizothandiza konse. Tinaona kuti tifunika kutolera tokha.
Mwambiri, pali gawo lokhazikika lomwe limatha kusonkhanitsa ziwerengero, komabe, liyeneranso kukhazikitsidwa mu config - izi. . Koma sanatikomere.
Choyamba, imapereka mafunso omwewo pogwiritsa ntchito njira zosiyanasiyana mkati mwa database yomweyo Ma QueryIds osiyanasiyana. Ndiko kuti, ngati mutachita choyamba SET search_path = '01'; SELECT * FROM user LIMIT 1;kenako SET search_path = '02'; ndi pempho lomwelo, ndiye kuti ziwerengero za gawoli zidzakhala ndi zolemba zosiyanasiyana, ndipo sindingathe kusonkhanitsa ziwerengero zambiri makamaka pazochitika za pempholi, popanda kuganizira za ziwembu.
Mfundo yachiwiri imene inatilepheretsa kuigwiritsa ntchito ndi kusowa mapulani. Ndiko kuti, palibe ndondomeko, pali pempho lokha. Timawona zomwe zikucheperachepera, koma sitikumvetsetsa chifukwa chake. Ndipo apa tikubwerera ku vuto la dataset yomwe ikusintha mwachangu.
Ndipo mphindi yomaliza - kusowa "zowona". Ndiko kuti, simungathe kuthana ndi vuto linalake la mafunso - palibe, pali ziwerengero zongophatikizika. Ngakhale ndizotheka kugwira ntchito ndi izi, ndizovuta kwambiri.
Chifukwa chake, tinaganiza zolimbana ndi copy-paste ndikuyamba kulemba wokhometsa.
Wosonkhanitsa amalumikizana kudzera pa SSH, amakhazikitsa kulumikizana kotetezeka kwa seva ndi database pogwiritsa ntchito satifiketi, ndi tail -F "amamamatira" kwa icho mu fayilo ya logi. Kotero mu gawo ili timapeza "galasi" lathunthu la fayilo yonse ya chipika, zomwe seva imapanga. Katundu pa seva pawokha ndi wocheperako, chifukwa sitimayika chilichonse pamenepo, timangowonetsa kuchuluka kwa magalimoto.
Popeza tinali titayamba kale kulemba mawonekedwe mu Node.js, tinapitiriza kulemba osonkhanitsa mmenemo. Ndipo ukadaulo uwu wadzilungamitsa, chifukwa ndizosavuta kugwiritsa ntchito JavaScript kuti mugwiritse ntchito ndi data yolembedwa mofooka, yomwe ndi chipika. Ndipo zomangamanga za Node.js palokha ngati nsanja yakumbuyo imakulolani kuti mugwire ntchito mosavuta ndi ma network, komanso ndi mitsinje iliyonse ya data.
Chifukwa chake, "timatambasula" maulalo awiri: woyamba "kumvera" chipikacho ndikudzitengera tokha, ndipo chachiwiri kufunsa nthawi ndi nthawi. "Koma chipikacho chikuwonetsa kuti chikwangwani chokhala ndi oid 123 chatsekedwa," koma izi sizikutanthauza chilichonse kwa wopanga mapulogalamuwo, ndipo zingakhale bwino kufunsa nkhokwe, "OID = 123 ndi chiyani?" Ndipo chifukwa chake timafunsa nthawi ndi nthawi zomwe sitikudziwa za ife eni.
"Pali chinthu chimodzi chokha chomwe simunachiganizirepo, pali mtundu wa njuchi ngati njovu! .." Tinayamba kupanga dongosolo ili pamene tikufuna kuyang'anira ma seva a 10. Chofunikira kwambiri pakumvetsetsa kwathu, pomwe mavuto ena adabuka omwe anali ovuta kuthana nawo. Koma m'gawo loyamba, tinalandira zana kuti tiwunikire - chifukwa dongosololi linagwira ntchito, aliyense ankafuna, aliyense anali womasuka.
Zonsezi ziyenera kuwonjezeredwa, kusuntha kwa deta ndi kwakukulu komanso kogwira ntchito. M'malo mwake, zomwe timawunika, zomwe titha kuthana nazo, ndizomwe timagwiritsa ntchito. Timagwiritsanso ntchito PostgreSQL ngati yosungirako deta. Ndipo palibe chomwe chimathamanga "kutsanulira" deta mmenemo kuposa woyendetsa COPY Osati pano.
Koma kungoti "kutsanulira" deta si kwenikweni luso lathu. Chifukwa ngati muli ndi zopempha pafupifupi 50k pamphindi pa maseva zana, ndiye kuti izi zimapanga 100-150GB ya zipika patsiku. Choncho, tinayenera "kudula" mosamala maziko.
Choyamba, tinatero kugawanika kwa tsiku, chifukwa, mokulira, palibe amene ali ndi chidwi ndi kugwirizana pakati pa masiku. Kodi zimapanga kusiyana kotani zomwe mudali nazo dzulo, ngati usikuuno mwatulutsa pulogalamu yatsopano - komanso ziwerengero zatsopano.
Kachiwiri, tinaphunzira (anakakamizika) kwambiri, mofulumira kwambiri kulemba ntchito COPY. Ndiko kuti, osati chabe COPYchifukwa ali wachangu kuposa INSERT, ndipo ngakhale mofulumira.
Mfundo yachitatu - ndinayenera kutero kusiya zoyambitsa, motero, ndi makiyi akunja. Ndiko kuti, tilibe kukhulupirika konsekonse. Chifukwa ngati muli ndi tebulo lomwe lili ndi ma FKs, ndipo mumanena muzosungirako kuti "apa pali zolemba zomwe zimatchulidwa ndi FK, mwachitsanzo, ku gulu la zolemba," ndiye mukayiyika, PostgreSQL. alibe kanthu koma momwe angatengere ndikuzichita moona mtima SELECT 1 FROM master_fk1_table WHERE ... ndi chizindikiritso chomwe mukuyesera kuyikapo - kungoyang'ana kuti cholembedwachi chilipo, kuti "musathyole" Chinsinsi chakunja ichi ndikuyika kwanu.
M'malo mwa cholembera chimodzi chopita ku tabulo lolunjika ndi ma index ake, timapeza phindu lowonjezereka la kuŵerenga kuchokera pa matebulo onse omwe amawatchula. Koma sitifunikira izi konse - ntchito yathu ndikulemba momwe tingathere komanso mwachangu momwe tingathere ndi katundu wocheperako. Ndiye FK - pansi!
Mfundo yotsatira ndikuphatikiza ndi hashing. Poyambirira, tidawagwiritsa ntchito m'dawunilodi - pambuyo pake, ndikwabwino kuti nthawi yomweyo, mbiri ikafika, tichite mumtundu wina wa piritsi. "plus one" mu choyambitsacho. Chabwino, ndizosavuta, koma zoipa zomwezo - mumayika mbiri imodzi, koma mumakakamizika kuwerenga ndi kulemba chinachake kuchokera pa tebulo lina. Komanso, sikuti mumangowerenga ndi kulemba, mumachitanso nthawi zonse.
Tsopano yerekezerani kuti muli ndi tebulo momwe mumangowerengera kuchuluka kwa zopempha zomwe zadutsa pagulu linalake: +1, +1, +1, ..., +1. Ndipo inu, kwenikweni, simukusowa izi - ndizotheka sum mu kukumbukira pa wosonkhanitsa ndikutumiza ku database imodzi +10.
Inde, pakakhala zovuta zina, kukhulupirika kwanu "kutha", koma izi ndizovuta kwambiri - chifukwa muli ndi seva yabwinobwino, ili ndi batire mwa wowongolera, muli ndi chipika chogulitsira, chipika pa file system... Mwambiri, sizoyenera. Kutayika kwa zokolola zomwe mumapeza chifukwa choyambitsa / FK sikuyenera kuwononga ndalama zomwe mumapeza.
Ndi chimodzimodzi ndi hashing. Pempho linalake likuwulukira kwa inu, mumawerengera chizindikiritso china kuchokera pamenepo, ndikulemba ku database ndikuwuza aliyense. Chilichonse chili bwino mpaka, panthawi yojambula, munthu wachiwiri amabwera kwa inu yemwe akufuna kulemba chinthu chomwecho - ndipo mumatsekedwa, ndipo izi ndi zoipa kale. Chifukwa chake, ngati mutha kusamutsa m'badwo wa ma ID kwa kasitomala (wachibale ndi database), ndibwino kuchita izi.
Zinali zabwino kwambiri kuti tigwiritse ntchito MD5 kuchokera palemba - pempho, ndondomeko, template, ... Timawerengera pa mbali yosonkhanitsa, ndi "kutsanulira" ID yokonzedwa kale mu database. Kutalika kwa MD5 ndi kugawa kwatsiku ndi tsiku kumatilola kuti tisadere nkhawa za kugunda komwe kungatheke.
Koma kuti tilembe zonsezi mwachangu, tidafunikira kusintha momwe timajambulira.
Kodi mumalemba bwanji zambiri? Tili ndi mtundu wina wa deta, timagawaniza m'matebulo angapo, ndiyeno KOPERANI - choyamba mpaka choyamba, kenako chachiwiri, chachitatu ... Ndizovuta, chifukwa tikuwoneka kuti tikulemba mtsinje umodzi wa deta mu masitepe atatu. motsatizana. Zosasangalatsa. Kodi zingatheke mofulumira? Mutha!
Kuti tichite izi, ndikwanira kungowola mitsinje iyi limodzi ndi mnzake. Zikuoneka kuti tili ndi zolakwika, zopempha, ma templates, zotchinga, ... zowuluka mu ulusi wosiyana - ndipo timalemba zonse mofanana. Zokwanira pa izi sungani njira ya COPY yotseguka nthawi zonse patebulo lililonse lomwe mukufuna.
Ndiko kuti, kwa wosonkhanitsa pali nthawi zonse mtsinje, momwe ndingalembemo zomwe ndikufuna. Koma kuti databaseyo iwone izi, ndipo wina asamadikire kudikirira kuti izi zilembedwe, KOPI iyenera kusokonezedwa pakapita nthawi. Kwa ife, nthawi yabwino kwambiri inali pafupi 100ms - timatseka ndipo nthawi yomweyo timatsegulanso patebulo lomwelo. Ndipo ngati tilibe kutulutsa kumodzi kokwanira pamapiri ena, ndiye kuti timaphatikizana mpaka malire ena.
Kuphatikiza apo, tidapeza kuti pazambiri zotere, kuphatikiza kulikonse, zolemba zikasonkhanitsidwa m'magulu, ndizoyipa. Classic zoipa ndi INSERT ... VALUES ndi zolemba zina 1000. Chifukwa panthawiyo muli ndi chiwongoladzanja cholembera pawailesi, ndipo wina aliyense amene akuyesera kulemba chinachake pa disk akuyembekezera.
Kuti muchotse zolakwika zotere, musaphatikizepo chilichonse, osabisa chilichonse. Ndipo ngati buffering to disk ikuchitika (mwamwayi, Stream API mu Node.js imakulolani kuti mudziwe) - sinthani kugwirizana uku. Mukalandira chochitika kuti ndi chaulere kachiwiri, chilembereni kuchokera pamzere womwe mwasonkhanitsa. Ndipo pamene ili yotanganidwa, tengani yaulere yotsatira kuchokera padziwe ndikulembera.
Tisanayambe njira iyi yojambulira deta, tinali ndi pafupifupi 4K kulemba ops, ndipo motere tinachepetsa katunduyo nthawi zinayi. Tsopano akuliranso ka 4 chifukwa cha nkhokwe zatsopano zoyang'aniridwa - mpaka 6MB/s. Ndipo tsopano timasunga zipika kwa miyezi 100 yapitayi mu voliyumu pafupifupi 3-10TB, tikuyembekeza kuti m'miyezi itatu yokha wopanga aliyense adzatha kuthetsa vuto lililonse.
Timamvetsa mavutowo
Koma kungosonkhanitsa deta yonseyi ndi yabwino, yothandiza, yofunikira, koma sikokwanira - iyenera kumveka. Chifukwa awa ndi mamiliyoni a mapulani osiyanasiyana patsiku.
Koma mamiliyoni ndi osasunthika, choyamba tiyenera kuchita "zing'onozing'ono". Ndipo, choyamba, muyenera kusankha momwe mungakonzekere "chinthu chaching'ono" ichi.
Tapeza mfundo zitatu zofunika:
- ndani adatumiza pempho ili
Ndiko kuti, kuchokera ku ntchito yomwe "idafika": mawonekedwe a intaneti, backend, njira yolipira kapena china. - kumene zidachitika
Pa seva yeniyeni? Chifukwa ngati muli ndi ma seva angapo pansi pa pulogalamu imodzi, ndipo mwadzidzidzi imodzi "imakhala yopusa" (chifukwa "disk yavunda", "kukumbukira kutayikira", vuto lina), ndiye kuti muyenera kutchula seva. - momwe vuto linaonekera mwa njira imodzi
Kuti timvetsetse "ndani" watitumizira pempho, timagwiritsa ntchito chida chokhazikika - kukhazikitsa gawo losiyana: SET application_name = '{bl-host}:{bl-method}'; - timatumiza dzina la mwini bizinesi komwe pempho limachokera, ndi dzina la njira kapena ntchito yomwe idayambitsa.
Titadutsa "mwini" wa pempholi, liyenera kutulutsidwa ku chipikacho - chifukwa cha izi timakonza kusintha. log_line_prefix = ' %m [%p:%v] [%d] %r %a'. Kwa omwe ali ndi chidwi, mwina zonsezi zikutanthauza chiyani. Zikuoneka kuti tikuwona mu chipika:
- время
- zozindikiritsa ndondomeko ndi zochitika
- dzina la database
- IP ya munthu amene watumiza pempholi
- ndi dzina la njira
Kenako tinazindikira kuti sizosangalatsa kwambiri kuyang'ana kulumikizana kwa pempho limodzi pakati pa ma seva osiyanasiyana. Sikuti nthawi zambiri mumakhala ndi nthawi yomwe pulogalamu imodzi imamangirira mofanana apa ndi apo. Koma ngakhale zili zofanana, yang'anani pa seva iliyonse.
Ndiye apa pali odulidwa "server imodzi - tsiku limodzi" zinakhala zokwanira kwa ife kusanthula kulikonse.
Gawo loyamba la kusanthula ndilofanana "chitsanzo" - mawonekedwe ofupikitsa owonetsera dongosolo, ochotsedwa pa zizindikiro zonse za manambala. Kudula kwachiwiri ndiko kugwiritsa ntchito kapena njira, ndipo kudula kwachitatu ndi mfundo yeniyeni yomwe idatibweretsera mavuto.
Titachoka kuzinthu zina kupita ku ma tempuleti, tinali ndi zabwino ziwiri nthawi imodzi:
- kuchepetsa kangapo mu chiwerengero cha zinthu zofufuzidwa
Sitiyeneranso kusanthula vutoli osatinso ndi masauzande a mafunso kapena mapulani, koma ndi ma templates ambiri. - nthawi
Ndiko kuti, mwa kufotokoza mwachidule "zowona" mkati mwa gawo linalake, mukhoza kusonyeza maonekedwe awo masana. Ndipo apa mutha kumvetsetsa kuti ngati muli ndi mtundu wina wa chitsanzo chomwe chimachitika, mwachitsanzo, kamodzi pa ola, koma ziyenera kuchitika kamodzi pa tsiku, muyenera kuganizira zomwe zinalakwika - ndani adayambitsa ndi chifukwa chake, mwinamwake ziyenera kukhala pano. sayenera kutero. Iyi ndi njira ina yosawerengeka, yowoneratu.
Njira zotsalira zimachokera ku zizindikiro zomwe timachotsa mu ndondomekoyi: kangati kameneko kanachitika, nthawi yonse ndi nthawi, kuchuluka kwa deta yomwe inawerengedwa kuchokera ku diski, ndi kuchuluka kwa kukumbukira ...
Chifukwa, mwachitsanzo, mumabwera patsamba la analytics kwa wolandirayo, yang'anani - chinachake chikuyamba kuwerenga kwambiri pa disk. Disk pa seva silingathe kuigwira - ndani amawerenga kuchokera pamenepo?
Ndipo mutha kusanja ndi gawo lililonse ndikusankha zomwe mungathane nazo pakali pano - katundu pa purosesa kapena diski, kapena kuchuluka kwa zopempha ... adatulutsa pulogalamu yatsopano.
Ndipo nthawi yomweyo mutha kuwona mapulogalamu osiyanasiyana omwe amabwera ndi template yomweyo kuchokera ku pempho ngati SELECT * FROM users WHERE login = 'Vasya'. Frontend, backend, processing... Ndipo mukudabwa chifukwa processing angawerenge wosuta ngati sachita naye.
Njira yosiyana ndikuwona nthawi yomweyo kuchokera pakugwiritsa ntchito zomwe imachita. Mwachitsanzo, kutsogolo ndi izi, izi, izi, ndi izi kamodzi pa ola (mndandanda wanthawi umathandizira). Ndipo funso limabwera nthawi yomweyo: zikuwoneka ngati si ntchito yakutsogolo kuchita chinachake kamodzi pa ola ...
Patapita nthawi, tinazindikira kuti tinalibe ophatikizana ziwerengero ndi mapulani mfundo. Tidalekanitsidwa ndi mapulani okhawo omwe amachitapo kanthu ndi deta ya matebulo okha (werengani / lembani ndi index kapena ayi). M'malo mwake, gawo limodzi lokha limawonjezedwa pachithunzi cham'mbuyomu - Kodi node iyi yatibweretsera zolemba zingati?, ndi angati omwe anatayidwa (Mizere Yochotsedwa ndi Zosefera).
Mulibe cholozera choyenera pa mbale, mumapanga pempho kwa icho, chimawuluka kudutsa index, imagwera mu Seq Scan ... mwasefa zolemba zonse kupatula imodzi. Chifukwa chiyani mumafunikira zolemba zosefedwa 100M patsiku?
Titasanthula mapulani onse ndi node, tazindikira kuti pali zomangira zina m'mapulani zomwe zimawoneka zokayikitsa. Ndipo zingakhale bwino kuwuza wopanga mapulogalamuwo kuti: "Bwenzi, apa umayamba kuwerenga ndi index, kenako kusankha, kenako kudula" - monga lamulo, pali mbiri imodzi.
Aliyense amene adalemba mafunso mwina adakumanapo ndi njira iyi: "Ndipatseni dongosolo lomaliza la Vasya, tsiku lake." Ndipo ngati mulibe index pofika tsiku, kapena palibe deti lomwe mwagwiritsa ntchito, ndiye kuti ponda pa “rake” chimodzimodzi .
Koma tikudziwa kuti iyi ndi "rake" - bwanji osauza wopanga mapulogalamu zomwe ayenera kuchita. Chifukwa chake, potsegula dongosolo tsopano, wopanga mapulogalamu athu nthawi yomweyo amawona chithunzi chokongola chokhala ndi malangizo, pomwe amamuuza nthawi yomweyo kuti: "Muli ndi mavuto apa ndi apo, koma amathetsedwa mwanjira iyi."
Chotsatira chake, kuchuluka kwa zochitika zomwe zinkafunika kuthetsa mavuto pachiyambi ndipo tsopano zatsika kwambiri. Uwu ndi mtundu wa chida chomwe tili nacho.
Source: www.habr.com