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

                                                     

tutoriel mysqlla colonne présente l'insertion SQL par lots

recommandé (Gratuit) : tutoriel mysql

Pour certains systèmes avec de grandes quantités de données, en plus d'une faible efficacité des requêtes, les problèmes rencontrés par la base de données inclut Il faut juste beaucoup de temps pour que les données soient stockées dans la base de 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.

Une instruction SQL insère plusieurs éléments de données

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
    VALUES ('0', 'userid_0', 'content_0', 0);INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
    VALUES ('1', 'userid_1', 'content_1', 1);

rrree

• La principale raison de la grande efficacité de l'exécution SQL est fusion La quantité de journaux [le journal binaire 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 la transmission réseau.

Testez et comparez les données, respectivement, en important une seule donnée et en la convertissant en une instruction SQL pour l'importation.

Traitement de l'insertion dans une transaction

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
    VALUES ('0', 'userid_0', 'content_0', 0), ('1', 'userid_1', 'content_1', 1);

• L'utilisation de transactions peut améliorer l'efficacité de l'insertion des données, ce qui En effet, lorsqu'une opération d'insertion est effectuée, une transaction est créée en interne dans MySQL et l'opération de traitement d'insertion proprement dite est effectuée au sein de la transaction.
• Réduisez la consommation de création de transactions en utilisant des transactions. Toutes les insertions sont soumises avant d'être exécutées

Testez et comparez les données. Le test écrit est divisé en non applicable aux transactions et. à l'aide d'opérations de transaction

L'insertion ordonnée de données

L'insertion ordonnée de données consiste à insérer enregistrements dans l'ordre de la clé primaire Tri des commandes

START TRANSACTION;INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)     VALUES ('0', 'userid_0', 'content_0', 0);INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)     VALUES ('1', 'userid_1', 'content_1', 1);...COMMIT;

rrree

• Étant donné que la base de données doit conserver les données d'index lors de l'insertion, les enregistrements inutiles augmenteront le coût de maintenance de l'index.

Reportez-vous à 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 il y aura moins d'ajustement à l'index ; si l'enregistrement inséré Les enregistrements au milieu de l'index nécessitent que B+tree effectue le fractionnement et la fusion, ce qui consomme beaucoup 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.

Données de comparaison de tests, comparaison des performances des données aléatoires et des données séquentielles

Supprimez d'abord l'index et insérer Reconstruire l'index une fois terminé

Test de performance complet

• Fusionner les données + transaction L'amélioration des performances de cette méthode est évidente lorsque la quantité de données est faible. Lorsque la quantité de données est importante, les performances chutent fortement car la quantité de données dépasse la capacité d'innodb_buffer à ce moment-là, et chaque positionnement. L'index implique davantage d'opérations de lecture et d'écriture sur le disque. Les performances se dégradent rapidement.
• La méthode données fusionnées + transaction + ordonnée 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 de lecture et d'écriture fréquentes. le disque. L'opération peut être maintenue à un niveau élevé

Remarques

• Les instructions SQL ont un limite de longueur. La fusion des données dans le même SQL ne doit pas dépasser la limite de longueur SQL. Elle peut être modifiée via la configuration max_allowed_packet. La valeur par défaut est 1M et peut être modifiée en 8M pendant les tests.

• Les transactions doivent être contrôlées en taille. Les éléments trop volumineux peuvent affecter l'efficacité de l'exécution. MySQL a innodb_log_buffer_size éléments de configuration. Si cette valeur est dépassée, les données innodb seront vidées sur le disque. À ce moment, l'efficacité sera réduite. Une meilleure approche consiste donc à effectuer la validation de 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!