Cum să optimizați interogările mysql. Optimizarea interogărilor MySQL. Instrumente de ajutor: Setul de instrumente Percona pentru indecșii neutilizați
Cum să optimizați interogările MySQL?
Pentru un site obișnuit, care nu este deosebit de vizitat, nu există o mare diferență dacă interogările MySQL la baza de date sunt optimizate sau nu. Dar pentru serverele de producție cu încărcare mare, diferența dintre SQL corect și incorect este uriașă, iar în timpul execuției acestea pot afecta semnificativ comportamentul și fiabilitatea serviciilor. În acest articol voi vedea cum să scriu interogări rapideși factorii care le fac să încetinească.
De ce MySQL?
Astăzi se vorbește mult despre Dig Data și despre alte tehnologii noi. NoSQL și soluții cloud acest lucru este grozav, dar o mulțime de software popular (cum ar fi WordPress, phpBB, Drupal) încă rulează pe MySQL. Migrația către ultimele solutii poate duce nu numai la modificarea configurației pe servere. În plus, eficiența MySQL este încă la nivel, în special versiunea Percona.
Nu faceți greșeala comună de a arunca tot mai mult hardware la rezolvarea problemei interogărilor lente și sarcină mare servere - este mai bine să mergeți la rădăcinile problemelor. Putere crescută a procesorului și hard disk-uri si adaugand RAM aceasta este de asemenea anumit tip optimizarea, însă, nu este ceea ce vom vorbi în acest articol. De asemenea, prin optimizarea site-ului și rezolvarea problemei cu hardware-ul, încărcarea va crește doar exponențial. Prin urmare, aceasta este doar o soluție pe termen scurt.
O bună înțelegere a SQL este cel mai important instrument pentru un dezvoltator web, vă va permite să optimizați și să utilizați eficient baze de date relaționale date. În acest articol ne vom concentra pe o bază de date populară cu sursă deschisă folosită adesea împreună cu PHP, MySQL.
Pentru cine este acest articol?
Pentru dezvoltatori web, arhitecți și dezvoltatori de baze de date și administratorii de sistem, familiarizat cu MySQL. Dacă nu ați folosit MySQL înainte, acest articol poate să nu vă fie de mare folos, dar voi încerca totuși să fiu cât mai informativ și util, chiar și pentru cei care cunosc MySQL.
Mai întâi faceți backup
Recomand sa faci pașii următori bazat pe MySQL cu care lucrați, dar nu uitați să faceți copie de rezervă. Dacă nu aveți o bază de date cu care să lucrați, vă voi oferi exemple pentru crearea propriei baze de date, acolo unde este cazul.
Efectuarea de copii de rezervă MySQL este ușoară folosind utilitarul mysqldump:
$ mysqldump myTab > myTab-backup.sql Puteți afla mai multe despre mysqldump.
Ce face o interogare lentă?
Iată o listă generală a factorilor care afectează viteza solicitărilor și încărcarea serverului:
- indici de tabel;
- UNDE stare (și utilizare funcții interne MySQL, de exemplu, cum ar fi IF sau DATE);
- sortați după ORDER BY;
- repetarea frecventă a cererilor identice;
- tipul mecanismului de stocare a datelor (InnoDB, MyISAM, Memory, Blackhole);
- nefolosind versiunea Percona;
- configurații server (my.cnf / my.ini);
- ieșiri mari de date (mai mult de 1000 de rânduri);
- conexiune instabilă;
- configurație distribuită sau cluster;
- Design slab al mesei.
Ce sunt indicii?
Indecșii sunt utilizați în MySQL pentru a căuta rânduri cu valorile de coloană specificate, cum ar fi clauza WHERE. Fără indexuri, MySQL trebuie, începând de la primul rând, să citească întregul tabel căutat valori relevante. Cum mai multă masă, cu atât costă mai multe.
Dacă tabelul are indecși pe coloanele care vor fi utilizate în interogare, MySQL va găsi rapid locațiile informatiile necesare fără a vizualiza întregul tabel. Acest lucru este mult mai rapid decât căutarea fiecărei linii secvenţial.
Conexiune instabilă?
Când aplicația dvs. se conectează la o bază de date și este configurată o conexiune stabilă, aceasta va fi utilizată de fiecare dată fără a fi nevoie să deschideți o nouă conexiune de fiecare dată. Acest solutie optima pentru mediul de lucru.
Reducerea repetarii frecvente a cererilor identice
Cel mai rapid și mod eficient Soluția pe care am găsit-o pentru aceasta este crearea unui depozit de interogări și rezultatele execuției acestora folosind Memcached sau Redis. Cu Memcache puteți stoca cu ușurință în cache rezultatul interogării dvs., de exemplu, astfel:
connect("localhost",11211); $cacheResult = $cache->get("nume-cheie"); if($cacheResult)( //nu este nevoie de o interogare $result = $cacheResult; ) else ( //rulați interogarea $mysqli = mysqli("p:localhost","nume utilizator","parolă","tabel" ); //adăugați p: pentru stocare pe termen lung $sql = "SELECT * FROM posts LEFT JOIN userInfo folosind (UID) WHERE posts.post_type = "post" || posts.post_type = "articol" ORDER BY coloana LIMIT 50" ; $mysqli->query($memc->set("key-name", $result->fetch_array(), MEMCACHE_COMPRESSED,86400) //Parola $cacheResult pentru șablonul->assign("); posturi", $cacheResult); ?> Acum cerere grea, folosind LEFT JOIN, va fi executat doar o dată la 86.400 de secunde (adică o dată pe zi), ceea ce va reduce semnificativ încărcarea pe serverul MySQL, lăsând resurse pentru alte conexiuni.
Notă: Adăugați p: la începutul argumentului gazdă MySQLi pentru a crea o conexiune persistentă.
Configurație distribuită sau în cluster
Când datele devin din ce în ce mai multe, iar viteza serviciului tău scade, panica te poate pune stăpânire. Soluție rapidă poate deveni distribuție de resurse (sharding). Cu toate acestea, nu recomand să faceți acest lucru decât dacă aveți experienta buna, deoarece distribuția în mod inerent face structurile de date complexe.
Design slab al mesei
Crearea de scheme de baze de date nu este munca grea, dacă urmați regulile de aur de a lucra cu limitări și de a ști ce va funcționa. De exemplu, stocarea imaginilor în celulele BLOB este foarte confuză - stocarea căii fișierului într-o celulă VARCHAR este o soluție mult mai bună.
Asigurarea designului adecvat pentru utilizare dorită este esențial în crearea aplicației dvs. Stocați date diferite în tabele diferite (de exemplu, categorii și articole) și asigurați-vă că relațiile mai multe la unu și unu la mai multe pot fi asociate cu ușurință cu ID-uri. Utilizarea FOREIGN KEY în MySQL este ideală pentru stocarea datelor în cascadă în tabele.
Când creați un tabel, rețineți următoarele:
- Creați tabele eficiente pentru a vă rezolva problemele, în loc să umpleți tabelele cu date și relații inutile.
- Nu vă așteptați ca MySQL să vă execute logica de afaceri sau programarea - datele trebuie să fie gata pentru a introduce rândul dvs limbaj de script. De exemplu, dacă trebuie să sortați o listă în ordine aleatorie, faceți-o în matrice PHP fără a utiliza ORDER BY din arsenalul MySQL.
- Utilizați tipuri de index UNIQUE pentru seturi de date unice și utilizați ON DUPLICATE KEY UPDATE pentru a menține o dată la zi, de exemplu pentru a afla când a fost modificat ultima dată un rând.
- Utilizați tipul de date INT pentru a stoca numere întregi. Dacă nu specificați o dimensiune a tipului de date, MySQL o va face pentru dvs.
Pentru a optimiza eficient, trebuie să aplicăm trei abordări aplicației dvs.:
- Analiză (înregistrarea interogărilor lente, studierea sistemului, analiza interogărilor și proiectarea bazei de date)
- Cerințe de execuție (câți utilizatori)
- Limitări ale tehnologiei (viteza hardware, utilizarea incorectă a MySQL)
Coloane, vedeți, salvați informatii importante despre cerere. Coloanele cărora ar trebui să le acordați cea mai mare atenție sunt posibil_keys și Extra.
Coloana posibil_keys va afișa indecșii la care MySQL a avut acces pentru a executa interogarea. Uneori trebuie să atribuiți indecși pentru ca interogarea să ruleze mai rapid. Coloana Extra va indica dacă a fost folosit un WHERE sau ORDER BY suplimentar. Cel mai important lucru de observat este dacă Utilizarea Filesort este în ieșire.
Ce face Utilizarea Filesort este menționat în ajutorul MySQL:
MySQL trebuie să facă o trecere suplimentară pentru a afla cum să returneze rândurile în formă sortată. Acest sortare are loc prin iterarea peste toate rândurile în funcție de tipul de îmbinare și stocarea cheii de sortare și a indicatorului de rând pentru toate rândurile care se potrivesc cu clauza WHERE. Cheile sunt sortate și rândurile sunt returnate în ordinea corectă.O trecere suplimentară vă va încetini aplicarea și ar trebui evitată cu orice preț. Un alt rezultat critic al Extra pe care ar trebui să-l evităm este Utilizarea temporară. Se spune că MySQL a trebuit să creeze un tabel temporar pentru a executa interogarea. Evident că este groaznic folosind MySQL. În acest caz, rezultatul interogării ar trebui să fie stocat în Redis sau Memcache și să nu fie executat din nou de utilizatori.
Pentru a evita problema cu Utilizarea Filesort trebuie să ne asigurăm că MySQL folosește INDEX. În prezent, există mai multe chei specificate în posibil_keys din care să alegeți, dar MySQL poate selecta doar un index pentru interogarea finală. De asemenea, indecșii pot fi alcătuiți din mai multe coloane și puteți introduce și indicii pentru optimizatorul MySQL, arătând spre indecșii pe care i-ați creat.
Aluzie index
Optimizatorul MySQL va folosi statistici bazate pe interogări de tabel pentru a selecta cel mai bun index pe care să ruleze interogarea. Funcționează destul de simplu, pe baza logicii statistice încorporate, deci având mai multe opțiuni, nu funcționează întotdeauna alegere corectă fără ajutorul aluziei. Pentru a vă asigura că a fost folosită cheia corectă (sau incorectă), utilizați cuvintele cheie FORCE INDEX, USE INDEX și IGNORE INDEX în interogarea dvs. Puteți citi mai multe despre indicarea indexului în ajutorul MySQL.
Pentru a afișa cheile de tabel, utilizați comanda SHOW INDEX. Puteți specifica mai multe indicii pentru utilizare de către optimizator.
Pe lângă EXPLAIN există cuvânt cheie DESCRIE. Cu DESCRIBE, puteți vizualiza informațiile din tabel după cum urmează:
Adăugarea unui index
Pentru a adăuga indecși în MySQL, trebuie să utilizați sintaxa CREATE INDEX. Există mai multe tipuri de indici. FULLTEXT este folosit pentru căutarea cu text integral, iar UNIQUE este folosit pentru a stoca date unice.
Pentru a adăuga un index la tabelul dvs., utilizați următoarea sintaxă:
Mysql> CREATE INDEX idx_bookname ON `carti` (bookname(10)); Aceasta va crea un index pe tabelul de cărți care va folosi primele 10 litere ale coloanei care stochează titlurile cărților și este de tip varchar. În acest caz, orice căutare cu o interogare WHERE pe un titlu de carte cu o potrivire de până la 10 caractere va produce același rezultat ca scanarea întregului tabel de la început până la sfârșit.
Indici compoziți
Indicii au mare influență asupra vitezei de executare a interogării. Doar atribuirea unei chei unice principale nu este suficientă - cheile compozite sunt un adevărat domeniu de aplicare în Configurare MySQL, care uneori necesită unele teste A/B folosind EXPLAIN.
De exemplu, dacă trebuie să facem referire la două coloane în condiția unei clauze WHERE, o cheie compusă ar fi o soluție ideală.
Mysql> CREATE INDEX idx_composite ON utilizatori (nume utilizator, activ); Odată ce am creat o cheie bazată pe coloana nume de utilizator, care stochează numele utilizatorului și coloanele active de tip ENUM, care determină dacă contul său este activ. Acum totul este optimizat pentru o interogare care va folosi WHERE pentru a găsi un nume de utilizator valid cu un cont activ (activ = 1).
Cât de rapid este MySQL-ul tău?
Să activăm profilarea pentru a arunca o privire mai atentă asupra interogărilor MySQL. Acest lucru se poate face prin rularea comenzii set profiling=1, după care trebuie să rulați show profiles pentru a vedea rezultatul.
Dacă utilizați PDO, rulați următorul cod:
$db->query("set profile=1"); $db->query("selectați titlul, corpul, etichetele din postări"); $rs = $db->query("arată profiluri"); $db->query("set profiling=0"); // dezactivează profilarea după executarea interogării $records = $rs->fetchAll(PDO::FETCH_ASSOC); // obțineți rezultatele profilării $errmsg = $rs->errorInfo(); //Prinți câteva erori aici Același lucru se poate face folosind mysqli:
$db = new mysqli($gazdă,$nume utilizator,$parolă,$nume db);
$db->query("set profile=1"); $db->query("selectați titlul, corpul, etichetele din postări"); if ($rezultat = $db->interogare(„Afișează profiluri”, MYSQLI_USE_RESULT)) ( în timp ce ($rând = $rezultat->fetch_row()) ( var_dump($rând); ) $rezultat->close(); ) if ($rezultat = $db->query("arată profilul pentru interogarea 1", MYSQLI_USE_RESULT)) ( în timp ce ($rând = $rezultat->fetch_row()) ( var_dump($rând); ) $rezultat->close( ); ) $db->query("set profile=0"); Aceasta vă va returna datele profilate care conțin timpul de execuție a interogării în al doilea element al matricei asociative.
Array(3) ( => string(1) "1" => string(10) "0.00024300" => string(17) "select headline, body, tags from posts" ) Această interogare a durat 0,00024300 de secunde pentru a fi finalizată. Este destul de rapid, așa că să nu ne facem griji. Dar când cifrele devin mari, trebuie să ne uităm mai profund. Accesați aplicația dvs. pentru a practica cu un exemplu de lucru. Verificați constanta DEBUG din configurația bazei de date și apoi începeți să explorați sistemul activând ieșirea profilării utilizând funcțiile var_dump sau print_r. În acest fel, puteți trece de la o pagină la alta din aplicația dvs., obținând profiluri convenabile ale sistemului.
Pentru a efectua un audit complet al solicitărilor dvs., activați înregistrarea. Unii dezvoltatori de site-uri web se tem că înregistrarea în jurnal are un impact semnificativ asupra execuției și încetinește și mai mult solicitările. Cu toate acestea, practica arată că diferența este nesemnificativă.
Pentru a activa înregistrarea în MySQL 5.1.6, utilizați variabila globală log_slow_queries, puteți, de asemenea, să marcați un fișier pentru înregistrare folosind variabila slow_query_log_file. Acest lucru se poate face executând următoarea interogare:
Setați global log_slow_queries = 1; setați global slow_query_log_file = /dev/slow_query.log; Puteți specifica acest lucru și în fișierele de configurare /etc/my.cnf sau my.ini ale serverului dumneavoastră.
După efectuarea modificărilor, nu uitați să reporniți serverul MySQL cu comanda necesară, de exemplu service mysql restart dacă utilizați Linux.
În versiunile MySQL după 5.6.1, variabila log_slow_queries este depreciată și se folosește în schimb slow_query_log. De asemenea, pentru o depanare mai convenabilă, puteți activa ieșirea tabelului setând variabila log_output la TABLE, cu toate acestea, această funcție este disponibilă numai din MySQL 5.6.1.
Log_output = TABLE; log_queries_not_using_indexes = 1; timp_interogare_lung = 1; Variabila long_query_time specifică numărul de secunde după care interogarea este considerată lentă. Valoarea este 10, iar minimul este 0. De asemenea, puteți specifica milisecunde folosind o fracție; acum am indicat o secundă. Și acum fiecare cerere care va fi executată mai mult de 1 secundă este înregistrată în jurnalele din tabel.
Înregistrarea se va face în tabelele mysql.slow_log și mysql.general_log ale dvs. baze de date MySQL date. Pentru a dezactiva înregistrarea, schimbați log_output la NONE.
Conectarea pe un server de producție
Pe un server de producție care deservește clienții, este mai bine să utilizați înregistrarea doar pentru o perioadă scurtă și să monitorizați încărcarea, pentru a nu crea încărcătură suplimentară. Dacă serviciul dvs. este supraîncărcat și este nevoie de atenție imediată, încercați să izolați problema rulând SHOW PROCESSLIST sau accesând tabelul information_schema.PROCESSLIST rulând SELECT * FROM information_schema.PROCESSLIST;.
Înregistrarea tuturor solicitărilor pe serverul de producție vă poate oferi o mulțime de informații și deveni bun remediuîn scopuri de cercetare atunci când verificați un proiect, dar jurnalele pentru perioade lungi nu vă vor spune prea multe informatii utile comparativ cu jurnalele pe o perioadă de până la 48 de ore (încercați să urmăriți sarcini de vârf pentru a avea șansa de a investiga mai bine execuția interogărilor).
Notă: dacă aveți un site care se confruntă cu valuri de trafic și, uneori, aproape fără trafic, cum ar fi un site de sport în afara sezonului, atunci utilizați aceste informații pentru a construi și a studia înregistrarea în jurnal.
Înregistrarea cererilor multiple
Nu numai că este important să fii conștient de interogările care durează mai mult de o secundă pentru a se executa, dar trebuie să fii conștient și de interogările care sunt executate de sute de ori. Chiar dacă interogările sunt executate rapid, într-un sistem ocupat ele pot consuma toate resursele.
Acesta este motivul pentru care trebuie să fiți mereu în gardă după ce faceți modificări într-un proiect live - acesta este momentul cel mai critic pentru funcționarea oricărei baze de date.
Cache cald și rece
Numărul de solicitări și încărcarea serverului au un impact puternic asupra execuției și pot afecta, de asemenea, timpul de execuție al solicitărilor. Când dezvoltați, ar trebui să faceți o regulă ca fiecare solicitare să nu dureze mai mult de o fracțiune de milisecundă (0.0xx sau mai rapid) pentru a se finaliza pe un server gratuit.
Utilizarea Memcache are un efect puternic asupra încărcării serverelor și va elibera resurse care execută cereri. Asigurați-vă că utilizați Memcached în mod eficient și că ați testat aplicația cu un cache cald (date încărcate) și un cache rece.
Pentru a evita rularea pe un server de producție cu un cache gol, este o idee bună să aveți un script care să colecteze toată memoria cache necesară înainte de a porni serverul, astfel încât un aflux mare de clienți să nu reducă timpul de pornire a sistemului.
Remedierea interogărilor lente
Acum că înregistrarea în jurnal este configurată, este posibil să găsiți câteva interogări lente pe site-ul dvs. Să le reparăm! Ca exemplu, voi arăta mai multe probleme comune și puteți vedea logica pentru remedierea lor.
Dacă nu ați găsit încă o interogare lentă, verificați setările long_query_time dacă utilizați această metodă de înregistrare. În caz contrar, după ce ați verificat toate interogările de profilare (set profiling=1), faceți o listă de interogări care durează mai mult decât o fracțiune de milisecundă (0.000x secunde) și începeți de acolo.
Probleme comune
Iată cele mai frecvente șase probleme pe care le-am găsit la optimizarea interogărilor MySQL:
ORDER BY și sortare fișiere
Prevenirea sortării fișierelor nu este uneori posibilă din cauza clauzei ORDER BY. Pentru optimizare, stocați rezultatul în Memcache sau efectuați sortarea în logica aplicației.
Folosind ORDER BY cu WHERE și LEFT JOIN
ORDER BY face interogările foarte lente. Dacă este posibil, încercați să nu utilizați ORDER BY. Dacă aveți nevoie de sortare, atunci utilizați sortarea după indici.
Utilizarea ORDER BY pe coloanele temporare
Doar nu o face. Dacă trebuie să combinați rezultatele, faceți-o în logica aplicației; nu utilizați filtrarea sau sortarea pe un tabel temporar interogare MySQL. Acest lucru necesită o mulțime de resurse.
Se ignoră indexul FULLTEXT
Utilizarea LIKE este cea mai bună modalitate de a încetini căutarea textului integral.
Alegere nerezonabilă cantitate mare linii
Uitarea de LIMIT în interogarea dvs. poate crește foarte mult timpul necesar pentru preluarea din baza de date, în funcție de dimensiunea tabelelor.
Utilizarea excesivă a JOIN în loc de a crea tabele sau vizualizări compuse
Când utilizați mai mult de trei sau patru operatori LEFT JOIN într-o singură interogare, întrebați-vă: este totul corect aici? Continuați cu excepția cazului în care aveți un motiv întemeiat, de exemplu - interogarea nu este folosită des pentru ieșire în panoul de administrare sau rezultatul rezultat poate fi stocat în cache. Dacă trebuie să rulați o interogare cu un număr mare operațiuni de îmbinare a tabelelor, atunci este mai bine să vă gândiți la crearea de tabele compuse din coloanele necesare sau la utilizarea vizualizărilor.
Aşa
Am discutat despre elementele de bază ale optimizării și despre instrumentele necesare pentru a face treaba. Am examinat sistemul folosind profilarea și declarația EXPLAIN pentru a vedea ce se întâmplă cu baza de date și a înțelege cum ar putea fi îmbunătățit designul.
Am analizat, de asemenea, câteva exemple și capcane clasice în care puteți cădea când utilizați MySQL. Folosind indicii de index, ne putem asigura că MySQL va selecta indecșii necesari, mai ales când se fac selecții multiple pe același tabel. Pentru a continua studiul subiectului, vă sfătuiesc să priviți spre proiectul Percona.
Lucrul cu o bază de date este adesea cel mai slab punct al performanței multora aplicații web. Și nu doar DBA-urile trebuie să-și facă griji pentru asta. Programatorii trebuie să aleagă structura corecta tabele, scrieți interogări optimizate și cod bun. Următoarele sunt metode pentru optimizarea MySQL pentru programatori.
1. Optimizați interogările pentru cache de interogări
Cele mai multe Servere MySQL Memorarea în cache a interogărilor este activată. Unul dintre cele mai bune moduriÎmbunătățirile de performanță sunt pur și simplu pentru a lăsa stocarea în cache în baza de date în sine. Când o interogare se repetă de mai multe ori, rezultatul acesteia este preluat din cache, ceea ce este mult mai rapid decât accesarea directă a bazei de date. Problema principală este că mulți oameni folosesc pur și simplu interogări care nu pot fi stocate în cache:
// cererea nu va fi memorată în cache$r = mysql_query( „SELECTează numele de utilizator FROM user WHERE signup_date >= CURDATE()”); // si asa va fi! $azi = data("A-m-d"); $r = mysql_query();„SELECT username FROM user WHERE signup_date >= „$today”” Motivul este că prima interogare folosește funcția CURDATE(). Acest lucru se aplică tuturor funcțiilor precum NOW(), RAND() și altora al căror rezultat este nedeterminist. Dacă rezultatul unei funcții se poate modifica, atunci MySQL nu memorează în cache o astfel de interogare. ÎNîn acest exemplu
acest lucru poate fi prevenit prin calcularea datei înainte de executarea interogării.
2. Utilizați EXPLAIN pentru interogările dvs. SELECT// creează o instrucțiune pregătită if ($stmt = $mysqli ->prepare()) { „SELECTARE nume de utilizator FROM utilizator WHERE state=?”// leagă valori $stmt ->bind_param("s" , $state );// executa $stmt ->execute(); // leagă rezultatul$stmt ->bind_result($nume utilizator );// obțineți date
$stmt ->fetch();
printf("%s este de la %s\n" , $nume utilizator , $state );
$stmt ->close(); )
13. Solicitări fără tampon De obicei, atunci când se face o solicitare, scriptul se oprește și așteaptă rezultatul execuției sale. Puteți modifica acest lucru utilizând interogări fără tampon. Există o descriere bună în documentația funcției mysql_unbuffered_query():
„mysql_unbuffered_query() trimite o interogare SQL către MySQL fără să recupereze sau să tamponeze automat rândurile rezultate, așa cum o face mysql_query(). Pe de o parte, aceasta economisește o cantitate semnificativă de memorie pentru interogările SQL care produc seturi mari de rezultate. Pe de altă parte, puteți începe să lucrați la setul de rezultate de tăiere după ce primul rând a fost preluat: nu trebuie să așteptați să ruleze interogarea SQL completă."
Totuși există
anumite restricții
. Va trebui să citiți toate înregistrările sau să apelați mysql_free_result() înainte de a putea rula o altă interogare. De asemenea, nu puteți utiliza mysql_num_rows() sau mysql_data_seek() pentru rezultatul unei funcții.
15. Tabelele cu dimensiuni fixe (statice) sunt mai rapide
Dacă fiecare coloană dintr-un tabel are o dimensiune fixă, atunci tabelul se numește „static” sau „dimensiune fixă”. Exemplu de coloane cu lungime nefixă: VARCHAR, TEXT, BLOB. Dacă includeți un astfel de câmp într-un tabel, acesta nu va mai fi reparat și va fi procesat diferit de MySQL.
Folosirea unor astfel de tabele va crește eficiența, deoarece... MySQL poate căuta mai rapid înregistrările din ele. Când să alegi linia dorită tabel, MySQL își poate calcula poziția foarte rapid. Dacă dimensiunea înregistrării nu este fixă, aceasta este căutată după index.
Aceste tabele sunt, de asemenea, mai ușor de stocat și restaurat după o blocare a bazei de date. De exemplu, dacă convertiți VARCHAR(20) în CHAR(20), intrarea va ocupa 20 de octeți, indiferent de conținutul său real.
Folosind metoda „diviziunii pe verticală”, puteți muta coloanele cu lungimi variabile de rând într-un tabel separat.
16. Separarea verticală
Partiționarea verticală se referă la împărțirea unui tabel în coloane pentru a îmbunătăți performanța.
Exemplul 1. Dacă adresele sunt stocate în tabelul utilizatorilor, atunci nu este un fapt că veți avea nevoie de ele foarte des. Puteți împărți tabelul și puteți stoca adrese masa separata. Astfel, masa de utilizator va fi redusă în dimensiune. Productivitatea va crește.
Exemplul 2: aveți un câmp „last_login” într-un tabel. Este actualizat de fiecare dată când utilizatorul se autentifică pe site. Dar toate modificările aduse tabelului îi șterg memoria cache. Prin stocarea acestui câmp într-un alt tabel, veți menține la minimum modificările aduse tabelului utilizatorilor.
Dar dacă utilizați constant join-uri pe aceste mese, va duce la o performanță slabă.
17. Separați interogările mari DELETE și INSERT
Dacă trebuie să faceți o cerere mare de ștergere sau inserare a datelor, trebuie să aveți grijă să nu rupeți aplicația. Execuţie mare cerere poate bloca masa și poate cauza funcționarea defectuoasă a întregii aplicații.
Apache poate rula mai multe procese paralele în același timp. Prin urmare, funcționează mai eficient dacă scripturile sunt executate cât mai repede posibil.
Dacă blocați mesele pe termen lung(de exemplu, timp de 30 de secunde sau mai mult), apoi, cu trafic mare pe site, poate apărea o coadă mare de procese și solicitări, ceea ce poate duce la muncă lentă site-ul sau chiar o prăbușire a serverului.
Dacă aveți astfel de interogări, utilizați LIMIT pentru a le rula în rafale mici.
somn (50000); )
18. Coloanele mici sunt mai rapide
Pentru o bază de date, lucrul cu hard disk-ul este poate cel mai slab punct. Înregistrările mici și compacte sunt de obicei mai bune în ceea ce privește performanța, deoarece... reduce munca pe disc.
Documentația MySQL are o listă de cerințe de stocare a datelor pentru toate tipurile de date.
Dacă tabelul dvs. va stoca câteva rânduri, atunci nu are sens să faceți cheia principală de tip INT, ar putea fi mai bine să o faceți MEDIUMINT, SMALLINT sau chiar TINYINT. Dacă nu trebuie să stocați ora, utilizați DATE în loc de DATETIME.
Cu toate acestea, ai grijă ca lucrurile să nu iasă ca Slashdot.
19. Alegeți tipul de masă potrivit
20. Folosiți ORM
21. Fii atent la conexiunile persistente
Conexiunile persistente sunt concepute pentru a reduce costul stabilirii comunicării cu MySQL. Odată ce o conexiune este creată, aceasta rămâne deschisă după finalizarea scriptului. Data viitoare, acest script va folosi aceeași conexiune.
mysql_pconnect() în PHP
Dar acest lucru sună bine doar în teorie. Din experiența mea personală (și a altora), utilizarea acestei funcții nu este justificată. Veți avea probleme serioase cu limitele de conectare, limitele de memorie și așa mai departe.
Apache creează multe fire paralele. Acesta este motivul principal pentru care conexiunile persistente nu funcționează atât de bine pe cât ne-am dori. Înainte de a utiliza mysql_pconnect(), consultați administratorul de sistem. De la autor:
unul dintre prietenii mei a decis să-și optimizeze mașina. Mai întâi a scos o roată, așa că a tăiat acoperișul, apoi motorul... În general, acum merge. Acestea sunt toate consecințele unei abordări greșite! Prin urmare, pentru ca SGBD-ul dvs. să continue să ruleze, optimizarea MySQL trebuie făcută corect.
Când să optimizați și de ce?
Nu merită să intri în setările serverului și să schimbi din nou valorile parametrilor (mai ales dacă nu știi cum s-ar putea termina acest lucru). Dacă luăm în considerare acest subiect din „clopotnița” îmbunătățirii performanței resurselor web, atunci este atât de extins încât trebuie să îi fie dedicată o întreagă publicație științifică în 7 volume.
Dar în mod clar nu am așa răbdare ca scriitor și nici tu ca cititor. O vom face mai simplu și vom încerca să ne adâncim doar puțin în desișul de optimizare a serverului MySQL și a componentelor sale. Prin setarea optimă a tuturor parametrilor DBMS, pot fi atinse mai multe obiective:
Creșteți viteza de execuție a interogărilor.
Reduceți timpul de așteptare pentru încărcarea paginilor de resurse.
Reduceți consumul capacității serverului de găzduire.
Reduceți cantitatea de spațiu pe disc consumată.
Vom încerca să împărțim întregul subiect al optimizării în mai multe puncte, astfel încât să fie mai mult sau mai puțin clar ce face „oala să fiarbă”.
De ce să configurați un server
În MySQL, optimizarea performanței ar trebui să înceapă de la server. În primul rând, ar trebui să grăbiți funcționarea acestuia și să reduceți timpul necesar procesării cererilor. Un mijloc universal de a atinge toate obiectivele de mai sus este activarea stocării în cache. Nu știi „ce este”? Acum voi explica totul.
Dacă stocarea în cache este activată pe instanța dvs. de server, sistemul MySQL „își amintește” automat interogarea introdusă de utilizator. Și data viitoare când se repetă, acest rezultat al interogării (pentru eșantionare) nu va fi procesat, ci preluat din memoria sistemului. Se pare că în acest fel serverul „economisește” timp la emiterea unui răspuns și, ca urmare, viteza de răspuns a site-ului crește. Acest lucru se aplică și vitezei generale de descărcare.
În MySQL, optimizarea interogărilor este aplicabilă acelor motoare și CMS care funcționează pe baza acestui DBMS și PHP. În acest caz, codul scris într-un limbaj de programare, pentru a genera o pagină web dinamică, solicită din baza de date unele dintre părțile sale structurale și conținutul (înregistrări, arhive și alte taxonomii).
Datorită stocării în cache activate în MySQL, executarea interogărilor către serverul DBMS este mult mai rapidă. Din acest motiv, viteza de încărcare a întregii resurse în ansamblu crește. Și acest lucru are un efect pozitiv atât asupra experienței utilizatorului, cât și asupra poziției site-ului în rezultatele căutării.
Activați și configurați memoria cache
Dar să ne întoarcem de la teoria „plictisitoare” la practica interesantă. Vom continua optimizarea bazei de date MySQL prin verificarea stării de cache pe serverul dumneavoastră de baze de date. Pentru a face acest lucru, folosind o solicitare specială, vom afișa valorile tuturor variabilelor de sistem:
Este o cu totul altă chestiune.
Să facem o mică trecere în revistă a valorilor obținute, care ne va fi de folos pentru optimizarea bazelor de date Date MySQL:
have_query_cache – valoarea indică dacă interogarea în cache este „ON” sau nu.
query_cache_type – afișează tipul de cache activ. Avem nevoie de valoarea „ON”. Aceasta indică faptul că stocarea în cache este activată pentru toate tipurile de selecție (comanda SELECT). Cu excepția celor care folosesc parametrul SQL_NO_CACHE (interzice salvarea informațiilor despre această interogare).
Avem toate setările setate corect.
Măsurăm memoria cache pentru indecși și chei
Acum trebuie să verificați câtă memorie RAM este alocată pentru indecși și chei. Se recomanda setarea acestui parametru, important pentru optimizarea bazei de date MySQL, la 20-30% din cantitatea de RAM disponibila serverului. De exemplu, dacă sunt alocate 4 „hectare” pentru o instanță DBMS, atunci nu ezitați să setați 32 de „metri”. Dar totul depinde de caracteristicile unei anumite baze de date și de structura ei (tipurile) de tabele.
Pentru a seta valoarea parametrului, trebuie să editați conținutul fișierului de configurare my.ini, care în Denver se află pe următoarea cale: F:\Webserver\usr\local\mysql-5.5
Deschideți fișierul folosind Notepad. Apoi găsim în el parametrul key_buffer_size și setăm dimensiunea optimă pentru sistemul PC-ului tău (în funcție de „hectarele” de RAM). După aceasta, trebuie să reporniți serverul bazei de date.
SGBD utilizează mai multe subsisteme suplimentare (nivel inferior), iar toate setările lor principale sunt, de asemenea, specificate în acest fișier de configurare. Prin urmare, dacă trebuie să optimizați MySQL InnoDB, atunci bine ați venit aici. Vom studia acest subiect mai detaliat într-unul dintre materialele noastre viitoare.
Măsurarea nivelului indicilor
Utilizarea indecșilor în tabele crește semnificativ viteza de procesare și generare a unui răspuns DBMS la o interogare introdusă. MySQL „măsoară” în mod constant nivelul de utilizare a indexului și a cheilor în fiecare bază de date. Pentru a obține această valoare, utilizați interogarea:
AFIȚI STARE CA „handler_read%”
AFIȚI STARE CA „handler_read%” |
În rezultatul rezultat, ne interesează valoarea din linia Handler_read_key. Dacă numărul indicat acolo este mic, atunci aceasta indică faptul că indecșii nu sunt utilizați aproape niciodată în această bază de date. Și asta este rău (ca al nostru).
Gestionarea indecșilor, adică modul în care sunt creați și întreținute, poate avea un impact semnificativ asupra performanței interogărilor SQL.
Foarte des pot fi aplicate următoarele optimizări:
- eliminați indecșii neutilizați
- identifica indicii neutilizați și ineficienți
- îmbunătățirea indicilor
- evitați cu totul interogările SQL!
- simplifica interogările SQL
- și opțiuni magice de cache
Combinarea interogărilor DDL
Interogările care modifică structura datelor blochează de obicei tabelul. Din punct de vedere istoric, rularea unei interogări ALTER a necesitat crearea unei noi copii a tabelului, care poate consuma mult timp și disc. Prin urmare, în loc de trei interogări cu modificări mici, este mult mai profitabil să efectuați una combinată. Acest lucru poate economisi o cantitate semnificativă de timp în sarcinile de administrare a bazei de date.
Eliminarea indecșilor duplicați
Indexurile duplicate sunt dăunătoare din două motive: toate cererile de modificare a datelor vor fi mai lente, deoarece se lucrează dublu pentru a menține caracterul complet al indexului. În plus, acest lucru creează o încărcare suplimentară asupra sistemului de fișiere, deoarece dimensiunea bazei de date devine fizic mare și duce la o creștere a timpului de creare a backup-ului și a timpului de recuperare.
Câteva condiții simple pot duce la indecși duplicați. De exemplu, mysql nu are nevoie de un index pe câmpurile PRIMARY.
Un index duplicat poate exista și dacă partea stângă a unuia dintre indici este exact aceeași cu alt index.
Utilitarul pt-duplicate-key-checker de la perkona-toolkit este o modalitate simplă și rapidă de a verifica structura bazei de date pentru prezența indecșilor inutile.
Eliminarea indecșilor neutilizați
Pe lângă indecșii care nu sunt utilizați niciodată deoarece sunt duplicate, pot exista indecși neduplicați care pur și simplu nu sunt utilizați niciodată. Astfel de indici au același impact ca indici duplicați. În mysql standard nu există nicio modalitate de a determina ce indici nu sunt utilizați, dar unele versiuni au o caracteristică similară, de exemplu atunci când se utilizează patch-ul Google MySQL.
Acest patch a introdus o funcție: SHOW INDEX_STATISTICS.
Și în mysql obișnuit, mai întâi trebuie să colectați toate interogările sql utilizate, să le rulați și să priviți planul de execuție, în timp ce colectați informații despre indecșii utilizați în fiecare caz și aduceți acest lucru într-un singur tabel. În orice caz, este o experiență utilă.
Optimizarea câmpurilor index.
Pe lângă crearea de noi indici pentru a îmbunătăți performanța, puteți îmbunătăți performanța prin optimizări suplimentare de proiectare. Aceste optimizări includ utilizarea de date personalizate și tipuri de câmpuri. Beneficiul în acest caz este o încărcare mai mică a discului și un volum mai mare de indici care pot încadra în RAM.
Tipuri de date
Unele tipuri pot fi înlocuite fără durere pe bazele existente actuale.
BIGINT vs INT
Când tasta PRIMARY este definită ca BIGINT AUTO INCREMENT, în general, nu există niciun motiv pentru a o folosi. Tipul de date INT UNSIGNED AUTO_INCREMENT poate stoca un număr maxim de până la 4294967295. Dacă aveți de fapt mai multe înregistrări decât acest număr, probabil că veți avea nevoie de o arhitectură diferită.
De la o astfel de schimbare de la BIGINT la INT UNSIGNED, fiecare rând al tabelului începe să ocupe de 2 ori mai puțin spațiu pe disc, în plus, dimensiunea ocupată de cheia PRIMARY este redusă de la 8 octeți la 4.
Aceasta este probabil una dintre cele mai tangibile îmbunătățiri simple care pot fi făcute destul de fără durere.
DATETIME vs TIMESTAMP
Totul este simplu aici: timestamp - 4 bytes, datetime - 8 bytes.
Ar trebui folosit ori de câte ori este posibil deoarece:
- verificare suplimentară a integrității datelor
- un astfel de câmp va folosi doar 1 octet pentru a stoca 255 de valori unice
- Astfel de câmpuri sunt mai convenabil de citit :)
Din punct de vedere istoric, utilizarea câmpurilor enumerare a dus la ca baza să fie dependentă de modificări ale valorilor posibile din enumerare. Aceasta a fost o solicitare DDL de blocare. Începând cu MySQL 5.1, adăugarea de noi variante la o enumerare este foarte rapidă și nu are legătură cu dimensiunea tabelului.
NULL vs NOT NULL
Dacă nu sunteți sigur că o coloană poate conține o valoare nulă (NULL), este mai bine să o definiți ca NOT NULL. Indicele de pe o astfel de coloană va fi mai mic ca dimensiune și mai ușor de procesat.
Conversii automate de tip
Când selectați un tip de date pentru alăturarea câmpurilor, se poate întâmpla ca tipul de date al câmpului să fie nedefinit. Conversia încorporată poate fi o suprasolicitare complet inutilă.
Pentru câmpurile întregi, asigurați-vă că SIGNED și UNSIGNED se potrivesc, pentru tipurile de câmpuri variabile, convertirea codificării la alăturare poate fi inutilă, așa că asigurați-vă că le verificați și pe acestea. O problemă comună este conversia automată între codificările latin1 și utf8.
Tipuri de coloane
Unele tipuri de date sunt adesea stocate în coloane greșite. Schimbarea tipului atunci când faceți acest lucru poate duce la o stocare mai eficientă, mai ales când acele coloane sunt incluse în index. Să ne uităm la câteva exemple tipice.
adresa IP
Adresa IPv4 poate fi stocată în câmpul INT UNSIGNED, care necesită doar 4 octeți. O situație comună apare atunci când adresa IP este stocată în câmpul VARCHAR(15), care are 12 octeți. Această modificare poate reduce dimensiunea cu 2/3. Funcțiile INET_ATON() și INET_NTOA sunt folosite pentru a converti între un șir cu o adresă IP și o valoare numerică.
Pentru adresele IPv6, care devin din ce în ce mai populare, este important să stocați valoarea lor numerică de 128 de biți în câmpurile BINARY(16) și să nu utilizați VARCHAR pentru formatul care poate fi citit de om.
Stocarea câmpurilor md5 ca CHAR(32) este o practică obișnuită. Dacă utilizați un câmp VARCHAR(32), adăugați, de asemenea, o suprasarcină suplimentară de lungime a șirului pentru fiecare valoare. Cu toate acestea, un șir md5 este o valoare hexazecimală - și poate fi stocat mai eficient folosind funcțiile UNHEX() și HEX(). În acest caz, datele pot fi stocate în câmpuri BINARY(16). Această acțiune simplă va reduce dimensiunea câmpului de la 32 de octeți la 16 de octeți. Un principiu similar poate fi aplicat oricăror valori hexazecimale.
Bazat pe cartea lui Ronald Bradford.
→ Optimizarea interogărilor MySQL
MySQL are o gamă largă de funcții pentru diferite sortări ( COMANDA PENTRU), grupuri ( GROUP BY), asociații ( LEFT JOIN sau ÎNSCRIEȚI DREPT) și așa mai departe. Toate sunt cu siguranță convenabile, dar în condiții de solicitări unice. De exemplu, dacă personal trebuie să descoperi ceva în baza de date folosind o grămadă de tabele și link-uri, atunci, pe lângă funcțiile de mai sus, poți și chiar trebuie să folosești operatori condiționali DACĂ. Principala greșeală a programatorilor începători este dorința de a aplica astfel de interogări în codul de lucru al site-ului. În acest caz, o interogare complexă este cu siguranță frumoasă, dar dăunătoare. Chestia este că orice operatori de sortare, grupare, alăturare sau imbricate nu pot fi executați în RAM și folosesc hard disk-ul pentru a crea tabele temporare. Și hard disk-ul, după cum știți, este blocajul serverului.
Reguli pentru optimizarea interogărilor mysql
1. Evitați interogările imbricate
Aceasta este cea mai gravă greșeală. Procesul părinte va aștepta întotdeauna finalizarea procesului copil și în acest moment păstrează o conexiune la baza de date, folosește discul și încarcă iowait. Două solicitări paralele la baza de date și efectuează filtrarea necesară în interpretul serverului ( Perl, PHP etc.) va fi executat cu un ordin de mărime mai rapid decât cel imbricat.
Exemple în perl ce sa nu faci:
My $sth = $dbh->prepare("SELECT elementID,elementNAME,groupID FROM tbl WHERE groupID IN(2,3,7)");
$sth->execute();
while (my @row = $sth->fetchrow_array()) ( my $groupNAME = $dbh->selectrow_array("SELECT groupNAME FROM groups WHERE groupID = $row"); ### Să presupunem că trebuie să colectați numele de grupuri ### și adăugați-le la sfârșitul matricei de date push @row => $groupNAME ### Faceți altceva... )
Dacă este nevoie de astfel de acțiuni, în toate cazurile este mai bine să utilizați un hash, o matrice sau orice altă cale de filtrare.
Un exemplu în perl, așa cum fac de obicei:
%grupurile mele;
my $sth = $dbh->prepare("SELECT groupID,groupNAME FROM groups WHERE groupID IN(2,3,7)");
$sth->execute();
while (@row-ul meu = $sth->fetchrow_array()) ( $groups($row) = $row; ) ### Acum să facem preluarea principală fără subinterogarea my $sth2 = $dbh->prepare("SELECT elementID ,elementNAME,groupID FROM tbl WHERE groupID IN(2,3,7)");
$sth2->execute();
while (@row-ul meu = $sth2->fetchrow_array()) ( push @row => $groups($row); ### Să facem altceva... )
2. Nu sortați, grupați sau filtrați în baza de date
Dacă este posibil, nu utilizați operatorii ORDER BY, GROUP BY sau JOIN în interogările dvs. Toți folosesc tabele temporare. Dacă sortarea sau gruparea este necesară doar pentru afișarea elementelor, de exemplu alfabetic, este mai bine să efectuați aceste acțiuni în variabilele interpretor. Exemple Perl despre cum să nu sortați: My $sth = $dbh->prepare("SELECT elementID,elementNAME FROM tbl WHERE groupID IN(2,3,7) ORDER BY elementNAME");
$sth->execute();
în timp ce (@row-ul meu = $sth->fetchrow_array()) (printează qq($row => $row); )
Un exemplu în perl despre cum de obicei sortez:
Lista mea de $ = $dbh->selectall_arrayref("SELECT elementID,elementNAME FROM tbl WHERE groupID IN(2,3,7)");
foreach (sortare ( $a-> cmp $b-> ) @$list)( print qq($_-> => $_->); )
