Share
Databasebehandlinger pleier ofte å være hovedflaskehalsen for de fleste webapplikasjoner i dag. Det er ikke bare DBAs (databaseadministratorer) som må bekymre seg for disse ytelsesproblemene. Vi som programmerere trenger å gjøre vår del ved å strukturere tabeller på riktig måte, skrive optimaliserte spørringer og bedre kode. I denne artikkelen vil jeg oppgi noen MySQL-optimaliseringsteknikker for programmerere.
Før vi begynner, vær oppmerksom på at du kan finne massevis av nyttige MySQL-skript og verktøy på Envato Market.
MySQL skript og verktøy på Envato Market De fleste MySQL-servere har aktivert caching-aktivering. Det er en av de mest effektive metodene for å forbedre ytelsen, som håndteres stille av databasemotoren. Når det samme spørsmålet utføres flere ganger, hentes resultatet fra hurtigbufferen, noe som er ganske fort.
Hovedproblemet er at det er så enkelt og skjult fra programmereren, de fleste av oss pleier å ignorere det. Noen ting vi gjør, kan faktisk forhindre spørrebufferen fra å utføre oppgaven sin.
Accentsconagua
// query cache fungerer ikke $ r = mysql_query ("SELECT brukernavn FRA bruker WHERE signup_date> = CURDATE ()"); // query cache fungerer! $ i dag = dato ("y-m-d"); $ r = mysql_query ("SELECT brukernavn FRA bruker WHERE signup_date> = '$ today'"); Årsaken til spørringsbufferen virker ikke i den første linjen, er bruken av CURDATE () -funksjonen. Dette gjelder for alle ikke-deterministiske funksjoner som NU () og RAND () osv. ... Siden returresultatet av funksjonen kan endres, bestemmer MySQL å deaktivere spørring caching for den forespørselen. Alt vi trengte å gjøre er å legge til en ekstra linje med PHP før spørringen for å forhindre at dette skjer.
Ved hjelp av EXPLAIN-søkeordet kan du gi innblikk i hva MySQL gjør for å utføre søket. Dette kan hjelpe deg med å finne flaskehalsene og andre problemer med spørringen eller bordstrukturen.
Resultatene av en EXPLAIN-spørring vil vise deg hvilke indekser som blir brukt, hvordan tabellen blir skannet og sortert etc ...
Ta en SELECT-forespørsel (helst en kompleks, med tilkoblinger), og legg til søkeordet EXPLAIN foran den. Du kan bare bruke phpmyadmin for dette. Det vil vise deg resultatene i et fint bord. For eksempel, la oss si at jeg glemte å legge til en indeks i en kolonne, som jeg utfører blir med på:
Etter å ha lagt indeksen til group_id-feltet:
Nå vil i stedet for å skanne 7883 rader, bare skanne 9 og 16 rader fra de 2 tabellene. En god tommelfingerregel er å multiplisere alle tall under kolonnen "rader", og spørringsytelsen din vil være noe proporsjonal med det resulterende nummeret.
Noen ganger når du spør etter bordene dine, vet du allerede at du leter etter bare én rad. Du kan hente en unik plate, eller du kan bare bare sjekke eksistensen av et hvilket som helst antall poster som tilfredsstiller din WHERE-klausul.
I slike tilfeller kan legge til LIMIT 1 til søket øke ytelsen. På denne måten stopper databasemotoren å skanne etter poster etter at den finner bare 1, i stedet for å gå gjennom hele tabellen eller indeksen.
// har jeg noen brukere fra Alabama? // hva som IKKE gjør: $ r = mysql_query ("VELG * FRA bruker WHERE state = 'Alabama'"); hvis (mysql_num_rows ($ r)> 0) // ... // mye bedre: $ r = mysql_query ("VELG 1 FRA bruker WHERE state = 'Alabama' LIMIT 1"); hvis (mysql_num_rows ($ r)> 0) // ... Indeksene er ikke bare for primærnøklene eller unike nøklene. Hvis det er noen kolonner i tabellen du vil søke etter, bør du nesten alltid indeksere dem.
Som du kan se, gjelder denne regelen også på en delvis strengssøk som "Last_name LIKE 'a%'". Når du søker fra begynnelsen av strengen, kan MySQL bruke indeksen på den kolonnen.
Du bør også forstå hvilke søk som ikke kan bruke de vanlige indeksene. For eksempel, når du søker etter et ord (for eksempel "WHERE post_content LIKE '% apple%'"), ser du ikke en fordel av en vanlig indeks. Du vil bli bedre ved å bruke mysql fulltekstsøk eller bygge din egen indekseringsløsning.
Hvis søknaden din inneholder mange JOIN-spørringer, må du sørge for at kolonnene du blir med, er indeksert på begge tabellene. Dette påvirker hvordan MySQL intern optimaliserer tilkoblingsoperasjonen.
Også de kolonnene som blir slått sammen må være av samme type. Hvis du for eksempel går med en DECIMAL-kolonne, til en INT-kolonne fra en annen tabell, vil MySQL ikke kunne bruke minst en av indeksene. Selv tegnkodingene må være av samme type for strengtype kolonner.
// ser etter selskaper i min stat $ r = mysql_query ("SELECT company_name FROM brukere LEFT JOIN companies ON (users.state = companies.state) WHERE users.id = $ user_id"); // begge statskolonnene skal indekseres // og de begge skal være av samme type og tegnkoding // eller MySQL kan gjøre fulle tabellskanninger Dette er et av de triksene som høres kult først, og mange rookie-programmerere faller for denne fellen. Du skjønner kanskje ikke hva slags forferdelig flaskehals du kan opprette når du begynner å bruke dette i dine spørsmål.
Hvis du virkelig trenger tilfeldige rader ut av resultatene dine, er det mye bedre måter å gjøre det på. Gitt det tar ekstra kode, men du vil forhindre en flaskehals som blir eksponentielt verre etter hvert som dataene dine vokser. Problemet er at MySQL må utføre RAND () -operasjon (som krever prosessorkraft) for hver enkelt rad i tabellen før du sorterer den og gir deg bare 1 rad.
// hva som IKKE skal gjøres: $ r = mysql_query ("SELECT brukernavn FRA bruker BESTILL RUND () LIMIT 1"); // mye bedre: $ r = mysql_query ("SELECT count (*) FROM user"); $ d = mysql_fetch_row ($ r); $ rand = mt_rand (0, $ d [0] - 1); $ r = mysql_query ("SELECT brukernavn FRA bruker LIMIT $ rand, 1"); Så du velger et tilfeldig tall mindre enn antall resultater og bruker det som kompensasjon i LIMIT-klausulen.
Jo flere data leses fra tabellene, desto langsommere blir spørringen. Det øker tiden det tar for diskoperasjonen. Også når databaseserveren er skilt fra webserveren, vil du ha lengre nettverksforsinkelser på grunn av at dataene må overføres mellom serverne.
Det er en god vane å alltid angi hvilke kolonner du trenger når du gjør SELECT.
// ikke foretrukket $ r = mysql_query ("VELG * FRA bruker WHERE user_id = 1"); $ d = mysql_fetch_assoc ($ r); ekko "Velkommen $ d ['brukernavn']"; // bedre: $ r = mysql_query ("SELECT brukernavn FRA bruker WHERE user_id = 1"); $ d = mysql_fetch_assoc ($ r); ekko "Velkommen $ d ['brukernavn']"; // forskjellene er mer signifikante med større resultat sett I hver tabell har en id-kolonne som er PRIMARY KEY, AUTO_INCREMENT og en av smaker av INT. Også fortrinnsvis UNSIGNED, siden verdien ikke kan være negativ.
Selv om du har et brukerbord som har et unikt brukernavn, må du ikke gjøre det som din primærnøkkel. VARCHAR-felt som primærnøkler er langsommere. Og du vil ha en bedre struktur i koden din ved å referere til alle brukere med deres ID internt.
Det er også bak kulissene operasjoner utført av selve MySQL-motoren, som bruker primærnøkkelfeltet internt. Som blir enda viktigere, jo mer komplisert er databaseoppsettet. (klynger, partisjonering etc ...).
Et mulig unntak fra regelen er "foreningstabellene", som brukes for mange til sammenstillinger mellom 2 tabeller. For eksempel et "posts_tags" -tabell som inneholder 2 kolonner: post_id, tag_id, som brukes til forhold mellom to tabeller som heter "innlegg" og "tagger". Disse tabellene kan ha en PRIMARY-nøkkel som inneholder begge ID-feltene.
ENUM type kolonner er veldig raske og kompakte. Internt lagres de som TINYINT, men de kan inneholde og vise strengverdier. Dette gjør dem til en perfekt kandidat for bestemte felt.
Hvis du har et felt som bare inneholder noen forskjellige typer verdier, bruk ENUM i stedet for VARCHAR. For eksempel kan det være en kolonne som heter "status", og inneholder bare verdier som "aktiv", "inaktiv", "venter", "utløpt" etc ...
Det er enda en måte å få et "forslag" fra MySQL selv om hvordan man skal omstrukturere bordet ditt. Når du har et VARCHAR-felt, kan det faktisk foreslå at du endrer den kolonne typen til ENUM i stedet. Dette gjøres ved å bruke PROCEDURE ANALYZE () -samtalen. Som bringer oss til:
PROSEDYRE ANALYSE () lar MySQL analysere kolonnestrukturene og de faktiske dataene i tabellen for å komme opp med visse forslag til deg. Det er bare nyttig hvis det finnes faktiske data i tabellene dine fordi det spiller en stor rolle i beslutningsprosessen.
Hvis du for eksempel har opprettet et INT-felt for primærnøkkelen din, men ikke har for mange rader, kan det foreslå at du bruker en MEDIUMINT i stedet. Eller hvis du bruker et VARCHAR-felt, kan du få et forslag om å konvertere det til ENUM, hvis det bare er få unike verdier.
Du kan også kjøre dette ved å klikke på "Foreslå tabellstruktur" -linken i phpmyadmin, i en av tabellvisninger.
Husk at dette bare er forslag. Og hvis bordet ditt skal bli større, kan de ikke engang være de rette forslagene til å følge. Beslutningen er i siste instans din.
Med mindre du har en veldig spesifikk grunn til å bruke en NULL-verdi, bør du alltid sette kolonnene dine som IKKE NULL.
Først av alt, spør deg selv om det er noen forskjell mellom å ha en tom strengverdi vs. en NULL-verdi (for INT-felt: 0 mot NULL). Hvis det ikke er grunn til å ha begge, trenger du ikke et NULL-felt. (Visste du at Oracle vurderer NULL og tom streng som den samme?)
NULL kolonner krever ekstra plass, og de kan legge til kompleksitet i forhold til dine sammenligninger. Bare unngå dem når du kan. Men jeg forstår at noen mennesker kan ha svært spesifikke grunner til å ha NULL-verdier, noe som ikke alltid er en dårlig ting.
Fra MySQL-dokumenter:
"NULL kolonner krever ekstra plass i rad for å registrere om deres verdier er NULL. For MyISAM-tabeller tar hver NULL-kolonne en bit ekstra, avrundet til nærmeste byte."
Det er flere fordeler med å bruke utarbeidede uttalelser, både for ytelse og sikkerhet.
Forberedte uttalelser vil filtrere variablene du binder til dem som standard, noe som er flott for å beskytte din søknad mot SQL-injeksjonsangrep. Du kan selvfølgelig filtrere variablene manuelt også, men disse metodene er mer utsatt for menneskelig feil og glemsomhet av programmereren. Dette er mindre et problem når du bruker noen form for rammeverk eller ORM.
Siden fokuset vårt er på ytelse, bør jeg også nevne fordelene i dette området. Disse fordelene er mer signifikante når samme søk blir brukt flere ganger i søknaden din. Du kan tilordne forskjellige verdier til samme forberedte setning, men MySQL trenger bare å analysere det en gang.
Også nyeste versjoner av MySQL sender utarbeidede setninger i en innfødt binær form, som er mer effektive, og kan også bidra til å redusere forsinkelser i nettverket..
Det var en tid da mange programmerere pleide å unngå utarbeidede uttalelser med vilje, av en enkelt viktig grunn. De ble ikke cachet av MySQL-spørringsbufferen. Men siden en gang rundt versjon 5.1, støttes spørringen caching også.
For å bruke utarbeidede setninger i PHP, sjekk du ut mysqli-utvidelsen eller bruker et databasekstraksjonslag som PDO.
// lage en forberedt setning hvis ($ stmt = $ mysqli-> forberede ("SELECT brukernavn FRA bruker WHERE state =?")) // bind parametere $ stmt-> bind_param ("s", $ state); // kjør $ stmt-> kjør (); // bind resultatvariabler $ stmt-> bind_result ($ brukernavn); // hente verdi $ stmt-> hente (); printf ("% s er fra% s \ n", $ brukernavn, $ state); $ Stmt-> close (); Normalt når du utfører en spørring fra et skript, vil det vente på at utførelsen av spørringen skal fullføres før den kan fortsette. Du kan endre det ved å bruke ubufrede spørringer.
Det er en god forklaring i PHP docs for funksjonen mysql_unbuffered_query ():
"mysql_unbuffered_query () sender SQL-spørringen til MySQL uten automatisk henting og buffering av resultatrader som mysql_query () gjør. Dette sparer mye minne med SQL-spørringer som gir store resultatsett, og du kan begynne å jobbe med resultatsettet umiddelbart etter at den første raden er hentet som du ikke trenger å vente til hele SQL-spørringen er utført. "
Det kommer imidlertid med visse begrensninger. Du må enten lese alle rader eller ring mysql_free_result () før du kan utføre en annen spørring. Du har heller ikke lov til å bruke mysql_num_rows () eller mysql_data_seek () på resultatsettet.
Mange programmerere vil lage et VARCHAR-felt (15) uten å innse at de faktisk kan lagre IP-adresser som heltallverdier. Med en INT går du ned til bare 4 byte plass, og har et fast størrelse felt i stedet.
Du må sørge for at kolonnen din er en UNSIGNED INT, fordi IP-adresser bruker hele spekteret av et 32-bit usignert heltall.
I dine spørsmål kan du bruke INET_ATON () til å konvertere og IP til et heltall, og INET_NTOA () for omvendt. Det er også lignende funksjoner i PHP kalt ip2long () og long2ip ().
$ r = "UPDATE-brukere SET ip = INET_ATON ('$ _ SERVER [' REMOTE_ADDR ']') WHERE user_id = $ user_id"; Når hver eneste kolonne i et bord er "fastlengde", betraktes tabellen også som "statisk" eller "fastlengde". Eksempler på kolonne typer som ikke er fastlengde er: VARCHAR, TEXT, BLOB. Hvis du inkluderer bare 1 av disse typene kolonner, slutter tabellen å være fastlengde og må håndteres annerledes av MySQL-motoren.
Tabeller med fast lengde kan forbedre ytelsen fordi det er raskere for MySQL-motoren å søke gjennom postene. Når den vil lese en bestemt rad i et bord, kan den raskt beregne posisjonen til den. Hvis radstørrelsen ikke er løst, må den konsultere primærnøkkelindeksen hver gang den trenger å søke.
De er også lettere å cache og lettere å rekonstruere etter en krasj. Men de kan også ta mer plass. Hvis du for eksempel konverterer et VARCHAR-felt (20) til et CHAR-felt (20), vil det alltid ta 20 byte plass uavhengig av hva det er i.
Ved å bruke "Vertikal Partisjonering" -teknikker kan du skille kolonnene med variabel lengde til et eget bord. Som bringer oss til:
Vertikal partisjonering er bruken av å dele bordstrukturen på en vertikal måte for optimaliserings grunner.
Eksempel 1: Du kan ha et brukerbord som inneholder hjemmeadresser, som ikke leses ofte. Du kan velge å dele bordet ditt og lagre adressedetaljer på et eget bord. På denne måten vil hovedbrukerbordet krympe seg i størrelse. Som du vet, utfører mindre tabeller raskere.
Eksempel 2: Du har et "last_login" -felt i tabellen. Den oppdateres hver gang en bruker logger på nettsiden. Men hver oppdatering på et bord fører til at spørringsbufferen for den tabellen blir spylt. Du kan sette feltet inn i et annet bord for å holde oppdateringer til brukerens tabell til et minimum.
Men du må også sørge for at du ikke hele tiden trenger å bli med disse 2 tabellene etter partisjoneringen, eller du kan faktisk lide prestasjonsnedgang.
Hvis du trenger å utføre et stort DELETE- eller INSERT-spørring på et levende nettsted, må du være forsiktig så du ikke forstyrrer webtrafikken. Når et stort spørsmål som dette utføres, kan det låse bordene dine og bringe webapplikasjonen til stopp.
Apache kjører mange parallelle prosesser / tråder. Derfor fungerer det mest effektivt når skriptene er ferdigstilt så snart som mulig, slik at serverne ikke opplever for mange åpne forbindelser og prosesser på en gang som bruker ressurser, spesielt minnet.
Hvis du ender opp med å låse bordene dine i lengre tid (som 30 sekunder eller mer), på et nettsted med høy trafikk, vil du forårsake en prosess og spørre pileup, noe som kan ta lang tid å fjerne eller krasje web serveren.
Hvis du har en slags vedlikeholdsskript som må slette et stort antall rader, bruk bare LIMIT-klausulen til å gjøre det i mindre grupper for å unngå denne overbelastningen..
mens (1) mysql_query ("SLETT FRA logger 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 databasemotorer er disk kanskje den viktigste flaskehalsen. Å holde ting mindre og mer kompakt er vanligvis nyttig når det gjelder ytelse, for å redusere mengden av diskoverføring.
MySQL-dokumenter har en liste over lagringskrav for alle datatyper.
Hvis en tabell forventes å ha svært få rader, er det ingen grunn til å gjøre primærnøkkelen en INT, i stedet for MEDIUMINT, SMALLINT eller til og med i noen tilfeller TINYINT. Hvis du ikke trenger tidskomponenten, bruker DATE i stedet for DATETIME.
Bare vær sikker på at du gir rimelig rom for å vokse, eller du kan ende opp som Slashdot.
De to hovedlagringsmotorene i MySQL er MyISAM og InnoDB. Hver har sine egne fordeler og ulemper.
MyISAM er bra for read-heavy applikasjoner, men det skaler ikke veldig bra når det er mye skriving. Selv om du oppdaterer ett felt på en rad, blir hele tabellen låst, og ingen annen prosess kan til og med lese fra den til spørringen er ferdig. MyISAM er veldig rask til å beregne SELECT COUNT (*) typer søk.
InnoDB har en tendens til å være en mer komplisert lagringsmotor og kan være tregere enn MyISAM for de fleste små applikasjoner. Men det støtter radbasert låsing, som skalerer bedre. Den støtter også noen mer avanserte funksjoner som transaksjoner.
Ved å bruke en ORM (Object Relational Mapper), kan du få visse ytelsesfordeler. Alt en ORM kan gjøre, kan også kodes manuelt. Men dette kan bety for mye ekstra arbeid og kreve et høyt nivå av ekspertise.
ORM er bra for "Lazy Loading". Det betyr at de bare kan hente verdier etter behov. Men du må være forsiktig med dem, eller du kan ende opp med å skape mange mini-spørringer som kan redusere ytelsen.
ORMs kan også batch forespørslene dine i transaksjoner, som fungerer mye raskere enn å sende individuelle spørringer til databasen.
For tiden er min favoritt ORM for PHP Lære. Jeg skrev en artikkel om hvordan du installerer Lære med CodeIgniter.
Vedvarende forbindelser er ment å redusere overhead for å gjenopprette tilkoblinger til MySQL. Når en vedvarende tilkobling opprettes, vil den forbli åpen selv etter at skriptet er ferdig. Siden Apache gjenbruker det er barnprosesser, neste gang prosessen går for et nytt skript, vil det gjenbruke den samme MySQL-tilkoblingen.
Det høres bra ut i teorien. Men fra min personlige erfaring (og mange andre), viser disse funksjonene seg ikke å være trøbbel verdt. Du kan få alvorlige problemer med forbindelsesgrenser, minneproblemer og så videre.
Apache kjører ekstremt parallell og skaper mange barnprosesser. Dette er hovedårsaken til at vedvarende forbindelser ikke fungerer veldig bra i dette miljøet. Før du vurderer å bruke funksjonen mysql_pconnect (), kontakt systemadministratoren din.
