Comprendere le query MySQL con Explain

Sei nel tuo nuovo lavoro come amministratore di database o ingegnere dei dati e ti sei appena perso cercando di capire cosa dovrebbero significare e fare queste query dall’aspetto folle. Perché ci sono 5 join e perché c’è un ORDER BY usato all’interno di una subquery prima che uno dei join si verifichi? Ricorda, sei stato assunto per un motivo – molto probabilmente, questa ragione ha anche a che fare con molte query contorte che sono state create e modificate nell’ultimo decennio.

La parola chiaveEXPLAIN viene utilizzata in vari database SQL e fornisce informazioni su come il database SQL esegue una query. In MySQL, EXPLAIN può essere utilizzato a fronte di una query che iniziano con SELECTINSERTDELETEREPLACE e UPDATE. Per una semplice query, sarebbe simile al seguente:

EXPLAIN SELECT * FROM foo WHERE foo.bar = 'infrastructure as a service' OR foo.bar = 'iaas';

Invece del solito output di risultato, MySQL mostrerebbe il suo piano di esecuzione dell’istruzione spiegando quali processi avvengono in quale ordine durante l’esecuzione dell’istruzione.

Nota: se EXPLAIN non funziona per te, l’utente del database potrebbe non avere il privilegio SELECT per le tabelle o le viste che stai usando nell’istruzione.

EXPLAIN è un ottimo strumento per rimediare rapidamente query lente. Mentre può certamente aiutare, non toglierà la necessità di pensiero strutturale e una buona panoramica dei modelli di dati in atto. Spesso, la soluzione più semplice e il consiglio più rapido è aggiungere un indice alle colonne di una tabella specifica in questione se vengono utilizzate in molte query con problemi di prestazioni. Attenzione, però, non utilizzare troppi indici in quanto ciò potrebbe essere controproducente. Leggere l’indice e la tabella ha senso solo se la tabella ha una quantità significativa di righe e sono necessari solo alcuni punti dati. Se si recupera un enorme set di risultati da una tabella e si interrogano spesso colonne diverse, un indice su ogni colonna non ha senso e ostacola le prestazioni più di quanto aiuti. Per ulteriori informazioni sui calcoli effettivi di index vs no index, leggi la stima delle prestazioni nella documentazione ufficiale di MySQL.

Le cose che vuoi evitare laddove possibile e applicabile sono l’ordinamento e i calcoli all’interno delle query. Se pensi di non poter evitare i calcoli all’interno delle tue query: sì, puoi. Scrivi il set di risultati da qualche altra parte e calcola il tuo punto dati al di fuori della query, metterà meno sforzo sul database e quindi sarà complessivamente migliore per la tua applicazione. Assicurati di documentare il motivo per cui stai calcolando all’interno della tua applicazione piuttosto che avere un risultato prodotto in SQL subito. Altrimenti il prossimo amministratore o sviluppatore del database arriverà e avrà la gloriosa idea di utilizzare un calcolo all’interno della query sulla falsariga di ” oh guarda, il mio predecessore non sapeva nemmeno che puoi farlo in SQL!”Alcuni team di sviluppatori che non hanno ancora avuto l’inevitabile problema di database morenti potrebbero utilizzare calcoli in query per differenze di numero tra date o punti dati simili.

La regola generale per le query SQL è la seguente:

Sii preciso e genera solo i risultati di cui hai bisogno.

Diamo un’occhiata a una query leggermente più complicata

