Come ottenere la dimensione di una tabella in MySQL

Come molti database relazionali, MySQL fornisce utili metadati sul database stesso. Mentre la maggior parte degli altri database si riferisce a queste informazioni come catalog, la documentazione ufficiale di MySQL si riferisce ai metadati INFORMATION_SCHEMA come tables.

A prescindere dal nome, ciò che conta sono le informazioni fornite da queste tabelle INFORMATION_SCHEMA. Tutto, da views e user_privilieges a columns e tables può essere trovato nelle INFORMATION_SCHEMA. Per i nostri scopi siamo particolarmente interessati ai metadati tables, che possiamo interrogare per estrarre effettivamente le dimensioni delle varie tabelle nel sistema.

List Table Sizes From a Single Database

Come si può vedere nella documentazione ufficiale, la tabella INFORMATION_SCHEMA.TABLES contiene circa 20 colonne, ma allo scopo di determinare la quantità di spazio su disco utilizzato dalle tabelle, ci concentreremo su due colonne in particolare: DATA_LENGTH e INDEX_LENGTH.

• DATA_LENGTH è la lunghezza (o dimensione) di tutti i dati nella tabella (in bytes).
• INDEX_LENGTH è la lunghezza (o dimensione) del file indice della tabella (anch’esso in bytes).

Con queste informazioni, possiamo eseguire una query che elencherà tutte le tabelle in un database specifico insieme allo spazio su disco (dimensione) di ciascuna. Possiamo anche diventare un po’ più sofisticati e convertire i normali valori di dimensione da bytes in qualcosa di più utile e comprensibile per la maggior parte delle persone come megabytes.

SELECT TABLE_NAME AS `Table`, ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`FROM information_schema.TABLESWHERE TABLE_SCHEMA = "bookstore"ORDER BY (DATA_LENGTH + INDEX_LENGTH)DESC;

In questo esempio usando il database bookstore, stiamo combinando DATA_LENGTH e INDEX_LENGTH come bytes, poi dividendolo per 1024 due volte per convertire in kilobytes e poi megabytes. Il nostro set di risultati sarà simile a questo:

+----------------------------------+-----------+| Table | Size (MB) |+----------------------------------+-----------+| book | 267 || author | 39 || post | 27 || cache | 24 |...

Se non vi interessano tutte le tabelle nel database e volete solo la dimensione di una tabella particolare, potete semplicemente aggiungere AND TABLE_NAME = "your_table_name" alla clausola WHERE. Qui vogliamo solo informazioni sulla tabella book:

SELECT TABLE_NAME AS `Table`, ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`FROM information_schema.TABLESWHERE TABLE_SCHEMA = "bookstore" AND TABLE_NAME = "book"ORDER BY (DATA_LENGTH + INDEX_LENGTH)DESC;

I risultati, come previsto, sono ora:

+-------+-----------+| Table | Size (MB) |+-------+-----------+| book | 267 |+-------+-----------+1 row in set (0.00 sec)

List All Table Sizes From ALL Databases

Se stai incontrando un problema in cui il tuo database sta crescendo di dimensioni ma non sai quale tabella è la colpevole, può essere utile interrogare le dimensioni di tutte le tabelle in tutti i database dell’intero sistema. Questo può essere realizzato facilmente con la seguente query:

SELECT TABLE_SCHEMA AS `Database`, TABLE_NAME AS `Table`, ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`FROM information_schema.TABLESORDER BY (DATA_LENGTH + INDEX_LENGTH)DESC;

Questo restituirà non solo la dimensione della tabella, ma anche il nome della tabella e il database padre a cui è associata.