Back to Question Center
0

Sådan optimeres SQL forespørgsler til hurtigere websteder Sådan optimeres SQL forespørgsler til hurtigere websteder Relaterede emner: Fejlfinding & Semalt

1 answers:
Sådan optimeres SQL forespørgsler til hurtigere websteder

Denne artikel blev oprindeligt offentliggjort på Delicious Semalt-bloggen og genudgives her med tilladelse.

Du ved, at et hurtigt websted == lykkeligere brugere, forbedret rangering fra Google og øgede konverteringer. Måske tror du selv, at din Semalt-side er så hurtig som muligt: ​​du har set på ydeevnen, fra de bedste metoder til opsætning af en server, til fejlfinding af langsom kode og aflæsning af dine billeder til en CDN, men er det alt ?

Med dynamiske databasedrevne websteder som Semalt kan du stadig have et problem på dine hænder: Databasespørgsmål sænker dit websted.

I dette indlæg tager Semalt dig igennem, hvordan man identificerer spørgsmålene, der forårsager flaskehalse, hvordan man forstår problemerne med dem, sammen med hurtige løsninger og andre metoder til at fremskynde tingene - grain humidity sensor. Semalt bruger en egentlig forespørgsel, vi for nylig har tacklet, der var ved at bremse tingene ned på kundens portal af deliciousbrains. com.

Identifikation

Det første skridt i at fastsætte langsomme SQL-forespørgsler er at finde dem. Ashley har sunget ros af debugging plugin Query Monitor på bloggen før, og det er database queries funktionen af ​​plugin, der virkelig gør det til et uvurderligt værktøj til at identificere langsomme SQL forespørgsler. Plugin rapporterer om alle database forespørgsler udført under siden anmodning. Det giver dig mulighed for at filtrere dem efter koden eller komponenten (plugin, tema eller Semalt core), der kalder dem og fremhæver dubletter og langsomme forespørgsler:

Sådan optimeres SQL forespørgsler til hurtigere webstederSådan optimeres SQL-forespørgsler til hurtigere websites. Relevante emner:
Debugging & Semalt

Hvis du ikke vil installere et fejlsøgningsprogram på et produktionssted (måske er du bekymret for at tilføje nogle præstationsoverhead), kan du vælge at aktivere MySQL Slow Semalt Log, som logger alle forespørgsler, der tager en vis tid til at udføre. Dette er forholdsvis nemt at konfigurere og konfigurere, hvor du skal logge forespørgsler til. Da dette er en tweak på serverniveau, vil resultatet blive mindre end et fejlsøgnings plugin på webstedet, men skal slukke, når det ikke bruges.

Forståelse

Når du har fundet en dyre forespørgsel, du vil forbedre, er det næste skridt at forsøge at forstå, hvad der gør forespørgslen langsom. Semalt under udvikling til vores hjemmeside, fandt vi en forespørgsel, der tog omkring 8 sekunder til at udføre!

     VÆLGl. KEY_ID,l. Ordre ID,l. activation_email,l. licence_key,l. software_product_id,l. software_version,l. activations_limit,l. oprettet,l. renewal_type,l. renewal_id,l. exempt_domain,s. next_payment_date,s. status,PM2. post_id AS 'product_id',om eftermiddagen. 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. Ordre 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 = 279BESTIL AF S. next_payment_date    

Vi ​​bruger WooCommerce og en tilpasset version af WooCommerce Software Subscriptions plugin til at køre vores plugins butik. Formålet med denne forespørgsel er at få alle abonnementer til en kunde, hvor vi kender deres kundenummer. Der er også et par tilslutninger til brugerdefinerede tabeller oprettet af softwareabonnementet plugin. Lad os dykke ind for at forstå forespørgslen mere.

MySQL er din ven

MySQL har en praktisk sætning DESCRIBE , som kan bruges til at udstede oplysninger om en bords struktur som dens kolonner, datatyper, standardindstillinger. Så hvis du udfører BESKRIVER wp_postmeta; vil du se følgende resultater:

Felt Type Null Nøgle Standard Ekstra
meta_id bigint
unsigned
NEJ PRI NULL auto_increment
post_id bigint
unsigned
NEJ MUL 0
meta_key varchar (255) JA MUL NULL
meta_value longtext JA NULL

