Top 20 de recomandări pentru MySQL

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

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

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)

4. CHAR pentru date fixe

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

• 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

 // 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'"); 

7. Evitați să utilizați „SELECT *” în interogările dvs.

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

Î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) {// …}

9. Utilizarea ***** BY

Nu abuzați de utilizarea acestuia.

10. Alegeți un motor de bază adecvat

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ă (SELECTează numărul (*) din tabelul WHERE col = „o valoare”)> 0

 Dacă EXISTĂ (SELECT * din tabelul WHERE col = „o valoare”)

12. Utilizați EXPLAIN în interogările dvs. SELECT

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

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");

14. Folosiți NOT NULL dacă puteți

Î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â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ă

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

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

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 ()

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");

20. Optimizați clauza WHERE

• Eliminați parantezele inutile. De exemplu:

 De la catre5 ȘI b = c ȘI a = 5

 SELECTAȚI COUNT (*) DIN tabel.