1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Help! MySQL

Discussion in 'Ajutor: Software și sisteme de operare' started by Marius '95, Feb 4, 2014.

  1. Marius '95

    Marius '95 Membru Senior

    Nu stiu unde a disparut topicul vechi de MySQL... Fac altul.

    Am un tabel MySQL care contine raspunsurile unor pacienti la niste chestionare. Coloanele sunt intrebarile: A1,A2,A3,B1,B2,B3,...,B12,C,D1,D2...etc.

    Intrebari:
    -Cum fac un SELECT aplicand aceeasi conditie WHERE pe mai multe coloane consecutive simultan fara sa le enumar?
    -Cum obtin media valorilor din fiecare coloana fara sa enumar coloanele?

    Multumesc!
     
  2. wirespot

    wirespot Membru Senior

    Nu faci. Il intrebi pe cel care a proiectat monstruozitatea care parte din cuvantul "relational" n-a priceput-o. Datele trebuiau plasate pe randuri, iar coloanele folosite pentru clasificarea abstracta. Atunci puteai face tot ce vreai tu.
     
  3. puterfixer

    puterfixer Administrator

    Faci o structură normalizată pentru baza de date, transpui datele din tabela tăntălăului cu rol de DBA, apoi faci interogarea pe structura ta.
     
  4. termita

    termita Membru Senior

    Construiesti interogarea dinamic in PHP sau vreun alt limbaj in care sa te poti juca cu string-uri.
     
  5. puterfixer

    puterfixer Administrator

    Adică îmbraci o tâmpenie într-o complicație și speri că nu începe să pută :biggrin:
     
  6. termita

    termita Membru Senior

    El pare sa vrea rezolvare acum, nu re-proiectarea structurii initiale. Nu e frumos da-i sanatos.
    In plus, i-ati dat deja voi solutiile intelepte :biggrin:
     
  7. Marius '95

    Marius '95 Membru Senior

    Cred ca o sa import in Excel prin ODBC si cu asta gata. Thx!
     
  8. Marius '95

    Marius '95 Membru Senior

    Un analizor transmite imagini 128x128, 4 culori. Care ar fi cea mai eficienta metoda de a stoca imaginile intr-o baza de date MySQL?
     
  9. miahi

    miahi Wizzard

    Teoretic imaginea are ~32KB necompresată, așa că încape într-un BLOB fără modificări de variabile sistem. Cu o compresie png lossless peste iese mult mai mică, deci nu-i bai de stocat. Atenție însă la modul în care engine-ul face alocarea blocurilor pentru LOB-uri, în funcție de opțiunile alese variind destul de mult - ex: te trezești că-ți papă 16KB per imagine chiar dacă BLOB-ul tău are 1KB, pentru că aliniază LOB-urile în blocuri externe. Vezi aici mai multe opțiuni: http://www.mysqlperformanceblog.com/2010/02/09/blob-storage-in-innodb/, din păcate nu știu nici eu suficient mysql ca să-ți dau o soluție clară (caută echivalentul enable storage in row din oracle db).

    Best practices: ținut LOB-urile în altă tabelă decât cea cu metadate; o tabelă cu un PK și LOB-ul, după care le referențiezi cu PK-ul unde ai nevoie. În stilul ăsta scapi de parcurs LOB-uri atunci când nu ai nevoie de ele. Mai e utilă și partiționarea unei astfel de tabele, fie că o faci manual (ex: tabela "imagini0000" pentru imaginile cu ID<10000, tabela "imagini0001" pentru 10000<=id<20000) sau prin opțiunile DB engine (range partitioning). Astfel nu te trezești că ai o tabelă de 500GB plină cu LOB-uri și nu prea ai de unde s-o mai apuci.
     
  10. Marius '95

    Marius '95 Membru Senior

    M-ai pierdut. Eu folosesc MySQL v3.23 cu baza de date MyISAM.
    Am retinut totusi sugestia sa comprim PNG inainte sa stochez.

    Analizorul transmite imaginea cu 4 culori (2 biti) sub forma a doua siruri de valori hex corespunzatoare celor doi biti. Sirurile sunt comprimate cu ceva asemanator RLE.

    Teoretic vorbind, care varianta este mai eficienta:
    - convertit din hex in binar si stocat fara alte procesari in baza de date
    - construit imaginea, comprimat intr-un format standardizat si salvat asa in baza de date
    Tind sa cred ca a doua varianta.

    Acum, referitor la format, GIF sau PNG? (sau altceva?)
    Imaginea este ceva de genul celor atasate, doar ca are doar 4 culori, inclusiv fundalul.
     

    Attached Files:

  11. miahi

    miahi Wizzard

    Depinde de ce faci cu imaginile mai departe: îți mai trebuie versiunea inițială, în formatul dubios (există softuri care să lucreze cu acele date?) sau trebuie doar să le afișezi. E bine să ții datele în formatul cel mai ușor de "consumat", să nu le transformi de fiecare dată când ai nevoie de ele, și să nu le ții în formate/encodings dubioase, care nu îți vor mai spune nimic peste 2 ani (și trebuie s-o iei de la cap să înțelegi ce ai vrut să faci cu ele :smile:).

    Formatul PNG are compresie ceva mai bună decât GIF, dar probabil ai putea face niște teste.

    Ce ziceam mai sus: baza de date păstrează BLOB-urile (binary large objects, ceea ce o să fie și imaginea ta) asemănător cu modul în care o face un sistem de fișiere. Adică alocă blocuri, iar fiecare bloc are un tip (ex: bloc în care se păstrează rânduri de tabele, bloc în care se păstrează BLOB-uri). Adresarea datelor se face la nivel de bloc (ex: când faci un index pe o tabelă, indexul păstrează adresa blocului de date). Pentru a nu apărea probleme de performanță, dimensiunea unui bloc din DB e cel puțin egală, dacă nu mai mare decât dimensiunea unui cluster din filesystem. Dacă la stocarea de rânduri de tabelă nu e o problemă dimensiunea blocului (pentru că poți avea mai multe rânduri într-un singur bloc), stocarea BLOB-urilror se face cu blocuri dedicate, adică nu poți avea două BLOB-uri într-un singur bloc, rezultând aceeași problemă pe care o ai într-un file system: ai un obiect de 1KB dar el îți ocupă un întreg bloc de DB (care poate fi 4/8/16KB, depinde de opțiuni și versiunea de mysql). Va trebui să vezi dacă acest mod de stocare nu o să îți consume (mult mai) mult spațiu decât te-ai aștepta.

    Referitor la partiționare: în funcție și de câte astfel de imagini o să ai, poți avea probleme când se adună multe, pentru că nu va fi ușor de manevrat o astfel de tabelă.
     
  12. Marius '95

    Marius '95 Membru Senior

    Vor fi vreo 10-15 astfel de grafice adaugate zilnic. Nu am nevoie de structura initiala transmisa de analizor. De fapt, ma cam incurca. Fiecare grafic va fi adaugat o data (niciodata modificat), citit de 2-3 ori in aceeasi zi si apoi nu va mai fi citit probabil niciodata.

    Ma apuc sa scriu php-ul care va construi imaginea din sirurile alea. Vad ce rezulta si apoi ma mai gandesc cum stochez.
    O sa folosesc php_gd.dll ca mi-e lene sa fac PNG manual. :tongue:
     
  13. Marius '95

    Marius '95 Membru Senior

    Am o baza de date foarte simpla care stocheaza pacienti, solicitari de analize si rezultatele la analize. Din motive de acreditare, trebuie restructurata si facuta sa tina minte mult mai multe date decat stocheaza in prezent. Am nevoie de ajutor in a o "proiecta". Pls, help!

    Trebuie sa stochez:
    - datele pacientului (CNP, nume, etc.)
    - cererea de analize (trimiterea, daca exista, si analizele de pe ea, plus ce analize mai solicita pacientul in plus fata de trimitere)
    - codul probei si date despre ea (ora, locul recoltarii, sosire la laborator, degradata sau nu, etc.)
    - rezultatele la analize

    Probleme:

    - Rezultatele pot fi de diverse tipuri: text, numar, varianta aleasa dintr-o lista, grafic (sir de numere), imagini in formate si dimensiuni variate, etc. La fiecare tip de rezultat trebuie stocate alte informatii aditionale: la numerice trebuie interval de referinta in timp ce la text nu-i nevoie.
    Cum le stochez toate astea? Tabele separate dupa tipul de rezultat?

    - O singura analiza poate avea mai multe rezultate din formate diferite, dar unele analize cuprind rezultate ce pot fi cerute si ca analiza separata: electroforeza include valoare pentru proteinele totale, care este si analiza separata.
    Cum definesc analizele?
    Pana acum defineam fiecare rezultat posibil intr-un tabel: Cod / Nume / Tip rezultat / Unitate de masura / etc. Fiecare rand era considerat o analiza separata cu un singur rezultat. Nu existau analize cu mai multe rezultate.

    - Fiecare analiza poate fi lucrata prin mai multe metode si fiecare metoda are tipul ei de rezultat si intervale de referinta. Metoda si intervalele trebuiesc tinute minte la fiecare rezultat. Exemplu: daca peste 2 ani modific metoda si intervalul de referinta la o analiza, daca afisez un buletin din urma, trebuie sa apara cu metoda si intervalul vechi.
    Cum fac? Stochez metoda si intervalul la fiecare rezultat? Nu irosesc spatiu? Sau definesc intervalul intr-un tabel cu metode si stochez doar metoda? Sau fac "history" cu metode si la fiecare afisare de buletin caut metoda si intervalul valabil la momentul respectiv?

    - Mai multe analize se lucreaza dintr-o proba (asta-i simplu), dar exista analize la care trebuie mai multe probe: testul de toleranta la glucoza (3 probe). Cum fac? Stochez codul probei la fiecare rezultat in loc sa-l stochez o singura data in cererea de analize? Nu irosesc spatiu aiurea?

    - Vine un pacient cu o trimitere => mai multe analize pe trimiterea aia. Mai cere si analize suplimentare, si toate se lucreaza din aceeasi proba. Cum stochez care analiza pe care cerere este?
     
  14. miahi

    miahi Wizzard

    Ai pornit cumva greșit. Niciodată în DB nu e vorba de irosit spațiu, e vorba de organizare bună a datelor :smile:. Într-o bază de date bine făcută de obicei indecșii ocupă mai mult spatiu decât datele în sine. Citește despre design și forme normale (unu, doi, trei). În esență împarți cât de cât în tabele datele tale, după care aplici regulile de normalizare:

    Prima formă normală (1NF)
    Entitățile nu conțin grupuri repetate de date (liste)
    Fiecare entitate are un identificator (cheie primară), care poate fi explicită (ID), dar poate fi și implicită (nu sunt rânduri duble)

    A doua formă normală (2NF)
    Entitățile sunt în prima formă normală și atributele non-cheie au legătură cu cheia primară. Dacă cheia primară este compusă, atributele non-cheie trebuie să se refere la toate componentele cheii respective.

    A treia formă normală (3NF)
    Entitățile sunt în a doua formă normală și toate atributele depind direct de cheia primară (toate atributele unei entități se referă la toate componentele cheii primare și nu sunt dependente una de alta)

    Și un exemplu de normalizare făcut de mine pentru o prezentare aici (din păcate fără comentarii).

    Cel mai simplu din punctul meu de vedere e să desenezi ce tabele vrei și relațiile între ele. Am desenat mai jos ceva care corespunde cu 80% din cerințele tale (ex: nu e modelată relația între probă și rezultat), din păcate nu am timp de mai mult. Oricum, nu e considerat un task ușor un design de genul ăsta (iar teoria normalizării de mai sus era curs de un semestru), tu ai noroc că știi măcar business-ul bine. Vezi și many to many (tabele de legătură) și ce mai e linkat pe acolo.

    2017-07-19 15.21.28_small.jpg
     
    icsfails, jarod and puterfixer like this.
  15. Marius '95

    Marius '95 Membru Senior

    Multumesc. Se pare ca am de citit...
     
  16. miahi

    miahi Wizzard

    Primul lucru pe care poți să-l faci, pentru că știi mai mult business-ul, e să scrii într-un excel toate câmpurile de care ai nevoie (cum am început eu în poză) și să le dai o .

    Ex: Persoana are nevoie de:
    id (int, primary key, autoincrement)
    cnp (char)
    nume (char)
    prenume
    ... ce mai ai nevoie ...

    Cererea are nevoie de:
    id (int, primary key, autoincrement)
    dată (date)
    număr (char)
    persoană (well, aici incepe să fie interesant, ar trebui o legătură către persoană; adică int și fk către persoana)

    și tot așa.

    Formele normale o să ți se pară naturale după ce te obișnuiești, nici nu te mai gândești la ele.
     
  17. Marius '95

    Marius '95 Membru Senior

    Am citit, nu prea am inteles de la 2NF incolo, dar eu zic ca ma descurc cu partea aia. De facut efectiv stiu s-o fac. Problema mea este cum e mai bine/eficient/usor. Imi lipseste experienta practica. Hai sa o luam punctual:
    Fiecare analiza poate fi lucrata prin mai multe metode si fiecare metoda are intervale de referinta diferite. Mai mult, intervalele de referinta sunt actualizate din cand in cand. Metoda si intervalele trebuiesc tinute minte la fiecare rezultat. Exemplu: daca peste 2 ani modific intervalul de referinta la o analiza, daca afisez un buletin din urma, trebuie sa apara cu intervalul vechi.

    Varianta A:
    E varianta actuala, simpla. Tabel cu rezultate ca in Excel, cate o proba pe cate un rand, coloanele stocheaza rezultatele la fiecare analiza (numerice, text, ENUM, etc. dupa caz), cate unul pentru fiecare analiza. Idem un tabel cu intervale de referinta, doar la acele analize care au asa ceva.
    Avantaje: la afisarea unui buletin se citeste un singur rand, integritate mai buna a datelor, nu sunt duplicate ale datelor deci eficient ca storage pentru un numar mic de analize posibile.
    Dezavantaj: Daca laboratorul are un numar mare de analize disponibile, multe coloane vor fi cu NULL. La un moment dat, acele NULL vor fi mai mari (ca storage) decat o cheie primara necesara pentru normalizare. Faptul ca sunt si coloane TEXT si BLOB face cautarea randului I/O-intensiva.
    PS: in prezent, in forma actuala, baza de date are 70 MB, cu datele pe 10 ani.

    Varianta B:
    Tabele separate dupa tipul de rezultat cu: [cod proba, cod analiza,] rezultat, metoda, interval de referinta, etc. unde [cod proba, cod analiza] e cheie primara.
    Avantaje: mai putin spatiu irosit cu NULL cand laboratorul are multe analize disponibile, nu mai exista tabele separate pentru metode, intervale de referinta, etc.
    Dezavantaj: Informatia utila este rezultatul. 99% din ele sunt enum (1byte) sau numerice (4bytes). Cod proba si cod analiza vor aparea la fiecare analiza solicitata, au impreuna 20 B, intervalul de referinta are 8B, metoda 8B, incertitudinea 4B si cred mai sunt si altele... deci overhead minim 1000%. :frown: Baza de date va fi imensa, cativa GB sau zeci de GB, n-o sa mai incapa in memorie si va trebui citita de pe disc. Obtinerea datelor din ea va fi greoaie fiindca un singur pacient avea zeci de analize, deci la fiecare buletin vor trebui citite zeci de randuri. Procesarea este mai dificila pentru a le afisa/printa.

    Varianta 3:
    Ca varianta 2, dar stochez intervalul de referinta intr-un tabel cu metoda, iar langa rezultat nu stochez decat metoda, nu si interval de referinta, incertitudine, etc.
    Avantaje: overhead mai mic, dar tot vreo 700%. Tot n-o sa incapa in memorie.
    Dezavantaje: procesare si mai greoaie.

    Varianta 4:
    Tabel cu rezultate si, separat, tabel cu metode, parametrii metodei si data intrarii in vigoare. La afisarea buletinului, caut la fiecare analiza metoda valabila la data respectiva si iau de acolo intervale, etc.
    Avantaje: overhead "doar" 500%, adica doar cheia primara in plus fata de rezultat.
    Dezavantaje: procesarea pentru a afisa un buletin este enorm de grea, atat ca linii de cod de scris, cat si munca procesorului dupa.

    Deci care ar fi varianta buna?
    Doar mi se pare mie sau imi trebuie NoSQL?
     
    Last edited: Jul 20, 2017
  18. puterfixer

    puterfixer Administrator

    Să presupunem că o analiză are:
    • un cod unic, pentru identificare;
    • un nume;
    • o metodă de analiză;
    • o unitate de măsură;
    • o valoare minim normală;
    • o valoare maxim normală;
    • un flag binar pentru „activă”;
    • eventual precizie, o dată de activare și o dată de dezactivare, alte elemente.
    De exemplu, pot avea 3 înregistrări pentru măsurat tensiunea arterială (știu că nu e o analiză, e doar un exemplu ușor de înțeles), pentru tensiometru digital la încheietură, pentru tensiometru digital pe braț, și pentru tensiometru cu capsulă și stetoscop. Toate trei sunt active, și cererea cuiva va avea de exemplu ID-ul măsurătorii cu tensiometrul digital de la încheietură.

    Dar la un moment dat se fufu tensiometrul, sau se înlocuiește cu unul mai precis; atunci la analiza respectivă i se dezactivează flagul de „activ” pentru a nu mai putea fi selectată pentru analize viitoare, însă rămâne în baza de date și păstrează referința în istoricul de analize. Vei adăuga o înregistrare nouă pentru măsurat tensiunea arterială cu noul tensiometru, cu flagul activ, ca să poată fi selectată pentru analize solicitate.

    Cred că te complici foarte mult dacă lucrezi cu intervale de timp, fiindcă orice interogare va da mai mult de lucru bazei de date, ori poți rezolva problema elegant cu un flag de activ/inactiv pentru solicitări noi, respectiv ID-uri unice pentru fiecare formă a unei analize în timp.

    A doua tabelă ar fi cu solicitările de analize, care ar avea un ID de solicitare, un ID de pacient, o dată a solicitării, un câmp de status, un câmp de observații.

    A treia tabelă ar avea conținutul analizelor propriu-zise, legături many-to-many între ID-ul unei solicitări, ID-ul unei analize și valoarea analizei.

    Dacă chiar vrei, poți avea încă o tabelă intermediară de analize compuse, în care să definești grupări de markeri individuali. De fapt vor fi două tabele, una cu ID de grupare și nume de grupare, și a doua cu relația many-to-many între ID de grupare și ID de analiză.
     
    miahi likes this.
  19. miahi

    miahi Wizzard

    Tu ai niște probleme de abstractizare, te gândești prea mult ce se întâmplă la nivel de octet, chiar dacă asta a) nu îți rezolvă efectiv problema și b) imaginea pe care o ai despre ce se întâmplă la nivel de octet e incorectă.

    Problemele de performanță la acces în bazele de date au fost rezolvate acum vreo 20-30 de ani, nu trebuie să-ți faci probleme de ce face "în spate" o bază de date, o să-ți dea rezultate rapid cât timp e corect indexată. Ex: coloanele TEXT și BLOB sunt în general ținute separat, în afara rândului efectiv, tocmai ca să nu impacteze performanța căutărilor; pe rând ai doar pointeri către storage-ul de LOB. Citirea de pe disc nu se face la nivel de rând, se face la nivel de bloc, iar un index îți spune care blocuri trebuie citite (iar dacă e chiar bine făcut, nici nu trebuie să citească datele efective, îți poate da răspuns direct). Baze de date de zeci de GB? Un mizilic, rezultatul unui query pe PK e instant. Baze de date de zeci de TB? Aici problema e de obicei cât I/O ai, nu faptul că e dimensiunea mare (ca să ajungi la zeci de TB de obicei scrii în DB cel puțin la nivelul de MB/s, dacă nu mai mult, și poate sute de utilizatori în paralel); și tot poți obține performanțe de răspuns remarcabile și așa. Ex: am o tabelă cu 157 milioane de înregistrări/8GB date/6GB indecși, este parcursă complet (count) în 43 de secunde, dar datele de pe ultima zi (220k înregistrări) sunt aduse în 400ms.

    Varianta 3 pare ok, cu mențiunile lui puterfixer de mai sus. Varianta 4 e overkill dacă nu ai cerințe speciale de istoric, cu posibilitatea modificării datelor din rezultate în timp (ceea ce nu e cazul aici).

    NoSQL: Cam asta cred toți ăia care nu știu să lucreze cu baze de date, că NoSQL e soluția pentru orice. Da, rezolvi rapid problema de stocare de date cu NoSQL, dar de fapt doar muți problemele efective în altă parte, în programul cu care accesezi datele, care va fi mai complex și mai greu de modificat. Iar obținerea unui raport de tipul "câți pacienți au cerut analiza X în ultimii 3 ani" din NoSQL... mult succes (inclusiv cu operatori de lookup). Datele tale sunt relaționale, n-are rost.
     
    puterfixer likes this.
  20. Marius '95

    Marius '95 Membru Senior

    Bine atunci, fac varianta 3.