Cet article présente les stratégies de MySQL pour améliorer l'efficacité des opérations de chargement de données. Souvent, vous souhaitez optimiser les requêtes SELECT, car ce sont les requêtes les plus couramment utilisées, et déterminer comment les optimiser n'est pas toujours simple. Le chargement de données dans une base de données est relativement simple. Souvent, vous souhaitez optimiser les requêtes SELECT, car ce sont les requêtes les plus couramment utilisées, et déterminer comment les optimiser n'est pas toujours simple. Le chargement de données dans une base de données est relativement simple. Il existe cependant des stratégies qui peuvent être utilisées pour améliorer l’efficacité des opérations de chargement des données, dont les principes de base sont les suivants :
Le chargement groupé est plus rapide que le chargement sur une seule ligne, car le cache d'index n'a pas besoin d'être vidé après le chargement de chaque enregistrement ; il peut être vidé après le chargement du lot d'enregistrements ;
Le chargement d'une table sans index est plus rapide que le chargement après un index. S'il existe des index, non seulement des enregistrements doivent être ajoutés aux fichiers de données, mais chaque index doit également être modifié pour refléter l'ajout des nouveaux enregistrements.
Les instructions SQL plus courtes sont plus rapides que les instructions SQL plus longues car elles impliquent moins d'analyses côté serveur et parce qu'elles sont plus rapides à envoyer sur le réseau du client au serveur. Certains de ces facteurs peuvent sembler insignifiants (surtout le dernier), mais si vous chargez une grande quantité de données, même de petits facteurs peuvent faire une grande différence dans les résultats. Nous pouvons utiliser les principes généraux ci-dessus pour tirer plusieurs conclusions pratiques sur la manière de charger les données le plus rapidement :
LOAD DATA (sous toutes ses formes) est plus efficace que INSERT car il charge les lignes par lots. Les actualisations d'index sont moins nombreuses et le serveur n'a qu'à analyser et interpréter une seule instruction au lieu de plusieurs.
LOAD DATA est plus efficace que LOAD DATA LOCAL. Avec LOAD DATA, le fichier doit être situé sur le serveur et doit disposer des autorisations FILE, mais le serveur peut lire le fichier directement à partir du disque. Avec LOAD DATA LOCAL, le client lit le fichier et l'envoie via le réseau au serveur, ce qui est lent.
Si vous devez utiliser INSERT, vous devez utiliser un formulaire qui permet de spécifier plusieurs lignes dans une seule instruction, tel que :
Plus vous pouvez spécifier de lignes dans une instruction, mieux c'est. Cela réduit le nombre d'instructions requises et réduit le nombre d'actualisations d'index. Si vous utilisez mysqldump pour générer un fichier de sauvegarde de base de données, vous devez utiliser l'option --extended-insert afin que le fichier de vidage contienne des instructions INSERT multilignes. Vous pouvez également utiliser --opt (optimisation), qui active l'option --extended-insert. À l'inverse, l'utilisation de l'option --complete-insert dans mysqldump doit être évitée ; cette option entraîne que les instructions INSERT soient sur une seule ligne, prennent plus de temps à s'exécuter et nécessitent plus d'analyses que les instructions générées sans l'option --complete-insert.
Utilisez des protocoles client/serveur compressés pour réduire le trafic de données réseau. Pour la plupart des clients MySQL, cela peut être spécifié avec l'option de ligne de commande --compress. Il n’est généralement utilisé que sur des réseaux plus lents car la compression nécessite beaucoup de temps processeur.
Laissez MySQL insérer des valeurs par défaut ; ne spécifiez pas de colonnes dans l'instruction INSERT auxquelles des valeurs par défaut seront attribuées de quelque manière que ce soit. En moyenne, cela se traduit par des instructions plus courtes et réduit le nombre de caractères envoyés sur le réseau au serveur. De plus, les instructions contenant moins de valeurs nécessitent moins d'analyse et de transformation de la part du serveur.
Si la table est indexée, vous pouvez utiliser des insertions en masse (instructions LOAD DATA ou INSERT à plusieurs lignes) pour réduire la surcharge de l'index. Cela minimise l'impact des mises à jour de l'index, car celui-ci ne doit être actualisé que lorsque toutes les lignes ont été traitées, plutôt qu'après chaque ligne.
Si vous devez charger une grande quantité de données dans une nouvelle table, vous devez créer la table et la charger lorsqu'elle n'est pas indexée, puis créer l'index après avoir chargé les données. C'est plus rapide. Créer l'index une fois (plutôt que de le modifier une fois par ligne) est plus rapide.
Si vous supprimez ou désactivez un index avant le chargement, recréer ou activer l'index après le chargement des données peut accélérer le chargement. Si vous souhaitez utiliser une stratégie de suppression ou de désactivation pour le chargement des données, assurez-vous de faire quelques expériences pour voir si cela en vaut la peine (si vous chargez une petite quantité de données dans une grande table, la reconstruction et l'indexation peuvent prendre plus de temps que le chargement. les données) ).
DROP INDEX et CREATE INDEX peuvent être utilisés pour supprimer et reconstruire des index. Une alternative consiste à désactiver et activer les index à l'aide de myisamchk ou isamchk. Cela nécessite un compte sur l'hôte du serveur MySQL avec un accès en écriture aux fichiers de table. Pour désactiver les index de table, entrez le répertoire de base de données correspondant et exécutez l'une des commandes suivantes :
Utilisez myisamchk pour les tables MyISAM avec des fichiers d'index avec une extension .MYI et isamchk pour les tables ISAM avec des fichiers d'index avec une extension .ISM. Après avoir chargé les données dans la table, activez l'index comme suit :
Si vous décidez d'utiliser la désactivation et l'activation de l'index, vous devez utiliser le protocole de verrouillage de réparation de table décrit au chapitre 13 pour empêcher le serveur de modifier les verrous en même temps (bien que la table ne soit pas réparée à ce moment-là, elle est modifiée comme une table processus de réparation, vous devez donc utiliser le même protocole de verrouillage).
Les principes de chargement des données décrits ci-dessus s'appliquent également aux requêtes fixes liées aux clients qui doivent effectuer différentes opérations. Par exemple, vous souhaitez généralement éviter d'exécuter de longues requêtes SELECT sur des tables fréquemment mises à jour. Les requêtes SELECT de longue durée peuvent créer de nombreux conflits et réduire les performances du rédacteur. Une solution possible consiste à stocker d'abord les enregistrements dans une table temporaire, puis à ajouter périodiquement les enregistrements à la table principale si les écritures sont principalement des opérations INSERT. Cette approche n’est pas réalisable si un accès immédiat aux nouveaux dossiers est requis. Mais cette méthode peut être utilisée à condition de ne pas y accéder pendant une courte période. L'utilisation de tables temporaires présente deux avantages. Premièrement, cela réduit les conflits avec l'instruction de requête SELECT sur la table principale et, par conséquent, s'exécute plus rapidement. Deuxièmement, le temps total de chargement des enregistrements de la table temporaire vers la table principale est inférieur au temps total de chargement des enregistrements séparément. Le cache d'index correspondant ne doit être actualisé qu'à la fin de chaque chargement par lots, plutôt qu'après chaque ligne ; charger. Une application de cette stratégie consiste à accéder à la base de données MySQL à partir de la page Web du serveur Web. Dans ce scénario, il se peut qu'il n'y ait pas de niveau d'autorité supérieur garantissant l'entrée immédiate de l'enregistrement dans la table principale.
Si les données ne correspondent pas exactement au type d'enregistrement unique qui serait inséré en cas d'arrêt du système, une autre stratégie pour réduire les actualisations d'index consiste à utiliser l'option de création de table DELAYED_KEY_WRITE pour les tables MyISAM (ce qui peut être possible si MySQL est utilisé pour certains travaux de saisie de données). Cela arrivera). Cette option entraîne l'actualisation du cache d'index seulement occasionnellement, plutôt qu'après chaque insertion.
Si vous souhaitez profiter de l'actualisation différée de l'index à l'échelle du serveur, démarrez simplement mysqld avec l'option --delayed-key-write. Dans ce scénario, les écritures du bloc d'index sont retardées jusqu'à ce que le bloc doive être vidé pour faire de la place à d'autres valeurs d'index, jusqu'à ce qu'une commande flush-tables soit exécutée ou jusqu'à ce que la table d'index soit fermée.
-