Načrtovanje podatkovne zbirke MySQL za spletne aplikacije

Ko trije zaposleni zjutraj vzporedno knjižijo blago, stranka preveri status dostave, in zaledna pisarna ustvari račun, se kakovost aplikacije ne pokaže v njeni zasnovi. Pokaže se v tem, ali vsi vidijo popolnoma isto, pravilno stanje podatkov. Načrtovanje podatkovne zbirke MySQL za spletno aplikacijo torej ne pomeni čim hitrejšega ustvarjanja tabel. Pomeni dovolj natančno razumeti resnične delovne procese, da podatki ostanejo zanesljivi tudi pod obremenitvijo, med napakami, in ko podjetje raste.

Zlasti pri notranjih platformah, skladiščnih in naročilnih procesih, ali portalih za stranke se s podatkovno zbirko pogosto ukvarjajo prepozno. Najprej se zgradi vmesnik, nato se dodajajo polja, sledijo izjeme. To deluje za prototip. V praktičnem delovanju to privede do podvojenih podatkovnih nizov, nejasnih stanj, in poročil, ki jim nihče več popolnoma ne zaupa.

Načrtovanje podatkovne zbirke MySQL za spletne aplikacije: začnite z delovnim procesom

Prvi osnutek naj se ne začne z imeni stolpcev, temveč s konkretno delovno situacijo. Vzemimo prejem blaga: Dostava prispe, se dodeli dobavitelju in naročilu, količine se preverijo, dodeli se skladiščna lokacija, in zaloga se spremeni. Glede na obrat ta proces dodatno zahteva fotografije, kontrolo kakovosti, status zadržanja, ali sledljiv popravek. Iz tega delovnega procesa se izluščijo funkcionalni objekti. Tipični primeri so artikli, dobavitelji, naročila, pozicije, skladiščne lokacije, premiki zalog, in uporabniki.

Razlikovanje med objektom in dogodkom je ključno. Artikel opisuje, kaj nekaj je. Premik zaloge dokumentira, da se je količina spremenila na določeni lokaciji ob določenem trenutku. Mešanje obojega v eno samo tabelo hitro privede do izgube sledljivosti.

Nekaj trdih vprašanj pomaga za vsak objekt: Kaj je enolična identiteta? Katere informacije se smejo spreminjati? Kdo jih sme spreminjati? Katere podatke je treba zgodovinsko ohraniti? In katera pravila veljajo, ko dve osebi delata hkrati? Ta vprašanja bolje preprečijo poznejšo improvizacijo kot dolg seznam domnevno popolnih polj podatkovne zbirke.

Podatkovni model naj izraža pravila

Podatkovna zbirka ni zgolj shramba za vnose obrazcev. Sama naj uveljavlja osrednja pravila. Če mora vsak premik zaloge pripadati natanko enemu artiklu in eni skladiščni lokaciji, v model spadajo tuji ključi. Če se sme zunanja številka naročila pojaviti samo enkrat na najemnika, je potreben enoličen indeks. Če pozicija nikoli ne sme obstajati brez glavnega naročila, mora biti to razmerje jasno modelirano.

MySQL 8 z InnoDB za to zagotavlja trdne temelje: transakcije, tuje ključe, mehanizme zaklepanja, in dosledne spremembe prek več tabel. Ko se pri knjiženju prejema blaga zapiše premik, trenutna zaloga, in dnevnik pregleda, naj se to zgodi kot enotna transakcija. Če en korak spodleti, ne sme ostati polovično opravljena operacija.

Vendar ne spada vsako pravilo v podatkovno zbirko. Odobritve, zapletena logika oblikovanja cen, ali od vloge odvisni koraki procesa so pogosto bolje umeščeni v logiko aplikacije, ker se funkcionalno hitreje spreminjajo. Meja je pragmatična: pravila, katerih kršitev trajno poškoduje podatke, naj bodo zavarovana čim bliže podatkom. Pravila, ki se pogosto spreminjajo ali močno odvisijo od konteksta, zahtevajo dobro preizkušeno kodo aplikacije.

