Kuinka käyttää EXPLAIN-käskyä MySQL:ssä kyselyiden ymmärtämiseen ja nopeuttamiseen

Etusivu » Python » Kuinka käyttää EXPLAIN-käskyä MySQL:ssä kyselyiden ymmärtämiseen ja nopeuttamiseen

Jos työskentelet MySQL:n kanssa tarpeeksi kauan, ennemmin tai myöhemmin törmäät hitaaseen kyselyyn, joka pilaa päiväsi. Ehkä raportti, joka ei koskaan valmistu, API-päätepiste, joka aikakatkeaa kuormituksen aikana, tai kojelauta, jonka lataaminen kestää yhtäkkiä 20 sekuntia. Kun näin tapahtuu, ensimmäinen oikea virheenkorjaustyökalu, johon kannattaa tarttua, on EXPLAIN komentojen perhe.

MySQL:n EXPLAIN- ja EXPLAIN ANALYZE-funktiot näyttävät, miten optimoija suunnittelee ja tosiasiallisesti suorittaa kyselyn. Oikein käytettynä ne paljastavat, mitkä taulukot skannataan, mitä indeksejä käytetään (tai jätetään huomiotta), miten liitokset suoritetaan, kuinka monta riviä tutkitaan ja mihin aikaa todellisuudessa käytetään. Tässä oppaassa käymme läpi selkeästi, miten niitä käytetään, miten niiden tulostetta luetaan ja miten tämä tieto muunnetaan konkreettisiksi suorituskyvyn parannuksiksi.

Mitä EXPLAIN tekee MySQL:ssä (ja milloin sitä käytetään)

MySQL:ssä EXPLAIN avainsana on diagnostiikkatyökalu, joka näyttää lausekkeen suunnitellun suoritusstrategian sen normaalin suorittamisen sijaan. Sinun paikkasi EXPLAIN kyselysi eteen, ja MySQL vastaa pienellä taulukolla, jossa kuvataan, miten se aikoo hakea pyydetyt rivit.

Voit käyttää EXPLAIN-komentoa SELECT, INSERT, UPDATE, DELETE ja REPLACE lauseita, ei pelkästään SELECT-lauseita. Esimerkiksi:

Esimerkiksi:

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

Työntekijätietojen palauttamisen sijaan MySQL palauttaa rivi riviltä selityksen siitä, miten se suorittaisi kyseisen lausekkeen. Näet, mikä taulukko luetaan, mikä indeksi valitaan, kuinka monta riviä MySQL odottaa tarkastavansa ja lisätietoja, kuten tarvitaanko väliaikainen taulukko vai tiedostojen lajittelu.

Käytä EXPLAIN-käskyä aina, kun epäilet kyselyn olevan hitaampi kuin sen pitäisi olla, tai kun suunnittelet indeksejä ja haluat varmistaa, että MySQL todella käyttää niitä. Se on myös erittäin hyödyllinen, kun perit monimutkaisen vanhan SQL:n, jossa on useita alikyselyitä ja liitoksia, ja sinun on takaisinmallinnettava optimoijan toiminta.

EXPLAIN-käskyn muunnelmat: EXTENDED, PARTITIONS, ANALYZE ja formaatit

Perus EXPLAIN on vasta lähtökohta; MySQL tarjoaa useita laajennuksia ja tulostusmuotoja, jotka antavat enemmän tietoa. Näiden vaihtoehtojen ymmärtäminen auttaa sinua valitsemaan oikean yksityiskohtien tason kuhunkin tilanteeseen.

EXPLAIN EXTENDED lisää optimointitietoja, erityisesti filtered sarake ja uudelleenkirjoitettu kyselyteksti. Juoksun jälkeen EXPLAIN EXTENDED ... voit toteuttaa SHOW WARNINGS; nähdäksesi, miten optimoija kirjoitti kyselysi uudelleen sisäisesti, mikä on erittäin kätevää optimointipäätösten ymmärtämisen kannalta.

EXPLAIN PARTITIONS näyttää, mitä osioidun taulukon osioita kysely käyttää. RFID lukija NFC lukija partitions sarake listaa asiaankuuluvat osiot, mikä auttaa sinua varmistamaan, että osioiden karsintaa todella tapahtuu etkä vahingossa osu jokaiseen osioon.

