Share
Am folosit phpMyAdmin de peste un deceniu. În primii ani cu instrumentul, aveam nevoie de ceva care să-mi arate structura tabelului și să-mi dau rapid datele înăuntru. Pe măsură ce nevoile mele au crescut, tot așa au și instrumentele incluse în phpMyAdmin, care mă împiedică să revin ca instrument principal MySQL, chiar și cu optimizarea.
Am avut plăcerea de a lucra cu mai multe baze de date diferite. Fiecare are dezavantajele sale și fiecare are punctele forte. Când am dat o alegere, am tendința de a migra înapoi la MySQL, în ciuda faptului că sunt prea ieftin pentru a achiziționa MySQL Enterprise. În schimb, mă fac dator cu phpMyAdmin ca principalul meu instrument de profilare. Ea funcționează bine pentru mine, dar a trebuit să fac destul de puțin de cercetare pentru a înțelege ce mă uit la analizarea cererilor mele. Sper să treacă acest lucru într-un mod care poate fi înțeles de către începător, până la proaspătul profesionist.
Optimizarea necesită timp. Managerii, clienții și colegii, pentru că contează, nu le place să audă că un proiect este în spatele graficului datorită optimizării. De multe ori ne grăbim optimizarea pentru a respecta acele valori de referință. În final, totuși, nu facem pe nimeni vreun favor. Cea mai frumoasă aplicație web din lume vă va face repetarea afacerii dacă durează 10 secunde pentru a încărca fiecare pagină. De asemenea, dacă așteptăm să optimizăm până la sfârșitul proiectelor noastre, există șanse să fie mult mai mult de făcut decât dacă am fi verificat pe măsură ce proiectul merge mai departe.
Câteva note înainte să intrăm în carne și cartofi. În primul rând, nu voi intra în MySQL Tuning, deoarece este puțin din sfera acestui tutorial. În timp ce tuning-ul este optimizare, este un subiect în sine în opinia mea. Voi menționa pe scurt câteva oportunități de a optimiza cum să vă reglați serverul, dar mențiunile vor fi scurte. În plus, mă voi uita în principal la tabelele MyISAM și nu la tabelele InnoDB. Regula principală este dacă scrieți o mulțime de date, utilizați InnoDB, dar dacă utilizați SELECT mult mai mult, utilizați MyISAM. De asemenea, nu intrăm în tabelul REPAIR, OPTIMIZE, CHECK și ANALYZE deoarece acest tutorial acoperă optimizarea interogărilor cu phpMyAdmin. Din nou, aceasta este puțin din sfera de aplicare a acestui tutorial.
În cele din urmă, mă voi uita la WordPress ca pe un exemplu real al lumii. Voi fi primul care vă va spune că nu sunt un expert în WordPress, dar pot să văd întrebările generate cu cei mai buni dintre ei. Din ceea ce am văzut, baza de date cu WordPress este bine indexată, dar odată ce începem să adăugăm lucruri care se află în afara acelor fișiere principale de bază, acei indicatori ar putea să nu fie cei mai buni pentru ceea ce avem nevoie.
"Optimizarea necesită timp. Managerii, clienții și colegii, pentru că nu vor să audă că un proiect este în spatele graficului datorită optimizării".
Răspunsul scurt este da.
Răspunsul lung este phpMyAdmin ne oferă șansa de a vedea dacă trebuie să optimizăm interogările noastre și cât de rău trebuie să le optimizăm. Mi-aș imagina că ați văzut acest ecran de mai multe ori dacă ați folosit phpMyAdmin:
Este ecranul standard de pornire pentru phpMyAdmin. Dacă nu căutați modalități de optimizare, ar fi bine să mergeți direct la mesele din meniul din stânga și să nu vedeți niciodată meniul tab-ului din partea de sus. Acest meniu, în special fișierele Stare și Variabile, sunt locul unde vom începe.
Să începem cu ecranul Status, care ar putea fi cel mai important instrument pe care îl oferă phpMyAdmin:
Acesta este partea de sus a ecranului de stare. Deși are unele date interesante, dacă nu ați trecut niciodată sub parola, ați pierdut câteva informații foarte importante. Din motive de coincidență, vreau să mă uit la două valori contrare foarte simple pe care le obsedez, primul din mediul meu de testare:
Cele două valori care trebuie să acorde o atenție deosebită sunt Handler_read_rnd și Handler_read_rnd_next. Dacă cele două valori sunt în roșu, atunci există câteva interogări care trebuie verificate, ca atunci când MySQL face o selecție, se citește întregul tabel. În unele cazuri, acest lucru ar putea fi prin design, ca atunci când plasați un index pe o masă, este nevoie de un pic mai mult pentru a scrie, și este nevoie de un pic mai mult spațiu. Cu toate acestea, dacă vedeți ceva de genul:
șansele sunt, acest lucru nu a fost prin design. 141 milioane de solicitări de citire a unui rând pe o poziție fixă și 16 miliarde de cereri de citire a următorului rând înseamnă probabil că ne lipsește un indice sau două (mii). Evident, acest număr crește pe baza numărului de solicitări, astfel încât cu cât un motor de căutare indexează mai mult site-ul dvs. sau cu cât mai mulți vizitatori ai, cu atât devine un indice mic mai mic. Scanările complete de mese sunt dușmanul, iar acest lucru vă oferă o modalitate rapidă de a vedea cât de aproape este inamicul la porți.
Un alt tabel grozav pentru a verifica performanța interogării aruncă o privire asupra selecțiilor și indexelor direct:
Acest tabel acordă o atenție deosebită participărilor dvs. O combinație periculoasă nu se folosește și nu se indexează pe niciun tabel, deoarece scanările complete ale meselor cresc exponențial pe numărul de conexiuni pe care le utilizați. Cu cât tabelele dvs. sunt mai normalizate, cu atât mai mult trebuie să acordați atenție indexurilor dvs., precum și definiția câmpurilor pe care le îmbinați.
În cele din urmă, în funcție de o variabilă globală, veți dori să verificați și această tabelă variabilă:
Dacă vă logați la întrebările dvs. lente, acest contor variabil arată numărul care a fost identificat pentru observație, în funcție de setarea timpului de interogare lung. Aceste variabile pot fi găsite din fila variabile. O privire rapidă în mediul meu de testare arată această setare (deocamdată):
Aceste două file arată mai multe informații, dintre care unele sunt absolut necesare pentru reglarea serverului MySQL. PhpMyAdmin face foarte ușor chiar și începătorului să identifice o problemă și să aibă o înțelegere de bază a problemei. Dacă o valoare este verde, suntem buni. Dacă este roșu, este nevoie de o atenție. De asemenea, ne permite să înțelegem că am făcut unele progrese. Când restarim serverul nostru, aceste variabile de sesiune sunt toate spălate. Dacă am făcut schimbări, putem vedea chiar dacă l-am făcut vreun impact.
Acum că am identificat că trebuie să facem o anumită optimizare, să analizăm unele dintre instrumentele pe care le vom folosi înainte de a ne găsi problemele. Primul dintre instrumente, și probabil cel mai util este să utilizați EXPLAIN. EXPLAIN ne dă practic planul de executare a interogării. Acest lucru ne spune ce intenționează MySQL să facă cu această interogare înainte de executarea acesteia.
Fără să citiți pe EXPLAIN, este posibil ca rezultatele să nu însemne mult pentru dvs. Folosind un tabel pe care l-am creat pentru un tutorial trecut, să analizăm un plan de execuție neoptimizat. Tabelul meu are doar două câmpuri în acest caz, unul fiind sales_id, iar celălalt fiind sales_amount. Iată interogarea cu care lucrez:
SELECT sales_id, sale_amount DE LA tutorial.sales ORDER BY sale_amount
La suprafață, aceasta este o interogare foarte simplă. Fiind însă o masă de vânzări, masa va crește și va crește și crește. Am generat 200 de înregistrări pentru tutorialul precedent și făcând un simplu SELECT cu o clauză ORDER BY, de fapt, a durat mult mai mult decât mi-aș fi așteptat:
Interogarea cu doar 200 de înregistrări ne costă 15 secunde. Să folosim EXPLAIN pentru a înțelege modul în care MySQL văd această interogare. Doar faceți clic pe linkul "Explicați SQL" pentru a vedea rezultatele:
La fel ca majoritatea lucrurilor, acest lucru nu are sens decât dacă înțelegeți ce se spune. Pentru cineva care nu a executat niciodată un EXPLAIN la o interogare, acest lucru ar putea fi scris în hieroglife. Să vedem dacă putem traduce ceva mai ușor de înțeles.
Tipul select_type ne spune că MySQL vede SELECT-ul ca simplu, mergeți la o masă și un proces. Dacă ar exista o uniune sau o subcotare, atunci aceasta ar arăta ce parte din instrucțiunea SELECT ar fi chemat. De exemplu, dacă creez o interogare care are un subquery:
SELECT sale_amount ca sumă de la vânzări WHERE sales_id IN (SELECT sales_id FROM sales_force WHERE sales_id = 4)
Avem un EXPLAIN de acest lucru:
Ceea ce ne spune despre interogarea însăși. În acest caz, select_type-ul nostru sa schimbat pentru a spune că prima interogare este primară, iar apoi MySQL va ieși și va efectua subdotarea, care este o vizualizare, deci există un alt subquery pentru a efectua, prin urmare, vom termina cu cele trei separate ID-uri. Manualul de referință MySQL oferă toate valorile posibile:
Înapoi la exemplul nostru original:
Tipul este cel care trebuie să acorde atenție, deoarece vă spune dacă MySQL va scana întreaga tabelă sau dacă va folosi un index pentru a găsi rapid rezultatele. Aceasta este coloana primară pentru a vă uita la optimizarea interogărilor dvs. De la ordinul bun la rău, valorile sunt:
În cazul în care doriți să începeți, se optimizează orice interogare care este fie tipul de index sau toate. Dacă vă puteți scuti aplicația de la aceste două tipuri, performanța dvs. se va îmbunătăți. Prietenii mei, este locul unde începi.
Restul coloanelor se ocupă de indexurile pe care le va utiliza MySQL și de numărul de rânduri pe care va trebui să le scaneze înainte de a vedea dacă există un rezultat valid. Pe măsură ce eliminați tipurile "index" și "all", acestea vin la îndemână pentru a înțelege exact ce index MySQL utilizează pentru a executa această interogare. Pentru a muta o interogare pe scară, începeți să vă optimizați indexurile pentru a îmbunătăți performanța. În scopul ilustrării, mă voi abține să scot scanarea "tuturor" sau a meselor complete.
Coloana finală este coloana "extra". Coloana suplimentară vă oferă informații despre interogare, indiferent dacă este sau nu folosită o clauză WHERE, indiferent dacă este sau nu imposibilă WHERE, adică această interogare va întoarce întotdeauna un NULL deoarece clauza WHERE face imposibilă executarea. Singura valoare pe care trebuie să o acordăm o atenție deosebită și să ne eliberăm de ea este "Folosirea filelor" pe care o avem în exemplul nostru. Când vedeți acea valoare, MySQL trebuie să facă o altă trecere prin rezultate pentru a sorta valorile. Deci, în cazul interogării noastre originale:
SELECT sales_id, sale_amount DE LA tutorial.sales ORDER BY sale_amount
Nu numai că MySQL scanează întreaga tabelă, dar trebuie să o scaneze de două ori pentru a sorta rezultatele din cauza instrucțiunii ORDER BY. Acest lucru este evident dublu de rău. Vom optimiza această interogare și multe altele în secțiunile următoare.
În MySQL 5.0.37 a devenit disponibil un alt instrument pentru optimizarea utilizării, și anume profilul MySQL. În plus, phpMyAdmin a adăugat suport pentru această caracteristică în versiunea 2.11, deci dacă aveți ambele versiuni disponibile, avem un alt instrument de adăugat la optimizare.
Ceea ce face MySQL Profiler, este de a oferi informații despre blocajele interogărilor noastre. Ne permite să vedem ce se întâmplă pe parcursul executarea reală a interogărilor noastre, viciul a ceea ce face EXPLAIN, care este planul de execuție inainte de. Să vedem ce informații putem obține de la phpMyAdmin din interogarea mea inițială proastă:
Dacă faceți clic pe caseta de selectare "Profilare" de sub interogarea noastră, o nouă lume se deschide cu:
phpMyAdmin oferă timpii de execuție efectivi ai interogării care a fost furnizată. Putem vedea acum blocajele de unde ar trebui abordate interogările noastre sau chiar structura nivelului meselor. Poate că vedem necesitatea din fișierele de jurnal că acest tabel nu este scris într-adevăr la fel de mult ca și cum se citește, deci în loc de InnoDB, îl putem acum schimba în MyISAM.
Există un dezavantaj în utilizarea phpMyAdmin atunci când folosiți MySQL Profiler, și asta este faptul că profiler se bazează pe sesiune, iar phpMyAdmin distruge sesiunea pe fiecare vizualizare de pagină ... Problema pe care ne-o dă este că nu avem o cale pentru a menține un total de date de profil, dar există o modalitate de a păcăli phpMyAdmin, deși într-o manieră cludgy:
SET PROFIL = 1; SELECT sales_id, sale_amount DE LA tutorial.sales ORDER BY sale_amount; SHOW profile;
Care are ca rezultat:
Deoarece executăm mai multe interogări, trebuie să utilizați delimitatorul. Aceasta va arăta că interogarea mea este query_id 1. La fiecare dată după care execut această interogare, aceasta este încă query_id 1, deoarece sesiunea mea este distrusă la pornire. Nu sunt sigur dacă acest lucru este de design, un bug sau o ignoranță din partea mea că phpMyAdmin distruge sesiunea cu comanda QUIT, dar putem rezolva această problemă doar puțin. MySQL are o minunată scriere despre utilizarea profilatorului de către Robin Schumacher și voi folosi o interogare a lui Robin pentru a obține numărul de operații în phpMyAdmin:
SET PROFIL = 1; SELECT sales_id, sale_amount DE LA tutorial.sales ORDER BY sale_amount; SELECT min (seq) ca secventa, stare, numar (*) ca operatiuni, rotunda (suma (durata), 5) ca durata FROM information_schema.profiling WHERE query_id = 1 GROUP dupa ORDER by seq;
Din nou, nu este ideal cu phpMyAdmin, dar în continuare obținem ceea ce ne dorim în cele din urmă:
Înainte de a pune tot ce am învățat împreună, să aruncăm o privire asupra modului de capturare a interogărilor utilizând fișierele de jurnal ale MySQL. Putem capta fiecare interogare pe care rulează MySQL în tabela mysql.general_log. Prin rularea acestei comenzi:
SET GLOBAL general_log = 'ON'; SET GLOBAL log_output = 'TABLE';
Acum putem avea o înregistrare pentru toate interogările care se execută, indiferent de sursă. În timp ce această operațiune este costisitoare și nu o derulăm într-un cadru de producție, aceasta ne oferă o metodă clară și concisă de a obține toate întrebările noastre și ordinea execuției lor din aplicațiile noastre. Pe scurt, acesta ar putea fi cel mai valoros instrument de optimizare a interogărilor SQL pe care îl aveți în caseta de instrumente. Prin stabilirea acestor două variante GLOBAL, avem ultimul pas în obținerea unor tehnici practice de optimizare.
Iată o sumă abreviată din tabela mysql.general_log folosind această interogare:
SELECT event_time, command_type, argument FROM mysql.general_log ORDER BY event_time
produce acest lucru:
De fapt am interogarea mea, împreună cu tot ce a făcut phpMyAdmin în fundal. Dacă am golirea tabelului înainte de fiecare nouă comandă, am ceva cu care pot lucra în fiecare vizualizare de pagină sau apel AJAX pe care îl fac din aplicațiile mele. Pentru a goli jurnalul, pur și simplu TRUNCATE masa așa:
TRUNCATE mysql.general_log
Truncate este o afirmație mult mai bună de folosit decât DELETE FROM, deoarece instruciunea DELETE șterge rândul după rând, unde TRUNCATE goleste întregul tabel deodată.
Odată ce ați terminat optimizarea, pur și simplu trebuie să dezactivați jurnalul de interogări cu această comandă:
SET GLOBAL general_log = 'OFF';
Jurnalul general devine costisitor în timp și cu siguranță încetinește performanța aplicației. Mă opresc între optimizările mele, pentru a obține o simțire organică pentru performanța a ceea ce scriu. Acestea fiind spuse, în curs de dezvoltare, întotdeauna păstrez logul lent de interogări activat, pe măsură ce vreau să văd întrebările mele mai lent ca un instrument de optimizare rapidă. Puteți face acest lucru cu ușurință:
SET GLOBAL slow_query_log = 'ON'; SET GLOBAL log_queries_not_using_indexes = 'ON'; SET GLOBAL log_output = 'TABLE';
și putem verifica că din fila Variabile din pagina noastră de bun venit:
Pentru a vedea rezultatul, trebuie doar să verificăm mysql.slow_log sau să putem folosi o interogare de genul:
SELECT sql_text din mysql.slow_log
Ceea ce îmi dă interogările reale înregistrate ca lent:
Acum putem pune acest lucru în totalitate și vom folosi phpMyAdmin ca un instrument relativ decent de optimizare a interogării. Să începem cu primul exemplu de interogare:
EXPLAIN SELECT sales_id, sale_amount DE LA tutorial.sales ORDER BY sale_amount
Care produce o producție de:
Știm că trebuie să obținem cel puțin un INDEX pe această masă. Să ne oprim și să ne gândim cum se folosește acest tabel. Este o masă simplă de căutare pentru a vă alătura unui tabel sales_force pentru a ne spune că au făcut o vânzare care a fost de suma înregistrată. Dacă tot ce facem vreodată este să vă alăturați acestui tabel de pe sales_id, atunci este ceea ce trebuie să indexăm făcând clic pe link-ul de detalii:
Putem apoi să definim acel index ca acesta:
Interogarea noastră originală ne dă încă o scanare completă, dar într-o aplicație practică:
SELECT sfn.first_name, sfn.last_name, s.sale_amount DIN sales_force_normalized sfn INNER JOIN vânzări s ON sfn.sales_id = s.sales_id
Să vedem dacă este mai bine:
Acum ajungem undeva. Cu toate acestea, dacă facem ceva de genul:
SELECT max (sales_amount) din vânzări
Apoi ne întoarcem în aceeași barcă de a face o scanare completă a mesei. În acest caz, putem să editați indexul și să adăugăm suma de vânzare:
Ceea ce ne îmbunătățește de la rău la rău:
Sau putem adăuga un nou indice la doar suma:
Și avem rezultatul minunat al:
Ceea ce înseamnă că MySQL nici măcar nu trebuie să deschidă masa, deoarece trebuie doar să analizeze indexul. Am atins nivelul optim absolut pentru această funcție COUNT. Consultați cât timp a fost necesar să executați această interogare acum:
Și, pentru o bună măsură, să faceți clic pe caseta de selectare Profilare din interogare pentru a vedea eventualele blocaje acum:
Am jucat cu interogări pretexte și pretindem baze de date, dar să punem acest tutorial la test. Am o instalare WordPress stoc, cu doar pluginul Lorem Ipsum pentru a adăuga aproximativ 5000 de postări și 11.000 de comentarii, astfel încât să putem pune o mică presiune asupra MySQL atunci când facem selecțiile noastre.
Să începem din nou logarea interogărilor noastre din phpMyAdmin și să trunchăm jurnalele lentă și generală pentru a vedea ce se întâmplă atunci când încărcăm o pagină din WordPress:
SET GLOBAL general_log = 'ON'; TRUNCATE mysql.slow_log; TRUNCATE mysql.general_log;
Există câteva artefacte în general_log, deoarece phpMyAdmin cauzează o anumită activitate în cadrul MySQL, dar ar trebui să fim capabili să obținem totul în ordine când îmi reîncarc pagina de index din WordPress în acest moment și dacă folosim o condiție LIKE, poate obține în mare parte doar rezultate WordPress, deoarece tabelele sunt prefixate cu wp_:
SELECT event_time, command_type, argument FROM mysql.general_log WHERE argument LIKE "% wp_%" ORDER BY event_time
Care ne dă un rezultat rezonabil din:
Acum, știm că WordPress ne dă 11 întrebări cu privire la încărcarea paginii index cu o instalație destul de vanilie. Să găsim ceva optimizat pentru a fi ratat. Dacă luăm prima interogare care este executată ori de câte ori se încarcă WordPress:
EXPLAIN SELECT nume_activitate, opțiune_valoare FROM wp_options WHERE autoload = 'da'
Aflăm că acest lucru nu este optimizat:
Să aruncăm o privire la ceea ce au făcut prin phpMyAdmin:
Vedem că există un index pe opțiunea nume_intilizare, dar nu există un index pe autoload, care este condiția specificată pe pagina index. Să adăugăm și să vedem dacă nu putem optimiza instalarea de bază WordPress doar puțin:
Deoarece autoload este varchar și fie "da" sau "nu" din ceea ce văd, pot limita valoarea indexului meu la 1. Înseamnă că acum vede fie "y", fie "n", ceea ce ne reduce timpul și mai mult. Să vedem EXPLAIN după ce am optimizat:
Am trecut de la rău, la al patrulea cel mai bun tip. Nu-i rău pentru câteva minute de muncă. Acordat, WordPress nu a sufocat această valoare, dar, în funcție de încărcarea blogului dvs., fiecare bit ajută. Acordat acum, scrierea durează mai mult, pentru că trebuie să indice "y" sau "n" pentru fiecare linie care este scrisă.
Dacă mergem puțin mai departe, putem vedea MySQL Profiler în acțiune doar prin bifarea casetei de selectare "Profilare". Acum vedem că interogarea noastră este într-adevăr bâzâit chiar de-a lungul:
Optimizarea nu este ușoară, nici nu este chiar foarte distractivă. Cu toate acestea, atunci când ignorați acest pas de dezvoltare, acesta întotdeauna vine înapoi să te bântuie. Cred că este relativ ușor să folosiți instrumentele din phpMyAdmin pentru a obține o privire optimă asupra aplicațiilor dvs. Acestea fiind spuse, există instrumente noi adăugate tot timpul, cum ar fi Jet Profiler, care ia ceea ce tocmai am făcut într-un timp real și o natură grafică.
.
Accentsconagua