Ne zamenjujte zgodovine s trenutnimi vrednostmi

Pogosta napaka je shranjevanje samo trenutne zaloge ali trenutnega statusa. To zadostuje, dokler nekdo ne vpraša, zakaj se je količina včeraj spremenila, ali kdo je ponastavil naročilo. Za operativne sisteme je zgodovina premikov ali dogodkov pogosto dragocenejša od enega samega polja, ki ga je mogoče prepisati.

To ne pomeni trajnega beleženja vsakega klika. Beležiti je treba poslovno relevantne spremembe: spremembe statusa, spremembe količin, popravke, odobritve, in dodelitve. Dober vnos revizije vsebuje časovni žig, uporabnika ali sistemski proces, prejšnjo in novo vrednost, in razumljiv razlog, kadar to zahteva delovni proces. To omogoča razjasnitev napak, ne da bi bilo treba brskati po e-poštnih sporočilih, papirnih seznamih, ali varnostnih kopijah podatkovne zbirke.

Zavestno izbirajte ključe, podatkovne tipe, in poimenovalne konvencije

Tehnične odločitve se zdijo majhne, a oblikujejo vzdrževanje in integracije za leta vnaprej. Za notranje primarne ključe so vrednosti BIGINT s samodejnim dodeljevanjem pogosto trezna, enostavno obvladljiva izbira. UUID-ji so lahko smiselni, ko podatki nastajajo brez povezave, več sistemov piše neodvisno, ali zunanji vmesniki ne bi smeli razkrivati zaporednih ID-jev. Vendar stanejo več prostora za shranjevanje in zahtevajo nekoliko več pozornosti pri indeksih in razvrščanju.

Denarne zneske je treba shranjevati kot DECIMAL, ne FLOAT ali DOUBLE. Tudi količine potrebujejo funkcionalno ustrezno natančnost: število kosov je pogosto celo število, teže in dolžine pa niso. Časovne žige je treba obravnavati enotno, idealno interno v UTC, medtem ko vmesnik prikazuje lokalni časovni pas obrata. Zlasti med menjavami izmen in poletnim časom to prepreči težko odkrivna odstopanja.

Imena naj bodo tudi dolgočasna in nedvoumna. order_items ali inventory_movements so bolj koristna kot ustvarjalne okrajšave, ki jih razume samo prvotna projektna ekipa. Dosledna ednina ali množina je manj pomembna od doslednosti same. Enako smiselna so polja, kot so created_at, updated_at, in po potrebi deleted_at. Mehki izbris kljub temu ni standardna obveznost. Za pravno ali operativno relevantne zapise je čist storno običajno boljši od nevidno izbrisanega podatkovnega niza.

Indeksi sledijo dejanskim poizvedbam, ne ugibanju

Indeks lahko iskanje močno pospeši, a zaplete operacije pisanja in porabi prostor za shranjevanje. Zato „indeks na vsakem polju" ni strategija. Najpomembnejše poizvedbe je treba določiti zgodaj: odprta naročila stranke, premiki artikla znotraj obdobja, zaloga po skladiščni lokaciji, ali nedavno spremenjeni zapisi za vmesnik.

Tu je pomemben vrstni red sestavljenih indeksov. Če aplikacija redno išče po tenant_id, status, in created_at, je sestavljen indeks natanko v tem vrstnem redu pogosto smiseln. Ali dejansko ustreza, pokaže izvedbeni načrt z EXPLAIN, ne občutek. Podatkovne zbirke ne postanejo hitre zaradi spektakularnih trikov, temveč zaradi opazovanih poizvedb, ustreznih indeksov, in realistično testiranih količin podatkov.

Za rastoče tabele se izplača jasna strategija hrambe. Ali morajo tehnični dnevniki pet let sedeti v primarni produkcijski podatkovni zbirki? Ne nujno. Poslovni zapisi, premiki, in dokazi pregledov zahtevajo drugačne roke hrambe kot informacije za razhroščevanje. Arhiviranje ni znak šibkega sistema, temveč premišljena operativna odločitev.

Delovanje z več uporabniki zahteva transakcije in jasna stanja

