Share
Operațiile de baze de date adesea tind să fie principalul obstacol pentru majoritatea aplicațiilor web de astăzi. Nu numai administratorii bazei de date (DBA) trebuie să vă faceți griji cu privire la aceste probleme de performanță. Noi, ca programatori, trebuie să ne facem parte prin structurarea corectă a tabelelor, scriind interogări optimizate și coduri mai bune. În acest articol, voi lista câteva tehnici de optimizare MySQL pentru programatori.
Înainte de a începe, rețineți că puteți găsi o mulțime de scripturi și utilitare MySQL utile pe Envato Market.
MySQL script-uri și utilități pe Envato Market Majoritatea serverelor MySQL au activat interogarea cache. Este una dintre cele mai eficiente metode de îmbunătățire a performanței, care este tratată în liniște de motorul bazei de date. Când aceeași interogare este executată de mai multe ori, rezultatul este extras din memoria cache, ceea ce este destul de rapid.
Principala problemă este că este atât de ușor și ascuns de programator, majoritatea dintre noi tind să o ignore. Unele lucruri pe care le facem pot împiedica cache-ul interogării să-și îndeplinească sarcina.
Accentsconagua
// cache-ul interogării NU funcționează $ r = mysql_query ("SELECT username FROM FROM WHERE signup_date> = CURDATE ()"); // interogare cache interogare! $ today = data ("Y-m-d"); $ r = mysql_query ("SELECT numele de utilizator FROM user WHERE signup_date> = '$ today' '); Motivul pentru care cache-ul de interogare nu funcționează în prima linie este utilizarea funcției CURDATE (). Acest lucru se aplică tuturor funcțiilor non-deterministe precum NOW () și RAND () etc ... Deoarece rezultatul returnării funcției se poate schimba, MySQL decide să dezactiveze cache-ul de interogare pentru acea interogare. Tot ce trebuie să facem este să adăugăm o linie suplimentară de PHP înainte de interogare pentru a împiedica acest lucru să se întâmple.
Utilizarea cuvântului cheie EXPLAIN vă poate oferi informații despre ceea ce face MySQL pentru a vă executa interogarea. Acest lucru vă poate ajuta să identificați blocajele și alte probleme legate de interogarea sau structurile dvs. de tabel.
Rezultatele unei interogări EXPLAIN vă vor arăta ce indexuri sunt utilizate, cum sunt scanate și sortate tabelele etc.
Faceți o interogare SELECT (preferabil una complexă, cu conexiuni) și adăugați cuvântul cheie EXPLAIN în fața acesteia. Puteți folosi doar phpmyadmin pentru acest lucru. Acesta vă va arăta rezultatele într-o masă frumoasă. De exemplu, să presupunem că am uitat să adaug un index la o coloană, pe care am efectuat-o la data de:
După adăugarea indexului în câmpul group_id:
Acum, în loc de scanare 7883 rânduri, va scana doar 9 și 16 rânduri din cele 2 tabele. O regulă bună este de a multiplica toate numerele în coloana "rânduri", iar performanța dvs. de interogare va fi oarecum proporțională cu numărul rezultat.
Uneori când interogați mesele dvs., știți deja că căutați doar un singur rând. S-ar putea să obțineți o înregistrare unică sau s-ar putea doar să verificați existența unui număr de înregistrări care să corespundă clauzei dvs. WHERE.
În astfel de cazuri, adăugarea limitei 1 la interogarea dvs. poate crește performanța. În acest fel, motorul bazei de date va opri scanarea înregistrărilor după ce va găsi doar 1, în loc să treacă prin întregul tabel sau index.
// am un utilizator din Alabama? // ce nu trebuie făcut: $ r = mysql_query ("SELECT * FROM user WHERE state = 'Alabama'"); dacă (mysql_num_rows ($ r)> 0) // ... // mult mai bine: $ r = mysql_query ("SELECT 1 FROM user WHERE state = 'Alabama' LIMIT 1"); dacă (mysql_num_rows ($ r)> 0) // ... Indicii nu sunt doar pentru cheile primare sau tastele unice. Dacă în tabel există coloane pe care le veți căuta, ar trebui să le indexați aproape întotdeauna.
După cum puteți vedea, această regulă se aplică și în cazul unei căutări parțiale a șirului ca "last_name LIKE" a% '". Când căutați de la începutul șirului, MySQL este capabil să utilizeze indexul din acea coloană.
Ar trebui să înțelegeți, de asemenea, tipurile de căutări care nu pot utiliza indicii obișnuiți. De exemplu, atunci când căutați un cuvânt (de ex. "WHERE post_content LIKE '% apple%'"), nu veți vedea un beneficiu dintr-un index normal. Veți fi mai bine să utilizați căutare mysql fulltext sau să vă construiți propria soluție de indexare.
Dacă cererea conține mai multe interogări JOIN, trebuie să vă asigurați că coloanele la care vă alăturați sunt indexate pe ambele tabele. Acest lucru afectează modul în care MySQL optimizează intern operația de conectare.
De asemenea, coloanele care sunt îmbinate trebuie să fie de același tip. De exemplu, dacă vă înscrieți într-o coloană DECIMAL, într-o coloană INT dintr-un alt tabel, MySQL nu va putea utiliza cel puțin unul dintre indexuri. Chiar și codificările de caractere trebuie să fie de același tip pentru coloanele de tip șir.
// căutați companii din statul meu $ r = mysql_query ("SELECT company_name FROM users users LEFT JOIN companies ON (users.state = companies.state) WHERE users.id = $ user_id"); // ambele coloane de stare ar trebui să fie indexate // și ambele ar trebui să fie de același tip și codare de caractere // sau MySQL ar putea efectua scanări complete de tabele Acesta este unul dintre acele trucuri care sună rece la început, și mulți programatori începători cad pentru această capcană. S-ar putea să nu realizați ce ghinion îngrozitor puteți crea odată ce începeți să utilizați acest lucru în interogările dvs..
Dacă într-adevăr aveți nevoie de rânduri aleatorii din rezultatele dvs., există modalități mult mai bune de a face acest lucru. Acordat este nevoie de cod suplimentar, dar veți preveni o strangulare care devine exponențial mai rău pe măsură ce datele dvs. cresc. Problema este că MySQL va trebui să efectueze o operațiune RAND () (care are putere de procesare) pentru fiecare rând din tabel înainte de ao sorta și vă va da doar 1 rând.
// ce nu trebuie făcut: $ r = mysql_query ("SELECT nume utilizator FROM ORDER 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 ("SELECT nume utilizator FROM LIMIT utilizator $ rand, 1"); Deci, alegeți un număr aleatoriu mai mic decât numărul de rezultate și utilizați-l ca fiind compensat în clauza LIMIT.
Cu cât sunt citite mai multe date din tabele, cu atât interogarea va deveni mai lentă. Aceasta crește timpul necesar pentru operațiile discului. De asemenea, atunci când serverul de baze de date este separat de serverul web, veți avea întârzieri mai lungi în rețea datorită faptului că datele trebuie transferate între servere.
Este un obicei bun să specificați întotdeauna coloanele de care aveți nevoie atunci când faceți SELECT-ul dvs..
// nu este preferat $ r = mysql_query ("SELECT * FROM user WHERE user_id = 1"); $ d = mysql_fetch_assoc ($ r); echo "Bun venit $ d ['username']"; // mai bine: $ r = mysql_query ("SELECT nume utilizator FROM user WHERE user_id = 1"); $ d = mysql_fetch_assoc ($ r); echo "Bun venit $ d ['username']"; // diferențele sunt mai semnificative cu seturi de rezultate mai mari În fiecare tabel există o coloană id care este KEY PRIMARY, AUTO_INCREMENT și una dintre aromele INT. De asemenea, de preferință UNSIGNED, deoarece valoarea nu poate fi negativă.
Chiar dacă aveți un tabel de utilizatori care are un câmp unic de nume de utilizator, nu faceți ca cheia primară. Câmpurile VARCHAR ca taste primare sunt mai lente. Și veți avea o structură mai bună în codul dvs., referindu-vă la toți utilizatorii cu ID-ul lor intern.
Există, de asemenea, în spatele operațiunilor efectuate de motorul MySQL însuși, care folosește câmpul cheie primar intern. Care devin și mai importante, cu atât este mai complicată configurarea bazei de date. (clustere, partiționare etc.).
O posibilă excepție de la această regulă sunt "tabelele de asociere", utilizate pentru tipurile de asociații de la multe la multe dintre două tabele. De exemplu, un tabel "posts_tags" care conține 2 coloane: post_id, tag_id, care este folosit pentru relațiile dintre două tabele numite "post" și "etichete". Aceste tabele pot avea o cheie primară care conține ambele câmpuri id.
Coloanele tip ENUM sunt foarte rapide și compacte. În interior sunt stocate ca TINYINT, dar pot conține și afișa valorile șirului. Acest lucru le face un candidat perfect pentru anumite domenii.
Dacă aveți un câmp, care va conține doar câteva tipuri diferite de valori, utilizați ENUM în loc de VARCHAR. De exemplu, ar putea fi o coloană numită "stare" și conține numai valori precum "activ", "inactiv", "în așteptare", "expirat" etc ...
Există chiar și o modalitate de a obține o "sugestie" din MySQL însuși cu privire la modul de restructurare a mesei. Când aveți un câmp VARCHAR, acesta vă poate sugera să schimbați tipul de coloană în ENUM. Acest lucru sa făcut folosind apelul PROCEDURE ANALYZE (). Care ne aduce la:
PROCEDURA ANALYZE () va permite MySQL să analizeze structurile coloanelor și datele efective din tabelul dvs. pentru a veni cu anumite sugestii pentru dvs. Este utilă numai dacă există date efective în tabelele dvs., deoarece acestea joacă un rol important în luarea deciziilor.
De exemplu, dacă ați creat un câmp INT pentru cheia dvs. primară, dar nu aveți prea multe rânduri, s-ar putea să vă sugerăm să utilizați în schimb un MEDIUMINT. Sau dacă utilizați un câmp VARCHAR, este posibil să primiți o sugestie pentru ao converti în ENUM, dacă există doar câteva valori unice.
De asemenea, puteți rula acest lucru făcând clic pe linkul "Propune structura tabelă" din phpmyadmin, într-unul din vizualizările dvs. de tabel.
Rețineți că acestea sunt doar sugestii. Și dacă masa ta va crește, ar putea să nu fie sugestiile potrivite de urmat. Decizia este în cele din urmă a ta.
Dacă nu aveți un motiv foarte specific pentru a utiliza o valoare NULL, ar trebui să setați întotdeauna coloanele dvs. ca NU NULL.
Mai întâi, întrebați-vă dacă există vreo diferență între a avea o valoare de șir gol și o valoare NULL (pentru câmpurile INT: 0 vs. NULL). Dacă nu există niciun motiv pentru a avea ambele, nu aveți nevoie de un câmp NULL. (Știați că Oracle consideră NULL și șir gol ca fiind aceleași?)
Coloanele NULL necesită spațiu suplimentar și pot adăuga complexitate declarațiilor de comparație. Doar evitați-le când puteți. Cu toate acestea, înțeleg că unii oameni ar putea avea motive foarte specifice pentru a avea valori NULL, ceea ce nu este întotdeauna un lucru rău.
Din documentele MySQL:
"Coloanele NULL necesită spațiu suplimentar în rând pentru a înregistra dacă valorile lor sunt NULL. Pentru tabelele MyISAM, fiecare coloană NULL are un bit extra, rotunjită până la cel mai apropiat byte."
Există mai multe avantaje în utilizarea declarațiilor pregătite, atât din motive de performanță, cât și din motive de securitate.
Instrucțiunile pregătite vor filtra implicit variabilele pe care le legați la acestea, ceea ce este excelent pentru protejarea aplicației împotriva atacurilor de tip SQL. Puteți, bineînțeles, să filtrați variabilele și manual, dar aceste metode sunt mai predispuse la eroarea umană și la uitare de către programator. Aceasta este mai puțin o problemă atunci când se utilizează un anumit tip de cadru sau ORM.
Deoarece accentul nostru se pune pe performanță, ar trebui să menționez, de asemenea, beneficiile din acest domeniu. Aceste beneficii sunt mai importante atunci când aceeași interogare este utilizată de mai multe ori în aplicația dvs. Puteți atribui diferite valori aceleiași instrucțiuni pregătite, dar MySQL va trebui să o parseze o singură dată.
De asemenea, ultimele versiuni ale MySQL transmit declarații pregătite într-o formă binară nativă, care sunt mai eficiente și pot, de asemenea, ajuta la reducerea întârzierilor în rețea.
A existat un timp în care mulți programatori au folosit pentru a evita declarațiile pregătite în scop, din un singur motiv important. Nu au fost cacheate de cache-ul de interogare MySQL. Dar din moment ce în jurul versiunii 5.1, se acceptă și cache-ul de interogare.
Pentru a folosi instrucțiunile pregătite în PHP, verificați extensia mysqli sau folosiți un strat de abstractizare a bazei de date, cum ar fi PDO.
// crea o declarație pregătită dacă ($ stmt = $ mysqli-> prepare ("SELECT username FROM user WHERE state =?")) // parametrii bind $ stmt-> bind_param ("s", $ state); // executați $ stmt-> execute (); // bind variabilele de rezultat $ stmt-> bind_result ($ username); // preluați valoarea $ stmt-> fetch (); printf ("% s este de la% s \ n", $ username, $ state); $ Stmt-> close (); În mod normal, atunci când efectuați o interogare dintr-un script, va aștepta ca executarea acelei interogări să se termine înainte de a putea continua. Puteți schimba acest lucru utilizând interogări fără buclă.
Există o explicație foarte bună în doc-urile PHP pentru funcția mysql_unbuffered_query ():
"mysql_unbuffered_query () trimite cererea de interogare SQL către MySQL fără a prelua automat și tamponarea rândurilor de rezultate ca mysql_query () .Acest lucru economisește o cantitate considerabilă de memorie cu interogări SQL care produc seturi de rezultate mari și puteți începe să lucrați la setul de rezultate imediat după ce primul rând a fost preluat, deoarece nu trebuie să așteptați până când nu a fost efectuată interogarea SQL completă. "
Cu toate acestea, ea vine cu anumite limitări. Trebuie fie să citiți toate rândurile, fie să apelați mysql_free_result () înainte de a putea efectua o altă interogare. De asemenea, nu vi se permite să utilizați mysql_num_rows () sau mysql_data_seek () în setul de rezultate.
Mulți programatori vor crea un câmp VARCHAR (15) fără să-și dea seama că pot stoca efectiv adrese IP ca valori întregi. Cu un INT, coborâți la doar 4 octeți de spațiu și aveți în schimb un câmp de dimensiune fixă.
Trebuie să vă asigurați că coloana dvs. este UNSIGNED INT, deoarece adresele IP utilizează întreaga gamă a unui număr întreg nesemnat de 32 de biți.
În interogările dvs. puteți utiliza INET_ATON () pentru a converti și IP la un număr întreg, iar INET_NTOA () pentru invers. Există, de asemenea, funcții similare în PHP numite ip2long () și long2ip ().
$ r = "UPDATE utilizatori SET ip = INET_ATON ('$ _ SERVER [' REMOTE_ADDR ']') WHERE user_id = $ user_id"; Când fiecare coloană dintr-un tabel este "lungimea fixă", tabelul este de asemenea considerat "static" sau "lungime fixă". Exemple de tipuri de coloane care NU sunt fixate sunt: VARCHAR, TEXT, BLOB. Dacă includeți doar 1 dintre aceste tipuri de coloane, tabelul nu mai are o durată fixă și trebuie să fie tratat diferit de motorul MySQL.
Tabelele cu lungime fixă pot îmbunătăți performanța deoarece este mai rapid ca motorul MySQL să caute înregistrările. Când dorește să citească un rând specific într-un tabel, poate calcula rapid poziția acestuia. Dacă mărimea rândului nu este fixă, de fiecare dată când are nevoie să facă o căutare, trebuie să consulte indexul cheii primare.
Ele sunt, de asemenea, mai ușor de cache și mai ușor de reconstituit după un accident. Dar, de asemenea, acestea pot lua mai mult spațiu. De exemplu, dacă convertiți un câmp VARCHAR (20) într-un câmp CHAR (20), acesta va dura întotdeauna 20 de octeți de spațiu, indiferent de ce este în.
Prin utilizarea tehnicilor de "Partiție verticală", puteți separa coloanele cu lungime variabilă într-un tabel separat. Care ne aduce la:
Partiționarea verticală este actul de împărțire a structurii tabelului într-o manieră verticală din motive de optimizare.
Exemplul 1: Este posibil să aveți un tabel de utilizatori care conține adrese de domiciliu, care nu se citesc frecvent. Puteți alege să vă împărțiți masa și să stocați informațiile despre adresă într-o tabelă separată. În acest fel, tabelul principal al utilizatorilor se va micșora. După cum știți, mesele mai mici funcționează mai repede.
Exemplul 2: În tabelul dvs. aveți un câmp "last_login". Se actualizează de fiecare dată când un utilizator se conectează la site. Dar fiecare actualizare dintr-o tabelă face ca memoria cache-ului pentru interogare să fie spălată. Puteți pune acel câmp într-un alt tabel pentru a păstra la minimum o actualizare a tabelului utilizatorilor.
Dar, de asemenea, trebuie să vă asigurați că nu aveți nevoie în mod constant să vă alăturați acestor 2 mese după partiționarea sau că s-ar putea să suferiți de fapt declinul performanței.
Dacă trebuie să efectuați o interogare mare DELETE sau INSERT pe un site web live, trebuie să aveți grijă să nu deranjați traficul web. Atunci când o astfel de interogare este efectuată, aceasta poate bloca tabelele și poate să vă oprească aplicația web.
Apache rulează multe procese / fire în paralel. Prin urmare, funcționează cel mai eficient când scripturile se termină executând cât mai curând posibil, astfel încât serverele să nu aibă prea multe conexiuni deschise și procese simultan care consumă resurse, în special memoria.
Dacă ați terminat blocarea meselor pentru o perioadă mai lungă de timp (de exemplu, 30 de secunde sau mai mult), pe un site Web de mare trafic, veți provoca un proces și o interogare de pileu, care ar putea dura mult timp pentru a șterge sau chiar a vă prăbuși Server.
Dacă aveți un anumit script de întreținere care trebuie să șterge un număr mare de rânduri, trebuie doar să utilizați clauza LIMIT pentru a o face în loturi mai mici pentru a evita această congestie.
în timp ce (1) mysql_query ("DELETE FROM logs WHERE log_date <= '2009-10-01' LIMIT 10000"); if (mysql_affected_rows() == 0) // done deleting break; // you can even pause a bit usleep(50000); Cu motoarele de baze de date, discul este probabil cel mai important obstacol. Menținerea lucrurilor mai mici și mai compacte este de obicei utilă în termeni de performanță, pentru a reduce cantitatea de transfer pe disc.
Documentele MySQL au o listă de cerințe de stocare pentru toate tipurile de date.
Dacă se așteaptă ca o tabelă să aibă foarte puține rânduri, nu există niciun motiv pentru a face cheia primară un INT, în loc de MEDIUMINT, SMALLINT sau chiar în unele cazuri TINYINT. Dacă nu aveți nevoie de componenta de timp, utilizați DATE în loc de DATETIME.
Doar asigurați-vă că vă lăsați spațiu rezonabil să crească sau s-ar putea să ajungeți ca Slashdot.
Cele două motoare principale de stocare din MySQL sunt MyISAM și InnoDB. Fiecare are propriile argumente pro și contra.
MyISAM este bun pentru aplicațiile greu citite, dar nu scade foarte bine atunci când există multe scrieri. Chiar dacă actualizați un câmp dintr-un rând, întreaga tabelă este blocată și nici un alt proces nu poate citi chiar de la ea până când interogarea nu este terminată. MyISAM este foarte rapid la calcularea SELECT COUNT (*) tipuri de interogări.
InnoDB tinde să fie un motor de stocare mai complicat și poate fi mai lent decât MyISAM pentru majoritatea aplicațiilor mici. Dar aceasta susține blocarea bazată pe rând, care scade mai bine. De asemenea, acceptă câteva caracteristici mai avansate, cum ar fi tranzacțiile.
Folosind un ORM (Object Relational Mapper), puteți obține anumite beneficii de performanță. Tot ce poate face un ORM, poate fi codat manual. Dar aceasta poate însemna prea multă muncă suplimentară și necesită un nivel ridicat de expertiză.
ORM-urile sunt excelente pentru "Lazy Loading". Aceasta înseamnă că ele pot aduce valori numai în măsura în care sunt necesare. Dar trebuie să fii atent cu ei sau poți să ajungi la multe mini-interogări care pot reduce performanța.
ORM-urile pot, de asemenea, să vă afișeze interogările în tranzacții, care funcționează mult mai repede decât trimiterea de interogări individuale către baza de date.
În prezent, ORM-ul meu preferat pentru PHP este doctrină. Am scris un articol despre modul de instalare a Doctrinei cu CodeIgniter.
Conexiunile persistente sunt menite să reducă cheltuielile de recuperare a conexiunilor la MySQL. Atunci când se creează o conexiune permanentă, aceasta va rămâne deschisă chiar și după terminarea executării scriptului. Deoarece Apache reutilizează procesele copilului, data viitoare când procesul se execută pentru un script nou, acesta va reutiliza aceeași conexiune MySQL.
Sună grozav în teorie. Dar din experiența mea personală (și multe altele), aceste trăsături se dovedesc a nu merita probleme. Puteți avea probleme serioase cu limite de conectare, probleme de memorie și așa mai departe.
Apache rulează extrem de paralel și creează multe procese copil. Acesta este motivul principal în care conexiunile persistente nu funcționează foarte bine în acest mediu. Înainte de a lua în considerare utilizarea funcției mysql_pconnect (), consultați administratorul de sistem.