Myös tulostusmuoto on joustava: voit saada taulukkomuotoisen, sarkainerotellun, pystysuuntaisen tai JSON-tulosteen komentoriviltä (CLI) ja visuaalisia suunnitelmia työkaluilla, kuten MySQL Workbench. JSON-tuloste on erityisen hyödyllinen automatisoinnissa ja syvällisemmässä analyysissä, koska se sisältää kustannusarvioita ja sisäkkäisiä suunnitelmarakenteita, mutta huomaa, että tavallinen EXPLAIN ANALYZE MySQL ei tällä hetkellä tue raakaa JSON-tulostetta.

Visuaaliset työkalut, kuten MySQL Workbench, voivat hahmontaa EXPLAIN-funktion graafisena puuna, mikä on usein helpompi ymmärtää monimutkaisten liitosten ja alikyselyiden yhteydessä. Ulkoiset työkalut, kuten Percona Toolkit, EverSQL tai Releem, voivat käyttää EXPLAIN-tulostetta tai hitaita kyselylokeja ja auttaa sinua priorisoimaan, mitkä lauseet optimoidaan ensin.

EXPLAIN-sarakkeiden ymmärtäminen (taulukkomuoto)

Kun suoritat klassisen EXPLAIN-komennon ilman JSON-muotoa, MySQL palauttaa yhden rivin jokaista lausekkeeseesi liittyvää taulukkoa tai alikyselyä kohden. Näiden rivien järjestyksellä on merkitystä: se näyttää järjestyksen, jossa taulukoita käytetään suorituksen aikana.

Nämä ovat tärkeimmät näkemäsi sarakkeet ja mitä ne kertovat:

• id: Kyselyn jokaiselle osalle on peräkkäinen tunniste. Yksi yksinkertainen SELECT-käsky sisältää yleensä id = 1Useat tunnisteet viittaavat alikyselyihin, johdettuihin taulukoihin tai UNION-osiin. Suuremmat luvut suoritetaan yleensä ennen pienempiä, mikä antaa vihjeen suoritusjärjestyksestä.
• select_type: Kuvaa kyseisen SELECT-metodin roolia koko kyselyssä esimerkiksi seuraavilla arvoilla: SIMPLE (ei alikyselyitä tai UNIONEJA), PRIMARY (uloin SELECT monimutkaisessa kyselyssä), UNION, UNION RESULT, DERIVED (alikysely FROM-lausekkeessa) tai kokotekstiin liittyviä tyyppejä. Näin näet yhdellä silmäyksellä, onko kyseessä sisäkkäisiä kyselyitä, yhdisteitä vai johdettuja taulukoita.
• table: Ilmaisee, mihin taulukkoon tai sisäiseen tulokseen tämä rivi viittaa. Se voi olla todellinen taulukon nimi tai pseudonimi, kuten <unionM,N> sisäisen UNION-tuloksen osalta <derivedN> johdetulle taulukolle tai vastaavia merkkejä materialisoiduille alikyselyille.
• partitions: Kun käytetään osioituja taulukoita yhdessä EXPLAIN PARTITIONS, tämä listaa osiot, jotka sisältävät kyselyehtoja vastaavia rivejä. Jos näet useita osioita listattuna, et ehkä hyödy osioiden karsimisesta.
• type: Usein liittymis- tai käyttötyypiksi kutsuttu tämä on yksi tärkeimmistä suorituskyvyn indikaattoreista. Se kuvaa, miten MySQL käyttää rivejä: arvot vaihtelevat erittäin tehokkaasta (kuten const, eq_ref, ref) vähemmän tehokkaaksi (range) köyhäksi (index, ALL koko taulukon skannaus). Erikoistyypit, kuten index_merge, unique_subqueryja index_subquery osoittavat tiettyjä optimointeja.
• possible_keys: Listaa indeksit, joita MySQL katsoo sopivan käytettäväksi kyselyn tässä osassa. Jos näin on NULL, se tarkoittaa, että MySQL ei näe hyödyllistä indeksiä, mikä on vahva signaali siitä, että sinun on ehkä luotava sellainen WHERE- tai JOIN-ehtojesi perusteella.
• key: Näyttää optimoijan tälle käyttöoikeudelle valitseman todellisen indeksin. Jos se on NULL vaikka possible_keys listaa ehdokkaita, optimoija päätti, että indeksin käyttö ei ollut vaivan arvoista, usein alhaisen selektiivisyyden tai pienen taulukkokoon vuoksi.
• key_len: Näyttää indeksin käytettyjen tavujen määrän. Yhdistelmäindekseissä tämä osoittaa, kuinka monta alkusaraketta on tehokkaasti käytössä. Tämä on ratkaisevan tärkeää sen ymmärtämiseksi, käytetäänkö monisarakkeista indeksiä kokonaan vai vain osittain.
• ref: Ilmaisee, mitä verrataan kohdassa lueteltuihin indeksisarakkeisiin key: se voi osoittaa toiseen taulukon sarakkeeseen (liitoksia varten) tai vakioon (yksinkertaisia ​​suodattimia varten).
• rows: Arvio siitä, kuinka monta riviä MySQL odottaa tutkivansa tässä vaiheessa. Arvio on arvio, joka perustuu tilastoihin, mutta erittäin hyödyllinen sen arvioimiseksi, kuinka kallis kysely tulee olemaan ja pienentääkö indeksi hakutilaa tehokkaasti.
• filtered: Saatavana EXPLAIN EXTENDED, tämä prosenttiosuus kuvaa sitä, kuinka monen tutkituista riveistä odotetaan läpäisevän kyseisen taulukon ehdot. Alhainen prosenttiosuus yhdistettynä korkeaan rows usein huutavat parempaa indeksointia tai valikoivampia ehtoja.
• Extra: Vapaamuotoinen kenttä, joka sisältää ylimääräisiä muistiinpanoja, jotka eivät sovi muualle, kuten Using index, Using where, Using temporary, Using filesort, kokotekstisiä vihjeitä ja paljon muuta, jotka kaikki sisältävät tärkeitä suorituskykyyn liittyviä vihjeitä.

