Kako koristiti EXPLAIN u MySQL-u za razumijevanje i ubrzavanje upita

Početna » piton » Kako koristiti EXPLAIN u MySQL-u za razumijevanje i ubrzavanje upita

Ako dovoljno dugo radite s MySQL-om, prije ili kasnije ćete naići na spor upit koji će vam uništiti dan. Možda izvještaj koji se nikad ne završi, API krajnja tačka koja ističe prilikom opterećenja ili kontrolna tabla kojoj je iznenada potrebno 20 sekundi da se učitava. Kada se to dogodi, prvi pravi alat za otklanjanje grešaka koji biste trebali koristiti je EXPLAIN porodica komandi.

MySQL-ove EXPLAIN i EXPLAIN ANALYZE pokazuju kako optimizator planira i zapravo izvršava upit. Kada se dobro koriste, oni otkrivaju koje se tabele skeniraju, koji se indeksi koriste (ili ignorišu), kako se izvode spajanja, koliko se redova pregleda i gdje se zaista troši vrijeme. U ovom vodiču ćemo, jednostavnim jezikom, objasniti kako ih koristiti, kako čitati njihov izlaz i kako taj uvid pretvoriti u konkretna poboljšanja performansi.

Šta EXPLAIN radi u MySQL-u (i kada ga koristiti)

U MySQL-u, EXPLAIN Ključna riječ je dijagnostički alat koji prikazuje planiranu strategiju izvršavanja naredbe umjesto njenog normalnog izvršavanja. Vaše mjesto EXPLAIN ispred vašeg upita, a MySQL odgovara malom tabelom koja opisuje kako namjerava dobiti tražene redove.

Možete koristiti OBJAŠNJENJE sa SELECT, INSERT, UPDATE, DELETE i REPLACE naredbe, ne samo SELECT. Na primjer:

Primjer:

Primjer upita: EXPLAIN SELECT * FROM employees WHERE last_name = 'Puppo' AND first_name = 'Kendra';

Umjesto vraćanja podataka o zaposlenima, MySQL vraća objašnjenje red po red kako bi izvršio tu naredbu. Vidjet ćete koja se tabela čita, koji je indeks odabran, koliko redova MySQL očekuje da pregleda i dodatne napomene poput toga da li je potrebna privremena tabela ili sortiranje datoteka.

Koristite EXPLAIN kad god sumnjate da je upit sporiji nego što bi trebao biti ili kada dizajnirate indekse i želite provjeriti da li će ih MySQL zapravo koristiti. Također je izuzetno korisno kada nasljeđujete složeni SQL s više podupita i spajanja i morate rekonstruirati ono što optimizator radi.

Varijacije naredbe OBJAŠNJENJE: PROŠIRENO, PARTICIJE, ANALIZA i formati

Osnovni EXPLAIN je samo početna tačka; MySQL nudi nekoliko proširenja i izlaznih formata koji daju bolji uvid. Razumijevanje ovih opcija pomaže vam da odaberete pravi nivo detalja za svaku situaciju.

EXPLAIN EXTENDED dodaje više informacija o optimizatoru, posebno filtered kolona i prepisani tekst upita. Nakon trčanja EXPLAIN EXTENDED ... možete izvršiti SHOW WARNINGS; da vidite kako je optimizator interno prepisao vaš upit, što je vrlo korisno za razumijevanje optimizacijskih odluka.

EXPLAIN PARTITIONS prikazuje kojim particijama particionirane tabele će upit pristupiti. The partitions Kolona navodi relevantne particije, što vam pomaže da provjerite da li se orezivanje particija zaista dešava i da li slučajno pogađate svaku particiju.

Izlazni format je također fleksibilan: možete dobiti tabelarni, tabulatorima odvojen, vertikalni ili JSON izlaz u CLI-ju, a vizualne planove u alatima poput MySQL Workbencha. JSON izlaz je posebno koristan za automatizaciju i dublju analizu, jer uključuje procjene troškova i ugniježđenu strukturu plana, ali imajte na umu da običan EXPLAIN ANALYZE MySQL trenutno ne podržava sirovi JSON izlaz.

