I-ProHoster > Блог > Ukuphatha > I-Messenger database (ingxenye 2): ukwahlukanisa "ngenzuzo"

I-Messenger database (ingxenye 2): ukwahlukanisa "ngenzuzo"

Siklame ngempumelelo ukwakheka kwesizindalwazi sethu se-PostgreSQL ukuze sigcine izincwadi, unyaka usudlulile, abasebenzisi bayawugcwalisa ngenkuthalo, futhi manje usuqukethe. izigidi zamarekhodi, futhi... into yaqala ukwehla.

I-Messenger database (ingxenye 2): ukwahlukanisa "ngenzuzo"
Point wukuthi Njengoba ubukhulu betafula bukhula, kanjalo "nokujula" kwezinkomba. - nakuba i-logarithmically. Kodwa ngokuhamba kwesikhathi lokhu kuphoqa iseva ukuthi yenze imisebenzi efanayo yokufunda/ukubhala cubungula amakhasi edatha aphindwe kaningikunasekuqaleni.

Yilapho esiza khona ukuhlenga ukwahlukanisa.

Ake ngiphawule ukuthi asikhulumi ngokushadi, okungukuthi, ukusabalalisa idatha phakathi kwedatha ehlukene noma amaseva. Ngoba ngisho nokuhlukanisa idatha phakathi eziningana amaseva, ngeke ususe inkinga yezinkomba "zokuvuvukala" ngokuhamba kwesikhathi. Kuyacaca ukuthi uma ungakwazi ukukhokhela ukufaka iseva entsha ukusebenza nsuku zonke, izinkinga zakho ngeke zisalala nhlobo endizeni yedatha ethile.

Ngeke sicabangele izikripthi ezithile zokuqalisa ukwahlukanisa "ku-hardware", kodwa indlela ngokwayo - ukuthi yini futhi kanjani okufanele "inqunywe zibe izingcezu", nokuthi isifiso esinjalo siholelaphi.

Umqondo

Ake siphinde sichaze umgomo wethu: sifuna ukwenza isiqiniseko sokuthi namuhla, kusasa, futhi ngonyaka, inani ledatha elifundwe yi-PostgreSQL ngesikhathi sanoma yikuphi ukusebenza kokufunda/ukubhala lihlala lifana.

Noma yikuphi idatha eqoqwe ngokulandelana kwesikhathi (imiyalezo, imibhalo, izingodo, izingobo zomlando, ...) ukukhetha okungokwemvelo njengokhiye wokuhlukanisa usuku/isikhathi somcimbi. Esimweni sethu, umcimbi onjalo isikhathi sokuthumela umlayezo.

Qaphela ukuthi abasebenzisi cishe njalo sebenza kuphela nalezo "zakamuva". idatha enjalo - bafunda imilayezo yakamuva, bahlaziye izingodo zakamuva,... Cha, kunjalo, bangakwazi ukupheqa baphindele emuva ngesikhathi, kodwa lokhu bakwenza kuyaqabukela.

Kusukela kulezi zingqinamba kuyacaca ukuthi isixazululo esilungile somlayezo sizoba izigaba "zansuku zonke". - Phela, umsebenzisi wethu cishe uzofunda njalo lokho okufike kuye "namuhla" noma "izolo".

Uma sibhala futhi sifunde cishe esigabeni esisodwa kuphela phakathi nosuku, khona-ke lokhu kusinika nathi ukusetshenziswa ngokuphumelelayo kwememori nediski - njengoba zonke izinkomba zezigaba zingena kalula ku-RAM, ngokungafani nalezo "ezinkulu nezinamafutha" kulo lonke ithebula.

Igxathu emvakwe gxathu

Ngokuvamile, konke okushiwo ngenhla kuzwakala njengenzuzo eyodwa eqhubekayo. Futhi kungenzeka, kodwa kulokhu kuzodingeka sizame kanzima - ngoba isinqumo sokuhlukanisa enye yamabhizinisi siholela esidingweni "sokubona" ​​okuhlobene.