SELECT site_options.domain, sites_users.user, site_taxes.monthly_statement_fee, site.name, AVG(price) AS average_product_price FROM sites_orders_products, site_taxes, site, sites_users, site_options WHERE site_options.site_id = site.id AND sites_users.id = site.user_id AND site_taxes.site_id = site.id AND sites_orders_products.site_id = site.id GROUP BY site.id ORDER BY site.date_modified desc LIMIT 5;+-----------------------------+-----------------------------+-----------------------+------------------------------------------+-----------------------+| domain | user | monthly_statement_fee | name | average_product_price |+-----------------------------+-----------------------------+-----------------------+------------------------------------------+-----------------------+| www.xxxxxxxxxxxxxxxxxxx.com | [email protected] | 0.50 | xxxxxxxxxxxxxxxxxxxxx | 3.254781 || www.xxxxxxxxxxx.com | [email protected] | 0.50 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | 9.471022 || | [email protected] | 0.00 | xxxxxxxxxxxxxxxxx | 8.646297 || | [email protected] | 0.00 | xxxxxxxxxxxxxxx | 9.042460 || | [email protected] | 0.00 | xxxxxxxxxxxxxxxxxx | 6.679182 |+-----------------------------+-----------------------------+-----------------------+------------------------------------------+-----------------------+5 rows in set (0.00 sec)

and e il suo EXPLAIN output.

+------+-------------+---------------------------------+--------+-----------------+---------------+---------+---------------------------------+------+-----------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+------+-------------+---------------------------------+--------+-----------------+---------------+---------+---------------------------------+------+-----------+| 1 | SIMPLE | sites | index | PRIMARY,user_id | PRIMARY | 4 | NULL | 858 | Using temporary; Using filesort || 1 | SIMPLE | sites_options | ref | site_id | site_id | 4 | service.sites.id | 1 | || 1 | SIMPLE | sites_taxes | ref | site_id | site_id | 4 | service.sites.id | 1 | || 1 | SIMPLE | sites_users | eq_ref | PRIMARY | PRIMARY | 4 | service.sites.user_id | 1 | || 1 | SIMPLE | sites_orders_products | ref | site_id | site_id | 4 | service.sites.id | 4153 | |//+------+-------------+---------------------------------+--------+-----------------+---------------+---------+---------------------------------+------+-----------+5 rows in set (0.00 sec)

Le colonne nell’outputEXPLAIN con quelle che richiedono particolare attenzione per identificare i problemi in grassetto sono:

• id (id query)
• select_type (tipo di dichiarazione)
• table (tabella di riferimento)
• tipo (il tipo di join)
• possible_keys (i tasti che potrebbe essere stato usato)
• chiave (chiave che è stato utilizzato)
• key_len (lunghezza della chiave utilizzata)
• ref (colonne rispetto all’indice)
• righe (quantità di righe cercato)
• Extra (ulteriori informazioni)

maggiore è La quantità di righe cercato, migliore è il livello di ottimizzazione per quanto riguarda gli indici e query di precisione necessario per massimizzare le prestazioni. La colonna Extra mostra le possibili azioni su cui è possibile concentrarsi per migliorare la query, se applicabile.

Mostra Avvisi;

Se la query che hai usato con i tag EXPLAIN non analizza correttamente, è possibile digitare SHOW WARNINGS; in MySQL query editor per visualizzare le informazioni circa l’ultima istruzione che è stato eseguito e non diagnostico, cioè di visualizzare le informazioni relative dichiarazioni del tipo: SHOW FULL PROCESSLIST;. Anche se non può fornire un piano di esecuzione della query corretto come EXPLAIN, potrebbe fornire suggerimenti su quei frammenti di query che potrebbe elaborare. Diciamo che usiamo la query EXPLAIN SELECT * FROM foo WHERE foo.bar = 'infrastructure as a service' OR foo.bar = 'iaas';su un dato database che in realtà non ha una tabellafoo. L’output MySQL sarebbe:

ERROR 1146 (42S02): Table 'db.foo' doesn't exist

Se digitiamoSHOW WARNINGS; l’output è il seguente:

+-------+------+-------------------------------------+| Level | Code | Message |+-------+------+-------------------------------------+| Error | 1146 | Table 'db.foo' doesn't exist |+-------+------+-------------------------------------+1 row in set (0.00 sec)

Proviamo questo con un errore di sintassi deliberato.

EXPLAIN SELECT * FROM foo WHERE name = ///;

Questo genera i seguenti avvisi:

> SHOW WARNINGS;+-------+------+---------------------------------------------------------------------+| Level | Code | Message |+-------+------+---------------------------------------------------------------------+| Error | 1064 | You have an error in your SQL syntax; (...) near '///' at line 1 |+-------+------+---------------------------------------------------------------------+

