Înțelegerea interogări MySQL cu explica

sunteți în noul loc de muncă ca Administrator de baze de date sau inginer de date și tocmai te-ai pierdut încercând să dau seama ce aceste interogări caută nebun ar trebui să însemne și de a face. De ce există 5 se alătură și de ce există un ORDER BY utilizat într-o subquery înainte de una dintre se alătură chiar se întâmplă? Amintiți – vă, ați fost angajat dintr-un motiv-cel mai probabil, acest motiv are de-a face și cu multe interogări complicate care au fost create și editate în ultimul deceniu.

EXPLAIN cuvântul cheie este utilizat în diferite baze de date SQL și oferă informații despre modul în care baza de date SQL execută o interogare. În MySQL, EXPLAIN poate fi utilizat în fața unei interogări care începe cu SELECTINSERTDELETEREPLACE, și UPDATE. Pentru o interogare simplă, ar arăta următoarele:

EXPLAIN SELECT * FROM foo WHERE foo.bar = 'infrastructure as a service' OR foo.bar = 'iaas';

în loc de rezultatul obișnuit, MySQL ar arăta apoi planul său de execuție a declarației explicând ce procese au loc în ce ordine la executarea declarației.

Notă: Dacă EXPLAIN nu funcționează pentru dvs., este posibil ca utilizatorul bazei de date să nu aibă privilegiul SELECT pentru tabelele sau vizualizările pe care le utilizați în instrucțiunea dvs.

EXPLAIN este un instrument excelent pentru a remedia rapid interogări lente. Deși vă poate ajuta cu siguranță, nu va elimina nevoia de gândire structurală și o imagine de ansamblu bună a modelelor de date existente. Adesea, cel mai simplu remediu și cel mai rapid sfat este să adăugați un index la coloanele unui anumit tabel în cauză dacă sunt utilizate în multe interogări cu probleme de performanță. Atenție, totuși, nu folosiți prea mulți indici, deoarece acest lucru ar putea fi contraproductiv. Citirea indexului și a tabelului are sens numai dacă tabelul are o cantitate semnificativă de rânduri și aveți nevoie doar de câteva puncte de date. Dacă recuperați un set de rezultate uriașe dintr-un tabel și interogați adesea coloane diferite, un index pe fiecare coloană nu are sens și împiedică performanța mai mult decât ajută. Pentru mai multe informații despre calculele reale ale indexului vs fără index, citiți estimarea performanței în documentația oficială MySQL.

lucrurile pe care doriți să le evitați ori de câte ori este posibil și aplicabil sunt sortarea și calculele în cadrul interogărilor. Dacă credeți că nu puteți evita calculele în cadrul întrebărilor dvs.: Da, puteți. Scrieți rezultatul setat în altă parte și calculați punctul de date în afara interogării, acesta va pune mai puțină presiune asupra bazei de date și, prin urmare, va fi în general mai bun pentru aplicația dvs. Asigurați-vă că documentați de ce calculați în cadrul aplicației dvs., mai degrabă decât să aveți un rezultat produs în SQL imediat. În caz contrar, următorul administrator de baze de date sau dezvoltator va veni de-a lungul și au ideea glorioasă de a folosi un calcul în interogarea de-a lungul liniilor de, „Oh uite, predecesorul meu nici măcar nu știu puteți face asta în SQL!”Unele echipe de dezvoltatori care nu au avut încă probleme inevitabile de a muri baze de date ar putea utiliza calcule în interogare pentru diferențele de număr între date sau puncte de date similare.

regula generală pentru interogările SQL este următoarea:

fii precis și generează doar rezultatele de care ai nevoie.

să verificăm o interogare puțin mai complicată…

