Quel est le plus rapide: plusieurs INSERT simples ou un INSERT à plusieurs lignes?

Question

J'essaie d'optimiser une partie de mon code qui insère des données dans MySQL. Dois-je enchaîner les INSERT pour créer un INSERT à plusieurs lignes ou plusieurs INSERT distincts?

Answer 1

http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html

Le temps nécessaire pour l'insertion d'une ligne est déterminée par les facteurs suivants, où les chiffres indiquent les proportions approximatives:

• Connexion: (3)
• L'envoi de la requête au serveur: (2)
• L'analyse de la requête: (2)
• L'insertion de la ligne: (1 × taille de ligne)
• L'insertion d'index: (1 × nombre d'index)
• Clôture: (1)

De ce fait, il devrait être évident que l'envoi d'une grande instruction, vous économiserez des frais généraux de 7 pour instruction insert, qui, en plus de la lecture du texte dit aussi:

Si vous insérez le nombre de lignes à partir du même client dans le même temps, l'utilisation des instructions INSERT avec plusieurs listes de VALEURS à insérer plusieurs lignes à la fois. C'est beaucoup plus rapide (plusieurs fois plus rapide dans certains cas) qu'à l'aide d'une ligne unique de l'INSERT.

Answer 2

Je sais que je vais répondre à cette question, près de deux ans et demi après, il a été demandé, mais je voulais juste fournir certaines données à partir d'un projet sur lequel je travaille en ce moment qui montre qu'en effet, la réalisation de plusieurs VALEUR blocs par insertion est BEAUCOUP plus rapide que séquentielle VALEUR unique d'INSERTION des blocs de déclarations.

Le code que j'ai écrit pour ce cas-test en C# utilise ODBC pour lire les données dans la mémoire à partir d'une source de données MSSQL (~de 19 000 lignes, sont à lire avant toute écriture commence), et le MySql .NET (connecteur Mysql.Les données.*) des trucs pour INSÉRER les données de la mémoire dans une table sur un serveur MySQL via des requêtes préparées. Il a été écrit de manière à me permettre d'ajuster dynamiquement le nombre de VALEUR blocs préparé par INSERTION (ie, insérez n lignes à la fois, où j'ai pu régler la valeur de n avant une course.) J'ai également fait le test plusieurs fois pour chaque n.

Faire seule VALEUR blocs (par exemple, 1 ligne à la fois) a pris de 5,7 5,9 secondes pour s'exécuter. Les autres valeurs sont comme suit:

2 lignes à la fois: 3.5 - 3.5 secondes
5 lignes à la fois: 2.2 - 2.2 secondes
10 lignes à la fois: 1.7 - 1.7 secondes
50 lignes à la fois: 1.17 - 1.18 secondes
100 lignes à la fois: 1.1 - 1.4 secondes
500 lignes à la fois: 1.1 - 1.2 secondes
1000 lignes à la fois: 1.17 - 1.17 secondes

Donc oui, même juste le regroupement de 2 ou 3 écrit ensemble fournit une amélioration spectaculaire de la vitesse (runtime coupé par un facteur de n), jusqu'à ce que vous arrivez à quelque part entre n = 5 et n = 10, à quel point l'amélioration de baisse de façon marquée, et quelque part dans le n = 10 n = 50 plage de l'amélioration devient négligeable.

L'espoir qui aide les gens à décider (a) si l'utilisation de l'multiprepare idée, et (b) combien de VALEUR des blocs pour créer par déclaration (en supposant que vous souhaitez travailler avec des données qui peuvent être assez grand pour pousser la requête passé le max de taille de requête pour MySQL, qui, je crois, est de 16 mo par défaut dans beaucoup d'endroits, probablement plus grande ou plus petite, selon la valeur de max_allowed_packet défini sur le serveur.)

Answer 3

Un facteur important sera de savoir si vous êtes en utilisant un moteur transactionnel et si vous avez de validation automatique.

Validation automatique est activée par défaut et vous voulez probablement le laisser sur; par conséquent, chaque insert que vous faites ne sa propre transaction. Cela signifie que si vous faites un insert par ligne, vous allez commettre une transaction pour chaque ligne.

En supposant qu'un seul thread, cela signifie que le serveur a besoin de synchroniser les données de disque pour CHAQUE LIGNE. Il doit attendre que les données atteignent un stockage persistant emplacement (en espérant que la batterie de sauvegarde de la ram dans votre contrôleur RAID). C'est en soi plutôt lent et va probablement devenir le facteur limitant dans ces cas.

Je suis bien sûr en supposant que vous utilisez un moteur transactionnel (généralement innodb) ET que vous n'avez pas modifié les paramètres pour réduire la longévité.

Je suis aussi en supposant que vous êtes en utilisant un seul fil de ces insertions. L'utilisation de plusieurs threads brouille un peu les choses parce que certaines versions de MySQL ont groupe de travail de validation en innodb, ce qui signifie que plusieurs threads en faisant leur propre commet peuvent partager une seule écriture dans le journal des transactions, ce qui est bon, car cela signifie moins de synchronisations de stockage persistant.

D'autre part, le résultat final est que vous VOULEZ VRAIMENT UTILISER les insertions de lignes.

Il y a une limite au cours de laquelle il devient contre-productif, mais dans la plupart des cas, il est à moins de 10 000 lignes. Donc, si vous lot jusqu'à 1 000 lignes, vous êtes probablement en sécurité.

Si vous utilisez MyISAM, il y a toute une autre charge de choses, mais je ne vais pas vous ennuyer avec ces. La paix.

Answer 4

Envoyez autant d'insertions sur le fil que possible. La vitesse d'insertion réelle devrait être la même, mais vous obtiendrez des gains de performance grâce à la réduction de la charge réseau.

Answer 5

En général, moins il y a d'appels à la base de données, mieux c'est (c'est-à-dire plus rapide, plus efficace), essayez donc de coder les insertions de manière à minimiser les accès à la base de données. Rappelez-vous que, sauf si vous utilisez un pool de connexions, chaque accès à la base de données doit créer une connexion, exécuter le fichier SQL, puis supprimer la connexion. Un peu de frais généraux!