Comparaison de plusieurs méthodes MySQL pour insérer ou modifier de grandes quantités de données

Qu'il s'agisse du traitement quotidien de données commerciales ou de l'importation et de l'exportation de bases de données, vous pourriez être confronté au besoin de traiter l'insertion ou la modification de grandes quantités de données. La méthode d'insertion ou de modification et le moteur de base de données affecteront tous la vitesse d'insertion. Cet article vise à analyser et comparer diverses méthodes d'un point de vue théorique et pratique pour faciliter la sélection des méthodes d'insertion dans les applications futures.

Analyse d'insertion

Le temps requis pour insérer un enregistrement dans MySQL est composé des facteurs suivants, où les nombres représentent des proportions approximatives :

Connexion : (3)
Envoyer la requête Au serveur : (2)
Requête d'analyse : (2)
Insérer un enregistrement : (1x taille de l'enregistrement)
Insérer un index : (1x index)
Fermer : (1)

Si nous exécutons une instruction SQL pour chaque insertion, alors nous devons exécuter toutes les étapes sauf la connexion et la fermeture N fois, ce qui prend beaucoup de temps. Il existe plusieurs façons d'optimiser :

(1 ) Écrivez plusieurs lignes dans chaque instruction d'insertion, insertion par lots

(2) Écrivez toutes les instructions de requête dans la transaction

(3) Utilisez Load Data pour importer des données

Les performances de chaque méthode est la suivante.

Moteur Innodb

InnoDB fournit à MySQL une table de type transaction (conforme à l'ACID), de restauration (rollback) et de récupération après incident (capacités de récupération après incident) sécurisée. InnoDB fournit le verrouillage de ligne (verrouillage au niveau de la ligne) et des contraintes de clé étrangère (contraintes FOREIGN KEY).

InnoDB est conçu pour gérer des systèmes de bases de données de grande capacité, et son utilisation du processeur est inégalée par les autres moteurs de bases de données relationnelles sur disque. Techniquement, InnoDB est un système de base de données complet placé sur l'arrière-plan MySQL. InnoDB établit son propre pool de tampons dédié dans la mémoire principale pour la mise en cache des données et des index.

Environnement de test

Macbook Air 12mid apache2.2.26 php5.5.10 mysql5.6.16

Total 1 million de données

Après l'insertion, la taille de la base de données est de 38,6 Mo (sans index), 46,8 (avec index)

Durée totale pour une insertion unique sans index : 229 s Mémoire maximale : 246 Ko
Durée totale pour une insertion unique avec index : 242 s Mémoire maximale : 246 Ko
Lot sans index Temps d'insertion total : 10s Mémoire maximale : 8643 Ko
Temps total d'insertion du lot avec index : 16s Mémoire maximale : 8643 Ko
Temps total d'insertion sans transaction d'index : 78s Mémoire maximale : 246 Ko
Temps total d'insertion avec transaction d'indexation Temps : 82 s Mémoire maximale : 246 Ko
Temps total pour l'insertion des données de chargement sans index : 12 s Mémoire maximale : 246 Ko
Temps total pour l'insertion des données de chargement indexées : 11 s Mémoire maximale : 246 Ko

Moteur MyIASM

MyISAM est le moteur de stockage par défaut pour MySQL. La conception est simple et prend en charge la recherche en texte intégral.

Environnement de test

Macbook Air 12mid apache2.2.26 php5.5.10 mysql5.6.16

Total 1 million de données

Après l'insertion, la taille de la base de données est de 19,1 Mo (sans index), 38,6 (avec index)

Durée totale pour une insertion unique sans index : 82 s Mémoire maximale : 246 Ko
Durée totale pour une insertion unique avec index : 86 s Mémoire maximale : 246 Ko
Lot sans index Temps total d'insertion : 3s Mémoire pic : 8643 Ko
Temps total d'insertion du lot avec index : 7s Mémoire pic : 8643 Ko
Temps total d'insertion sans index Chargement des données : 6s Mémoire pic : 246 Ko
Chargement Données avec index Temps total d'insertion : 8s Mémoire maximale : 246 Ko

Résumé

La quantité de données que j'ai testée n'est pas très importante, mais je peux à peu près comprendre l'impact de ces méthodes d'insertion sur la vitesse. La méthode la plus rapide doit être la méthode Load Data. Cette méthode est relativement délicate car elle implique l'écriture de fichiers, mais elle peut prendre en compte à la fois la mémoire et la vitesse.

Code de test

<?php
$dsn = &#39;mysql:host=localhost;dbname=test&#39;;
$db = new PDO($dsn,&#39;root&#39;,&#39;&#39;,array(PDO::ATTR_PERSISTENT => true));
//删除上次的插入数据
$db->query(&#39;delete from `test`&#39;);
//开始计时
$start_time = time();
$sum = 1000000;
// 测试选项
$num = 1;
if ($num == 1){
    // 单条插入
    for($i = 0; $i < $sum; $i++){
        $db->query("insert into `test` (`id`,`name`) values ($i,&#39;tsetssdf&#39;)");
    }
} elseif ($num == 2) {
    // 批量插入，为了不超过max_allowed_packet，选择每10万插入一次
    for ($i = 0; $i < $sum; $i++) {
        if ($i == $sum - 1) { //最后一次
            if ($i%100000 == 0){
                $values = "($i, &#39;testtest&#39;)";
                $db->query("insert into `test` (`id`, `name`) values $values");
            } else {
                $values .= ",($i, &#39;testtest&#39;)";
                $db->query("insert into `test` (`id`, `name`) values $values");
            }
            break;
        }
        if ($i%100000 == 0) { //平常只有在这个情况下才插入
            if ($i == 0){
                $values = "($i, &#39;testtest&#39;)";
            } else {
                $db->query("insert into `test` (`id`, `name`) values $values");
                $values = "($i, &#39;testtest&#39;)";
            }
        } else {
            $values .= ",($i, &#39;testtest&#39;)";    
        }
    }
} elseif ($num == 3) {
    // 事务插入
    $db->beginTransaction(); 
    for($i = 0; $i < $sum; $i++){
        $db->query("insert into `test` (`id`,`name`) values ($i,&#39;tsetssdf&#39;)");
    }
    $db->commit();
} elseif ($num == 4) {
    // 文件load data
    $filename = dirname(__FILE__).&#39;/test.sql&#39;;
    $fp = fopen($filename, &#39;w&#39;);
    for($i = 0; $i < $sum; $i++){
        fputs($fp, "$i,&#39;testtest&#39;\r\n");    
    }
    $db->exec("load data infile &#39;$filename&#39; into table test fields terminated by &#39;,&#39;");
}
$end_time = time();
echo "总耗时", ($end_time - $start_time), "秒\n";
echo "峰值内存", round(memory_get_peak_usage()/1000), "KB\n";

Voici une brève description de la forme d'épissage des instructions d'insertion et de modification d'instructions SQL :

(1) épissage d'instruction d'insertion :

INSERT INTO utilisateur (id, nom) VALUES (1, '张三');
INSERT INTO utilisateur (id, nom) VALUES (2, '李思');
INSERT INTO utilisateur (id , nom) VALUES (3, '王五');

peut être fusionné en :

INSERT INTO user (id, name) VALUES
(1, '张三'),
(2, '李思'),
(3, '王五');

(2) épissage de l'instruction de mise à jour :

mettre à jour le nom de l'ensemble d'utilisateurs ='Zhang San' où id='1';
mettre à jour le nom de l'ensemble d'utilisateurs='李思' où id='2';
mettre à jour le nom de l'ensemble d'utilisateurs='Wang Wu' où id='3';

Peut être épissé comme :

mettre à jour l'utilisateur
définir le nom = CASE id
quand 1 puis '张三'
quand 2 puis '李思'
when 3 then '王五'
end
where id IN (1,2,3);

Ce qui précède est une comparaison de plusieurs méthodes MySQL pour insérer ou modifier de grandes quantités de données . J'espère que cela sera utile à tout le monde.

Tutoriels associés :

Discussion sur l'insertion de grandes quantités de données dans MySQL (les changements quantitatifs entraînent des changements qualitatifs)_MySQL

Solution : Les données ne peuvent pas être insérées dans la base de données. Solution

php ne peut pas insérer de données dans MySQL

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!