Share
Databasoperationer tenderar ofta att vara huvudflaskhalsen för de flesta webbapplikationer idag. Det är inte bara DBA: s (databasadministratörer) som måste oroa sig för dessa prestandafrågor. Vi som programmerare behöver göra vår del genom att strukturera tabeller ordentligt, skriva optimerade frågor och bättre kod. I den här artikeln ska jag lista några MySQL-optimeringstekniker för programmerare.
Innan vi börjar, var medveten om att du kan hitta massor av användbara MySQL-skript och verktyg på Envato Market.
MySQL skript och verktyg på Envato Market De flesta MySQL-servrar har aktiverad sökning av caching. Det är en av de mest effektiva metoderna för att förbättra prestanda, som hanteras tyst av databasmotorn. När samma fråga körs flera gånger hämtas resultatet från cacheminnet, vilket är ganska snabbt.
Det största problemet är att det är så enkelt och dolt från programmeraren, de flesta av oss tenderar att ignorera det. Vissa saker vi gör kan faktiskt förhindra att sökcachen utför sin uppgift.
Accentsconagua
// query cache fungerar inte $ r = mysql_query ("SELECT användarnamn FRÅN användare WHERE signup_date> = CURDATE ()"); // query cache fungerar! $ idag = datum ("y-m-d"); $ r = mysql_query ("SELECT användarnamn FROM användare WHERE signup_date> = '$ today'"); Anledningen till att fråga cache inte fungerar i den första raden är användningen av funktionen CURDATE (). Detta gäller för alla icke-deterministiska funktioner som NU () och RAND () etc ... Eftersom funktionens återkomstresultat kan förändras bestämmer MySQL att deaktivera query caching för den frågan. Allt vi behövde göra är att lägga till en extra rad PHP före frågan för att förhindra att detta händer.
Använda EXPLAIN-sökordet kan ge dig inblick i vad MySQL gör för att utföra din fråga. Detta kan hjälpa dig att hitta flaskhalsar och andra problem med din fråga eller bordstrukturer.
Resultaten av en EXPLAIN-fråga visar vilka index som används, hur tabellen skannas och sorteras etc ...
Ta en SELECT-fråga (helst en komplex med anslutningar) och lägg till sökordet EXPLAIN framför den. Du kan bara använda phpmyadmin för detta. Det visar resultaten i ett fint bord. Till exempel, låt oss säga att jag glömde att lägga till ett index till en kolumn som jag utför går med på:
Efter att du har lagt till indexet i group_id-fältet:
Nu istället för att skanna 7883 rader kommer den bara att skanna 9 och 16 rader från de 2 tabellerna. En bra tumregel är att multiplicera alla tal under kolumnen "rader" och din sökprestanda kommer att vara något proportionell mot det resulterande numret.
Ibland när du frågar dina bord vet du redan att du letar efter bara en rad. Du kanske hämtar en unik post, eller du kan bara bara kontrollera förekomsten av ett antal poster som uppfyller din WHERE-klausul.
I sådana fall kan lägga till LIMIT 1 till din fråga öka prestanda. På så sätt slutar databasmotorn att skanna efter poster efter att den bara har hittat 1, istället för att gå igenom hela tabellen eller indexet.
// har jag några användare från Alabama? // vad som inte gör: $ r = mysql_query ("VÄLJ * FRÅN användare WHERE state = 'Alabama'"); om (mysql_num_rows ($ r)> 0) // ... // mycket bättre: $ r = mysql_query ("VÄLJ 1 FRÅN användare WHERE state = 'Alabama' LIMIT 1"); om (mysql_num_rows ($ r)> 0) // ... Indexer är inte bara för de primära nycklarna eller de unika nycklarna. Om det finns några kolumner i ditt bord som du ska söka efter, bör du nästan alltid indexera dem.
Som du kan se gäller denna regel också för en partiell strängsökning som "last_name LIKE 'a%'". När du söker från början av strängen kan MySQL använda indexet på den kolumnen.
Du bör också förstå vilka typer av sökningar som inte kan använda de vanliga indexerna. När du till exempel söker efter ett ord (t ex "WHERE post_content LIKE '% apple%'") ser du inte någon fördel av ett normalt index. Du kommer att vara bättre med hjälp av mysql fulltextsökning eller bygga din egen indexeringslösning.
Om din ansökan innehåller många JOIN-frågor måste du se till att kolumnerna du går med är indexerade på båda tabellerna. Detta påverkar hur MySQL internt optimerar anslutningsoperationen.
Kolumnerna som förenas måste också vara samma typ. Om du till exempel kommer med en DECIMAL-kolumn till en INT-kolumn från en annan tabell, kommer MySQL inte att kunna använda minst ett av indexen. Även teckenkodningarna måste vara samma typ för strängtyps kolumner.
// söker företag i mitt tillstånd $ r = mysql_query ("VÄLJ company_name FROM användare LEFT JOIN companies ON (users.state = companies.state) WHERE users.id = $ user_id"); // båda statliga kolumnerna bör indexeras // och de båda borde ha samma typ och teckenkodning // eller MySQL kan göra fullständiga bordsskanningar Detta är ett av de knep som låter coolt i början, och många rookieprogrammers faller för denna fälla. Du kanske inte inser vilken typ av fruktansvärt flaskhals du kan skapa när du börjar använda detta i dina frågor.
Om du verkligen behöver slumpmässiga rader ur dina resultat finns det mycket bättre sätt att göra det. Beviljas att det krävs ytterligare kod, men du kommer att förhindra en flaskhals som blir exponentiellt värre när dina data växer. Problemet är att MySQL måste utföra RAND () -operation (som tar processorkraft) för varje rad i tabellen innan du sorterar den och ger dig bara 1 rad.
// vad som inte gör: $ r = mysql_query ("VÄLJ användarnamn FRÅN användare ORDER PER RAND () LIMIT 1"); // mycket bättre: $ r = mysql_query ("SELECT count (*) FROM user"); $ d = mysql_fetch_row ($ r); $ rand = mt_rand (0, $ d [0] - 1); $ r = mysql_query ("SELECT användarnamn FRÅN användare LIMIT $ rand, 1"); Så du väljer ett slumptal mindre än antalet resultat och använder det som kompensation i din LIMIT-klausul.
Ju mer data läses från tabellerna, desto långsammare kommer frågan att bli. Det ökar den tid det tar för diskoperationerna. Även när databasservern är separat från webbservern kommer du att ha längre nätverksförseningar på grund av att data måste överföras mellan servrarna.
Det är en bra vana att alltid ange vilka kolumner du behöver när du gör din SELECTs.
// inte föredragen $ r = mysql_query ("VÄLJ * FRÅN användare WHERE user_id = 1"); $ d = mysql_fetch_assoc ($ r); echo "Välkommen $ d ['användarnamn']"; // bättre: $ r = mysql_query ("SELECT användarnamn FROM användare WHERE user_id = 1"); $ d = mysql_fetch_assoc ($ r); echo "Välkommen $ d ['användarnamn']"; // Skillnaderna är större med större resultatuppsättningar I varje tabell har en id-kolumn som är PRIMARY KEY, AUTO_INCREMENT och en av smakerna av INT. Också helst UNSIGNED, eftersom värdet inte kan vara negativt.
Även om du har ett användartabell som har ett unikt användarnamn, gör du inte den primära nyckeln. VARCHAR-fält som primära nycklar är långsammare. Och du kommer att ha en bättre struktur i din kod genom att referera till alla användare med deras id internt.
Det finns också bakom kulisserna som utförs av MySQL-motorn själv, som använder det primära nyckelfältet internt. Som blir ännu viktigare, desto mer komplicerat är databasinstallationen. (kluster, partitionering etc ...).
Ett eventuellt undantag från regeln är "associeringstabellerna", som används för många typer av sammanslutningar mellan 2 tabeller. Till exempel ett "posts_tags" -tabell som innehåller 2 kolumner: post_id, tag_id, som används för relationerna mellan två tabeller med namnet "post" och "tags". Dessa tabeller kan ha en PRIMARY-nyckel som innehåller båda ID-fälten.
ENUM typ kolumner är väldigt snabba och kompakta. Internt lagras de som TINYINT, men de kan innehålla och visa strängvärden. Detta gör dem till en perfekt kandidat för vissa områden.
Om du har ett fält som bara innehåller några olika typer av värden, använd ENUM istället för VARCHAR. Det kan till exempel vara en kolumn som heter "status" och innehåller bara värden som "aktiv", "inaktiv", "väntar", "utgått" etc ...
Det finns även ett sätt att få ett "förslag" från MySQL själv om hur man omstrukturerar ditt bord. När du har ett VARCHAR-fält kan det faktiskt föreslå att du ändrar den här kolumntypen till ENUM istället. Detta görs genom att använda PROCEDURE ANALYZE () -samtalet. Som tar oss till:
PROCEDURE ANALYZE () kommer att låta MySQL analysera kolumnstrukturerna och de faktiska data i din tabell för att komma med vissa förslag till dig. Det är bara användbart om det finns faktiska data i dina tabeller eftersom det spelar en stor roll i beslutsfattandet.
Om du till exempel har skapat ett INT-fält för din primära nyckel har du inte för många rader, det kan föreslå att du använder en MEDIUMINT istället. Eller om du använder ett VARCHAR-fält kanske du får ett förslag om att konvertera det till ENUM, om det bara finns några unika värden.
Du kan också köra detta genom att klicka på länken "Föreslå tabellstruktur" i phpmyadmin, i en av dina tabellvyer.
Tänk på att det här är bara förslag. Och om ditt bord kommer att bli större, kanske de inte ens är rätt förslag att följa. Beslutet är i sista hand ditt.
Om du inte har en särskild anledning att använda ett NULL-värde bör du alltid ställa in dina kolumner som INTE NULL.
Först och främst, fråga dig själv om det finns någon skillnad mellan att ha ett tomt strängvärde vs. ett NULL-värde (för INT-fält: 0 mot NULL). Om det inte finns någon anledning att ha båda, behöver du inte ett NULL-fält. (Visste du att Oracle anser NULL och den tomma strängen vara densamma?)
NULL kolumner kräver extra utrymme och de kan lägga till komplexitet i dina jämförelser. Undvik bara dem när du kan. Jag förstår dock att vissa människor kan ha mycket specifika skäl att ha NULL-värden, vilket inte alltid är en dålig sak.
Från MySQL-dokument:
"NULL-kolumner kräver extra utrymme i raden för att spela in om deras värden är NULL. För MyISAM-tabeller tar varje NULL-kolumn en bit extra, avrundad till närmaste byte."
Det finns flera fördelar med att använda beredda uttalanden, både för prestanda och säkerhetsskäl.
Förberedda uttalanden kommer att filtrera de variabler du binder till dem som standard, vilket är utmärkt för att skydda din ansökan mot SQL-injektionsattacker. Du kan naturligtvis filtrera dina variabler manuellt också, men de metoderna är mer benägna att mänskliga fel och glömska av programmeraren. Det här är mindre av ett problem när du använder någon form av ram eller ORM.
Eftersom fokus ligger på prestanda, borde jag också nämna fördelarna i det området. Dessa fördelar är mer signifikanta när samma fråga används flera gånger i din ansökan. Du kan tilldela olika värden till samma förberedda uttalande, men MySQL behöver bara analysera det en gång.
Även senaste versionerna av MySQL överför beredda uttalanden i en inbyggd binär form, som är effektivare och kan också bidra till att minska nätverksförseningarna.
Det var en tid då många programmerare brukade undvika förberedda uttalanden med syfte, av en enda viktig anledning. De caches inte av MySQL-fråga-cachen. Men sedan någon gång runt version 5.1 stöds också fråga caching.
För att använda beredda uttalanden i PHP kan du kolla in mysqli-förlängningen eller använda ett databasabstraktionslager som PDO.
// skapa ett förberedt uttalande om ($ stmt = $ mysqli-> förbereda ("SELECT användarnamn FROM användare WHERE state =?")) // bind parametrar $ stmt-> bind_param ("s", $ state); // kör $ stmt-> execute (); // bind resultatvariabler $ stmt-> bind_result ($ användarnamn); // hämta värde $ stmt-> hämta (); printf ("% s är från% s \ n", $ användarnamn, $ state); $ Stmt-> close (); Normalt när du utför en fråga från ett skript väntar det att körningen av den frågan avslutas innan den kan fortsätta. Du kan ändra det genom att använda obuffrade frågor.
Det finns en bra förklaring i PHP docs för funktionen mysql_unbuffered_query ():
"mysql_unbuffered_query () skickar SQL-frågefrågan till MySQL utan att automatiskt hämta och buffra resultatraderna som mysql_query () gör. Detta sparar en stor mängd minne med SQL-frågor som producerar stora resultatuppsättningar och du kan börja arbeta med resultatuppsättningen omedelbart efter att den första raden har hämtats eftersom du inte behöver vänta tills den fullständiga SQL-frågan har utförts. "
Det kommer emellertid med vissa begränsningar. Du måste antingen läsa alla raderna eller ringa mysql_free_result () innan du kan utföra en ny fråga. Du får inte heller använda mysql_num_rows () eller mysql_data_seek () på resultatuppsättningen.
Många programmerare kommer att skapa ett VARCHAR-fält (15) utan att inse att de faktiskt kan lagra IP-adresser som heltal. Med en INT går du ner till endast 4 byte utrymme, och har istället ett fast formatfält.
Du måste se till att din kolumn är en UNSIGNED INT, eftersom IP-adresser använder hela sortimentet av ett 32-bitars unsigned integer.
I dina frågor kan du använda INET_ATON () för att konvertera och IP till ett heltal, och INET_NTOA () för vice versa. Det finns också liknande funktioner i PHP som heter ip2long () och long2ip ().
$ r = "UPDATE-användare SET ip = INET_ATON ('$ _ SERVER [' REMOTE_ADDR ']') WHERE user_id = $ user_id"; När varje enskild kolumn i en tabell är "fast längd" betraktas tabellen även "statisk" eller "fast längd". Exempel på kolumntyper som inte är fastlängd är: VARCHAR, TEXT, BLOB. Om du bara inkluderar 1 av dessa typer av kolumner, upphör bordet att vara fastlängd och måste hanteras annorlunda med MySQL-motorn.
Tabeller med fast längd kan förbättra prestanda eftersom det är snabbare för MySQL-motorn att söka igenom posterna. När man vill läsa en viss rad i ett bord kan det snabbt beräkna läget av det. Om radstorleken inte är fixad måste den alltid hämta det primära nyckelindexet varje gång det behövs.
De är också enklare att cache och lättare att rekonstruera efter en krasch. Men de kan också ta mer plats. Om du till exempel konverterar ett VARCHAR-fält (20) till ett CHAR-fält (20) tar det alltid 20 byte utrymme oberoende av vad det är i.
Genom att använda "Vertikal partitionering" -tekniker kan du skilja kolumnerna med variabel längd till ett separat bord. Som tar oss till:
Vertikal partitionering är en uppgift att dela upp din bordstruktur på ett vertikalt sätt för optimeringsskäl.
Exempel 1: Det kan hända att du har ett användartabell som innehåller hemadresser, som inte läses ofta. Du kan välja att dela upp ditt bord och lagra adressinformationen på ett separat bord. På detta sätt kommer din huvudanvändartabell att krympa i storlek. Som du vet fungerar mindre tabeller snabbare.
Exempel 2: Du har ett "last_login" -fält i din tabell. Den uppdateras varje gång en användare loggar in på webbplatsen. Men varje uppdatering på ett bord leder till att frågarcachen för den tabellen spolas. Du kan lägga det fältet i ett annat bord för att hålla uppdateringar till användarens bord åtminstone.
Men du måste också se till att du inte ständigt behöver ansluta sig till dessa 2 tabeller efter partitioneringen eller om du faktiskt kan drabbas av prestationsnedgång.
Om du behöver utföra en stor DELETE eller INSERT-fråga på en levande webbplats, måste du vara försiktig så att du inte stör webbanan. När en stor fråga så utförs kan den låsa dina bord och få din webbapplikation stoppad.
Apache kör många parallella processer / trådar. Därför fungerar det mest effektivt när skript slutar att köras så snart som möjligt, så servrarna upplever inte för många öppna anslutningar och processer på en gång som förbrukar resurser, särskilt minnet.
Om du slutar låsa dina bord under en längre tid (som 30 sekunder eller mer) på en webbplats med hög trafik, kommer du att orsaka en process och fråga pileup, vilket kan ta lång tid att rensa eller ens krascha din web server.
Om du har någon typ av underhållsscript som behöver ta bort ett stort antal rader, använd bara LIMIT-klausulen för att göra det i mindre satser för att undvika denna trängsel.
medan (1) mysql_query ("DELETE FROM logs WHERE log_date <= '2009-10-01' LIMIT 10000"); if (mysql_affected_rows() == 0) // done deleting break; // you can even pause a bit usleep(50000); Med databasmotorer är disken kanske den viktigaste flaskhalsen. Att hålla saker mindre och mer kompakta är vanligtvis användbar när det gäller prestanda, för att minska mängden disköverföring.
MySQL-dokument har en lista över lagringskrav för alla datatyper.
Om ett bord förväntas ha mycket få rader finns det ingen anledning att göra primärnyckeln en INT, i stället för MEDIUMINT, SMALLINT eller till och med i vissa fall TINYINT. Om du inte behöver tidskomponenten använder du DATE istället för DATETIME.
Se bara till att du lämnar rimligt utrymme att växa, eller du kan sluta som Slashdot.
De två huvudlagringsmotorerna i MySQL är MyISAM och InnoDB. Varje har sina egna fördelar och nackdelar.
MyISAM är bra för läsbara applikationer, men det går inte väldigt bra när det finns många skrivningar. Även om du uppdaterar ett fält i en rad, blir hela tabellen låst, och ingen annan process kan till och med läsa från den tills frågan är klar. MyISAM är mycket snabb vid beräkning av SELECT COUNT (*) typer av frågor.
InnoDB tenderar att vara en mer komplicerad lagringsmotor och kan vara långsammare än MyISAM för de flesta små applikationer. Men den stöder radbaserad låsning, vilken vågar bättre. Den stöder också några mer avancerade funktioner som transaktioner.
Genom att använda en ORM (Object Relational Mapper) kan du få vissa prestandafördelar. Allt som en ORM kan göra kan också kodas manuellt. Men detta kan betyda för mycket extra arbete och kräva en hög kompetensnivå.
ORM är bra för "Lazy Loading". Det betyder att de bara kan hämta värden när de behövs. Men du måste vara försiktig med dem eller du kan sluta skapa många mini-frågor som kan minska prestanda.
ORMs kan också batcha dina frågor till transaktioner, som fungerar mycket snabbare än att skicka enskilda frågor till databasen.
För närvarande är min favorit ORM för PHP Läran. Jag skrev en artikel om hur man installerar Läran med CodeIgniter.
Hållbara anslutningar är avsedda att minska överkostnaden för att återskapa anslutningar till MySQL. När en permanent anslutning skapas, kommer den att vara öppen även efter att skriptet slutförts. Eftersom Apache återvänder är det barnprocesser, nästa gång processen körs för ett nytt skript, kommer det att återanvända samma MySQL-anslutning.
Det låter bra i teorin. Men från min personliga erfarenhet (och många andra) verkar det här inte vara värt besväret. Du kan få allvarliga problem med anslutningsgränser, minnesproblem och så vidare.
Apache går extremt parallell och skapar många barnprocesser. Detta är den främsta anledningen till att beständiga anslutningar inte fungerar väldigt bra i denna miljö. Innan du överväger att använda funktionen mysql_pconnect (), kontakta systemadministratören.
