Nawonso database ya Messenger (gawo 1): kupanga maziko

Momwe mungamasulire zofunikira zamabizinesi muzinthu zinazake za data pogwiritsa ntchito chitsanzo chopanga nkhokwe ya messenger kuyambira poyambira.

• Gawo 1: Kupanga maziko a maziko
• Gawo 2: gawo la "phindu"

Maziko athu sadzakhala aakulu komanso ogawidwa, ngati VKontakte kapena Badoo, koma "kotero kuti zinali", koma zinali zabwino - zogwira ntchito, mofulumira komanso zokwanira pa seva imodzi PostgreSQL - kotero kuti mutha kugwiritsa ntchito gawo lina lautumiki kwinakwake kumbali, mwachitsanzo.

Chifukwa chake, sitikhudzanso nkhani za sharding, kubwereza ndi kugawa kwa geo, koma timayang'ana kwambiri mayankho ozungulira mkati mwa database.

Gawo 1: Zina zabizinesi

Sitidzapanga mauthenga athu mwachisawawa, koma tidzawaphatikiza ndi chilengedwe makampani ochezera a pa Intaneti. Ndiko kuti, anthu athu "samangofanana," koma amalankhulana wina ndi mnzake pothana ndi mavuto ena abizinesi.

Ndipo ntchito za bizinesi ndi ziti? .. Tiyeni tiwone chitsanzo cha Vasily, mkulu wa dipatimenti yachitukuko.

• "Nikolai, pa ntchitoyi tikufuna chigamba lero!"
  Izi zikutanthauza kuti kulemberana makalata kungathe kuchitidwa muzochitika za ena chikalata.
• "Kolya, ukupita ku Dota madzulo ano?"
  Ndiko kuti, ngakhale awiri a interlocutors amatha kulankhulana nthawi imodzi pa mitu yosiyanasiyana.
• "Peter, Nikolay, yang'anani zomwe zili pamndandanda wamitengo ya seva yatsopano."
  Kotero, uthenga umodzi ukhoza kukhala nawo olandira angapo. Pankhaniyi, uthenga ukhoza kukhala Mafayilo ophatikizidwa.
• "Semyon, yang'ananso."
  Ndipo payenera kukhala mwayi wolowa m'makalata omwe alipo itanani membala watsopano.

Tiyeni tikhazikike pa mndandanda wa zosowa "zowonekera" pakadali pano.

Popanda kumvetsetsa zomwe zagwiritsidwa ntchito pavutoli ndi malire omwe amaperekedwa kwa iwo, kupanga ogwira schema database kuti athetse ndizosatheka.

Khwerero 2: Dongosolo Laling'ono Lomveka

Pakadali pano, zonse zimagwira ntchito mofanana kwambiri ndi makalata a imelo - chida chamalonda chachikhalidwe. Inde, "algorithmically" mavuto ambiri amabizinesi amafanana wina ndi mnzake, chifukwa chake zida zowathetsera zidzakhala zofanana.

Tiyeni tikonze zojambula zomveka zomwe zapezeka kale zamaubwenzi. Kuti fanizo lathu likhale losavuta kumva, tidzagwiritsa ntchito mawonekedwe akale kwambiri Zithunzi za ER popanda zovuta za UML kapena zolemba za IDEF:

Mu chitsanzo chathu, munthu, chikalata ndi "thupi" lachiphamaso la fayilo ndi "zakunja" zomwe zimakhalapo popanda ntchito yathu. Chifukwa chake, tidzangowawona mtsogolomo ngati maulalo ena "penapake" ndi UUID.

Jambulani zojambula zosavuta momwe zingathere - ambiri mwa anthu omwe muwawonetse si akatswiri powerenga UML/IDEF. Koma onetsetsani kujambula.

3: Kujambula kapangidwe ka tebulo

Za tebulo ndi mayina a mindaMayina a "Russian" a minda ndi matebulo akhoza kuchitidwa mosiyana, koma iyi ndi nkhani ya kukoma. Chifukwa ndi kuno ku Tensor palibe otukula akunja, ndipo PostgreSQL imatilola kuti tipatse mayina ngakhale m'ma hieroglyphs, ngati atero ophatikizidwa m'mawu, ndiye timakonda kutchula zinthu momveka bwino komanso momveka bwino kuti pasakhale kusiyana.
Popeza anthu ambiri amatilembera mauthenga nthawi imodzi, ena angachite zimenezi popanda intaneti, ndiye njira yosavuta ndiyo gwiritsani ntchito ma UUID ngati zizindikiritso osati zakunja zokha, komanso zinthu zonse mkati mwautumiki wathu. Kuphatikiza apo, amatha kupangidwa ngakhale kumbali ya kasitomala - izi zitithandiza kuthandizira kutumiza mauthenga pakanthawi kochepa, ndipo kuthekera kwa kugunda kumakhala kotsika kwambiri.

Dongosolo la tebulo lokonzekera mu database yathu likuwoneka motere:
Zolemba: RU