Vizualni alati poput MySQL Workbencha mogu prikazati EXPLAIN kao grafičko stablo, što je često lakše razumjeti za složene spojeve i podupite. Vanjski alati poput Percona Toolkit, EverSQL ili Releem mogu potrošiti EXPLAIN izlaz ili usporiti zapise upita i pomoći vam da odredite koje naredbe prvo optimizirati.

Razumijevanje EXPLAIN kolona (format tabele)

Kada pokrenete klasični EXPLAIN bez JSON formata, MySQL vraća jedan red po tabeli ili podupitu uključenom u vašu naredbu. Redoslijed ovih redova je važan: on pokazuje redoslijed kojim se pristupa tabelama tokom izvršavanja.

Ovo su ključne kolone koje ćete vidjeti i šta vam one govore:

• id: Sekvencijalni identifikator za svaki dio upita. Jedan jednostavan SELECT obično ima id = 1Višestruki ID-ovi označavaju podupite, izvedene tabele ili UNION dijelove. Veći brojevi se obično izvršavaju prije nižih, dajući vam naznaku redoslijeda izvršavanja.
• select_type: Opisuje ulogu tog SELECT-a unutar cjelokupnog upita, s vrijednostima kao što su SIMPLE (bez podupita ili UNION-ova), PRIMARY (najudaljeniji SELECT u složenom upitu), UNION, UNION RESULT, DERIVED (podupit u FROM) ili tipovi povezani s punim tekstom. Ovo vam omogućava da na prvi pogled vidite da li imate posla s ugniježđenim upitima, unijama ili izvedenim tabelama.
• table: Označava na koju tabelu ili interni rezultat se ovaj red odnosi. Može biti pravo ime tabele ili pseudoime poput <unionM,N> za interni rezultat UNIJE, <derivedN> za izvedenu tabelu ili slične markere za materijalizovane podupite.
• partitions: Kada se particionirane tabele koriste zajedno sa EXPLAIN PARTITIONS, ovo navodi particije koje sadrže redove koji odgovaraju uslovima upita. Ako vidite mnogo particija navedenih, možda nemate koristi od skraćivanja particija.
• type: Često nazivan tipom spajanja ili pristupa, ovo je jedan od najvažnijih pokazatelja performansi. Opisuje kako MySQL pristupa redovima: vrijednosti se kreću od vrlo efikasnih (kao const, eq_ref, ref) do manje efikasnog (range) do lošeg (index, ALL skeniranje cijele tabele). Posebni tipovi kao što su index_merge, unique_subquery, I index_subquery naznačiti specifične optimizacije.
• possible_keys: Navodi indekse koje MySQL smatra da bi se mogli koristiti za ovaj dio upita. Ako je ovo NULL, to znači da MySQL ne vidi koristan indeks, što je snažan signal da ga možda trebate kreirati na osnovu vaših WHERE ili JOIN uslova.
• key: Prikazuje stvarni indeks koji je optimizator odabrao za ovaj pristup. Ako je NULL dok possible_keys lista kandidata, optimizator je odlučio da se korištenje indeksa ne isplati, često zbog niske selektivnosti ili male veličine tabele.
• key_len: Prikazuje broj bajtova indeksa koji se koriste. Za kompozitne indekse, ovo pokazuje koliko je vodećih kolona efektivno u igri. Ovo je ključno za razumijevanje da li se vaš indeks s više kolona koristi u potpunosti ili samo djelimično.
• ref: Označava šta se poredi sa indeksnim kolonama navedenim u key: može pokazivati ​​na drugu kolonu tabele (za spajanja) ili na konstantu (za jednostavne filtere).
• rows: Procjena koliko redova MySQL očekuje da ispita za ovaj korak. Približna je, zasnovana na statistici, ali vrlo korisna za procjenu koliko će upit biti skup i da li indeks efikasno smanjuje prostor za pretragu.
• filtered: Dostupno sa EXPLAIN EXTENDED, ovaj postotak predstavlja koliko se od pregledanih redova očekuje da zadovoljavaju uslove na toj tabeli. Niski postoci u kombinaciji s visokim rows često vape za boljim indeksiranjem ili selektivnijim uslovima.
• Extra: Polje slobodnog oblika koje sadrži dodatne bilješke koje ne odgovaraju nigdje drugdje, kao što je Using index, Using where, Using temporary, Using filesort, savjeti za puni tekst i još mnogo toga, a sve to sadrži važne naznake o performansama.

