# Administration des tables

## Optimiser les tables MySQL et défragmenter pour récupérer de l'espace

  
Si votre application effectue beaucoup de suppressions et de mises à jour sur la base de données MySQL, il est très probable que vos fichiers de données MySQL soient fragmentés.

Cela entraînera beaucoup d'espace inutilisé et pourrait également affecter les performances.

Il est donc fortement recommandé de défragmenter vos tables MySQL de manière continue.

##  Mise en place de la table de test

Il est interessant de se doter d'une table de test

<div data-lang="" id="bkmrk-create-table-randomi"><textarea style="display: none;">create table randomint(value integer); INSERT INTO randomint ( value ) VALUES ( rand() \* 3333 ); -- repeter l'opration suivante plusieurs fois INSERT INTO randomint ( value ) SELECT value \* rand() FROM randomint;</textarea><div><div><textarea spellcheck="false" style="position: absolute; bottom: -1em; padding: 0px; width: 1px; height: 1em; outline: currentcolor none medium;" tabindex="0" wrap="off"></textarea></div><div><div></div><div>```
create table randomint(value integer);<br></br>INSERT INTO randomint ( value ) VALUES ( rand() * 3333 );<br></br><br></br>-- repeter l'opration suivante plusieurs fois<br></br><br></br>INSERT INTO randomint ( value ) SELECT value * rand() FROM randomint;
```

</div></div></div></div>### Requete utiles

La requête suivante permet de lister toutes les bases de données et de calculer la taille de chacune d’elles en Mo.

<div data-lang="" id="bkmrk-select-table_schema-"><textarea style="display: none;">SELECT table\_schema AS NomBaseDeDonnees, ROUND(SUM( data\_length + index\_length ) / 1024 / 1024, 2) AS BaseDonneesMo FROM information\_schema.TABLES GROUP BY TABLE\_SCHEMA;</textarea><div><div><textarea spellcheck="false" style="position: absolute; bottom: -1em; padding: 0px; width: 1px; height: 1em; outline: currentcolor none medium;" tabindex="0" wrap="off"></textarea></div><div>```
SELECT table_schema AS NomBaseDeDonnees, ROUND(SUM( data_length + index_length ) / 1024 / 1024, 2) AS BaseDonneesMo <br></br>FROM information_schema.TABLES GROUP BY TABLE_SCHEMA;
```

</div><div><div></div><div><div></div></div></div></div></div>Il est également possible de visualiser en détail la taille d’une base de données spécifiquement. La requête ci-dessous présente la taille d’une base en Mo, en Ko et aussi en octets. Il faut juste adapter la requête en remplaçant “nom\_base\_de\_donnees” par la base de votre choix.

<div data-lang="" id="bkmrk-select%C2%A0-concat%28-sum%28"><textarea style="display: none;">SELECT CONCAT( SUM(ROUND( ( (DATA\_LENGTH + INDEX\_LENGTH - DATA\_FREE) / 1024 / 1024),2)), 'Mo' ) AS TailleMo, CONCAT( SUM(ROUND( ( (DATA\_LENGTH + INDEX\_LENGTH - DATA\_FREE) / 1024 ),2)), 'Ko' ) AS TailleKo, CONCAT( SUM(ROUND( ( (DATA\_LENGTH + INDEX\_LENGTH - DATA\_FREE) ),2)), 'o' ) AS Tailleo FROM information\_schema.TABLES WHERE TABLE\_SCHEMA = 'nom\_base\_de\_donnees';</textarea></div>```
SELECT CONCAT( SUM(ROUND( ( (DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2)), 'Mo' ) AS TailleMo,
CONCAT( SUM(ROUND( ( (DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 ),2)), 'Ko' ) AS TailleKo, 
CONCAT( SUM(ROUND( ( (DATA_LENGTH + INDEX_LENGTH - DATA_FREE) ),2)), 'o' ) AS Tailleo 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'nom_base_de_donnees';
```

<div id="bkmrk-"></div><div data-lang="" id="bkmrk-pour-conna%C3%AEtre-la-ta"><div><div>Pour connaître la taille de chaque table inclus dans une base il est possible d’exécuter la requête ci-dessous :</div></div></div><div data-lang="" id="bkmrk-select-table_name%2C-c"><textarea style="display: none;">SELECT TABLE\_NAME, CONCAT(ROUND(((DATA\_LENGTH + INDEX\_LENGTH - DATA\_FREE) / 1024 / 1024), 2), 'Mo') AS TailleMo FROM information\_schema.TABLES WHERE TABLE\_SCHEMA = 'nom\_base\_de\_donnees'</textarea></div>```
SELECT TABLE_NAME,
CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024), 2), 'Mo') AS TailleMo 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'nom_base_de_donnees'
```

Pour connaître uniquement la taille d’une table SQL il est possible d’exécuter la requête suivante :

<div data-lang="" id="bkmrk-select-concat%28round%28"><textarea style="display: none;">SELECT CONCAT(ROUND(((DATA\_LENGTH + INDEX\_LENGTH - DATA\_FREE) / 1024 / 1024), 2), 'Mo') AS TailleMo FROM information\_schema.TABLES WHERE TABLE\_SCHEMA = 'nom\_base\_de\_donnees' AND TABLE\_NAME = 'nom\_table';</textarea><div><div><textarea spellcheck="false" style="position: absolute; bottom: -1em; padding: 0px; width: 1px; height: 1em; outline: currentcolor none medium;" tabindex="0" wrap="off"></textarea></div><div></div><div>```
SELECT <br></br>CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024), 2), 'Mo') AS TailleMo <br></br>FROM information_schema.TABLES <br></br>WHERE TABLE_SCHEMA = 'nom_base_de_donnees'<br></br>AND   TABLE_NAME = 'nom_table';
```

<div><div></div></div></div></div></div>##  data\_length et data\_free

  
l'instruction suivante permet d'afficher la data\_lenght et data\_free

<div data-lang="" id="bkmrk-mysql%3E-select-table_"><textarea style="display: none;">mysql&gt; select table\_name,data\_length,data\_free from information\_schema.tables where table\_name like 'randomint'; +------------+-------------+-----------+ | TABLE\_NAME | DATA\_LENGTH | DATA\_FREE | +------------+-------------+-----------+ | randomint | 1589248 | 4194304 | +------------+-------------+-----------+ 1 row in set (0,00 sec)</textarea><div><div><textarea spellcheck="false" style="position: absolute; bottom: -1em; padding: 0px; width: 1px; height: 1em; outline: currentcolor none medium;" tabindex="0" wrap="off"></textarea></div><div></div><div>```
mysql> select table_name,data_length,data_free from information_schema.tables where table_name like 'randomint';<br></br>+------------+-------------+-----------+<br></br>| TABLE_NAME | DATA_LENGTH | DATA_FREE |<br></br>+------------+-------------+-----------+<br></br>|  randomint |  1589248    |   4194304 |<br></br>+------------+-------------+-----------+<br></br><br></br>1 row in set (0,00 sec)
```

<div><div></div></div></div></div></div>ou alors:

<div data-lang="" id="bkmrk-mysql%3E-show-table-st"><textarea style="display: none;">mysql&gt; show table status \\G \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* Name: randomint Engine: InnoDB Version: 10 Row\_format: Dynamic Rows: 32960 Avg\_row\_length: 48 Data\_length: 1589248 Max\_data\_length: 0 Index\_length: 0 Data\_free: 2097152 Auto\_increment: NULL Create\_time: 2019-01-14 11:34:49 Update\_time: NULL Check\_time: NULL Collation: utf8mb4\_0900\_ai\_ci Checksum: NULL Create\_options: Comment: 1 row in set (0,00 sec)</textarea><div><div><textarea spellcheck="false" style="position: absolute; bottom: -1em; padding: 0px; width: 1px; height: 1em; outline: currentcolor none medium;" tabindex="0" wrap="off"></textarea></div><div>```
mysql> show table status \G<br></br>*************************** 1. row ***************************<br></br>Name: randomint<br></br>Engine: InnoDB<br></br>Version: 10<br></br>Row_format: Dynamic<br></br>Rows: 32960<br></br>Avg_row_length: 48<br></br>Data_length: 1589248<br></br>Max_data_length: 0<br></br>Index_length: 0<br></br>Data_free: 2097152<br></br>Auto_increment: NULL<br></br>Create_time: 2019-01-14 11:34:49<br></br>Update_time: NULL<br></br>Check_time: NULL<br></br>Collation: utf8mb4_0900_ai_ci<br></br>Checksum: NULL<br></br>Create_options:<br></br>Comment:<br></br>1 row in set (0,00 sec)
```

<div><div></div></div></div></div></div>### DATA\_LENGTH

Pour MyISAM, DATA\_LENGTH est la longueur du fichier de données, en octets.

Pour InnoDB, DATA\_LENGTH est la quantité approximative de mémoire allouée pour l'index clusterisé, en octets. Plus précisément, il s’agit de la taille de l’index clusterisé, exprimée en pages, multipliée par la taille de la page InnoDB.

###  MAX\_DATA\_LENGTH

Pour MyISAM, MAX\_DATA\_LENGTH est la longueur maximale du fichier de données. Il s'agit du nombre total d'octets de données pouvant être stockés dans la table, en fonction de la taille du pointeur de données utilisé.

Non utilisé pour InnoDB.

### INDEX\_LENGTH

Pour MyISAM, INDEX\_LENGTH est la longueur du fichier d'index, en octets.

Pour InnoDB, INDEX\_LENGTH est la quantité approximative de mémoire allouée pour les index non clusterisés, en octets. Plus précisément, il s'agit de la somme des tailles d'index non clusterisées, en pages, multipliée par la taille de page InnoDB.

###  DATA\_FREE

Nombre d'octets alloués mais non utilisés.

Les tables InnoDB indiquent l'espace libre de l'espace de table auquel la table appartient. Pour une table située dans l'espace de table partagé, il s'agit de l'espace libre de l'espace de table partagé. Si vous utilisez plusieurs espaces de table et que la table possède son propre espace de table, l'espace disponible ne concerne que cette table. Espace libre signifie le nombre d'octets dans des étendues complètement libres moins une marge de sécurité. Même si l'espace libre affiche 0, il est possible d'insérer des lignes tant qu'il n'est pas nécessaire d'attribuer de nouvelles extensions.

DATA\_FREE indique l’espace alloué sur le disque pour un tableau ou un fragment de données de disque, mais n’est pas utilisé par ce dernier. (L'utilisation des ressources de données en mémoire est signalée par la colonne DATA\_LENGTH.)  
La taille du fichier est

```
-rw-r----- 1 pilou pilou 7340032 janv. 14 11:34 randomint.ibd
```

<div data-lang="" id="bkmrk--rw-r------1-pilou-p-0"><textarea style="display: none;">-rw-r----- 1 pilou pilou 7340032 janv. 14 11:34 randomint.ibd </textarea><div><div><textarea spellcheck="false" style="position: absolute; bottom: -1em; padding: 0px; width: 1px; height: 1em; outline: currentcolor none medium;" tabindex="0" wrap="off"></textarea></div></div></div>**Le Data\_free est difficile à prédire.**

- Si la table a été créée avec innodb\_file\_per\_table = 0, il s'agit de l'espace disponible dans ibdata1.
- Si la table n'est pas partitionnée et qu'elle est minuscule, elle est généralement nulle.
- Non partitionné et d’une taille d’au moins mégaoctets, Data\_free fait habituellement exactement 4 Mo, 5 Mo, 6 Mo ou 7 Mo. En effet, InnoDB obtient une "extension" de 8 Mo en anticipant le besoin de davantage d’espace.

Pour les tables partitioné(avant les "partitions natives" de 5.7.xx), chaque PARTITION ressemble à une table et se comporte comme une table. Ainsi, le Data\_free pour la table est vraiment la somme des 4-7 Mo pour chaque partition.   
La table INFORMATION\_SCHEMA.TABLES contient environ 20 colonnes, mais pour déterminer la quantité d’espace disque utilisée par les tables, nous nous intéresserons plus particulièrement à deux colonnes: DATA\_LENGTH et INDEX\_LENGTH.

- DATA\_LENGTH est la longueur (ou la taille) de toutes les données de la table (en octets).
- INDEX\_LENGTH est la longueur (ou la taille) du fichier d'index de la table (également en octets).

Armés de ces informations, nous pouvons exécuter une requête qui listera toutes les tables d’une base de données spécifique ainsi que l’espace disque (taille) de chacune. Nous pouvons même devenir un peu plus sophistiqués et convertir les valeurs de taille normale d'octets en quelque chose de plus utile et compréhensible pour la plupart des gens, comme les mégaoctets.

<div data-lang="" id="bkmrk-mysql%3E-select-table_-0"><textarea style="display: none;">mysql&gt; SELECT TABLE\_NAME AS 'Table', ROUND((DATA\_LENGTH + INDEX\_LENGTH) / 1024 / 1024) AS 'Size (MB)' FROM information\_schema.TABLES WHERE TABLE\_name LIKE "randomint" ORDER BY (DATA\_LENGTH + INDEX\_LENGTH) ; +-----------+-----------+ | Table | Size (MB) | +-----------+-----------+ | randomint | 2 | +-----------+-----------+ 1 row in set (0,00 sec)</textarea><div><div><textarea spellcheck="false" style="position: absolute; bottom: -1em; padding: 0px; width: 1px; height: 1em; outline: currentcolor none medium;" tabindex="0" wrap="off"></textarea></div><div></div><div>```
mysql> SELECT TABLE_NAME AS 'Table', ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS 'Size (MB)'<br></br> FROM information_schema.TABLES WHERE TABLE_name LIKE "randomint" ORDER BY (DATA_LENGTH + INDEX_LENGTH) ;<br></br><br></br>+-----------+-----------+<br></br>|    Table  | Size (MB) |<br></br>+-----------+-----------+<br></br>| randomint |         2 |<br></br>+-----------+-----------+<br></br><br></br>1 row in set (0,00 sec)
```

<div><div></div></div></div></div></div>## ANALYZE TABLE

L’optimiseur de requête MySQL est un composant important du serveur MySQL qui crée un plan d’exécution de requête optimal pour une requête. Pour une requête particulière, l'optimiseur de requête utilise la distribution de clé stockée et d'autres facteurs pour décider de l'ordre dans lequel les tables doivent être jointes lors de l'exécution de la jointure et de l'index à utiliser pour une table spécifique.

Cependant, les distributions de clés peuvent parfois être inexactes, par exemple, après avoir effectué de nombreuses modifications de données dans la table, notamment insérer, supprimer ou mettre à jour. Si la distribution de clé n'est pas exacte, l'optimiseur de requête peut choisir un plan d'exécution de requête incorrect pouvant entraîner un problème de performances majeur.

<div data-lang="" id="bkmrk-mysql%3E-analyze-table"><textarea style="display: none;">mysql&gt; analyze table randomint; +----------------------+---------+----------+----------+ | Table | Op | Msg\_type | Msg\_text | +----------------------+---------+----------+----------+ | myisamtest.randomint | analyze | status | OK | +----------------------+---------+----------+----------+ 1 row in set (0,03 sec)</textarea><div><div><textarea spellcheck="false" style="position: absolute; bottom: -1em; padding: 0px; width: 1px; height: 1em; outline: currentcolor none medium;" tabindex="0" wrap="off"></textarea></div><div></div><div>```
mysql> analyze table randomint;<br></br><br></br>+----------------------+---------+----------+----------+<br></br>|                Table |      Op | Msg_type | Msg_text |<br></br>+----------------------+---------+----------+----------+<br></br>| myisamtest.randomint | analyze |   status |       OK |<br></br>+----------------------+---------+----------+----------+<br></br><br></br>1 row in set (0,03 sec)
```

<div><div></div></div></div></div></div>## OPTIMIZE TABLE

  
Lorsque vous travaillez avec la base de données, vous effectuez de nombreuses modifications, telles que l'insertion, la mise à jour et la suppression de données dans la table, susceptibles de fragmenter le stockage physique de la table. En conséquence, les performances du serveur de base de données sont dégradées.

MySQL vous fournit une instruction qui vous permet d'optimiser la table pour éviter ce problème de défragmentation.

<div data-lang="" id="bkmrk-mysql%3E-optimize-tabl"><textarea style="display: none;">mysql&gt; optimize table randomint; +----------------------+----------+----------+-------------------------------------------------------------------+ | Table | Op | Msg\_type | Msg\_text | +----------------------+----------+----------+-------------------------------------------------------------------+ | myisamtest.randomint | optimize | note | Table does not support optimize, doing recreate + analyze instead | | myisamtest.randomint | optimize | status | OK | +----------------------+----------+----------+-------------------------------------------------------------------+ 2 rows in set (0,88 sec)</textarea><div><div><textarea spellcheck="false" style="position: absolute; bottom: -1em; padding: 0px; width: 1px; height: 1em; outline: currentcolor none medium;" tabindex="0" wrap="off"></textarea></div><div><div></div>```
mysql> optimize table randomint;<br></br><br></br>+----------------------+----------+----------+-------------------------------------------------------------------+<br></br>| Table                |       Op | Msg_type |                                                          Msg_text |<br></br>+----------------------+----------+----------+-------------------------------------------------------------------+<br></br>| myisamtest.randomint | optimize | note     | Table does not support optimize, doing recreate + analyze instead |<br></br>| myisamtest.randomint | optimize | status   |                                                                OK |<br></br>+----------------------+----------+----------+-------------------------------------------------------------------+<br></br><br></br>2 rows in set (0,88 sec)
```

<div><div></div></div></div></div></div>InnoDB ne supporte pas OPTIMIZE comme MyISAM. Il crée une table vide et y copie toutes les lignes de la table existante. Il supprime essentiellement l'ancienne table, la renomme, puis exécute un ANALYZE pour collecter des statistiques. C'est ce qui se rapproche le plus d'InnoDB pour réaliser une OPTIMISATION.

Supprimons les données

<div data-lang="" id="bkmrk-mysql%3E-delete-from-r"><textarea style="display: none;">mysql&gt; delete from randomint; Query OK, 32768 rows affected (0,88 sec) mysql&gt; show table status \\G \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* Name: randomint Engine: InnoDB Version: 10 Row\_format: Dynamic Rows: 32960 Avg\_row\_length: 48 Data\_length: 1589248 Max\_data\_length: 0 Index\_length: 0 Data\_free: 2097152 Auto\_increment: NULL Create\_time: 2019-01-14 11:34:49 Update\_time: NULL Check\_time: NULL Collation: utf8mb4\_0900\_ai\_ci Checksum: NULL Create\_options: Comment: 1 row in set (0,00 sec)</textarea><div><div><textarea spellcheck="false" style="position: absolute; bottom: -1em; padding: 0px; width: 1px; height: 1em; outline: currentcolor none medium;" tabindex="0" wrap="off"></textarea></div><div><div></div></div><div>```
mysql> delete from randomint;<br></br><br></br>Query OK, 32768 rows affected (0,88 sec)<br></br><br></br>mysql> show table status \G<br></br><br></br>*************************** 1. row ***************************<br></br>Name: randomint<br></br>Engine: InnoDB<br></br>Version: 10<br></br>Row_format: Dynamic<br></br>Rows: 32960<br></br>Avg_row_length: 48<br></br>Data_length: 1589248<br></br>Max_data_length: 0<br></br>Index_length: 0<br></br>Data_free: 2097152<br></br>Auto_increment: NULL<br></br>Create_time: 2019-01-14 11:34:49<br></br>Update_time: NULL<br></br>Check_time: NULL<br></br>Collation: utf8mb4_0900_ai_ci<br></br>Checksum: NULL<br></br>Create_options:<br></br>Comment:<br></br><br></br>1 row in set (0,00 sec)
```

</div><div></div><div><div></div><div><div></div></div></div></div></div>  
Ce qui ne change pas grand chose dans MySQL, mais sur le disque

<div data-lang="" id="bkmrk-pilou%40lubuntu%3A%7E%2Fmysq"><textarea style="display: none;">pilou@lubuntu:~/mysql80/mysql-8.0.13-linux-glibc2.12-x86\_64/data/myisamtest$ ls -al total 88 drwxr-x--- 2 pilou pilou 4096 janv. 14 12:00 . drwxr-x--- 12 pilou pilou 4096 janv. 14 11:19 .. -rw-r----- 1 pilou pilou 114688 janv. 14 12:00 randomint.ibd</textarea><div><div><textarea spellcheck="false" style="position: absolute; bottom: -1em; padding: 0px; width: 1px; height: 1em; outline: currentcolor none medium;" tabindex="0" wrap="off"></textarea></div><div><div><div></div>```
<span role="presentation">-rw-r----- 1 pilou pilou 114688 janv. 14 12:00 randomint.ibd</span>
```

</div><div></div><div><div></div></div></div></div></div>Pour raffraichir les statistiques, il faut faire un analyze table

<div data-lang="" id="bkmrk-mysql%3E-analyze-table-0"><textarea style="display: none;">mysql&gt; analyze table randomint; +----------------------+---------+----------+----------+ | Table | Op | Msg\_type | Msg\_text | +----------------------+---------+----------+----------+ | myisamtest.randomint | analyze | status | OK | +----------------------+---------+----------+----------+ 1 row in set (0,09 sec) mysql&gt; show table status \\G \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* Name: randomint Engine: InnoDB Version: 10 Row\_format: Dynamic Rows: 0 Avg\_row\_length: 0 Data\_length: 16384 Max\_data\_length: 0 Index\_length: 0 Data\_free: 0 Auto\_increment: NULL Create\_time: 2019-01-14 12:00:11 Update\_time: NULL Check\_time: NULL Collation: utf8mb4\_0900\_ai\_ci Checksum: NULL Create\_options: Comment: 1 row in set (0,00 sec)</textarea><div><div><textarea spellcheck="false" style="position: absolute; bottom: -1em; padding: 0px; width: 1px; height: 1em; outline: currentcolor none medium;" tabindex="0" wrap="off"></textarea></div><div></div><div>```
mysql> analyze table randomint;<br></br><br></br>+----------------------+---------+----------+----------+<br></br>| Table                | Op      | Msg_type | Msg_text |<br></br>+----------------------+---------+----------+----------+<br></br>| myisamtest.randomint | analyze | status   |       OK |<br></br>+----------------------+---------+----------+----------+<br></br><br></br>1 row in set (0,09 sec)<br></br><br></br>​<br></br>mysql> show table status \G<br></br><br></br>************************** 1. row ***************************<br></br>Name: randomint<br></br>Engine: InnoDB<br></br>Version: 10<br></br>Row_format: Dynamic<br></br>Rows: 0<br></br>Avg_row_length: 0<br></br>Data_length: 16384<br></br>Max_data_length: 0<br></br>Index_length: 0<br></br>Data_free: 0<br></br>Auto_increment: NULL<br></br>Create_time: 2019-01-14 12:00:11<br></br>Update_time: NULL<br></br>Check_time: NULL<br></br>Collation: utf8mb4_0900_ai_ci<br></br>Checksum: NULL<br></br>Create_options:<br></br>Comment:<br></br><br></br>1 row in set (0,00 sec)
```

<div><div></div></div></div></div></div>## Format des tables

  
Afin de voir les différents format, nous allons créer une table de champs TEXT

```
mysql> create table compacttable (c1 LONGTEXT) row_format=compact;
Query OK, 0 rows affected (0,06 sec)

