Comparaison des performances des méthodes d'insertion MySQL sous de gros volumes de données

Qu'il s'agisse de traitement quotidien de données professionnelles ou d'importation et d'exportation de bases de données, vous pouvez rencontrer des insertions qui nécessitent le traitement de grandes quantités de données. La méthode d'insertion et le moteur de base de données affecteront 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 nécessaire 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)

• Analyser la requête : (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 à chaque fois que nous insérons un élément, alors nous devons exécuter toutes les étapes sauf la connexion et la fermeture N fois. Cela prend beaucoup de temps.

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 sont les suivantes.

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 des verrous 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

• Insertion unique avec index Temps total pris : 242 s Mémoire maximale : 246 Ko

• Temps total nécessaire pour l'insertion par lots sans index : 10 s Mémoire maximale : 8643 Ko

• Insertion par lots avec index Temps total pris : 16 s Mémoire maximale : 8643 Ko

• Temps total nécessaire pour l'insertion d'une transaction sans index : 78 s Mémoire maximale : 246 Ko

• Insertion avec transaction indexée Temps total passé : 82s Mémoire maximale : 246 Ko

• Chargement sans index Temps total consacré à l'insertion des données : 12s Mémoire maximale : 246 Ko

• Charger avec index Temps total d'insertion des données : 11 s Mémoire maximale : 246 Ko

Moteur MyIASM

MyISAM est le moteur de stockage par défaut de 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

• Insertion unique avec index Temps total nécessaire : 86 s Mémoire maximale : 246 Ko

• Temps total nécessaire pour l'insertion par lots sans index : 3 s Mémoire maximale : 8643 Ko

• Insertion par lots avec index Temps total passé : 7s Mémoire maximale : 8643 Ko

• Chargement sans index Temps total consacré à l'insertion des données : 6s Mémoire maximale : 246 Ko

• Charger avec index Temps total d'insertion des données : 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 la vitesse de ces méthodes d’insertion. 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";

?>

Ce qui précède est l'analyse des performances et la comparaison de diverses méthodes pour insérer de grandes quantités de données dans MySQL. Pour plus de contenu connexe, veuillez faire attention au site Web PHP chinois. (www.php.cn) !