Čitanjem ovih kolona zajedno umjesto odvojeno, dobijate sažet, ali snažan sažetak o tome kako MySQL namjerava da zadovolji vaš upit. Uz malo vježbe, brzo ćete uočiti znakove upozorenja poput skeniranja punih tabela, nedostajućih indeksa ili nepotrebnih privremenih tabela.

Kako EXPLAIN definira tipove pristupa ( type kolona)

The type Kolona zaslužuje posebnu pažnju jer je jedan od najbržih načina za procjenu stanja upita. Iako se u dokumentaciji naziva tipom spajanja, tačnije je smatrati ga tipom pristupa koji opisuje kako se redovi pronalaze.

Na opštem nivou, tipovi pristupa se kreću od „odličnog“ do „užasnog“ u smislu performansi. Iako je tačna lista duga, neke važne uključuju:

• const / system: MySQL može razriješiti tabelu u najviše jedan red putem primarnog ključa ili jedinstvenog indeksa s konstantnom vrijednošću. Ovo je izuzetno efikasno.
• eq_ref: Za svaki red iz prethodne tabele, MySQL čita tačno jedan odgovarajući red iz ove tabele, obično zbog jedinstvene ili primarne reference ključa u spajanju.
• ref: MySQL pristupa redovima putem nejedinstvenog indeksa koji odgovara konstanti ili koloni; više redova se može podudarati. I dalje je generalno dobro.
• range: MySQL koristi indeks za preuzimanje redova u datom rasponu vrijednosti (npr. BETWEEN, >=ili prefiks koji se podudara sa LIKE 'abc%'). Ovo je često prihvatljivo, posebno za datumske ili numeričke raspone.
• index: MySQL skenira cijeli indeks umjesto svih podataka tabele. Bolje od skeniranja cijele tabele, ali i dalje često preskupo na velikim indeksima.
• ALL: Potpuno skeniranje tabele. MySQL čita svaki red kako bi pronašao podudaranja. Na malim tabelama ovo može biti u redu, ali na velikim tabelama biste uglavnom trebali razmotriti indeksiranje ili prepisivanje upita kako biste to izbjegli.
• index_merge: Označava da MySQL kombinuje rezultate iz više indeksa na istoj tabeli. Zvuči lijepo, ali u praksi često daje lošije rezultate od dobrog jednog kompozitnog indeksa.
• unique_subquery / index_subquery: Posebni tipovi pristupa koji se koriste za optimizaciju određenih IN (SELECT ...) podupiti putem pretraga jedinstvenosti ili nejedinstvenih indeksa, zamjenjujući manje efikasne obrasce pretrage.

Kada pregledate izlaz funkcije EXPLAIN, obično želite da tipovi pristupa budu što bliži const / eq_ref / ref što je više moguće i izbjegavati ALL na velikim stolovima. Ako vidite ALL sa visokom rows procjenjuje se da je gotovo uvijek kandidat za bolje indeksiranje.

Ključ, mogući_ključevi i dužina_ključa: da li vaši indeksi zaista pomažu?

Indeksi su često najmoćniji način za ubrzavanje upita, a EXPLAIN vam pomaže da shvatite kako se tačno koriste. Tri kolone su posebno važne za dijagnostiku indeksa: possible_keys, key, I key_len.

possible_keys navodi sve indekse za koje MySQL smatra da bi mogli biti relevantni za tu tabelu na osnovu uslova WHERE i JOIN. Ako je tako NULL Za tabelu koja učestvuje u intenzivnom filteru ili spajanju, to je snažan znak da biste trebali razmotriti dodavanje indeksa koji pokriva relevantne kolone.

key govori vam koji je indeks MySQL zapravo odabrao od kandidata. If key is NULL dok postoje unosi u possible_keys, optimizator je odlučio da je potpuno skeniranje jeftinije, što obično ukazuje na lošu selektivnost indeksa, zastarjelu statistiku ili obrazac upita koji ne može imati koristi od dostupnog dizajna indeksa.