V spletni aplikaciji do istih podatkov hkrati dostopa več zahtev. To je v vsakodnevnem skladiščnem delovanju normalno, ne izjema. Dva zaposlena lahko knjižita isto zalogo, medtem ko uvoz ustvarja nova naročila. Brez transakcij in ciljanega zaklepanja obstaja tveganje izgubljenih sprememb ali negativnih zalog, ki postanejo vidne šele tedne pozneje.

Za kritične operacije mora biti jasno, kateri podatki se v okviru transakcije berejo in pišejo. Včasih zadostuje atomarna posodobitev, na primer zaloga, ki se spremeni samo, če je razpoložljiva količina zadostna. V drugih primerih je smiselno zaklepanje vrstice, da lahko operacija nadzorovano preveri stanje podatkov in ga nato spremeni. Dolge transakcije so po drugi strani problematične: blokirajo drugo delo in povečajo tveganje konfliktov.

Enako pomemben je omejen nabor funkcionalnih stanj. Naročilo ne bi smelo biti hkrati „odprto", „delno dostavljeno", in „ročno obdelano" zaradi nasprotujočih si vzdrževanih polj. Opredeljeni prehodi stanja poenostavijo vmesnike, poročila, in avtomatizacije. Izjeme so lahko dovoljene, a naj bodo poimenovane in dokumentirane.

Od začetka načrtujte varnost, najemnike, in delovanje

Aplikacija naj za MySQL uporablja namenskega uporabnika podatkovne zbirke z minimalnimi pravicami. Pravica pisanja za spletno aplikacijo ne pomeni, da mora ta uporabnik brisati tabele ali spreminjati pravice uporabnikov. Administrativni računi ne spadajo v produkcijske konfiguracijske datoteke in nikoli v repozitorij.

Ko znotraj aplikacije deluje več strank, lokacij, ali podjetij, je izolacija najemnikov arhitekturna odločitev, ne naknaden filtrirni pogoj. Skupna podatkovna zbirka z tenant_id je lahko učinkovita in enostavno vzdrževana, a zahteva dosledne preverbe v vsaki poizvedbi in jasna pravila za indekse. Ločene podatkovne zbirke ponujajo močnejšo izolacijo, a povečajo napor pri posodobitvah, evalvacijah, in delovanju. Katera različica ustreza, je odvisno od zahtev glede varstva podatkov, obsega podatkov, in poslovnega modela.

Varnostne kopije so varnostne kopije šele, ko je bila obnovitev preizkušena. Potreben je opredeljen ritem varnostnih kopij, hrambe, in obnovitve. Prav tako v sistem spada nadzor prostora za shranjevanje, počasnih poizvedb, in neuspelih opravil, skupaj z dokumentiranimi posodobitvami. MySQL 8, PHP 8.4, in sodobne spletne aplikacije je mogoče dolgoročno dobro upravljati, če odvisnosti, dostopni podatki, in koraki uvajanja ne obstajajo samo v glavi enega razvijalca.

Smiseln načrt pred prvim dnem v produkciji

Pred izvedbo naj obstaja strnjen podatkovni model s primeri delovnih procesov. To vključuje ključne tabele in razmerja, pravila stanj, dovoljenja, pričakovane poizvedbe, vmesnike, in koncept za varnostne kopije in revizijske dnevnike. Ta načrt ni treba, da je dolg sto strani. Mora zajeti odločitve, katerih poznejši popravek bi bil drag.

Pri softify.pro se načrtovanje podatkovne zbirke zato začne z ljudmi, ki knjižijo, preverjajo, komisionirajo, ali rešujejo izjeme. Če obstoječa preglednica zanesljivo odraža obvladljiv proces, lahko ostane pravilna rešitev. Če hkrati dela več ljudi, nastajajo zapisi, in napake morajo biti sledljive, si podatkovna zbirka nasprotno zasluži enak napor pri načrtovanju kot vmesnik. Najboljša arhitektura je na koncu tista, ki poenostavi delovni dan in jo je čez dve leti še vedno mogoče pregledno spremeniti.