Messenger database (chikamu 1): kugadzira hwaro hwaro

Iwe unogona kushandura sei bhizinesi zvinodiwa mune chaiyo data zvimiro uchishandisa muenzaniso wekugadzira messenger dhatabhesi kubva kutanga.

• Chikamu 1: kugadzira iyo base frame
• Chikamu 2: chikamu che "kuitira purofiti"

Nheyo yedu haizove yakakura uye yakagovaniswa, zvakafanana VKontakte kana Badoo, asi "kuitira kuti ive", asi yakanga yakanaka - inoshanda, inokurumidza uye kukwana pane imwe server PostgreSQL - kuitira kuti iwe ugone kuendesa yakaparadzana muenzaniso wesevhisi kumwe kudivi, semuenzaniso.

Naizvozvo, isu hatisi kuzobata pamusoro penyaya dze sharding, kudzokorora uye geo-yakagoverwa masisitimu, asi tichatarisa pane edunhu mhinduro mukati medhatabhesi.

Danho 1: Mamwe mabhizinesi chaiwo

Hatisi kuzogadzira mameseji edu zvisina tsarukano, asi tichaabatanidza mune zvakatipoteredza corporate social network. Ndiko kuti, vanhu vedu "havangowirirani chete," asi vanotaurirana mumwe nemumwe mumamiriro ekugadzirisa mamwe matambudziko ebhizinesi.

Uye ndeapi mabasa ebhizinesi? .. Ngatitarisei muenzaniso waVasily, mukuru wedhipatimendi rekusimudzira.

• "Nikolai, basa iri tinoda chigamba nhasi!"
  Izvi zvinoreva kuti kunyorerana tsamba kunogona kuitwa muchirevo chevamwe chinyorwa.
• "Kolya, uri kuenda kuDota manheru ano?"
  Ndiko kuti, kunyange vaviri vaviri vanopindirana vanogona kutaurirana panguva imwe chete pamisoro yakasiyana-siyana.
• "Peter, Nikolay, tarisa mune zvakanamirwa pamitengo yeiyo server nyowani."
  Saka, meseji imwe chete inogona kuva vakawanda vanogamuchira. Muchiitiko ichi, meseji inogona kunge iine Akabatanidzwa mafaira.
• "Semyon, tarisawo."
  Uye panofanirwa kuve nemukana wekupinda mutsamba iripo koka nhengo itsva.

Ngatigarei pane iyi runyorwa rwezvido “zviri pachena” izvozvi.

Pasina kunzwisisa zvakashandiswa zvakanangana nedambudziko uye zvipimo zvakapihwa kwairi, dhizaini inoshanda database schema kuigadzirisa zvinenge zvisingaiti.

Nhanho yechipiri: Minimal Logic Circuit

Parizvino, zvese zvinoshanda zvakafanana kune email tsamba - yechinyakare bhizinesi chishandiso. Hongu, "algorithmically" matambudziko mazhinji ebhizinesi akafanana kune mumwe nemumwe, saka maturusi ekuagadzirisa achave akafanana.

Ngatigadzirisei iyo yakatowanikwa inonzwisisika dhayagiramu yehukama hukama. Kuita kuti modhi yedu ive nyore kunzwisisa, isu tichashandisa yakanyanya primitive kuratidza sarudzo ER mienzaniso pasina matambudziko eUML kana IDEF notations:

Mumuenzaniso wedu, munhu, gwaro uye bhinari "muviri" wefaira "ekunze" masangano aripo akazvimirira pasina sevhisi yedu. Naizvozvo, isu tichangovaona mune ramangwana semamwe malink "kumwe" neUUID.

Dhirowa madhayagiramu ari nyore sezvinobvira - Vazhinji vevanhu vauchavaratidza kwavari havasi nyanzvi pakuverenga UML/IDEF. Asi iva nechokwadi chekudhirowa.

Nhanho 3: Kudhirowa chimiro chetafura