key_len prikazuje koliko bajtova odabranog indeksa je korišteno. Za kompozitne indekse, ovo vam omogućava da potvrdite koliko indeksiranih kolona doprinosi pretrazi. Ako ste izgradili indeks na (last_name, first_name) i key_len pokriva samo dužinu za last_name, znate da upiti ne koriste u potpunosti kompozitni indeks.

The ref Kolona dopunjuje ovo tako što vam govori šta MySQL poredi sa indeksom, bilo da je to konstanta (kao 'Puppo') ili kolonu iz spojene tabele. Ako otklanjate greške prilikom spajanja, vidjeti ispravnu kolonu spajanja u ref zajedno s dobrim type vrijednost kao što je eq_ref je znak da je vaš spoj dobro indeksiran.

Redovi, filtrirani i dodatni: uočavanje skrivenih neefikasnosti

The rows i filtered kolone daju okvirnu predstavu o obimu posla, dok Extra ističe posebne operacije koje često objašnjavaju probleme s performansama. Ova tri bi uvijek trebala biti pregledana zajedno.

rows je MySQL-ova procjena broja zapisa koje mora ispitati u ovom koraku. Nije uvijek tačno, ali je dovoljno dobro da otkrije očigledno loše slučajeve, kao što je skeniranje stotina hiljada redova kada ste očekivali samo nekoliko.

filtered (dostupno putem EXPLAIN EXTENDED) daje procijenjeni postotak pregledanih redova koji će zadovoljiti uvjete za tu tabelu. Visok rows sa vrlo niskim filtered Procenat obično ukazuje na nedostajuće ili neoptimalne indekse ili loše selektivne predikate.

The Extra Kolona agregira dodatne bilješke o izvršenju koje ne odgovaraju nigdje drugdje. Neke od najvažnijih vrijednosti s kojima biste mogli susresti uključuju:

• Using where: Uslov WHERE se primjenjuje za filtriranje redova za ovu tabelu.
• Using index: MySQL može poslužiti sve potrebne kolone samo iz indeksa bez dodirivanja podataka iz tabele, što je poznato kao pokrivajući indeks i generalno je vrlo efikasno.
• Using temporary: MySQL kreira internu privremenu tabelu za čuvanje međurezultata, na primjer za složenu GROUP BY ili obradu podupita. Na velikim skupovima podataka ovo može ozbiljno uticati na performanse.
• Using filesort: MySQL izvodi zaseban korak sortiranja, često za ORDER BY ili GROUP BY, što se može obaviti u memoriji ili na disku i obično je skuplje od korištenja sortiranja zasnovanog na indeksu.
• Bilješke vezane za puni tekst: Prilikom korištenja FULLTEXT indeksa, EXPLAIN može otkriti da li se pretraga cijelog teksta ispravno kombinuje sa spajanjima ili drugim filterima.

Obratite posebnu pažnju na Using temporary i Using filesort in Extra kada su upiti spori, jer oboje može ukazivati ​​na obimno sortiranje ili grupiranje koje bi se moglo preusmjeriti na bolje indekse ili restrukturirane upite.

OBJAŠNJENJE ANALIZA: uvid u stvarne troškove izvršenja

Počevši od MySQL 8.0.18, dobijate još moćniji alat: EXPLAIN ANALYZE, koji zapravo pokreće upit i prilaže statistiku izvođenja planu. Ovo premošćuje jaz između teorijskih procjena troškova i stvarnih performansi.

Za razliku od obične EXPLAIN funkcije, koja samo predviđa, EXPLAIN ANALYZE izvršava naredbu i mjeri koliko dugo traje svaki iterator (korak) u planu, koliko redova vraća i koliko petlji izvršava. Sintaksa je jednostavna:

Run: EXPLAIN ANALYZE SELECT * FROM table_name WHERE condition;

Kada pokrenete EXPLAIN ANALYZE, MySQL koristi FORMAT=tree automatski i generira plan u obliku stabla koji kombinira procijenjene i stvarne metrike. Za svaki čvor možete vidjeti:

• Procijenjeni trošak izvršenja: Model optimizatora koji pokazuje koliko bi ovaj korak trebao biti skup.
• Procijenjeni broj redova: Koliko se redova očekivalo da će biti vraćeno iz ovog iteratora.
• Stvarno vrijeme do prvog reda: Koliko je vremena trebalo da se proizvede prvi red.
• Stvarno vrijeme provedeno po petlji: Prosječno vrijeme u milisekundama potrebno za izvršavanje iteratora, uključujući njegovu djecu, ali ne i roditelja, kroz petlje.
• Stvarni redovi i petlje: Koliko je redova zaista vraćeno i koliko puta se iterator pokrenuo.

