Rechercher dans le manuel MySQL
8.5.1 Optimizing Storage Layout for InnoDB Tables
Once your data reaches a stable size, or a growing table has increased by tens or some hundreds of megabytes, consider using the
OPTIMIZE TABLE
statement to reorganize the table and compact any wasted space. The reorganized tables require less disk I/O to perform full table scans. This is a straightforward technique that can improve performance when other techniques such as improving index usage or tuning application code are not practical.OPTIMIZE TABLE
copies the data part of the table and rebuilds the indexes. The benefits come from improved packing of data within indexes, and reduced fragmentation within the tablespaces and on disk. The benefits vary depending on the data in each table. You may find that there are significant gains for some and not for others, or that the gains decrease over time until you next optimize the table. This operation can be slow if the table is large or if the indexes being rebuilt do not fit into the buffer pool. The first run after adding a lot of data to a table is often much slower than later runs.In
InnoDB
, having a longPRIMARY KEY
(either a single column with a lengthy value, or several columns that form a long composite value) wastes a lot of disk space. The primary key value for a row is duplicated in all the secondary index records that point to the same row. (See Section 15.6.2.1, “Clustered and Secondary Indexes”.) Create anAUTO_INCREMENT
column as the primary key if your primary key is long, or index a prefix of a longVARCHAR
column instead of the entire column.Use the
VARCHAR
data type instead ofCHAR
to store variable-length strings or for columns with manyNULL
values. ACHAR(
column always takesN
)N
characters to store data, even if the string is shorter or its value isNULL
. Smaller tables fit better in the buffer pool and reduce disk I/O.When using
COMPACT
row format (the defaultInnoDB
format) and variable-length character sets, such asutf8
orsjis
,CHAR(
columns occupy a variable amount of space, but still at leastN
)N
bytes.For tables that are big, or contain lots of repetitive text or numeric data, consider using
COMPRESSED
row format. Less disk I/O is required to bring data into the buffer pool, or to perform full table scans. Before making a permanent decision, measure the amount of compression you can achieve by usingCOMPRESSED
versusCOMPACT
row format.
Traduction non disponible
Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.
Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-optimizing-innodb-storage-layout.html
L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.
Références
Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.