Nezve tafura nemazita emundaMazita e "Russian" eminda nematafura anogona kurapwa zvakasiyana, asi iyi inyaya yekuravira. Nokuti iyo pano paTensor hapana vagadziri vekunze, uye PostgreSQL inotitendera kuti tipe mazita kunyangwe mune hieroglyphs, kana ivo zvakavharirwa mumakotesheni, tobva tasarudza kutumidza zvinhu zvisina kujeka uye zvakajeka kuitira kuti pasave nekusiyana.
Sezvo vanhu vakawanda vachitinyorera mameseji panguva imwe chete, vamwe vavo vangatoita izvi offline, ipapo sarudzo iri nyore ndeye shandisa maUUID sezviziviso kwete zvekunze chete, asiwo kune zvese zvinhu zviri mukati mesevhisi yedu. Uyezve, anogona kugadzirwa kunyangwe kudivi revatengi - izvi zvichatibatsira kutsigira kutumira mameseji kana dhatabhesi risipo kwenguva pfupi, uye mukana wekudhumhana wakadzikira zvakanyanya.

Iyo dhizaini tafura chimiro mune yedu database ichaita seizvi:
Matafura: 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
);

Matafura: 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
);

Chinhu chiri nyore kana uchitsanangura fomati kutanga "kusunungura" girafu yekubatanidza kubva pamatafura asina kutaurwa ivo pachavo kuna ani zvake.

Danho rechina: Tsvaga zvisiri pachena zvido

Ndizvozvo, isu takagadzira dhatabhesi maunogona kunyora zvakakwana uye neimwe nzira verenga.

Ngatizviisei mutsoka dzemushandisi webasa redu - chii chatinoda kuita nacho?

• Mameseji ekupedzisira
  ichi zvakarongwa zvichitevedzana registry ye "zvangu" mameseji zvichienderana neakasiyana maitiro. Kwandiri mumwe wevakagamuchira, kwandinenge ndiri munyori, kwavakandinyorera uye handina kupindura, kwavasina kundipindura, ...
• Vatori vechikamu
  Ndiani ari kutotora chikamu muiyi hurukuro refu, refu?

Chimiro chedu chinotibvumira kugadzirisa matambudziko maviri aya "muzhinji," asi kwete nekukurumidza. Dambudziko nderekuti kuronga mukati mebasa rekutanga kutadza kugadzira index, inokodzera mumwe nemumwe wevatori vechikamu (uye iwe uchafanirwa kubvisa zvinyorwa zvese), uye kugadzirisa yechipiri yaunoda bvisa mameseji ese pamusoro penyaya iyi.

Mabasa asingatarisirwe emushandisi anogona kuisa akashinga yambuka pakubudirira.

Nhanho 5: Smart Denormalisation

Matambudziko edu ese ari maviri anozogadziriswa nemamwe matafura atinoda dzokorora chikamu che data, zvakafanira kuumba paari indices akakodzera mabasa edu.


Matafura: RU

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

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

Matafura: 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 takashandisa nzira mbiri dzinoshandiswa pakugadzira matafura ebetsero:

• Kuwedzera marekodhi
  Tichishandisa meseji yekutanga rekodhi, tinogadzira akati wandei ekutevera marekodhi mumhando dzakasiyana dzemarejista evaridzi vakasiyana - zvese kune anotumira uye kune anogamuchira. Asi imwe neimwe yemarejista ikozvino inowira pane index - mushure mezvose, mune yakajairika kesi, isu tichada kuona chete peji rekutanga.
• Unique zvinyorwa
  Pese paunotumira meseji mukati meimwe musoro wenyaya, zvakakwana kuti utarise kana iyo yekupinda yatovepo. Kana zvisina kudaro, wedzera kune yedu "duramazwi".

Muchikamu chinotevera chechinyorwa tichataura nezvacho kushandiswa kwekuparadzanisa muchimiro che database yedu.

Source: www.habr.com