- 1. Convenția de numire
- 2. Folosiți întotdeauna tipul corect de date
- 3. Folosiți CHAR (1) peste VARCHAR (1)
- 4. CHAR pentru date fixe
- 5. Evitați utilizarea formatelor de date regionale
- 6. Optimizați solicitările pentru cache
- 7. Evitați să utilizați „SELECT *” în interogările dvs.
- 8. Utilizați LIMIT 1 când doriți doar un singur rând
- 9. Utilizarea ***** BY
- 10. Alegeți un motor de bază adecvat
- 11. Utilizați clauza EXISTS ori de câte ori este necesar
- 12. Utilizați EXPLAIN în interogările dvs. SELECT
- 13. Indexați și utilizați același tip de coloană pentru Alăturări
- 14. Folosiți NOT NULL dacă puteți
- 15. Tabelele cu dimensiuni fixe (statice) sunt mai rapide
- 16. Partiționare verticală
- 17. Stochează adresele IP ca UNSIGNED INT
- 18. Creați vizualizări pentru a simplifica utilizarea obișnuită în tabele
- 19. Nu utilizați ***** BY RAND ()
- 20. Optimizați clauza WHERE
Baza de date MySQL a devenit cea mai populară bază de date relațională open source din lume datorită performanței sale ridicate, consistenței, fiabilității ridicate și ușurinței de utilizare. Cu toate acestea, aceste beneficii pe care ni le oferă sunt adesea afectate de modul în care lucrăm la el.
În acest tutorial veți învăța o serie de sfaturi care vor fi foarte utile și ne vor permite să profităm la maximum atât din punctul de vedere al programatorului, cât și al administratorului bazei de date.
Ce instrument vom folosi?Consola de comandă MySQL '] MySQL are un program, numit cu același nume ca baza de date (mysql) care este utilizat pentru a gestiona baza de date prin linia de comandă.
WindowsSe află într-un director precum:
C: \ Program Files \ MySQL \ MySQL Server 5.6 \ bin
Directorul poate varia, de exemplu, poate fi localizat în rădăcina discului C: sau în orice alt loc în care am instalat MySQL. Pentru a accesa consola MySQL din Windows va trebui să ne aflăm în acel director.
LinuxVezi următorul link:
Accesați MySQL din Linux
MacCalculatoare cu sistem Mac OS X au terminalul de linie de comandă încorporat printre aplicațiile disponibile. Pentru a accesa aceeași comandă este utilizată ca în Linux.
phpMyAdminEste un instrument software gratuit scris în PHP, care este utilizat pentru administrarea MySQL prin Internet. Dacă aveți un mediu de dezvoltare local instalat, cum ar fi XAMPP sau WAMP, acest instrument va fi deja instalat. Dacă aveți un plan de găzduire cu un panou administrativ, majoritatea oferă acest instrument ca administrator de baze de date MySQL.
[color = rgb (169,169,169)] phpMyAdmin de la XAMPP [/ color]
MARI
[color = # a9a9a9] phpMyAdmin de la CPanel [/ color]
Având la îndemână aceste 2 instrumente, putem începe să testăm toate aceste bune practici pe care le prezentăm mai jos.
Acestea sunt Top 20 cele mai bune practici MySQL:
1. Convenția de numire
Există standarde de codare pentru fiecare limbaj de programare, dar în termeni MySQL nu am găsit practici de codare universale pe care să le urmeze toată lumea. Cu toate acestea, examinând mai multe cadre open source bazate pe PHP, filtrăm câteva reguli generale de aplicație care ne vor ajuta să scriem mai rapid interogări SQL, să eliminăm confuziile și conflictele, atât în interogare, cât și în limbajul de programare pe care îl folosim.
Reguli generaleRețineți următoarele reguli pentru a evita problemele.
- Folosiți litere mici, deoarece vă ajută la viteza de tastare, va evita erorile în cazul funcțiilor sensibile cu litere mici și majuscule etc.
- Nu utilizați spațiul alb, folosiți în schimb sublinierea (_).
- Nu utilizați numere în nume, ci doar caractere alfabetice englezești.
- Folosiți nume valabile de înțeles.
- Numele ar trebui să se explice de la sine.
- Numele nu trebuie să conțină mai mult de 64 de caractere.
- Evitați să folosiți prefixe.
Reguli pentru numele bazelor de dateRespectați toate regulile generale de mai sus.
- Numele poate fi atât singular, cât și plural, dar baza de date reprezintă o bază de date, deci ar trebui să fie singular cât mai mult posibil.
- Evitați prefixele acolo unde este posibil.
Reguli pentru numele tabelelorUtilizați minuscule pentru numele tabelelor: MySQL este de obicei găzduit pe servere Linux, este sensibil la majuscule, așa că cea mai bună practică este să scrieți minuscule numele tabelelor.
- Numele tabelelor trebuie să fie la singular: tabelul este o entitate unică la fel ca modelul, deci este ciudat să ai numele tabelului la plural.
- Prefixe în numele tabelului: Am văzut de multe ori că tabelele sunt prefixate cu numele bazei de date sau cu numele proiectului. Uneori acest lucru este necesar atunci când în proiectul nostru avem multe baze de date pentru a depăși limitarea unor furnizori de găzduire. Dar dacă nu este necesar și proiectul nostru este mic, evitați să utilizați prefixe.
Numele câmpurilorUtilizați toate regulile de mai sus, adică utilizați minuscule, nu utilizați spații goale, nu utilizați numere și evitați prefixele.
- Folosiți unul sau două cuvinte scurte acolo unde este posibil.
- Numele câmpurilor trebuie să poată fi înțelese, de exemplu: preț, nume_de companie etc.
- Nume coloană principală: cheia primară poate avea un nume de ID sau un nume de tabel _id. Asta va depinde de alegere.
- Evitați să folosiți cuvinte rezervate în câmpurile: *****, dată etc. În aceste cazuri este de preferat să folosiți prefixe precum record_date etc.
- Evitați să folosiți nume de coloane cu același nume de tabel. Acest lucru poate provoca confuzie atunci când scrieți interogările dvs.
- Evitați numele în acronime prescurtate sau concatenate.
2. Folosiți întotdeauna tipul corect de date
Folosiți tipuri de date pe baza naturii datelor. Utilizarea unor tipuri de date irelevante poate ocupa mai mult spațiu sau poate duce la erori.
ExempluUtilizarea varchar (20) pentru a stoca valorile datetime în loc de DATETIME poate provoca erori în timpul calculelor legate de dată și este posibilă și în cazul stocării datelor nevalide.
3. Folosiți CHAR (1) peste VARCHAR (1)
Dacă ați stocat un singur șir de caractere, folosiți CHAR (1) în loc de VARCHAR (1) deoarece VARCHAR (1) va lua un octet suplimentar pentru a stoca informații. Cu care să-l țineți cont atunci când gestionați aceste personaje.
4. CHAR pentru date fixe
Utilizați datele CHAR pentru a stoca numai datele cu lungime fixă
Exemplu:
Utilizarea CHAR (1000) în loc de VARCHAR (1000) consumă mai mult spațiu dacă lungimea datelor este mai mică de 1000.
5. Evitați utilizarea formatelor de date regionale
Când utilizați tipuri de date:
- DATETIME
- DATA
Utilizați întotdeauna formatul AAAA-LL-ZZ sau formatul de dată ISO care se potrivește cu motorul dvs. SQL. Alte formate regionale, cum ar fi ZZ-LL-AAAA, ZZ-LL-AAAA, nu vor fi stocate corect.
6. Optimizați solicitările pentru cache
majoritatea Serverele MySQL au sistemul cache activat. Este una dintre cele mai eficiente metode de îmbunătățire a performanței, care vine din mâna motorului bazei de date. Când aceeași cerere este executată de mai multe ori, rezultatul este preluat din cache, ceea ce este mult mai rapid.
Următorul exemplu este în PHP:
// Cache-ul NU funcționează $ r = mysql_query ("SELECT nume DE LA UTILIZATORI ÎNREGISTRARE> = CURDATE ()");
// Cache funcționează astăzi $ = data („Y-m-d”); $ r = mysql_query ("SELECTAți numele DE LA UTILIZATORI ÎNREGISTRAȚI> = '$ astăzi'"); Motivul pentru care nu funcționează în primul caz este din cauza utilizării CURDATE (). Poate fi aplicat tuturor funcțiilor nedeterministe, cum ar fi ACUM () și RAND (). Deoarece rezultatul returnat de funcție se poate modifica, MySQL decide să dezactiveze cache-ul pentru acea interogare.
7. Evitați să utilizați „SELECT *” în interogările dvs.
Ca regulă generală, cu cât sunt citite mai multe date din tabele, cu atât este mai lentă o interogare. Având în vedere că unele tabele de producție pot conține zeci de coloane, dintre care unele sunt formate din tipuri mari de date, nu ar fi înțelept să le selectăm pe toate.
Este un obicei bun specificați coloanele necesare în instrucțiunea dvs. SELECT.
8. Utilizați LIMIT 1 când doriți doar un singur rând
Uneori, când vă întrebați mesele și știți că aveți nevoie doar de un singur rând. În aceste cazuri, trebuie să solicitați un singur rezultat din baza de date, altfel va verifica fiecare dintre meciurile din clauza WHERE.
În aceste cazuri, adăugarea LIMIT 1 la interogarea dvs. poate îmbunătăți semnificativ viteza. Astfel, baza de date va opri scanarea rezultatelor în momentul în care o va găsi, în loc să parcurgă întregul tabel sau un index.
// Am utilizatori din Madrid? // ce NU trebuie făcut: $ r = mysql_query ("SELECT * FROM user WHERE city =‘ Madrid ’"); if (mysql_num_rows ($ r)> 0) {// …} // mult mai bine: $ r = mysql_query ("SELECT 1 FROM user WHERE city =‘ Madrid ’LIMIT 1"); if (mysql_num_rows ($ r)> 0) {// …}[culoare = # a9a9a9]Recomandare LIMIT[/Culoare]
9. Utilizarea ***** BY
Utilizarea ***** DE poate încetini timpul de răspuns în medii multi-utilizator. Așadar, vă recomandăm ca clauza ***** BY să fie utilizată numai atunci când este necesar.
Nu abuzați de utilizarea acestuia.
10. Alegeți un motor de bază adecvat
Dacă dezvoltați o aplicație care citește datele mai des decât scrierea.
(de exemplu: motor de căutare), selectați motorul de stocare MyISAM.
Dacă dezvoltați o aplicație care scrie date mai frecvent decât citirea
(de exemplu: activități bancare în timp real), alegeți motorul de stocare InnoDB.
Alegerea unui motor de stocare greșit va afecta performanța interogărilor dvs.
11. Utilizați clauza EXISTS ori de câte ori este necesar
Dacă doriți să verificați datele, nu utilizați:
Dacă (SELECTează numărul (*) din tabelul WHERE col = „o valoare”)> 0În schimb, utilizați clauza EXISTS:
Dacă EXISTĂ (SELECT * din tabelul WHERE col = „o valoare”)Ceea ce este mai rapid în timp de răspuns.
12. Utilizați EXPLAIN în interogările dvs. SELECT
Utilizarea cuvântului cheie EXPLAIN vă va oferi o mulțime de detalii interne despre ceea ce face MySQL pentru a vă executa interogarea. Acest lucru vă poate ajuta să identificați blocaje și alte probleme cu interogarea sau structura tabelului.
Rezultatul unei interogări EXPLAIN vă va arăta indexurile care sunt utilizate, modul în care tabelul este explorat, modul în care este ordonat etc.
Selectați o interogare SELECT (de preferință una complexă, cu îmbinări) și adăugați cuvântul EXPLICAȚI la începutul tuturor. Acesta va returna rezultatele într-un tabel simplu. De exemplu, să presupunem că am uitat să indexez o coloană, ne-ar arăta următorul ecran:
MARI
După adăugarea indexului în tabelul de stare, ar arăta astfel:
MARI
13. Indexați și utilizați același tip de coloană pentru Alăturări
Dacă aplicația dvs. conține multe instrucțiuni JOIN, trebuie să vă asigurați că coloanele la care vă alăturați sunt indexate în ambele tabele. Acest lucru afectează modul în care MySQL optimizează intern JOIN operațiuni.
De asemenea, coloanele la care vă alăturați trebuie să fie de același tip. De exemplu, dacă vă alăturați unei coloane de tip DECIMAL cu o coloană de tip INT dintr-un alt tabel, MySQL nu va putea utiliza cel puțin unul dintre cei doi indici. Chiar și codificarea caracterelor trebuie să fie de același tip pentru coloanele din Tipul șirului.
// caut companii in orasul meu $ r = mysql_query ("SELECT company_name FROM users LEFT JOIN companyias ON (users.city = companyias.city) WHERE users.id = $ user_id");Ambele coloane de oraș trebuie să fie indexate și ambele să fie de același tip și codare a caracterelor sau MySQL va trebui să facă o scanare completă a tabelelor.
14. Folosiți NOT NULL dacă puteți
Dacă nu aveți un motiv specific pentru a utiliza valoarea NULL, ar trebui să setați întotdeauna coloanele la NOT NULL.
În primul rând, întrebați-vă dacă ar exista o diferență între a avea un șir gol și o valoare NULL (sau pentru câmpurile INT: 0 versus NULL). Dacă nu există nicio problemă între cele două valori, nu aveți nevoie de un câmp NULL. Coloanele NULL necesită spațiu suplimentar și pot adăuga complexitate declarațiilor dvs. de comparație. Evitați-le ori de câte ori puteți.
În orice caz, înțelegem că în unele cazuri foarte specifice există un motiv pentru a utiliza coloane NULL, ceea ce nu este întotdeauna un lucru rău.
15. Tabelele cu dimensiuni fixe (statice) sunt mai rapide
Când fiecare coloană dintr-un tabel are lungime fixă, întreaga tabelă este considerată „statică” sau „cu lungime fixă”.
Câteva exemple de tipuri de coloane care NU au dimensiuni fixe sunt:
- VARCHAR
- TEXT
- BLOB
Dacă includeți doar unul dintre aceste tipuri de coloane, tabelul nu va mai avea o dimensiune fixă și va trebui tratat diferit de către Motor MySQL.
Tabelele cu dimensiuni fixe pot crește productivitatea, deoarece motorul MySQL este mai rapid să caute prin înregistrările dvs. Când doriți să citiți un anumit rând din tabel, puteți calcula rapid poziția acestuia. Dacă dimensiunea rândului nu este fixă, de fiecare dată când trebuie să căutați, trebuie mai întâi să interogați indexul cheii primare.
De asemenea, sunt mai ușor de căutat și de reconstruit după un accident. Dar, pe de altă parte, ar putea ocupa și mai mult spațiu.
16. Partiționare verticală
Partiționarea verticală este actul de a separa structura tabelului pe verticală din motive de optimizare.
Exemplul 1:
Cu siguranță veți avea un tabel de utilizator care conține o adresă poștală, care nu este folosită foarte des. Aici puteți împărți tabelul și puteți stoca adresele într-un tabel separat. În acest fel, tabelul dvs. principal de utilizator ar avea o dimensiune mai strânsă. După cum știți, cu cât sunt mai mici mesele.
Exemplul 2:
Aveți un câmp „last_access” în tabel. Este actualizat de fiecare dată când un utilizator vă accesează pagina. Dar fiecare acces face ca memoria cache a interogării pentru tabelul respectiv să fie eliberată. Ce puteți face este să puneți acest câmp într-un alt tabel, astfel încât modificările din tabelul dvs. de utilizator să fie reduse la minimum.
Dar trebuie, de asemenea, să vă asigurați că nu trebuie să vă alăturați constant celor două tabele după partiționare sau veți suferi o scădere a performanței, exact opusul a ceea ce căutam.
17. Stochează adresele IP ca UNSIGNED INT
Mulți programatori ar crea un Câmp VARCHAR (15) fără să-și dea seama că pot stoca adresele IP ca numere întregi. Când utilizați un INT, utilizați doar 4 octeți în memorie și are, de asemenea, o dimensiune fixă în tabel.
Dar trebuie să vă asigurați că coloana este UNSIGNED INT (întreg nesemnat) deoarece adresele IP utilizează întregul interval nesemnat pe 32 de biți.
În interogările dvs. puteți utiliza fișierul Funcția INET_ATON () pentru a converti o adresă IP într-un număr întreg și INET_NTOA () pentru a face contrariul. Există, de asemenea, funcții similare în PHP numite ip2long () Da long2ip ().
18. Creați vizualizări pentru a simplifica utilizarea obișnuită în tabele
Vizualizările simplifică atât schemele complexe, cât și implementarea securității. O modalitate prin care contribuie la partea de securitate este că vă permite să ascundeți numele câmpurilor de la dezvoltatori.
Poate fi folosit și pentru filtrarea coloanelor neindexate, lăsând doar câmpurile care se afișează mai rapid în căutare.
19. Nu utilizați ***** BY RAND ()
Acesta este unul dintre acele trucuri care sună grozav la prima vedere și în care mulți programatori începători tind să cadă. Este posibil să nu vă fi dat seama de blocajul incredibil care poate fi cauzat de utilizarea acestei tehnici în solicitările dvs.
Dacă într-adevăr aveți nevoie de tabele aleatorii pentru rezultatul dvs., există modalități mult mai bune de a face acest lucru. Este clar că vor prelua mai mult cod, dar veți preveni un posibil blocaj care crește exponențial pe măsură ce conținutul dvs. crește.
Problema este că MySQL va trebui să ruleze RAND () (care necesită putere de procesare) pentru fiecare rând înainte de a le sorta și a returna un singur rând.
// modul NU de a face acest lucru: $ r = mysql_query ("SELECTAȚI numele de utilizator DE la utilizator ***** BY RAND () LIMIT 1"); // mult mai bine: $ r = mysql_query ("SELECT count (*) FROM user"); $ d = mysql_fetch_row ($ r); $ rand = mt_rand (0, $ d [0] - 1); $ r = mysql_query ("SELECTAȚI numele de utilizator DIN LIMITE de nume de utilizator $ rand, 1");Deci, selectați un număr aleatoriu mai mic decât numărul de rezultate și îl utilizați ca compensare în clauza LIMIT.
20. Optimizați clauza WHERE
Acestea sunt unele sfaturi pentru optimizarea clauzei WHERE:
- Eliminați parantezele inutile. De exemplu:
De la catre5 ȘI b = c ȘI a = 5
- NUMARA (*) este optimizat pentru a returna un SELECT mult mai repede, atâta timp cât este pe o masă și fără a utiliza WHERE. De exemplu:
SELECTAȚI COUNT (*) DIN tabel.
- Opțiunea SQL_SMALL_RESULT, poate fi folosit cu A SE GRUPA CU sau DISTINCT pentru a indica faptul că setul de rezultate este mic. În acest caz, MySQL folosește tabele temporare foarte rapide pentru a stoca tabelul rezultat în loc să folosească sortarea.
Tutoriale MySQL