Možete koristiti EXPLAIN ANALYZE sa SELECT, višetabelarnim UPDATE, DELETE i TABLE naredbama. Posebno je korisno kada normalni EXPLAIN sugerira jednu stvar, ali se upit i dalje ponaša drugačije u produkciji, jer sada možete uporediti procijenjeni i stvarni broj redova i troškove.

Velika odstupanja između procjena i stvarnih vrijednosti otkrivaju gdje su statistike optimizatora pogrešne ili gdje složeni predikati (uključujući funkcije, UDF-ove ili pohranjene rutine) čine predviđanje troškova netačnim. Ta mjesta su glavne mete za prilagođavanje sheme, nove indekse ili prepisivanje upita.

Klasično OBJAŠNJENJE vs. OBJAŠNJENJE ANALIZA: prednosti i ograničenja

Iako su EXPLAIN i EXPLAIN ANALYZE nevjerovatno korisni, važno je razumjeti njihove nedostatke kako ne biste previše interpretirali rezultat.

Regularni EXPLAIN je u suštini aproksimacija onoga što optimizator misli da će se dogoditi. Neke od njegovih statistika su grube procjene, a određene interne optimizacije uopće nisu prikazane u izlazu. Na primjer, ne govori vam kako će okidači, pohranjene funkcije ili korisnički definirane funkcije utjecati na vrijeme procesora. Također ne pokazuje šta se događa unutar pohranjenih procedura.

Neke oznake u izlazu su previše pojednostavljene od onoga što se zaista dešava. Na primer, type Kolona se naziva tipom spajanja, iako zapravo predstavlja tip pristupa. Extra vrijednost Using temporary ne pravi razliku između privremenih tabela u memoriji i onih na disku, i Using filesort obuhvata i memorijske i diskovne vrste pod istom oznakom.

EXPLAINE ANALYZE rješava neke od ovih problema prikazivanjem stvarnog vremena i broja redova, ali ima i nedostatke. Mora izvršiti upit, što može biti skupo ili čak opasno u produkciji ako upit zahtijeva puno resursa ili mijenja podatke. Također, sirovi JSON izlaz trenutno nije direktno dostupan za EXPLAIN ANALYZE, tako da ćete uglavnom raditi s formatom stabla u CLI-ju ili klijentskim alatima.

Ispravan pristup je korištenje funkcije EXPLAIN za brz uvid u plan s malim utjecajem, a EXPLAIN ANALYZE za dublja istraživanja gdje si možete priuštiti da zapravo pokrenete upit.

Korištenje EXPLAIN i EXPLAIN ANALYZE za optimizaciju stvarnih upita

Čitanje naredbe EXPLAIN je korisno samo ako vodi do boljih upita; ključno je prevesti ono što vidite u specifične promjene indeksa i SQL-a. Prođimo kroz neke uobičajene scenarije optimizacije koje možete direktno riješiti uz pomoć EXPLAIN-a.

Zamislite jednostavan upit na employees tabela bez ikakvih relevantnih indeksa:

Primjer filtera: EXPLAIN SELECT * FROM employees WHERE last_name = 'Puppo' AND first_name = 'Kendra';

Bez indeksa, EXPLAIN će vjerovatno prikazati type = ALL i veoma veliku vrijednost u rows (na primjer, oko 299,000 redova) što ukazuje na potpuno skeniranje tabele. To je jasan signal da vam je potreban indeks koji podržava oba uslova.

Jedan naivan pristup je kreiranje dva odvojena indeksa, jednog na last_name i jedan na first_name, ali to i dalje ne čini kombinovanu pretragu efikasnom. MySQL može brzo pronaći sve osobe sa prezimenom 'Puppo' ili sve osobe sa imenom 'Kendra', ali identifikacija tačnog reda 'Kendra Puppo' zahtijeva presijecanje tih skupova, što nije tako efikasno kao što biste možda očekivali.