Questo output di avvertimenti è abbastanza semplice e mostrato da MySQL come output di risultato subito, ma per query più complicate che non analizzano, è ancora possibile dare un’occhiata a ciò che accade in quei frammenti di query che possono essere analizzati. SHOW WARNINGS; include marcatori speciali che possono fornire informazioni utili, come ad esempio:

• <index_lookup>(query fragment): una ricerca di indice si verificherebbe se la query fosse stata correttamente analizzata
• <if>(condition, expr1, expr2): se la condizione è in corso in questa specifica parte della query
• <primary_index_lookup>(query fragment): un indice di ricerca sarebbe accadendo via la chiave primaria
• <temporary table>: una tabella interna sarebbe creata qui per il salvataggio dei risultati temporanei, per esempio nelle sottoquery prima del join

Per saperne di più su questi speciali pennarelli, leggi Estesa Spiegare il Formato di Output nella gazzetta ufficiale della documentazione di MySQL.

La correzione a lungo termine

Esistono diversi modi per correggere la causa principale delle cattive prestazioni del database. Il primo punto da guardare è il modello di dati, cioè come sono strutturati i dati e stai utilizzando il database giusto? Per molti prodotti, un database SQL va bene. Una cosa importante da ricordare è separare sempre i log di accesso dal normale database di produzione, cosa che purtroppo non accade in molte aziende. Per lo più in questi casi, un’azienda ha iniziato in piccolo, è cresciuta e utilizza essenzialmente lo stesso database, il che significa che accede allo stesso database sia per le funzionalità di registrazione che per altre transazioni. Ciò riduce significativamente le prestazioni complessive, soprattutto quando l’azienda diventa più grande. Quindi, è molto importante creare un modello di dati che si adatta ed è sostenibile.

Modello di dati

La scelta di un modello di dati molto probabilmente rivelerà anche la giusta forma di database. A meno che il tuo prodotto non sia molto semplice, probabilmente avrai diversi database per diversi casi d’uso – se hai bisogno di mostrare numeri quasi in tempo reale per i log di accesso, molto probabilmente vorrai un data warehouse altamente performante mentre le transazioni regolari potrebbero avvenire tramite un database SQL, e potresti avere un database grafico che accumula anche i punti dati rilevanti di entrambi i database in un motore di raccomandazione.

L’architettura software del prodotto complessivo è altrettanto importante del database stesso poiché la cattiva progettazione qui comporterà colli di bottiglia che vanno verso il database e rallentano tutto sia dal lato software che da ciò che il database può produrre. Dovrai scegliere se i contenitori sono adatti al tuo prodotto, se un monolite è il modo migliore per gestire le cose, se potresti voler avere un monolite principale con diversi microservizi che mirano ad altre funzionalità distribuite altrove e come accedi, raccogli, elabora e archivia i dati.

Hardware

Altrettanto importante quanto la struttura generale, l’hardware è un componente chiave nelle prestazioni del database. Exoscale offre varie opzioni di istanza che è possibile utilizzare a seconda della transazione e del volume di archiviazione, nonché il tempo di risposta desiderato.

È fondamentale determinare i periodi di picco dell’applicazione e quindi sapere quando omettere le query amministrative più lente, se possibile. Anche le statistiche di I/O e di rete su disco devono essere considerate quando si progettano i tempi delle transazioni e delle analisi del database.

Sommario

In conclusione, ecco i punti principali per le prestazioni a lungo termine riassunti:

• creare un sistema sostenibile di dati modello che si adatta alle esigenze della vostra azienda
• scegliere la giusta forma di database
• utilizzare un software di architettura che corrisponde al prodotto
• regolari di iterazioni di osservare la struttura delle query e uso EXPLAIN il più contorto quelli, ottimizzare l’utilizzo scelto per il vostro database(s), anche per quanto riguarda gli aggiornamenti del database e come potrebbero influenzare
• scegliere le istanze più adatti alla vostra applicazione e il database deve essere in conformità con le prestazioni e la larghezza di banda