Comment pouvons-nous optimiser les jointures MySQL et réduire les E/S disque pour accélérer les requêtes lentes ?

Atténuer les requêtes lentes MySQL en optimisant les jointures et en réduisant l'écriture sur le disque

Dans une situation où une requête MySQL utilisant plusieurs jointures et recherches de table prenait trop de temps, affectant particulièrement le Web vitesses de chargement des pages, une enquête et une résolution ont été entreprises.

La requête en question impliquait trois tables : poster_data, poster_categories et poster_prodcat. La requête cherchait à afficher des informations spécifiques de ces tables sur la base d'une condition selon laquelle une valeur apcatnum particulière était égale à « 623 ».

Une analyse du schéma de base de données et du plan de requête (fourni sous forme d'instruction EXPLAIN) a révélé que la table poster_prodcat, qui contenait plus de 17 millions de lignes, a largement contribué au ralentissement. Plus précisément, la requête était écrite sur le disque lors de l'accès à un sous-ensemble d'environ 400 000 lignes de cette table en raison de ses conditions de jointure non optimisées.

Pour résoudre ce problème, une restructuration de la structure de la base de données et des index a été proposée. La structure de jointure complexe d'origine a été remplacée par une stratégie de jointure simplifiée et plus efficace. De plus, des index composites clusterisés ont été introduits sur les colonnes pertinentes pour améliorer les performances des requêtes.

L'extrait de code suivant présente la structure et la requête optimisées de la base de données :

-- Create the tables
DROP TABLE IF EXISTS poster;
CREATE TABLE poster (
  poster_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL UNIQUE
)
ENGINE = INNODB;


DROP TABLE IF EXISTS category;
CREATE TABLE category (
  cat_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL UNIQUE
)
ENGINE = INNODB;


DROP TABLE IF EXISTS poster_category;
CREATE TABLE poster_category (
  cat_id MEDIUMINT UNSIGNED NOT NULL,
  poster_id INT UNSIGNED NOT NULL,
  PRIMARY KEY (cat_id, poster_id) -- Clustered composite index
)
ENGINE = INNODB;


-- Populate the tables with data

-- Fetch data from the existing database to populate the new structure

SELECT
  p.*,
  c.*
FROM
  poster_category pc
INNER JOIN category c
  ON pc.cat_id = c.cat_id
INNER JOIN poster p
  ON pc.poster_id = p.poster_id
WHERE
  pc.cat_id = 623
ORDER BY
  p.name
LIMIT
  32;

L'intégration de ces optimisations a considérablement réduit le temps d'exécution de la requête. Les techniques de restructuration et d'indexation se sont combinées pour minimiser les opérations d'écriture sur disque et améliorer l'efficacité des requêtes, entraînant une amélioration substantielle de la vitesse de chargement de la page Web.

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!