Lukemalla nämä sarakkeet yhdessä erikseen lukemisen sijaan saat tiiviin mutta tehokkaan yhteenvedon siitä, miten MySQL aikoo vastata kyselyysi. Harjoittelemalla huomaat nopeasti varoitusmerkit, kuten täysien taulukoiden skannaukset, puuttuvat indeksit tai tarpeettomat väliaikaiset taulukot.

Miten EXPLAIN määrittelee käyttöoikeustyypit ( type sarake)

RFID lukija NFC lukija type sarake ansaitsee erityistä huomiota, koska se on yksi nopeimmista tavoista arvioida kyselyn kuntoa. Vaikka dokumentaatiossa sitä kutsutaan liitostyypiksi, on tarkempaa ajatella sitä rivien löytämistä kuvaavana käyttötyyppinä.

Korkealla tasolla käyttöoikeustyypit vaihtelevat suorituskyvyn suhteen "erinomaisesta" "kamalaan". Vaikka tarkka lista on pitkä, tärkeimpiin kuuluvat:

• const / system: MySQL voi ratkaista taulukon enintään yhteen riviin ensisijaisen avaimen tai vakioarvoisen yksilöllisen indeksin avulla. Tämä on erittäin tehokasta.
• eq_ref: MySQL lukee edellisen taulukon jokaista riviä kohden täsmälleen yhden vastaavan rivin tästä taulukosta, yleensä liitoksen yksilöllisen tai ensisijaisen avaimen viittauksen vuoksi.
• ref: MySQL käyttää rivejä epäyksilöllisen indeksin kautta, joka vastaa vakiota tai saraketta; useita rivejä voi olla samat. Yleisesti ottaen silti hyvä.
• range: MySQL käyttää indeksiä hakeakseen rivejä tietyltä arvoalueelta (esim. BETWEEN, >=, tai etuliite, joka vastaa LIKE 'abc%'). Tämä on usein hyväksyttävää, erityisesti päivämäärä- tai numeroalueille.
• index: MySQL skannaa koko indeksin koko taulukon datan sijaan. Parempi kuin koko taulukon skannaus, mutta silti usein liian kallis suurilla indekseillä.
• ALL: Koko taulukon skannaus. MySQL lukee jokaisen rivin löytääkseen osumia. Pienissä taulukoissa tämä voi olla hyvä, mutta suurissa taulukoissa kannattaa yleensä harkita indeksointia tai kyselyiden uudelleenkirjoittamista sen välttämiseksi.
• index_merge: Ilmaisee, että MySQL yhdistää tuloksia useista indekseistä samassa taulukossa. Se kuulostaa hyvältä, mutta käytännössä se toimii usein huonommin kuin hyvä yksittäinen yhdistelmäindeksi.
• unique_subquery / index_subquery: Erityiset käyttöoikeustyypit, joita käytetään tiettyjen IN (SELECT ...) alikyselyt ainutlaatuisuus- tai ei-yksilöllisten indeksihakujen kautta, korvaamalla vähemmän tehokkaita hakumalleja.

