Scrivere Query MySQL Veloci Come un Fulmine

() translation by (you can also view the original English article)

Le differenze tra SQL scritto bene e SQL scritto male sono tantissime, e in ambiente di produzione su siti ad alta richiesta possono ripercuotersi sulla performance e sull'affidabilità del servizio. In questa guida parlerò di come scrivere query veloci e si quali fattori contribuiscono a renderle lente.

Perché MySQL?

Oggigiorno ci sono parecchie discussioni sui Big Data e le nuove tecnologie. NoSQL e le soluzioni basate sul cloud sono ottime, ma molti software web popolari (come WordPress, phpBB, Drupal, Forum VBullettin, etc.) girano ancora su MySQL. Migrare verso queste nuove soluzioni potrebbe non essere così semplice a favore di un'ottimizzazione della configurazione che hai già nell'ambiente di produzione. Inoltre, la performance di MySQL è davvero buona, specialmente la versione Percona.

Non commettere il comune errore di aggiungere ancora più potenza di calcolo per tamponare il problema delle query lente e i picchi di carico elevati, piuttosto che indirizzarti verso la radice del problema. Aggiungere potenza di CPU, dischi SSD o RAM è una forma di ottimizzazione, se vuoi, ma non è quello di cui parleremo in questa sede. Inoltre, senza un sito ottimizzato, al crescere dell'hardware otterrai una moltiplicazione esponenziale dei problemi. Perciò questa non è una soluzione a lungo termine.

Saper padroneggiare l'SQL è sempre uno strumento fondamentale per un web developer, ed essendo spesso un fix così semplice da risolvere aggiungendo un indice o modificando leggermente l'uso della tabella, aiuta davvero a utilizzare il tuo RDBMS correttamente.*** In questo contesto, porremo l'attenzione su un database popolare e open-source usato insieme a PHP: MySQL.

Per Chi È Questa Guida?

Web developers, Database Architects / DBAs e Amministratori di Sistema che sono esperti di MySQL. Se sei agli inizi e non sei esperto di MySQL, questa guida potrebbe non essere molto indicata, ma cercherò di essere il più divulgativo possibile per i nuovi arrivati al MySQL.

Prima di Tutto Backup

Consiglio di testare gli step*** sul tuo database MySQL ( facendo un backup di tutto prima, naturalmente!) Se non hai nessun database su cui fare lavorare, esempi di schemi per creare database saranno resi disponibili ove possibile.

Fare un backup di MySQL è semplice con l'utility a riga di comando mysqldump.

1	$ mysqldump myTable > myTable-backup.sql

Puoi saperne di più su mysqldump.

Cosa Rende Lenta una Query?

In breve, e non in ordine di importanza, questi sono i fattori significativi che influiscono sulla performance di query e server:

indici di tabella
clausole Where ( e uso di funzioni MySQL interne come IF e DATE, ad esempio)
ordinare con Order By
frequenza di richieste contemporanee
tipo di storage engine (InnoDB, MyISAM, Memory, Blackhole)
non usare l'edizione Percona
variabili di configurazione del server (regolazione di my.cnf / my.ini)
grandi insiemi di risultati (> 1.000 righe)
connessioni non persistenti
configurazione del partizionamento orizzontale / cluster
scarso design delle tabelle

Tratteremo tutti questi aspetti durante questa guida. Inoltre, se non lo stai già utilizzando, ti invito a installare Percona, un'alternativa che sostituisce MySQL e che porta un aumento della performance. Per vedere un benchmark di Percona in confronto a Mysql, dai un'occhiata a questa comparazione.

Cosa Sono gli Indici?

Gli indici sono usati da MySQL per trovare velocemente righe con uno specifico valore di colonna, per esempio dentro ad una WHERE. Senza un indice, MySQL deve iniziare dalla prima riga e leggere l'intera tabella per trovare le righe pertinenti. Più grande è la tabella, più richiede risorse. ***

Se la tabella ha un indice per le colonne in questione, MySQL può determinare velocemente la posizione da cercare nel mezzo del file di dati senza dover guardare tutti i dati. Questo metodo è molto più veloce che leggere ogni riga sequenzialmente.

Connessioni Non-Persistenti?

