Share
Ho usato phpMyAdmin per oltre un decennio. Nei miei primi anni con lo strumento, avevo semplicemente bisogno di qualcosa che potesse mostrarmi la struttura della tabella e rapidamente darmi i dati all'interno. Con l'aumentare delle mie esigenze, anche gli strumenti inclusi in phpMyAdmin mi consentono di tornare come strumento primario MySQL, anche con l'ottimizzazione.
Ho avuto il piacere di lavorare con diversi database diversi. Ognuno ha i suoi svantaggi e ognuno ha i suoi punti di forza. Quando viene data una scelta, tendo a migrare di nuovo a MySQL, nonostante sia troppo economico per acquistare MySQL Enterprise. Invece, faccio causa con phpMyAdmin come mio strumento di profilazione principale. Funziona bene per me, ma ho dovuto fare un bel po 'di ricerche per capire che cosa sto guardando mentre disegno le mie applicazioni. Spero di passare questo in un modo che possa essere compreso dal principiante, fino al professionista esperto.
L'ottimizzazione richiede tempo. Gestori, clienti e colleghi, non mi piace sapere che un progetto è in ritardo sulla pianificazione a causa dell'ottimizzazione. Spesso ci affrettiamo a ottimizzare per raggiungere quei parametri. Alla fine però, non stiamo facendo alcun favore a nessuno. La più bella applicazione web al mondo ti farà ripetere affari se impiegheranno 10 secondi per caricare ogni pagina. Allo stesso modo, se aspettiamo di ottimizzare fino alla fine dei nostri progetti, è probabile che ci sarà molto più lavoro da fare, che se avessimo controllato mentre il progetto andava avanti.
Un paio di note prima di entrare nella carne e nelle patate. Primo, non ho intenzione di entrare in MySQL Tuning, dato che è un po 'fuori dal campo di applicazione di questo tutorial. Mentre la messa a punto è l'ottimizzazione, è un argomento tutto per sé a mio parere. Citerò brevemente un paio di opportunità per ottimizzare il modo in cui sintonizzare il server, ma le citazioni saranno brevi. Inoltre, guarderò principalmente alle tabelle MyISAM e non alle tabelle InnoDB. La regola generale è se si stanno scrivendo molti dati, utilizzare InnoDB, ma se si utilizza SELECT molto di più, quindi utilizzare MyISAM. Inoltre, non sto entrando in livello di tabella RIPARARE, OTTIMIZZARE, CONTROLLARE e ANALIZZARE dato che questo tutorial tratta dell'ottimizzazione delle query con phpMyAdmin. Ancora una volta, questo è un po 'fuori dal campo di applicazione per questo tutorial.
Infine, guarderò WordPress come un esempio reale. Sarò il primo a dirti che non sono un esperto di WordPress, ma posso guardare le query generate con il meglio di loro. Da quello che ho visto il database con WordPress è ben indicizzato, ma una volta che iniziamo ad aggiungere cose che sono al di fuori di quei file core principali, quegli indici potrebbero non essere i migliori per quello di cui abbiamo bisogno.
"L'ottimizzazione richiede tempo. I gestori, i clienti e i colleghi, peraltro, non amano sentire che un progetto è in ritardo sulla pianificazione a causa dell'ottimizzazione".
La risposta breve è sì.
La lunga risposta è phpMyAdmin ci dà la possibilità di vedere se abbiamo bisogno di ottimizzare le nostre query, e quanto male abbiamo bisogno di ottimizzarle. Immagino che tu abbia visto questa schermata più di una volta se hai usato phpMyAdmin:
È la schermata iniziale standard per phpMyAdmin. A meno che tu non stia cercando modi per ottimizzare, potresti andare direttamente ai tuoi tavoli nel menu a sinistra e non vedere mai il menu a schede in alto. Quel menu, in particolare le schede Stato e Variabili, sono le aree in cui inizieremo.
Iniziamo con la schermata Stato, che potrebbe essere lo strumento più importante fornito da phpMyAdmin:
Questa è la parte superiore della schermata di stato. Mentre ha alcuni dati interessanti, se non sei mai andato sotto lo scroll hai perso alcune informazioni molto importanti. Per brevità, voglio guardare due semplici contro valori su cui sono ossessionato, il primo dal mio ambiente di test:
I due valori a cui prestare molta attenzione sono Handler_read_rnd e Handler_read_rnd_next. Se quei due valori sono in rosso, allora ci sono alcune query che devono essere verificate, come quando MySQL fa un SELECT, sta leggendo l'intera tabella. In alcuni casi, questo potrebbe essere dovuto alla progettazione, poiché quando si posiziona un indice su un tavolo, ci vuole un po 'più tempo per scrivere, e ci vuole un po' più di spazio. Tuttavia, se vedi qualcosa di simile:
è probabile che non fosse di progettazione. 141 milioni di richieste per leggere una riga su una posizione fissa e 16 miliardi di richieste per leggere la riga successiva, probabilmente significa che ci manca un indice o due (migliaia). Ovviamente, questo numero cresce in base al numero di richieste, quindi più un motore di ricerca indicizza il tuo sito, o più visitatori hai, più grande diventa un piccolo indice mancante. Le scansioni complete delle tabelle sono il nemico e questo ti dà un modo rapido per individuare quanto vicino quel nemico è vicino alle porte.
Un'altra grande tabella per verificare le prestazioni delle query dà un'occhiata a selezioni e indici direttamente:
Questa tabella presta particolare attenzione ai tuoi contatti. Una combinazione pericolosa non viene utilizzata e indicizzata su una delle due tabelle, perché le scansioni complete della tabella aumentano in modo esponenziale sul numero di join utilizzati. Più le tue tabelle sono normalizzate, più devi prestare attenzione ai tuoi indici, oltre alla definizione dei campi che stai unendo.
Infine, a seconda di una variabile globale, vorrete anche controllare questa tabella delle variabili:
Se si stanno registrando le query lente, questo contatore variabile mostra il numero che è stato identificato per l'osservazione, a seconda dell'impostazione del tempo di interrogazione lungo. Quelle variabili possono essere trovate dalla scheda delle variabili. Un rapido sguardo nel mio ambiente di test mostra questa impostazione (per ora):
Queste due schede mostrano un po 'più di informazioni, alcune delle quali sono assolutamente vitali per l'ottimizzazione del server MySQL. PhpMyAdmin rende davvero facile anche per i principianti individuare un problema e avere una comprensione di base di quale potrebbe essere il problema. Se un valore è verde, siamo buoni. Se è rosso, ha bisogno di un po 'di attenzione. Ci permette anche di capire che abbiamo fatto dei progressi. Quando riavviamo il nostro server, queste variabili di sessione vengono tutte svuotate. Se abbiamo apportato delle modifiche, possiamo vedere subito se abbiamo avuto un impatto.
Ora che abbiamo identificato che dobbiamo fare un po 'di ottimizzazione, diamo un'occhiata ad alcuni degli strumenti che useremo prima di trovare i nostri problemi. Il primo degli strumenti e probabilmente il più utile è usare EXPLAIN. EXPLAIN ci fornisce fondamentalmente il nostro piano di esecuzione delle query. Questo ci dice cosa intende fare MySQL con questa query prima che venga eseguita.
Senza leggere su EXPLAIN, l'output potrebbe non significare molto per te. Usando un tavolo che ho creato per un tutorial passato, diamo un'occhiata a un piano di esecuzione non ottimizzato. La mia tabella ha solo due campi in questo caso, uno è sales_id e l'altro è sale_amount. Ecco la query con cui sto lavorando:
SELECT sales_id, sale_amount FROM tutorial.sales ORDER BY sale_amount
In superficie, questa è una query molto semplice. Essendo un tavolo di vendita, il tavolo crescerà, crescerà e crescerà. Ho generato 200 record per il tutorial precedente e, facendo una semplice SELECT con una clausola ORDER BY, in realtà ci sono voluti un po 'più di quanto mi sarei aspettato:
Quella query con solo 200 record ci è costata 15 secondi. Usiamo EXPLAIN per capire come MySQL vede questa query. Fai clic sul link "Spiega SQL" per vedere i risultati:
Come molte altre cose, questo non ha molto senso se non si capisce cosa viene detto. Per qualcuno che non ha mai eseguito EXPLAIN su una query, potrebbe essere scritto anche in geroglifici. Vediamo se possiamo tradurre qualcosa di un po 'più comprensibile.
Il select_type ci dice che MySQL vede questo SELECT come un semplice, andare su una tabella ed elaborare. Se ci fosse un sindacato o una sottoquery, questo mostrerebbe quale parte dell'istruzione SELECT chiamerebbe. Ad esempio se creo una query che ha una sottoquery:
SELECT sale_amount come importo FROM vendite WHERE sales_id IN (SELECT sales_id FROM sales_force WHERE sales_id = 4)
Otteniamo una SPIEGAZIONE di questo:
Che ci parla della query stessa In questo caso il nostro select_type è cambiato per dire che la prima query è la primaria, e quindi MySQL uscirà ed eseguirà la sottoquery, che è una vista, quindi c'è un'altra subquery da eseguire, quindi finiamo con i tre separati iD. Il manuale di riferimento MySQL fornisce tutti i valori possibili:
Torna al nostro esempio originale:
Il tipo è quello a cui prestare attenzione, poiché indica se MySQL sta eseguendo la scansione dell'intera tabella o se utilizzerà un indice per trovare rapidamente i risultati. Questa è la colonna principale da esaminare quando si ottimizzano le query. Dall'ordine buono al cattivo, i valori sono:
Dove vuoi iniziare sta ottenendo l'ottimizzazione di qualsiasi query che sia il tipo di indice o tutti. Se riesci a liberare la tua applicazione da questi due tipi, le tue prestazioni miglioreranno. Questo è un mio amico, è dove inizi.
Il resto delle colonne si occupa degli indici che MySQL utilizzerà e il numero di righe che dovrà analizzare prima di poter vedere se esiste un risultato valido. Man mano che si eliminano i tipi "index" e "all", questi sono utili per capire esattamente quale indice sta usando MySQL per eseguire questa query. Per spostare una query su ladder, inizi a modificare gli indici per migliorare le prestazioni. A scopo illustrativo, ho intenzione di limitarmi a ridimensionare le scansioni "all" o full table.
La colonna finale è la colonna "extra". La colonna extra fornisce informazioni sulla query, indipendentemente dal fatto che venga utilizzata o meno una clausola WHERE, indipendentemente dal fatto che sia impossibile DOVE, nel senso che questa query restituirà sempre un valore NULL perché la clausola WHERE rende impossibile l'esecuzione. L'unico valore di cui dobbiamo prestare molta attenzione e liberarci è "Using filesort" che abbiamo nel nostro esempio. Quando vedi quel valore, MySQL deve fare un altro passaggio attraverso i risultati per ordinare i valori. Quindi, nel caso della nostra query originale:
SELECT sales_id, sale_amount FROM tutorial.sales ORDER BY sale_amount
MySQL non è solo la scansione dell'intera tabella, ma deve eseguirne la scansione due volte per ordinare i risultati a causa della nostra dichiarazione ORDER BY. Questo, ovviamente, è doppiamente negativo. Ottimizzeremo questa query e molte altre nelle seguenti sezioni.
In MySQL 5.0.37 un altro strumento è diventato disponibile per noi da utilizzare nell'ottimizzazione, e questo è il profiler MySQL. Inoltre, phpMyAdmin ha aggiunto il supporto per questa funzione nella versione 2.11, quindi se hai entrambe queste versioni disponibili, abbiamo un altro strumento da aggiungere all'ottimizzazione.
Che cosa fa MySQL Profiler, fornisce informazioni sui colli di bottiglia delle nostre query. Ci permette di vedere cosa succede durante l'effettiva esecuzione delle nostre query, vicepresidente di EXPLAIN, che fornisce il piano di esecuzione prima. Vediamo quali informazioni possiamo ottenere da phpMyAdmin dalla mia brutta query originale:
Se clicchiamo sulla casella di controllo "Profilazione" sotto la nostra query, si apre un nuovo mondo con:
phpMyAdmin fornisce i tempi di esecuzione effettivi della query che è stata fornita. Ora possiamo vedere i colli di bottiglia di dove devono essere indirizzate le nostre query o anche la struttura a livello di tabella. Forse, vediamo la necessità dai file di log che questa tabella in realtà non è scritta tanto quanto viene letta, quindi, invece di InnoDB, possiamo ora passare a MyISAM.
C'è un piccolo svantaggio nell'usare phpMyAdmin quando si usa MySQL Profiler, e cioè che il profiler è basato sulla sessione, e phpMyAdmin distrugge la sessione su ogni pageview ... Il problema che questo ci dà è che non abbiamo un modo per mantenere un totale parziale dei dati di profilazione, ma c'è un modo per ingannare phpMyAdmin, anche se in modo approssimativo:
SET profiling = 1; SELECT sales_id, sale_amount FROM tutorial.sales ORDER BY sale_amount; SHOW profili;
Quale risulta in:
Poiché stiamo eseguendo più query, è necessario utilizzare il delimitatore. Questo mostrerà che la mia query è query_id 1. Ogni volta che eseguo questa query, è ancora query_id 1 poiché la mia sessione viene distrutta all'avvio. Non sono sicuro che questo sia dovuto alla progettazione, a un bug o all'ignoranza da parte mia che phpMyAdmin distrugge la sessione con il comando QUIT, ma possiamo aggirare questo problema solo un po '. MySQL ha una meravigliosa esperienza nell'usare il profiler di Robin Schumacher, e ho intenzione di usare un po 'di query di Robin per ottenere il numero di operazioni in phpMyAdmin:
SET profiling = 1; SELECT sales_id, sale_amount FROM tutorial.sales ORDER BY sale_amount; SELECT min (seq) come sequenza, stato, conteggio (*) come operazioni, round (somma (durata), 5) come durata FROM information_schema.profiling WHERE query_id = 1 GROUP by state ORDER di seq;
Ancora una volta, non è l'ideale con phpMyAdmin, ma alla fine otteniamo ciò che vogliamo:
Prima di mettere insieme tutto ciò che abbiamo imparato insieme, diamo anche un'occhiata a come acquisire le query usando i file di log di MySQL. Siamo in grado di catturare ogni query che MySQL esegue nella tabella mysql.general_log. Eseguendo questo comando:
SET GLOBAL general_log = 'ON'; SET GLOBAL log_output = 'TABLE';
Ora possiamo avere un record per tutte le query che vengono eseguite, indipendentemente dalla fonte. Sebbene questa operazione sia costosa, e non la eseguirò su un ambiente di produzione, ci fornisce un metodo chiaro e conciso per ottenere tutte le nostre domande e l'ordine della loro esecuzione dalle nostre applicazioni. In breve, questo potrebbe essere lo strumento di ottimizzazione delle query SQL più prezioso che hai nella tua casella degli strumenti. Impostando questi due VAR GLOBALI, abbiamo il passo finale per ottenere alcune pratiche tecniche di ottimizzazione.
Ecco un output abbreviato dalla tabella mysql.general_log che utilizza questa query:
SELEZIONA event_time, command_type, argomento FROM mysql.general_log ORDER BY event_time
produce questo:
Fondamentalmente ho la mia query, insieme a tutto ciò che phpMyAdmin ha fatto in background. Se svuoto la tabella prima di ogni nuovo comando, ho qualcosa su cui posso lavorare su ogni visualizzazione di pagina o chiamata AJAX che faccio dalle mie applicazioni. Per svuotare il registro, semplicemente TRONCATO la tabella in questo modo:
TRUNCATE mysql.general_log
Truncate è un'istruzione molto migliore da usare qui di DELETE FROM, poiché l'istruzione DELETE cancella riga per riga, dove TRUNCATE svuota l'intera tabella in una sola volta.
Una volta che hai finito con l'ottimizzazione, devi semplicemente disattivare il registro delle query con questo comando:
SET GLOBAL general_log = 'OFF';
Il registro generale diventa costoso nel tempo e certamente rallenta le prestazioni della tua applicazione. Lo tengo spento tra le mie ottimizzazioni semplicemente così posso avere un aspetto organico per la performance di ciò che sto scrivendo. Detto questo, in fase di sviluppo, tengo sempre attivo il log lento delle query, in quanto voglio vedere le mie query più lente come uno strumento di ottimizzazione rapido. Puoi farlo facilmente:
SET GLOBAL slow_query_log = 'ON'; SET GLOBAL log_queries_not_using_indexes = 'ON'; SET GLOBAL log_output = 'TABLE';
e possiamo verificarlo dalla nostra scheda Variabili dalla nostra pagina di benvenuto:
Per vedere l'output, abbiamo solo bisogno di controllare il mysql.slow_log o possiamo usare una query come questa:
SELEZIONA sql_text FROM mysql.slow_log
Che mi dà le query effettive che sono state registrate come lente:
Ora possiamo mettere tutto insieme e utilizzare phpMyAdmin come uno strumento di ottimizzazione delle query relativamente decente. Iniziamo con il primo esempio di query:
EXPLAIN SELECT sales_id, sale_amount FROM tutorial.sales ORDER BY sale_amount
Che produce un output di:
Sappiamo che dobbiamo avere almeno un INDICE su questo tavolo. Fermiamoci e pensiamo a come viene usata questa tabella. È una semplice tabella di ricerca per partecipare a una tabella sales_force per dirci che hanno effettuato una vendita dell'importo registrato. Se tutto ciò che facciamo è unirsi contro questa tabella su sales_id, quindi è quello che dobbiamo indicizzare cliccando sul link dettagli:
Possiamo quindi semplicemente definire quell'indice in questo modo:
La nostra query originale ci offre ancora una scansione completa, ma in un'applicazione pratica:
SELECT sfn.first_name, sfn.last_name, s.sale_amount FROM sales_force_normalized sfn Vendite JOIN INTERNO s ON sfn.sales_id = s.sales_id
Vediamo se questo è meglio:
Adesso stiamo andando da qualche parte. Tuttavia, se facciamo qualcosa di simile:
SELEZIONA max (sale_mount) DALLE vendite
Quindi siamo di nuovo nella stessa barca per eseguire una scansione completa del tavolo. In questo caso, possiamo semplicemente modificare l'indice e aggiungere il sale_amount:
Il che ci migliora dal male al male:
Oppure possiamo aggiungere un nuovo indice solo sulla quantità:
E abbiamo il meraviglioso risultato di:
Ciò significa che MySQL non ha nemmeno bisogno di aprire la tabella, in quanto deve solo guardare nell'indice. Ora abbiamo raggiunto il livello ottimale assoluto per questa funzione COUNT. Scopri quanto tempo ci è voluto per eseguire questa query ora:
E per buona misura, facciamo clic sulla casella di controllo Profilazione sulla query per vedere ora eventuali colli di bottiglia:
Abbiamo giocato con finte query e finto database, ma mettiamo alla prova questo tutorial. Ho una versione di WordPress installata, con solo il plug-in Lorem Ipsum per aggiungere circa 5000 post e 11.000 commenti, quindi possiamo mettere un po 'di sforzo su MySQL quando stiamo facendo la nostra selezione.
Cominciamo a registrare nuovamente le nostre query da phpMyAdmin e tronceremo anche i log lenti e generali in modo che possiamo vedere cosa succede quando carichiamo una pagina da WordPress:
SET GLOBAL general_log = 'ON'; TRUNCATE mysql.slow_log; TRUNCATE mysql.general_log;
Ci saranno alcuni artefatti nel general_log dato che phpMyAdmin causa alcune attività all'interno di MySQL, ma dovremmo essere in grado di ottenere tutto in ordine quando ricarico la mia pagina indice da WordPress a questo punto, e se usiamo una condizione LIKE, può ottenere principalmente risultati di WordPress poiché le tabelle sono precedute da wp_:
SELEZIONA event_time, command_type, argomento FROM mysql.general_log WHERE argomento LIKE "% wp_%" ORDER BY event_time
Il che ci dà un risultato ragionevole di:
Ora sappiamo che WordPress ci fornisce semplicemente 11 query sul caricamento della pagina indice con un'installazione piuttosto carina. Troviamo qualcosa da ottimizzare che potrebbero aver perso. Se prendiamo la primissima query che viene eseguita ogni volta che WordPress carica:
EXPLAIN SELECT nome_opzione, opzione_valore FROM wp_options WHERE autoload = 'sì'
Troviamo che questo non è ottimizzato:
Diamo un'occhiata a cosa hanno fatto con phpMyAdmin:
Vediamo che esiste un indice su option_name, ma non esiste un indice sul caricamento automatico, che è la condizione specifica nella pagina dell'indice. Aggiungiamolo e vediamo se non siamo in grado di ottimizzare l'installazione di base di WordPress solo un po ':
Dal momento che il caricamento automatico è varchar e "sì" o "no" da quello che vedo, posso limitare il valore dell'indice a 1. Significa che ora viene visualizzato "y" o "n" che riduce ulteriormente il nostro tempo. Vediamo la SPIEGAZIONE dopo aver ottimizzato:
Siamo passati dal pessimo al quarto tipo migliore. Non male per un paio di minuti di lavoro. Certo, WordPress non stava soffocando su questo valore, ma a seconda del carico del tuo blog, ogni piccolo aiuto. Premesso ora, le scritture richiedono più tempo, perché dobbiamo indicizzare la nostra "y" o "n" per ogni riga scritta.
Se andiamo ancora un po 'oltre, possiamo vedere il MySQL Profiler in azione semplicemente spuntando la casella "Profiling". Ora vediamo che la nostra query è davvero entusiasmante:
L'ottimizzazione non è facile, né è davvero molto divertente. Tuttavia, quando ignori questo passaggio di sviluppo, torna sempre a perseguitarti. Credo che sia relativamente facile usare gli strumenti in phpMyAdmin per ottenere un buon aspetto di ottimizzazione delle tue applicazioni. Detto questo, ci sono nuovi strumenti aggiunti in continuazione, come Jet Profiler che prende ciò che ho appena fatto in tempo reale e la natura grafica.
.
Accentsconagua