CREATE TABLE "Тема"(
  "Тема"
    uuid
      PRIMARY KEY
, "Документ"
    uuid
, "Название"
    text
);

CREATE TABLE "Сообщение"(
  "Сообщение"
    uuid
      PRIMARY KEY
, "Тема"
    uuid
, "Автор"
    uuid
, "ДатаВремя"
    timestamp
, "Текст"
    text
);

CREATE TABLE "Адресат"(
  "Сообщение"
    uuid
, "Персона"
    uuid
, PRIMARY KEY("Сообщение", "Персона")
);

CREATE TABLE "Файл"(
  "Файл"
    uuid
      PRIMARY KEY
, "Сообщение"
    uuid
, "BLOB"
    uuid
, "Имя"
    text
);

Matebulo: EN

CREATE TABLE theme(
  theme
    uuid
      PRIMARY KEY
, document
    uuid
, title
    text
);

CREATE TABLE message(
  message
    uuid
      PRIMARY KEY
, theme
    uuid
, author
    uuid
, dt
    timestamp
, body
    text
);

CREATE TABLE message_addressee(
  message
    uuid
, person
    uuid
, PRIMARY KEY(message, person)
);

CREATE TABLE message_file(
  file
    uuid
      PRIMARY KEY
, message
    uuid
, content
    uuid
, filename
    text
);

Chosavuta kwambiri pofotokozera mawonekedwe ndikuyamba "kumasula" graph yolumikizira kuchokera pa matebulo omwe sanatchulidwe okha kwa aliyense.

Khwerero 4: Pezani zosowa zosawonekera

Ndizo zonse, tapanga database momwe mungalembe bwino komanso mwanjira ina werengani.

Tiyeni tidziyike tokha mu nsapato za wogwiritsa ntchito ntchito yathu - kodi tikufuna kuchita chiyani nayo?

• Mauthenga otsiriza
  izi zosanjidwa motsatira nthawi kaundula wa mauthenga "anga" kutengera njira zosiyanasiyana. Komwe ndili m'modzi mwa olandila, komwe ndine wolemba, pomwe adandilembera ndipo sindinayankhe, pomwe sanandiyankhe, ...
• Otenga nawo mbali pamakalata
  Ndani amene akutenga nawo mbali pamacheza aatali awa?

Mapangidwe athu amatilola kuthetsa mavuto onsewa "mwambiri," koma osati mwachangu. Vuto ndilakuti kusanja mkati mwa ntchito yoyamba sanathe kupanga index, yoyenera kwa aliyense wa omwe atenga nawo mbali (ndipo mudzayenera kuchotsa zolemba zonse), ndi kuthetsa yachiwiri yomwe mukufuna kuchotsa mauthenga onse pa mutu uwu.

Zochita zosayembekezereka za ogwiritsa ntchito zitha kukhala zolimba mtima kudutsa pa zokolola.

Gawo 5: Smart Denormalization

Mavuto athu onse awiri adzathetsedwa ndi matebulo owonjezera omwe tidzatero fanizira gawo la data, zofunika kupanga pa iwo zizindikiro zoyenera ntchito zathu.


Zolemba: RU

CREATE TABLE "РеестрСообщений"(
  "Владелец"
    uuid
, "ТипРеестра"
    smallint
, "ДатаВремя"
    timestamp
, "Сообщение"
    uuid
, PRIMARY KEY("Владелец", "ТипРеестра", "Сообщение")
);
CREATE INDEX ON "РеестрСообщений"("Владелец", "ТипРеестра", "ДатаВремя" DESC);

CREATE TABLE "УчастникТемы"(
  "Тема"
    uuid
, "Персона"
    uuid
, PRIMARY KEY("Тема", "Персона")
);

Matebulo: EN

CREATE TABLE message_registry(
  owner
    uuid
, registry
    smallint
, dt
    timestamp
, message
    uuid
, PRIMARY KEY(owner, registry, message)
);
CREATE INDEX ON message_registry(owner, registry, dt DESC);

CREATE TABLE theme_participant(
  theme
    uuid
, person
    uuid
, PRIMARY KEY(theme, person)
);

Pano tagwiritsa ntchito njira ziwiri zomwe zimagwiritsidwa ntchito popanga matebulo othandizira:

• Kuchulukitsa zolemba
  Pogwiritsa ntchito mbiri yauthenga woyamba, timapanga zolemba zingapo zotsatiridwa m'mitundu yosiyanasiyana ya eni ake - onse kwa wotumiza komanso kwa wolandira. Koma zolembera zonse tsopano zikugwera pa index - pambuyo pake, muzochitika zenizeni, tidzafuna kuwona tsamba loyamba lokha.
• Zolemba zapadera
  Nthawi iliyonse mukatumiza uthenga mkati mwa mutu wakutiwakuti, ndikwanira kuwona ngati cholowacho chilipo kale. Ngati sichoncho, onjezani ku "dikishonale" yathu.

M’gawo lotsatira la nkhaniyi tidzakambirana kukhazikitsa partitioning mu kapangidwe ka database yathu.

Source: www.habr.com