Comment optimiser et migrer 20 millions de données dans MySQL

Récemment, il existe une table de données contenant 20 millions d'enregistrements qui doit être optimisée et migrée. Les données de 2000 W sont embarrassantes pour MySQL en raison de la création raisonnable d'index La vitesse est encore assez rapide. Peu importe le degré d'optimisation effectué, la vitesse ne s'améliorera pas beaucoup. Cependant, ces données comportent de nombreux champs redondants et des messages d'erreur, ce qui est très gênant pour les statistiques et l'analyse. , je dois donc en créer une. Créer une nouvelle table, retirer les données de l'ancienne table une par une, l'optimiser et les remettre dans la nouvelle table

1. Effacer les données redondantes et optimiser la structure des champs Dans les

requête, nous créons donc de nouveaux champs pour cette partie des données et modifier raisonnablement la structure des champs pour les données ordinaires, telles que les cartes d'identité, qui sont varchar (18). Après avoir fusionné les données sans importance, nous avons un champ avec une structure de texte <.>Pour certaines données connexes, nous devons calculer, telles que le type de carte d'identité pour obtenir le sexe et la naissance précis, l'anniversaire, l'âge

La quantité de données est trop importante pour être obtenue en une seule fois (jeter 2000 W de données dans la mémoire est assez effrayant) ;

Nous pouvons l'obtenir par lots grâce à la syntaxe limite de MySQL. exemple, pour obtenir 50 000 à chaque fois, l'instruction SQL est la suivante :

1. Cette méthode peut résoudre le problème d'une trop grande quantité de données, mais elle le fera à mesure que le premier paramètre de limite devient de plus en plus grand , la vitesse de requête sera horriblement lente (l'exécution du SQL ci-dessus prendra 35 secondes), nous avons donc commencé à optimiser l'instruction SQL, et après optimisation, elle est devenue la suivante :

   2000W de données peuvent être divisé par dichotomie. Lorsque des données de 1000 W sont exécutées, les données sont inversées. Après optimisation, l'efficacité d'exécution SQL est considérablement améliorée, de 35 secondes à 9 secondes

   select * from table_name limit 15000000,50000;

   Copier après la connexion
   Cependant, c'est toujours très lent, le temps c'est la vie ; ... Heureusement, nous avons l'ID auto-incrémenté (la première loi de création de tables de données, il doit y avoir des champs auto-incrémentés), le SQl optimisé est le suivant :

   select * from table_name order by id desc limit 5000000,50000;

   Copier après la connexion
   Par souci d'intuition Pour démontrer, j'ai écrit deux SQL avec la même fonction, par rapport au premier, la limite du deuxième SQL entraînera une pire atteinte à l'index et l'efficacité sera également réduite. Le temps d'exécution du premier SQL est de 2. millisecondes. Le temps d'exécution des deux éléments est de 5 millisecondes (la valeur moyenne que j'ai prise). La vitesse de chaque requête de données est passée directement de 35 secondes à 2 millisecondes...
   Le la quantité de données est trop importante et les données ne peuvent pas être prédites. On estime que certaines données spéciales entraîneront l'échec de l'importation des données

   1. select * from table_name where id>15000000 and id<15050000； 2. select * from table_name where id>15000000 limit 50000;

   Copier après la connexion

   Nous avons trois options pour enregistrer de nouvelles données dans la nouvelle table :

2. Un Lorsqu'un

   insère des données

, je penserai certainement que cette solution ne fonctionnera pas au début, car à chaque insertion aura une opération d’E/S de base de données. Cependant, l'un des avantages de cette solution est qu'elle peut détecter les données problématiques à temps et poursuivre l'exécution après modification ; l'utilisation de "Bind
1. Variable

   " dans Oracle peut améliorer les performances, et MySQL fournit également la fonction de variable "Bind". Alors sans changer la logique, essayez d’optimiser la vitesse de stockage des données. Le code est le suivant :

   L'effet final n'est pas très bon. La "variable de liaison" de MySQL n'apporte pas d'amélioration significative de la vitesse, mais elle peut efficacement empêcher l'injection SQL ; 🎜 >

   Insérez 50 000 données à la fois ; C'est la solution que j'ai finalement choisie. Premièrement, elle peut détecter les données problématiques à temps, et deuxièmement, les données importées sont très stables. Tout comme la prise en charge de la reprise du point d'arrêt, vous pouvez voir l'effet à chaque étape. Lors de l'exécution du script, vous pouvez également commencer à écrire la logique d'analyse simultanément

   public function actionTest(array $data)
   {
       $mysqli = new mysqli("192.168.1.106", "username", "password", "test");
       $sql = "insert into table_name(name,identity) values (?,?)";
   
       $stmt = $connection->prepare($sql);
       $name = "";
       $identity = "";
       //使用绑定变量
       $stmt->bind_param("si", $name, $identity);
       foreach($data as $val)
       {
           $name = $val[name];
           $identity = $val[card_id];
           //执行
           $stmt->execute();
       }
       $stmt->close();
   }

   Copier après la connexion
   est assemblée dans un fichier SQL et finalement importée de manière uniforme
 ;

Il est également bon d'assembler un gros fichier SQL et enfin de l'importer via l'outil fourni avec MySQL. Mais s'il y a un problème avec l'une des instructions SQL, vous devrez peut-être réexécuter le script. Parce qu'il est très pénible de modifier un symbole dans un fichier texte 9G...

Résumé

À travers chaque Ceci. Une sorte d'optimisation a finalement réduit le temps d'exécution du script à moins de 20 minutes. Après l'optimisation, la qualité des données a été hautement garantie. La prochaine fois, nous essaierons d'optimiser et de migrer 200 millions de données...

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!