ProHoster > Ibhulogi > Ulawulo > Isiseko sedatha yesithunywa (icandelo 2): ukwahlula "ngenzuzo"

Isiseko sedatha yesithunywa (icandelo 2): ukwahlula "ngenzuzo"

Siye sayila ngempumelelo isiseko sedatha yethu yePostgreSQL yokugcina imbalelwano, unyaka udlulile, abasebenzisi bayayizalisa ngenkuthalo, kwaye ngoku iqulethe. izigidi zeerekhodi, kwaye... into yaqala ukucotha.

Isiseko sedatha yesithunywa (icandelo 2): ukwahlula "ngenzuzo"
Inyani yile Njengoko ubungakanani betafile bukhula, ngokunjalo "ubunzulu" bezalathisi. - nangona i-logarithmically. Kodwa ekuhambeni kwexesha oku kunyanzela umncedisi ukuba enze umsebenzi ofanayo wokufunda/ubhale qhubekisa amaphepha amaninzi edathakunasekuqaleni.

Kulapho kusiza khona ukwahlulahlula.

Makhe ndiqaphele ukuba asithethi malunga nokwabelana, oko kukuthi, ukusabalalisa idatha phakathi kwedatha eyahlukeneyo okanye iiseva. Ngenxa yokuba nokwahlula-hlula idatha eziliqela abancedisi, awuyi kulahla ingxaki yezalathisi "ukudumba" ekuhambeni kwexesha. Kucacile ukuba ukuba unako ukukwazi ukubeka iseva entsha ekusebenzeni yonke imihla, ngoko iingxaki zakho aziyi kuphinda zilale kwinqwelo-moya yedatha ethile.

Asiyi kuqwalasela izikripthi ezithile zokuphumeza ukwahlula "kwi-hardware", kodwa indlela ngokwayo - yintoni kwaye kufuneka "inqunyulwe ibe ngamaqhekeza", kwaye yintoni na loo mnqweno ukhokelela kuyo.

Ingcamango

Masichaze injongo yethu kwakhona: sifuna ukuqinisekisa ukuba namhlanje, ngomso, kwaye ngonyaka, inani ledatha efundwe yi-PostgreSQL ngexesha laluphi na umsebenzi wokufunda / wokubhala uhlala ufana.

Kuba nayiphi na idatha eqokelelwe ngokolandelelwano (imiyalezo, amaxwebhu, iilog, oovimba, ...) ukhetho lwendalo njengeqhosha lokwahlula umhla/ixesha lomsitho. Kwimeko yethu, isiganeko esinjalo ixesha lokuthumela umyalezo.

Qaphela ukuba abasebenzisi phantse ngalo lonke ixesha sebenza kuphela nezi "zamva nje". idatha enjalo - bafunda imiyalezo yamva nje, bahlalutye iilog zamva nje,... Hayi, kunjalo, banokuskrola emva kwexesha, kodwa bakwenza oku kunqabile kakhulu.

Ukusuka kule miqobo kucacile ukuba esona sisombululo somyalezo siya kuba amacandelo "emihla ngemihla". - emva kwayo yonke loo nto, umsebenzisi wethu uya kuhlala efunda into eza kuye "namhlanje" okanye "izolo".

Ukuba sibhala kwaye sifunde phantse kuphela kwicandelo elinye emini, ngoko oku kusinika nathi usetyenziso olusebenzayo lwenkumbulo kunye nediski - ekubeni zonke izalathisi zecandelo zingena lula kwi-RAM, ngokungafaniyo nezona "zinkulu kunye namafutha" kuyo yonke itafile.

Inyathelo nenyathelo

Ngokubanzi, yonke into echazwe ngasentla ivakala njengenzuzo enye eqhubekayo. Kwaye kuyafezekiswa, kodwa ngenxa yoku kuya kufuneka sizame nzima - kuba isigqibo sokwahlula elinye lamaqumrhu kukhokelela kwisidingo “sokubona” okunxulumeneyo.

Umyalezo, iimpawu zayo kunye noqikelelo

