Optimizarea bazelor de date: agregarea datelor
September 28, 2008 – 11:01 pmEra MySQL 4.x a trecut de mult, ca să nu mai vorbesc de 3.x. Oh, ce vremuri de „pionierat”.
MySQL 5 a venit cu o gramada de bunătăți (ce existau demult în alte DBMS) și totuși, încă văd o mulțime de aplicații web care nu folosesc avantajele oferite de noua versiune. De fapt, de ce mă mir? Încă văd aplicații ce se doresc a fii serioase și folosesc tabele MyISAM. Tranzacții? Foreign keys? Prostii…cine are nevoie de ele?
Dar nu despre tranzacții și foreign keys vreau să vorbesc, ci despre triggers – o funcționalitate apărută în MySQL 5. Orice programator care a avut de-a face cu baze de date ar trebui să știe ce e un trigger, așa că nu o să explic conceptul. O să dau doar un exemplu în care un trigger poate să-ți ușureze viața. Presupunem că avem următoarele tabele:
| customer | customer_payments |
| customer_id | payment_id |
| firstname | customer_id |
| lastname | payment_amount |
Pentru a afla totalul plăților făcute de fiecare client în parte, avem un query de genul:
Cazul cu un singur join este unul fericit și l-am luat doar pentru simplitatea exemplului. În realitate însă, query-urile de acest gen pot deveni mari consumatoare de resurse.
Putem scăpa de join-ul respectiv dacă adaugăm un câmp în tabela customer, câmp care va conține suma tuturor plăților făcute de un client. Noua tabelă va arăta așa:
| customer |
| customer_id |
| firstname |
| lastname |
| total_payments |
Câmpul total_payments va fi actualizat automat (cu ajutorul unui trigger, evident) la fiecare adăugare, ștergere sau modificare a unei înregistrări din tabela customer_payments.
Costul actualizării câmpului total_payments este net inferior costului unui JOIN făcut de mii de ori pe zi.
/Actualizare: poate un exemplu mai bun era cu articolele dintr-un blog și comentariile la articole.
/Actualizare 2: vreau să accentuez faptul că soluția dată mai sus trebuie aplicată doar când situația o cere, nu la fiecare join cu group by si sum()
4 Responses to “Optimizarea bazelor de date: agregarea datelor”
Nu stiu daca iti dai seama dar exemplul dat este unul destul de simplist si care nu ajuta foarte mult pentru ca in 90% din cazuri TREBUI sa ai o tabela separata pentru comenzile clientului.
Intr’adevar, denormalizarea este practicata dar de la un anumit nivel in sus . Nu merita incurajata chestia asta pentru ca fiecare gigel cu 3 tabele zice ca il costa mai mult un join decat o normalizare
. Ceea ce e destul de stupid.
Iar cand chiar este nevoie, deja e cineva care se pricepe in spatele bazei de date
.
altfel, cei care inca sunt la mysql4 … nu cred ca stiu de foreign key & the like.
By sirrocco on Sep 29, 2008
@sirrocco, am specificat în articol că exemplul este simplist, nu am vrut să pun cititorii să citească un query pe 3 rânduri. Bineînteles, nu trebuie să faci agregarea de fiecare dată când ai un join și un sum().
By Mihai Brehar on Sep 29, 2008
Prima remarca: nu exista o versiune MySQL 5, exista un 5.0.x si o versiune 5.1.x, care va iesi la sfarsitului lui 2008, inceput 2009. Diferentele de la 5.0 la 5.1 sunt majore.
A doua remarca: folosirea MyISAM e inca de actualitate. Alegerea motorului de stocare, InnoDB sau MyISAM, depinde foarte mult de tipul de date care urmeaza sa fie stocate in tabele. Ca exemplu, avind doua tabele pe care vrei sa le ‘legi’ logic, nu trebuie sa folosesti implicit InnoDB ci sa executi o serie de teste pe date relativ reale si sa te asiguri ca InnoDB e mai rapid decit MyISAM cu replicarea actiunilor de DELETE/UPDATE implementate la nivel aplicativ.
A trei remarca: in versiunea actuala, 5.0, triggerele nu garanteaza punerea la zi a unui cimp. Exista inca destul de multe probleme de acces concurent la date care apar in servere MySQL incarcate cum trebuie ( > 2500 qps.). Utilizarea unei tabele adiacente, cu risc de introducere a unei redundante la nivelul datelor stocate, e mai mult decit recomandata.
A patra remarca: un JOIN nu e neaparat costisitor. Daca tabelele (coloanele) care intra in componenta JOIN-ului nu sunt puse la zi foarte des, MySQL va returna ultimul rezultat (query_cache). O baza de date care are tabele indexate cu grija si care are o structura ingrijita fara aberatii de tipul ‘pun tip de cimp la TEXT in loc de VARCHAR(64) doar ca sa stochez numele clientului’ poate sa se dovedeasca extrem de rapida la un JOIN chiar daca seturile de date implicate depasesc citeva milioane de inregistrari.
Ar trebui sa refaci articolul dupa o documentare mai serioasa si eventual sa alegi un exemplu mai percutant.
By Antoniu-George SAVU on Sep 30, 2008
@Antoniu, mersi pentru un comentariu asa stufos.
Legat de MyISAM vs. InnoDB, când văd o aplicaţie de contabilitate sau una de gestiune de stoc folosind MyISAM, mi se scoala părul de pe mâini. De ce să execuţi o serie de teste, când poţi să foloseşti foreign keys?
Legat de restul comentariului, repet a treia oară, optimizarea cu agregarea datelor trebuie făcută cu cap, atunci când e nevoie.
By Mihai Brehar on Sep 30, 2008