SELECT site_options.domain, sites_users.user, site_taxes.monthly_statement_fee, site.name, AVG(price) AS average_product_price FROM sites_orders_products, site_taxes, site, sites_users, site_options WHERE site_options.site_id = site.id AND sites_users.id = site.user_id AND site_taxes.site_id = site.id AND sites_orders_products.site_id = site.id GROUP BY site.id ORDER BY site.date_modified desc LIMIT 5;+-----------------------------+-----------------------------+-----------------------+------------------------------------------+-----------------------+| domain | user | monthly_statement_fee | name | average_product_price |+-----------------------------+-----------------------------+-----------------------+------------------------------------------+-----------------------+| www.xxxxxxxxxxxxxxxxxxx.com | [email protected] | 0.50 | xxxxxxxxxxxxxxxxxxxxx | 3.254781 || www.xxxxxxxxxxx.com | [email protected] | 0.50 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | 9.471022 || | [email protected] | 0.00 | xxxxxxxxxxxxxxxxx | 8.646297 || | [email protected] | 0.00 | xxxxxxxxxxxxxxx | 9.042460 || | [email protected] | 0.00 | xxxxxxxxxxxxxxxxxx | 6.679182 |+-----------------------------+-----------------------------+-----------------------+------------------------------------------+-----------------------+5 rows in set (0.00 sec)

…și ieșirea saEXPLAIN.

+------+-------------+---------------------------------+--------+-----------------+---------------+---------+---------------------------------+------+-----------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+------+-------------+---------------------------------+--------+-----------------+---------------+---------+---------------------------------+------+-----------+| 1 | SIMPLE | sites | index | PRIMARY,user_id | PRIMARY | 4 | NULL | 858 | Using temporary; Using filesort || 1 | SIMPLE | sites_options | ref | site_id | site_id | 4 | service.sites.id | 1 | || 1 | SIMPLE | sites_taxes | ref | site_id | site_id | 4 | service.sites.id | 1 | || 1 | SIMPLE | sites_users | eq_ref | PRIMARY | PRIMARY | 4 | service.sites.user_id | 1 | || 1 | SIMPLE | sites_orders_products | ref | site_id | site_id | 4 | service.sites.id | 4153 | |//+------+-------------+---------------------------------+--------+-----------------+---------------+---------+---------------------------------+------+-----------+5 rows in set (0.00 sec)

coloanele din EXPLAIN ieșire cu cele care au nevoie de o atenție specială pentru identificarea problemelor cu caractere aldine sunt:

• id (interogare id)
• select_type (tip de declarație)
• table (tabel de referință)
• type (join type)
• possible_keys (care chei ar fi putut fi utilizate)
• key (cheie care a fost utilizat)
• key_len (lungimea cheii utilizate)
• ref (coloane în comparație cu index)
• li> rânduri (cantitatea de rânduri căutate)
• extra (informații suplimentare)

cu cât este mai mare cantitatea de rânduri căutate, cu atât trebuie să fie mai bun nivelul de optimizare în ceea ce privește indicii și precizia interogării pentru a maximiza performanța. Coloana Extra arată acțiunile posibile pe care v-ați putea concentra pentru a vă îmbunătăți interogarea, dacă este cazul.

Afișați avertismente;

dacă interogarea pe care ați folosit-o cuEXPLAIN nu analizează corect, puteți introduceSHOW WARNINGS; în Editorul de interogări MySQL pentru a afișa informații despre ultima instrucțiune care a fost rulată și nu a fost diagnosticată, adică nu va afișa informații pentru instrucțiuni precumSHOW FULL PROCESSLIST;. Deși nu poate oferi un plan adecvat de execuție a interogării, cum ar fiEXPLAIN, vă poate oferi indicii despre acele fragmente de interogare pe care le-ar putea procesa. Să presupunem că folosim interogarea EXPLAIN SELECT * FROM foo WHERE foo.bar = 'infrastructure as a service' OR foo.bar = 'iaas'; pe orice bază de date Dată care nu are de fapt un tabel foo. Ieșirea MySQL ar fi:

ERROR 1146 (42S02): Table 'db.foo' doesn't exist

dacă tastămSHOW WARNINGS; ieșirea este după cum urmează:

+-------+------+-------------------------------------+| Level | Code | Message |+-------+------+-------------------------------------+| Error | 1146 | Table 'db.foo' doesn't exist |+-------+------+-------------------------------------+1 row in set (0.00 sec)

să încercăm acest lucru cu o eroare de sintaxă deliberată.

EXPLAIN SELECT * FROM foo WHERE name = ///;

aceasta generează următoarele avertismente:

> SHOW WARNINGS;+-------+------+---------------------------------------------------------------------+| Level | Code | Message |+-------+------+---------------------------------------------------------------------+| Error | 1064 | You have an error in your SQL syntax; (...) near '///' at line 1 |+-------+------+---------------------------------------------------------------------+

