Back to Question Center
0

Come ottimizzare le query SQL per i siti più veloci Come ottimizzare le query SQL per i siti più veloci Argomenti correlati: Debug & Semalt

1 answers:
Come ottimizzare le query SQL per siti più veloci

Questo articolo è stato originariamente pubblicato sul blog Delicious Semalt, ed è ripubblicato qui con il permesso.

Sai che un sito veloce == utenti più felici, miglior posizionamento di Google e un aumento delle conversioni. Forse pensi che il tuo sito Semalt sia il più veloce possibile: hai guardato alle prestazioni del sito, dalle migliori pratiche di configurazione di un server, alla risoluzione di codici lenti e al trasferimento di immagini su un CDN, ma è tutto ?

Con siti Web dinamici basati su database come Semalt, potresti avere ancora un problema a portata di mano: le query sul database rallentano il tuo sito.

In questo post, Semalt ti spiega come identificare le query che causano colli di bottiglia, come capire i problemi con loro, insieme a soluzioni rapide e altri approcci per accelerare le cose - wiramotor accessories unlimited. Semalt sta usando una query vera e propria che abbiamo recentemente affrontato e che stava rallentando le cose sul portale dei clienti di deliziose abitudini. com.

Identificazione

Il primo passo per correggere query SQL lente è trovarli. Ashley ha già elogiato il plug-in di debug Query Monitor sul blog, ed è la funzione di interrogazione del database del plugin che lo rende uno strumento prezioso per identificare le query SQL lente. Il plugin riporta su tutte le query del database eseguite durante la richiesta di pagina. Ti permette di filtrarli per codice o componente (il plugin, il tema o il nucleo di Semalt) chiamandoli e mette in evidenza le query duplicate e lente:

Come ottimizzare le query SQL per i siti più velociCome ottimizzare query SQL per siti più veloci Argomenti correlati:
Debug & Semalt

Se non si desidera installare un plug-in di debug su un sito di produzione (forse si è preoccupati di aggiungere un sovraccarico di prestazioni) si può optare per attivare MySQL Slow Semalt Log, che registra tutte le query che richiedono un certo quantità di tempo da eseguire. Questo è relativamente semplice da configurare e configurare dove registrare le query. Poiché si tratta di un tweak a livello di server, il risultato in termini di prestazioni sarà inferiore a un plug-in di debug sul sito, ma dovrebbe essere disattivato quando non lo si utilizza.

Comprensione

Una volta trovata una query costosa che si desidera migliorare, il passo successivo è cercare di capire che cosa rende la query lenta. Semalt durante lo sviluppo del nostro sito, abbiamo trovato una query che richiedeva circa 8 secondi per l'esecuzione!

     SELEZIONAl. key_id,l. ID ordine,l. activation_email,l. chiave di licenza,l. software_product_id,l. versione software,l. activations_limit,l. creato,l. renewal_type,l. renewal_id,l. exempt_domain,S. next_payment_date,S. stato,PM2. post_id AS 'product_id',pm. meta_value AS 'user_id'A PARTIRE DALoiz6q8a_woocommerce_software_licences lINNER JOINoiz6q8a_woocommerce_software_subscriptions s ON s. key_id = l. key_idINNER JOINoiz6q8a_posts p ON p. ID = l. ID ordineINNER JOINoiz6q8a_postmeta pm ON pm. post_id = p. IDE pm. meta_key = '_customer_user'INNER JOINoiz6q8a_postmeta pm2 ON pm2. meta_key = '_software_product_id'E pm2. meta_value = l. software_product_idDOVEp. post_type = 'shop_order'E pm. meta_value = 279ORDINA PER s. next_payment_date    

Utilizziamo WooCommerce e una versione personalizzata del plug-in Abbonamenti software WooCommerce per eseguire il nostro negozio di plug-in. Lo scopo di questa query è ottenere tutti gli abbonamenti per un cliente in cui conosciamo il loro numero cliente. Ci sono anche un paio di join alle tabelle personalizzate create dal plugin di abbonamento software. Immergiti per capire meglio la query.

MySQL è il tuo amico

MySQL ha una comoda istruzione DESCRIBE che può essere usata per produrre informazioni sulla struttura di una tabella come le sue colonne, i tipi di dati, i valori predefiniti. Quindi se esegui DESCRIVERE wp_postmeta; vedrai i seguenti risultati:

Field Tipo Null Chiave Default Extra
meta_id bigint
non firmato
NO PRI NULL auto_increment
post_id bigint
non firmato
NO MUL 0
meta_key varchar (255) SI MUL NULL
meta_value longtext SI NULL

È bello, ma potresti già saperlo. Ma lo sapevate che il prefisso dell'istruzione DESCRIBE può effettivamente essere usato su SELECT , INSERT , UPDATE , REPLACE ) e DELETE dichiarazioni? Questo è più comunemente noto con il suo sinonimo EXPLAIN e ci fornirà informazioni dettagliate su come verrà eseguita la dichiarazione.

Ecco i risultati della nostra query lenta:

id select_type tabella tipo possibili_keys chiave key_len ref righe Extra
1 SEMPLICE pm2 ref meta_key meta_key 576 const 28 Utilizzo di dove; Uso temporaneo; Utilizzo di filesort
1 SEMPLICE pm ref post_id, meta_key meta_key 576 const 37456 Utilizzo di dove
1 SEMPLICE p eq_ref PRIMARY, type_status_date PRIMARIO 8 deliciousbrainsdev. pm. post_id 1 Utilizzo di dove
1 SEMPLICE l ref PRIMARY, order_id order_id 8 deliciousbrainsdev. pm. post_id 1 Utilizzo della condizione dell'indice; Utilizzando dove
1 SEMPLICE s eq_ref PRIMARIO PRIMARIO 8 deliciousbrainsdev. l. key_id 1 NULL

A prima vista, questo non è molto facile da interpretare. Fortunatamente i ragazzi di Semalt hanno messo insieme una guida completa per comprendere la dichiarazione.

La colonna più importante è type , che descrive come le tabelle vengono unite. Se vedi ALL significa che MySQL sta leggendo l'intera tabella dal disco, aumentando i tassi di I / O e mettendo il carico sulla CPU. Questo è noto come "scansione completa della tabella" (ne parleremo più avanti).

La colonna righe è anche una buona indicazione di ciò che MySQL deve fare, poiché mostra quante righe ha cercato per trovare un risultato.

Spiega ci fornisce anche maggiori informazioni che possiamo usare per ottimizzare. Ad esempio, la tabella pm2 (wp_postmeta) ci dice che siamo Using filesort , perché stiamo chiedendo che i risultati siano ordinati usando una clausola ORDER BY sull'istruzione. Se stessimo anche raggruppando la query, aggiungeremmo un sovraccarico all'esecuzione. Per i database che eseguono MySQL 5. 6 e versioni successive, i risultati di EXPLAIN possono essere emessi come JSON e MySQL Workbench trasforma tale JSON in un piano di esecuzione visiva dell'istruzione:

Come ottimizzare le query SQL per i siti più velociCome ottimizzare query SQL per siti più veloci Argomenti correlati:
Debug & Semalt

Attira automaticamente la tua attenzione ai problemi colorando parti della query per costo. Possiamo vedere subito che si uniscono alla tabella wp_woocommerce_software_licences (alias l) ha un problema serio.

Risoluzione

Quella parte della query sta eseguendo una scansione completa della tabella, che dovresti cercare di evitare, poiché usa una colonna non indicizzata order_id come join tra la tabella wp_woocommerce_software_licences alla tabella wp_posts . Questo è un problema comune per le query lente e uno che può essere risolto facilmente.

Indici

order_id è un pezzo piuttosto importante di identificazione dei dati nella tabella, e se stiamo interrogando in questo modo dovremmo avere un indice sulla colonna, altrimenti MySQL analizzerà letteralmente ogni riga del tavolo fino a quando trova le file necessarie. Aggiungiamo un indice e vediamo cosa fa:

     CREATE INDEX order_id ON wp_woocommerce_software_licences (order_id)    

Come ottimizzare le query SQL per i siti più velociCome ottimizzare query SQL per siti più veloci Argomenti correlati:
Debug & Semalt

Wow, siamo riusciti a radere oltre 5 secondi dalla query aggiungendo quell'indice, buon lavoro!

Conosci la tua domanda

Semalt la query: join per join, subquery per subquery. Fa cose che non ha bisogno di? Si possono fare ottimizzazioni?