Kun tarkastelet EXPLAIN-tulostetta, haluat yleensä käyttöoikeustyyppien olevan mahdollisimman lähellä const / eq_ref / ref kuin mahdollista ja välttää ALL suurilla pöydillä. Jos näet ALL korkealla rows arvion mukaan se on lähes aina ehdokas parempaan indeksointiin.

Key, possible_keys ja key_len: Ovatko indeksisi todella hyödyllisiä?

Indeksit ovat usein tehokkain yksittäinen tapa nopeuttaa kyselyitä, ja EXPLAIN auttaa sinua ymmärtämään tarkalleen, miten niitä käytetään. Kolme saraketta ovat erityisen tärkeitä indeksidiagnostiikassa: possible_keys, keyja key_len.

possible_keys listaa kaikki indeksit, joiden MySQL uskoo olevan merkityksellisiä kyseiselle taulukolle WHERE- ja JOIN-ehtojen perusteella. Jos tämä on NULL Jos taulukko osallistuu raskaaseen suodatukseen tai liitokseen, se on vahva vihje siitä, että kannattaa harkita indeksin lisäämistä, joka kattaa asiaankuuluvat sarakkeet.

key kertoo, minkä indeksin MySQL todellisuudessa valitsi ehdokkaista. If key is NULL vaikka siellä on merkintöjä possible_keysoptimoija päätti, että täysi skannaus on halvempi, mikä yleensä viittaa huonoon indeksin selektiivisyyteen, vanhentuneisiin tilastoihin tai kyselymalliin, joka ei voi hyötyä käytettävissä olevasta indeksirakenteesta.

key_len näyttää kuinka monta tavua valitusta indeksistä on käytetty. Yhdistelmäindeksien tapauksessa tämän avulla voit vahvistaa, kuinka monta indeksoitua saraketta osallistuu hakuun. Jos olet luonut indeksin (last_name, first_name) ja key_len kattaa vain pituuden last_nametiedät, että kyselyt eivät hyödynnä yhdistelmäindeksiä täysimääräisesti.

RFID lukija NFC lukija ref sarake täydentää tätä kertomalla, mitä MySQL vertaa indeksiin, onko se vakio (kuten 'Puppo') tai sarake yhdistetystä taulukosta. Jos vianmäärität liitosta ja näet oikean liitossarakkeen kohdassa ref yhdessä hyvän kanssa type arvo, kuten eq_ref on merkki siitä, että liitoksesi on hyvin indeksoitu.

Rivit, suodatetut ja ylimääräiset: piilevien tehottomuuksien havaitseminen

RFID lukija NFC lukija rows ja filtered sarakkeet antavat karkean kuvan työmäärästä, kun taas Extra korostaa erikoisoperaatioita, jotka usein selittävät suorituskykyongelmia. Näitä kolmea tulisi aina tarkastella yhdessä.

rows on MySQL:n arvio siitä, kuinka monta tietuetta sen on tutkittava tässä vaiheessa. Se ei ole aina tarkka, mutta riittävän hyvä havaitsemaan ilmeisen huonot tapaukset, kuten satojen tuhansien rivien skannauksen, kun odotettiin vain kourallista.

filtered (saatavilla kautta EXPLAIN EXTENDED) antaa arvion tutkittujen rivien prosenttiosuudesta, jotka läpäisevät kyseisen taulukon ehdot. Korkea rows erittäin matalalla filtered prosenttiosuus viittaa yleensä puuttuviin tai optimaalisia heikompiin indekseihin tai huonosti valikoiviin predikaatteihin.

RFID lukija NFC lukija Extra sarake kokoaa yhteen suoritusta koskevia lisätietoja, jotka eivät sovi mihinkään muualle. Joitakin tärkeimpiä arvoja, joihin saatat törmätä, ovat:

