MySQL: Calcul des volumes de données avec la base information_schema

MySQL (depuis la version 5.0.2) fournit une base de données « virtuelle » contenant des informations intéressantes sur les bases existantes (définitions, tailles, statistiques, …): information_schema.

Son schéma est dénormalisé; cette base permet donc de trouver facilement les informations, sans jointure.

Voici quelques requêtes en rapport avec la taille des données et indexes que j’utilise régulièrement pour identifier ce qui consomme de la place.

Taille des données et indexes par moteur

select ENGINE,
sum(DATA_LENGTH)/(1024*1024) as "Données (Mo)",
sum(INDEX_LENGTH)/(1024*1024) as "Indexes (Mo)",
sum(DATA_LENGTH+INDEX_LENGTH)/(1024*1024) as "Total (Mo)"
from information_schema.TABLES
group by ENGINE;

Résultat:

+--------+---------------+--------------+------------+
| ENGINE | Données (Mo)  | Indexes (Mo) | Total (Mo) |
+--------+---------------+--------------+------------+
| InnoDB |        9.9531 |       2.9219 |    12.8750 |
| MEMORY |        0.0000 |       0.0000 |     0.0000 |
| MyISAM |       55.0762 |       4.9922 |    60.0683 |
+--------+---------------+--------------+------------+

Taille des données et indexes par base

select TABLE_SCHEMA,
sum(DATA_LENGTH)/(1024*1024) as "Données (Mo)",
sum(INDEX_LENGTH)/(1024*1024) as "Indexes (Mo)",
sum(DATA_LENGTH+INDEX_LENGTH)/(1024*1024) as "Total (Mo)"
from information_schema.TABLES
group by TABLE_SCHEMA;

Résultat:

+--------------------+---------------+--------------+------------+
| TABLE_SCHEMA       | Données (Mo)  | Indexes (Mo) | Total (Mo) |
+--------------------+---------------+--------------+------------+
| ampache            |       51.8759 |       4.7305 |    56.6064 |
| blogs              |        0.8438 |       0.7344 |     1.5781 |
| cdarchive          |        0.2568 |       0.0664 |     0.3232 |
| site1              |        5.5625 |       0.7500 |     6.3125 |
| gallery2           |        0.9375 |       0.7344 |     1.6719 |
| information_schema |        0.0000 |       0.0039 |     0.0039 |
| site2              |        2.3750 |       0.5000 |     2.8750 |
| mysql              |        0.4392 |       0.0654 |     0.5046 |
| phpinv             |        0.1150 |       0.0488 |     0.1638 |
| piwigo             |        0.0102 |       0.0557 |     0.0659 |
| zenphoto           |        0.2344 |       0.2031 |     0.4375 |
+--------------------+---------------+--------------+------------+

Taille des données et indexes des tables d’une base

select TABLE_NAME,
DATA_LENGTH/(1024*1024) as Datas,
INDEX_LENGTH/(1024*1024) as Indexes,
TABLE_ROWS as Rows
from information_schema.TABLES
where TABLE_SCHEMA='ampache'
order by Datas DESC limit 10;

Résultat:

+-------------------+---------+---------+-------+
| TABLE_NAME        | Datas   | Indexes | Rows  |
+-------------------+---------+---------+-------+
| album_data        | 47.7389 |  0.0176 |   868 |
| song              |  3.0678 |  3.4121 | 18086 |
| song_data         |  0.4783 |  0.1787 | 18086 |
| tag_map           |  0.3905 |  0.7305 | 17059 |
| artist            |  0.0943 |  0.1865 |  3732 |
| tmp_browse        |  0.0385 |  0.0029 |    41 |
| album             |  0.0352 |  0.0781 |   935 |
| tmp_playlist_data |  0.0103 |  0.0127 |   447 |
| session           |  0.0048 |  0.0059 |    29 |
| object_count      |  0.0042 |  0.0215 |   243 |
+-------------------+---------+---------+-------+

Recherche des colonnes de type ‘blob’

select TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE
from information_schema.COLUMNS
where DATA_TYPE like '%blob%' and TABLE_SCHEMA != 'mysql';

Résultat:

+--------------+-------------+-------------+------------+
| TABLE_SCHEMA | TABLE_NAME  | COLUMN_NAME | DATA_TYPE  |
+--------------+-------------+-------------+------------+
| ampache      | album_data  | art         | mediumblob |
| ampache      | album_data  | thumb       | mediumblob |
| ampache      | artist_data | art         | mediumblob |
| ampache      | artist_data | thumb       | mediumblob |
+--------------+-------------+-------------+------------+

Référence: http://dev.mysql.com/doc/refman/5.1/en/information-schema.html