Share
Ik heb phpMyAdmin al meer dan tien jaar gebruikt. In mijn vroege jaren met de tool, had ik gewoon iets nodig dat me de structuur van de tafel kon laten zien en me snel de gegevens erin kon geven. Naarmate mijn behoeften zijn toegenomen, zijn de hulpprogramma's ook opgenomen in phpMyAdmin waardoor ik terugkom als mijn primaire MySQL-tool, zelfs met optimalisatie.
Ik heb het genoegen gehad om met verschillende databases te werken. Elk heeft zijn nadelen, en elk heeft zijn sterke punten. Wanneer ik een keuze krijg, ga ik meestal terug naar MySQL, ondanks dat ik te goedkoop ben om de MySQL Enterprise te kopen. In plaats daarvan moet ik het doen met phpMyAdmin als mijn belangrijkste profileerhulpprogramma. Het werkt goed voor mij, maar ik moest behoorlijk wat onderzoek doen om te begrijpen waar ik naar kijk terwijl ik mijn applicaties profileerde. Ik hoop dit door te geven op een manier die begrepen kan worden door de beginner, tot aan de doorgewinterde professional.
Optimalisatie kost tijd. Managers, klanten en collega's houden er trouwens niet van om te horen dat een project achterloopt op schema vanwege optimalisatie. Vaak haasten we de optimalisatie om aan die benchmarks te voldoen. Uiteindelijk doen we niemand iets gunnen. De mooiste webapplicatie ter wereld zorgt ervoor dat u terugkerende klanten krijgt als het 10 seconden duurt om elke pagina te laden. Evenzo, als we wachten om te optimaliseren tot het einde van onze projecten, is de kans groot dat er nog veel meer werk te doen is dan wanneer we hadden gecontroleerd terwijl het project doorging.
Een paar biljetten voordat we in het vlees en de aardappelen komen. Ten eerste ga ik niet in op MySQL Tuning, omdat het een beetje buiten de scope van deze tutorial ligt. Hoewel tunen een optimalisatie is, is het naar mijn mening een onderwerp op zich. Ik zal kort enkele mogelijkheden vermelden om te optimaliseren hoe je je server kunt tunen, maar de vermeldingen zullen kort zijn. Daarnaast zal ik vooral kijken naar MyISAM-tabellen en niet naar InnoDB-tabellen. De vuistregel is dat als je veel gegevens schrijft, InnoDB gebruikt, maar als je SELECT veel meer gebruikt, gebruik dan MyISAM. Ook ben ik niet bezig met tabelniveau REPARATIE, OPTIMALISEREN, CONTROLE en ANALYSE omdat deze zelfstudie query-optimalisatie behandelt met phpMyAdmin. Nogmaals, dit is een beetje buiten het bereik van deze tutorial.
Ten slotte ga ik naar WordPress kijken als een voorbeeld uit de echte wereld. Ik zal de eerste zijn om u te vertellen dat ik geen expert ben in WordPress, maar ik kan de gegenereerde query's bekijken met de beste ervan. Van wat ik heb gezien, is de database met WordPress goed geïndexeerd, maar zodra we dingen beginnen toe te voegen die buiten die hoofdkernbestanden liggen, zijn die indices misschien niet de beste voor wat we nodig hebben.
"Optimalisatie kost tijd. Managers, klanten en collega's houden er overigens niet van om te horen dat een project achterloopt op schema vanwege optimalisatie."
Het korte antwoord is ja.
Het lange antwoord is phpMyAdmin geeft ons een kans om te zien of we onze vragen moeten optimaliseren en hoe slecht we ze moeten optimaliseren. Ik kan me voorstellen dat je dit scherm meer dan eens hebt gezien als je phpMyAdmin hebt gebruikt:
Het is het standaard startscherm voor phpMyAdmin. Tenzij u op zoek bent naar manieren om te optimaliseren, gaat u wellicht meteen naar uw tabellen in het menu aan de linkerkant en ziet u nooit het tabbladmenu bovenaan. Dat menu, met name de tabbladen Status en Variabelen, zijn de plaatsen waar we beginnen.
Laten we beginnen met het statusscherm, dat misschien wel de belangrijkste tool is die phpMyAdmin biedt:
Dit is de bovenkant van het statusscherm. Hoewel het een aantal interessante gegevens bevat, heb je, als je nog nooit onder de scroll bent gegaan, een aantal zeer belangrijke informatie gemist. Kortheidshalve wil ik kijken naar twee zeer eenvoudige tegenwaarden die ik obsedeer, de eerste uit mijn testomgeving:
De twee waarden waaraan zeer veel aandacht moet worden besteed zijn Handler_read_rnd en Handler_read_rnd_next. Als die twee waarden in het rood staan, zijn er enkele queries die moeten worden gecontroleerd, zoals wanneer MySQL een SELECT uitvoert, leest het de hele tabel. In sommige gevallen kan dit een ontwerp zijn, zoals wanneer u een index op een tabel plaatst, duurt het iets langer om te schrijven en duurt het een beetje meer ruimte. Als u echter zoiets ziet als dit:
de kans is groot, dit was niet bij ontwerp. 141 miljoen verzoeken om een rij op een vaste positie te lezen, en 16 miljard verzoeken om de volgende rij te lezen, betekent waarschijnlijk dat we een index of twee (duizend) missen. Vanzelfsprekend groeit dit aantal op basis van het aantal verzoeken, dus hoe meer een zoekmachine uw site indexeert, of hoe meer bezoekers u heeft, hoe groter een kleine gemiste index wordt. Volledige tafelscans zijn de vijand, en dit geeft u een snelle manier om te zien hoe dicht die vijand bij de poorten is.
Een andere geweldige tabel om te controleren op queryprestaties neemt een kijkje op selecties en indexen rechtstreeks:
In deze tabel wordt speciale aandacht besteed aan uw joins. Een gevaarlijke combinatie gebruikt en indexeert niet aan beide tafels, omdat uw volledige tafelscans exponentieel stijgen op het aantal joins dat u gebruikt. Hoe genormeerder uw tabellen, hoe meer u op uw indexen moet letten, evenals de definitie van de velden waaraan u deelneemt.
Ten slotte, afhankelijk van een globale variabele, wilt u deze variabelietabel ook controleren:
Als u uw langzame query's registreert, geeft deze variabele teller het aantal weer dat is geïdentificeerd voor observatie, afhankelijk van de instelling van lange querytijd. Die variabelen zijn te vinden op het tabblad variabelen. Een snelle blik in mijn testomgeving toont deze instelling (voorlopig):
Deze twee tabbladen tonen nogal wat meer informatie, waarvan sommige absoluut essentieel zijn voor het afstemmen van uw MySQL-server. PhpMyAdmin maakt het zelfs voor beginnende gebruikers gemakkelijk om een probleem te herkennen en een basisbegrip te hebben van wat dat probleem zou kunnen zijn. Als een waarde groen is, zijn we goed. Als het rood is, heeft het wat aandacht nodig. Het laat ons ook begrijpen dat we enige vooruitgang hebben geboekt. Wanneer we onze server opnieuw opstarten, worden deze sessievariabelen allemaal doorgespoeld. Als we wijzigingen hebben aangebracht, kunnen we meteen zien of we invloed hebben gehad.
Nu we hebben vastgesteld dat we iets moeten optimaliseren, laten we eens kijken naar enkele hulpprogramma's die we gaan gebruiken voordat we onze problemen hebben ontdekt. De eerste van de hulpprogramma's en waarschijnlijk het nuttigst is om EXPLAIN te gebruiken. EXPLAIN geeft ons in feite ons query-uitvoeringsplan. Dit vertelt ons wat MySQL met deze query van plan is voordat het wordt uitgevoerd.
Zonder te lezen op EXPLAIN, kan de uitvoer niet veel voor u betekenen. Met behulp van een tabel die ik voor een eerdere tutorial heb gemaakt, laten we een niet-geoptimaliseerd uitvoeringsplan bekijken. Mijn tabel heeft in dit geval slechts twee velden, één daarvan is sales_id en de andere is sale_amount. Hier is de vraag waarmee ik werk:
SELECT sales_id, sale_amount FROM tutorial.sales BESTELLING BY sale_amount
Op het eerste gezicht is dit een heel eenvoudige vraag. Omdat het een verkooptafel is, zal de tafel groeien en groeien en groeien. Ik heb 200 records gegenereerd voor de vorige zelfstudie en door een eenvoudige SELECT uit te voeren met een ORDER BY-component, duurde het eigenlijk een beetje langer dan ik had verwacht:
Die vraag met slechts 200 records kostte ons .15 seconden. Laten we EXPLAIN gebruiken om te begrijpen hoe MySQL deze query ziet. Klik gewoon op de link 'SQL verklaren' om de resultaten te bekijken:
Zoals de meeste dingen, heeft dit weinig zin, tenzij je begrijpt wat er wordt gezegd. Voor iemand die nog nooit een EXPLAIN op een query heeft uitgevoerd, kan dit net zo goed in hiërogliefen worden geschreven. Laten we kijken of we iets kunnen vertalen naar iets begrijpelijkers.
Het select_type vertelt ons dat MySQL deze SELECT als een simpele beschouwt, naar een tabel gaat en verwerkt. Als er een unie of een subquery was, zou dit laten zien welk deel van de SELECT-instructie dit zou aanroepen. Als ik bijvoorbeeld een query maak met een subquery:
SELECT sale_amount als bedrag FROM sales WHERE sales_id IN (SELECT sales_id FROM sales_force WHERE sales_id = 4)
We krijgen hiervan een EXPLAIN:
Welke ons over de vraag zelf vertelt. In dit geval is ons select_type veranderd om te zeggen dat de eerste query de primaire is, en dan gaat MySQL uit en voert de subquery uit, wat een weergave is, dus er is nog een subquery om uit te voeren, daarom eindigen we met de drie afzonderlijke ids. De MySQL Reference-handleiding geeft alle mogelijke waarden:
Terug naar ons oorspronkelijke voorbeeld:
Het type is degene om op te letten, omdat het vertelt of MySQL de hele tabel gaat scannen, of dat het een index zal gebruiken om snel de resultaten te vinden. Dit is de primaire kolom om naar te kijken wanneer u uw zoekopdrachten optimaliseert. Van de volgorde goed tot slecht, de waarden zijn:
Waar u wilt beginnen, is het optimaliseren van elke vraag die het type is inhoudsopgave of allemaal. Als u uw toepassing van deze twee typen kunt verwijderen, gaat uw prestatie verbeteren. Dit zijn mijn vrienden, waar je begint.
De rest van de kolommen behandelen de indexen die MySQL zal gebruiken, en het aantal rijen dat het zal moeten scannen voordat het kan zien of er een geldig resultaat is. Als u de "index" - en "alle" -typen verwijdert, zijn deze handig om precies te begrijpen welke index MySQL gebruikt om deze query uit te voeren. Als u een query op de ladder wilt verplaatsen, begint u uw indexen aan te passen om de prestaties te verbeteren. Ter illustratie, ik blijf vasthouden aan het "ontdoen van" alle of volledige tafelscans.
De laatste kolom is de kolom 'extra'. De extra kolom vertelt u informatie over de vraag, of een WHERE-clausule al dan niet wordt gebruikt, ongeacht of deze een onmogelijke WHERE is, wat betekent dat deze query altijd een NULL retourneert omdat de WHERE-component het onmogelijk maakt om uit te voeren. De enige waarde waar we heel veel aandacht aan moeten schenken en die we moeten verwijderen, is het "Using filesort" dat we in ons voorbeeld hebben. Wanneer u die waarde ziet, moet MySQL de resultaten opnieuw doorgeven om de waarden te sorteren. Dus in het geval van onze oorspronkelijke vraag:
SELECT sales_id, sale_amount FROM tutorial.sales BESTELLING BY sale_amount
MySQL scant niet alleen de hele tabel, maar moet deze twee keer scannen om de resultaten te sorteren vanwege onze ORDER BY-instructie. Dit is duidelijk dubbel slecht. We optimaliseren deze query en nog veel meer in de volgende secties.
In MySQL 5.0.37 is er nog een tool beschikbaar die we kunnen gebruiken voor optimalisatie, en dat is de MySQL-profiler. Bovendien heeft phpMyAdmin ondersteuning voor deze functie toegevoegd in versie 2.11, dus als u beide versies beschikbaar hebt, hebben we een andere tool om toe te voegen aan optimalisatie.
Wat de MySQL Profiler doet, is informatie geven over de knelpunten van onze vragen. Het stelt ons in staat om te zien wat er gebeurt gedurende de daadwerkelijke uitvoering van onze vragen, in tegenstelling wat EXPLAIN doet, wat het uitvoeringsplan is voor. Laten we eens kijken welke informatie we kunnen krijgen van phpMyAdmin van mijn originele slechte zoekopdracht:
Als we klikken op het selectievakje "Profilering" onder onze vraag, opent een nieuwe wereld met:
phpMyAdmin biedt de daadwerkelijke uitvoeringstijden van de opgegeven query. We kunnen nu de knelpunten zien van waar onze vragen of zelfs de structuur van het tabelniveau moeten worden aangepakt. Misschien zien we de noodzaak van logbestanden dat deze tabel echt niet zo vaak wordt geschreven als waarop wordt gelezen, dus in plaats van InnoDB kunnen we deze nu omschakelen naar MyISAM.
Er is een beetje een nadeel aan het gebruik van phpMyAdmin bij het gebruik van de MySQL Profiler, en dat is dat de profiler gebaseerd is op de sessie, en phpMyAdmin vernietigt de sessie op elke paginaweergave ... Het probleem dat dit ons geeft is dat we geen manier hebben om een lopend totaal van de profileringsgegevens bij te houden, maar er is een manier om phpMyAdmin te misleiden, zij het op een slordige manier:
SET-profilering = 1; SELECT sales_id, sale_amount FROM tutorial.sales BESTELLING BY sale_amount; SHOW-profielen;
Wat resulteert in:
Omdat we meerdere query's uitvoeren, moet u het scheidingsteken gebruiken. Dit laat zien dat mijn query query_id is 1. Elke volgende keer dat ik deze query uitvoer, is het nog steeds query_id 1 omdat mijn sessie bij het opstarten wordt vernietigd. Ik weet niet zeker of dit door het ontwerp, een bug of onwetendheid van mijn kant is dat phpMyAdmin de sessie vernietigt met de opdracht QUIT, maar we kunnen dit probleem een beetje omzeilen. MySQL heeft een prachtige beschrijving van het gebruik van de profiler door Robin Schumacher, en ik ga een beetje Robin's query gebruiken om het aantal bewerkingen in phpMyAdmin te krijgen:
SET-profilering = 1; SELECT sales_id, sale_amount FROM tutorial.sales BESTELLING BY sale_amount; SELECT min (seq) als volgorde, staat, tel (*) als bewerkingen, rond (som (duur), 5) als duur FROM information_schema.profiling WHERE query_id = 1 GROEP op status ORDER by seq;
Nogmaals, niet ideaal met phpMyAdmin, maar we krijgen uiteindelijk toch wat we willen:
Voordat we alles wat we hebben geleerd samen leggen, laten we ook kijken hoe je zoekopdrachten kunt vastleggen door de logbestanden van MySQL te gebruiken. We kunnen elke vraag vastleggen dat MySQL de mysql.general_log-tabel tegenkomt. Door deze opdracht uit te voeren:
SET GLOBAL general_log = 'ON'; SET GLOBAL log_output = 'TABLE';
We kunnen nu een record hebben voor alle query's die worden uitgevoerd, ongeacht de bron. Hoewel deze bewerking duur is en ik het niet zou uitvoeren op een productie-instelling, geeft het ons een duidelijke en beknopte methode om al onze vragen en de volgorde van uitvoering ervan uit onze applicaties te halen. Kortom, dit kan de meest waardevolle tool voor het optimaliseren van SQL-query's zijn die u in uw toolbox hebt. Door deze twee GLOBAL vars in te stellen, hebben we de laatste stap naar een aantal praktische optimalisatietechnieken.
Hier volgt een verkorte uitvoer van de tabel mysql.general_log met behulp van deze query:
SELECT event_time, command_type, argument FROM mysql.general_log ORDER BY event_time
produceert dit:
Ik heb in principe mijn vraag, samen met alles wat phpMyAdmin op de achtergrond doet. Als ik de tabel leegmaak vóór elke nieuwe opdracht, heb ik iets waarmee ik kan werken op elke paginaweergave of AJAX-aanroep die ik maak vanuit mijn toepassingen. Om het log leeg te maken, TRUNCEREN we eenvoudig de tabel zoals:
TRUNCATE mysql.general_log
Truncate is een veel betere verklaring om hier te gebruiken dan DELETE FROM, omdat de DELETE-instructie rij per rij verwijdert, waarbij TRUNCATE de hele tafel in één keer leegmaakt.
Zodra u klaar bent met uw optimalisatie, hoeft u alleen maar uw querylog uit te schakelen met deze opdracht:
SET GLOBAL general_log = 'OFF';
Het algemene logboek wordt duur in de tijd en vertraagt zeker de prestaties van uw toepassing. Ik houd het uitgeschakeld tussen mijn optimalisaties, zodat ik een organisch gevoel kan krijgen voor de uitvoering van wat ik schrijf. Dat gezegd hebbende, houd ik in ontwikkeling altijd het logboek voor langzame query's ingeschakeld omdat ik mijn langzamere query's wil zien als een snelle optimalisatietool. U kunt dit eenvoudig doen:
SET GLOBAL slow_query_log = 'ON'; SET GLOBAL log_queries_not_using_indexes = 'ON'; SET GLOBAL log_output = 'TABLE';
en we kunnen dat controleren op ons tabblad Variabelen van onze welkomstpagina:
Om de uitvoer te zien, hoeven we alleen het mysql.slow_log te controleren of we kunnen een vraag als deze gebruiken:
SELECT sql_text FROM mysql.slow_log
Welke me de eigenlijke vragen geeft die als langzaam werden geregistreerd:
Nu kunnen we dit allemaal samenvoegen en phpMyAdmin gebruiken als een redelijk fatsoenlijke queryoptimalisatietool. Laten we beginnen met het eerste vraagvoorbeeld:
EXPLAIN SELECT sales_id, sale_amount FROM tutorial.sales ORDER BY sale_amount
Die een output produceert van:
We weten dat we ten minste één INDEX op deze tafel moeten krijgen. Laten we stoppen en nadenken over hoe deze tabel wordt gebruikt. Het is een eenvoudige opzoektabel om lid te worden van een sales_force-tabel om ons te laten weten dat ze een uitverkoop hebben gedaan die van het opgenomen bedrag was. Als het enige wat we ooit doen is om zich aan te sluiten tegen deze tabel op de sales_id, dan is dat wat we moeten indexeren door op de link met details te klikken:
We kunnen dan die index net zo definiëren:
Onze oorspronkelijke vraag geeft ons nog steeds een volledige scan, maar in een praktische toepassing:
SELECT sfn.first_name, sfn.last_name, s.sale_amount FROM sales_force_normalized sfn INNER JOIN sales s ON sfn.sales_id = s.sales_id
Laten we kijken of dit beter is:
Nu komen we ergens. Als we echter zoiets doen als dit:
SELECT max (sale_amount) FROM sales
Dan zijn we terug in dezelfde boot van een volledige scan van de tafel. In dit geval kunnen we de index bewerken en het verkoopbedrag toevoegen:
Wat ons van echt slecht tot slecht verbetert:
Of we kunnen een nieuwe index toevoegen voor alleen het bedrag:
En we hebben het prachtige resultaat van:
Dat betekent dat MySQL niet eens de tafel hoeft te openen, omdat het gewoon in de index hoeft te kijken. We hebben nu het absolute optimale niveau voor deze COUNT-functie bereikt. Bekijk hoe lang het duurde om deze query nu uit te voeren:
Laten we voor de goede orde klikken op het selectievakje Profilering in de query om nu eventuele knelpunten te zien:
We hebben gespeeld met doen alsof vragen, en doen alsof databases, maar laten we deze tutorial op de proef stellen. Ik heb een stock WordPress-installatie, met alleen de Lorem Ipsum-plug-in om ongeveer 5000 berichten en 11.000 reacties toe te voegen, dus we kunnen MySQL een beetje onder druk zetten wanneer we onze selecties maken.
Laten we beginnen met het opnieuw inloggen van onze vragen vanuit phpMyAdmin en ook de langzame en algemene logs afkappen, zodat we kunnen zien wat er gebeurt als we een pagina laden vanuit WordPress:
SET GLOBAL general_log = 'ON'; TRUNCATE mysql.slow_log; TRUNCATE mysql.general_log;
Er komen een paar artefacten in de general_log omdat phpMyAdmin enige activiteit veroorzaakt binnen MySQL, maar we zouden in staat moeten zijn om alles op orde te krijgen wanneer ik mijn indexpagina van WordPress herlaad op dit punt, en als we een LIKE-conditie gebruiken, kunnen meestal alleen WordPress-resultaten krijgen, omdat de tabellen worden voorafgegaan door wp_:
SELECT event_time, command_type, argument FROM mysql.general_log WHERE argument LIKE "% wp_%" ORDER BY event_time
Wat ons een redelijk resultaat geeft van:
Nu weten we dat WordPress ons eenvoudigweg 11 query's geeft over het laden van de indexpagina met een mooie vanille-installatie. Laten we iets vinden dat is geoptimaliseerd dat ze mogelijk hebben gemist. Als we de allereerste query uitvoeren die wordt uitgevoerd wanneer WordPress wordt geladen:
EXPLAIN SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes'
We vinden dat dit niet is geoptimaliseerd:
Laten we eens kijken wat ze hebben gedaan met phpMyAdmin:
We zien dat er een index op option_name staat, maar er is geen index op autoload, wat de voorwaarde specificeert op de indexpagina. Laten we het toevoegen en kijken of we de kern WordPress-installatie niet een beetje kunnen optimaliseren:
Aangezien autoload varchar is en ofwel "ja" of "nee" is van wat ik zie, kan ik mijn indexwaarde beperken tot 1. Dit betekent dat het nu "y" of "n" ziet, waardoor onze tijd nog groter wordt. Laten we de EXPLAIN bekijken nadat we zijn geoptimaliseerd:
We zijn van echt slecht naar het vierde beste type gegaan. Niet slecht voor een paar minuten werk. Toegegeven, WordPress was niet aan het stikken voor deze waarde, maar afhankelijk van de belasting van je blog, helpt elk klein beetje. Toegegeven, het schrijven duurt langer, omdat we onze "y" of "n" moeten indexeren voor elke regel die is geschreven.
Als we iets verder gaan, kunnen we de MySQL Profiler ook in actie zien door het selectievakje "Profilering" aan te vinken. Nu zien we dat onze vraag nu echt zoemt:
Optimalisatie is niet eenvoudig en het is ook niet echt heel leuk. Wanneer je deze stap van ontwikkeling echter negeert, komt het altijd weer bij je terug. Ik geloof dat het relatief eenvoudig is om de hulpprogramma's in phpMyAdmin te gebruiken om een behoorlijk goede optimalisatie van je applicaties te krijgen. Dat gezegd hebbende, er zijn altijd nieuwe tools toegevoegd, zoals Jet Profiler, die neemt wat ik zojuist heb gedaan in een real-time, en grafische aard.
.
Accentsconagua