• Using where: Tämän taulukon suodatusriveille käytetään WHERE-ehtoa.
• Using index: MySQL voi tarjoilla kaikki tarvittavat sarakkeet pelkästään indeksistä koskematta taulukon tietoihin, mikä tunnetaan peittävänä indeksinä ja on yleensä erittäin tehokasta.
• Using temporary: MySQL luo sisäisen väliaikaisen taulukon välitulosten säilyttämistä varten, esimerkiksi monimutkaista GROUP BY -hakua tai alikyselyä varten. Suurissa tietojoukoissa tämä voi vaikuttaa merkittävästi suorituskykyyn.
• Using filesort: MySQL suorittaa erillisen lajitteluvaiheen, usein ORDER BY- tai GROUP BY -operaatioille, joka voidaan tehdä muistissa tai levyllä ja on yleensä kalliimpi kuin indeksipohjaisen järjestyksen käyttö.
• Kokotekstiin liittyviä huomautuksia: FULLTEXT-indeksejä käytettäessä EXPLAIN voi paljastaa, yhdistetäänkö kokotekstihaku oikein liitosten tai muiden suodattimien kanssa.

Kiinnitä erityistä huomiota Using temporary ja Using filesort in Extra kun kyselyt ovat hitaita, koska molemmat voivat viitata raskaaseen lajittelu- tai ryhmittelytyöhön, joka saatetaan siirtää parempiin indekseihin tai uudelleenjärjesteltyihin kyselyihin.

SELITÄ ANALYYSI: todellisten toteutuskustannusten näkeminen

MySQL 8.0.18:sta alkaen saat käyttöösi entistä tehokkaamman työkalun: EXPLAIN ANALYZE, joka itse asiassa suorittaa kyselyn ja liittää ajonaikaiset tilastot suunnitelmaan. Tämä kuroa umpeen kuilua teoreettisten kustannusarvioiden ja todellisen suorituskyvyn välillä.

Toisin kuin tavallinen EXPLAIN, joka vain ennustaa, EXPLAIN ANALYZE suorittaa lausekkeen ja mittaa, kuinka kauan kukin iteraattori (vaihe) suunnitelmassa kestää, kuinka monta riviä se palauttaa ja kuinka monta silmukkaa se suorittaa. Syntaksi on suoraviivainen:

Juosta: EXPLAIN ANALYZE SELECT * FROM table_name WHERE condition;

Kun suoritat EXPLAIN ANALYZE -komennon, MySQL käyttää FORMAT=tree automaattisesti ja tuottaa puumaisen suunnitelman, joka yhdistää arvioidut ja todelliset mittarit. Jokaisesta solmusta näet:

• Arvioidut toteutuskustannukset: Optimoijan malli siitä, kuinka kalliiksi tämän vaiheen tulisi tulla.
• Arvioidut rivit: Kuinka monta riviä tämän iteraattorin odotettiin palauttavan.
• Todellinen aika ensimmäiselle riville: Kuinka kauan kesti ennen kuin ensimmäinen rivi tuotettiin.
• Todellinen silmukkaa kohden käytetty aika: Iteraattorin, mukaan lukien sen lapset, mutta ei sen pääasiallista osiota, keskimääräinen suoritusaika millisekunteina silmukoiden välillä.
• Varsinaiset rivit ja silmukat: Kuinka monta riviä todella palautettiin ja kuinka monta kertaa iteraattori suoritettiin.

Voit käyttää EXPLAIN ANALYZE -lauseketta SELECT-, monitaulukko-UPDATE-, DELETE- ja TABLE-lausekkeiden kanssa. Se on erityisen hyödyllistä silloin, kun normaali EXPLAIN ehdottaa yhtä asiaa, mutta kysely toimii silti eri tavalla tuotannossa, koska nyt voit verrata arvioituja ja todellisia rivimääriä ja kustannuksia.

Suuret eroavaisuudet arvioiden ja toteumien välillä paljastavat, missä optimoijan tilastotiedot ovat pielessä tai missä monimutkaiset predikaatit (mukaan lukien funktiot, UDF:t tai tallennetut rutiinit) tekevät kustannusennusteista epätarkkoja. Nuo paikat ovat ensisijaisia ​​kohteita skeeman muutoksille, uusille indekseille tai kyselyiden uudelleenkirjoituksille.

Klassinen EXPLAIN vs. EXPLAIN ANALYZE: vahvuudet ja rajoitukset

Vaikka EXPLAIN ja EXPLAIN ANALYZE ovat uskomattoman hyödyllisiä, on tärkeää ymmärtää niiden sokeat pisteet, jotta et tulkitse tulosta liikaa.

