Share
Forskjellene mellom velskrevet SQL og ikke stor, og i produksjon på et høyt etterspørselssted, forårsaker de alvorlige konsekvenser i ytelse og pålitelighet av tjenesten. I denne veiledningen vil jeg diskutere hvordan man skriver raske spørsmål og hvilke faktorer som bidrar til å få dem til å gå sakte.
I dag er det mye snakk om Big Data og ny teknologi. NoSQL og cloud-baserte løsninger er gode, men mye populært webprogramvare (som WordPress, phpBB, Drupal, VBulletin Forum software, etc.) kjører fortsatt på MySQL. Overføring til disse nyere løsningene kan ikke være så enkelt som bare å optimalisere konfigurasjonen du allerede har i produksjonen. Dessuten er ytelsen til MySQL veldig bra, spesielt Percona-versjonen.
Ikke gjør den vanlige feilen ved å kaste flere og flere datakilder ved å håndtere problemet med sakte spørringer og høye serverbelastninger, i stedet for å adressere de grunnliggende problemene. Å legge til CPU-strøm, SSD eller RAM er en form for optimalisering hvis du vil, men det er ikke det jeg snakker om her. Også uten et optimalisert nettsted, etter hvert som du vokser med maskinvarevinduene, vil problemene multiplisere eksponentielt. Så det er ikke en solid langsiktig løsning.
Å være god i SQL er alltid et viktig verktøy for en webutvikler, og med løsningen blir det ofte så enkelt som bare å legge til en indeks eller noe å endre hvordan bordet blir brukt, det hjelper virkelig å vite hvordan du bruker RDBMS godt. I dette tilfellet fokuserer vi på en populær åpen kildekode database som ofte brukes sammen med PHP, og det er MySQL.
Webutviklere, Database Architects / DBAs og System Administrators som er kjent med MySQL. Hvis du ikke er kjent med MySQL som nybegynner, vil denne veiledningen nok ikke gi stor mening, men jeg vil forsøke å holde det så informativt som mulig for nykommere til MySQL.
Jeg anbefaler å prøve trinnene som tilbys på din egen MySQL-database (sikkerhetskopiere alt først selvfølgelig!). Hvis du ikke har noen database til å fungere på, finnes eksempelvis databaseskjemaer der det er aktuelt.
Sikkerhetskopiering av MySQL er enkelt med mysqldump kommandolinjeverktøy:
bash $ mysqldump myTable> myTable-backup.sql
Du kan lære mer om mysqldump.
Kort sagt og i ingen rekkefølge av betydning spiller følgende alle viktige faktorer i spørring og serverytelse:
Accentsconagua
Hvor klausul (og bruk av interne MySQL-funksjoner som HVIS og DATO for eksempel)Rekkefølge etter Vi vil adressere alle disse områdene i denne veiledningen. Også, hvis du ikke allerede bruker den, vennligst installer Percona, som er en erstatning for MySQL som gir en seriøs ytelsesøkning. For å se et referansepunkt for Percona vs MySQL, se på denne sammenligningen.
Indekser brukes av MySQL for å finne rader med bestemte kolonnverdier raskt, for eksempel innenfor a HVOR. Uten en indeks må MySQL begynne med den første raden og deretter lese gjennom hele tabellen for å finne de relevante rader. Jo større bordet er, desto mer koster dette.
Hvis tabellen har en indeks for de aktuelle kolonnene, kan MySQL raskt bestemme posisjonen for å søke i midten av datafilen uten å måtte se på alle dataene. Dette er mye raskere enn å lese hver rad i rekkefølge.
Når skriptspråk kobles til databasen, hvis du har konfigurert vedvarende tilkoblinger, vil den kunne gjenbruke en eksisterende tilkobling uten å måtte opprette en ny. Dette er optimalt for produksjonsbruk og må være aktivert.
PHP-brukere kan lese mer i PHP-håndboken.
Den raskeste og mest effektive måten jeg har funnet for å fikse dette, er å bruke en nøkkelverdi-parbutikk, for eksempel memcached eller Redis.
Med memcache Du kan ganske enkelt cache søkeinnholdet ditt med følgende, for eksempel:
"php koble ( 'localhost', 11211); $ cacheResult = $ cache-> get ('nøkkelnavn'); hvis ($ cacheResult) // ... ikke å spørre $ result = $ cacheResult; annet // ... kjør søket ditt $ mysqli = mysqli ('p: localhost', 'brukernavn', 'passord', 'tabell'); // prepend p: til vertsnavn for utholdenhet $ sql = 'VELG * FRA innlegg VENSTRE GÅ TIL brukerInfo bruker (UID) WHERE posts.post_type =' post '|| posts.post_type = 'artikkel' BESTILL BY kolonne LIMIT 50 '; $ result = $ mysqli-> spørring ($ sql); $ memc-> sett ('nøkkelnavn', $ resultat-> fetch_array (), MEMCACHE_COMPRESSED, 86400);
// Pass $ cacheResult til mal $ template-> assign ('posts', $ cacheResult);
?>"
Nå er eksemplet VENSTRE BLI MED spørringen vil bare bli kjørt en gang hver 86.400 sekunder (24 timer), og tar dermed en stor belastning vekk fra MySQL-serveren og reduserer samtidige tilkoblinger.
Merk: Prepend p: til vertsargumentet ditt i MySQLi for vedvarende tilkoblinger.
Når dataene dine blir store, eller etterspørselen etter serviceramper opp, kan panikk angi. En rask løsning for å sikre at tjenesten forblir på nettet, kan skje. Men jeg anbefaler det ikke, fordi skjære iboende ser ut til å gjøre datastrukturene altfor kompliserte. Og som forklart veldig veltalende i denne artikkelen fra Percona-bloggen, ikke skjære.
Å lage databaseskjemaer er ikke for vanskelig når du godtar noen gylne regler, for eksempel å jobbe med begrensningene og være klar over hva som vil være effektivt. Lagring av bilder i databasen som blob Datatyper, for eksempel, er svært motet; lagrer et filnavn i en varchar Datatype kolonne er langt overlegen.
Forsikre deg om at designet er riktig for den nødvendige bruken, er viktig når du lager din app. Hold spesifikk data separert (for eksempel kategorier og innlegg), og sørg for at mange til ett eller ett til mange forhold lett kan kobles til IDer. Bruk av UTENLANDSKE NØKKEL Anlegget i MySQL er ideelt for å kaskade datatilfelle mellom tabeller.
Når du bygger bordet ditt, prøv å huske følgende:
REKKEFØLGE ETTER i MySQL.UNIK indeks type for unike datasett og bruk PÅ DUPLIKATE KEY UPDATE for å holde en datetime eller unix tidsstempel oppdatert for eksempel for siste gang raden ble sjekket.INT datatype for heltall tall. Hvis du ikke angir lengden, vil MySQL beregne det som kreves selv.For å optimalisere må vi se på tre grunnleggende datasett angående søknaden din:
Analyse kan gjøres på flere måter. For det første vil vi ta den mest direkte ruten til å se under panseret av MySQL-spørringer. Det første verktøyet i optimaliseringsverktøyskassen er FORKLARE. Bruk dette i spørringen din før Å VELGE vil gi deg følgende utgang:
sql mysql> FORKLAR SELECT * FROM 'wp_posts' WHERE 'post_type' = 'post'; + ---- + ------------- + ---------- + ------ + ------------ ------ + ------------------ + --------- + ------- + ------ + ------------- + | id | select_type | bord | type | possible_keys | nøkkel | key_len | ref | rader | Ekstra | + ---- + ------------- + ---------- + ------ + ------------ ------ + ------------------ + --------- + ------- + ------ + ------------- + | 1 | Enkelt | wp_posts | ref | type_status_date | type_status_date | 82 | const | 2 | Bruke hvor | + ---- + ------------- + ---------- + ------ + ------------ ------ + ------------------ + --------- + ------- + ------ + ------------- + 1 rad i settet (0,00 sek)
De oppførte kolonnene inneholder nyttig informasjon om spørringen som blir utført. Kolonnene du trenger å være oppmerksom på, er possible_keys og Ekstra.
possible_keys vil vise indeksene som MySQL-motoren har tilgjengelig for bruk for spørringen. Noen ganger må du tvinge en indeks for å sikre at spørringen utføres på raskeste måte.
De Ekstra kolonne vil vise om en betinget HVOR eller REKKEFØLGE ETTER var brukt. Viktigst å merke er om Bruk av filort vises. Vurder følgende eksempel:
sql EXPLAIN SELECT main_text FRA innlegg WHERE user = 'myUsername' && status = '1' && (status_spam_user = 'no_spam' || (status_spam_user = 'nøytral' && status_spam_system = 'nøytral')) BESTILL BY dato DESC LIMIT 6430, 10
Denne typen spørring kan komme til disk på grunn av den betingede hvor, som skjer hvis vi ser på FORKLARE:
sql id select_type tabelltype possible_keys key key_len ref rader Ekstra 1 SIMPLE innlegg ref index_user, index_status index_user 32 const 7800 Bruke hvor; Bruk av filort
Så dette spørsmålet har mulighet til å bruke to indekser og for tiden er det å trykke på disken på grunn av Bruk av filort i Ekstra.
Hva Bruk av filort gjør er definert her fra MySQL manualen:
"MySQL må gjøre et ekstra pass for å finne ut hvordan du henter radene i sortert rekkefølge. Sorteringen er gjort ved å gå gjennom alle rader i henhold til tilkoblingstypen og lagre sorteringsnøkkelen og pekeren til raden for alle rader som samsvarer med WHERE-klausulen. Nøklene blir deretter sortert og radene hentes i rekkefølge. "
Dette ekstrapasset vil redusere appen din og må unngås for enhver pris. En annen viktig Ekstra Resultatet å unngå er Bruke midlertidig, noe som betyr at MySQL måtte opprette en midlertidig tabell for spørringen. Åpenbart er dette en heslig bruk av MySQL og må unngås for enhver pris, med mindre du ikke kan optimalisere noe ytterligere på grunn av datakravene. I dette tilfellet skal spørringen caches i Redis eller Memcache og ikke drives av brukerne.
Å fikse problemet med Bruk av filort Vi må sikre at MySQL bruker en INDEX. Den har flere possible_keys å velge mellom, men MySQL kan bare bruke en indeks i den endelige spørringen. Selv om indekser kan være kompositter av flere kolonner, er den inverse ikke sant, selv om du kan gi tips til MySQL optimizer om hvilke indekser du har opprettet.
MySQLs optimizer vil bruke statistikk basert på spørreknappene for å velge den beste indeksen for omfanget av spørringen. Det gjør det basert på den innebygde optimisatorens statistiske logikk, men med flere valg kan dette ikke alltid være korrekt uten å antyde. For å sikre at riktig nøkkel brukes (eller ikke brukes), bruk FORCE INDEX, BRUK INDEX og IGNORE INDEX søkeord i søket ditt. Du kan lese mer om indekshinting i MySQL-håndboken.
For å se på bordtastene, bruk kommandoen VIS INDEX.
Du kan angi flere tips for optimalisatoren å bruke, for eksempel:
sql SELECT * FROM table1 BRUK INDEX (col1_index, col2_index) WHERE col1 = 1 og col2 = 2 og col3 = 3;
Kjører en FORKLARE vil vise deg hvilken indeks som ble brukt i det endelige utfallet. Så for å fikse det forrige eksempelet vil vi legge til BRUK INDEX og så:
sql EXPLAIN SELECT main_text FRA innlegg USE INDEX (index_user) HVOR bruker = 'myUsername' && status = '1' && (status_spam_user = 'no_spam' || (status_spam_user = 'nøytral' && status_spam_system = 'nøytral')) BESTILL BY dato DESC LIMIT 6430, 10
Nå som MySQL har index_status fra bordet til bruk, er spørringen løst.
sql id select_type tabellen type possible_keys nøkkel nøkkel_no ref rader Ekstra 1 SIMPLE innlegg ref index_user, index_status index_user 32 const 7800 Bruk hvor
Ved siden av FORKLARE er den BESKRIVE søkeord. Med BESKRIVE Du kan se en tabellinformasjon som følger:
sql mysql> DESCRIBE City; + ------------ + ---------- + ------ + ----- + --------- + - -------------- + | Felt | Type | Null | Nøkkel | Standard | Ekstra | + ------------ + ---------- + ------ + ----- + --------- + - -------------- + | Id | int (11) | NEI | PRI | NULL | auto_increment | | Navn | char (35) | NEI | | | | | Land | char (3) | NEI | UNI | | | | Distrikt | char (20) | JA | MUL | | | | Befolkning | int (11) | NEI | | 0 | | +------------+----------+------+-----+---------+----------------+
Du oppretter indekser i MySQL med CREATE INDEX syntaks. Det er noen smaker av indeksen. FULL TEKST brukes til fulltekstsøkingsformål, og da er det UNIK type for å sikre data blir holdt unikt.
Hvis du vil legge til en indeks i tabellen, bruker du følgende syntaks for eksempel:
sql mysql> CREATE INDEX idx_start_of_username ON 'brukere' (brukernavn (10));
Dette vil skape en indeks på bordet brukere, som vil bruke de første 10 bokstavene i brukernavnet kolonnen, som er en varchar datatype.
I dette tilfellet er det noen oppslag som krever a HVOR sortere på brukernavnet med kampen som er i de første 10 tegnene, ville være det samme som et oppslag på hele bordet.
Indekser har stor effekt på hastigheten det tar å returnere forespørselsdataene. Bare å sette en primærnøkkel og en unik indeks er vanligvis ikke nok-komposittnøkler er der den ekte tuningnissen ligger i MySQL, og det krever oftest litt A / B-kontroll med FORKLARE.
For eksempel, hvis vi trenger å referere to kolonner i vår HVOR betinget, en komposittnøkkel ville være ideell.
sql mysql> CREATE INDEX idx_composite ON brukere (brukernavn, aktiv);
Her er denne nøkkelen opprettet på brukernavn kolonne fra det forrige eksempel og kolonnen aktiv, en ENUM datatype som angir om brukerkontoen er aktiv. Så nå når du spørre dataene for HVOR Brukernavnet er gyldig og kontoen er aktiv = 1, Datasettet er nå optimalisert for å håndtere dette bedre.
Aktiver profilering for å se nærmere på dine MySQL-spørringer. Dette kan gjøres i kjøretid via sett profilering = 1, og deretter utføre spørringen din og se på resultatet av vise profiler.
Med PDO er det en kodebit som bare gjør det:
$ php $ db-> spørring ('set profiling = 1'); $ db-> spørring ('velg overskrift, kropp, koder fra innlegg'); $ rs = $ db-> spørring ('vis profiler'); $ db-> spørring ('set profiling = 0'); // Deaktiver profilering etter at spørringen er kjørt
$ records = $ rs-> fetchAll (PDO :: FETCH_ASSOC); // Få resultatene fra profilering
$ errmsg = $ rs-> errorInfo () [2]; // Fang noen feil her "
Hvis du ikke bruker PDO, kan det samme gjøres med mysqli og så:
"php $ db = new mysqli ($ host, $ brukernavn, $ passord, $ dbname);
$ db-> spørring ('sett profilering = 1'); $ db-> spørring ('velg overskrift, kropp, merker fra innlegg'); hvis ($ result = $ db-> spørring ("SHOW profiler", MYSQLI_USE_RESULT)) mens ($ row = $ result-> fetch_row ()) var_dump ($ rad); $ result-> close ();
hvis ($ result = $ db-> spørring ("vis profil for spørring 1", MYSQLI_USE_RESULT)) while ($ row = $ result-> fetch_row ()) var_dump ($ rad); $ result-> close ();
$ db-> spørring ('sett profilering = 0'); "
Dette vil returnere deg profildataene, som vil inkludere utføringstiden i den andre verdien av den associative arrayen:
php array (3) [0] => streng (1) "1" [1] => streng (10) "0.00024300" [2] => streng (17) "velg overskrift, kropp, merker fra innlegg" Forespørselen tok 0,00024300 sekunder å fullføre. Det er raskt nok til ikke å bekymre deg for. Men når tallene raser opp, må vi ta et dypere utseende.
Som et fungerende eksempel, bli kjent med appen din. Sett en sjekk for a DEBUG konstant i databasen abstraksjonslag / rammeverkdatabase driver, og deretter kan du starte revisjon ved å aktivere en profil sak og skrive resultatet med en var_dump / print_r. Nå kan du bla gjennom og profilere nettstedets sider med letthet!
For å gjøre en full revisjon av dine spørsmål, aktiver loggingen. Noen utviklere jeg har jobbet med, er bekymret for at dette er et dobbeltsidig problem ved at det muliggjør logging noe, påvirker ytelsen, og statistikken du registrerer, vil derfor være litt lavere enn i virkeligheten. Selv om dette er sant, viser mange referanser at det ikke er for mye av en forskjell.
For å aktivere logging i MySQL versjon 5.1.6, bruker du den globale log_slow_queries og kan spesifisere en fil med slow_query_log_file global. Dette kan gjøres i kjøretidsprompten som det er:
bash sett global log_slow_queries = 1; sett global slow_query_log_file = /dev/slow_query.log;
Du kan angi dette vedvarende i /etc/my.cnf eller my.ini konfigurasjonsfil for serveren din.
bash log_slow_queries = 1; slow_query_log_file = /dev/slow_query.log;
Etter å ha gjort denne endringen, må du starte MySQL-serveren på nytt, f.eks. service mysql restart på Linux-systemer.
I den nyere MySQL 5.6.1, log_slow_queries er avskrevet og slow_query_log brukes i stedet. Aktivering BORD som utskriftstype muliggjør en mye bedre feilsøkingsopplevelse og kan gjøres som følger i MySQL 5.6.1 og senere:
bash log_output = TABLE; log_queries_not_using_indexes = 1; long_query_time = 1
long_query_time angir antall sekunder en sakte spørring er klassifisert som. Standard er 10 og minimum 0. Det kan ta millisekundverdier ved å spesifisere en flyte; her har jeg satt det til 1 sekund. Så et spørsmål som tar lengre tid enn et sekund, kommer til å bli logget inn i BORD Utgående format.
Dette logger på mysql.slow_log og mysql.general_log bord innenfor MySQL.
For å deaktivere logging, sett log_output til INGEN.
log_queries_not_using_indexes er en nyttig boolean som, når den er aktivert i forbindelse med den langsomme spørringsloggen, betyr at kun spørringer som forventes å hente alle rader, logges.
Dette alternativet betyr ikke alltid at ingen indeks brukes. For eksempel, når en forespørsel bruker en full indekssøk, vil dette bli logget fordi indeksen ikke vil begrense antall rader.
Aktivering av logging på et produksjonssted med trafikk vil ganske mye alltid må gjøres i en kort periode, mens overvåking av belastningen for å sikre at den ikke påvirker tjenesten. Hvis du er under tung belastning og trenger en snarlig løsning, starter du med å løse problemet ved spørsmål med VIS PROCESSLIST eller via information_schema.PROCESSLIST bord direkte, f.eks. velg * fra information_schema.PROCESSLIST;.
Logging av alle forespørsler i produksjonen kan fortelle deg mye og er en god praksis for forskningsformål når du reviderer et prosjekt, men etterlater det å løpe i flere dager, vil det ofte ikke gi deg mer brukbare data enn maksimalt 48 timer ville gjøre ( i gjennomsnitt, i hvert fall fange topptider for bruk for å få en god titt på spørringene og få noen ideer om frekvens).
Merk: Hvis du kjører et nettsted som opplever økninger av topptrafikk og deretter perioder med ikke mye i det hele tatt (for eksempel en sportswebside under og uten sesong), vær logisk med hvordan du ser på logging. Ikke anta at nettstedet fungerer raskt. Gjør revisjon og sett viktigst opp noen grafikk.
Percona har noen gode verktøy som følger med det, og pt-spørring fordøye er et kommandolinjeverktøy for å analysere spørringslogger, prosesslisten eller tcpdumps.
Du kan bruke pt-spørring fordøye på følgende måter:
Analyser en * .log-fil (utgitt fra din sakte spørringslogging for eksempel):
bash $ pt-query-digest slow.log
Rapporter om de langsomste spørringene fra host1 i sanntid (veldig nyttig!):
bash $ pt-query-digest - prosesslist h = vert1
Bruk tcpdump til å rapportere de tregeste spørringene fra MySQL protokolldata:
"bash $ tcpdump -s 65535 -x -nn -q -tttt -i hvilken som helst -c 1000 port 3306> mysql.tcp.txt
$ pt-query-digest -type tcpdump mysql.tcp.txt "
Til slutt kan vi lagre langsomme spørringsdata fra en vert til en annen for senere gjennomgang. Her lagrer vi spørringen fordøye for slow.log til host2:
bash $ pt-query-digest --review h = host2 - no-report slow.log
For å lære å fullt ut bruke pt-spørring fordøye verktøyet til Percona, les den manuelle siden.
Denne grafen over InnoDB Row Operations viser radoperasjonen InnoDB har utført: oppdateringer, leser, slett og legger inn.
Dette er et stort emne, og jeg vil bare røre på det nok i denne veiledningen for å komme i gang med MySQL-overvåking. Det er imidlertid viktig å merke seg generelt at overvåking av alle nettstedets tjenester er ideell for å virkelig vite hva ytelsen din og bruken er.
For å oppnå dette anbefaler jeg å sette opp en RRDTool-basert løsning som Cacti med en MySQL-konfigurasjon. Få en mal for kaktus fra gutta på Percona.
Når du har fått Cacti satt opp og kan begynne å analysere appen din, må du passe på at grafene kan bygge opp. Etter noen dager begynner du å se dag og natt rytmer av trafikken din og se hvor opptatt serveren virkelig får.
Hvis du er på utkikk etter automatiserte varsler og utløsere, se etter konfigurasjonsmonit, en åpen kildekode proaktiv skjerm for Unix-systemer. Med overvåkingen kan du opprette regler for serveren din og sørge for at du blir varslet når lasten stiger, slik at du kan ta det mens det skjer.
Ved å logge på alle sakte spørringer som tar mer enn et sekund å fullføre, kan vi fortelle noe, men også å vite hvilke spørsmål som utføres hundrevis av ganger, er like viktig. Selv om disse spørringene er korte for å utføre, tar overhead av høye forespørsler fortsatt sin tolv på serveren.
Derfor holder du deg rundt når du oppdaterer noe og legger den til live, er den mest avgjørende tiden for nye databaser og endringer. Vi har alltid en policy på teamene mine for å aldri synkronisere nye funksjonsdatabaseendringer etter en onsdag på et live-prosjekt. Det må gjøres i begynnelsen av uken, senest tirsdag, slik at alle lagene kan overvåke og gi støtte tilsvarende.
Før du går med nye spørringer, må du referere til et lastetestingsverktøy, for eksempel ab. Når du kjører referansen må du se på VIS PROCESSLIST, og også muliggjøre logging og overvåkning med systemverktøy som topp, gratis og iostat. Dette er et avgjørende skritt før du setter noen ny forespørsel inn i en levende produksjon. Men det er ikke en 100% syre test fordi live trafikk kan oppføre seg langt annerledes enn en beregnet referanse.
Å benchmark med ab, sørg for at pakken er installert, for eksempel:
bash #centos users $ sudo yum installere ab #debian / ubuntu brukere $ sudo apt-get install ab
Nå kan du starte med å teste appen din, for eksempel:
bash $ ab-k-c 350-n 20000 my-domain.com/
De -k betyr å holde i live tilkoblingen, og -c 350 er antall samtidige tilkoblinger, dvs. antall personer / klienter som vil treffe nettstedet på en gang. Endelig -n 20000 er antall forespørsler som vil bli gjort til my-domain.com.
Så ved å kjøre kommandoen ovenfor, vil du treffe http://my-domain.com/ med 350 samtidige tilkoblinger til 20.000 forespørsler er oppfylt, og dette vil bli gjort ved hjelp av holde levende header.
Etter at prosessen er ferdig med de 20.000 forespørsler, vil du motta tilbakemelding på statistikk. Dette vil fortelle deg hvor godt nettstedet utføres under stresset du legger det når du bruker parameterne ovenfor. Dette er en god måte å vite automatisk, hvis søket ditt har endret noe.
Forespørselsbeløp og serverbelastning har stor innvirkning på ytelsen, og spørringen kan påvirkes på grunn av dette. I det hele tatt bør du aktivere den langsomme spørringsloggen for å fange dette i produksjon, og som regel for utvikling må du sørge for at alle spørringer utføres i brøkdeler av en millisekund (0,0xx eller raskere) på en tomgangsserver.
implementering memcache vil ha en dramatisk innvirkning på belastningskravene dine, og vil bli brukt til å alvorlig avlaste ressurser som ble brukt til å behandle spørringer. Kontroller at du bruker memcached effektivt og benchmark appen din med en hurtigbuffer (forhåndslastet med verdier) vs. en kald en.
For å unngå å gå ut i produksjon med en tom cache, er et pre-loader-skript en god måte å sikre at hurtigbufferen blir lest og du får ikke et stort antall forespørsler som alle kommer inn på en gang når du kommer tilbake fra nedetid på grunn av overkapasitetsfeil.
Så du har aktivert logging, du har nå funnet noen sakte søk i appen din. La oss få til å fikse dem! For eksempel vil jeg demonstrere ulike vanlige problemer du vil møte og logikken for å fikse dem.
Hvis du ikke har funnet noen sakte spørringer ennå, kan du kanskje sjekke hva dine innstillinger var for long_query_time hvis du bruker spørringsloggingsmetoden. Ellers har du sjekket alle dine spørsmål med profilering (sett profilering = 1), lage en liste over spørsmålene som tar lengre tid enn brøkdeler av en millisekund for å fullføre (0,000x sekunder) og la oss starte på disse.
Her er seks vanlige problemer jeg kjører inn når du optimaliserer MySQL-spørringer:
REKKEFØLGE ETTER bruker filort.sql mysql> Forklar velg * fra produkter hvor produkter. Priser> 4 og produkter. Lager> 0 Bestill etter navn; + ---- + ------------- + ---------- + ------ + ------------ --- + ------ + --------- + ------ + ------ + --------------- -------------- + | id | select_type | bord | type | possible_keys | nøkkel | key_len | ref | rader | Ekstra | + ---- + ------------- + ---------- + ------ + ------------ --- + ------ + --------- + ------ + ------ + --------------- -------------- + | 1 | Enkelt | produkter | ALLE | NULL | NULL | NULL | NULL | 1142 | Bruker hvor; Bruke filesort | +----+-------------+----------+------+---------------+------+---------+------+------+-----------------------------+
Å unngå filort på dette er umulig på grunn av BESTILL BY navn. Uansett hvilken indekspermutasjon du bruker, er det beste du får Bruker hvor; Bruk av filort i din Ekstra kolonne. For å optimalisere dette, lagre resultatet i Memcache, eller bestill i programmets logikklag.
REKKEFØLGE ETTER på HVOR og a VENSTRE BLI MED REKKEFØLGE ETTER har en betydelig avgift på spørringer. For eksempel er følgende et grunnleggende VENSTRE BLI MED av a Produkter bord og kategorier bord ved hjelp av et heltall-ID. Når bestilling er fjernet, så er filingsorting.
"sql mysql> forklar utvalgte produkter. * fra produkter brukerindeks (idx_price) igjen delta kategorier ved hjelp av (catID) hvor products.price> 4 og catID = 4 BESTILL BY lager ASC grense 10; + - + - + - + - - + - + - + - + - + - + - + | id | select_type | table | type | possible_keys | nøkkel | key_len | ref | rader | Ekstra | + - + - + - + - + - + - + - + - + - + - + | 1 | SIMPLE | produkter | ALLE | idx_price | NULL | NULL | NULL | 986 | Bruke hvor; Bruke filesort | | 1 | SIMPLE | kategorier | const | PRIMARY | PRIMARY | 4 | const | 1 | Bruke indeks | + - + - + - + - + - + - + - + - + - + - + 2 rader i settet (0,00 sek)
mysql> Forklar utvalgte produkter. * Fra produkter brukerindeks (idx_price) igjen bli med kategorier ved hjelp av (catID) hvor products.price> 4 og catID = 4; + - + - + - + - + - + - + - + - + - + - + | id | select_type | bord | type | possible_keys | nøkkel | key_len | ref | rader | Ekstra | + - + - + - + - + - + - + - + - + - + - + | 1 | Enkelt | produkter | ALLE | idx_price | NULL | NULL | NULL | 986 | Bruke hvor | | 1 | Enkelt | kategorier | const | PRIMARY | PRIMARY | 4 | const | 1 | Bruk av indeks | + - + - + - + - + - + - + - + - + - + - + 2 rader i settet (0,00 sek) "
Når det kan unngås, prøv å ikke bruke en REKKEFØLGE ETTER. Hvis det absolutt må brukes, bestilles kun på en indeksnøkkel.
Rekkefølge etter på en temp kolonneBare ikke gjør det. Hvis du trenger å samle resultatene dine, gjør du det i programlogikken din; Ikke filtrer eller bestill på en midlertidig tabell i MySQL. Det kommer til å være veldig ressurskrevende.
FULL TEKST indexBruker en SOM spørringen er langt den tregeste måten å utføre en fulltekst-kamp på dataene dine. Implementer et fulltekstsøk og høst fordelene med denne strålende egenskapen til MySQL som sådan:
sql mysql> VELG * FRA ARTIKKER -> HVOR MATCH (tittel, kropp) MOT ('database'); + ---- + ------------------- + ------------------------ ------------------ + | id | tittel | kropp | + ---- + ------------------- + ------------------------ ------------------ + | 5 | MySQL vs YourSQL | I følgende databasesammenligning ... | | 1 | MySQL Tutorial | DBMS står for DataBase ... | + ---- + ------------------- + ------------------------ ------------------ + 2 rader i settet (0,00 sek)
Glemmer a GRENSE På en forespørsel kan det vesentlig endre oppslagstiden over store datasett (over en million rader).
Når det kommer til tre eller fire nivåer av VENSTRE BLI MED, du burde spørre deg selv: "Gjør jeg det riktig?" Hvis du har et rimelig argument for hvorfor denne spørringen må være slik, for eksempel vises den bare i en admin skjerm med lav etterspørsel eller ved bruk av en større statistisk visning som kan være bufret, fortsett deretter. Men hvis du trenger tilgang til dataene dine ofte med et stort antall sammenhenger, bør du se på hvordan sammensatte kolonner sammen i et nytt bord kan være mer fordelaktige, eller skape en visning.
Vi har diskutert grunnleggende optimalisering og verktøyene vi har til rådighet for å utføre arbeidet. Vi må revidere med profilering, og bruke pt-spørring f
