Share
Jeg har brukt phpMyAdmin i over et tiår. I mine tidlige år med verktøyet trengte jeg bare noe som kunne vise meg tabellstruktur og raskt gi meg dataene inni. Etter hvert som mine behov har vokst, så har verktøyene inkludert phpMyAdmin som holder meg tilbake som mitt primære MySQL-verktøy, selv med optimalisering.
Jeg har hatt gleden av å jobbe med flere forskjellige databaser. Hver har sine ulemper, og hver har sine sterke sider. Når jeg får et valg, pleier jeg å migrere tilbake til MySQL, til tross for at jeg er for billig til å kjøpe MySQL Enterprise. I stedet gjør jeg forfalt med phpMyAdmin som mitt hovedprofileringsverktøy. Det fungerer bra for meg, men jeg måtte gjøre en del forskning for å forstå hva jeg ser på mens jeg profilerer mine applikasjoner. Jeg håper å passere dette på en måte som kan forstås av nybegynnere, opp til den erfarne proffsen.
Optimalisering tar tid. Ledere, klienter og jevnaldrende for det saks skyld, liker ikke å høre at et prosjekt ligger bak planen på grunn av optimalisering. Ofte taster vi optimeringen for å møte disse referansene. Til slutt skjønner vi ikke noen noen favoriserer. Den peneste webapplikasjonen i verden skal få deg til å gjenta bedriften hvis det tar 10 sekunder å laste hver side. På samme måte, hvis vi venter på å optimalisere til slutten av prosjektene våre, er det sjansene for at det vil bli mye mer arbeid å gjøre enn om vi hadde sjekket etter hvert som prosjektet går langs.
Et par notater før vi kommer inn i kjøtt og poteter. Først vil jeg ikke komme inn i MySQL Tuning, da det er litt utenfor omfanget av denne opplæringen. Mens tuning er optimalisering, er det et tema alt til seg selv etter min mening. Jeg vil kort nevne et par muligheter til å optimalisere hvordan du stiller serveren din, men nevnelsene vil være korte. I tillegg vil jeg hovedsakelig se på MyISAM-tabeller og ikke InnoDB-tabeller. Tommelfingerregelen er hvis du skriver mye data, bruk InnoDB, men hvis du bruker SELECT mye mer, bruk MyISAM. Også, jeg kommer ikke inn på tabellnivå REPARASJON, OPTIMER, KONTROLLER OG ANALYSE fordi denne opplæringen dekker spørreoptimalisering med phpMyAdmin. Igjen, dette er litt utenfor omfanget av denne opplæringen.
Til slutt skal jeg se på WordPress som et ekte verdenseksempel. Jeg vil være den første til å fortelle deg at jeg ikke er ekspert i WordPress, men jeg kan se på de genererte spørringene med det beste av dem. Fra det jeg har sett, er databasen med WordPress godt indeksert, men når vi begynner å legge til ting som ligger utenfor de viktigste kjernefilene, er disse indeksene kanskje ikke det beste for det vi trenger.
"Optimalisering tar tid. Ledere, klienter og jevnaldrende for det saks skyld, liker ikke å høre at et prosjekt ligger bak planen på grunn av optimalisering."
Det korte svaret er ja.
Det lange svaret er phpMyAdmin gir oss sjansen til å se om vi trenger å optimalisere våre spørsmål, og hvor dårlig vi trenger for å optimalisere dem. Jeg kan tenke meg at du har sett denne skjermen mer enn en gang hvis du har brukt phpMyAdmin:
Det er standard startskjermbildet for phpMyAdmin. Med mindre du er på utkikk etter måter å optimalisere, kan du gå rett til bordene dine på menyen til venstre, og aldri se fanemenyen øverst. Den menyen, spesielt kategoriene Status og variabler, er hvor vi skal begynne.
La oss starte med Status-skjermen, som kan være det viktigste verktøyet som phpMyAdmin tilbyr:
Dette er toppen av statusskjermbildet. Mens det har noen interessante data, hvis du aldri har gått under rullebladet, har du gått glipp av noen svært viktig informasjon. For korthetens skyld vil jeg se på to svært enkle motverdier som jeg obsesserer over, den første fra testmiljøet mitt:
De to verdiene å være svært oppmerksom på er, Handler_read_rnd og Handler_read_rnd_next. Hvis disse to verdiene er i rødt, så er det noen spørsmål der ute som må kontrolleres, som når MySQL gjør en SELECT, leser den hele tabellen. I noen tilfeller kan dette være av design, som når du plasserer en indeks på et bord, tar det litt lenger tid å skrive, og det tar litt mer plass. Men hvis du ser noe slikt:
sjansene er, dette var ikke av design. 141 millioner forespørsler om å lese en rad på en fast stilling, og 16 milliarder forespørsler om å lese neste rad, betyr trolig at vi mangler en indeks eller to (tusen). Åpenbart vokser dette tallet basert på antall forespørsler, jo mer en søkemotor indekserer nettstedet ditt, eller jo flere besøkende du har, desto større blir en liten oversatt indeks. Fullt bordssøk er fienden, og dette gir deg en rask måte å se hvor nær fienden er til portene.
Et annet flott bord for å sjekke etter søkeytelse tar en titt på velg og indekser direkte:
Denne tabellen legger særlig vekt på dine tilmeldinger. En farlig kombinasjon bruker og indekserer ikke på begge tabeller, fordi de fulle tabellskannene går opp eksponentielt på antall sammenføyninger du bruker. Jo mer normaliserte tabellene dine, desto mer må du være oppmerksom på indeksene dine, så vel som definisjonen av feltene du går med.
Til slutt, avhengig av en global variabel, vil du også sjekke dette variabeltabellen også:
Hvis du logger på dine sakte spørringer, viser denne variabelteller nummeret som er identifisert for observasjon, avhengig av innstillingen for lang spørringstid. Disse variablene finner du fra fanebladet variabler. En rask titt i testmiljøet mitt viser denne innstillingen (for nå):
Disse to kategoriene viser ganske mye mer informasjon, hvorav noen er helt avgjørende for å justere MySQL-serveren. PhpMyAdmin gjør det veldig enkelt for selv nybegynneren å oppdage et problem, og å ha en grunnleggende forståelse av hva dette problemet kan være. Hvis en verdi er grønn, er vi gode. Hvis det er rødt, trenger det litt oppmerksomhet. Det tillater oss også å forstå at vi har gjort noe fremskritt. Når vi starter vår server på nytt, blir disse øktvariablene skyllet. Hvis vi har gjort endringer, kan vi se rett utenfor flaggermuset hvis vi gjorde noen innvirkning.
Nå som vi har identifisert at vi trenger å gjøre noen optimalisering, la oss se på noen av verktøyene vi skal bruke før vi finner våre problemer. Den første av verktøyene, og sannsynligvis det mest nyttige er å bruke EXPLAIN. EXPLAIN gir i utgangspunktet vår forespørselsplan for utførelse. Dette forteller oss hva MySQL planlegger å gjøre med denne spørringen før den utføres.
Uten å lese på EXPLAIN, kan produksjonen ikke bety mye for deg. Ved å bruke et bord jeg opprettet for en tidligere opplæring, la oss se på en ikke optimalisert utførelsesplan. Mitt bord har bare to felt i dette tilfellet, en er salg_id, og den andre er salg_amount. Her er spørsmålet jeg jobber med:
SELECT sales_id, sale_amount FROM tutorial.sales BESTILL BY sale_amount
På overflaten er dette et veldig enkelt spørsmål. Å være et salgstabell skjønt, vil bordet vokse og vokse og vokse. Jeg genererte 200 poster for den forrige opplæringen, og ved å gjøre en enkel SELECT med en ORDER BY-setning, tok det faktisk litt lengre tid enn jeg hadde forventet:
Spørringen med bare 200 poster kostet oss 0,15 sekunder. La oss bruke EXPLAIN for å forstå hvordan MySQL ser dette spørsmålet. Bare klikk på "Forklar SQL" -linken for å se resultatene:
Som det meste, betyr dette ikke mye fornuft med mindre du forstår hva som blir sagt. Til noen som aldri har kjørt en EXPLAIN på en forespørsel, kan dette også skrives i hieroglyfer. La oss se om vi kan oversette til noe litt mer forståelig.
Selektypen forteller oss at MySQL ser denne SELECT som en enkel, går til ett bord og prosess. Hvis det var en union eller en undersøkelse, ville dette vise hvilken del av SELECT-setningen dette ville kalle. For eksempel hvis jeg lager en spørring som har en underspørsel:
VELG salg_amount som beløp FRA salg HVOR sales_id IN (SELECT sales_id FROM sales_force WHERE sales_id = 4)
Vi får en forklaring på dette:
Som forteller oss om spørringen selv. I så fall er vår select_type endret for å si at den første spørringen er den primære, og deretter vil MySQL gå ut og utføre subqueryen, som er en visning, så det er en annen undersøkelse å utføre, derfor slutter vi med de tre separate IDer. MySQL Reference Manual gir alle mulige verdier:
Tilbake til vårt opprinnelige eksempel:
Typen er den som skal være oppmerksom på, da den forteller om MySQL skal skanne hele bordet, eller om det vil bruke en indeks for å raskt finne resultatene. Dette er den primære kolonnen for å se på når du optimaliserer dine spørsmål. Fra ordren bra til dårlig er verdiene:
Hvor du vil begynne, blir det å optimalisere ethvert spørsmål som enten er typen index eller alle. Hvis du kan befri din søknad av disse to typene, vil ytelsen din bli bedre. Dette er vennene mine, hvor du starter.
Resten av kolonnene omhandler indeksene som MySQL vil bruke, og antall rader som den må skanne før det kan se om det er et gyldig resultat. Når du blir kvitt "indeksen" og "alle" -typene, vil disse komme til nytte for å forstå nøyaktig hvilken indeks MySQL bruker for å utføre denne spørringen. For å flytte et spørsmål oppover stigen begynner du å justere indeksene dine for å forbedre ytelsen. Med det formål å illustrere, kommer jeg til å holde fast med ridding "all" eller full bordskanning.
Den endelige kolonnen er kolonnen "ekstra". Den ekstra kolonnen forteller deg informasjon om spørringen, om en WHERE-setning brukes, uansett om det er umulig. HVOR betyr det at denne spørringen alltid vil returnere en NULL fordi WHERE-klausulen gjør det umulig å utføre. Den ene verdien som vi trenger å være svært oppmerksom på, og kvitte oss med, er "Bruke filesort" som vi har i vårt eksempel. Når du ser den verdien, må MySQL gjøre et nytt pass gjennom resultatene for å sortere verdiene. Så, når det gjelder vår opprinnelige spørring:
SELECT sales_id, sale_amount FROM tutorial.sales BESTILL BY sale_amount
Ikke bare er MySQL skanning hele tabellen, men det må skannes to ganger for å sortere resultatene på grunn av ORDER BY-setningen. Dette er åpenbart dobbelt dårlig. Vi vil optimalisere denne spørringen og mange flere i de følgende avsnittene.
I MySQL 5.0.37 ble et annet verktøy tilgjengelig for oss å bruke i optimalisering, og det er MySQL profiler. I tillegg har phpMyAdmin lagt til støtte for denne funksjonen i versjon 2.11, så hvis du har begge disse versjonene tilgjengelige, har vi et annet verktøy for å legge til optimalisering.
Hva MySQL Profiler gjør, er å gi informasjon om flaskehalsene i våre spørsmål. Det lar oss se hva som skjer under den faktiske utførelsen av våre spørsmål, vis hva EXPLAIN gjør, som er å gi utførelsesplanen før. La oss se hvilken informasjon vi kan få fra phpMyAdmin fra min opprinnelige dårlige spørring:
Hvis vi klikker på avkrysningsruten "Profiling" under forespørselen, åpner en ny verden med:
phpMyAdmin gir den faktiske utføringstiden for spørringen som ble oppgitt. Vi kan nå se flaskehalsene der våre spørsmål, eller til og med tabellnivå struktur bør tas opp. Kanskje ser vi behovet fra loggfiler at denne tabellen egentlig ikke er skrevet til så mye som den leses fra, så i stedet for InnoDB kan vi nå bytte den til MyISAM.
Det er litt ulempe med å bruke phpMyAdmin når du bruker MySQL Profiler, og det er at profileren er basert på økten, og phpMyAdmin ødelegger økten på hver sidevisning ... Problemet dette gir oss er at vi ikke har en vei å holde en løpende total av profileringsdataene, men det er en måte å lure phpMyAdmin på, men i en kludgy måte:
SET profilering = 1; SELECT sales_id, sale_amount FROM tutorial.sales BESTILL BY sale_amount; VIS profiler;
Som resulterer i:
Siden vi utfører flere søk, må du bruke avgrensningen. Dette vil vise at spørringen min er query_id 1. Hver gang jeg kjører denne spørringen, er den fortsatt query_id 1 siden økten blir ødelagt ved oppstart. Jeg er ikke sikker på om dette er av design, en feil eller uvitenhet fra min side at phpMyAdmin ødelegger økten med QUIT-kommandoen, men vi kan bare løse dette problemet litt. MySQL har en fantastisk skrive opp på bruk av profiler av Robin Schumacher, og jeg skal bruke litt av Robins spørring for å få antall operasjoner i phpMyAdmin:
SET profilering = 1; SELECT sales_id, sale_amount FROM tutorial.sales BESTILL BY sale_amount; SELECT min (seq) som sekvens, tilstand, telle (*) som operasjoner, runde (sum (varighet), 5) som varighet FRA information_schema.profiling WHERE query_id = 1 GRUPPE med tilstand ORDER ved seq;
Igjen, ikke ideell med phpMyAdmin, men vi får fremdeles det vi vil ha til slutt:
Før vi legger alt vi har lært sammen, la oss også ta en titt på hvordan å fange spørringer ved å bruke MySQLs loggfiler. Vi kan fange opp alle forespørsler som MySQL går inn i mysql.general_log-tabellen. Ved å kjøre denne kommandoen:
SET GLOBAL general_log = 'ON'; SET GLOBAL log_output = 'TABLE';
Vi kan nå ha en rekord for alle spørsmålene som kjøres, uavhengig av kilden. Selv om denne operasjonen er dyr, og jeg ikke ville kjøre den på en produksjonsinnstilling, gir den oss en klar og konsis metode for å få alle våre spørsmål, og rekkefølgen av deres utførelse fra våre applikasjoner. Kort sagt, dette kan være det mest verdifulle SQL-søkeoptimaliseringsverktøyet du har i verktøykassen. Ved å sette disse to GLOBAL vars, har vi det siste skrittet for å få noen praktiske optimaliseringsteknikker.
Her er en forkortet utgang fra mysql.general_log-tabellen ved hjelp av dette spørsmålet:
VELg event_time, command_type, argument FRA mysql.general_log ORDER BY event_time
produserer dette:
Jeg har stort sett spørringen min, sammen med alt phpMyAdmin har gjort i bakgrunnen. Hvis jeg tømmer tabellen før hver ny kommando, har jeg noe jeg kan jobbe med på hver sidevisning, eller AJAX-anrop jeg lager fra mine applikasjoner. For å tømme loggen, tømmer vi bare bordet slik:
TRUNCATE mysql.general_log
Truncate er en mye bedre setning å bruke her enn DELETE FROM, da DELETE-setningen sletter rad for rad, der som TRUNCATE tømmer hele bordet samtidig.
Når du er ferdig med optimaliseringen, må du bare slå av spørringsloggen med denne kommandoen:
SET GLOBAL general_log = 'OFF';
Den generelle loggen blir dyr over tid, og sikkert reduserer ytelsen til søknaden din. Jeg holder det slått av mellom mine optimaliseringer, så jeg kan få en organisk følelse for ytelsen til det jeg skriver. Når det er sagt, i utviklingen holder jeg alltid den langsomme spørringsloggen på, da jeg vil se mine langsommere spørsmål som et raskt optimaliseringsverktøy. Du kan gjøre dette enkelt:
SET GLOBAL slow_query_log = 'ON'; SET GLOBAL log_queries_not_using_indexes = 'ON'; SET GLOBAL log_output = 'TABLE';
og vi kan sjekke det fra fanen Variabler fra vår velkomstside:
For å se utgangen, trenger vi bare å sjekke mysql.slow_log eller vi kan bruke et spørsmål som dette:
VELG sql_text FRA mysql.slow_log
Som gir meg de faktiske spørringene som ble logget som sakte:
Nå kan vi sette dette helt og bruke phpMyAdmin som et relativt anstendig søkeoptimaliseringsverktøy. La oss starte med det første spørreeksemplet:
FORKLAR SELECT sales_id, sale_amount FROM tutorial.sales BESTILL BY sale_amount
Som produserer en produksjon av:
Vi vet at vi trenger å få minst ett INDEX på dette bordet. La oss stoppe og tenke hvordan dette bordet brukes. Det er et enkelt oppslagstabell for å bli med på en sales_force-tabell for å fortelle oss at de gjorde et salg som var av registrert beløp. Hvis alt vi noen gang gjør er å bli med på denne tabellen på sales_id, så er det det vi trenger å indeksere ved å klikke på detaljerlinken:
Vi kan da bare definere den indeksen slik:
Vår opprinnelige spørring gir oss fortsatt en full skanning, men i en praktisk applikasjon:
SELECT sfn.first_name, sfn.last_name, s.sale_amount FRA sales_force_normalized sfn INNER JOIN salg s ON sfn.sales_id = s.sales_id
La oss se om dette er noe bedre:
Nå kommer vi et sted. Men hvis vi gjør noe slikt:
SELECT max (sale_amount) fra salg
Så er vi tilbake i samme båt for å gjøre en full skanning av bordet. I dette tilfellet kan vi bare redigere indeksen og legge til sales_amount:
Som forbedrer oss fra virkelig dårlig til bare dårlig:
Eller vi kan legge til en ny indeks på bare beløpet:
Og vi har det fantastiske resultatet av:
Det betyr at MySQL ikke engang må åpne bordet, da det bare må se på indeksen. Vi har nå truffet absolutt optimal nivå for denne COUNT-funksjonen. Sjekk ut hvor lang tid det tok å utføre dette spørsmålet nå:
Og for godt mål, la oss klikke på avkrysningsruten Profiling på spørringen for å se noen flaskehalser nå:
Vi har spilt med å late spørsmål, og late som databaser, men la oss sette denne opplæringen til testen. Jeg har en lager WordPress installasjon, med bare Lorem Ipsum plugin for å legge til omtrent 5000 innlegg og 11 000 kommentarer, så vi kan bare legge litt på MySQL når vi lager våre valg.
La oss begynne å logge våre spørsmål igjen fra phpMyAdmin og også avkorte de langsomme og generelle loggene, slik at vi kan se hva som skjer når vi laster inn en side fra WordPress:
SET GLOBAL general_log = 'ON'; TRUNCATE mysql.slow_log; TRUNCATE mysql.general_log;
Det kommer til å være noen gjenstander i general_log som phpMyAdmin forårsaker litt aktivitet innen MySQL, men vi bør kunne få alt i orden når jeg laster inn indekssiden fra WordPress på dette tidspunktet, og hvis vi bruker en LIKE-tilstand, vi kan få mesteparten bare WordPress-resultater siden tabellene er prefixed med wp_:
SELECT event_time, command_type, argument FRA mysql.general_log HVOR argument som "% wp_%" ORDER BY event_time
Som gir oss et rimelig resultat av:
Nå vet vi at WordPress bare gir oss 11 spørsmål om lasting av indekssiden med en pen vaniljeinstallasjon. La oss finne noe å optimalisere at de kanskje har savnet. Hvis vi tar det aller første spørsmålet som utføres når WordPress laster:
EXPLAIN SELECT option_name, option_value FROM wp_options hvor autoload = 'ja'
Vi finner at dette ikke er optimalisert:
La oss ta en titt på hva de gjorde gjennom phpMyAdmin:
Vi ser at det er en indeks på option_name, men det er ikke en indeks på autoload, som er tilstanden angir på indekssiden. La oss legge til det, og se om vi ikke kan optimalisere kjernen WordPress-installasjonen bare litt:
Siden autoload er varchar og enten "ja" eller "nei" fra det jeg ser, kan jeg begrense indeksverdien til 1. Betydningen, det ser nå enten "y" eller "n" som reduserer tiden vår enda større. La oss se EXPLAIN etter at vi har optimalisert:
Vi har gått fra veldig dårlig til den fjerde beste typen. Ikke dårlig for et par minutter med jobb. Gitt, WordPress var ikke kvalt på denne verdien, men avhengig av belastningen på bloggen din, hjelper hver liten bit. Gitt nå, skrivene tar lengre tid, fordi vi må indeksere vår "y" eller "n" for hver linje som er skrevet.
Hvis vi går litt lenger, kan vi også se MySQL Profiler i handling ved å bare merke av for "Profiling". Nå ser vi at spørringen vår virkelig suger rett sammen:
Optimalisering er ikke lett, og det er heller ikke veldig morsomt. Men når du ignorerer dette utviklingsstrinnet, kommer det alltid tilbake for å hjemsøke deg. Jeg tror at det er relativt enkelt å bruke verktøyene i phpMyAdmin for å få et ganske godt optimaliseringslook på applikasjonene dine. Når det er sagt, er det nye verktøy lagt til hele tiden, for eksempel Jet Profiler som tar det jeg nettopp har gjort i sanntid og grafisk natur.
.
Accentsconagua