Tavallinen EXPLAIN on pohjimmiltaan likimääräinen kuvaus siitä, mitä optimoija olettaa tapahtuvan. Jotkin sen tilastotiedoista ovat karkeita arvioita, eivätkä tietyt sisäiset optimoinnit näy tulosteessa lainkaan. Se ei esimerkiksi kerro, miten triggerit, tallennetut funktiot tai käyttäjän määrittämät funktiot vaikuttavat suorittimen aikaan. Se ei myöskään näytä, mitä tallennettujen proseduurien sisällä tapahtuu.

Jotkin tulosteen otsikot yksinkertaistavat liikaa sitä, mitä todella tapahtuu. Esimerkiksi type saraketta kutsutaan liitostyypiksi, vaikka se todellisuudessa edustaa käyttöoikeustyyppiä. Extra arvo Using temporary ei erottele muistissa olevia ja levyllä olevia väliaikaisia ​​taulukoita, ja Using filesort kattaa sekä muisti- että levypohjaiset lajittelut saman nimikkeen alla.

EXPLAIN ANALYZE ratkaisee joitakin näistä ongelmista näyttämällä reaaliaikaisen ajoituksen ja rivimäärät, mutta sillä on myös kompromisseja. Sen on suoritettava kysely, mikä voi olla kallista tai jopa vaarallista tuotannossa, jos kysely on resursseja vaativa tai muokkaa dataa. Raakaa JSON-tulostetta ei myöskään ole tällä hetkellä saatavilla suoraan EXPLAIN ANALYZE -funktiolle, joten työskentelet enimmäkseen puumuodossa komentorivillisessä käyttöliittymässä tai asiakastyökaluissa.

Oikea lähestymistapa on käyttää EXPLAIN-metodia nopeaan ja vähävaikutteiseen tiedonhakuun suunnitelmasta ja EXPLAIN ANALYZE-metodia syvällisempiin tutkimuksiin, joissa kyselyn suorittaminen on mahdollista.

EXPLAIN- ja EXPLAIN ANALYZE -funktioiden käyttö todellisten kyselyiden optimointiin

EXPLAIN-käskyn lukeminen on hyödyllistä vain, jos se johtaa parempiin kyselyihin; avainasemassa on kääntää näkemäsi tietyiksi indekseiksi ja SQL-muutoksiksi. Käydään läpi joitakin yleisiä optimointiskenaarioita, joihin voit puuttua suoraan EXPLAINin avulla.

Kuvittele yksinkertainen kysely employees taulukko ilman asiaankuuluvia indeksejä:

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

Ilman indeksejä EXPLAIN todennäköisesti näkyy type = ALL ja erittäin suuri arvo rows (esimerkiksi noin 299 000 riviä), mikä osoittaa koko taulukon skannauksen. Se on selvä merkki siitä, että tarvitset indeksin, joka tukee molempia ehtoja.

Yksi naiivi lähestymistapa on luoda kaksi erillistä indeksiä, toinen last_name ja toinen päälle first_name, mutta se ei silti tee yhdistelmähausta tehokasta. MySQL löytää nopeasti kaikki, joiden last_name = 'Puppo' tai kaikki, joiden first_name = 'Kendra', mutta 'Kendra Puppo' -rivin tarkan tunnistaminen vaatii joukkojen leikkaamisen, mikä ei ole niin tehokasta kuin voisi toivoa.

Parempi ratkaisu on monisarakkeinen indeksi, joka noudattaa valikoivimpia ja useimmin käytettyjä hakukaavojasi:

Indeksin luominen: CREATE INDEX fullnames ON employees(last_name, first_name);

Suorittamalla EXPLAINin uudelleen, sinun pitäisi nähdä, että tätä indeksiä käytetään, type paranee johonkin sellaiseen ref tai jopa const riippuen ainutlaatuisuudesta ja rows putoaa arvoon 1. Tämä vahvistaa, että vain yhteen riviin tarvitsee koskea, mikä selittää valtavan suorituskyvyn parannuksen.

Samanlainen kaava näkyy myös liittymisen optimoinnissa. Oletetaan, että sinulla on kaksi taulukkoa A ja B, joissa on jaettu sarake X, jota käytetään yhdistämiseen, ja luot ne aluksi ilman indeksejä:

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

