Isu takabudirira kugadzira chimiro chePostgreSQL dhatabhesi yedu yekuchengetedza tsamba, gore rapfuura, vashandisi vari kushingaira kuizadza, uye ikozvino ine. mamiriyoni ezvinyorwa, uye... chimwe chinhu chakatanga kudzikira.
Chikamu 1: kugadzira iyo base frame - Chikamu 2: chikamu che "kuitira purofiti"
Point ndechokuti Sezvo saizi yetafura inokura, ndizvo zvinoitawo "kudzika" kwemaindex. - kunyangwe logarithmically. Asi nekufamba kwenguva izvi zvinomanikidza sevha kuita zvakafanana kuverenga / kunyora mabasa gadzirisa kakawanda mamwe mapeji e datakupfuura pakutanga.
Apa ndipo panouya kununura chikamu.
Rega ndione kuti hatisi kutaura nezve sharding, ndiko kuti, kugovera data pakati pemadatabase akasiyana kana maseva. Nekuti kunyange kupatsanura iyo data mukati akawanda maseva, hausi kuzobvisa dambudziko reindex "kuzvimba" nekufamba kwenguva. Zviri pachena kuti kana iwe uchikwanisa kuisa sevha itsva kushanda zuva rega rega, ipapo matambudziko ako haachazorara zvachose mundege yedatabase chaiyo.
Tichafunga kwete zvinyorwa zvekushandisa kugovera "mu Hardware", asi nzira yacho pachayo - chii uye sei chinofanira "kuchekwa kuita zvidimbu", uye kuti chishuwo chakadaro chinotungamira chii.
Pfungwa
Ngatitsanangurirei chinangwa chedu zvakare: tinoda kuve nechokwadi chekuti nhasi, mangwana, uye mugore, huwandu hwe data yakaverengwa nePostgreSQL panguva chero yekuverenga / kunyora kushanda inoramba yakafanana.
Kune chero mashoko akaunganidzwa achitevedzana (mameseji, zvinyorwa, matanda, matura, ...) sarudzo yechisikigo sekiyi yekuparadzanisa ndeye chiitiko zuva/nguva. Muchiitiko chedu, chiitiko chakadaro nguva yekutumira meseji.
Ziva kuti vashandisi vanenge nguva dzose shanda chete nevaya βvachangoburwaβ data rakadaro - vanoverenga mameseji achangoburwa, vanoongorora matanda ekupedzisira,... Kwete, chokwadi, vanogona kupfuurira kumashure munguva, asi vanoita izvi kashoma.
Kubva pane izvi zvipingamupinyi zviri pachena kuti iyo yakakwana meseji mhinduro yaizova "zuva nezuva" zvikamu - mushure mezvose, mushandisi wedu anogara achiverenga zvakauya kwaari "nhasi" kana "zuro".
Kana tikanyora uye tichiverenga zvinenge muchikamu chimwe chete mukati mezuva, zvino izvi zvinotipawo Kunyanya kushandisa ndangariro uye dhisiki - sezvo ese echikamu indexes anokwana nyore mu RAM, kusiyana neiyo "hombe nemafuta" patafura yese.
mbichana mbichana
Kazhinji, zvese zvataurwa pamusoro apa zvinonzwika senge purofiti imwe inoenderera. Uye zvinogoneka, asi nekuda kweizvi tichafanirwa kuedza nesimba - nekuti Sarudzo yekuparadzanisa imwe yemasangano inotungamira kune kudikanwa kwe "kuona" yakabatana.
Mharidzo, zvimiro zvayo uye mafungidziro
Sezvo isu takafunga kucheka mameseji nemazuva, zvine musoro kugoverawo masangano-zvivakwa zvinoenderana nazvo (akabatanidzwa mafaera, runyorwa rwevanogamuchira), uye zvakare nezuva remeseji.
Sezvo rimwe remabasa edu akajairika ari kunyatso kuona marejista emeseji (asina kuverengwa, anouya, ese), zvine musoro zvakare "kuadhonzera mukati" mukugovanisa nemazuva emeseji.
Isu tinowedzera kiyi yekugovera (zuva remeseji) kune ese matafura: vanogamuchira, faira, registries. Iwe haufanirwe kuiwedzera kune meseji pachayo, asi shandisa iripo DateTime.
shinda
Sezvo paine musoro mumwe chete wemameseji akati wandei, hapana nzira yekui "cheka" mumhando imwechete; iwe unofanirwa kuvimba nechimwe chinhu. Muchiitiko chedu chakanaka zuva rekutanga meseji mutsamba - ndiko kuti, nguva yekusika, chaizvoizvo, yehurukuro.
Wedzera kiyi yekugovera (musoro wenyaya) kumatafura ese: musoro, mutori wechikamu.
Asi ikozvino tine matambudziko maviri panguva imwe chete:
- Ndechipi chikamu chandinofanira kutsvaga mameseji pamusoro wenyaya?
- Muchikamu chipi chandinofanira kutsvaga musoro kubva mumeseji?
Tinogona, hongu, kuramba tichitsvaga muzvikamu zvese, asi izvi zvichave zvinosuruvarisa uye zvicharamba zvese zvatakahwina. Naizvozvo, kuti tizive pekutarisisa chaiko, isu tichaita zvine musoro zvinongedzo / zvinongedzo kune zvikamu:
- tichawedzera mushoko topic date field
- ngatiwedzere kune musoro wenyaya meseji date yakatarwa tsamba iyi (inogona kunge iri tafura yakaparadzana, kana nhevedzano yemazuva)
Sezvo kuchave nekugadziriswa kushoma kune rondedzero yemazuva emeseji yetsamba yega yega (mushure mezvose, anenge mameseji ese anowira pamazuva 1-2 ari padyo), ini ndichatarisa pane iyi sarudzo.
Pakazara, chimiro chedhatabhesi yedu chakatora fomu rinotevera, tichifunga nezvekugovanisa:
Matafura: RU, kana iwe uine kuvenga kune Cyrillic alphabet mumazita ematafura / minda, zviri nani kusatarisa.
-- ΡΠ΅ΠΊΡΠΈΠΈ ΠΏΠΎ Π΄Π°ΡΠ΅ ΡΠΎΠΎΠ±ΡΠ΅Π½ΠΈΡ
CREATE TABLE "Π‘ΠΎΠΎΠ±ΡΠ΅Π½ΠΈΠ΅_YYYYMMDD"(
"Π‘ΠΎΠΎΠ±ΡΠ΅Π½ΠΈΠ΅"
uuid
PRIMARY KEY
, "Π’Π΅ΠΌΠ°"
uuid
, "ΠΠ°ΡΠ°Π’Π΅ΠΌΡ"
date
, "ΠΠ²ΡΠΎΡ"
uuid
, "ΠΠ°ΡΠ°ΠΡΠ΅ΠΌΡ" -- ΠΈΡΠΏΠΎΠ»ΡΠ·ΡΠ΅ΠΌ ΠΊΠ°ΠΊ Π΄Π°ΡΡ
timestamp
, "Π’Π΅ΠΊΡΡ"
text
);
CREATE TABLE "ΠΠ΄ΡΠ΅ΡΠ°Ρ_YYYYMMDD"(
"ΠΠ°ΡΠ°Π‘ΠΎΠΎΠ±ΡΠ΅Π½ΠΈΡ"
date
, "Π‘ΠΎΠΎΠ±ΡΠ΅Π½ΠΈΠ΅"
uuid
, "ΠΠ΅ΡΡΠΎΠ½Π°"
uuid
, PRIMARY KEY("Π‘ΠΎΠΎΠ±ΡΠ΅Π½ΠΈΠ΅", "ΠΠ΅ΡΡΠΎΠ½Π°")
);
CREATE TABLE "Π€Π°ΠΉΠ»_YYYYMMDD"(
"ΠΠ°ΡΠ°Π‘ΠΎΠΎΠ±ΡΠ΅Π½ΠΈΡ"
date
, "Π€Π°ΠΉΠ»"
uuid
PRIMARY KEY
, "Π‘ΠΎΠΎΠ±ΡΠ΅Π½ΠΈΠ΅"
uuid
, "BLOB"
uuid
, "ΠΠΌΡ"
text
);
CREATE TABLE "Π Π΅Π΅ΡΡΡΠ‘ΠΎΠΎΠ±ΡΠ΅Π½ΠΈΠΉ_YYYYMMDD"(
"ΠΠ°ΡΠ°Π‘ΠΎΠΎΠ±ΡΠ΅Π½ΠΈΡ"
date
, "ΠΠ»Π°Π΄Π΅Π»Π΅Ρ"
uuid
, "Π’ΠΈΠΏΠ Π΅Π΅ΡΡΡΠ°"
smallint
, "ΠΠ°ΡΠ°ΠΡΠ΅ΠΌΡ"
timestamp
, "Π‘ΠΎΠΎΠ±ΡΠ΅Π½ΠΈΠ΅"
uuid
, PRIMARY KEY("ΠΠ»Π°Π΄Π΅Π»Π΅Ρ", "Π’ΠΈΠΏΠ Π΅Π΅ΡΡΡΠ°", "Π‘ΠΎΠΎΠ±ΡΠ΅Π½ΠΈΠ΅")
);
CREATE INDEX ON "Π Π΅Π΅ΡΡΡΠ‘ΠΎΠΎΠ±ΡΠ΅Π½ΠΈΠΉ_YYYYMMDD"("ΠΠ»Π°Π΄Π΅Π»Π΅Ρ", "Π’ΠΈΠΏΠ Π΅Π΅ΡΡΡΠ°", "ΠΠ°ΡΠ°ΠΡΠ΅ΠΌΡ" DESC);
-- ΡΠ΅ΠΊΡΠΈΠΈ ΠΏΠΎ Π΄Π°ΡΠ΅ ΡΠ΅ΠΌΡ
CREATE TABLE "Π’Π΅ΠΌΠ°_YYYYMMDD"(
"ΠΠ°ΡΠ°Π’Π΅ΠΌΡ"
date
, "Π’Π΅ΠΌΠ°"
uuid
PRIMARY KEY
, "ΠΠΎΠΊΡΠΌΠ΅Π½Ρ"
uuid
, "ΠΠ°Π·Π²Π°Π½ΠΈΠ΅"
text
);
CREATE TABLE "Π£ΡΠ°ΡΡΠ½ΠΈΠΊΠ’Π΅ΠΌΡ_YYYYMMDD"(
"ΠΠ°ΡΠ°Π’Π΅ΠΌΡ"
date
, "Π’Π΅ΠΌΠ°"
uuid
, "ΠΠ΅ΡΡΠΎΠ½Π°"
uuid
, PRIMARY KEY("Π’Π΅ΠΌΠ°", "ΠΠ΅ΡΡΠΎΠ½Π°")
);
CREATE TABLE "ΠΠ°ΡΡΠ‘ΠΎΠΎΠ±ΡΠ΅Π½ΠΈΠΉΠ’Π΅ΠΌΡ_YYYYMMDD"(
"ΠΠ°ΡΠ°Π’Π΅ΠΌΡ"
date
, "Π’Π΅ΠΌΠ°"
uuid
PRIMARY KEY
, "ΠΠ°ΡΠ°"
date
);
Chengetedza peni rakanaka
Zvakanaka, ko kana tikasashandisa
Saka kana wakadaro Iwe uri kunetseka zvakanyanya nezvehuwandu hwe data yakachengetwa?, ipapo iwe unogona kubvisa aya "ekuwedzera" minda uye kero chaiyo matafura. Ichokwadi, zvose zvakasarudzwa kubva muzvikamu zvakati munyaya iyi zvinofanira kuendeswa kudivi rekushandisa.
Source: www.habr.com