Explication détaillée de l'optimisation des performances d'insertion SQL par lots MySQL

Pour certains systèmes contenant de grandes quantités de données, la base de données est confrontée non seulement à une faible efficacité des requêtes, mais également à une longue durée de stockage des données. En particulier pour les systèmes de reporting, le temps consacré à l'importation des données peut durer plusieurs heures, voire plus de dix heures par jour. Il est donc logique d’optimiser les performances d’insertion des bases de données.

Recommandé : "Tutoriel mysql"

Après quelques tests de performances sur MySQL InnoDB, j'ai trouvé quelques méthodes qui peuvent améliorer l'efficacité de l'insertion pour votre référence.

1. Une instruction SQL pour insérer plusieurs éléments de données

Les instructions d'insertion couramment utilisées telles que :

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) <br/>    VALUES (&#39;0&#39;, &#39;userid_0&#39;, &#39;content_0&#39;, 0);<br/>INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) <br/>    VALUES (&#39;1&#39;, &#39;userid_1&#39;, &#39;content_1&#39;, 1);<br/>

sont modifiées en :

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) <br/>    VALUES (&#39;0&#39;, &#39;userid_0&#39;, &#39;content_0&#39;, 0), (&#39;1&#39;, &#39;userid_1&#39;, &#39;content_1&#39;, 1);<br/>

L'opération d'insertion modifiée peut améliorer l'efficacité de l'insertion du programme. La principale raison pour laquelle l'efficacité de l'exécution du deuxième SQL est élevée ici est que la quantité de journaux après la fusion (le journal binlog de MySQL et les journaux de transactions d'innodb) est réduite, ce qui réduit le volume de données et la fréquence de vidage des journaux, améliorant ainsi l'efficacité. En fusionnant les instructions SQL, cela peut également réduire le nombre d'instructions SQL analysées et réduire les E/S de transmission réseau.

Voici quelques données de comparaison de tests, qui consistent à importer une seule donnée et à la convertir en une instruction SQL pour l'importation, et à tester respectivement 100, 1 000 et 10 000 enregistrements de données.

2. Effectuez le traitement d'insertion dans la transaction.

Modifiez l'insertion en :

START TRANSACTION;
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) <br/>    VALUES (&#39;0&#39;, &#39;userid_0&#39;, &#39;content_0&#39;, 0);<br/>INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) <br/>    VALUES (&#39;1&#39;, &#39;userid_1&#39;, &#39;content_1&#39;, 1);<br/>...
COMMIT;

L'utilisation de transactions peut améliorer l'efficacité de l'insertion de données, car lorsqu'une opération INSERT est effectuée, une transaction sera établie en interne dans MySQL, et uniquement dans la transaction Effectuer les opérations de traitement d'insertion réelles. En utilisant des transactions, vous pouvez réduire le coût de création de transactions. Toutes les insertions sont exécutées avant la validation.

Une comparaison de test est également fournie ici, qui est le cas de la non-utilisation des transactions et de l'utilisation des transactions lorsque le nombre d'enregistrements est de 100, 1000 et 10000.

3. Les données sont insérées dans l'ordre.

L'insertion ordonnée des données signifie que les enregistrements insérés sont classés dans l'ordre sur la clé primaire. Par exemple, datetime est la clé primaire de l'enregistrement :

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) <br/>    VALUES (&#39;1&#39;, &#39;userid_1&#39;, &#39;content_1&#39;, 1);<br/>INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) <br/>    VALUES (&#39;0&#39;, &#39;userid_0&#39;, &#39;content_0&#39;, 0);<br/>INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) <br/>    VALUES (&#39;2&#39;, &#39;userid_2&#39;, &#39;content_2&#39;,2);<br/>

est modifié en :

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) <br/>    VALUES (&#39;0&#39;, &#39;userid_0&#39;, &#39;content_0&#39;, 0);<br/>INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) <br/>    VALUES (&#39;1&#39;, &#39;userid_1&#39;, &#39;content_1&#39;, 1);<br/>INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
    VALUES ('2', 'userid_2', 'content_2',2);

Étant donné que la base de données doit conserver les données d'index lors de l'insertion, des enregistrements désordonnés augmenteront le coût de maintenance de l'index. On peut se référer à l'index B+tree utilisé par InnoDB. Si chaque enregistrement inséré est à la fin de l'index, l'efficacité du positionnement de l'index est très élevée, et l'ajustement de l'index est faible si l'enregistrement inséré est au milieu de l'index. index, B+tree est requis. Les processus tels que le fractionnement et la fusion consommeront plus de ressources informatiques et l'efficacité du positionnement de l'index des enregistrements insérés diminuera lorsque la quantité de données est importante, il y aura des opérations de disque fréquentes.

Ce qui suit fournit une comparaison des performances des données aléatoires et des données séquentielles, qui sont enregistrées respectivement sous la forme 100, 1 000, 10 000, 100 000 et 1 million.

D'après les résultats des tests, les performances de cette méthode d'optimisation ont été améliorées, mais l'amélioration n'est pas très évidente.

4. Test de performance complet

Voici un test qui utilise les trois méthodes ci-dessus en même temps pour optimiser l'efficacité de INSERT.

Comme le montrent les résultats des tests, l'amélioration des performances de la méthode de fusion données + transactions est évidente lorsque la quantité de données est faible. est important (1 000 dix mille ou plus), les performances chuteront fortement, car la quantité de données dépasse la capacité d'innodb_buffer à ce moment-là. Chaque positionnement d'index implique plus d'opérations de lecture et d'écriture sur le disque et les performances chutent rapidement. La méthode d'utilisation de données fusionnées + transactions + données ordonnées fonctionne toujours bien lorsque le volume de données atteint des dizaines de millions. Lorsque le volume de données est important, le positionnement de l'index des données ordonnées est plus pratique et ne nécessite pas d'opérations de lecture et d'écriture fréquentes sur le disque. Des performances élevées peuvent donc être maintenues.

Remarques :

1. Les instructions SQL ont des limites de longueur lors de la fusion de données dans le même SQL, la limite de longueur SQL ne doit pas être dépassée. la configuration max_allowed_packet , la valeur par défaut est 1M et a été modifiée à 8M lors des tests.

2. La taille des transactions doit être contrôlée. Si une transaction est trop importante, cela peut affecter l'efficacité de son exécution. MySQL a l'élément de configuration innodb_log_buffer_size. Si cette valeur est dépassée, les données innodb seront vidées sur le disque. À ce stade, l'efficacité diminuera. Une meilleure approche consiste donc à valider la transaction avant que les données n'atteignent cette valeur.

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!