Ukusukela ukuba sigqibe ukusika imiyalezo ngemihla, iyavakala ukwahlula izinto-iipropathi ezixhomekeke kuzo (iifayile ezincanyathiselwe, uluhlu lwabamkeli), kwaye nangomhla womyalezo.

Kuba omnye wemisebenzi yethu yesiqhelo kukujonga ngokuchanekileyo iirejista zomyalezo (ezingafundwanga, ezingenayo, zonke), kusengqiqweni ukuba "zitsalele ngaphakathi" ekwahluleni ngemihla yomyalezo.

Isiseko sedatha yesithunywa (icandelo 2): ukwahlula "ngenzuzo"

Songeza iqhosha lokwahlula (umhla womyalezo) kuzo zonke iitafile: abamkeli, ifayile, iirejistri. Akunyanzelekanga ukuba uyongeze kumyalezo ngokwawo, kodwa sebenzisa iDateTime ekhoyo.

Imixholo

Kuba kukho isihloko esinye kuphela kwimiyalezo emininzi, akukho ndlela "yokunqunqa" kwimodeli efanayo; kufuneka uthembele kwenye into. Kwimeko yethu ifanelekile umhla womyalezo wokuqala kwimbalelwano - oko kukuthi, umzuzu wokudala, ngokwenene, wesihloko.

Isiseko sedatha yesithunywa (icandelo 2): ukwahlula "ngenzuzo"

Yongeza iqhosha lokwahlula (umhla wesihloko) kuzo zonke iitheyibhile: isihloko, umthathi-nxaxheba.

Kodwa ngoku sineengxaki ezimbini ngaxeshanye:

  • Leliphi icandelo endimele ndijonge kulo imiyalezo ngesihloko?
  • Leliphi icandelo apho kufuneka ndijonge isihloko kumyalezo?

Sinako, ngokuqinisekileyo, ukuqhubeka ukukhangela kuwo onke amacandelo, kodwa oku kuya kuba buhlungu kakhulu kwaye kuya kukhanyela zonke lokuwina kwethu. Ke ngoko, ukuze sazi ukuba sijonge phi kanye kanye, siya kwenza amakhonkco/izalathisi ezinengqiqo kumacandelo:

  • siyakongeza kumyalezo indawo yomhla wesihloko
  • songeze kwisihloko Umhla womyalezo usetiwe le mbalelwano (inokuba yitafile eyahlukileyo, okanye uluhlu lwemihla)

Isiseko sedatha yesithunywa (icandelo 2): ukwahlula "ngenzuzo"

Ekubeni kuya kubakho ukulungiswa okumbalwa kuluhlu lwemihla yomyalezo kwimbalelwano nganye (emva koko, phantse yonke imiyalezo iwela kwi-1-2 iintsuku ezikufutshane), ndiya kugxininisa kolu khetho.

Lilonke, ulwakhiwo lwesiseko sedatha yethu luthathe le ndlela ilandelayo, kuthathelwa ingqalelo ukwahlulahlula:

Iitheyibhile: RU, ukuba unenzondo kwialfabhethi yesiCyrillic kumagama eetafile / amasimi, kungcono ukuba ungajongi.

-- секции по дате сообщения
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
);

Gcina ipeni entle

Kulungile, ukuba asisebenzisi ukhetho lwamacandelo lwakudala ngokusekwe kunikezelo lwamaxabiso entsimi (ngokusebenzisa izitshixo kunye nelifa okanye i-PARTITION BY), kwaye "ngesandla" kwinqanaba lesicelo, uya kuqaphela ukuba ixabiso leqhosha lokwahlula sele ligcinwe kwigama letafile ngokwayo.

Ngoko ukuba kunjalo Ngaba ukhathazeke kakhulu malunga nesixa sedatha egciniweyo?, emva koko unokulahla le mimandla "eyongezelelweyo" kunye nedilesi yeetafile ezithile. Enyanisweni, zonke iinketho ezivela kumacandelo amaninzi kule meko ziya kufuneka zidluliselwe kwicala lesicelo.

umthombo: www.habr.com

Yongeza izimvo