Comment puis-je optimiser une requête MySQL SELECT lente pour améliorer les performances et réduire l'utilisation de l'espace disque ?

Optimisation des requêtes de sélection MySQL pour les performances et la réduction de l'espace disque

Une requête MySQL lente qui prend plusieurs minutes à s'exécuter est un problème de performances courant. par les développeurs. Dans ce scénario, la requête récupère les données de trois tables pour afficher une page Web. Après enquête à l'aide de la commande EXPLAIN, il a été constaté que la requête écrivait des résultats intermédiaires sur le disque, ce qui entraînait un goulot d'étranglement important en termes de performances.

Pour optimiser la requête, une approche globale a été adoptée :

Analyse de la structure des tables

La requête utilisait trois tables : poster_data, poster_categories et poster_prodcat. Poster_data contenait des informations sur des affiches individuelles, tandis que poster_categories répertoriait toutes les catégories (par exemple, films, art). Poster_prodcat contenait les identifiants des affiches et les catégories associées. Le principal goulot d'étranglement a été identifié dans poster_prodcat, qui comptait plus de 17 millions de lignes et un grand nombre de résultats pour la catégorie spécifique filtrée (environ 400 000).

Optimisation de l'index

La sortie EXPLAIN a révélé que la requête manquait d'indexation optimale, ce qui entraînait un accès aux données inefficace et une exécution lente. Le principal problème était l’absence d’index sur la colonne poster_prodcat.apcatnum, utilisée pour le filtrage. Sans index, l'optimiseur MySQL a eu recours à une analyse complète de la table, ce qui a entraîné des E/S disque excessives et des temps d'exécution longs.

Réécriture des requêtes

Pour améliorer les performances problème, la requête a été réécrite en utilisant une approche plus efficace :

• Les trois tables ont été jointes à l'aide d'un INNER JOIN au lieu du SELECT moins efficace *.
• La clause WHERE a été simplifiée pour filtrer directement sur la catégorie souhaitée, évitant ainsi le besoin d'une sous-requête.
• La clause ORDER BY a été déplacée à la fin de la requête pour éviter un tri inutile d'ensembles de données volumineux.

Création de table temporaire

Pour atténuer l'espace disque problème d'utilisation, la requête optimisée a été encore modifiée pour créer une table temporaire qui stockait les résultats intermédiaires. Cette approche a permis à la requête de contourner l'écriture de données sur le disque, améliorant ainsi considérablement les performances.

Optimisations supplémentaires

Outre les optimisations principales, plusieurs mesures supplémentaires ont été mises en œuvre pour améliorer davantage performances des requêtes :

• Limitation des résultats à l'aide de la clause LIMIT : la requête d'origine n'avait pas de limite, renvoyant potentiellement un grand nombre de résultats qui n'étaient pas requis pour la page Web.
• Résultats de la mise en cache : la table temporaire a été créée avec le moteur MEMORY, qui a conservé les données en mémoire pour un accès plus rapide.

Conclusion

En traitant de l'indexation, de la structure des requêtes et de l'utilisation des tables temporaires, la requête d'origine a été optimisée pour améliorer les performances et réduire l'utilisation de l'espace disque. Cette optimisation s'est traduite par une réduction significative du temps d'exécution, rendant la génération de pages web beaucoup plus réactive.

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!