Maison > Article > base de données > 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
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
2. 🎜>Nous retirons une ancienne donnée de la base de données, puis obtenons les nouvelles données souhaitées par calcul et traitement. Cependant, les problèmes suivants sont rencontrés lors de l'obtention de nouvelles donnéesNous 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 :
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;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;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;
Nous avons trois options pour enregistrer de nouvelles données dans la nouvelle table :
insère des données
" 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(); }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...
À 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!