Come ottimizzare le tabelle MySQL

Introduzione

L'ottimizzazione delle tabelle MySQL aiuta a riordinare le informazioni in un server di archiviazione dedicato per migliorare la velocità di input e output dei dati. Tuttavia, sapere quando utilizzare ciascuna funzione di ottimizzazione e come applicarla alla propria situazione è la chiave per una manutenzione praticabile della tabella.

Questo articolo fornisce suggerimenti pratici e funzioni per l'ottimizzazione delle tabelle MySQL.

Prerequisiti

• MySQL versione 8.0 installata e configurata
• Accesso al prompt dei comandi o alla riga di comando
• Tabelle database frammentate

Perché dovresti ottimizzare le tabelle MySQL?

Il motivo principale per una tabella MySQL non ottimizzata è l'aggiornamento e l'eliminazione delle query eseguite frequentemente. A sua volta, questo provoca la frammentazione e ci sono alcune conseguenze:

1. La tabella del database occupa più spazio del necessario.

2. L'esecuzione di query sui dati richiede più tempo di quanto dovrebbe.

Le tecniche di ottimizzazione delle tabelle MySQL riguardano la disposizione dei dati all'interno di un database. Il risultato è un'archiviazione pulita senza spazio ridondante e inutilizzato, che aiuta a velocizzare le query.

Quando dovresti ottimizzare le tabelle MySQL?

Le tabelle in cui le informazioni in un database vengono aggiornate continuamente, come i database transazionali, sono i candidati più probabili per l'ottimizzazione.

Tuttavia, a seconda delle dimensioni del database, la query di ottimizzazione richiede molto tempo per terminare con tabelle più grandi. Pertanto, bloccare una tabella per un lungo numero di ore non è vantaggioso per i sistemi transazionali.

Invece di ottimizzare subito una tabella, prova a provare alcuni di questi trucchi per le tabelle del motore INNODB:

• Elimina l'indice, ottimizza e aggiungi nuovamente l'indice . Se una tabella funziona senza indici per un po' di tempo, in alcuni casi l'eliminazione, l'ottimizzazione e l'aggiunta degli indici sono più veloci.
• Analizza quale valore trae maggiori benefici dalla compattazione . A volte, l'indice primario non è utile o problematico. L'ottimizzazione per il valore errato provoca la frammentazione con gli indici secondari.

Trova tabelle per l'ottimizzazione

Esistono diversi modi per mostrare le tabelle e analizzarle per l'ottimizzazione. Inizia connettendoti al tuo database MySQL:

use <database name>

A seconda del tuo caso d'uso, filtra le informazioni necessarie seguendo uno dei suggerimenti di seguito.

Suggerimento 1:mostra lo spazio inutilizzato in una tabella

Verifica lo stato della tabella desiderata con:

show table status like "<table name>" \G

L'output mostra alcune informazioni generali sulla tabella. I seguenti due numeri sono importanti:

• Lunghezza_dati rappresenta la quantità di spazio occupata in totale dal database.
• Senza dati mostra i byte allocati inutilizzati all'interno della tabella del database. Queste informazioni aiutano a identificare quali tabelle necessitano di ottimizzazione e quanto spazio verrà rilasciato in seguito.

Suggerimento 2:mostra lo spazio inutilizzato per tutte le tabelle

Lo schema informativo memorizza i metadati su uno schema di database. Per controllare i dati allocati inutilizzati per tutte le tabelle in uno schema selezionato, eseguire:

select table_name, data_length, data_free
from information_schema.tables
where table_schema='<schema name>'
order by data_free desc;

La query visualizza il nome della tabella, lo spazio totale e lo spazio allocato inutilizzato. Per impostazione predefinita, la memoria stampa in byte.

Suggerimento 3:visualizza i dati in Megabyte

Stampa i dati in megabyte con:

select table_name, round(data_length/1024/1024), round(data_free/1024/1024)
from information_schema.tables
where table_schema='<schema name>'
order by data_free desc;

Le tabelle di esempio non sono molto frammentate. Tuttavia, utilizzando optimize table aiuta a liberare spazio dalla test_table .

Ottimizza le tabelle

Esistono diversi modi per ottimizzare le tabelle mediante la deframmentazione. Tuttavia, i passaggi generali che esegui sono simili.

Innanzitutto, il processo crea una copia temporanea della tabella in cui si verifica l'ottimizzazione. Quindi, al termine dell'operazione, la funzione sostituisce la tabella originale con quella ottimizzata, rinominandola dopo l'originale.

Suggerimento 1:ottimizza una tabella utilizzando MySQL

MySQL fornisce una funzione per l'ottimizzazione di una tabella. Per ottimizzare una tabella, usa:

OPTIMIZE TABLE <table name>;

L'output mostra un messaggio di stato informativo sulle azioni e sui risultati dell'ottimizzazione in formato tabellare.

Suggerimento 2:ottimizza più tabelle contemporaneamente

Per ottimizzare più tabelle contemporaneamente, utilizza:

OPTIMIZE TABLE <table 1>, <table 2>, <table 3>;

Il risultato mostra lo stato dell'ottimizzazione per ciascuna tabella ottimizzata.

Suggerimento 3:ottimizza le tabelle utilizzando il terminale

Esegui l'ottimizzazione della tabella tramite il terminale Linux con:

sudo mysqlcheck -o <schema> <table> -u <username> -p <password>

Ad esempio, per eseguire il controllo su uno schema denominato test e la tabella denominata test_table utilizzando la radice credenziali utente, eseguire:

sudo mysqlcheck -o test test_table -u root

Per eseguire l'ottimizzazione su più tabelle, separa ogni nome di tabella con uno spazio:

sudo mysqlcheck -o <schema> <table 1> <table 2> -u <username> -p <password>

Dopo l'ottimizzazione

I risultati dell'ottimizzazione modificano i valori di data_length e data_free della tabella ottimizzata. Entrambi i valori vengono abbassati, indicando:

1. L'ottimizzazione ha liberato la memoria allocata inutilizzata.

2. La memoria complessiva del database è inferiore a causa dello spazio rilasciato.