Share
Le operazioni di database tendono spesso ad essere il principale collo di bottiglia per la maggior parte delle applicazioni Web oggi. Non sono solo gli amministratori di database (amministratori di database) che devono preoccuparsi di questi problemi di prestazioni. Noi come programmatori abbiamo bisogno di fare la nostra parte strutturando le tabelle correttamente, scrivendo query ottimizzate e codice migliore. In questo articolo, elencherò alcune tecniche di ottimizzazione di MySQL per i programmatori.
Prima di iniziare, sappi che puoi trovare un sacco di utili script e utility MySQL su Envato Market.
Script e utility MySQL su Envato Market La maggior parte dei server MySQL ha abilitato il caching delle query. È uno dei metodi più efficaci per migliorare le prestazioni, gestito in modo silenzioso dal motore del database. Quando la stessa query viene eseguita più volte, il risultato viene recuperato dalla cache, che è abbastanza veloce.
Il problema principale è che è così facile e nascosto dal programmatore, la maggior parte di noi tende a ignorarlo. Alcune cose che facciamo possono effettivamente impedire alla cache della query di eseguire la sua attività.
Accentsconagua
// query cache NON funziona $ r = mysql_query ("SELECT nome utente FROM utente WHERE signup_date> = CURDATE ()"); // query cache funziona! $ today = date ("Y-m-d"); $ r = mysql_query ("SELEZIONA nome utente FROM utente WHERE signup_date> = '$ today'"); Il motivo per cui la cache della query non funziona nella prima riga è l'uso della funzione CURDATE (). Questo vale per tutte le funzioni non deterministiche come NOW () e RAND () ecc ... Poiché il risultato del ritorno della funzione può cambiare, MySQL decide di disabilitare il caching delle query per quella query. Tutto quello che dovevamo fare è aggiungere una riga aggiuntiva di PHP prima della query per evitare che ciò accada.
L'uso della parola chiave EXPLAIN può darti un'idea di cosa sta facendo MySQL per eseguire la tua query. Questo può aiutarti a individuare i colli di bottiglia e altri problemi con la tua query o le strutture della tabella.
I risultati di una query EXPLAIN ti mostreranno quali indici vengono utilizzati, come la tabella viene scansionata e ordinata, ecc ...
Fai una query SELECT (preferibilmente complessa, con join) e aggiungi la parola chiave EXPLAIN di fronte ad essa. Puoi semplicemente usare phpmyadmin per questo. Ti mostrerà i risultati in un bel tavolo. Ad esempio, supponiamo di aver dimenticato di aggiungere un indice a una colonna, sulla quale eseguo i join su:
Dopo aver aggiunto l'indice al campo id_gruppo:
Ora invece di scansionare 7883 righe, scansionerà solo 9 e 16 righe dalle 2 tabelle. Una buona regola è quella di moltiplicare tutti i numeri sotto la colonna "righe" e le prestazioni della tua query saranno in qualche modo proporzionali al numero risultante.
A volte, quando stai interrogando i tuoi tavoli, sai già che stai cercando solo una riga. È possibile che si stia recuperando un record univoco oppure si potrebbe semplicemente verificare l'esistenza di un numero qualsiasi di record che soddisfano la clausola WHERE.
In questi casi, l'aggiunta di LIMIT 1 alla query può aumentare le prestazioni. In questo modo il motore di database interromperà la scansione dei record dopo aver trovato solo 1, invece di passare attraverso l'intera tabella o indice.
// ho qualche utente dall'Alabama? // cosa NON fare: $ r = mysql_query ("SELECT * FROM user WHERE state = 'Alabama'"); if (mysql_num_rows ($ r)> 0) // ... // molto meglio: $ r = mysql_query ("SELECT 1 FROM user WHERE state = 'Alabama' LIMIT 1"); if (mysql_num_rows ($ r)> 0) // ... Gli indici non sono solo per le chiavi primarie o le chiavi univoche. Se ci sono delle colonne nella tua tabella che cercherai, dovresti indicizzarle quasi sempre.
Come puoi vedere, questa regola si applica anche a una ricerca di stringa parziale come "last_name LIKE 'a%'". Durante la ricerca dall'inizio della stringa, MySQL è in grado di utilizzare l'indice su quella colonna.
Dovresti anche capire quali tipi di ricerche non possono usare gli indici regolari. Ad esempio, quando cerchi una parola (ad esempio "WHERE post_content LIKE '% apple%'"), non vedrai un beneficio da un indice normale. Farai meglio a usare mysql fulltext search o a costruire la tua soluzione di indicizzazione.
Se la tua applicazione contiene molte query JOIN, devi assicurarti che le colonne a cui partecipi siano indicizzate su entrambe le tabelle. Ciò influisce sul modo in cui MySQL ottimizza internamente l'operazione di join.
Inoltre, le colonne che sono unite devono essere dello stesso tipo. Ad esempio, se si aggiunge una colonna DECIMAL a una colonna INT da un'altra tabella, MySQL non sarà in grado di utilizzare almeno uno degli indici. Anche le codifiche dei caratteri devono essere dello stesso tipo per le colonne di tipo stringa.
// cercando società nel mio stato $ r = mysql_query ("SELECT company_name FROM utenti LEFT JOIN companies ON (users.state = companies.state) WHERE users.id = $ user_id"); // entrambe le colonne di stato dovrebbero essere indicizzate // ed entrambe dovrebbero essere dello stesso tipo e codifica dei caratteri // oppure MySQL potrebbe eseguire scansioni di tabelle complete Questo è uno di quei trucchi che suona bene all'inizio, e molti programmatori alle prime armi si innamorano di questa trappola. Non ti rendi conto di quale terribile collo di bottiglia puoi creare quando inizi a utilizzare questo nelle tue query.
Se hai davvero bisogno di righe casuali dai tuoi risultati, ci sono modi molto migliori per farlo. Certo, richiede un codice aggiuntivo, ma impedirai un collo di bottiglia che peggiora esponenzialmente man mano che i tuoi dati crescono. Il problema è che MySQL dovrà eseguire l'operazione RAND () (che richiede potenza di elaborazione) per ogni singola riga nella tabella prima di ordinarla e offrendoti solo una riga.
// cosa NON fare: $ r = mysql_query ("SELECT nome utente FROM utente ORDER BY RAND () LIMIT 1"); // molto meglio: $ r = mysql_query ("SELECT count (*) FROM user"); $ d = mysql_fetch_row ($ r); $ Rand = mt_rand (0, $ d [0] - 1); $ r = mysql_query ("SELECT nome utente FROM utente LIMIT $ rand, 1"); Quindi scegli un numero casuale inferiore al numero di risultati e utilizzalo come offset nella tua clausola LIMIT.
Più dati vengono letti dalle tabelle, più lentamente diventerà la query. Aumenta il tempo necessario per le operazioni del disco. Inoltre, quando il server del database è separato dal server Web, si avranno ritardi di rete più lunghi a causa del trasferimento dei dati tra i server.
È una buona abitudine specificare sempre quali colonne sono necessarie quando si eseguono i SELEZIONA.
// non preferito $ r = mysql_query ("SELECT * FROM user WHERE user_id = 1"); $ d = mysql_fetch_assoc ($ r); echo "Benvenuto $ d ['username']"; // migliore: $ r = mysql_query ("SELECT nome utente FROM utente WHERE user_id = 1"); $ d = mysql_fetch_assoc ($ r); echo "Benvenuto $ d ['username']"; // le differenze sono più significative con i set di risultati più grandi In ogni tabella ha una colonna id che è la PRIMARY KEY, AUTO_INCREMENT e uno dei sapori di INT. Anche preferibilmente UNSIGNED, poiché il valore non può essere negativo.
Anche se si dispone di una tabella utenti con un campo nome utente univoco, non impostarla come chiave primaria. I campi VARCHAR come chiavi primarie sono più lenti. E avrai una struttura migliore nel tuo codice facendo riferimento a tutti gli utenti con i loro ID interni.
Esistono anche operazioni dietro le quinte eseguite dal motore MySQL stesso, che utilizza internamente il campo chiave principale. Che diventano ancora più importanti, più complicata è la configurazione del database. (cluster, partizionamento ecc ...).
Una possibile eccezione alla regola sono le "tabelle di associazione", utilizzate per il tipo molti-a-molti di associazioni tra 2 tabelle. Ad esempio una tabella "posts_tags" che contiene 2 colonne: post_id, tag_id, che viene utilizzata per le relazioni tra due tabelle denominate "post" e "tag". Queste tabelle possono avere una chiave PRIMARY che contiene entrambi i campi id.
Le colonne di tipo ENUM sono molto veloci e compatte. Internamente sono memorizzati come TINYINT, ma possono contenere e visualizzare valori di stringa. Questo li rende un candidato perfetto per determinati campi.
Se hai un campo, che conterrà solo alcuni diversi tipi di valori, usa ENUM invece di VARCHAR. Ad esempio, potrebbe essere una colonna denominata "stato" e contenere solo valori come "attivo", "inattivo", "in sospeso", "scaduto" ecc ...
C'è anche un modo per ottenere un "suggerimento" da MySQL stesso su come ristrutturare il tuo tavolo. Quando hai un campo VARCHAR, in realtà può suggerirti di cambiare quel tipo di colonna in ENUM. Questo fatto utilizzando la chiamata PROCEDURE ANALYZE (). Il che ci porta a:
PROCEDURE ANALYZE () consentirà a MySQL di analizzare le strutture delle colonne e i dati effettivi nella tabella per fornire determinati suggerimenti. È utile solo se ci sono dati reali nelle tue tabelle perché questo gioca un ruolo importante nel processo decisionale.
Ad esempio, se hai creato un campo INT per la tua chiave primaria, tuttavia non hai troppe righe, potrebbe suggerirti di utilizzare invece MEDIUMINT. O se stai usando un campo VARCHAR, potresti ricevere un suggerimento per convertirlo in ENUM, se ci sono solo pochi valori unici.
Puoi anche eseguirlo facendo clic sul link "Proponi struttura tabella" in phpmyadmin, in una delle tue visualizzazioni tabella.
Tieni presente che questi sono solo suggerimenti. E se il tuo tavolo sta per diventare più grande, potrebbero non essere nemmeno i suggerimenti giusti da seguire. La decisione è alla fine tua.
A meno che tu non abbia una ragione molto specifica per usare un valore NULL, devi sempre impostare le colonne come NOT NULL.
Prima di tutto, chiediti se c'è qualche differenza tra avere un valore di stringa vuoto rispetto a un valore NULL (per i campi INT: 0 rispetto a NULL). Se non c'è motivo di averli entrambi, non è necessario un campo NULL. (Sapevi che Oracle considera NULL e stringa vuota come uguale?)
Le colonne NULL richiedono spazio aggiuntivo e possono aggiungere complessità alle istruzioni di confronto. Basta evitarli quando puoi. Tuttavia, capisco che alcune persone potrebbero avere ragioni molto specifiche per avere valori NULL, il che non è sempre una cosa negativa.
Dai documenti MySQL:
"Le colonne NULL richiedono uno spazio aggiuntivo nella riga per registrare se i loro valori sono NULL. Per le tabelle MyISAM, ogni colonna NULL richiede un bit in più, arrotondato al byte più vicino."
Ci sono molti vantaggi nell'usare le dichiarazioni preparate, sia per motivi di prestazioni che di sicurezza.
Le istruzioni preparate filtrano le variabili che vengono associate a esse per impostazione predefinita, il che è ottimo per proteggere la tua applicazione dagli attacchi di SQL injection. Ovviamente puoi anche filtrare manualmente le tue variabili, ma quei metodi sono più inclini a errori umani e dimenticanze da parte del programmatore. Questo è meno di un problema quando si utilizza un qualche tipo di framework o ORM.
Dal momento che ci concentriamo sulle prestazioni, dovrei menzionare anche i benefici in quell'area. Questi vantaggi sono più significativi quando la stessa query viene utilizzata più volte nell'applicazione. È possibile assegnare diversi valori alla stessa istruzione preparata, tuttavia MySQL dovrà solo analizzarlo una volta.
Anche le ultime versioni di MySQL trasmettono istruzioni preparate in un formato binario nativo, che sono più efficienti e possono anche aiutare a ridurre i ritardi della rete.
C'è stato un tempo in cui molti programmatori erano soliti evitare dichiarazioni preparate di proposito, per un singolo motivo importante. Non venivano memorizzati nella cache delle query MySQL. Ma dal momento che intorno alla versione 5.1, anche il caching delle query è supportato.
Per usare le istruzioni preparate in PHP, controlla l'estensione mysqli o usa un livello di astrazione del database come PDO.
// crea un'istruzione preparata if ($ stmt = $ mysqli-> prepare ("SELECT nome utente FROM WHERE state =?")) // bind parameters $ stmt-> bind_param ("s", $ state); // esegue $ stmt-> execute (); // associa variabili di risultato $ stmt-> bind_result ($ username); // recupera valore $ stmt-> fetch (); printf ("% s è da% s \ n", $ username, $ state); $ Stmt-> close (); Normalmente quando si esegue una query da uno script, si attenderà che l'esecuzione di tale query termini prima che possa continuare. Puoi cambiarlo usando le query senza buffer.
C'è una grande spiegazione nei documenti PHP per la funzione mysql_unbuffered_query ():
"mysql_unbuffered_query () invia la query di query SQL a MySQL senza recuperare automaticamente e bufferizzando le righe dei risultati come mysql_query (). Ciò consente di risparmiare una notevole quantità di memoria con query SQL che generano serie di risultati grandi e iniziare a lavorare sul set di risultati immediatamente dopo che la prima riga è stata recuperata poiché non è necessario attendere fino a quando non è stata eseguita la query SQL completa. "
Tuttavia, viene fornito con alcune limitazioni. Devi leggere tutte le righe o chiamare mysql_free_result () prima di poter eseguire un'altra query. Inoltre non ti è permesso usare mysql_num_rows () o mysql_data_seek () sul set di risultati.
Molti programmatori creeranno un campo VARCHAR (15) senza rendersi conto che possono effettivamente memorizzare gli indirizzi IP come valori interi. Con un INT si scende a soli 4 byte di spazio e si ha invece un campo di dimensioni fisse.
Devi assicurarti che la tua colonna sia INT INTEGRATA, perché gli indirizzi IP usano l'intero intervallo di un intero senza segno a 32 bit.
Nelle query è possibile utilizzare INET_ATON () per convertire e IP per un intero e INET_NTOA () per vice versa. Ci sono anche funzioni simili in PHP chiamate ip2long () e long2ip ().
$ r = "UPDATE users SET ip = INET_ATON ('$ _ SERVER [' REMOTE_ADDR ']') WHERE user_id = $ user_id"; Quando ogni singola colonna di una tabella è "a lunghezza fissa", anche la tabella viene considerata "statica" o "a lunghezza fissa". Esempi di tipi di colonna NON di lunghezza fissa sono: VARCHAR, TEXT, BLOB. Se includi anche solo 1 di questi tipi di colonne, la tabella cessa di essere di lunghezza fissa e deve essere gestita in modo diverso dal motore MySQL.
Le tabelle a lunghezza fissa possono migliorare le prestazioni perché è più veloce per il motore MySQL cercare i record. Quando vuole leggere una riga specifica in una tabella, può calcolare rapidamente la sua posizione. Se la dimensione della riga non è fissa, ogni volta che deve effettuare una ricerca, deve consultare l'indice della chiave primaria.
Sono anche più facili da memorizzare nella cache e più facili da ricostruire dopo un crash. Ma possono anche prendere più spazio. Ad esempio, se si converte un campo VARCHAR (20) in un campo CHAR (20), saranno sempre necessari 20 byte di spazio indipendentemente da cosa sia in.
Utilizzando le tecniche di "Vertical Partitioning", è possibile separare le colonne di lunghezza variabile in una tabella separata. Il che ci porta a:
Vertical Partitioning è l'atto di suddividere la struttura della tabella in modo verticale per ragioni di ottimizzazione.
Esempio 1: Potresti avere una tabella utenti che contiene indirizzi di casa, che non vengono letti spesso. È possibile scegliere di dividere la tabella e memorizzare le informazioni sull'indirizzo su una tabella separata. In questo modo la tabella degli utenti principali si ridurrà di dimensioni. Come saprai, i tavoli più piccoli si comportano più velocemente.
Esempio 2: Hai un campo "last_login" nella tua tabella. Si aggiorna ogni volta che un utente accede al sito web. Ma ogni aggiornamento su una tabella fa sì che la cache delle query per quella tabella venga svuotata. Puoi mettere quel campo in un'altra tabella per mantenere al minimo gli aggiornamenti alla tua tabella utenti.
Ma devi anche assicurarti di non aver bisogno di unirti costantemente a queste due tabelle dopo il partizionamento, altrimenti potresti subire un calo delle prestazioni.
Se è necessario eseguire una query DELETE o INSERT su un sito Web attivo, è necessario prestare attenzione a non disturbare il traffico web. Quando viene eseguita una query di questo tipo, è possibile bloccare le tabelle e arrestare l'applicazione Web.
Apache esegue molti processi / thread paralleli. Pertanto funziona in modo più efficiente quando gli script terminano l'esecuzione il prima possibile, quindi i server non presentano troppe connessioni e processi aperti contemporaneamente che consumano risorse, specialmente la memoria.
Se finisci per bloccare i tuoi tavoli per un periodo di tempo prolungato (come 30 secondi o più), in un sito Web ad alto traffico, causerai un processo e un accumulo di query, che potrebbe richiedere molto tempo per cancellare o addirittura danneggiare il tuo web server.
Se si dispone di una sorta di script di manutenzione che deve eliminare un numero elevato di righe, basta utilizzare la clausola LIMIT per farlo in lotti più piccoli per evitare questa congestione.
while (1) mysql_query ("DELETE FROM logs WHERE log_date <= '2009-10-01' LIMIT 10000"); if (mysql_affected_rows() == 0) // done deleting break; // you can even pause a bit usleep(50000); Con i motori di database, il disco è forse il collo di bottiglia più significativo. Mantenere le cose più piccole e più compatte è di solito utile in termini di prestazioni, per ridurre la quantità di trasferimento su disco.
I documenti MySQL hanno un elenco di requisiti di archiviazione per tutti i tipi di dati.
Se ci si aspetta che una tabella abbia pochissime righe, non c'è motivo di trasformare la chiave primaria in INT, invece di MEDIUMINT, SMALLINT o persino in alcuni casi TINYINT. Se non ti serve il componente orario, utilizza DATE anziché DATETIME.
Assicurati di lasciare spazio ragionevole per crescere o potresti finire come Slashdot.
I due principali motori di archiviazione di MySQL sono MyISAM e InnoDB. Ognuno ha i suoi pro e contro.
MyISAM è adatto per applicazioni leggere-pesanti, ma non scala molto bene quando ci sono molte scritture. Anche se si sta aggiornando un campo di una riga, l'intera tabella viene bloccata e nessun altro processo può nemmeno leggerlo fino a quando non termina quella query. MyISAM è molto veloce nel calcolare i tipi di query SELECT COUNT (*).
InnoDB tende ad essere un motore di archiviazione più complicato e può essere più lento di MyISAM per la maggior parte delle piccole applicazioni. Ma supporta il blocco basato su file, che si adatta meglio. Supporta anche alcune funzionalità più avanzate come le transazioni.
Utilizzando un ORM (Object Relational Mapper), è possibile ottenere determinati vantaggi in termini di prestazioni. Tutto ciò che un ORM può fare, può anche essere codificato manualmente. Ma questo può significare troppo lavoro extra e richiede un alto livello di competenza.
Gli ORM sono ottimi per "Lazy Loading". Significa che possono recuperare i valori solo quando sono necessari. Ma devi stare attento con loro o puoi finire con la creazione di molte miniserie che possono ridurre le prestazioni.
Gli ORM possono anche eseguire il batch delle query in transazioni, che funzionano molto più velocemente rispetto all'invio di singole query al database.
Attualmente il mio ORM preferito per PHP è Doctrine. Ho scritto un articolo su come installare Doctrine con CodeIgniter.
Connessioni persistenti hanno lo scopo di ridurre il sovraccarico di ricreare le connessioni a MySQL. Quando viene creata una connessione permanente, rimarrà aperta anche al termine dell'esecuzione dello script. Poiché Apache riutilizza i suoi processi figli, la volta successiva che il processo viene eseguito per un nuovo script, riutilizzerà la stessa connessione MySQL.
Sembra fantastico in teoria. Ma dalla mia esperienza personale (e molti altri), questa caratteristica risulta non valga la pena. È possibile avere seri problemi con i limiti di connessione, problemi di memoria e così via.
Apache funziona in modo estremamente parallelo e crea molti processi figli. Questo è il motivo principale per cui le connessioni persistenti non funzionano molto bene in questo ambiente. Prima di prendere in considerazione l'utilizzo della funzione mysql_pconnect (), consultare l'amministratore di sistema.
