Comment optimiser les performances ? Explication détaillée d'exemples de MySQL implémentant l'insertion par lots pour optimiser les performances

Cet article présente principalement le tutoriel de MySQL pour implémenter l'insertion par lots pour optimiser les performances. Le temps d'exécution est donné dans l'article pour indiquer la comparaison après optimisation des performances. Les amis dans le besoin peuvent s'y référer

Pour certains. données avec de grandes quantités de données, Dans les grands systèmes, 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.
Après quelques tests de performances sur MySQL innodb, j'ai trouvé quelques méthodes qui peuvent améliorer l'efficacité des insertions pour votre référence.

1. Insérez plusieurs éléments de données avec une seule instruction SQL.
Les instructions d'insertion couramment utilisées telles que

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);

sont modifiées en :

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

L'opération d'insertion modifiée peut améliorer l'efficacité d'insertion de le 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. Effectuer le traitement d'insertion dans la transaction.
Modifiez l'insertion en :

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;

3. Insérez les donné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`) 
  VALUES ('1', 'userid_1', 'content_1', 1);
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
  VALUES ('0', 'userid_0', 'content_0', 0);
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
  VALUES ('2', 'userid_2', 'content_2',2);

modifiée en. :

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);
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.
La comparaison des performances des données aléatoires et des données séquentielles est fournie ci-dessous, 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.

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

Il ressort des résultats des tests que l'amélioration des performances de la méthode de fusion données + transactions est évidente lorsque la quantité de données est faible. La quantité de données est importante, l'amélioration des performances est évidente (plus de 10 millions), 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 de lecture et d'écriture sur le disque. opérations, 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 via la configuration max_allowed_packet. . La valeur par défaut est 1 M, modifiée à 8 M 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!