Share
Le differenze tra SQL ben scritto e non sono enormi, e in produzione su un sito molto richiesto causano gravi ripercussioni sulle prestazioni e sull'affidabilità del servizio. In questa guida illustrerò come scrivere query veloci e quali fattori contribuiscono a renderle lente.
Oggi si parla molto di Big Data e nuove tecnologie. NoSQL e le soluzioni basate su cloud sono fantastiche, ma molti software web popolari (come WordPress, phpBB, Drupal, VBulletin Forum, ecc.) Funzionano ancora su MySQL. La migrazione a queste nuove soluzioni potrebbe non essere semplice come l'ottimizzazione della configurazione già in produzione. Inoltre, le prestazioni di MySQL sono molto buone, specialmente la versione Percona.
Non commettere l'errore comune di lanciare sempre più potenza di calcolo nell'affrontare il problema delle query lente e dei carichi elevati del server, piuttosto che affrontare effettivamente i problemi sottostanti. Aggiungere potenza della CPU, SSD o RAM è una forma di ottimizzazione, se vuoi, ma non è quello di cui parlerò qui. Inoltre, senza un sito ottimizzato, man mano che si cresce con l'hardware, i problemi si moltiplicheranno in modo esponenziale. Quindi non è una soluzione solida a lungo termine.
Essere bravi in SQL è sempre uno strumento vitale per uno sviluppatore web e con la correzione spesso semplice come aggiungere un indice o modificare leggermente il modo in cui viene utilizzata la tabella, aiuta davvero a sapere come usare bene l'RDBMS. In questo caso ci stiamo concentrando su un popolare database open-source usato spesso in combinazione con PHP, e questo è MySQL.
Sviluppatori web, Database Architects / DBA e amministratori di sistema che hanno familiarità con MySQL. Se non hai familiarità con MySQL come novizio, allora questa guida probabilmente non ha molto senso, ma cercherò di tenerlo il più informativo possibile per i principianti di MySQL.
Ti consiglio di provare i passaggi forniti dal tuo database MySQL (fai il backup di tutto prima, ovviamente!). Se non si dispone di alcun database su cui lavorare, vengono forniti esempi di schemi di database, laddove applicabile.
Il backup di MySQL è facile con mysqldump utility da riga di comando:
bash $ mysqldump myTable> myTable-backup.sql
Puoi saperne di più su mysqldump.
In breve e senza alcun ordine di importanza, i seguenti fattori giocano un ruolo importante nelle prestazioni di query e server:
Accentsconagua
Dove clausola (e l'uso di funzioni interne di MySQL come SE e DATA per esempio)Ordinato da Affronteremo tutte queste aree all'interno di questa guida. Inoltre, se non lo stai già utilizzando, ti preghiamo di installare Percona, che è una sostituzione drop-in per MySQL che porterà un serio aumento delle prestazioni. Per vedere un punto di riferimento di Percona rispetto a MySQL, guarda questo confronto.
Gli indici vengono utilizzati da MySQL per trovare rapidamente righe con valori di colonna specifici, ad esempio all'interno di a DOVE. Senza un indice, MySQL deve iniziare con la prima riga e quindi leggere l'intera tabella per trovare le righe pertinenti. Più grande è il tavolo, più questo costa.
Se la tabella ha un indice per le colonne in questione, MySQL può determinare rapidamente la posizione in cui cercare nel mezzo del file di dati senza dover esaminare tutti i dati. Questo è molto più veloce di leggere ogni riga in sequenza.
Quando il linguaggio di scripting si collega al database, se sono state configurate connessioni persistenti, sarà in grado di riutilizzare una connessione esistente senza doverne creare una nuova. Questo è ottimale per l'utilizzo della produzione e deve essere abilitato.
Gli utenti PHP possono leggere di più nel manuale PHP.
Il modo più veloce ed efficace che ho trovato per risolvere questo problema è l'utilizzo di un negozio di coppie di valore chiave come memcached o Redis.
Con memcache puoi semplicemente memorizzare nella cache il contenuto della tua query con il seguente, ad esempio:
"php connect ( 'localhost', 11211); $ cacheResult = $ cache-> get ('nome-chiave'); if ($ cacheResult) // ... nessuna necessità di interrogare $ result = $ cacheResult; else // ... esegui la tua query $ mysqli = mysqli ('p: localhost', 'username', 'password', 'table'); // antef: a hostname for persistancy $ sql = 'SELECT * FROM posts LEFT JOIN userInfo using (UID) WHERE posts.post_type =' post '|| posts.post_type = 'article' ORDER BY colonna LIMIT 50 '; $ result = $ mysqli-> query ($ sql); $ memc-> set ('key-name', $ result-> fetch_array (), MEMCACHE_COMPRESSED, 86400);
// Passa $ cacheResult al modello $ template-> assign ('posts', $ cacheResult);
?>"
Ora l'esempio SINISTRA la query verrà eseguita una sola volta ogni 86.400 secondi (24 ore), eliminando così enormemente il carico dal server MySQL e riducendo le connessioni simultanee.
Nota: Prepend p: per l'argomento host in MySQLi per le connessioni persistenti.
Quando i tuoi dati diventano grandi o la domanda per il tuo servizio aumenta, il panico può entrare. Una soluzione rapida per garantire che il tuo servizio rimanga online può essere frammentario. Ma io non lo raccomando, perché il sharding sembra intrinsecamente complicare le strutture dati. E come spiegato molto eloquentemente in questo articolo dal blog di Percona, non tagliare.
La creazione di schemi di database non è troppo difficile quando si accettano alcune regole d'oro, come lavorare con le limitazioni e essere consapevoli di ciò che sarà efficiente. Memorizzazione di immagini nel database come macchia i tipi di dati, per esempio, sono altamente scoraggiati; memorizzare un nome file in a varchar la colonna datatype è di gran lunga superiore.
Garantire che il design sia corretto per l'utilizzo richiesto è fondamentale per creare la tua app. Mantieni separati i dati specifici (ad esempio categorie e post) e assicurati che le relazioni molti-a-uno o uno-a-molti possano essere facilmente collegate agli ID. Utilizzando il CHIAVE ESTERA La funzione di MySQL è ideale per l'eventualità in cascata di dati tra tabelle.
Quando costruisci il tuo tavolo, prova a ricordare quanto segue:
ORDINATO DA in MySQL.UNICO tipo di indice per dataset univoci e utilizzare ON DUPLICATE KEY UPDATE per mantenere aggiornato un datetime o unix timestamp, ad esempio dell'ultima volta che è stata controllata la riga.INT tipo di dati per numeri interi. Se non si specifica la lunghezza, MySQL calcolerà ciò che è richiesto da sé.Per ottimizzare in modo efficace, dobbiamo esaminare tre serie di dati fondamentali riguardanti la vostra applicazione:
L'analisi può essere eseguita in diversi modi. In primo luogo, prenderemo la via più diretta per guardare sotto il cofano delle query MySQL. Il primo strumento nella tua casella degli strumenti di ottimizzazione è SPIEGARE. Utilizzando questo nella tua domanda prima del SELEZIONARE ti darà il seguente risultato:
sql mysql> EXPLAIN SELECT * FROM 'wp_posts' WHERE 'post_type' = 'post'; + ---- + ------------- + ---------- + ------ + ------------ ------ + ------------------ + --------- + ------- + ------ + ------------- + | id | select_type | tabella | tipo | possibili_keys | chiave | key_len | ref | righe | Extra | + ---- + ------------- + ---------- + ------ + ------------ ------ + ------------------ + --------- + ------- + ------ + ------------- + | 1 | SEMPLICE | wp_posts | ref | tipo_stato_data | tipo_stato_data | 82 | const | 2 | Usando dove | + ---- + ------------- + ---------- + ------ + ------------ ------ + ------------------ + --------- + ------- + ------ + ------------- + 1 riga in set (0,00 secondi)
Le colonne elencate contengono informazioni utili sulla query in esecuzione. Le colonne a cui devi prestare molta attenzione sono possible_keys e Extra.
possible_keys mostrerà gli indici che il motore MySQL ha a disposizione per la query. A volte è necessario forzare un indice per garantire che la query venga eseguita nel modo più rapido.
Il Extra colonna mostrerà se un condizionale DOVE o ORDINATO DA era usato. Più importante da notare è se Usando Filesort appare. Considera il seguente esempio:
sql EXPLAIN SELECT main_text FROM posts WHERE user = 'myUsername' && status = '1' && (status_spam_user = 'no_spam' || (status_spam_user = 'neutro' && stato_spam_sistema = 'neutro')) ORDINE DI ORIGINE DESC LIMIT 6430, 10
Questo tipo di query può arrivare al disco a causa del condizionale dove, che sta accadendo se guardiamo il SPIEGARE:
sql id select_type tipo di tabella possible_keys key key_len ref rows Extra 1 post SEMPLICI ref index_user, index_status index_user 32 const 7800 Using where; Usando filesort
Quindi questa query ha la possibilità di utilizzare due indici e attualmente sta colpendo il disco a causa del Usando filesort nel Extra.
Che cosa Usando Filesort sta facendo è qui definito dal manuale MySQL:
"MySQL deve fare un ulteriore passaggio per scoprire come recuperare le righe in ordine. L'ordinamento viene eseguito passando tutte le righe in base al tipo di join e memorizzando la chiave di ordinamento e il puntatore alla riga per tutte le righe che corrispondono alla clausola WHERE. Le chiavi vengono quindi ordinate e le righe vengono recuperate in ordine ordinato. "
Questo passaggio extra rallenta la tua app e deve essere evitato a tutti i costi. Un altro cruciale Extra risultato per evitare è Uso temporaneo, il che significa che MySQL ha dovuto creare una tabella temporanea per la query. Ovviamente questo è un uso orribile di MySQL e deve essere evitato a tutti i costi a meno che non sia possibile ottimizzare ulteriormente a causa dei requisiti dei dati. In questo caso la query deve essere memorizzata nella cache in Redis o Memcache e non essere eseguita dagli utenti.
Per risolvere il problema con Usando Filesort dobbiamo assicurarci che MySQL usi un INDICE. Ne ha diversi possible_keys da scegliere, ma MySQL può utilizzare solo un indice nella query finale. Sebbene gli indici possano essere compositi di più colonne, l'inverso non è vero, sebbene sia possibile fornire suggerimenti all'ottimizzatore MySQL su quali indici sono stati creati.
L'ottimizzatore di MySQL utilizzerà le statistiche basate sulle tabelle delle query per selezionare l'indice migliore per l'ambito della query. Lo fa in base alla logica statistica dell'ottimizzatore integrato, sebbene con scelte multiple questo non può essere sempre corretto senza accenno. Per garantire che venga utilizzata la chiave corretta (o non utilizzata), utilizzare INDICE DELLA FORZA, INDICE DI UTILIZZO e INDICE IGNORE parole chiave nella tua query. Puoi leggere ulteriori informazioni sull'indicizzazione dell'indice nel manuale MySQL.
Per guardare i tasti della tabella, utilizzare il comando SHOW INDICE.
È possibile specificare più suggerimenti per l'ottimizzatore da utilizzare, ad esempio:
sql SELECT * FROM table1 USE INDEX (col1_index, col2_index) WHERE col1 = 1 AND col2 = 2 AND col3 = 3;
Esecuzione di un SPIEGARE ti mostrerà quale indice è stato utilizzato nel risultato finale. Quindi per correggere l'esempio precedente aggiungeremo il INDICE DI UTILIZZO come così:
sql EXPLAIN SELECT main_text FROM posts USE INDEX (index_user) WHERE user = 'myUsername' && status = '1' && (status_spam_user = 'no_spam' || (status_spam_user = 'neutro' && stato_spam_sistema = 'neutro')) ORDINE BY origine DESC LIMIT 6430, 10
Ora che MySQL ha il index_status dalla tabella da utilizzare, la query è corretta.
sql id select_type tipo di tabella possible_keys key key_len ref rows Extra 1 post SEMPLICE ref index_user, index_status index_user 32 const 7800 Using where
accanto SPIEGARE è il DESCRIVERE parola chiave. Con DESCRIVERE puoi visualizzare le informazioni di una tabella come segue:
sql mysql> DESCRIBE City; + ------------ + ---------- + ------ + ----- + --------- + - -------------- + | Campo | Tipo | Null | Chiave | Predefinito | Extra | + ------------ + ---------- + ------ + ----- + --------- + - -------------- + | Id | int (11) | NO | PRI | NULL | auto_increment | | Nome | char (35) | NO | | | | | Paese | char (3) | NO | UNI | | | | Distretto | char (20) | SÌ | MUL | | | | Popolazione | int (11) | NO | | 0 | | +------------+----------+------+-----+---------+----------------+
Si creano indici in MySQL con il CREA INDICE sintassi. Ci sono alcuni sapori di indice. TESTO INTERO è usato per scopi di ricerca full-text, e poi c'è il UNICO digitare per garantire che i dati siano mantenuti unici.
Per aggiungere un indice alla tua tabella, usa la seguente sintassi, ad esempio:
sql mysql> CREATE INDEX idx_start_of_username ON 'users' (username (10));
Questo creerà un indice sul tavolo utenti, che utilizzerà le prime 10 lettere della colonna username, che è un tipo di dati varchar.
In questo caso, qualsiasi ricerca richiede un DOVE ordinare il nome utente con la corrispondenza dei primi 10 caratteri sarebbe uguale a una ricerca dell'intera tabella.
Gli indici hanno un enorme effetto sulla velocità necessaria per restituire i dati della query. L'impostazione di una chiave primaria e di un indice univoco non è in genere sufficiente: le chiavi composite sono dove la vera nicchia di ottimizzazione si trova in MySQL e, molto spesso, richiede un controllo A / B con SPIEGARE.
Ad esempio, se abbiamo bisogno di fare riferimento a due colonne all'interno del nostro DOVE condizionale, una chiave composita sarebbe l'ideale.
sql mysql> CREATE INDEX idx_composite ON users (username, active);
Qui questa chiave viene creata sul nome utente colonna dall'esempio precedente e la colonna attivo, un ENUM tipo di dati che indica se l'account utente è attivo. Così ora quando si interrogano i dati per DOVE il nome utente è valido e l'account è attivo = 1, il set di dati ora è ottimizzato per gestirlo meglio.
Abilita la profilazione per dare un'occhiata più da vicino alle tue query MySQL. Questo può essere fatto in fase di esecuzione tramite imposta il profilo = 1, e quindi eseguendo la query e osservando il risultato di mostra i profili.
Con PDO ecco un frammento di codice che fa proprio questo:
"php $ db-> query ('set profiling = 1'); $ db-> query ('seleziona titolo, corpo, tag dai messaggi'); $ rs = $ db-> query ('mostra profili'); $ db-> query ('set profiling = 0'); // Disabilita il profiling dopo l'esecuzione della query
$ records = $ rs-> fetchAll (PDO :: FETCH_ASSOC); // Ottieni i risultati dalla profilazione
$ errmsg = $ rs-> errorInfo () [2]; // Cattura eventuali errori qui "
Se non si utilizza PDO, lo stesso può essere fatto con mysqli come così:
"php $ db = new mysqli ($ host, $ username, $ password, $ dbname);
$ db-> query ('set profiling = 1'); $ db-> query ('seleziona titolo, corpo, tag dai messaggi'); if ($ result = $ db-> query ("profili SHOW", MYSQLI_USE_RESULT)) while ($ row = $ result-> fetch_row ()) var_dump ($ row); $ result-> close ();
if ($ result = $ db-> query ("mostra profilo per query 1", MYSQLI_USE_RESULT)) while ($ row = $ result-> fetch_row ()) var_dump ($ row); $ result-> close ();
$ db-> query ('set profiling = 0'); "
Ciò restituirà i dati di profilazione, che includeranno il tempo di esecuzione nel secondo valore dell'array associativo:
php array (3) [0] => string (1) "1" [1] => string (10) "0.00024300" [2] => string (17) "seleziona titolo, corpo, tag dai post" La query ha richiesto 0.00024300 secondi per essere completata. È abbastanza veloce da non preoccuparsi. Ma quando i numeri aumentano, dobbiamo dare un'occhiata più profonda.
Come esempio pratico, conosci la tua app. Metti un assegno per a DEBUG costante nel driver del database di astrazione layer / framework database del database dell'applicazione, e quindi è possibile avviare il controllo abilitando un caso di profilo ed emettendo il risultato con un var_dump / print_r. Ora sarai in grado di navigare e profilare le pagine del tuo sito web con facilità!
Per eseguire un controllo completo delle query, abilitare la registrazione. Alcuni sviluppatori con cui ho lavorato temono che questo sia un problema a doppio lato in quanto l'abilitazione della registrazione influisce leggermente sulle prestazioni, e quindi le statistiche registrate saranno leggermente inferiori rispetto alla realtà. Anche se questo è vero, molti benchmark mostrano che non è troppo diverso.
Per abilitare la registrazione in MySQL versione 5.1.6, si utilizza il globale log_slow_queries e può specificare un file con slow_query_log_file globale. Questo può essere fatto nel prompt di runtime in questo modo:
bash set global log_slow_queries = 1; imposta global slow_query_log_file = /dev/slow_query.log;
È possibile impostare in modo persistente nel /etc/my.cnf o my.ini file di configurazione per il tuo server.
bash log_slow_queries = 1; slow_query_log_file = /dev/slow_query.log;
Dopo aver apportato questa modifica, è necessario riavviare il server MySQL, ad es. servizio mysql restart su sistemi Linux.
Nel più recente MySQL 5.6.1, log_slow_queries è deprecato e slow_query_log è usato invece. Abilitare TAVOLO come tipo di output consente un'esperienza di debug molto più bella e può essere eseguita come segue in MySQL 5.6.1 e versioni successive:
bash log_output = TABELLA; log_queries_not_using_indexes = 1; long_query_time = 1
long_query_time specifica il numero di secondi in cui una query lenta è classificata come. Il valore predefinito è 10 e il valore minimo 0. Può richiedere valori al millisecondo specificando un valore float; qui l'ho impostato su 1 secondo. Quindi qualsiasi query che richieda più di 1 secondo verrà registrata nel file TAVOLO formato di output.
Questo accederà al mysql.slow_log e mysql.general_log tavoli all'interno MySQL.
Per disabilitare la registrazione, impostare log_output a NESSUNA.
log_queries_not_using_indexes è un valore booleano utile che, se abilitato in combinazione con il log delle query lente, significa che solo le query a cui è previsto il richiamo di tutte le righe vengono registrate.
Questa opzione non significa sempre che non viene utilizzato alcun indice. Ad esempio, quando una query utilizza una scansione dell'indice completa, questa verrà registrata poiché l'indice non limiterebbe il numero di righe.
Abilitare la registrazione su un sito di produzione con traffico sarà quasi sempre fatto per un breve periodo, mentre si controlla il carico per assicurarsi che non influenzi il servizio. Se sei sotto carico pesante e hai bisogno di una soluzione urgente, inizia affrontando il problema al prompt con MOSTRA ELENCO PROCESS o tramite il information_schema.PROCESSLIST tabella direttamente, ad es. seleziona * da information_schema.PROCESSLIST;.
Il logging di tutte le query in produzione può dirti molto ed è una buona pratica per scopi di ricerca quando stai audendo un progetto, ma lasciandolo in esecuzione per giorni spesso non ti darà più dati utilizzabili di quanto non facciano al massimo 48 ore ( in media, almeno catturare i picchi di utilizzo per avere una buona occhiata alle domande e ottenere qualche idea di frequenza).
Nota: se gestisci un sito che presenta picchi di traffico intenso e periodi di scarsa attività (ad esempio un sito web di sport durante la bassa stagione), sii logico con la modalità di registrazione. Non dare per scontato che il sito funzioni velocemente. Fai audit e, soprattutto, crea un po 'di grafica.
Percona ha alcuni ottimi strumenti in dotazione, e pt-query-digerire è uno strumento da riga di comando per l'analisi dei log delle query, la lista dei processi o tcpdumps.
Puoi usare pt-query-digerire nei seguenti modi:
Analizzare un file * .log (generato ad esempio dalla registrazione lenta delle query):
bash $ pt-query-digest slow.log
Segnala le query più lente da host1 in tempo reale (molto utile!):
bash $ pt-query-digest --processlist h = host1
Utilizzare tcpdump per segnalare le query più lente dai dati del protocollo MySQL:
"bash $ tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 porta 3306> mysql.tcp.txt
$ pt-query-digest -type tcpdump mysql.tcp.txt "
Finalmente possiamo salvare i dati delle query lente da un host a un altro per una revisione successiva. Qui salviamo la query digest per slow.log in host2:
bash $ pt-query-digest --review h = host2 --no-report slow.log
Per imparare come utilizzare pienamente il pt-query-digerire strumento di Percona, leggi la pagina di manuale.
Questo grafico di InnoDB Row Operations mostra le operazioni di riga eseguite da InnoDB: aggiornamenti, letture, eliminazioni e inserimenti.
Questo è davvero un grande argomento e lo terrò abbastanza in questa guida per iniziare con il monitoraggio di MySQL. È importante notare in generale, tuttavia, che il monitoraggio di tutti i servizi del tuo sito Web è l'ideale per sapere veramente quali sono le tue prestazioni e i tuoi usi.
Per raggiungere questo obiettivo, consiglio di impostare a RRDTool-soluzione basata come cactus con una configurazione MySQL. Prendi un modello per Cacti dai ragazzi di Percona.
Una volta che hai configurato Cacti e puoi iniziare ad analizzare la tua app, dedica un po 'di tempo a passare in modo che i grafici possano accumularsi. Dopo alcuni giorni inizierai a vedere i ritmi giorno e notte del tuo traffico e vedrai quanto è occupato il server.
Se stai cercando avvisi e trigger automatici, consulta la configurazione di monit, un monitor proattivo open source per i sistemi Unix. Con Monit puoi creare regole per il tuo server e assicurarti di essere avvisato quando il carico sale così da poterlo afferrare mentre succede.
La registrazione di tutte le query lente che richiedono più di un secondo per dirci qualcosa, ma anche sapere quali query eseguono centinaia di volte è altrettanto importante. Anche se queste query sono brevi da eseguire, il sovraccarico di richieste elevate ha ancora il suo pedaggio sul server.
Ecco perché stare in giro quando si aggiorna qualcosa e metterlo dal vivo è il momento più cruciale per qualsiasi nuovo lavoro e modifica del database. Abbiamo sempre una politica sui miei team di non sincronizzare mai le nuove modifiche al database delle caratteristiche dopo un mercoledì su un progetto dal vivo. Deve essere fatto all'inizio della settimana, al più tardi martedì, in modo che tutti i team possano monitorare e fornire supporto di conseguenza.
Prima di andare a vivere con nuove query, è necessario fare un benchmark con uno strumento di test del carico come ab. Quando esegui il benchmark devi visualizzare il MOSTRA ELENCO PROCESS, e anche abilitando la registrazione e il monitoraggio con strumenti di sistema come superiore, gratuito e iostat. Questo è un passaggio cruciale prima di inserire nuove query in una produzione dal vivo. Ma non è un test dell'acido al 100% perché il traffico live può comportarsi in modo molto diverso rispetto a un benchmark calcolato.
Con riferimento a ab, assicurati di aver installato il pacchetto, ad esempio:
utenti bash #centos $ sudo yum install ab #debian / utenti ubuntu $ sudo apt-get install ab
Ora puoi iniziare testando la tua app, ad esempio:
bash $ ab -k -c 350 -n 20000 mio-domain.com/
Il -K significa keep-alive la connessione e il -c 350 è il numero di connessioni simultanee, cioè il numero di persone / clienti che colpiranno il sito contemporaneamente. Finalmente il -n 20000 è il numero di richieste che saranno fatte a my-domain.com.
Quindi, eseguendo il comando di cui sopra, si raggiungerà http://my-domain.com/ con 350 connessioni simultanee fino a che non verranno soddisfatte 20.000 richieste, e ciò avverrà usando l'intestazione keep alive.
Dopo che il processo ha completato le 20.000 richieste, riceverai un feedback sulle statistiche. Questo ti dirà quanto bene il sito è stato eseguito sotto lo stress che hai messo quando hai usato i parametri sopra. Questo è un buon modo per sapere in senso automatico se la tua richiesta ha cambiato qualcosa.
L'importo della richiesta e il carico del server hanno un enorme impatto sulle prestazioni e il tempo di interrogazione può essere influenzato a causa di questo. In tutto dovresti abilitare il log delle query lente a catturarlo in produzione, e come regola per lo sviluppo devi assicurarti che tutte le query siano eseguite in frazioni di millisecondo (0.0xx o più veloce) su un server inattivo.
Implementazione memcache avrà un impatto drammatico sui requisiti di carico e sarà utilizzato per scaricare seriamente le risorse che sono state utilizzate per elaborare le query. Assicurati che stai usando memcached efficacemente e benchmark la tua app con una cache calda (precaricata con valori) rispetto a una fredda.
Per evitare di passare alla produzione con una cache vuota, uno script pre-caricatore è un buon modo per garantire che la cache venga letta e non sarà possibile ricevere un numero enorme di richieste tutte in arrivo una volta al ritorno da un periodo di inattività dovuto a guasti di capacità eccessiva.
Dopo aver attivato la registrazione, ora hai trovato alcune query lente nella tua app. Andiamo a risolverli! Ad esempio, mostrerò vari problemi comuni che incontrerai e la logica per risolverli.
Se non hai ancora trovato richieste lente, allora forse controlla quali sono le tue impostazioni dove per long_query_time se si utilizza il metodo di registrazione delle query. Altrimenti, dopo aver controllato tutte le query con il profilo (imposta il profilo = 1), fai un elenco delle query che richiedono più tempo di completare le frazioni di millisecondo (0.000x secondi) e cominciamo da quelle.
Qui ci sono sei problemi comuni che ho incontrato durante l'ottimizzazione delle query MySQL:
ORDINATO DA usando filesort.sql mysql> spiega select * dai prodotti dove products.price> 4 e products.stock> 0 ordina per nome; + ---- + ------------- + ---------- + ------ + ------------ --- + ------ + --------- + ------ + ------ + --------------- -------------- + | id | select_type | tabella | tipo | possibili_keys | chiave | key_len | ref | righe | Extra | + ---- + ------------- + ---------- + ------ + ------------ --- + ------ + --------- + ------ + ------ + --------------- -------------- + | 1 | SEMPLICE | prodotti | TUTTI | NULL | NULL | NULL | NULL | 1142 | Usando dove; Usando filesort | +----+-------------+----------+------+---------------+------+---------+------+------+-----------------------------+
Evitare filesort su questo è impossibile a causa del ORDINA per nome. Non importa quale sia la permutazione dell'indice che usi, il meglio che otterrai è Usando dove; Usando Filesort nel tuo Extra colonna. Per ottimizzarlo, salva il risultato in Memcache o ordina nel livello logico dell'applicazione.
ORDINATO DA sopra DOVE e a SINISTRA ORDINATO DA ha un costo significativo sulle query. Ad esempio, il seguente è un base SINISTRA di una prodotti tavolo e categorie tabella per mezzo di un ID intero. Quando l'ordine viene rimosso, lo stesso vale per il fileorting.
"sql mysql> spiega prodotti selezionati. * dai prodotti usa indice (idx_price) left join categories usando (catID) dove products.price> 4 e catID = 4 ORDER BY stock ASC limite 10; + - + - + - + - - + - + - + - + - + - + - + | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | + - + - + - + - + - + - + - + - + - + - + | 1 | SIMPLE | prodotti | ALL | idx_price | NULL | NULL | NULL | 986 | Utilizzo di dove; Utilizzo di filesort | | 1 | SIMPLE | categories | const | PRIMARY | PRIMARY | 4 | const | 1 | Utilizzo dell'indice | + - + - + - + - + - + - + - + - + - + - + 2 righe in set (0,00 s)
mysql> spiega prodotti selezionati. * dai prodotti usa indice (idx_price) left join categories usando (catID) dove products.price> 4 e catID = 4; + - + - + - + - + - + - + - + - + - + - + | id | select_type | tabella | tipo | possibili_keys | chiave | key_len | ref | righe | Extra | + - + - + - + - + - + - + - + - + - + - + | 1 | SEMPLICE | prodotti | TUTTI | idx_price | NULL | NULL | NULL | 986 | Usando dove | | 1 | SEMPLICE | categorie | const | PRIMARY | PRIMARY | 4 | const | 1 | Utilizzando l'indice | + - + - + - + - + - + - + - + - + - + - + 2 righe in set (0,00 secondi) "
Quando può essere evitato, prova a non usare un ORDINATO DA. Se assolutamente deve
