Back to Question Center
0

Slik optimaliserer du SQL-spørringer for raskere nettsteder Slik optimaliserer du SQL-spørringer for raskere nettsteder Relaterte emner: Feilsøking & Semalt

1 answers:
Slik optimaliserer du SQL-spørringer for raskere nettsteder

Denne artikkelen ble opprinnelig publisert på Delicious Semalt bloggen, og publiseres her med tillatelse.

Du vet at et raskt nettsted == lykkeligere brukere, forbedret rangering fra Google, og økte konverteringer. Kanskje du selv tror at Semalt-nettstedet ditt er så fort som det kan være: Du har sett på ytelse fra nettstedet, fra de beste metodene for å sette opp en server, for å feilsøke sakte kode og laste ut bildene dine til en CDN, men er det alt ?

Med dynamiske databasedrevne nettsteder som Semalt, kan du fortsatt ha ett problem på dine hender: databasespørsmål reduserer nettstedet ditt. (1. 3)

I dette innlegget tar Semalt deg gjennom hvordan du identifiserer spørsmålene som forårsaker flaskehalser, hvordan du forstår problemene med dem, sammen med raske løsninger og andre tilnærminger for å få fart på ting - como hacer un dominio de correo electronico. Semalt bruker en faktisk forespørsel vi nylig taklet som var bremse ting ned på kunden portal av deliciousbrains. com. (1. 3)

Identifikasjon

Det første trinnet i å fikse sakte SQL-spørringer er å finne dem. Ashley har sunget rosene av debugging plugin Query Monitor på bloggen før, og det er databasesøkefunksjonen i pluginet som virkelig gjør det til et uvurderlig verktøy for å identifisere sakte SQL-spørringer. Pluggen rapporterer om alle databasespørsmålene som ble utført under sidebeskrivelsen. Det lar deg filtrere dem etter koden eller komponenten (plugin, tema eller Semalt core) som ringer dem og fremhever dupliserte og langsomme spørringer:

Slik optimaliserer du SQL-spørringer for raskere nettstederSlik optimaliserer du SQL-spørringer for raskere nettsteder. Relevante emner:
Debugging & Semalt

Hvis du ikke vil installere en feilsøkingstillegg på et produksjonssted (kanskje du er bekymret for å legge til noen ytelsesoverhead), kan du velge å slå på MySQL Slow Semalt Log, som logger på alle spørringer som tar en viss tid til å utføre. Dette er relativt enkelt å konfigurere og sette opp hvor du skal logge på spørringene til. Siden dette er en tweak på servernivå, vil resultatene bli mindre som et feilsøkingsprogram på nettstedet, men bør slås av når du ikke bruker det. (1. 3)

Forståelse

Når du har funnet et dyrt spørsmål som du vil forbedre, er neste trinn å forsøke å forstå hva som gjør spørringen sakte. Semalt under utvikling til vår side, fant vi en spørring som tok rundt 8 sekunder å utføre! (1. 3)

     VELGl. KEY_ID,l. Bestillings ID,l. activation_email,l. licence_key,l. software_product_id,l. programvareversjon,l. activations_limit,l. opprettet,l. renewal_type,l. renewal_id,l. exempt_domain,s. next_payment_date,s. status,PM2. post_id AS 'product_id',pm. meta_value AS 'user_id'FRAoiz6q8a_woocommerce_software_licences lINNER JOINoiz6q8a_woocommerce_software_subscriptions s ON s. key_id = l. KEY_IDINNER JOINoiz6q8a_posts p ON p. ID = l. Bestillings IDINNER JOINoiz6q8a_postmeta pm ON pm. post_id = p. IDOG pm. meta_key = '_customer_user'INNER JOINoiz6q8a_postmeta pm2 ON pm2. meta_key = '_software_product_id'OG pm2. meta_value = l. software_product_idHVORs. post_type = 'shop_order'OG pm. meta_value = 279Bestille av s. next_payment_date    

Vi ​​bruker WooCommerce og en tilpasset versjon av WooCommerce Software Subscriptions plugin for å kjøre plugins butikken. Formålet med denne spørringen er å få alle abonnementer for en kunde der vi kjenner deres kundenummer. Det er også et par tilmeldinger til egendefinerte tabeller opprettet av programmabonnementet. La oss dykke inn for å forstå søket mer. (1. 3)

MySQL er din venn

