Ce qui est plus rapide: plusieurs unique INSERTs ou une INSERTION de plusieurs lignes?

Je suis en train d'optimiser une partie de mon code qui insère des données dans MySQL. Dois-je de la chaîne d'INSERTs pour faire un énorme INSERTION de plusieurs lignes ou sont multiples séparées Insère plus vite?

InformationsquelleAutor dusoft | 2009-11-24

260

https://dev.mysql.com/doc/refman/8.0/en/insert-optimization.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:
- De la connexion: (3)
- L'envoi de la requête au serveur: (2)
- L'analyse de la requête: (2)
- De l'insertion de la ligne: (1 × taille de ligne)
- L'insertion d'index: (1 × nombre d'index)
- De clôture: (1)
De cela, 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.
- Comment est-ce répondre à appliquer si plusieurs unique INSERTs sont dans la même transaction de base de données?
- Le nombre de lignes que je peux insérer à l'aide de la seule instruction insert. est-il me permettre d'insérer 10000 lignes à la fois?
- À l'aide d'une transaction tout en faisant ~1,5 k upserts (insertion/mises à jour) a diminué le temps de l'opération a pris de ~1,5 secondes pour ~0,2 secondes. Ou en d'autres termes, il a 86% plus rapide par rapport à une seule rangée d'inserts. Merde.
- Note: Semble être très différente dans le MSSQL: stackoverflow.com/questions/8635818/...
- Comment à propos de l'utilisation de l'Instruction Préparée pour l'insertion répétitif multiples simples semelles?
InformationsquelleAutor mbarkhau
143

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 vite 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 unique de la VALEUR des 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: de 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.

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.)
- Demande de précisions: c'est votre temps "secondes par ligne" ou "secondes au total".
- Secondes au total - secondes par ligne, c'est que divisé par le ~de 19 000 lignes. Si c'est un petit nombre, peut-être que les lignes/seconde est une meilleure métrique si vous êtes à la recherche pour facilement un nombre comparable.
- D'ailleurs, il y a quelques exemples .NET code pour l'approche que j'ai décrit ci-dessus sur cette réponse de la mine: stackoverflow.com/questions/25377357/...
InformationsquelleAutor Jon Kloske
17

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

De 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 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 se synchronise pour le 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.
- Est-il une raison quelconque, il devient contre-productif, après un point? J'ai vu cela se produire avant aussi, mais n'était pas sûr de savoir pourquoi.
- Savez-vous si il y a n'importe quel point du tout dans le dosage, MySQL insère quand l'utilisation de transactions. Je me demande juste si je peut me sauver de l'ennui d'avoir à générer les valeurs multiples de la commande SQL si ma bibliothèque sous-jacente (Java JDBC mysql-connector-java-5.1.30) n'est pas réellement commettre jusqu'à ce que je lui dis de.
- Je pense que vous aurez besoin d'effectuer un petit test pour déterminer que le comportement dans votre situation, il est fortement mise en œuvre spécifique de comportement, mais dans de nombreux cas, oui transactions devraient fournir des gains de performances similaires.
InformationsquelleAutor MarkR
8

Envoyer autant d'inserts à travers le fil à la fois que possible. La réelle insertion de la vitesse devrait être la même, mais vous verrez des gains de performances de la réduction de la surcharge du réseau.

InformationsquelleAutor RC.
7

En général moins le nombre d'appels à la base de données le mieux (ce qui signifie, plus rapide, plus efficace), alors essayez de code de la insère d'une façon telle qu'elle minimise les accès base de données. Rappelez-vous, à moins que votre utilisation d'un pool de connexion, chaque databse accès a pour créer une connexion, exécutez l'instruction sql, puis à la déchirure en bas de la connexion. Tout à fait un peu de surcharge!
- que faire si la connexion permanente est-il utilisé?
- Il y a toujours une surcharge. Le temps de Transit (pour et de pour chaque insert) va rapidement être perceptible si vous êtes en train de faire des milliers de plaquettes.
InformationsquelleAutor ennuikiller
4

Vous pourriez vouloir :
- Vérifier que l'auto-validation est désactivée
- Connexion Ouverte
- Envoyer plusieurs lots de l'insère dans une transaction unique (taille d'environ 4000-10000 lignes ? vous voyez)
- Fermeture de la connexion
Selon la façon dont votre serveur échelles (son définitivement ok avec PostgreSQl, Oracle et MSSQL), faire la chose la-dessus avec plusieurs threads et de multiples connexions.

InformationsquelleAutor Antibarbie
3

En général, plusieurs insertions sera plus lent en raison de la connexion de frais généraux. De la réalisation de plusieurs inserts à la fois permettra de réduire le coût des frais généraux par insert.

Selon la langue que vous utilisez, vous pouvez peut-être créer un lot dans votre programmation/langage de script avant d'aller à la db et ajouter chaque insertion pour le lot. Alors vous seriez en mesure d'exécuter un grand lot à l'aide d'une opération de connexion. Voici un exemple en Java.

InformationsquelleAutor Chris Williams
3

