5.2.9 Vitesse des requêtes INSERT

• Connexion : (3)
• Envoi au serveur : (2)
• Analyse de la requête : (2)
• Insertion de la ligne : (1 x taille de la ligne)
• Insertion des index : (1 x nombre d'index)
• Fermeture : (1)

Quelques méthodes pour accélérer les insertions :

• Si vous insérerz plusieurs lignes depuis le même client, en même temps, utilisez les valeurs multiples de la commande INSERT . C'est bien plus rapide (et parfois beaucoup plus rapide) que d'utiliser des commandes INSERT distinctes. Si vous ajoutez des données dans une table non vide, vous pouvez ajuster la variable bulk_insert_buffer_size pour l'accélérer encore plus. SHOW VARIABLES .
• Si vous insérez de nombreuses lignes depuis différents clients, vous pouvez accélérer les insertions en utilisant la commande INSERT DELAYED . Syntaxe des INSERT .
• Notez qu'avec les tables MyISAM , vous pouvez insérer des lignes en même temps que vous utilisez des commandes SELECT , du moment qu'il n'y a pas d'effacement de ligne dans la table.
• Lorsque vous chargez une table depuis un fichier texte, utilisez la commande LOAD DATA INFILE . Elle est généralement 20 fois plus rapide que l'équivalent en commandes INSERT . Syntaxe des LOAD DATA INFILE .
• Il est possible, avec un peu de travail supplémentaire, d'accélérer encore la vitesse des commandes LOAD DATA INFILE . Utilisez la procédure standard :
  • Créez optionnellement une table avec CREATE TABLE . Par exemple, en utilisant mysql ou Perl-DBI.
  • Exécutez une commande FLUSH TABLES ou la commande en ligne shell mysqladmin flush-tables .
  • Utilisez myisamchk --keys-used=0 -rq /path/to/db/tbl_name . Cela va supprimer l'utilisation des index dans la table.
  • Insérez vos données dans la table, avec LOAD DATA INFILE . Les index ne seront pas modifiés, et donc, très rapides.
  • Si vous allez uniquement lire la table dans le futur, utilisez myisampack pour la réduire de taille. Caractéristiques des tables compressées .
  • Re-créez les index avec myisamchk -r -q /path/to/db/tbl_name . Cette commande va créer l'arbre d'index en mémoire, avant de l'écrire sur le disque, ce qui est bien plus rapide, car il n'y a que peu d'accès disques. L'arbre final sera aussi parfaitement équilibrés.
  • Exécutez une commande FLUSH TABLES ou utilisez la commande en ligne shell mysqladmin flush-tables .
  Notez que la ocmmande LOAD DATA INFILE fait aussi les optimisations ci-dessus, si vous faites les insertions dans une table vide. La différence principale avec la procédure ci-dessus est que vous pouvez laisser myisamchk allouer plus de mémoire temporaire pour la création d'index, que vous ne pourriez le faire pour chaque recréation.Depuis MySQL 4.0 vous pouvez aussi utiliser ALTER TABLE tbl_name DISABLE KEYS au lieu de myisamchk --keys-used=0 -rq /path/to/db/tbl_name et ALTER TABLE tbl_name ENABLE KEYS au lieu de myisamchk -r -q /path/to/db/tbl_name . De cette façon, vous pouvez aussi éviter l'étape FLUSH TABLES .
• Vous pouvez accélérer les insertions qui sont faites avec plusieurs requêtes en verrouillant vos tables :

  mysql> LOCK TABLES a WRITE; mysql> INSERT INTO a VALUES (1,23),(2,34),(4,33); mysql> INSERT INTO a VALUES (8,26),(6,29); mysql> UNLOCK TABLES;

  La principale différence de vitesse est que l'index de buffer est écrit sur le disque une fois, après toutes les insertions INSERT terminées. Normalement, il aurait du y avoir de nombreuses écritures, une pour chaque commande INSERT . Le verrouillage n'est pas nécessaire si vous pouvez insérer toutes les lignes d'une seule commande.

  Pour les tables transactionnelles, vous devriez utiliser BEGIN/COMMIT au lieu de LOCK TABLES pour accélérer les opérations.

  Le verrouillage va aussi réduire le nombre total de tests de connexions, mais le temps d'attente maximum de certains threads va augmenter (car il va y avoir la queue pour les verrous). Par exemple :

  thread 1 fait 1000 insertions thread 2, 3, et 4 font 1 insertion thread 5 fait 1000 insertions

  Si vous ne voulez pas utiliser le verrouillage, les threads 2, 3 et 4 auront fini avant les 1 et 5. Si vous utilisez le verrouillage, 2, 3 et 4 me finiront probablement pas avant 1 ou 5, mais la durée globale de l'opération sera 40% plus courte.Comme les commandes INSERT , UPDATE et DELETE sont très rapides avec MySQL, vous obtiendrez de meilleures performances générales en ajoutant des verrous autour de toutes vos opérations de 5 insertions o modifications simultanées. Si vous faîtes de très nombreux insertions dans une ligne, vous pouvez utiliser LOCK TABLES suivi de UNLOCK TABLES une fois de temps en temps (par exemple, toutes les 1000) pour permettre aux autres threads d'accéder à la table. Cela vous donnera quand même une bonne accélération.Bien sur, LOAD DATA INFILE reste bien plus rapide pour charger les données.