MySQL 20 millions d'optimisation et de migration de données

Récemment, il existe une table de données contenant 20 millions d'enregistrements qui doit être optimisée et migrée. 20 millions de données sont très embarrassantes pour MySQL, car la vitesse de création d'index est toujours très rapide, et peu importe le degré d'optimisation effectué, la vitesse ne peut pas être beaucoup améliorée. Cependant, ces données comportent un grand nombre de champs redondants et d'informations sur les erreurs, ce qui est extrêmement gênant pour les statistiques et l'analyse. Je dois donc 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 table ; structure des champs

Dans les données 2000W, nous pouvons prédire les champs qui peuvent être utilisés comme conditions de requête. Par conséquent, créez un nouveau champ séparément pour cette partie des données et modifiez raisonnablement la structure du champ pour les données normales, telles que la carte d'identité est varchar(18). Pour les données sans importance, nous les fusionnons et il existe un champ avec une structure de texte.

Nous devons calculer certaines données connexes. Les données courantes telles que le type de carte d'identité peuvent obtenir un sexe, un lieu de naissance, une date de naissance et un âge précis.

2. Migration des données

Nous retirons une ancienne donnée de la base de données, puis obtenons les nouvelles données souhaitées par calcul et traitement, et enfin insérons les nouvelles données. dans le nouveau tableau. Cependant, les problèmes suivants ont été rencontrés lors de l'obtention de nouvelles données.

1. La quantité de données est trop importante pour être obtenue en une seule fois (2000 W de données jetées dans la mémoire sont assez effrayantes

   Nous pouvons l'obtenir par lots via MySQL) ; syntaxe limite. Par exemple, chaque fois que vous obtenez 50 000, l'instruction SQL est la suivante :

   select * from table_name limit 15000000,50000;

   Cette méthode peut résoudre le problème d'un volume de données trop important, mais à mesure que le premier paramètre de limite devient de plus en plus grand, le la vitesse des requêtes sera plus lente. C'est effrayant (le SQL ci-dessus prendra 35 secondes à s'exécuter). Le temps c'est la vie, nous avons donc commencé à optimiser l'instruction SQL. Après optimisation, elle est devenue la suivante :

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

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

   Cependant, elle est toujours très lente Le temps, c'est la vie... Heureusement, nous avons un ID auto-augmentant (le premier) ; loi de création d'une table de données, il faut qu'il y ait des champs d'auto-incrémentation), le SQL optimisé est le suivant :

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

   Pour une démonstration intuitive, j'ai écrit deux SQL avec la même fonction. Par rapport à la première, la deuxième limite entraînera une aggravation de l'accès à l'index SQL et une diminution de l'efficacité. Le temps d'exécution du premier SQL est de 2 millisecondes et le temps d'exécution du deuxième SQL est de 5 millisecondes (la valeur moyenne que j'ai prise). La vitesse de requête de chaque donnée est directement passée de 35 secondes à 2 millisecondes...

2. La quantité de données est trop importante et les données ne peuvent pas être estimées. Certaines données spéciales provoqueront des données. l'importation échoue ;

   Nous avons trois options pour stocker de nouvelles données dans une nouvelle table, comme suit :

1. Insérer les données une par une ;

   Vous y penserez certainement au début. Cette solution ne fonctionnera certainement pas, car chaque insertion provoquera une opération d'E/S dans la 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 « lier des variables » dans Oracle peut améliorer les performances, et MySQL fournit également la fonction « lier des variables ». Alors sans changer la logique, essayez d’optimiser la vitesse de stockage des données. Le code est le suivant :

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

   L'effet final n'est pas très bon. La "variable de liaison" de MySQL n'apporte pas d'amélioration évidente 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 une logique d'analyse simultanément ;

2. est assemblé dans un fichier SQL, et enfin importé uniformément

est assemblé dans un grand SQL ; fichier, et enfin via MySQL L'outil d'importation intégré est également excellent. 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...

3. Résumé

Grâce à diverses optimisations, le temps d'exécution du script a finalement été réduit à moins de 20 minutes. Après l'optimisation, la qualité des données est 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 de l'optimisation et de la migration de 20 millions de données MySQL. , plus Pour le contenu connexe, veuillez faire attention au site Web PHP chinois (www.php.cn) !