Quando il tuo linguaggio di scripting si connette al database, se hai connessioni persistenti configurate, potrai riutilizzare una connessione esistente senza doverne creare una nuova. Questo è ottimale per l'uso in ambiente di produzione e dev'essere abilitato.

Gli utenti PHP possono approfondire nel Manuale PHP.

Ridurre la Frequenza di Richieste Contemporanee

La via più veloce e più efficace che ho trovato per correggere questo problema è utilizzare un sistema di memorizzazione con coppia chiave-valore come Memcached o Redis.

Con Memcached puoi semplicemente fare la cache dei contenuti della tua query con il codice seguente, per esempio:

<?php
$cache = new Memcache;
$cache->connect('localhost',11211);
$cacheResult = $cache->get('key-name');
if($cacheResult){
    //.. no need to query
    $result = $cacheResult;
} else {
  //.. run your query
     $mysqli = mysqli('p:localhost','username','password','table'); //prepend p: to hostname for persistancy
     $sql = 'SELECT * FROM posts 
     		LEFT JOIN userInfo using (UID) WHERE posts.post_type = 'post' || posts.post_type = 'article' 
     ORDER BY column LIMIT 50';
     $result = $mysqli->query($sql);
     $memc->set('key-name', $result->fetch_array(), MEMCACHE_COMPRESSED,86400);
}

//Pass the $cacheResult to template
$template->assign('posts', $cacheResult);

?>

La query LEFT JOIN nell'esempio, viene eseguita solo una volta ogni 86.400 secondi (24 ore) evitando al server MySQL un carico eccessivo e riduce le connessioni simultanee.

Nota: anteponi p: all'argomento host in MySQLi per avere connessioni persistenti.

Partizionamento orizzontale / Clustering

Quando i tuoi dati diventano tanti o la richiesta di servizi aumenta, il panico può prendere il sopravvento. Un rapido fix per assicurarti che il servizio rimanga online può essere il partizionamento orizzontale. In realtà non lo raccomando, perché il partizionamento orizzontale porta di per sé troppa complicazione nelle strutture di dati. E, come spiegato molto chiaramente in questo articolo dal blog di Percona, non partizionare orizzontalmente.

Scarso Design delle Tabelle

Creare schemi per un database non è molto complicato se condividi alcune regole d'oro, come lavorare con le limitazioni e stare attento a ciò che sarà efficiente. Memorizzare immagini nel database come tipo blob, ad esempio, è fortemente sconsigliato; memorizzare un nome di file in una colonna di tipo varchar è di gran lungo meglio.

Assicurandoti che il design sia corretto per l'utilizzo richiesto è di primaria importanza durante la creazione della tua applicazione. Mantieni dati specifici separati (es. categorie e post) e assicurati che le relazioni molti-a-uno e uno-a-molti siano strettamente collegate con gli ID. Usare la funzionalità FOREIGN KEY di MySQL è ideale per l'eventualità di dati a cascata tra tabelle.

Implementando la tabella, cerca di ricordare quanto segue:

Usa il minimo per ottenere il massimo; sii stringato e sul pezzo. ***
Non aspettarti che MySQL si occupi della logica o sia programmatico—cosa che dovrebbe davvero essere fatta prima dell'inserimento ad opera del tuo linguaggio di scripting. Per esempio, se hai bisogno di randomizzare una lista, effettua tale operazione in un array PHP, non nella clausola ORDER BY nel MySQL.
Usa un indice di tipo UNIQUE per insiemi di dati unici e usa ON DUPLICATE KEY UPDATE per tenere un datetime o uno unix timestamp aggiornato, ad esempio per l'ultima volta che la riga è stata controllata.
Usa un tipo INT per gli interi numerici. Se non specifici la lunghezza, MySQL calcolerà ciò che è richiesto in maniera autonoma.

I Fondamenti dell'Ottimizzazione

Per ottimizzare in modo efficace, dobbiamo osservare tre insiemi di dati fondamentali della tua applicazione:

Analisi (log di query lente, auditing, analisi del design di query e tabelle)
Requisiti di performance (quanti utenti, qual è la richiesta)
Limiti della tecnologia (velocità dell'hardware, chiedere troppo a MySQL)

L'indagine può essere condotta in diverse maniere. Prima di tutto prenderemo la via più diretta per guardare nel vano motore delle query di MySQL. Il primo strumento nella tua cassetta degli attrezzi per l'ottimizzazione è EXPLAIN. Usando questo nelle tue query, al posto di SELECT, ti fornirà il seguente output:

1	mysql> EXPLAIN SELECT * FROM `wp_posts` WHERE `post_type`='post';
2	+----+-------------+----------+------+------------------+------------------+---------+-------+------+-------------+
3	\| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| Extra \|
4	+----+-------------+----------+------+------------------+------------------+---------+-------+------+-------------+
5	\| 1 \| SIMPLE \| wp_posts \| ref \| type_status_date \| type_status_date \| 82 \| const \| 2 \| Using where \|
6	+----+-------------+----------+------+------------------+------------------+---------+-------+------+-------------+
7	1 row in set (0.00 sec)

Ciascuna delle colonne elencate contiene utili informazioni relative all'esecuzione della query. Le colonne su cui devi concentrarti sono possible_keys e Extra.

possible_keys mostra gli indici che il motore di Mysql ha disponibili da usare per la query. Ogni tanto hai bisogno di forzare un indice per assicurarti che la query sia eseguita velocemente.

La colonna Extra mostra se sono stati usati un WHERE condizionale o un ORDER BY. Più importante da notare è se viene visualizzato Using FIlesort. Considera il seguente esempio:

EXPLAIN SELECT main_text
FROM posts
WHERE user = 'myUsername' &&
status = '1' && (
    status_spam_user = 'no_spam'
    || (
        status_spam_user = 'neutral' &&
        status_spam_system = 'neutral'
    )
)
ORDER BY datum DESC
LIMIT 6430 , 10

Questo tipo di query può arrivare al disco per via della condizione where, cosa che sta accadendo se guardiamo EXPLAIN:

1	id select_type table type possible_keys key key_len ref rows Extra
2	1 SIMPLE posts ref index_user,index_status index_user 32 const 7800 Using where; Using filesort

Quindi questa query ha la possibilità di usare due indici e attualmente sta raggiungendo il disco a causa del Using filesort in Extra.

Ciò che Using Filesort sta facendo è spiegato qui, dal manuale di MySQL:

"MySQL deve effettuare un passaggio extra per capire come recuperare le righe in modo ordinato. L'ordinamento è effettuato scorrendo tutte le righe, secondo il tipo di join, e memorizzando la chiave di ordinamento e il puntatore alla riga per tutte le righe che rientrano nella clausola WHERE. Le chiavi sono quindi ordinate e le righe sono recuperate in modo ordinato."

Questo passaggio extra rallenta la tua applicazione e va evitato a tutti i costi. Un altro risultato cruciale di Extra da evitare è Using temporary, che significa che MySQL deve creare una tabella temporanea per la query. Ovviamente questo è un pessimo utilizzo di MySQL e va evitato a tutti i costi a meno che non possa ulteriormente ottimizzare per via dei requisiti dei dati. In questo caso la query dovrebbe essere messa in cache in Redis o Memcached e non essere seguita dagli utenti.

Per risolvere il problema con Using Filesort dobbiamo assicurarci che MySQL usi un INDEX. Ci sono diverse possible_keys tra cui scegliere, ma MySQL può scegliere un solo indice nella query finale. Sebbene gli indici possano essere composti da diverse colonne, l'inverso non è possibile, anche se puoi fornire suggerimenti all'ottimizzatore MySQL su quali indici hai creato.

Suggerimenti sugli Indici

L'ottimizzatole MySQL usa statistiche basate sulle tabelle delle query per selezionare il migliore indice per gli scopi della query. Lo fa basandosi sulla logica statistica integrata dell'utilità di ottimizzazione, anche se, con scelte multiple, questo non può sempre essere corretto senza suggerimenti. Per assicurarti che venga usata la chiave corretta (o che non venga usata), usa le parole chiave FORCE INDEX, USE INDEX e IGNORE INDEX nella tua query. Puoi leggere di più sul suggerimento degli indici nel manuale MySQL.

Per vedere le chiavi di una tabella, usa il comando SHOW INDEX.

Puoi specificare multipli suggerimenti da far usare all'ottimizzare, come per esempio:

SELECT * FROM table1 USE INDEX (col1_index,col2_index)
  WHERE col1=1 AND col2=2 AND col3=3;
 ```
 
Running an `EXPLAIN` will show you which index was used in the final outcome. So to fix the previous example we will add the `USE INDEX` as so:

```sql
EXPLAIN SELECT main_text
FROM posts USE INDEX (index_user)
WHERE user = 'myUsername' &&
status = '1' && (
    status_spam_user = 'no_spam'
    || (
        status_spam_user = 'neutral' &&
        status_spam_system = 'neutral'
    )
)
ORDER BY datum DESC
LIMIT 6430 , 10

Ora che MySQL ha l'index_status dalla tabella da usare, la query è a posto.

1	id select_type table type possible_keys key key_len ref rows Extra
2	1 SIMPLE posts ref index_user,index_status index_user 32 const 7800 Using where

Insieme a EXPLAIN c'è anche la parola chiave DESCRIBE. Con DESCRIBE puoi vedere le informazioni di una tabella, come di seguito:

1	mysql> DESCRIBE City;
2	+------------+----------+------+-----+---------+----------------+
3	\| Field \| Type \| Null \| Key \| Default \| Extra \|
4	+------------+----------+------+-----+---------+----------------+
5	\| Id \| int(11) \| NO \| PRI \| NULL \| auto_increment \|
6	\| Name \| char(35) \| NO \| \| \| \|
7	\| Country \| char(3) \| NO \| UNI \| \| \|
8	\| District \| char(20) \| YES \| MUL \| \| \|
9	\| Population \| int(11) \| NO \| \| 0 \| \|
10	+------------+----------+------+-----+---------+----------------+

Aggiungere Indici

Crei indici in MySQL con la sintassi CREATE INDEX. Ci sono diversi tipi di indici. FULLTEXT è usato per finalità di ricerca testuale, e c'è il tipo UNIQUE per assicurarsi che un dato sia mantenuto unico.

Per aggiungere un indice alla tua tabella, usa, ad esempio, la seguente sintassi:

1	mysql> CREATE INDEX idx_start_of_username ON `users` (username(10));

Questo crea un indice nella tabella users, usando i primi dieci caratteri della colonna username di tipo varchar.

In questo caso, qualsiasi ricerca che richiede un ordinamento WHERE che combaci con i primi 10 caratteri dell'username dovrebbe dare lo stesso risultato di una ricerca nell'intera tabella. ***

Indici Composti

Gli indici hanno un'enorme ricaduta sulla velocità necessaria a restituire i dati della query. Impostare soltanto una primary key e una indice unica non è di solito sufficiente—le chiavi composte si trovano dove risiede la nicchia del vero tuning di MySQL,e molto spesso ciò richiede qualche controllo A/B tramite EXPLAIN. ***

Ad esempio, se abbiamo bisogno di referenziare due colonne nella condizione WHERE, una chiave composta è l'ideale.

1	mysql> CREATE INDEX idx_composite ON users (username, active);

In questo esempio, la chiave è stata creata sulla colonna username dell'esempio precedente e la colonna active, un tipo di dato ENUM che indica se l'account utente è attivo o meno. Adesso, perciò, eseguendo una query con un WHERE per trovare un valido username e un account con active = 1, il set di dati è ora ottimizzato per gestire meglio l'operazione.

Quanto è Veloce il Tuo MySQL?

Abilita la profilazione per dare un'occhiata più da vicino alle quei MySQL. Può essere fatto in fase di esecuzione impostando set profiling=1, eseguendo la query e guardando il risultato di show profiles.

Per PDO, ecco uno snippet di codice che fa la stessa cosa:

$db->query('set profiling=1'); 
$db->query('select headline, body, tags from posts');
$rs = $db->query('show profiles');
$db->query('set profiling=0'); // Disable profiling after the query has been run

$records = $rs->fetchAll(PDO::FETCH_ASSOC); // Get the results from profiling

$errmsg = $rs->errorInfo()[2]; //Catch any errors here

Se non stai usando PDO, la stessa cosa può essere fatta con mysqli in questo modo:

$db = new mysqli($host,$username,$password,$dbname);

$db->query('set profiling=1');
$db->query('select headline, body, tags from posts');
if ($result = $db->query("SHOW profiles", MYSQLI_USE_RESULT)) {
    while ($row = $result->fetch_row()) {
        var_dump($row);
    }
    $result->close();
}

if ($result = $db->query("show profile for query 1", MYSQLI_USE_RESULT)) {
    while ($row = $result->fetch_row()) {
        var_dump($row);
    }
    $result->close();
}

$db->query('set profiling=0');

Questo restituirà i dati di profilazione, che includeranno il tempo di esecuzione nel secondo valore dell'array associativo:

array(3) { 
	[0]=>  string(1) "1" 
    [1]=>  string(10) "0.00024300" 
    [2]=>  string(17) "select headline, body, tags from posts" 
    }

La query richiede 0.00024300 secondi per essere eseguita. È abbastanza veloce da non impensierire. Ma quando i numeri aumentano, occorre approfondire la cosa.

Come esempio funzionante, impara a conoscere la tua app. Inserisci un controllo per una costante DEBUG nel livello di astrazione del database / driver per il database del framework della tua applicazione, così puoi iniziare l'auditing abilitando un caso di profilo e stampando a video il risultato con var_dump / print_r. A questo punto potrai navigare e profilare facilmente le pagine del tuo sito!

Auditing Completo della Tua App

Per eseguire un full audit delle tue quei, abilita il logging. Alcuni sviluppatori con cui cui ho lavorato, sono preoccupati che questo sia un problema a doppia faccia, perché abilitare il logging interessa leggermente la performance e questo fa sì che le statistiche che registri saranno più lente di quello che sono in realtà. Benché ciò sia vero, molti benchmark mostrano che non c'è molta differenza.

Per abilitare il login in MySQL versione 5.1.6, usa la variabile globale blog_slow_queries e puoi specificare un file di destinazione con slow_query_log_file. Questo può essere fatto nel prompt di runtime in questo modo:

1	set global log_slow_queries = 1;
2	set global slow_query_log_file = /dev/slow_query.log;

Puoi impostarlo in maniera persistente nei file di configurazione /etc/my.cnf o my.ini per il tuo server.

1	log_slow_queries = 1;
2	slow_query_log_file = /dev/slow_query.log;

Dopo aver fatto questa modifica, devi riavviare il server MySQL, usando ad esempio service mysql restart nei sistemi Linux.

Nel nuovo MySQL 5.6.1, blog_slow_queries è deprecato ed è usato slow_query_log al suo posto. Abilitando TABLE come tipo di output, permette di fruire di un'esperienza di debugging migliore e in MySQL 5.6.1o successivi può essere fatto come indicato qui di seguito:

1	log_output = TABLE;
2	log_queries_not_using_indexes = 1;
3	long_query_time = 1

long_query_time specifica il numeri di secondi con cui è classificata una query lenta. *** Il default è 10 e il minimo è 0. Si possono ottenere valori in millisecondi specificando un tipo float; qui l'ho impostato a 1 secondo. Così, qualsiasi query che richieda più di un secondo verrà loggata nel formato di output TABLE.

Questo sarà inserito nelle tabelle di blog mysql.slow_log e mysql.general_log all'interno di MySQL.

Per disabilitare il jogging, imposta blog_output a NONE.

log_queries_not_using_indexes è un utile booleano che, quando abilitato insieme al log per le query lente, fa sì che siano slogate solo le query che servono a recuperare tutte le righe.

Questa opzione non significa che non è usato alcun indice. Ad esempio, quando una query usa uno scan full index, questo viene loggiato perché l'indice non limita il numero di righe.

Logging in Produzione?

Abilitare la registrazione su un sito di produzione con traffico praticamente continuo dovrà essere fatto per un breve periodo, mentre avviene il monitoraggio del carico per garantire che non influisca sul servizio. Se sono sotto carico pesante e avete bisogno di una correzione urgente, iniziate affrontando il problema al prompt con SHOW PROCESSLIST o tramite direttamente la tabella information_schema. PROCESSLIST , ad esempio SELECT * FROM information_schema. PROCESSLIST;.

La registrazione sul file di log, di tutte le query in produzione può dirvi molto ed è una buona pratica per scopi di ricerca quando si sta controllando un progetto, ma lasciarle in esecuzione per giorni alla fine spesso non vi darà alcun dato più usabile rispetto al massimo di 48 ore (in media, per avere un buon occhio sulle query e farvi un idea della frequenza, almeno catturate gli orari di picco di utilizzo).

Nota: se monitorate un sito che alterna picchi di traffico a periodi con poca attività (ad esempio un sito di attività sportiva in alta e bassa stagione), avrà senso il modo con cui si guarda la registrazione. Non date per scontato che il sito stia lavorando velocemente. Esegiite un audit e soprattutto impostate alcuni grafici.

Registrazione dei Log e il pt-query-digest di Percona

Percona ha alcuni grandi strumenti in bundle , e pt-query-digest è uno strumento a riga comando per analizzare i log delle query, il processlist o tcpdumps.

È possibile utilizzare pt-query-digest nei seguenti modi:

Analizzare un file *. log ( ad esempio, generato a seguito di una query di registrazione lenta ):

1	$ pt-query-digest slow.log

Report sulle query più lente da host1 in tempo reale (molto utile!):

1	$ pt-query-digest --processlist h=host1

Utilizzare tcpdump per segnalare le query più lente da dati di protocollo di MySQL:

1	$ tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt
2
3	$ pt-query-digest --type tcpdump mysql.tcp.txt

Infine possiamo salvare dati della query lenta da un host a altro per la revisione successiva. Qui salviamo il digest della query per slow.log su host2:

1	$ pt-query-digest --review h=host2 --no-report slow.log

Per imparare a utilizzare lo strumento completo pt-query-digest di Percona, leggete la pagina del manuale.

Rappresentazione grafica di MySQL e le prestazioni del Server

Questo grafico delle Operazioni delle righe InnoDB mostra le le operazioni che vengono eseguite sulla riga InnoDB : aggiornamento, lettura, eliminazione e inserimento.

Questo è un grande argomento ed infatti lo capirete da questa guida tanto da iniziare con il monitoraggio MySQL. È importante notare in generale, tuttavia, che di monitoraggio dei servizi del tuo sito è ideale per sapere veramente quali sono le prestazioni e gli utilizzi.

Per raggiungere questo obiettivo vi consiglio di impostare una soluzione basata su RRDTool come il Cacti con una configurazione di MySQL. Ottenete un modello per Cacti dai ragazzi della Percona.

Una volta che avete ottenuto e configurato Cacti potete iniziare ad analizzare la tua app, fate passare del tempo in modo che i grafici possono accumularsi. Dopo pochi giorni si inizieranno a vedere i ritmi giorno e notte del tuo traffico e come veramente si è saturato il server.

Se state cercando trigger e avvisi automatici, guardate in configurando monit, un monitor proattivo open source per sistemi Unix. Con monit è possibile creare regole per il server che nel momento in cui il carico aumenta vi garantiscono che veniate avvisati in modo che possiante vederlo mentre accade.

Log delle Query lente

La registrazione sul file di log di tutte le query lente che impiegano più di un secondo a completarsi, ci dice qualcosa, ma anche conoscre quali query vengono eseguite centinaia di volte ha la stessa importanza. Anche se tali query sono eseguite in breve tempo, il sovraccarico di richieste risulta essere peggiore per il server.

Ecco perché bisogna essere vigili quando aggiornate qualcosa o pubblicate qualcosa live è il momento più cruciale per qualsiasi nuovo lavoro del database e le modifiche. Noi ad esempio abbiamo una nostra policy per i gruppi di lavoro, nei progetti live mai sincronizzare nuove funzionalità e modifiche del database dopo un mercoledì. Deve essere fatto all'inizio della settimana, alle ultime martedì, in modo che tutti i gruppi di lavoro possono monitorare e fornire supporto di conseguenza.

Prima di andare live con delle nuove query, è necessario un confronto con uno strumento di test di carico come ab. Quando lanciate il benchmark dovete guardare la SHOWPROCESSLIST, ed attivare anche la registrazione ed il monitoraggio con strumenti quali top, free e iostat. Questo è un passo fondamentale prima di mettere qualsiasi nuova query live in produzione. Ma non è un test efficace al 100% perché il traffico in tempo reale può comportarsi in modo molto diverso rispetto ad un benchmark computato.

Per il confronto con ab, assicuratevi ad esempio di avere installato il packege :

1	#centos users
2	$ sudo yum install ab
3	#debian / ubuntu users
4	$ sudo apt-get install ab

Adesso potete partire con il test , per esempio:

1	$ ab -k -c 350 -n 20000 my-domain.com/

-k significa mantieni la connessione e -c 350 è il numero delle connessioni concorrenti, es. il numero di persone/clienti che accedono al sito almeno una volta. Infine il -n 20000 è il numero di richieste che verranno apportate al mio-dominio.com.

Quindi eseguendo il comando di cui sopra, colpirete il sito http://my-domain.com/ con 350 connessioni simultanee fino a 20.000 richieste soddisfatte, e questo sarà fatto utilizzando l'intestazione di keep alive.

Al termine del processo delle 20.000 richieste, riceverete un feedback sulle statistiche. Queste vi diranno qual'è stato il comportamento del sito nella situazione di stress a cui è stato sottoposto utilizzando i parametri precedenti. Questo è il modo migliore per sapere in modo automatico se le vostre query hanno cambiato qualcosa.

Analisi comparativa Caldo vs Freddo

La quantità delle richieste ed il carico sul server hanno un enorme impatto sulle prestazioni ed il tempo delle query può essere influenzato a causa di questo. E' noto a tutti che è necessario abilitare il log per catturare le query lente in produzione, e come una regola per lo sviluppo è necessario assicurarsi che tutte le query siano in esecuzione in frazioni di millisecondo (0.0xx o superiore) su un server inattivo.

Implementazione di Memcache avrà un impatto drammatico sulle vostre esigenze di carico e verrà utilizzato per alleggerire seriamente le risorse utilizzate per l'elaborazione delle query. Assicuratevi di utilizzare effettivamente Memcached e confrontate la vostra app con una hot cache (precaricata con dei valori ) vs una fredda.

Per evitare di fare un passo falso in produzione con una cache vuota, uno script di pre-caricamento è un buon modo di garantire che la cache venga letta e non otterrete un numero enorme di richieste provenienti tutte a causa di errori di eccesso di capacità dovuti a feedback per mancanza del servizio.

Riparazione delle query lente

Così dopo aver attivato la registrazione del log, ora avete trovato alcune query lente nella vostra app. Ripariamole ! In proposito per esempio, dimostreremo vari problemi comuni che si incontrano e la logica per risolverli.

Se non avete ancora trovato alcuna query lenta, quindi forse dovreste verificare che le impostazioni della long_query_time utilizzi il metodo di registrazione delle query. In caso contrario, dopo aver controllato tutte le vostre query con profilatura (set profiling = 1), fate una lista delle query che impiegano più tempo in frazioni di millisecondo per completare (0.000 x secondi) e iniziate su quelle.

Problemi comuni

Ecco sei problemi comuni in cui mi imbatto quando ottimizzo delle query di MySQL:

1. `ORDER BY` utilizzando filesort.

1	mysql> explain select * from products where products.price > 4 and products.stock > 0 order by name;
2	+----+-------------+----------+------+---------------+------+---------+------+------+-----------------------------+
3	\| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| Extra \|
4	+----+-------------+----------+------+---------------+------+---------+------+------+-----------------------------+
5	\| 1 \| SIMPLE \| products \| ALL \| NULL \| NULL \| NULL \| NULL \| 1142 \| Using where; Using filesort \|
6	+----+-------------+----------+------+---------------+------+---------+------+------+-----------------------------+

Evitare filesort su questo, è impossibile a causa del nome ORDER BY. Non importa quale permutazione di indice si utilizza, la migliore che si otterrà utilizzando Where; Utilizzando Filesort nella vostra colonna Extra. Per ottimizzare questo, salvare il risultato in Memcache, o eseguite l'ordinamento nell'applicazione a a livello logico.

2. utilizzo `ORDER BY` nella `WHERE` e una `LEFT JOIN`

ORDER BY è un tributo significativo sulle query. Ad esempio, la seguente è una base di LEFT JOIN tra una tabella prodotti e la tabella categorie mediante un ID integer Quando l'ordinamento viene rimosso, così è il filesorting.

1	mysql> explain select products.* from products use index (idx_price) left join categories using (catID) where products.price > 4 and catID = 4 ORDER BY stock ASC limit 10;
2	+----+-------------+------------+-------+---------------+---------+---------+-------+------+-----------------------------+
3	\| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| Extra \|
4	+----+-------------+------------+-------+---------------+---------+---------+-------+------+-----------------------------+
5	\| 1 \| SIMPLE \| products \| ALL \| idx_price \| NULL \| NULL \| NULL \| 986 \| Using where; Using filesort \|
6	\| 1 \| SIMPLE \| categories \| const \| PRIMARY \| PRIMARY \| 4 \| const \| 1 \| Using index \|
7	+----+-------------+------------+-------+---------------+---------+---------+-------+------+-----------------------------+
8	2 rows in set (0.00 sec)
9
10	mysql> explain select products.* from products use index (idx_price) left join categories using (catID) where products.price > 4 and catID = 4;
11	+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------------+
12	\| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| Extra \|
13	+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------------+
14	\| 1 \| SIMPLE \| products \| ALL \| idx_price \| NULL \| NULL \| NULL \| 986 \| Using where \|
15	\| 1 \| SIMPLE \| categories \| const \| PRIMARY \| PRIMARY \| 4 \| const \| 1 \| Using index \|
16	+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------------+
17	2 rows in set (0.00 sec)

Quando può essere evitato, non tentare di utilizzare una clausola ORDER BY. Se assolutamente deve essere usato, ordinate solo su una chiave indice.

3. `Order By` in una colonna temp

Semplicemente non farlo. Se si ha bisogno aggregare i risultati, fallo nella logica dell'applicazione; non fare un filtro o l'ordinamento in una tabella temporanea all'interno di MySQL. L'utilizzo di risorse sarà molto elevato .

4. Non utilizzte un indice `full-text`

Utilizzando una query LIKE è di gran lunga il modo più lento per eseguire una corrispondenza di testo completo sui tuoi dati. Implementate una ricerca full-text e i vantaggi di questa brillante caratteristica di MySQL :

1	mysql> SELECT * FROM articles
2	-> WHERE MATCH (title,body) AGAINST ('database');
3	+----+-------------------+------------------------------------------+
4	\| id \| title \| body \|
5	+----+-------------------+------------------------------------------+
6	\| 5 \| MySQL vs. YourSQL \| In the following database comparison ... \|
7	\| 1 \| MySQL Tutorial \| DBMS stands for DataBase ... \|
8	+----+-------------------+------------------------------------------+
9	2 rows in set (0.00 sec)

5. Selezione di un numero enorme di righe inutilmente

Dimenticare la funzione LIMIT su una query può significativamente cambiare il tempo di ricerca nel DataSet di grandi dimensioni (oltre 1 milione di righe).

6. over-joining invece di fare solo una vista o tabella composita

Quando si arriva a tre o quattro livelli di LEFT JOIN, dovresti chiederti: " Lo sto facendo bene?" Se avete un argomento ragionevole sul perché questa query debba essere così, come ad esempio apparirà solo sullo schermo dell'amministratore in pochi casi o verrà utilizzata come una vista statistica ingrandita che potrà essere memorizzati nella cache, allora procedete. Ma se avrete bisogno di accedere ai vostri dati frequentemente con un gran numero di join, dovreste vedere come la creazione di una vista o la composizione delle colonne insieme, in una nuova tabella possono essere più utili.

Conclusioni

Abbiamo parlato dei fondamenti della ottimizzazione e degli strumenti di cui disponiamo per eseguirla. Dobbiamo controllare con la profilatura e utilizzare lo strumento di pt-query-digest ed EXPLAIN in primo luogo per vedere che cosa sta realmente accadendo, e poi da lì possiamo progettare meglio.

Abbiamo anche esaminato diversi casi di esempio e trabocchetti comuni in cui è possibile imbattersi quando si utilizza MySQL. Utilizzando indice accennando che possiamo garantire MySQL seleziona gli indici giusti per il lavoro e non si confondono, soprattutto se ci sono più query sulla stessa tabella. Per continuare la lettura su questo argomento, Scopri il progetto Percona e MySQL Performance blog per ulteriori informazioni.

1	mysql> DESCRIBE City;
2	+------------+----------+------+-----+---------+----------------+
3	\| Field \| Type \| Null \| Key \| Default \| Extra \|
4	+------------+----------+------+-----+---------+----------------+
5	\| Id \| int(11) \| NO \| PRI \| NULL \| auto_increment \|
6	\| Name \| char(35) \| NO \| \| \| \|
7	\| Country \| char(3) \| NO \| UNI \| \| \|
8	\| District \| char(20) \| YES \| MUL \| \| \|
9	\| Population \| int(11) \| NO \| \| 0 \| \|
10	+------------+----------+------+-----+---------+----------------+