Traiter plusieurs millions de données dans une base très rapidement
Nous faisons face en ce moment à un problème récurrent dans de nombreux projets : traiter un nombre très important de données dans une BDD.
Bien sur si les requêtes sont importantes et que le serveur met jusqu’à plusieurs secondes à répondre pour chaque requête, le tout multiplié par le nombre d’utilisateur…je vous laisse imaginer le nombre de crash à la minute que va subir la pauvre machine !
Dans notre cas nous avons un fichier texte contenant…3 597 600 codes ! Le tout doit être stocké dans une table et l’on doit pouvoir rechercher un code particulier dans la liste.
On peut voir la vitesse d’exécution multipliée par 10000 entre la phase sans optimisation puis avec optimisation !
Une des première optimisation à apporter est de trier la source contenant nos codes avant de l’importer dans la base (par ordre alphabétique ici) à l’aide d’un invité de commande :
C’est ici qu’intervient la majeure partie de l’optimisation ! Au moment de créer notre table ‘codes’ dans notre BDD, nous allons lui associer un index ! :
CREATE TABLES codes (code CHAR(9) NOT NULL, INDEX ( code )) ENGINE = MYISAM; |
Un index est un élément lié à chaque entrée de notre table qui va permettre d’accéléré la lecture et l’analyse des éléments de la table. Bien sur, plus le nombre d’éléments est important, plus le gain est intéressant et non négligeable. On a aussi remarqué que la puissance du serveur (j’entend processeur) joue sur la vitesse d’exécution de la requête (au delà de la vitesse du disque dur et de la mémoire vive comme on pourrait le croire).
A noter que l’on force ici l’utilisation du moteur MySQL MYISAM avec ENGINE = MYISAM; car il est plus rapide que le moteur par défaut pour ce type d’opération.
Puis, on rempli la table ‘codes‘ à l’aide du fichier texte contenant les codes. L’opération peut prendre plusieurs minutes…voir crasher complètement si l’on a pas fait l’optimisation comme il faut !
LOAD DATA INFILE 'c:/sample_codes.txt' INTO TABLE codes_index |
Voilà comment on traiter facilement plusieurs millions de données en quelques millisecondes !