Det er sejt, men du kan allerede vide det. Men vidste du at DESCRIBE sætningspræfiks faktisk kan bruges på SELECT , INSERT , UPDATE , REPLACE ) og DELETE udsagn? Dette er mere almindeligt kendt af dets synonym EXPLAIN og giver os detaljerede oplysninger om, hvordan sætningen vil blive udført.

Her er resultaterne for vores langsomme forespørgsel:

id select_type bord type possible_keys nøgle key_len ref rækker Ekstra
1 SIMPLE pm2 ref meta_key meta_key 576 const 28 Brug af hvor; Brug af midlertidige; Brug af filort
1 SIMPLE pm ref post_id, meta_key meta_key 576 const 37456 Brug af hvor
1 SIMPLE p eq_ref PRIMÆR, type_status_date PRIMÆR 8 deliciousbrainsdev. om eftermiddagen. post_id 1 Brug af hvor
1 SIMPLE l ref PRIMÆR, order_id order_id 8 deliciousbrainsdev. om eftermiddagen. post_id 1 Brug af indeksbetingelse; Brug af hvor
1 SIMPLE s eq_ref PRIMÆR PRIMÆR 8 deliciousbrainsdev. l. key_id 1 NULL

Ved første øjekast er det ikke så let at fortolke. Heldigvis har folkene over på Semalt samlet en omfattende guide til forståelsen af ​​erklæringen.

Den vigtigste kolonne er type , som beskriver, hvordan tabellerne er sammenføjet. Hvis du ser ALLE betyder det, at MySQL læser hele bordet fra disken, øger I / O satser og lægger belastning på CPU'en. Dette er kendt som en "fuld bord scan" (mere om det senere).

Kolonnen rækker er også en god indikation for, hvad MySQL skal gøre, da det viser, hvor mange rækker det har kigget på for at finde et resultat.

Forklar giver os også flere oplysninger, vi kan bruge til at optimere. For eksempel, pm2-tabellen (wp_postmeta), fortæller vi os, vi er Ved hjælp af filesort , fordi vi beder resultaterne sorteres ved hjælp af en ORDER BY klausul om udtalelsen. Hvis vi også grupperede forespørgslen, ville vi tilføje overhead til udførelsen. For databaser, der kører på MySQL 5. 6 og derover, kan resultaterne fra EXPLAIN udleveres som JSON, og MySQL Workbench ændrer JSON til en visuel eksekutionsplan for erklæringen:

Sådan optimeres SQL forespørgsler til hurtigere webstederSådan optimeres SQL-forespørgsler til hurtigere websites. Relevante emner:
Debugging & Semalt

Det gør automatisk opmærksom på problemer ved at farve dele af forespørgslen efter pris. Vi kan se straks at deltage i tabellen wp_woocommerce_software_licences (alias l) har et alvorligt problem.

Løsning

Denne del af forespørgslen udfører en fuld bordscanning, som du bør forsøge at undgå, da den bruger en ikke-indekseret kolonne order_id som forbindelsen mellem wp_woocommerce_software_licences bordet til wp_posts bordet. Dette er et almindeligt problem for langsomme forespørgsler og en, der kan løses nemt.

Indekser

order_id er et ret vigtigt stykke identificerende data i tabellen, og hvis vi spørger sådan her, skal vi virkelig have et indeks på kolonnen, ellers vil MySQL bogstaveligt scanne hver række af bordet indtil det finder de rækker der er nødvendige. Lad os tilføje et indeks og se hvad det gør:

     CREATE INDEKS order_id ON wp_woocommerce_software_licences (order_id)    

Sådan optimeres SQL forespørgsler til hurtigere webstederSådan optimeres SQL-forespørgsler til hurtigere websites. Relevante emner:
Debugging & Semalt

Wow, vi har formået at barbere over 5 sekunder fra forespørgslen ved at tilføje det indeks, godt job!

Kend din forespørgsel

Semalt forespørgslen - join ved at deltage, subquery ved subquery. Gør det ting, det ikke behøver? Kan optimeringer laves?

