Rechercher dans le manuel MySQL
8.5.5 Bulk Data Loading for InnoDB Tables
These performance tips supplement the general guidelines for fast inserts in Section 8.2.5.1, “Optimizing INSERT Statements”.
When importing data into
InnoDB
, turn off autocommit mode, because it performs a log flush to disk for every insert. To disable autocommit during your import operation, surround it withSET autocommit
andCOMMIT
statements:The mysqldump option
--opt
creates dump files that are fast to import into anInnoDB
table, even without wrapping them with theSET autocommit
andCOMMIT
statements.If you have
UNIQUE
constraints on secondary keys, you can speed up table imports by temporarily turning off the uniqueness checks during the import session:For big tables, this saves a lot of disk I/O because
InnoDB
can use its change buffer to write secondary index records in a batch. Be certain that the data contains no duplicate keys.If you have
FOREIGN KEY
constraints in your tables, you can speed up table imports by turning off the foreign key checks for the duration of the import session:For big tables, this can save a lot of disk I/O.
Use the multiple-row
INSERT
syntax to reduce communication overhead between the client and the server if you need to insert many rows:This tip is valid for inserts into any table, not just
InnoDB
tables.When doing bulk inserts into tables with auto-increment columns, set
innodb_autoinc_lock_mode
to 2 (interleaved) instead of 1 (consecutive). See Section 15.6.1.4, “AUTO_INCREMENT Handling in InnoDB” for details.When performing bulk inserts, it is faster to insert rows in
PRIMARY KEY
order.InnoDB
tables use a clustered index, which makes it relatively fast to use data in the order of thePRIMARY KEY
. Performing bulk inserts inPRIMARY KEY
order is particularly important for tables that do not fit entirely within the buffer pool.For optimal performance when loading data into an
InnoDB
FULLTEXT
index, follow this set of steps:Define a column
FTS_DOC_ID
at table creation time, of typeBIGINT UNSIGNED NOT NULL
, with a unique index namedFTS_DOC_ID_INDEX
. For example:Load the data into the table.
Create the
FULLTEXT
index after the data is loaded.
NoteWhen adding
FTS_DOC_ID
column at table creation time, ensure that theFTS_DOC_ID
column is updated when theFULLTEXT
indexed column is updated, as theFTS_DOC_ID
must increase monotonically with eachINSERT
orUPDATE
. If you choose not to add theFTS_DOC_ID
at table creation time and haveInnoDB
manage DOC IDs for you,InnoDB
will add theFTS_DOC_ID
as a hidden column with the nextCREATE FULLTEXT INDEX
call. This approach, however, requires a table rebuild which will impact performance.
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-bulk-data-loading.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.