Share
Skillnaderna mellan välskriven SQL och inte stor, och i produktion på en högt ställd webbplats orsakar de allvarliga konsekvenser för prestanda och driftsäkerhet. I den här guiden kommer jag att diskutera hur man skriver snabba frågor och vilka faktorer som bidrar till att de går långsamt.
Idag är det mycket tal om Big Data och ny teknik. NoSQL och cloud-baserade lösningar är bra, men mycket populär webbprogramvara (som WordPress, phpBB, Drupal, VBulletin Forum-programvara etc.) kör fortfarande på MySQL. Migrering till dessa nyare lösningar kan inte vara så enkelt som att bara optimera konfigurationen du redan har i produktionen. Dessutom är MySQLs prestanda mycket bra, särskilt Percona-versionen.
Gör inte det vanliga misstaget att kasta mer och mer datorkraft vid hanteringen av problemet med långsamma frågor och höga serverbelastningar, i stället för att faktiskt ta itu med de rootunderliggande problemen. Att lägga till CPU-ström, SSD eller RAM är en form av optimering om du vill, men det är inte det jag ska prata om här. Också, utan en optimerad webbplats, när du växer med hårdvaruinställningarna kommer problemen att multiplicera exponentiellt. Så det är inte en solid långsiktig lösning.
Att vara bra på SQL är alltid ett viktigt verktyg för en webbutvecklare, och med fixen är det ofta så enkelt att bara lägga till ett index eller lite ändra hur bordet används, det hjälper verkligen att veta hur du använder din RDBMS bra. I det här fallet fokuserar vi på en populär open source-databas som ofta används i samband med PHP, och det är MySQL.
Webbutvecklare, databasarkitekter / DBA och systemadministratörer som är bekanta med MySQL. Om du inte är bekant med MySQL som nybörjare kommer den här guiden troligen inte att ge stor mening, men jag ska försöka hålla det så informativt som möjligt för nykomlingar till MySQL.
Jag rekommenderar att du försöker stegen som tillhandahålls på din egen MySQL-databas (säkerhetskopiera allt först förstås!). Om du inte har någon databas att arbeta på, skapas exempel skapande databasscheman där så är tillämpligt.
Att säkerhetskopiera MySQL är enkelt med mysqldump kommandoradsverktyg:
bash $ mysqldump myTable> myTable-backup.sql
Du kan lära dig mer om mysqldump.
Kortfattat och utan betydelse, spelar följande alla viktiga faktorer i fråga och serverns prestanda:
Accentsconagua
Var klausul (och användning av interna MySQL-funktioner som OM och DATUM till exempel)Sortera efter Vi kommer att ta itu med alla dessa områden i den här guiden. Om du inte redan använder den, vänligen installera Percona, vilket är en ersättning för MySQL som kommer att medföra en seriös prestationsökning. För att se en jämförelse av Percona vs MySQL, kolla på denna jämförelse.
Indexer används av MySQL för att hitta rader med specifika kolumnvärden snabbt, till exempel inuti a VAR. Utan ett index måste MySQL börja med den första raden och sedan läsa igenom hela tabellen för att hitta relevanta rader. Ju större bordet desto mer kostar detta.
Om tabellen har ett index för kolumnerna i fråga, kan MySQL snabbt bestämma positionen för att söka i mitten av datafilen utan att behöva titta på all data. Detta är mycket snabbare än att läsa varje rad i följd.
När ditt skriptspråk kopplas till databasen, om du har konfigurerat ihållande anslutningar kommer det att kunna återanvända en befintlig anslutning utan att behöva skapa en ny. Detta är optimalt för produktionsanvändning och måste vara aktiverat.
PHP-användare kan läsa mer i PHP-handboken.
Det snabbaste och mest effektiva sättet jag har hittat för att åtgärda detta är att använda en nyckelvärdesparaffär som memcached eller Redis.
Med memcache Du kan helt enkelt cache dina frågor innehåll med följande, till exempel:
"php ansluta ( 'localhost', 11211); $ cacheResult = $ cache-> get ('key-name'); om ($ cacheResult) // ... behöver inte fråga $ result = $ cacheResult; annars // ... kör din fråga $ mysqli = mysqli ('p: localhost', 'användarnamn', 'lösenord', 'tabell'); // prepend p: till värdnamn för uthållighet $ sql = 'VÄLJ * FRÅN inlägg LÄFTIGT GÅ MED användarInfo med (UID) WHERE posts.post_type =' post '|| posts.post_type = 'artikel' ORDER BY kolumn LIMIT 50 '; $ result = $ mysqli-> fråga ($ sql); $ memc-> set ('key-name', $ result-> fetch_array (), MEMCACHE_COMPRESSED, 86400);
// Förbi $ cacheResult till mall $ template-> assign ('posts', $ cacheResult);
?>"
Nu exemplet VÄNSTER JOIN Frågan kommer endast att köras en gång per 86.400 sekunder (24 timmar), vilket innebär en stor belastning bort från MySQL-servern och reducerar samtidiga anslutningar.
Obs! Förordna p: till ditt värdargument i MySQLi för ihållande anslutningar.
När dina data blir stora eller efterfrågan på din service ramper upp, kan panik införa. En snabbkorrigering för att säkerställa att din tjänst blir online kan skäras. Men jag rekommenderar inte det, för att skärpa i sig verkar göra datastrukturerna alltför komplicerade. Och som förklaras mycket vältaligt i den här artikeln från Percona-bloggen, skär inte.
Att skapa databasscheman är inte för svårt när du accepterar några gyllene regler, till exempel att arbeta med begränsningarna och vara medveten om vad som är effektivt. Lagring av bilder i databasen som klick Datatyper, till exempel, är mycket avskräckta; lagrar ett filnamn i en varchar datatyp kolonn är överlägsen.
Att säkerställa att konstruktionen är korrekt för den nödvändiga användningen är avgörande för att skapa din app. Håll specifika data separerade (t ex kategorier och inlägg) och se till att många till ett eller ett till många förhållanden enkelt kan kopplas till ID-skivor. Använda FRÄMMANDE NYCKEL anläggningen i MySQL är idealisk för att kaskad datakontingens mellan tabeller.
När du bygger ditt bord, försök att komma ihåg följande:
SORTERA EFTER i MySQL.UNIK indextyp för unika dataset och utnyttja OM DUPLICERA NYCKEL UPPDATERING för att hålla en datetime eller unix tidstämpel uppdaterad till exempel för sista gången raden kontrollerades.INT datatyp för heltalsnummer. Om du inte anger längden kommer MySQL att beräkna vad som krävs själv.För att effektivt optimera måste vi titta på tre grundläggande dataset angående din ansökan:
Analys kan göras på flera sätt. För det första kommer vi att ta den mest direkta vägen för att titta under kupan av MySQL-frågor. Det första verktyget i din optimeringsverktygslåda är FÖRKLARA. Använda detta i din fråga före VÄLJ kommer att ge dig följande produktion:
sql mysql> EXPLAIN SELECT * FRÅN 'wp_posts' WHERE 'post_type' = 'post'; + ---- + ------------- + ---------- + ------ + ------------ ------ + ------------------ + --------- + ------- + ------ + ------------- + | id | select_type | bord | typ | possible_keys | nyckel | key_len | ref | rader | Extra | + ---- + ------------- + ---------- + ------ + ------------ ------ + ------------------ + --------- + ------- + ------ + ------------- + | 1 | Enkelt | wp_posts | ref | type_status_date | type_status_date | 82 | const | 2 | Använda var | + ---- + ------------- + ---------- + ------ + ------------ ------ + ------------------ + --------- + ------- + ------ + ------------- + 1 rad i set (0,00 sek)
De listade kolumnerna innehåller var och en användbar information om frågan som körs. Kolumnerna du behöver ägna stor uppmärksamhet åt är possible_keys och Extra.
possible_keys kommer att visa de index som MySQL-motorn har tillgänglig att använda för frågan. Ibland måste du tvinga ett index för att säkerställa att frågan utförs på snabbast sätt.
De Extra kolumnen kommer att visa om en villkorlig VAR eller SORTERA EFTER var använd. Viktigast att notera är om Använda filort visas. Tänk på följande exempel:
sql EXPLAIN SELECT main_text FRÅN inlägg WHERE user = 'myUsername' && status = '1' && (status_spam_user = 'no_spam' || (status_spam_user = 'neutral' && status_spam_system = 'neutral')) BESTÄLL AV datum DESC LIMIT 6430, 10
Denna typ av fråga kan komma till disk på grund av det villkorliga varet, vilket händer om vi tittar på FÖRKLARA:
sql id select_type tabell typ possible_keys key key_len ref rader Extra 1 SIMPLE inlägg ref index_user, index_status index_user 32 const 7800 Använd var; Använda filort
Så denna fråga har möjlighet att använda två index och för närvarande är det att slå disken på grund av Använda filort i Extra.
Vad Använda filort gör här definieras här från MySQL manualen:
"MySQL måste göra ett extra pass för att ta reda på hur man hämtar raderna i sorterad ordning. Sorten görs genom att gå igenom alla rader i enlighet med typ av samtal och lagra sorteringsnyckeln och pekaren till raden för alla rader som matchar WHERE-klausulen. Nycklarna sorteras sedan och raderna hämtas i sorterad ordning. "
Det här extrapasset sakta ner din app och måste undvikas till varje pris. En annan viktig sak Extra Resultatet att undvika är Användar tillfälligt, vilket innebär att MySQL måste skapa ett temporärt bord för frågan. Självklart är detta en hemsk användning av MySQL och måste undvikas till varje pris, såvida du inte kan optimera ytterligare på grund av datakraven. I det här fallet bör frågan cachas i Redis eller Memcache och inte köras av användarna.
För att åtgärda problemet med Använda filort Vi måste se till att MySQL använder en INDEX. Det har flera possible_keys att välja mellan, men MySQL kan bara använda ett index i den sista frågan. Även om Indexes kan vara kompositer av flera kolumner, är invers inte sant, även om du kan ge tips till MySQL optimizer om vilka index du har skapat.
MySQLs optimizer använder statistik baserat på frågornas tabeller för att välja det bästa indexet för frågans omfattning. Det gör det baserat på den inbyggda optimatorens statistiska logik, men med flera val kan det inte alltid vara korrekt utan antydan. För att säkerställa att rätt nyckel används (eller inte används), använd FORCE INDEX, ANVÄND INDEX och IGNORE INDEX nyckelord i din fråga. Du kan läsa mer om indexhinting i MySQL-manualen.
För att titta på tabellnycklarna, använd kommandot VIS INDEX.
Du kan ange flera tips för optimeringen att använda, till exempel:
sql SELECT * FRÅN tabell1 ANVÄND INDEX (col1_index, col2_index) VAR col1 = 1 OCH col2 = 2 OCH col3 = 3;
Kör ett FÖRKLARA kommer att visa dig vilket index som användes i slutresultatet. Så för att fixa det föregående exemplet lägger vi till ANVÄND INDEX som så:
sql EXPLAIN SELECT main_text FRÅN inlägg ANVÄND INDEX (index_user) VAR användare = 'myUsername' && status = '1' && (status_spam_user = 'no_spam' || (status_spam_user = 'neutral' && status_spam_system = 'neutral')) BESTÄLL AV datum DESC LIMIT 6430, 10
Nu när MySQL har index_status från bordet att använda, är frågan fixad.
sql id select_type tabelltyp possible_keys key key_len ref rader Extra 1 SIMPLE inlägg ref index_user, index_status index_user 32 const 7800 Använda var
Vid sidan av FÖRKLARA är BESKRIVA nyckelord. Med BESKRIVA Du kan se en tabellinformation enligt följande:
sql mysql> DESCRIBE City; + ------------ + ---------- + ------ + ----- + --------- + - -------------- + | Fält | Typ | Null | Nyckel | Standard | Extra | + ------------ + ---------- + ------ + ----- + --------- + - -------------- + | Id | int (11) | NEJ | PRI | NULL | auto_increment | | Namn | char (35) | NEJ | | | | | Land | char (3) | NEJ | UNI | | | | District | char (20) | JA | MUL | | | | Befolkning | int (11) | NEJ | | 0 | | +------------+----------+------+-----+---------+----------------+
Du skapar index i MySQL med SKAPA INDEX syntax. Det finns några smaker av index. FULL TEXT används för fulltext söksyfte, och då finns det UNIK typ för att säkerställa data hålls unik.
Om du vill lägga till ett index i ditt bord använder du följande syntax till exempel:
sql mysql> CREATE INDEX idx_start_of_username ON 'användare' (användarnamn (10));
Detta skapar ett index på bordet användare, som kommer att använda de första 10 bokstäverna i användarnamnet kolumnen, vilket är en varchar datatyp.
I det här fallet måste alla sökningar som kräver en VAR sortera på användarnamnet med matchen i de första 10 tecknen skulle vara samma som en uppslagning av hela tabellen.
Indexer har stor effekt på hur snabbt det går att återge sökdata. Att bara ställa in en primär nyckel och unikt index är i allmänhet inte tillräckligt. Sammansatta nycklar är där den riktiga inställningsniken ligger i MySQL, och oftast kräver det lite A / B-kontroll med FÖRKLARA.
Till exempel, om vi behöver referera till två kolumner inom vår VAR förutsatt att en kompositnyckel skulle vara idealisk.
sql mysql> CREATE INDEX idx_composite ON-användare (användarnamn, aktiv);
Här skapas denna nyckel på Användarnamn kolumn från det tidigare exemplet och kolonnen aktiva, en ENUM datatyp som anger om användarkontot är aktivt. Så nu när du frågar uppgifterna för VAR användarnamnet är giltigt och kontot är aktiv = 1, Datasetet är nu optimerat för att hantera detta bättre.
Aktivera profilering för att titta närmare på dina MySQL-frågor. Detta kan göras vid körning via ställ profilering = 1, och sedan exekvera din fråga och titta på resultatet av visa profiler.
Med PDO här är ett koduttag som gör just det:
$ php $ db-> query ('set profiling = 1'); $ db-> fråga ('välj rubrik, kropp, taggar från inlägg'); $ rs = $ db-> fråga ('visa profiler'); $ db-> fråga ('set profiling = 0'); // Inaktivera profilering efter att frågan har körts
$ records = $ rs-> fetchAll (PDO :: FETCH_ASSOC); // Få resultaten från profilering
$ errmsg = $ rs-> errorInfo () [2]; // Fånga eventuella fel här "
Om du inte använder PDO kan samma sak göras med mysqli som så:
"php $ db = ny mysqli ($ värd, $ användarnamn, $ lösenord, $ dbname);
$ db-> fråga ('set profiling = 1'); $ db-> fråga ('välj rubrik, kropp, taggar från inlägg'); om ($ result = $ db-> fråga ("SHOW profiler", MYSQLI_USE_RESULT)) while ($ row = $ result-> fetch_row ()) var_dump ($ row); $ result-> close ();
om $ result = $ db-> query ("visa profil för fråga 1", MYSQLI_USE_RESULT)) while ($ row = $ result-> fetch_row ()) var_dump ($ rad); $ result-> close ();
$ db-> fråga ('set profiling = 0'); "
Detta kommer att återvända till dig profildata, som kommer att inkludera exekveringstiden i det andra värdet av den associativa arrayen:
php array (3) [0] => sträng (1) "1" [1] => sträng (10) "0.00024300" [2] => sträng (17) "välj rubrik, kropp, taggar från inlägg" Frågan tog 0.00024300 sekunder att slutföra. Det är snabbt nog att inte oroa dig för. Men när antalet rampar upp måste vi ta ett djupare utseende.
Som ett fungerande exempel lära känna din app. Placera en check för a RÄTTA TILL konstant i databasdrivrutinsdatabasets abstraktionslager / ramverk, och sedan kan du börja granska genom att aktivera ett profilfall och mata ut resultatet med en var_dump / print_r. Nu kan du enkelt bläddra och profilera webbplatsens sidor!
För att göra en fullständig granskning av dina frågor, aktivera loggning. Några utvecklare jag har arbetat med oroar att detta är ett dubbelsidigt problem, eftersom det möjliggör loggning något, påverkar prestanda, så statistiken du spelar in kommer att vara något lägre än i verkligheten. Även om detta är sant visar många riktmärken att det inte är för mycket av en skillnad.
För att aktivera loggning i MySQL version 5.1.6 använder du den globala log_slow_queries och kan ange en fil med slow_query_log_file global. Detta kan göras i runtime prompt så:
bash set global log_slow_queries = 1; sätt globala slow_query_log_file = /dev/slow_query.log;
Du kan ställa in detta ihållande i /etc/my.cnf eller my.ini konfigurationsfilen för din server.
bash log_slow_queries = 1; slow_query_log_file = /dev/slow_query.log;
När du har gjort denna ändring måste du starta om MySQL-servern, t.ex.. service mysql omstart på Linux-system.
I den nyare MySQL 5.6.1, log_slow_queries är avlägsnad och slow_query_log används istället. Möjliggör TABELL eftersom utmatningstypen möjliggör en mycket trevligare felsökningsupplevelse och kan göras enligt följande i MySQL 5.6.1 och senare:
bash log_output = TABLE; log_queries_not_using_indexes = 1; long_query_time = 1
long_query_time specificerar antalet sekunder som en långsiktig fråga är klassificerad som. Standardvärdet är 10 och minimum 0. Det kan ta millisekundervärden genom att ange en float; här har jag satt det till 1 sekund. Så en fråga som tar längre tid än en sekund kommer att bli inloggad i TABELL Utmatningsformat.
Detta loggar till mysql.slow_log och mysql.general_log tabeller inom MySQL.
För att inaktivera loggning, ställ in log_output till INGEN.
log_queries_not_using_indexes är en användbar booleska som när den aktiveras i samband med den långsamma förfrågan loggan innebär att endast frågor som förväntas hämta alla rader är inloggade.
Det här alternativet betyder inte alltid att inget index används. Till exempel, när en fråga använder en fullständig indexsökning, skulle detta loggas eftersom indexet inte skulle begränsa antalet rader.
Att aktivera loggning på en produktionsplats med trafik kommer i stort sett alltid att ske under en kort period, samtidigt som belastningen kontrolleras så att den inte påverkar tjänsten. Om du är tung belastad och behöver en brådskande åtgärd, börja med att ta itu med problemet vid prompten med VISA PROCESSLIST eller via information_schema.PROCESSLIST bord direkt, t.ex.. välj * från information_schema.PROCESSLIST;.
Att logga på alla frågor i produktionen kan berätta mycket för dig och är en bra metod för forskningsändamål när du granskar ett projekt, men om du lämnar det i flera dagar kan du inte ge dig mer användbar data än högst 48 timmar skulle göra ( i genomsnitt, fånga ibland topptiderna för användningen för att få en bra titt på frågorna och få några idéer om frekvensen).
Obs! Om du kör en webbplats som upplever störningar av topptrafik och då inte alls mycket (t.ex. en sportwebbplats under och utan säsongen), vara logisk med hur du tittar på loggning. Anta inte att webbplatsen fungerar snabbt. Gör granskning och framför allt skapa en viss grafering.
Percona har några bra verktyg buntade med det, och pt-query-digest är ett kommandoradsverktyg för att analysera sökfrågor, processlistan eller tcpdumps.
Du kan använda pt-query-digest på följande sätt:
Analysera en * .log-fil (utmatad från din långsamma frågarloggning till exempel):
bash $ pt-query-digest slow.log
Rapportera om de långsamma frågorna från värd1 i realtid (väldigt användbar!):
bash $ pt-query-digest - processlista h = värd1
Använd tcpdump för att rapportera de långsamma frågorna från MySQL-protokolldata:
"bash $ tcpdump -s 65535 -x -nn -q -tttt -i vilken som helst -c 1000 port 3306> mysql.tcp.txt
$ pt-query-digest -type tcpdump mysql.tcp.txt "
Slutligen kan vi spara långsama frågedata från en värd till en annan för senare granskning. Här sparar vi förfrågningsfördelningen för slow.log till host2:
bash $ pt-query-digest -review h = host2 -no-report slow.log
För att lära dig hur du använder pt-query-digest verktyg för Percona, läs den manuella sidan.
Den här grafen över InnoDB Row Operations visar radoperationerna InnoDB har utfört: uppdateringar, läser, raderar och lägger in.
Detta är ett stort ämne faktiskt och jag kommer bara att röra på det nog i den här guiden för att komma igång med MySQL-övervakning. Det är emellertid viktigt att notera i allmänhet att övervakningen av alla dina webbplatsers tjänster är idealisk för att verkligen veta vad din prestation och användningsområden är.
För att uppnå detta rekommenderar jag att du installerar en rrdtool-baserad lösning såsom kaktusar med en MySQL-konfiguration. Få en mall för kaktus från killarna på Percona.
När du har fått Cacti inrättad och kan börja analysera din app, låt det gå lite tid att skicka så att graferna kan byggas upp. Efter några dagar börjar du se dag och natt rytmer i din trafik och se hur upptagen servern verkligen får.
Om du letar efter automatiserade varningar och utlösare tittar du på konfigurationsmonit, en proaktiv öppen källkod för Unix-system. Med monit kan du skapa regler för din server och se till att du varnar när belastningen stiger så att du kan fånga det medan det händer.
Om du loggar på alla långsamma frågor som tar mer än en sekund att slutföra kan du berätta något för oss, men det är lika viktigt att du också vet vilka frågor som körs hundratals gånger. Även om dessa frågor är korta att genomföra, tar det höga kravet på höga begärningar fortfarande sin avgift på servern.
Det är därför som du bor när du uppdaterar något och sätter det i live är den viktigaste tiden för eventuella nya databasarbeten och förändringar. Vi har alltid en policy på mitt team att aldrig synkronisera nya funktionsdatabasändringar efter en onsdag på ett live-projekt. Det måste ske i början av veckan senast tisdag, så att alla lag kan övervaka och ge stöd i enlighet med detta.
Innan du bor med nya frågor måste du jämföra med ett lasttestverktyg som ab. När du kör benchmarken måste du titta på VISA PROCESSLIST, och möjliggör också loggning och övervakning med systemverktyg som topp, fri och iostat. Detta är ett viktigt steg innan du ställer in någon ny fråga till en liveproduktion. Men det är inte ett 100% surt test eftersom levande trafik kan uppträda långt annorlunda än ett beräknat riktmärke.
Att jämföra med ab, Se till att du har installerat paketet, t ex:
bash #centos users $ sudo yum installera ab #debian / ubuntu användare $ sudo apt-get install ab
Nu kan du börja med att testa din app, till exempel:
bash $ ab -k-c 350-n 20000 my-domain.com/
De -k betyder att håll vid liv anslutningen och -c 350 är antalet samtidiga anslutningar, det vill säga antalet personer / klienter som träffar webbplatsen samtidigt. Slutligen -n 20000 är antalet förfrågningar som kommer att göras till my-domain.com.
Så genom att köra kommandot ovan kommer du att slå http://my-domain.com/ med 350 samtidiga anslutningar tills 20.000 förfrågningar är uppfyllda, och detta kommer att göras med hjälp av den fortsatta levande rubriken.
När processen avslutat de 20 000 förfrågningarna får du feedback om statistik. Detta kommer att berätta hur bra webbplatsen utförs under den stress du lägger på när du använder parametrarna ovan. Det här är ett bra sätt att känna till automatiskt, om din fråga har ändrats något.
Begärans mängd och serverbelastning har stor inverkan på prestanda och frågestunden kan påverkas på grund av detta. Sammantaget borde du aktivera den långsamma frågeloggen för att fånga den i produktion, och som regel måste du se till att alla frågor exekveras i fraktioner av en millisekund (0,0xx eller snabbare) på en tomgångsserver.
Genomförandet memcache kommer att ha en dramatisk inverkan på dina belastningskrav och kommer att användas för att på allvar avlasta resurser som användes för att bearbeta frågor. Se till att du använder memcached effektivt och benchmark din app med en het cache (förinstallerad med värden) vs en kall en.
För att undvika att gå ut i produktion med en tom cache är ett pre-loader-skript ett bra sätt att säkerställa att cachen ska läsas och du får inte få ett stort antal förfrågningar som alla kommer in på en gång när du kommer tillbaka från en nedetid på grund av överkapacitetsfel.
Så att du har aktiverat loggar har du nu hittat några långsamma frågor i din app. Låt oss fixa dem! Till exempel kommer jag att demonstrera olika vanliga problem du kommer att stöta på och logiken att fixa dem.
Om du inte har hittat några långsamma frågor, kolla kanske vad dina inställningar var för long_query_time om du använder sökningsloggningsmetoden. Annars har du kontrollerat alla dina frågor med profilering (ställ profilering = 1), gör en lista över frågorna som tar längre tid än bråkdelar av en millisekund för att slutföra (0,000x sekunder) och låt oss börja på dem.
Här är sex vanliga problem som jag stöter på när du optimerar MySQL-frågor:
SORTERA EFTER använder filort.sql mysql> förklara välj * från produkter där products.price> 4 och products.stock> 0 order by name; + ---- + ------------- + ---------- + ------ + ------------ --- + ------ + --------- + ------ + ------ + --------------- -------------- + | id | select_type | bord | typ | possible_keys | nyckel | key_len | ref | rader | Extra | + ---- + ------------- + ---------- + ------ + ------------ --- + ------ + --------- + ------ + ------ + --------------- -------------- + | 1 | Enkelt | produkter | ALL | NULL | NULL | NULL | NULL | 1142 | Använda var Använda filesort | +----+-------------+----------+------+---------------+------+---------+------+------+-----------------------------+
Att undvika filort på detta är omöjligt på grund av BESTÄLL BY BY NAME. Oavsett vilken indexpermutation du använder, det bästa du kommer att få är Använda var Använda filort i din Extra kolumn. För att optimera detta, spara resultatet i Memcache, eller beställa i programmets logiklager.
SORTERA EFTER på VAR och a VÄNSTER JOIN SORTERA EFTER har en väsentlig vägtull på frågor. Till exempel är följande en grundläggande VÄNSTER JOIN av a Produkter bord och kategorier bordet med hjälp av ett heltal ID. När beställningen är borttagen, så är filsortering.
"sql mysql> förklara valda produkter. * från produkter användningsindex (idx_price) vänster ansluta kategorier med (catID) var products.price> 4 och catID = 4 BESTÄLL AV lager ASC limit 10; + - + - + - + - - + - + - + - + - + - + - + | id | select_type | tabell | typ | possible_keys | nyckel | key_len | ref | rader | Extra | + - + - + - + - + - + - + - + - + - + - + | 1 | SIMPLE | produkter | ALL | idx_price | NULL | NULL | NULL | 986 | Använda var; Använda filerort | | 1 | SIMPLE | kategorier | const | PRIMARY | PRIMARY | 4 | const | 1 | Använd index | + - + - + - + - + - + - + - + - + - + - + 2 rader i set (0,00 sek)
mysql> förklara valda produkter. * från produkter användningsindex (idx_price) vänster ansluta kategorier med (catID) där products.price> 4 och catID = 4; + - + - + - + - + - + - + - + - + - + - + | id | select_type | bord | typ | possible_keys | nyckel | key_len | ref | rader | Extra | + - + - + - + - + - + - + - + - + - + - + | 1 | Enkelt | produkter | ALL | idx_price | NULL | NULL | NULL | 986 | Använda var | | 1 | Enkelt | kategorier | const | PRIMARY | PRIMARY | 4 | const | 1 | Använd index | + - + - + - + - + - + - + - + - + - + - + 2 rader i setet (0,00 sek) "
När det kan undvikas, försök att inte använda en SORTERA EFTER. Om det absolut måste användas, beställ endast på en indexnyckel.
Sortera efter på en tempumn kolumnGör bara inte det. Om du behöver aggregera dina resultat gör du det i din applikationslogik. Gör inte filtreringen eller beställa på en tillfällig tabell i MySQL. Det kommer att bli väldigt resursintensivt.
FULL TEXT indexAnvänder en TYCKA OM Frågan är överlägset det långsammaste sättet att utföra en fulltext match på dina data. Implementera en fullständig text sökning och skörda fördelarna med denna lysande funktion i MySQL som så:
sql mysql> VÄLJ * FRÅN ARTIKLAR -> VAR MATCH (titel, kropp) AGAINST ('databas'); + ---- + ------------------- + ------------------------ ------------------ + | id | titel | kropp | + ---- + ------------------- + ------------------------ ------------------ + | 5 | MySQL vs YourSQL | I följande databas jämförelse ... | | 1 | MySQL Tutorial | DBMS står för DataBase ... | + ---- + ------------------- + ------------------------ ------------------ + 2 rader i set (0,00 sek)
Glömma a BEGRÄNSA på en fråga kan det betydligt ändra uppslagstiden över stora datamängder (över en miljon rader).
När det kommer till tre eller fyra nivåer av VÄNSTER JOIN, du borde fråga dig själv: "Gör jag det här rätt?" Om du har ett rimligt argument för varför denna fråga måste vara så visas den till exempel bara i en administrativ skärm med låg efterfrågan eller vid användning av en större statistisk vy som kan vara cachade och fortsätt sedan. Men om du behöver komma åt dina data ofta med ett stort antal samlingar bör du titta på hur sammansättning av kolumner tills