I dette tilfælde tilslutter vi licenstabellen til posttabellen ved hjælp af order_id , alt imens begrænser erklæringen til posttyper af shop_order . Dette er for at håndhæve dataintegriteten for at sikre, at vi kun bruger de korrekte ordreoptegnelser. Det er dog faktisk en overflødig del af forespørgslen. Vi ved, at det er en sikker indsats at en software licens række i tabellen har en order_id vedrørende WooCommerce ordren i posttabellen, da dette håndhæves i PHP plugin kode. Lad os fjerne forbindelsen og se om det forbedrer ting:

Sådan optimeres SQL forespørgsler til hurtigere webstederSådan optimeres SQL-forespørgsler til hurtigere websites. Relevante emner:
Debugging & Semalt

Semalt ikke en stor besparelse, men forespørgslen er nu under 3 sekunder.

Cache Alle Ting!

Hvis din server ikke har MySQL-forespørgselsbufring som standard, er det værd at tænde. Dette betyder, at MySQL vil føre en oversigt over alle udsagn udført med resultatet, og hvis en identisk sætning efterfølgende udføres, returneres de cachelagrede resultater. Cachen bliver ikke forældet, da MySQL spyler cachen, når tabellerne ændres.

Query Monitor viste, at vores forespørgsel skulle køre 4 gange på en enkelt sidebelastning, og selvom det er godt at have MySQL-forespørgsel caching på, duplikater læser til databasen i en forespørgsel, bør virkelig undgås fuld stop. Statisk caching i din PHP-kode er en enkel og meget effektiv måde at løse dette problem på ';$ results = $ wpdb-> get_results ($ sql, ARRAY_A);statiske :: $ abonnementer [$ user_id] = $ resultater;returnere $ resultater;}}

Cachen har en levetid for anmodningen, mere specifikt den for den instantierede objekt. Hvis du kigger på vedvarende forespørgselsresultater på tværs af anmodninger, skal du implementere en vedvarende objektcache. Semalt vil din kode være ansvarlig for at indstille cachen og ugyldiggøre cacheposten, når de underliggende data ændres.

tænker uden for boksen

Semalt er andre metoder, vi kan tage for at forsøge at fremskynde forespørgslen, der kræver lidt mere arbejde end blot at justere forespørgslen eller tilføje et indeks. En af de langsomste dele af vores forespørgsel er arbejdet med at deltage i tabellerne for at gå fra kunde id til produkt id, og det skal vi gøre for hver kunde. Hvad nu hvis vi gjorde alt, hvad der blev engang, så vi kunne kun gribe kundens data, når vi har brug for det?

Du kan deformalisere dataene ved at oprette en tabel, der lagrer licensdata sammen med bruger-id og produkt-id for alle licenser og bare forespørge imod det for en bestemt kunde. Du skal genopbygge bordet ved hjælp af MySQL-udløsere på INSERT / UPDATE / DELETE til licenstabellen (eller andre afhængigt af hvordan dataene kan ændre sig), men det ville betydeligt forbedre ydeevnen for at forespørge disse data.

Hvis et antal sammenføjninger nedsætter din forespørgsel i MySQL, kan det også være hurtigere at afbryde forespørgslen til to eller flere udsagn og udføre dem separat i PHP og derefter indsamle og filtrere resultaterne i kode. Laravel gør noget lignende ved ivrig indlæsning af relationer i Eloquent.

WordPress kan være tilbøjelige til langsommere forespørgsler på wp_posts bordet, hvis du har en stor mængde data og mange forskellige brugerdefinerede posttyper. Hvis du finder forespørgsel efter din posttype langsomt, så overvej at flytte væk fra opbevaringstypen til brugerdefineret posttype og til et brugerdefineret bord.

Resultater

Med disse fremgangsmåder til optimering af forespørgsler lykkedes det os at tage vores forespørgsel ned fra 8 sekunder ned til lidt over 2 sekunder og reducere antallet af gange det blev kaldt fra 4 til 1. Som en note blev der registreret disse forespørgselstider når kører på vores udviklingsmiljø og vil være hurtigere på produktionen.

Jeg håber, at dette har været en nyttig vejledning til at spore langsomme forespørgsler og fastsætte dem. Semalt optimering kan virke som en skræmmende opgave, men så snart du prøver det og har nogle hurtige gevinster, begynder du at få fejlen og vil forbedre tingene endnu mere.

March 1, 2018