Comment puis-je optimiser une requête MySQL lente pour améliorer les performances et réduire les E/S disque ?

Optimisation de la requête MySQL pour réduire le temps d'exécution et les écritures sur disque

Votre requête MySQL rencontre des problèmes de performances, prend plusieurs minutes pour se terminer et écrire sur disque pendant l'exécution. Cela peut avoir un impact significatif sur la réactivité de votre page Web. Pour résoudre ce problème, nous explorerons une solution impliquant la modélisation des données et l'optimisation de l'index.

La requête lente que vous avez mentionnée est :

SELECT * 
FROM poster_prodcat, 
     poster_data, 
     poster_categories 
WHERE poster_data.apnumber = poster_prodcat.apnumber 
  AND poster_categories.apcatnum = poster_prodcat.apcatnum 
  AND poster_prodcat.apcatnum='623'  
ORDER BY aptitle ASC 
LIMIT 0, 32

Selon la sortie EXPLAIN, la requête effectue une requête complète analyse de table sur la table poster_prodcat, qui contient plus de 17 millions de lignes. Cela contribue au temps d'exécution excessif.

Pour optimiser les performances des requêtes, nous pouvons mettre en œuvre les modifications suivantes :

Normalisation et indexation :

1. Normaliser les données : Convertissez la table poster_prodcat en une structure normalisée avec deux tables : poster et catégorie. Créez une troisième table, poster_category, pour établir la relation entre les affiches et les catégories.
2. Créer des index : Créez des index sur les colonnes appropriées dans les tables poster,category et poster_category. Cela permettra à la base de données de localiser rapidement les données sans avoir à effectuer des analyses complètes de la table.

Voici un exemple du schéma mis à jour :

CREATE TABLE poster (
  poster_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL UNIQUE,
  PRIMARY KEY (poster_id)
) ENGINE=InnoDB;

CREATE TABLE category (
  cat_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL UNIQUE,
  PRIMARY KEY (cat_id)
) ENGINE=InnoDB;

CREATE TABLE poster_category (
  cat_id MEDIUMINT UNSIGNED NOT NULL,
  poster_id INT UNSIGNED NOT NULL,
  PRIMARY KEY (cat_id, poster_id),
  INDEX (poster_id) -- Clustered composite index
) ENGINE=InnoDB;

Une fois les données normalisées et indexées , la requête devrait s'exécuter beaucoup plus rapidement, réduisant considérablement le temps d'exécution.

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!