MySQL
Suggerimenti per le prestazioni Mysql

Seleziona l'ottimizzazione della dichiarazione

Di seguito sono riportati alcuni suggerimenti da ricordare mentre stiamo scrivendo una query di selezione in MySQL che può aiutarci e ridurre i tempi di richiesta: -

1. Ogni volta che utilizziamo la posizione in una tabella di grandi dimensioni, dovremmo assicurarci che la colonna in cui la clausola è indicizzata o meno. Es .: - Selezionare * dal dipendente dove user_id> 2000. user_id se indicizzato quindi velocizzerà la valutazione della query atlot. Gli indici sono anche molto importanti durante i join e le chiavi esterne.

2. Quando hai bisogno della sezione più piccola di contenuti piuttosto che recuperare interi dati dalla tabella, prova a usare il limite. Piuttosto che scrivere Ex: - Seleziona * dal dipendente. Se hai bisogno solo dei primi 20 dipendenti da lakh, usa solo il limite Ex: - Seleziona * dal dipendente LIMIT 20.

3. È inoltre possibile ottimizzare la query fornendo il nome della colonna che si desidera nel set di risultati. Piuttosto che scrivere Ex: - Seleziona * dal dipendente. Basta menzionare il nome della colonna da cui hai bisogno di dati se la tabella ha un sacco di colonne e vuoi avere i dati per pochi di essi. Es .: - Seleziona id, nome dal dipendente.

4. Colonna dell'indice se si sta utilizzando per verificare la clausola NULL in where. Se hai qualche istruzione come SELECT * FROM tbl_name WHERE key_col IS NULL; quindi se key_col è indicizzato allora la query sarà valutata più velocemente.

Ottimizzazione del layout di archiviazione per le tabelle InnoDB

1. In InnoDB, con un lungo PRIMARY KEY (una singola colonna con un valore lungo o più colonne che formano un valore composito lungo) spreca molto spazio su disco. Il valore della chiave primaria per una riga è duplicato in tutti i record dell'indice secondario che puntano alla stessa riga. Crea una colonna AUTO_INCREMENT come chiave primaria se la tua chiave primaria è lunga.
2. Utilizzare il tipo di dati VARCHAR anziché CHAR per memorizzare stringhe di lunghezza variabile o per colonne con molti valori NULL. Una colonna CHAR (N) accetta sempre N caratteri per memorizzare i dati, anche se la stringa è più corta o il suo valore è NULL. Le tabelle più piccole si adattano meglio al pool buffer e riducono l'I / O del disco.

Quando si utilizza il formato di riga COMPACT (il formato InnoDB predefinito) e i set di caratteri a lunghezza variabile, come utf8 o sjis, le colonne CHAR (N) occupano una quantità variabile di spazio, ma comunque almeno N byte.

3. Per le tabelle che sono grandi o contengono molti testi ripetitivi o dati numerici, prendere in considerazione l'utilizzo del formato di riga COMPRESSA. È richiesto un numero minore di I / O su disco per portare i dati nel pool buffer o per eseguire scansioni complete della tabella. Prima di prendere una decisione definitiva, misurare la quantità di compressione che è possibile ottenere utilizzando il formato di riga COMPRESSA e COMPATTA. Avvertenza: i benchmark raramente mostrano una compressione migliore di 2: 1 e c'è molto overhead nel buffer_pool per COMPRESSED.
4. Una volta che i tuoi dati raggiungono una dimensione stabile, o una tabella crescente è aumentata di decine o qualche centinaia di megabyte, considera l'utilizzo dell'istruzione OPTIMIZE TABLE per riorganizzare la tabella e compattare qualsiasi spazio sprecato. Le tabelle riorganizzate richiedono meno I / O su disco per eseguire scansioni complete della tabella. Questa è una tecnica semplice che può migliorare le prestazioni quando altre tecniche come il miglioramento dell'uso dell'indice o del codice dell'applicazione di ottimizzazione non sono pratici. Avvertenza : indipendentemente dalla dimensione della tabella, la tabella OPTIMIZE deve essere eseguita solo raramente. Questo perché è costoso e raramente migliora il tavolo tanto da valerne la pena. InnoDB è abbastanza bravo a mantenere le sue B + Trees libere da molto spazio sprecato.

OTTIMIZZA TABELLA copia la parte di dati della tabella e ricostruisce gli indici. I vantaggi derivano dal miglioramento della compressione dei dati all'interno degli indici e dalla riduzione della frammentazione all'interno degli spazi tabella e sul disco. I vantaggi variano in base ai dati di ogni tabella. Potresti scoprire che ci sono guadagni significativi per alcuni e non per altri, o che i guadagni diminuiscono nel tempo fino a quando non ottimizzi la tabella. Questa operazione può essere lenta se la tabella è grande o se gli indici da ricostruire non si adattano al pool di buffer. La prima esecuzione dopo l'aggiunta di molti dati a una tabella è spesso molto più lenta delle esecuzioni successive.

Costruire un indice composito

In molte situazioni, un indice composito si comporta meglio di un indice con una singola colonna. Per costruire un indice composito ottimale, popolarlo con colonne in questo ordine.

• = colonna (i) prima dalla clausola WHERE . (ad es. INDEX(a,b,...) per WHERE a=12 AND b='xyz' ... )
• Colonna (e) IN ; l'ottimizzatore potrebbe essere in grado di saltare attraverso l'indice.
• Un "range" (es. x BETWEEN 3 AND 9 , name LIKE 'J%' ) Non utilizzerà nulla oltre la colonna del primo intervallo.
• Tutte le colonne in GROUP BY , nell'ordine
• Tutte le colonne in ORDER BY , nell'ordine. Funziona solo se tutti sono ASC o tutti sono DESC o si utilizza 8.0.

Note ed eccezioni:

• Non duplicare nessuna colonna.
• Salta su tutti i casi che non si applicano.
• Se non si utilizzano tutte le colonne di WHERE , non è necessario andare a GROUP BY , ecc.
• Ci sono casi in cui è utile indicizzare solo le colonne ORDER BY , ignorando WHERE .
• Non "nascondere" una colonna in una funzione (es. DATE(x) = ... non può usare x nell'indice.)
• L'indicizzazione 'Prefix' (ad esempio, text_col(99) ) è improbabile che sia utile; può ferire

Maggiori dettagli e suggerimenti .