mysql> insert into compacttable values (REPEAT("0123456789", 10000));
Query OK, 1 row affected (0,10 sec)

mysql> select length(REPEAT("0123456789", 10000));
+-------------------------------------+
| length(REPEAT("0123456789", 10000)) |
+-------------------------------------+
|                              100000 |
+-------------------------------------+

1 row in set (0,00 sec)


```

<div data-lang="" id="bkmrk-mysql%3E-create-table--0"><textarea style="display: none;">mysql&gt; create table compacttable (c1 LONGTEXT) row\_format=compact; Query OK, 0 rows affected (0,06 sec) mysql&gt; insert into compacttable values (REPEAT("0123456789", 10000)); Query OK, 1 row affected (0,10 sec) mysql&gt; select length(REPEAT("0123456789", 10000)); +-------------------------------------+ | length(REPEAT("0123456789", 10000)) | +-------------------------------------+ | 100000 | +-------------------------------------+ 1 row in set (0,00 sec)</textarea><div><div><textarea spellcheck="false" style="position: absolute; bottom: -1em; padding: 0px; width: 1px; height: 1em; outline: currentcolor none medium;" tabindex="0" wrap="off"></textarea></div><div>```
<span role="presentation"> </span>
```

</div></div></div>### Statut des tables

Il est possible d'avoir l'état d'une table ainsi

<div data-lang="" id="bkmrk-mysql%3E-show-table-st-0"><textarea style="display: none;">mysql&gt; SHOW TABLE STATUS IN test1\\G \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* Name: t1 Engine: InnoDB Version: 10 Row\_format: Dynamic Rows: 0 Avg\_row\_length: 0 Data\_length: 16384 Max\_data\_length: 0 Index\_length: 16384 Data\_free: 0 Auto\_increment: 1 Create\_time: 2016-09-14 16:29:38 Update\_time: NULL Check\_time: NULL Collation: latin1\_swedish\_ci Checksum: NULL Create\_options: Comment:</textarea><div>```
mysql> SHOW TABLE STATUS IN test1\G<br></br><br></br>*************************** 1. row ***************************<br></br>           Name: t1<br></br>         Engine: InnoDB<br></br>        Version: 10<br></br>     Row_format: Dynamic<br></br>           Rows: 0<br></br> Avg_row_length: 0<br></br>    Data_length: 16384<br></br>Max_data_length: 0<br></br>  Index_length: 16384<br></br>      Data_free: 0<br></br> Auto_increment: 1<br></br>    Create_time: 2016-09-14 16:29:38<br></br>    Update_time: NULL<br></br>     Check_time: NULL<br></br>      Collation: latin1_swedish_ci<br></br>       Checksum: NULL<br></br> Create_options: <br></br>        Comment:
```

<div></div><div><div><div></div></div></div></div></div>Erreur possible

Le format de ligne par défaut pour la table InnoDB est défini par la variable innodb\_default\_row\_format. La valeur par défaut est différente dans MySQL 5.6 et 5.7. Si vous êtes maintenant conscient de cette différence et que vous utilisez la valeur par défaut de MySQL 5.6, vous pouvez rencontrer des problèmes tels que:

<div id="bkmrk--0"></div><div data-lang="bash hljs" id="bkmrk-row-size-too-large-%28"><textarea style="display: none;">Row size too large (&gt; 8126). Changing some columns to TEXT or BLOB or using ROW\_FORMAT=DYNAMIC or ROW\_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline</textarea><div><div><textarea spellcheck="false" style="position: absolute; bottom: -1em; padding: 0px; width: 1px; height: 1em; outline: currentcolor none medium;" tabindex="0" wrap="off"></textarea></div><div><div><div></div></div></div></div></div><table id="bkmrk-row-formatredundantc"><thead><tr><th>Row Format</th><th>`REDUNDANT`</th><th>`COMPACT`</th><th>`DYNAMIC`</th><th>`COMPRESSED`</th></tr></thead><tbody><tr><td>Compact Storage Characteristics</td><td>No</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>Enhanced Variable-Length Column Storage</td><td>No</td><td>No</td><td>Yes</td><td>Yes</td></tr><tr><td>Large Index Key Prefix Support</td><td>No</td><td>No</td><td>Yes</td><td>Yes</td></tr><tr><td>Compression Support</td><td>No</td><td>No</td><td>No</td><td>Yes</td></tr><tr><td>Supported Tablespace Types</td><td>system, file-per table, general\*</td><td>system, file-per-table, general\*</td><td>file-per-table, general\*</td><td>file-per-table, general\*</td></tr><tr><td>Required File Format</td><td>Antelope or Barracuda</td><td>Antelope or Barracuda</td><td>Barracuda</td><td>Barracuda</td></tr></tbody></table>

###  Compact

Compact est le format par défaut et est généralement approprié pour le format de fichier Antelope. Introduit dans MySQL 5.0.

Dans ce format (comme dans Redundant) les colonnes BLOB et TEXT sont partiellement stockées dans les pages de données. Au moins 767 octets sont stockés dans la ligne; tous les débords sont stockés dans des pages dédiées. Comme les tailles de lignes de Compact et Redundant sont d'environ 8000 octets, cela limite le nombre de colonnes BLOB ou TEXT qui peuvent être utilisées dans une table. Chaque page de BLOB page contient 16Ko, sans compter les données.

Les autres colonnes peuvent être stockées dans des pages différentes si elles dépassent la taille de ligne maximum par page.

###  Redundant

Redundant est l'ancien, format non compressé supporté par les anciennes versions de MySQL. C'était le seul format dispobible avant la version 5.0 et a été le mode par défaut dans MySQL 5.0.3. Il est recommandé de paramétrer innodb\_strict\_mode lorsque vous l'utilisez.

###  Dynamic

Les tables en format Dynamic contiennent des enregistrements de taille variable, ce qui permet de mieux exploiter l'espace disque que Compact ou Redundant, notamment pour les tables contenant des BLOBs, mais moins que le format Compressed.

Il ne peut être utilisé qu'avec le format de fichier XtraDB Barracuda, et nécessite que les tables et index soient stockés dans leur propre tablespace, ce qui implique que les variables systèmes soient parametrées innodb\_file\_per\_table=1 et innodb\_file\_format=barracuda. Il est recommandé de parametrer innodb\_strict\_mode lors de l'utilisation de ce format.

La taille maximum des lignes est désormais de 65535 octets.

Avec le format Dynamic (et Compressed), les colonnes BLOB et TEXT sont stockées différemment de Compact. Si les données ne peuvent être contenues dans une ligne de page, alors seulement un pointeur sera stocké et contiendra l'adresse de la page dédiée. Chaque page externe contient une partie des données et l'adresse de la page suivante, si nécessaire. Les pointeurs ont une taille de 20Ko. Cela permet de stocker un grand nombre de colonnes BLOB ou de TEXT dans une table.

### Compressed

Le format Compressed permet de réduire la taille des données. Il ne peut être utilisé qu'avec le format de fichier XtraDB Barracuda, et nécessite que les tables et index soient stockés dans leur propre tablespace, ce qui implique que les variables systèmes soient parametrées innodb\_file\_per\_table=1 et innodb\_file\_format=barracuda. Il est recommandé de parametrer innodb\_strict\_mode lors de l'utilisation de ce format.

Le fait d'utiliser le format Compressed reduit aussi le KEY\_BLOCK\_SIZE par défaut. Si KEY\_BLOCK\_SIZE est omis de la clause CREATE TABLE ou ALTER TABLE, il sera par défaut à 8Ko - traditionnellement c'est 16Ko (cf. innodb\_page\_size). Il est aussi possible de régler le KEY\_BLOCK\_SIZE à 1Ko, 2Ko, 4Ko ou 16Ko. Le fait de le régler à 16Ko, la taille standard, provoquera généralement une compression minimale à moins qu'il n'y ait beaucoup de colonnes de type BLOB, TEXT ou VARCHAR.

Notez que préciser un KEY\_BLOCK\_SIZE spécifique dans une définition de table provoquera automatiquement une compression - Il n'est donc pas nécessaire de préciser l'option ROW\_FORMAT=COMPRESSED.

Afin d'évider trop de compression/décompression de pages, XtraDB/InnoDB essaye de garder les pages compressées et decompressées dans le buffer pool, quand il y a assez d'espace. Cela donne un cache plus gros. Lorsque la place vient à manquer, un algorithme de LRU vient décider de la suppression de pages compressées ou decompressées du buffer: pour soulager les CPUs, les pages compressées sont supprimées en priorité; pour soulager les I/O, les pages non compressées sont supprimées en priorité. Bien entendu, quand cela est nécessaire, la règle s'applique aux pages compressées et non compressées.