Suodattimen käyttäminen AX-liitoksessa voi näyttää täydet skannaukset sekä A:ssa että B:ssä, joissa kummassakin on noin 10 000 riviä tutkittuna, koska moottorilla ei ole parempaa vaihtoehtoa kuin vertailla rivejä raa'alla voimalla:

Liittymiskysely: EXPLAIN SELECT *
FROM A INNER JOIN B ON A.X = B.X
WHERE A.X = '100';

Kun olet lisännyt asianmukaiset indeksit ja viiteavaimen rajoitteet, EXPLAIN heijastaa parannettua strategiaa. Esimerkiksi:

Hakemisto ja 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);

Näiden muutosten jälkeen EXPLAIN näkyy yleensä. type parantumassa (esim. eq_ref yhdistetyssä taulukossa), sekä rows pudonnut tuhansista noin yhteen, mikä osoittaa, että liitos käyttää nyt tehokkaita indeksihakuja kaiken skannaamisen sijaan.

Kuvioiden yhteensovittaminen, lajittelu ja muut yleiset sudenkuopat

EXPLAIN on erityisen hyödyllinen käsiteltäessä kuvioita, lajitteluja ja ryhmittelyjä, joissa näennäisesti harmiton SQL voi poistaa indeksit kokonaan käytöstä. Näiden kaavojen tunnistaminen varhain säästää sinulta paljon arvailua.

Yksi klassinen ongelma on kuvioiden yhteensovittaminen johtavien jokerimerkkien kanssa. Esimerkiksi kysely, jossa on WHERE email LIKE '%yahoo.com' tekee mahdottomaksi standardin mukaisen indeksin email auttamaan, koska MySQL:llä ei ole tapaa hypätä suoraan riveille, jotka päättyvät kyseiseen päätteeseen. EXPLAIN näyttää tyypillisesti type = ALL ja suuri rows Kreivi.

Jäljessä olevat jokerimerkit, kuten LIKE 'john%', ovat indeksiystävällisiä, koska hakukone voi käyttää indeksiä löytääkseen rivivälin, joka alkaa merkeillä 'john'. EXPLAIN vahvistaa tämän näyttämällä hyvän käyttöoikeustyypin ja pienen rows arvio indeksoidussa sarakkeessa.

Lajittelu ja ryhmittely ovat toinen suorituskyvyn kannalta tärkeä tekijä. Jos SELITÄ näkyy Using filesort in Extra yhdessä suuren rows Arviosi mukaan ORDER BY- tai GROUP BY -käskysi todennäköisesti pakottaa eksplisiittisen lajitteluvaiheen.

Lajitteluesimerkki: EXPLAIN SELECT sale_id FROM sales ORDER BY sale_date;

Indeksin lisääminen sale_date sallii MySQL:n lukea rivejä suoraan lajitellussa järjestyksessä, mikä EXPLAIN yleensä ilmenee poistamalla Using filesort ja mahdollisesti käyttöoikeustyypin muuttaminen index tai parempi.

Samalla on oltava varovainen, ettet indeksoi liikaa. EXPLAIN voi näyttää, mitkä indeksit esiintyvät possible_keys mutta niitä ei koskaan käytetä niin key minkä tahansa tärkeän kyselyn avulla. Ne saattavat olla poistettavia, mutta sinun on ensin tarkistettava kokonaistyömäärä; se, että yksi kysely jättää indeksin huomiotta, ei tarkoita, etteivät muut tärkeät kyselyt olisi siitä riippuvaisia.

NÄYTÄ VAROITUKSET ja laajennetut selitysmerkit

Kun EXPLAINed-kysely ei edes jäsenny oikein, voit silti saada jonkin verran tietoa käyttämällä SHOW WARNINGS;. Tämä komento näyttää tietoja viimeisimmästä ei-diagnostisesta lausekkeesta ja voi antaa sinulle osittaisia ​​vihjeitä siitä, miten MySQL tulkitsi kyselysi osia.

Jos esimerkiksi suoritat EXPLAIN-komennon kyselyssä, joka viittaa olemattomaan taulukkoon tai sisältää syntaksivirheen, EXPLAIN saattaa epäonnistua, mutta SHOW WARNINGS voi silti näyttää sisäisiä merkkejä, jotka osoittavat, miten kyselyn osia käsitellään. Saatat nähdä merkkejä, kuten:

