Share
Jag har använt phpMyAdmin i över ett decennium. Under mina första år med verktyget behövde jag helt enkelt något som kunde visa mig bordstrukturen och snabbt ge mig data inuti. När mina behov har vuxit, så har verktygen som ingår i phpMyAdmin som håller mig tillbaka som mitt primära MySQL-verktyg, även med optimering.
Jag har haft nöjet att arbeta med flera olika databaser. Var och en har sina nackdelar, och alla har sina styrkor. När jag får ett val tenderar jag att migrera tillbaka till MySQL, trots att jag är för billig för att köpa MySQL Enterprise. Istället gör jag förfogande med phpMyAdmin som mitt huvudprofileringsverktyg. Det fungerar bra för mig, men jag var tvungen att göra en hel del forskning för att förstå vad jag tittar på när jag profilerar mina ansökningar. Jag hoppas kunna överföra det här på ett sätt som kan förstås av nybörjaren, upp till den erfarna proffsen.
Optimering tar tid. Chefer, kunder och kamrater för den delen, tycker inte om att höra att ett projekt ligger bakom schemat på grund av optimering. Ofta skyndar vi optimeringen för att uppfylla dessa riktmärken. I slutändan gör vi inte någon någon förmån. Den vackraste webbapplikationen i världen kommer att få dig att repetera om det tar 10 sekunder att ladda varje sida. På samma sätt, om vi väntar på att optimera fram till slutet av våra projekt är chansen att det kommer att bli mycket mer arbete att göra än om vi hade kontrollerat som projektet går längs.
Ett par anteckningar innan vi hamnar i kött och potatis. Först kommer jag inte att komma in i MySQL Tuning, eftersom det är lite utom räckhåll för denna handledning. Medan tuning är optimering är det ett ämne som jag själv anser enligt min mening. Jag kommer kort att nämna några möjligheter att optimera hur du stämmer med din server, men anmärkningarna kommer att vara korta. Dessutom tittar jag huvudsakligen på MyISAM-tabeller och inte på InnoDB-tabeller. Tumregeln är om du skriver mycket data, använd InnoDB, men om du använder SELECT mycket mer, använd sedan MyISAM. Jag kommer inte heller på tabellnivå REPARATION, OPTIMERA, KONTROLLERA OCH ANALYSER eftersom denna handledning täcker sökoptimering med phpMyAdmin. Återigen är detta lite utom räckhåll för denna handledning.
Slutligen ska jag titta på WordPress som ett verkligt världsexempel. Jag kommer att vara den första som berättar att jag inte är expert på WordPress, men jag kan titta på de genererade frågorna med det bästa av dem. Från vad jag har sett databasen med WordPress är väl indexerad, men när vi börjar lägga till saker som ligger utanför de viktigaste kärnfilerna, kanske dessa index inte är det bästa för vad vi behöver.
"Optimering tar tid. Ledare, kunder och kamrater för den delen, tycker inte om att höra att ett projekt ligger bakom schemat på grund av optimering."
Det korta svaret är ja.
Det långa svaret är phpMyAdmin ger oss en chans att se om vi behöver optimera våra frågor och hur mycket vi behöver optimera dem. Jag kan tänka mig att du har sett den här skärmen mer än en gång om du har använt phpMyAdmin:
Det är standard startskärmen för phpMyAdmin. Om du inte letar efter sätt att optimera kan du gå direkt till dina bord på vänstra menyn och aldrig se flikmenyn längst upp. Den menyn, särskilt flikarna Status och variabler, är där vi ska börja.
Låt oss börja med Status-skärmen, vilket kan vara det viktigaste verktyget som phpMyAdmin tillhandahåller:
Detta är toppen av statusskärmen. Medan det har några intressanta data, om du aldrig har gått under rullningen har du missat några viktiga uppgifter. För korthetens skull vill jag titta på två mycket enkla motvärden som jag obsesserar över, den första från min testmiljö:
De två värdena att vara mycket uppmärksam på är Handler_read_rnd och Handler_read_rnd_next. Om de två värdena är röda, så finns det några frågor där ute som måste kontrolleras, som när MySQL gör en SELECT, läser den hela tabellen. I vissa fall kan det vara av design, som när du lägger ett index på ett bord tar det lite längre tid att skriva, och det tar lite mer utrymme. Men om du ser något så här:
chansen är att detta inte var av design. 141 miljoner förfrågningar om att läsa en rad i en fast position och 16 miljarder förfrågningar om att läsa nästa rad betyder antagligen att vi saknar ett index eller två (tusen). Självklart växer detta antal baserat på antalet förfrågningar, så ju mer en sökmotor indexerar din webbplats, eller ju fler besökare du har desto större blir ett litet missat index. Fulla bordsskanningar är fienden, och detta ger dig ett snabbt sätt att upptäcka hur nära den här fienden är till grindarna.
Ett annat bra bord för att kontrollera efterfrågan är att titta på valda och index direkt:
Den här tabellen ägnar särskild uppmärksamhet åt dina kontakter. En farlig kombination använder och indexerar inte på någon tabell, eftersom dina fulla bordsskanningar går exponentiellt på antalet samlingar som du använder. Ju mer normaliserade dina tabeller desto mer behöver du vara uppmärksam på dina index, liksom definitionen av de fält du går med.
Slutligen, beroende på en global variabel, vill du också kolla det här variabla tabellen:
Om du loggar in dina långsamma frågor visar den här variabelräknaren det nummer som har identifierats för observation, beroende på inställningen av den långa frågestunden. Dessa variabler finns på fliken variabler. En snabb titt i min testmiljö visar den här inställningen (för nu):
Dessa två flikar visar en hel del mer information, varav några är absolut nödvändiga för att ställa in din MySQL-server. PhpMyAdmin gör det riktigt enkelt för ens nybörjaren att upptäcka ett problem och att ha en grundläggande förståelse av vad det här problemet kan vara. Om ett värde är grönt, är vi bra. Om det är rött, behöver det lite uppmärksamhet. Det låter oss också förstå att vi gjort vissa framsteg. När vi startar om vår server, spolas alla sessionsvariablerna. Om vi har gjort förändringar kan vi se direkt från fladdermusen om vi gjorde någon inverkan.
Nu när vi har identifierat att vi behöver göra lite optimering, låt oss titta på några av de verktyg som vi ska använda innan vi hittar våra problem. Det första av verktygen, och förmodligen det mest användbara är att använda EXPLAIN. EXPLAIN ger oss i princip vår förfrågningsplan. Detta berättar vad MySQL planerar att göra med denna fråga innan den körs.
Utan att läsa på EXPLAIN kan produktionen inte betyda mycket för dig. Med hjälp av ett bord som jag skapade för en tidigare handledning, låt oss titta på en ooptimerad exekveringsplan. Mitt bord har bara två fält i det här fallet, en är sales_id och den andra är sale_amount. Här är frågan jag jobbar med:
SELECT sales_id, sale_amount FRÅN tutorial.sales ORDER BY sale_amount
På ytan är det en mycket enkel fråga. Att vara ett försäljningsbord kommer dock att växa och växa och växa. Jag genererade 200 poster för den tidigare handledningen och genom att göra en enkel SELECT med en ORDER BY-klausul tog det faktiskt lite längre tid än jag hade förväntat mig:
Den frågan med endast 200 poster kostar oss .15 sekunder. Låt oss använda EXPLAIN för att förstå hur MySQL ser den här frågan. Klicka bara på länken "Förklara SQL" för att se resultaten:
Liksom de flesta saker är det inte så mycket menar du inte förstår vad som sägs. Till någon som aldrig har kört en EXPLAIN på en fråga, kan detta också skrivas i hieroglyfer. Låt oss se om vi kan översätta till något lite förståeligt.
Den select_type berättar att MySQL ser denna SELECT som en enkel, går till ett bord och process. Om det fanns en fackförening eller en undersökning, skulle det visa vilken del av SELECT-uttalandet det här skulle ringa. Till exempel om jag skapar en fråga som har en underfråga:
VÄLJ sales_amount som belopp FRÅN Försäljning WHERE sales_id IN (SELECT sales_id FRÅN sales_force WHERE sales_id = 4)
Vi får en förklaring av detta:
Vilket berättar om själva frågan. I det här fallet har vår select_type ändrats för att säga att den första frågan är den primära, och sedan kommer MySQL att gå ut och utföra underfrågan, vilket är en vy, så det finns en annan underfråga att utföra, så slutar vi med de tre separata ids. MySQL-referenshandboken ger alla möjliga värden:
Tillbaka till vårt ursprungliga exempel:
Typen är den som ska uppmärksamma, eftersom den berättar om MySQL ska skanna hela tabellen, eller om det ska använda ett index för att snabbt hitta resultaten. Det här är den primära kolumnen att titta på när du optimerar dina frågor. Från ordningen bra till dålig är värdena:
Där du vill börja blir optimering av varje fråga som är antingen typen av index eller Allt. Om du kan befria din ansökan av dessa två typer, kommer din prestation att förbättras. Det här är mina vänner, där du börjar.
Resten av kolumnerna handlar om de index som MySQL ska använda, och antalet rader som det måste skanna innan det kan se om det finns ett giltigt resultat. När du blir av med "index" och "alla" typer, kommer de att vara användbara för att förstå exakt vad index MySQL använder för att utföra denna fråga. För att flytta en fråga uppför stegen börjar du anpassa dina index för att förbättra prestanda. I syfte att illustrera kommer jag att hålla fast med att rita "alla" eller fullständiga bordsskanningar.
Den sista kolumnen är kolumnen "extra". Den extra kolumnen ger dig information om frågan, huruvida en WHERE-klausul används eller inte, oavsett om det är omöjligt VAR, vilket betyder att denna fråga alltid kommer att returnera en NULL eftersom WHERE-klausulen gör det omöjligt att utföra. Det värde vi behöver vara mycket uppmärksam på, och befria oss från, är "Använda filort" som vi har i vårt exempel. När du ser det värdet måste MySQL göra ett annat pass genom resultaten för att sortera värdena. Så, när det gäller vår ursprungliga fråga:
SELECT sales_id, sale_amount FRÅN tutorial.sales ORDER BY sale_amount
Inte bara är MySQL skannar hela tabellen, men det måste skanna det två gånger för att sortera resultaten på grund av vårt ORDER BY-uttalande. Detta är uppenbarligen dubbelt dåligt. Vi kommer att optimera denna fråga och många fler i följande avsnitt.
I MySQL 5.0.37 blev ett annat verktyg tillgängligt för oss att använda i optimering, och det är MySQL-profilen. Dessutom har phpMyAdmin lagt till stöd för den här funktionen i version 2.11, så om du har båda dessa versioner tillgängliga har vi ett annat verktyg för att lägga till optimering.
Vad MySQL Profiler gör är att ge information om flaskhalsarna i våra frågor. Det låter oss se vad som händer under den faktiska exekveringen av våra frågor, vice vad EXPLAIN gör, vilket är att ge exekveringsplanen innan. Låt oss se vilken information vi kan få från phpMyAdmin från min ursprungliga dåliga fråga:
Om vi klickar på kryssrutan "Profiling" under vår fråga öppnas en ny värld med:
phpMyAdmin ger den verkliga exekveringstiden för den fråga som tillhandahölls. Vi kan nu se flaskhalsarna av var våra frågor, eller till och med bordsnivå struktur bör behandlas. Kanske ser vi behovet av loggfiler att det här bordet verkligen inte är skrivet så mycket som det läses från, så istället för InnoDB kan vi nu byta till MyISAM.
Det är lite nackdel med att använda phpMyAdmin när du använder MySQL Profiler, och det är att profiler är baserad på sessionen och phpMyAdmin förstör sessionen på varje sidvisning ... Problemet ger oss det är att vi inte har något sätt för att hålla en löpande total av profildata, men det finns ett sätt att lura phpMyAdmin, om än på ett kludigt sätt:
SET profilering = 1; SELECT sales_id, sale_amount FRÅN tutorial.sales ORDER BY sale_amount; Visa profiler
Vilket resulterar i
Eftersom vi utför flera frågor måste du använda avgränsaren. Detta visar att min fråga är query_id 1. Varje efterföljande tid kör jag denna fråga, det är fortfarande query_id 1 eftersom min session förstörs vid start. Jag är inte säker på om det här är av design, en bugg eller okunnighet från min sida att phpMyAdmin förstör sessionen med QUIT-kommandot, men vi kan bara arbeta runt det här problemet. MySQL har en underbar skrivning på att använda profiler av Robin Schumacher, och jag ska använda lite av Robins fråga för att få antalet operationer i phpMyAdmin:
SET profilering = 1; SELECT sales_id, sale_amount FRÅN tutorial.sales ORDER BY sale_amount; SELECT min (seq) som sekvens, tillstånd, räkna (*) som operationer, runda (summa (varaktighet), 5) som varaktighet FRÅN information_schema.profiling VAR query_id = 1 GRUPP med tillstånd ORDER med seq;
Återigen, inte idealisk med phpMyAdmin, men vi får fortfarande det vi vill ha i slutet:
Innan vi lägger allt vi lärde oss tillsammans, låt oss också titta på hur man kan fånga frågor genom att använda MySQLs loggfiler. Vi kan fånga varje fråga som MySQL körs i tabellen mysql.general_log. Genom att köra det här kommandot:
SET GLOBAL general_log = 'ON'; SET GLOBAL log_output = 'TABLE';
Vi kan nu få en rekord för alla frågor som körs, oberoende av källan. Medan denna operation är dyr, och jag skulle inte köra den på en produktionsinställning, ger det oss en tydlig och koncis metod för att få alla våra frågor och ordningen för deras utförande från våra applikationer. Kort sagt kan detta vara det mest värdefulla SQL-sökverktyget du har i din verktygslåda. Genom att ställa in dessa två GLOBAL vars har vi det sista steget för att få några praktiska optimeringstekniker.
Här är en förkortad produktion från mysql.general_log-tabellen med hjälp av denna fråga:
VÄLJ event_time, command_type, argument FRÅN mysql.general_log ORDER BY event_time
producerar detta:
Jag har i grund och botten min fråga, tillsammans med allt som phpMyAdmin har gjort i bakgrunden. Om jag tömmer bordet före varje nytt kommando, har jag något jag kan arbeta med på varje sidvy eller AJAX-samtal jag gör från mina applikationer. För att tömma loggen, försöker vi helt enkelt bordet som så:
TRUNCATE mysql.general_log
Truncate är ett mycket bättre uttalande att använda här än DELETE FROM, eftersom DELETE-raderingen raderar rad för rad, där som TRUNCATE tömmer hela bordet på en gång.
När du är klar med din optimering behöver du helt enkelt stänga av din söklogg med det här kommandot:
SET GLOBAL general_log = 'OFF';
Den allmänna logg blir dyr över tiden, och sänker definitivt prestandan i din ansökan. Jag håller den avstängd mellan mina optimeringar, så att jag kan få en organisk känsla för utförandet av det jag skriver. Med det sagt, under utveckling håller jag alltid den långsamma frågeloggen på, eftersom jag vill se mina långsammare frågor som ett snabbt optimeringsverktyg. Du kan enkelt göra det här:
SET GLOBAL slow_query_log = 'ON'; SET GLOBAL log_queries_not_using_indexes = 'ON'; SET GLOBAL log_output = 'TABLE';
och vi kan kolla det från vår Variables-flik från vår välkomstsida:
För att se utmatningen behöver vi bara kolla mysql.slow_log eller vi kan använda en fråga så här:
VÄLJ sql_text FRÅN mysql.slow_log
Vilket ger mig de faktiska frågorna som loggades så långsamt:
Nu kan vi helt säga detta och använda phpMyAdmin som ett relativt anständigt sökverktyg. Låt oss börja med det första frågesexemplet:
EXPLAIN SELECT sales_id, sale_amount FRÅN tutorial.sales ORDER BY sale_amount
Vilket ger en produktion av:
Vi vet att vi behöver få minst en INDEX på denna tabell. Låt oss sluta och tänka på hur denna tabell används. Det är en enkel look-up-tabell för att gå med i en sales_force-tabell för att berätta för oss att de gjorde en försäljning som var av det registrerade antalet. Om allt vi någonsin gör är att gå med i den här tabellen på sales_id så är det vad vi behöver indexera genom att klicka på detaljer länken:
Då kan vi bara definiera det här indexet:
Vår ursprungliga fråga ger oss fortfarande en fullständig genomsökning, men i en praktisk applikation:
SELECT sfn.first_name, sfn.last_name, s.sale_amount FRÅN sales_force_normalized sfn INNER JOIN sälj s ON sfn.sales_id = s.sales_id
Låt oss se om det här är bättre:
Nu kommer vi någonstans. Men om vi gör något så här:
SELECT max (sale_amount) FRÅN försäljning
Då är vi tillbaka i samma båt för att göra en full skanning av bordet. I det här fallet kan vi bara redigera indexet och lägga till sales_amount:
Vilket förbättrar oss från riktigt dåligt till bara dåligt:
Eller vi kan lägga till ett nytt index på just beloppet:
Och vi har det underbara resultatet av:
Det betyder att MySQL inte ens behöver öppna bordet, eftersom det bara måste titta på indexet. Vi har nu träffat den absoluta optimala nivån för denna COUNT-funktion. Kolla in hur lång tid det tog att utföra denna fråga nu:
Och i god takt, låt oss klicka på kryssrutan Profiling på frågan för att se några flaskhalsar nu:
Vi har spelat med att låtsas och frågar databaser, men låt oss sätta denna handledning till testet. Jag har ett lager WordPress-installerat, med bara Lorem Ipsum-plugin för att lägga till cirka 5000 inlägg och 11 000 kommentarer, så vi kan bara lägga en liten belastning på MySQL när vi gör våra val.
Låt oss börja logga in våra frågor igen från phpMyAdmin och avkorta även de långsamma och allmänna loggarna så att vi kan se vad som händer när vi laddar en sida från WordPress:
SET GLOBAL general_log = 'ON'; TRUNCATE mysql.slow_log; TRUNCATE mysql.general_log;
Det kommer att vara några artefakter i general_log då phpMyAdmin orsakar viss aktivitet inom MySQL, men vi borde kunna få allt i ordning när jag laddar upp min indexsida från WordPress vid denna tidpunkt och om vi använder ett LIKE-villkor kan få mestadels bara WordPress resultat eftersom tabellerna är prefixed med wp_:
SELECT event_time, command_type, argument FRÅN mysql.general_log VAR argument som "% wp_%" ORDER BY event_time
Vilket ger oss ett rimligt resultat av:
Nu vet vi att WordPress bara ger oss 11 frågor om att ladda indexsidan med en vacker vaniljinstallation. Låt oss hitta något för att optimera att de kanske har missat. Om vi tar den allra första frågan som körs när WordPress laddar:
EXPLAIN SELECT option_name, option_value FROM wp_options VAR autoload = 'ja'
Vi finner att detta inte är optimerat:
Låt oss ta en titt på vad de gjorde genom phpMyAdmin:
Vi ser att det finns ett index på option_name, men det finns inget index på autoload, vilket är villkoret som anges på indexsidan. Låt oss lägga till det och se om vi inte kan optimera den centrala WordPress-installationen bara lite:
Eftersom autoload är varchar och antingen "ja" eller "nej" från vad jag ser kan jag begränsa mitt indexvärde till 1. Betydelsen, det ser nu antingen "y" eller "n" vilket sänker vår tid ännu större. Låt oss se EXPLAIN efter att vi har optimerat:
Vi har gått från riktigt dålig, till den fjärde bästa typen. Inte illa för ett par minuters arbete. Beviljas, WordPress stämde inte på det här värdet, men beroende på belastningen på din blogg hjälper varje liten bit. Beviljas nu, skrivningarna tar längre tid eftersom vi måste indexera vår "y" eller "n" för varje rad som är skriven.
Om vi går lite längre kan vi även se MySQL Profiler i åtgärd genom att bara kryssrutan "Profiling". Nu ser vi att vår fråga verkligen surrar direkt:
Optimering är inte lätt, det är inte heller väldigt roligt. Men när du ignorerar detta utvecklingssteg kommer det alltid tillbaka för att hemsöka dig. Jag tror att det är relativt lätt att använda verktygen i phpMyAdmin för att få ett ganska bra optimeringsperspektiv på dina applikationer. Det sägs att nya verktyg läggs hela tiden, till exempel Jet Profiler som tar vad jag just gjort i en realtid och grafisk natur.
.
Accentsconagua