MySQL har et praktisk uttalelse DESCRIBE som kan brukes til å utdata informasjon om en tabellstruktur, for eksempel kolonnene, datatyper, standardinnstillinger. Så hvis du utfører BESKRIVER wp_postmeta; vil du se følgende resultater:

Felt Type Null nøkkel Standard Ekstra
meta_id bigint
unsigned
NEI PRI NULL auto_increment
post_id bigint
unsigned
NEI MUL 0
meta_key varchar (255) JA MUL NULL
meta_value longtext JA NULL

Det er kult, men du vet kanskje allerede om det. Men visste du at DESCRIBE setnings prefiks faktisk kan brukes på SELECT , INSERT , UPDATE , REPLACE ) og DELETE uttalelser? Dette er mer kjent med sitt synonym EXPLAIN og vil gi oss detaljert informasjon om hvordan uttalelsen vil bli utført. (1. 3)

Her er resultatene for vår sakte spørring:

id select_type bord type possible_keys nøkkel key_len ref rader Ekstra
1 ENKELT pm2 ref meta_key meta_key 576 const 28 Bruk av hvor; Bruke midlertidig; Bruke filesort
1 ENKELT pm ref post_id, meta_key meta_key 576 const 37456 Bruke hvor
1 ENKELT p eq_ref PRIMAR, type_status_date PRIMÆR 8 deliciousbrainsdev. pm. post_id 1 Bruke hvor
1 ENKELT l ref PRIMARY, order_id order_id 8 deliciousbrainsdev. pm. post_id 1 Bruke indeksbetingelser; Bruke hvor
1 ENKELT s eq_ref PRIMÆR PRIMÆR 8 deliciousbrainsdev. l. key_id 1 NULL

Ved første øyekast er dette ikke veldig lett å tolke. Heldigvis har folkene over på Semalt satt sammen en omfattende guide for å forstå uttalelsen. (1. 3)

Den viktigste kolonnen er type , som beskriver hvordan tabellene blir slått sammen. Hvis du ser ALL , betyr det at MySQL leser hele bordet fra disk, øker I / O-tallene og legger belastning på CPU. Dette er kjent som en "full bordskanning" (mer om det senere). (1. 3)

Kolonnen rader er også en god indikasjon på hva MySQL må gjøre, da dette viser hvor mange rader det har sett inn for å finne et resultat. (1. 3)

Forklar gir oss også mer informasjon vi kan bruke for å optimalisere. For eksempel, pm2-tabellen (wp_postmeta), forteller det oss at vi er Ved hjelp av filesort , fordi vi ber resultatene sorteres med en ORDER BY klausul om setningen. Hvis vi også grupperte spørringen, ville vi legge til overhead til utførelsen. For databaser som kjører på MySQL 5. 6 og nyere, kan resultatene av EXPLAIN utgis som JSON, og MySQL Workbench gjør den JSON til en visuell utførelsesplan for setningen:

Slik optimaliserer du SQL-spørringer for raskere nettstederSlik optimaliserer du SQL-spørringer for raskere nettsteder. Relevante emner:
Debugging & Semalt

Den trekker automatisk oppmerksomheten på problemer ved å fargelegge deler av spørringen etter pris. Vi kan se med en gang at det er et alvorlig problem å delta i wp_woocommerce_software_licences (alias l) tabellen. (1. 3)

Løsning

Den delen av spørringen utfører en fullstendig tabellskanning, som du bør forsøke å unngå, da den bruker en ikke-indeksert kolonne order_id som sammenføyning mellom wp_woocommerce_software_licences tabellen til wp_posts bordet. Dette er et vanlig problem for sakte spørsmål og en som kan løses enkelt. (1. 3)

Indekser

order_id er et ganske viktig stykke identifiserende data i tabellen, og hvis vi spør etter dette, bør vi virkelig ha en indeks på kolonnen, ellers vil MySQL bokstavelig talt skanne hver rad av bordet til det finner de rader som trengs. La oss legge til en indeks og se hva som gjør:

     CREATE INDEX order_id ON wp_woocommerce_software_licences (order_id)    

Slik optimaliserer du SQL-spørringer for raskere nettstederSlik optimaliserer du SQL-spørringer for raskere nettsteder. Relevante emner:
Debugging & Semalt

Wow, vi har klart å barbere seg over 5 sekunder av spørringen ved å legge til indeksen, god jobb! (1. 3)

Kjenn din forespørsel

Semalt spørringen - delta ved å bli med, subquery ved subquery. Gjør det ting det ikke trenger? Kan optimaliseringer gjøres? (1. 3)