• <index_lookup>(query fragment): ehdottaa, että kyseiselle fragmentille suoritettaisiin indeksihaku, jos kysely olisi kelvollinen.
• <primary_index_lookup>(query fragment): osoittaa, että suoritetaan ensisijaiseen avaimeen perustuva haku.
• <if>(condition, expr1, expr2): viittaa ehdollisen arvioinnin käyttöön kyselyn kyseisessä osassa.
• <temporary table>: ilmaisee sisäisen väliaikaisen taulukon luomisen välituloksia varten, esimerkiksi ennen liittymistä.

Yhdistämällä EXPLAIN EXTENDED ja SHOW WARNINGS saat tehokkaan tavan nähdä paitsi suunnitelman myös optimoijan uudelleenkirjoitetun kyselyn, joka joskus paljastaa yksinkertaistuksia, predikaattien alaspäin suuntautuvia siirtoja tai muita odottamattomia muutoksia.

Beyond EXPLAIN: tietomalli, arkkitehtuuri ja työkalut

Vaikka EXPLAIN on loistava mikroskooppi yksittäisiin kyselyihin, kestävä suorituskyky riippuu myös yleisestä tietomallistasi, järjestelmäarkkitehtuuristasi ja laitteistostasi. Et voi korjata perustavanlaatuisesti huonoa kaavaa tai ylikuormitettua instanssia pelkästään säätämällä where-lausekkeita.

Hyvä lähtökohta on datamalli, joka erottelee hyvin erilaiset työkuormat ja JSON-käsittely SQL:ssä. Esimerkiksi suurten käyttölokimäärien ja tapahtumadatan yhdistäminen samassa MySQL-instanssissa on pitkän aikavälin ongelma: yrityksen kasvaessa lokitietoliikenne voi kuormittaa I/O:ta ja heikentää kriittisiä liiketoimintakyselyitä. Lokien jakaminen erilliseen säilöön tai tietovarastoon on usein skaalautuvampaa.

Myös ohjelmistoarkkitehtuurilla on merkittävä rooli. Päätökset monoliittisista vs. mikropalveluista, tietovarastoista ja erikoistuneista varastoista (kuten suositusten graafitietokannoista) vaikuttavat siihen, millaisia ​​kyselyitä suoritat ja missä. Jos palvelu tarvitsee lähes reaaliaikaista analytiikkaa valtaville tietojoukoille, tavallinen OLTP MySQL -instanssi ei välttämättä ole paras kohde näille kyselyille.

Myös laitteistolla ja instanssin koolla on merkitystä. Levyn I/O, muisti, suorittimen käyttö ja verkon läpimenoaika vaikuttavat kaikki kyselyiden latenssiin. Huippukäyttöaikojen tunnistaminen ja raskaiden analyyttisten tai hallinnollisten kyselyiden ajoittaminen näiden ikkunoiden ulkopuolelle voi suojata käyttäjien suorituskykyä. Hallittujen DBaaS-tarjousten avulla voit usein skaalata instansseja tai siirtyä suorituskykyisempiin luokkiin tarpeen mukaan.

Lopuksi, täydennä EXPLAINia jatkuvan seurannan ja profiloinnin työkaluilla. MySQL:n oma suorituskykykaava ja hitaiden kyselyiden loki, MySQL Workbench, Percona Toolkitin pt-query-digest, verkkotyökalut, kuten EverSQL, ja alustat, kuten Releem, voivat automaattisesti nostaa esiin pahimmat ongelmasi ja jopa ehdottaa optimointeja. EXPLAIN on sitten kirurginen työkalusi näihin tiettyihin kyselyihin.

Lisäämällä EXPLAINin ja EXPLAIN ANALYZEn työkalupakkiisi voit lopettaa hitaiden kyselyiden arvailun ja aloittaa päättelyn konkreettisten todisteiden avulla. Ymmärtämällä tunnukset, valintatyypit, käyttötavat, indeksien käytön, riviarviot, suodattimet ja ylimääräiset merkinnät voit systemaattisesti poistaa koko taulukon skannaukset, kesyttää kalliita liitoksia, suunnitella älykkäämpiä indeksejä ja välttää turhia lajitteluja ja väliaikaisia ​​taulukoita, samalla kun pidät silmällä skeemasuunnittelun ja infrastruktuurin kokonaiskuvaa, jotta MySQL-työkuormit pysyvät nopeina ja ennustettavina niiden kasvaessa.