Bolje rješenje je indeks s više kolona koji prati vaš najselektivniji i najčešće korišteni obrazac pretraživanja:

Kreiranje indeksa: CREATE INDEX fullnames ON employees(last_name, first_name);

Ponovnim pokretanjem EXPLAIN komande sada, trebali biste vidjeti da se ovaj indeks koristi, type poboljšava se do nečega poput ref ili čak const ovisno o jedinstvenosti i rows pada na 1. Ovo potvrđuje da je potrebno dodirnuti samo jedan red, što objašnjava ogromno poboljšanje performansi.

Sličan obrazac se pojavljuje i kod optimizacije spajanja. Pretpostavimo da imate dvije tabele A i B sa zajedničkom kolonom X koja se koristi za spajanje, i da ih inicijalno kreirate bez indeksa:

Šema: CREATE TABLE A ( X VARCHAR(10), Y VARCHAR(10));
CREATE TABLE B ( X VARCHAR(10), Z VARCHAR(10));

Pokretanje spajanja s filterom na AX-u može prikazati potpuna skeniranja i na A i na B, svaki s oko 10,000 pregledanih redova, jer mehanizam nema bolju opciju nego da izvrši grubu prisilnu usporedbu redova:

Upit za pridruživanje: EXPLAIN SELECT *
FROM A INNER JOIN B ON A.X = B.X
WHERE A.X = '100';

Nakon što dodate odgovarajuće indekse i ograničenja stranog ključa, EXPLAIN odražava poboljšanu strategiju. Na primjer:

Indeks i FK: CREATE UNIQUE INDEX a_unique_index ON A(X);
CREATE UNIQUE INDEX b_unique_index ON B(X);
ALTER TABLE B ADD CONSTRAINT fk_b_x FOREIGN KEY (X) REFERENCES A(X);

Nakon ovih promjena, EXPLAIN će obično prikazivati type poboljšanje (npr. eq_ref na spojenoj tabli), kao i rows padajući sa hiljada na oko 1, što dokazuje da spajanje sada koristi efikasne pretrage indeksa umjesto skeniranja svega.

Upoređivanje uzoraka, sortiranje i druge uobičajene zamke

EXPLAIN je posebno vrijedan kada se radi sa obrascima, sortiranjem i grupisanjem, gdje naizgled bezopasan SQL može potpuno onemogućiti indeksiranje. Rano prepoznavanje ovih obrazaca štedi vam mnogo nagađanja.

Jedan klasičan problem je upoređivanje uzoraka s vodećim džokerima. Na primjer, upit sa WHERE email LIKE '%yahoo.com' onemogućava standardni indeks na email da pomogne, jer MySQL nema načina da direktno pređe na redove koji završavaju tim sufiksom. EXPLAIN će obično prikazati type = ALL i velika rows brojati.

Završni džoker znakovi, kao što su LIKE 'john%', su prilagođeni indeksiranju, jer pretraživač može koristiti indeks za pronalaženje raspona redova koji počinju sa 'john'. OBJAŠNJENJE će ovo potvrditi prikazivanjem dobre vrste pristupa i malog rows procjena na indeksiranoj koloni.

Sortiranje i grupiranje su još jedna važna tačka performansi. Ako se prikaže OBJAŠNJENJE Using filesort in Extra zajedno s velikim rows procjena, vaša ORDER BY ili GROUP BY vjerovatno forsira eksplicitni korak sortiranja.

Primjer sortiranja: EXPLAIN SELECT sale_id FROM sales ORDER BY sale_date;

Dodavanje indeksa na sale_date omogućava MySQL-u da direktno čita redove u sortiranom redoslijedu, što EXPLAIN obično odražava uklanjanjem Using filesort i eventualno promjenu tipa pristupa na index ili bolje.

Istovremeno, pazite da ne pretjerate s indeksiranjem. EXPLAIN vam može pokazati koji se indeksi pojavljuju u possible_keys ali se nikada ne koriste kao key bilo kojim važnim upitom. To bi mogli biti kandidati za uklanjanje, ali prvo morate provjeriti ukupno opterećenje; samo zato što jedan upit ignoriše indeks ne znači da se drugi kritični upiti ne oslanjaju na njega.

PRIKAŽI UPOZORENJA i markere proširenog objašnjenja