Umlayezo, izakhiwo zawo kanye nokuqagela

Njengoba sinqume ukunqamula imilayezo ngamadethi, kunengqondo ukuphinda sihlukanise izinhlangano-izakhiwo ezincike kuzo (amafayela anamathiselwe, uhlu lwabamukeli), kanye futhi ngosuku lomlayezo.

Njengoba omunye wemisebenzi yethu evamile uwukubuka ngokunembile amarejista emilayezo (engafundiwe, engenayo, yonke), kunengqondo futhi “ukuyidwebela” ekuhlukaniseni ngezinsuku zemiyalezo.

I-Messenger database (ingxenye 2): ukwahlukanisa "ngenzuzo"

Sengeza ukhiye wokuhlukanisa (usuku lomlayezo) kuwo wonke amatafula: abamukeli, ifayela, okubhaliswa. Awudingi ukuyengeza kumlayezo ngokwawo, kodwa sebenzisa i-DateTime ekhona.

Izindikimba

Njengoba kunesihloko esisodwa kuphela semilayezo embalwa, ayikho indlela "yokusisika" ngemodeli efanayo; kufanele uthembele kokunye. Esimeni sethu kuhle usuku lomlayezo wokuqala obhalwe ngawo - okungukuthi, umzuzu wokudala, empeleni, wesihloko.

I-Messenger database (ingxenye 2): ukwahlukanisa "ngenzuzo"

Engeza ukhiye wokuhlukanisa (idethi yesihloko) kuwo wonke amathebula: isihloko, umhlanganyeli.

Kodwa manje sinezinkinga ezimbili ngesikhathi esisodwa:

  • Kukusiphi isigaba okufanele ngibheke imilayezo ngesihloko?
  • Kukusiphi isigaba okufanele ngibheke isihloko esivela kumlayezo?

Yebo, singaqhubeka nokusesha kuzo zonke izigaba, kodwa lokhu kuzodabukisa kakhulu futhi kuzokuphikisa konke ukuwina kwethu. Ngakho-ke, ukuze sazi ukuthi kufanele sibheke kuphi ngempela, sizokwenza izixhumanisi/izinkomba ezinengqondo ezigabeni:

  • sizokwengeza emlayezo inkambu yedethi yesihloko
  • ake sengeze esihlokweni idethi yomlayezo isethiwe lokhu kubhalelana (kungaba itafula elihlukile, noma uxhaxha lwezinsuku)

I-Messenger database (ingxenye 2): ukwahlukanisa "ngenzuzo"

Njengoba kuzoba nezinguquko ezimbalwa ohlwini lwezinsuku zemiyalezo zombhalo ngamunye (ngemuva kwakho konke, cishe yonke imilayezo iwela ezinsukwini ezi-1-2 eziseduze), ngizogxila kule nketho.

Sekukonke, ukwakheka kwesizindalwazi sethu kuthathe ngale ndlela elandelayo, kucatshangelwa ukwahlukanisa:

Amathebula: RU, uma unenzondo kuzinhlamvu zamagama zesiCyrillic emagameni amatafula/izinkambu, kungcono ukungabheki

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

Yonga isenti elihle

Hhayi-ke, kuthiwani uma singasebenzisi inketho yokuhlukanisa yakudala ngokusekelwe ekusabalaliseni kwamanani enkambu (ngezibangeli kanye nefa noma I-PARTITION BY), futhi "mathupha" ezingeni lohlelo lokusebenza, uzoqaphela ukuthi inani lokhiye wokuhlukanisa selivele ligcinwe egameni lethebula ngokwalo.

Ngakho uma unjalo Ingabe ukhathazeke kakhulu ngenani ledatha egcinwe?, khona-ke ungakwazi ukususa lezi zinkambu "ezengeziwe" futhi ukhulume namathebula athile. Yiqiniso, zonke izinketho ezivela ezigabeni eziningana kuleli cala kuzodingeka zidluliselwe ohlangothini lwesicelo.

Source: www.habr.com

Engeza amazwana