Descrivere gli spazi di tabella
Uno spazio tabelle è un gruppo di file di dati. Ogni spazio tabelle può archiviare i dati per una o più tabelle. Esistono tre tipi di spazi di tabella:
- Spazio di tabella di sistema
- Spazio di tabella file-per-table
- Spazio di tabella generale
Per visualizzare un elenco completo degli spazi di tabella nel server MySQL, eseguire la query:
SELECT TABLESPACE_NAME, FILE_NAME FROM information_schema.FILES;
Viene restituito un elenco di spazi di tabella insieme ai relativi nomi di file.
I file di dati innoDB hanno un'estensione ibd.
Nota
MySQL esegue una scansione degli spazi di tabella all'avvio, quindi un numero molto elevato di spazi di tabella può far sì che l'avvio o il failover di MySQL richieda più tempo o addirittura non riesca.
Spazio di tabella di sistema
Ogni database MySQL ha almeno uno spazio tabella, lo spazio tabella di sistema ed è possibile crearne di più con l'istruzione CREATE TABLESPACE .
Esiste sempre uno spazio di tabella di sistema , dove vengono archiviati il dizionario dati InnoDB e i log di annullamento.
In Database di Azure per MySQL il parametro del server innodb_data_file_path è un parametro statico impostato su autoextend. In questo modo viene configurato il tablespace di sistema per l'estensione automatica, il che significa che lo spazio non si esaurirà. Questo parametro non può essere modificato, quindi non è necessario preoccuparsi di ridimensionare lo spazio tabella di sistema.
Spazio di tabella file-per-table
Per impostazione predefinita, i dati di tabella e indice vengono archiviati in uno spazio di tabella file per tabella o in uno spazio di tabella a tabella singola. Uno spazio di tabella file-per-table contiene una tabella che include i dati e gli indici. Quando si elimina la tabella, viene eliminato anche lo spazio di tabella file-per-table.
Il parametro del server innodb_file_per_table definisce se ogni tabella appena creata viene creata nel proprio spazio di tabella. Per impostazione predefinita, questo parametro è impostato su ON. Se innodb_file_per_table è impostato su OFF, le nuove tabelle vengono create nello spazio tabelle di sistema.
Suggerimento
Per ottenere prestazioni ottimali, non modificare il valore predefinito della tabella innodb_file_per .
Spazio di tabella generale
Se si desidera archiviare le tabelle in uno spazio tabelle creato, usare la sintassi seguente:
CREATE TABLESPACE mytablespace ADD DATAFILE '/var/lib/mysql/mytablespace.ibd' engine = Innodb;
Per creare una tabella nello spazio di tabella generale creato, usare la sintassi seguente:
CREATE TABLE new_table (myID INT) TABLESPACE = mytablespace;
Quando si crea uno spazio di tabella generale, il percorso del file di dati è facoltativo. La sintassi seguente funzionerà anche:
CREATE TABLESPACE mytablespace ADD DATAFILE 'mytablespace.ibd' engine = Innodb;
Gli spazi di tabella generali non vengono eliminati automaticamente, anche se tutte le tabelle che contengono sono state eliminate. Per eliminare uno spazio di tabella generale:
DROP TABLESPACE mytablespace;
In genere, è preferibile mantenere l'impostazione predefinita che prevede la creazione di ogni nuova tabella nel proprio spazio di tabella file-per-table. Se si ha un numero elevato di tabelle di piccole dimensioni, potrebbe esserci spazio inutilizzato o frammentazione, che deve essere gestito, ma le prestazioni complessive saranno migliori.
Procedura consigliata per lo spazio di tabella
- Creare tutte le tabelle nello spazio di tabella innodb_file_per_table.
- Se una tabella ha dimensioni superiori a 1 TB, prendere in considerazione il partizionamento della tabella.
- Anche per le tabelle di grandi dimensioni, è consigliabile usare lo spazio di tabella file-per-table. In questo modo si impedisce che lo spazio di tabella di sistema superi il limite massimo di archiviazione.