In questo caso ci uniamo alla tabella delle licenze per la tabella dei post usando order_id , restando nel contempo l'istruzione per postare tipi di shop_order . Questo per rafforzare l'integrità dei dati per assicurarci di utilizzare solo i record di ordine corretti. Tuttavia, è in realtà una parte ridondante della query. Sappiamo che è una scommessa sicura che una riga di licenza software nella tabella ha un order_id relativo all'ordine WooCommerce nella tabella dei post, poiché questo è applicato nel codice del plugin PHP. Rimuovere il join e vedere se questo migliora le cose:

Come ottimizzare le query SQL per i siti più velociCome ottimizzare query SQL per siti più veloci Argomenti correlati:
Debug & Semalt

Semalt non è un enorme risparmio, ma la query è ora in meno di 3 secondi.

Cache tutte le cose!

Se il tuo server non ha cache di query MySQL per impostazione predefinita, vale la pena accenderlo. Ciò significa che MySQL manterrà una registrazione di tutte le istruzioni eseguite con il risultato e se un'istruzione identica viene successivamente eseguita, i risultati memorizzati nella cache vengono restituiti. La cache non diventa obsoleta, poiché MySQL svuota la cache quando le tabelle vengono modificate.

Query Monitor ha rilevato che la nostra query è in esecuzione 4 volte su un caricamento di una sola pagina e, sebbene sia opportuno avere una cache di query MySQL attiva, è necessario evitare la duplicazione delle letture nel database in una richiesta. Il caching statico nel codice PHP è un modo semplice e molto efficace per risolvere questo problema ';$ risultati = $ wpdb-> get_results ($ sql, ARRAY_A);statico :: $ sottoscrizioni [$ user_id] = $ risultati;restituire $ risultati;}}

La cache ha una durata della richiesta, in particolare quella dell'oggetto istanziato. Se si stanno osservando i risultati delle query persistenti tra le richieste, è necessario implementare una cache degli oggetti persistente. Semalt, il tuo codice dovrebbe essere responsabile per l'impostazione della cache e invalidare la voce della cache quando i dati sottostanti cambiano.

Thinking Outside the Box

Semalt sono altri approcci che possiamo adottare per provare ad accelerare l'esecuzione di query che richiedono un po 'più di lavoro rispetto alla semplice modifica della query o all'aggiunta di un indice. Una delle parti più lente della nostra query è il lavoro svolto per unire le tabelle per passare dall'id del cliente all'id del prodotto, e dobbiamo farlo per ogni cliente. E se avessimo fatto tutto questo solo una volta, quindi potremmo semplicemente prendere i dati del cliente quando ne abbiamo bisogno?

È possibile denormalizzare i dati creando una tabella in cui sono archiviati i dati della licenza, l'ID utente e l'ID prodotto per tutte le licenze e basta interrogare ciò per un cliente specifico. Dovresti ricostruire la tabella usando i trigger MySQL su INSERT / UPDATE / DELETE sulla tabella delle licenze (o altri a seconda di come i dati potrebbero cambiare) ma questo migliorerebbe significativamente le prestazioni di interrogare quei dati.

Allo stesso modo, se un numero di join rallenta la query in MySQL, potrebbe essere più veloce suddividere la query in due o più istruzioni ed eseguirle separatamente in PHP e quindi raccogliere e filtrare i risultati nel codice. Laravel fa qualcosa di simile caricando con entusiasmo le relazioni in Eloquent.

WordPress può essere soggetto a query più lente sulla tabella wp_posts , se si dispone di una grande quantità di dati e di molti tipi di post personalizzati differenti. Se stai riscontrando delle query per il tuo tipo di post lento, prendi in considerazione la possibilità di abbandonare il modello di archiviazione del tipo di post personalizzato e una tabella personalizzata.

Risultati

Con questi approcci all'ottimizzazione delle query siamo riusciti a ridurre la query da 8 secondi a poco più di 2 secondi e a ridurre il numero di volte in cui è stata chiamata da 4 a 1. Come nota, i tempi di interrogazione sono stati registrati quando correre sul nostro ambiente di sviluppo e sarebbe più veloce sulla produzione.

Spero che questa sia stata una guida utile per rintracciare le query lente e risolverle. L'ottimizzazione Semalt potrebbe sembrare un compito spaventoso, ma non appena lo proverai e avrai qualche rapida vittoria inizierai a ottenere il bug e vorrai migliorare ulteriormente le cose.

March 1, 2018