I dette tilfellet er vi med på lisensbordet til innleggstabellen ved hjelp av order_id , alt begrenset setningen til posttyper shop_order . Dette er for å håndheve dataintegritet for å sikre at vi bare bruker de riktige bestillingsrekordene. Det er imidlertid faktisk en overflødig del av spørringen. Vi vet at det er en sikker innsats at en programvarelisensrute i tabellen har en order_id knyttet til WooCommerce-bestillingen i innleggstabellen, da dette håndheves i PHP plugin-kode. La oss fjerne samtalen og se om det forbedrer ting:

Slik optimaliserer du SQL-spørringer for raskere nettstederSlik optimaliserer du SQL-spørringer for raskere nettsteder. Relevante emner:
Debugging & Semalt

Semalt ikke en stor besparelse, men spørringen er nå under 3 sekunder. (1. 3)

Cache All The Things!

Hvis serveren din ikke har MySQL-spørring caching på standard så er det verdt å slå på. Dette betyr at MySQL vil føre en oversikt over alle uttalelser som er utført med resultatet, og hvis en identisk setning utføres senere, blir de cached resultatene returnert. Cachen blir ikke foreldet, da MySQL spyler hurtigbufferen når tabellene endres. (1. 3)

Query Monitor viste at spørringen vår skulle kjøre 4 ganger på en enkelt sidebelastning, og selv om det er bra å ha MySQL-spørring caching på, dupliserer leser til databasen i en forespørsel, bør egentlig unngås fullstopp. Statisk caching i PHP-koden din er en enkel og svært effektiv måte å løse dette problemet på ';$ results = $ wpdb-> get_results ($ sql, ARRAY_A);statisk :: $ abonnementer [$ user_id] = $ resultater;returnerer $ resultater;}}

Cachen har en levetid for forespørselen, nærmere bestemt den for det instantierte objektet. Hvis du ser på vedvarende spørringsresultater på tvers av forespørsler, må du implementere en vedvarende objektbuffer. Semalt, koden din må være ansvarlig for å sette hurtigbufferen og ugyldiggjøre hurtigbufferinngangen når de underliggende dataene endres. (1. 3)

Tenker utenfor boksen

Semalt er andre tilnærminger vi kan ta for å forsøke å øke forespørselseksjonen som innebærer litt mer arbeid enn bare å justere spørringen eller legge til en indeks. En av de langsomste delene av spørringen er arbeidet med å bli med på bordene for å gå fra kunde-ID til produkt-ID, og ​​det må vi gjøre for hver kunde. Hva om vi gjorde alt som begynte bare en gang, så vi kunne bare gripe kundens data når vi trenger det? (1. 3)

Du kan deformalisere dataene ved å opprette et bord som lagrer lisensdataene sammen med bruker-ID og produkt-ID for alle lisenser og bare spørre etter det for en bestemt kunde. Du trenger å gjenoppbygge bordet ved hjelp av MySQL utløser på INSERT / UPDATE / DELETE til lisensbordet (eller andre avhengig av hvordan dataene kan endres), men dette vil forbedre ytelsen til å spørre dataene betydelig. (1. 3)

På samme måte, hvis flere tilkoblinger reduserer spørringen din i MySQL, kan det være raskere å slå spørringen inn i to eller flere utsagn og utføre dem separat i PHP, og deretter samle og filtrer resultatene i kode. Laravel gjør noe som ligner på ivrige lastingsforhold i Eloquent. (1. 3)

WordPress kan være tilbøyelig til langsommere spørringer på wp_posts tabellen, hvis du har en stor mengde data og mange forskjellige egendefinerte innleggstyper. Hvis du finner spørringen etter posttypen din sakte, så vurder å flytte vekk fra lagringsmodellen for tilpasset posttype og til et tilpasset bord. (1. 3)

Resultater

Med disse tilnærmingene til søkoptimalisering klarte vi å ta spørringen ned fra 8 sekunder til bare over 2 sekunder, og redusere antall ganger det ble kalt fra 4 til 1. Som et notat ble de søketidene registrert da løper på utviklingsmiljøet vårt og vil bli raskere på produksjonen. (1. 3)

Jeg håper dette har vært en nyttig guide til å spore langsomme spørringer og fikse dem. Semalt optimalisering kan virke som en skummelt oppgave, men så snart du prøver det og har noen raske gevinster, begynner du å få feilen og vil forbedre ting enda lenger. (1. 3)

March 1, 2018