MYSQL 5.5
Une instruction sql insert a pris ~300 ~450 ms.
tandis que le dessous de stats est pour les insertions multiples relevés.
```
(25492 row(s) affected)
Execution Time : 00:00:03:343
Transfer Time  : 00:00:00:000
Total Time     : 00:00:03:343
```
Je dirais inline est de chemin à faire 🙂

InformationsquelleAutor A_01
1

C'est ridicule, comment le mauvais Mysql et MariaDB sont optimisés quand il s'agit de plaquettes.
J'ai testé mysql 5.7 et mariadb 10.3, pas de réelle différence sur ceux-ci.

J'ai testé cela sur un serveur avec NVME disques, de 70 000 IOPS, 1,1 GO/sec seq débit et c'est possible en full duplex (lire et écrire).

Le serveur est un serveur hautes performances ainsi.

Il a donné 20 GO de ram.

La base de données complètement vide.

La vitesse je reçois était de 5000 insère par seconde quand on fait de multiples insertions de lignes (essayé avec 1 MO à 10 mo, des blocs de données)

Maintenant l'indice:

Si j'ajoute un autre thread et insérez-les dans les MÊMES tables j'ai subitement 2x5000 /sec.
Un fil en plus et j'ai 15000 total /sec

Considérez ceci: Quand UN thread inserts cela signifie que vous pouvez séquentiellement écrire sur le disque (avec des exceptions pour les indices).
Lors de l'utilisation de threads que vous dégradera les performances possibles, car il doit maintenant faire beaucoup plus aléatoire accède.
Mais la réalité montre que mysql est tellement mal optimisé que les fils d'une grande aide.

La performance réelle possible avec un tel serveur est probablement des millions par seconde, le PROCESSEUR est inactif le disque est inactif.

La raison est assez clairement que mariadb comme mysql a interne des retards.
- Qu'entendez-vous par l'ajout de fils?
- vous avez besoin de développement externes, il ne peut pas être fait dans mysql. Les Threads, vous devez utiliser plusieurs connexions au serveur, diviser la requête en plusieurs morceaux (par exemple en les divisant en parties par clé primaire). J'ai réussi à obtenir jusqu'à 10 000 fois la performance à l'aide de cette méthode sur de très grandes tables. Les requêtes qui irait à 40 000 secondes peut terminer dans les 2 à 3 minutes SI vous utilisez plusieurs threads et de votre serveur mysql est hautement optimisé.
- Génial merci!
- Intéressant et peut avoir une certaine immobilier nice applications... mais... Si vous divisez la requête en plusieurs morceaux comment avez-vous gérer les transactions? Et aussi envisager le scénario suivant: Tableau x a 'parent_id' colonne qui se rapporte à la même table "id". Quelque part à l'intérieur de vos données, vous devez les INSÉRER DANS x (id, parent_id) VALUES (1, NULL) . L'une des prochaines séries de valeurs de liens à cette ligne. Si vous diviser en morceaux et que le jeu arrive à un autre morceau, il peut être traité avant la première, à défaut de l'ensemble du processus. Une idée de comment faire face à cela?
- ceci est utile pour les insertions et les requêtes en masse. Les Transactions serait la ruine de la performance de toute façon comme ils incluent beaucoup de données en mémoire tampon. Mais vous pouvez aussi utiliser les opérations d'inserts filetés ou des requêtes.
InformationsquelleAutor John
0

plusieurs insertions sont plus rapides, mais il a thredshould. un autre thrik est de désactiver les contraintes des contrôles temprorary faire insère beaucoup beaucoup plus rapide. Il dosn pas d'importance à votre table ou pas. Par exemple tester la désactivation des clés étrangères et de profiter de la vitesse:
```
SET FOREIGN_KEY_CHECKS=0;
```
offcourse vous devez retourner sur le dos après insère par:
```
SET FOREIGN_KEY_CHECKS=1;
```
c'est la façon la plus commune de l'insertion de données énorme.
les données integridity peut se briser si vous devriez en occuper avant la désactivation du contrôle des clefs étrangères.
- Aucune idée de pourquoi ppl upvoted ce pour deux raisons: 1. Il n'a rien à voir avec la question 2. C'est une très mauvaise idée (à quelques exceptions près, comme le dumping ou de structure, les changements de temp -, mais mauvais en général). Les contrôles sont là pour une raison: Ils sont là pour garantir la cohérence des données. Ils ralentir les choses est parce qu'ils vous assurer de ne pas insérer ou de modifier les données que vous ne devriez pas. Essayez d'optimiser les requêtes de la bonne façon; dans toute entreprise critique de l'environnement, cela signifierait la mort de l'application puisque peu importe comment vous êtes attentif les choses vont échouer à un moment donné.
- peut-être, mais cette option est extrêmement efficace dans l'importation de grandes tables et très pratique et il peut laisser aux gens une idée de comment ils peuvent faire d'insertion de données beaucoup plus rapide.
InformationsquelleAutor MSS

Vous devez vous connecter pour publier un commentaire.