Comment insérer des 20 millions d'enregistrer en base de données MySql aussi vite que possible
J'ai une table de base de données comme ci-dessous:
create table temperature
(id int unsigned not null auto_increment primary key,
temperature double
);
Et dans mon programme, j'ai environ 20 millions de température à insérer dans le tableau.
Je worke .Net de l'environnement, utilisez le Connecteur/Net pour se connecter à MySql. Le code était comme ci-dessous:
List<double> temps = new List<double>();
...
string connStr = "server=localhost;user=name;database=test;port=3306;password=*****;";
MySqlConnection conn = new MySqlConnection(connStr);
try
{
conn.Open();
//temps.Count is about 20 million
for (int i = 0; i < temps.Count; i++)
{
string sql1 = "INSERT INTO temperature VALUES (null, "+temps[i]+")";
MySqlCommand cmd1 = new MySqlCommand(sql1, conn);
cmd1.ExecuteNonQuery();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
conn.Close();
Comment puis-je insérer autant de lignes de données aussi vite que possible?
(Il ne peut insérer des enregistrements de 2000 chaque minute dans mon ordinateur.)
Je suis un peu curieux. Pourquoi voulez-vous insérer de 20 millions de températures dans une DB ?
Avez-vous besoin d'auto-incrémentation? J'ai une situation similaire (sql server) et je gère les touches incrément côté client sur le chargeur. Je mange 75.000 lignes par seconde sur mon matériel actuel. Pas de SQL, mais...
Aussi votre sql "suce" 😉 pouvez-vous ne pas présenter de multiples états d'insertion dans une série? C'est une chaîne, peut mysql poignée hthat? 10 isnerts par voyage aller-retour est de 10%, les allers-retours. THreads ajouter moer (multi-threaded charge).
Je crois fermement que multithread 'forcing' de données dans le serveur ne sera pas d'une grande aide ici, car elle permettrait de générer inutile de verrouillage sur le serveur.
Oh, mais la plupart du temps le serveur n'ont rien faire ici parce que vous ahve allers-retours du client vers le serveur. Envoyer des données, attendez que la sauvegarde des données, en ce temps, un autre thread pourrait updaet la table.
Avez-vous besoin d'auto-incrémentation? J'ai une situation similaire (sql server) et je gère les touches incrément côté client sur le chargeur. Je mange 75.000 lignes par seconde sur mon matériel actuel. Pas de SQL, mais...
Aussi votre sql "suce" 😉 pouvez-vous ne pas présenter de multiples états d'insertion dans une série? C'est une chaîne, peut mysql poignée hthat? 10 isnerts par voyage aller-retour est de 10%, les allers-retours. THreads ajouter moer (multi-threaded charge).
Je crois fermement que multithread 'forcing' de données dans le serveur ne sera pas d'une grande aide ici, car elle permettrait de générer inutile de verrouillage sur le serveur.
Oh, mais la plupart du temps le serveur n'ont rien faire ici parce que vous ahve allers-retours du client vers le serveur. Envoyer des données, attendez que la sauvegarde des données, en ce temps, un autre thread pourrait updaet la table.
OriginalL'auteur Hancy | 2011-12-12
Vous devez vous connecter pour publier un commentaire.
vous pouvez utiliser le concept de
bulk insert
qui exécute de nombreux inserts en même temps de minimiser les frais généraux de l'appel àExecuteNonQuery
plusieurs fois.dans MySQL ce qui est appelé
LOAD DATA
, vérifiez ici pour plus de détails: http://dev.mysql.com/doc/refman/5.5/en/load-data.htmldans MS SQL Server, c'est appelé
bulk insert
et il est connu en tant que tel, c'est pourquoi je l'ai mentionné sous ce nom-là.OriginalL'auteur Davide Piras
Il y a un certain nombre de façons d'optimiser les insertions. Certains sont les suivants:
LOAD DATA INFILE
. Il y a un API wrapper pour .NET. C'est le moyen le plus rapide, mais présente certaines limites et des différences sémantiques à partir de simples plaquettes.De plusieurs lignes
INSERT
états:INSERT INTO temperature (temperature) VALUES (1.0), (2.0), (3.0), ...
Vous ne devez pas insérer 20.000.000 lignes à la fois, mais pouvez essayer de 1.000-10.000 pour une très grande vitesse. C'est un simple et sans problème de façon à augmenter la vitesse. Un facteur de 10 et parfois bien plus souvent possible.
De verrouillage de la table (
LOCK TABLES
).La désactivation des index temporairement.
MySQL options de réglage.
INSERT DELAYED
(probablement pas utile ici).La documentation ne vous donner plus de détail sur les options. Certaines options dépendent du type de table (InnoDB vs MyISAM).
Une suggestion générale: Toujours spécifier les colonnes que vous insérez dans le front de
VALUES
. Cela rend plus facile à gérer le code.OriginalL'auteur Thomas Luzat
Vous devez effectuer des insertions. L'ADO.NET façon de le faire est d'utiliser un DataAdapter.
Pour un MySQL solution spécifique, utilisez la MySqlBulkLoader.
OriginalL'auteur jgauffin
Règles générales :-
La plupart des conseils sont expliqués à la documentation.
OriginalL'auteur ajreal