Čak i kada se EXPLAINed upit ne parsira ispravno, i dalje možete izvući neke uvide koristeći SHOW WARNINGS;. Ova naredba prikazuje informacije o posljednjoj nedijagnostičkoj naredbi i može vam dati djelomične naznake o tome kako je MySQL interpretirao fragmente vašeg upita.

Na primjer, ako pokrenete EXPLAIN na upitu koji referencira nepostojeću tabelu ili sadrži sintaktičku grešku, EXPLAIN može propasti, ali SHOW WARNINGS i dalje može otkriti interne markere koji pokazuju kako će se dijelovi upita tretirati. Možda ćete vidjeti oznake kao što su:

• <index_lookup>(query fragment): sugerira da bi se za taj fragment izvršila pretraga indeksa ako bi upit bio valjan.
• <primary_index_lookup>(query fragment): označava da će se izvršiti pretraga na osnovu primarnog ključa.
• <if>(condition, expr1, expr2): ukazuje na uslovnu evaluaciju u tom dijelu upita.
• <temporary table>: označava kreiranje interne privremene tabele za međurezultate, na primjer prije spajanja.

Kombinacija naredbi EXPLAIN EXTENDED i SHOW WARNINGS je moćan način da se vidi ne samo plan već i prepisani upit optimizatora, koji ponekad otkriva pojednostavljenja, potiskivanje predikata ili druge transformacije koje možda ne biste očekivali.

Više od EXPLAIN-a: model podataka, arhitektura i alati

Iako je EXPLAIN fantastičan mikroskop za pojedinačne upite, održive performanse također ovise o vašem cjelokupnom modelu podataka, arhitekturi sistema i hardveru. Ne možete popraviti fundamentalno lošu shemu ili preopterećenu instancu samo podešavanjem where klauzula.

Dobra početna tačka je model podataka koji odvaja vrlo različita radna opterećenja i Obrada JSON-a u SQL-u. Na primjer, miješanje logova pristupa velikog obima s transakcijskim podacima u istoj MySQL instanci recept je za dugoročne probleme: kako kompanija raste, promet evidentiranja može preopteretiti I/O i degradirati kritične poslovne upite. Podjela logova u namjensku pohranu ili skladište podataka često je skalabilnija.

Arhitektura softvera također igra važnu ulogu. Odluke o monolitnim naspram mikroservisa, skladišta podataka i specijaliziranih trgovina (poput grafičkih baza podataka za preporuke) utjecat će na vrste upita koje pokrećete i gdje. Ako servisu treba analitika gotovo u stvarnom vremenu na ogromnim skupovima podataka, standardna OLTP MySQL instanca možda nije najbolja meta za te upite.

Hardver i veličina instance su također važni. Disk I/O, memorija, CPU i mrežni protok utiču na latenciju upita. Identifikacija perioda vršne upotrebe i zakazivanje teških analitičkih ili administrativnih upita izvan tih prozora može zaštititi performanse usmjerene na korisnike. Sa upravljanim DBaaS ponudama, često možete skalirati instance ili preći na klase sa boljim performansama po potrebi.

Konačno, dopunite EXPLAIN alatima za kontinuirano praćenje i profiliranje. MySQL-ova vlastita shema performansi i zapis sporih upita, MySQL Workbench, Percona Toolkit pt-query-digest, web alati poput EverSQL-a i platforme poput Releema mogu automatski istaknuti vaše najgore prestupnike, pa čak i predložiti optimizacije. EXPLAIN je tada vaš hirurški alat za te specifične upite.

Dodavanje EXPLAIN i EXPLAIN ANALYZE u vaš set alata vam omogućava da prestanete nagađati o sporim upitima i počnete razmišljati na osnovu konkretnih dokaza. Razumijevanjem ID-ova, tipova odabira, metoda pristupa, korištenja indeksa, procjena redova, filtera i dodatnih zastavica, možete metodično ukloniti skeniranje cijelog niza tabela, ukrotiti skupe spojeve, dizajnirati pametnije indekse i izbjeći nepotrebna sortiranja i privremene tabele, a sve to uz praćenje šire slike dizajna sheme i infrastrukture kako bi vaša MySQL radna opterećenja ostala brza i predvidljiva kako rastu.