această ieșire de avertismente este destul de simplă și prezentată de MySQL ca rezultat imediat, dar pentru interogări mai complicate care nu analizează, este încă posibil să aruncăm o privire la ceea ce se întâmplă în acele fragmente de interogare care pot fi analizate. SHOW WARNINGS; include markeri speciali care pot furniza informații utile, cum ar fi:

• <index_lookup>(query fragment): o căutare de index ar avea loc dacă interogarea ar fi fost analizată corect
• <if>(condition, expr1, expr2): o condiție if apare în această parte specifică a interogării
• <primary_index_lookup>(query fragment): o căutare index ar avea loc prin cheia primară
• <temporary table>: aici ar fi creat un tabel intern pentru salvarea rezultatelor temporare, de exemplu în Subinterogări înainte de a se alătura

pentru a afla mai multe extins explica formatul de ieșire în documentația oficială MySQL.

remedierea pe termen lung

există mai multe modalități de a remedia cauza principală a performanței proaste a bazei de date. Primul punct de analizat este modelul de date, adică. cum sunt structurate datele și utilizați baza de date potrivită? Pentru multe produse, o bază de date SQL este bine. Un lucru important de reținut este să separați întotdeauna jurnalele de acces de baza de date obișnuită de producție, ceea ce, din păcate, nu se întâmplă în multe companii. Mai ales în aceste cazuri, o companie a început mici, a crescut mai mare, și, în esență, încă folosește aceeași bază de date, ceea ce înseamnă că accesează aceeași bază de date atât pentru funcționalitatea de logare, precum și alte tranzacții. Acest lucru reduce semnificativ performanța generală, mai ales pe măsură ce compania crește. Prin urmare, este foarte important să se creeze un model de date care să se potrivească și să fie durabil.

model de date

alegerea unui model de date va dezvălui cel mai probabil și forma corectă a bazei de date. Cu excepția cazului în care produsul dvs. este foarte simplu, probabil că veți avea mai multe baze de date pentru mai multe cazuri de utilizare – dacă trebuie să afișați numere în timp real pentru jurnalele de acces, cel mai probabil veți dori un depozit de date extrem de performant, în timp ce tranzacțiile regulate s-ar putea întâmpla printr-o bază de date SQL și este posibil să aveți o bază de date grafic care acumulează punctele de date relevante ale ambelor baze de date într-un motor de recomandare.

arhitectura software a produsului global este la fel de importantă ca baza de date în sine, deoarece designul rău aici va duce la blocaje care merg spre baza de date și încetinesc totul atât din partea software-ului, cât și din ceea ce poate produce baza de date. Va trebui să alegeți dacă containerele sunt potrivite pentru produsul dvs., dacă un monolit este cea mai bună modalitate de a gestiona lucrurile, dacă doriți să aveți un monolit de bază cu mai multe microservicii care vizează alte funcționalități răspândite în altă parte și modul în care accesați, colectați, procesați și stocați date.

Hardware

la fel de important ca structura generală, hardware-ul este o componentă cheie în performanța bazei de date. Exoscale vă oferă diferite opțiuni de instanță pe care le puteți utiliza în funcție de tranzacție și volumul de stocare, precum și timpul de răspuns dorit.

este esențial să determinați perioadele de vârf ale aplicației dvs. și, prin urmare, să știți când să omiteți interogările administrative mai lente, dacă este posibil. Disc I / O și statisticile de rețea trebuie să fie luate în considerare, de asemenea, atunci când proiectați calendarul tranzacțiilor de baze de date și de analiză.

rezumat

În concluzie, aici sunt principalele puncte de performanță pe termen lung rezumate:

• creați un model de date durabil care se potrivește nevoilor companiei dvs.
• alegeți forma corectă a bazei de date
• utilizați o arhitectură software care se potrivește produsului dvs.
• treceți prin iterații regulate de a privi structura interogărilor dvs. și utilizațiEXPLAIN pe cele mai complicate, optimizați utilizarea bazei de date alese, de asemenea, în ceea ce privește actualizările bazei de date și modul în care acestea>
• alegeți instanțele care se potrivesc cel mai bine nevoilor aplicației și bazei de date în conformitate cu performanța și lățimea de bandă