Comment optimiser MySQL requêtes sur la base d'EXPLIQUER le plan d'

À la recherche lors d'une requête EXPLAIN plan, comment détermine-t-on, où des optimisations peuvent être mieux fait?

J'apprécie que l'une des premières choses à vérifier est si bonne que les index sont utilisés, mais au-delà de que je suis un peu perplexe. Par essais et erreurs dans le passé, j'ai parfois trouvé que l'ordre dans lequel les jointures sont effectuées peuvent être une bonne source d'amélioration, mais comment peut-on déterminer qu'en regardant le plan d'exécution?

Alors que j'aimerais beaucoup avoir une bonne compréhension générale de la manière d'optimiser les requêtes (a suggéré de lire beaucoup apprécié!), J'ai aussi conscience qu'il est souvent plus facile de discuter de cas concrets que de parler dans l'abstrait. Depuis que je suis actuellement en train de cogner ma tête contre le mur avec celui-ci, vos pensées serait bien apprécié:

id select_type type de table possible_keys clé key_len ref lignes Supplémentaires 
1 SIMPLE S const PRIMAIRE,l,p,f4 PRIMAIRE 2 const 1 Utilisation temporaire 
1 SIMPLE Q ref PRIMAIRE,S S 2 const 204 à l'Aide de l'indice de 
1 SIMPLE V ref PRIMAIRE,n,Q Q 5 const,db.Q.QID 6 à l'Aide de où; à l'Aide de l'index; Distinct 
1 SIMPLE R1 réf PRIMAIRE,L L 154 const,db.V.VID 447 à l'Aide de l'index; Distinct 
1 SIMPLE W eq_ref PRIMARY,w PRIMAIRE 5 const,db.R.DÉBARRASSER,const 1 à l'Aide de où; Distinct 
1 SIMPLE R2 eq_ref PRIMARY,L PRIMAIRE 156 const,db.W.DÉBARRASSER,const 1 à l'Aide de où; Distinct 

Suis-je correct dans l'interprétation de la dernière rangée du plan d'exécution comme suit:

  • que c'est totalement appariés sur sa clé primaire, une seule ligne de R2 besoin d'être récupérée par ligne de sortie;
  • toutefois, ces lignes de sortie sont ensuite filtrés en fonction de certains critères qui s'applique à R2?

Si donc, mon problème réside dans le filtrage de ce qui se produit lors de cette dernière étape. Si la condition entraîne pas de filtrage (par exemple WHERE `Col_1_to_3` IN (1,2,3)), la requête s'exécute très rapidement (~50ms); toutefois, si la condition limite les lignes sélectionnées (WHERE `Col_1_to_3` IN (1,2)), la requête prend beaucoup plus de temps (~5s). Si la restriction est pour un seul match (WHERE `Col_1_to_3` IN (1)), l'optimiseur suggère un tout à fait différent de l'exécution du plan (qui joue un peu mieux que le 5s, mais encore beaucoup de pire que de 50ms). Il ne me semble pas que il y a un meilleur indice qui peuvent être utilisés sur la table (étant donné qu'il est déjà entièrement à l'aide de la clé primaire de retourner une ligne par résultat?).

Comment doit-on interpréter cette information? Ai-je raison de supposer que, parce qu'une telle sortie de filtrage est de prendre place sur la table finale à être rejoint, un effort considérable est gaspillée rapport à la jointure de la table plus tôt et le filtrage de ces lignes plus tôt? Si oui, comment détermine-t-on quand dans le plan d'exécution R2 doit être joint?

Alors que j'ai résisté, y compris la requête & schéma dans son intégralité ici (comme je l'aurais vraiment susceptibles de savoir quoi chercher, et non pas simplement être informé de la réponse), je comprends que c'est nécessaire pour faire avancer la discussion:

SELECT DISTINCT
    `Q`.`QID`
FROM
    `S`
    NATURAL JOIN `Q`
    NATURAL JOIN `V`
    NATURAL JOIN `R` AS `R1`
    NATURAL JOIN `W`

    JOIN `R` AS `R2` ON (
            `R2`.`SID` = `S`.`SID`
        AND `R2`.`RID` = `R1`.`RID`
        AND `R2`.`VID` = `S`.`V_id`
        AND `R2`.`Col_1_to_3` IN (1,2) -- this is where performance suffers!
    )

WHERE
    AND `S`.`SID` = @x
    AND `W`.`WID` = @y
;

La définition de la table R est:

CREATE TABLE `R` (
  `SID` smallint(6) unsigned NOT NULL,
  `RID` smallint(6) unsigned NOT NULL,
  `VID` varchar(50) NOT NULL DEFAULT '',
  `Col_1_to_3` smallint(1) DEFAULT NULL,
  `T` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`SID`,`RID`,`VID`),
  KEY `L` (`SID`,`VID`,`Col_1_to_3`),
  CONSTRAINT `R_f1` FOREIGN KEY (`SID`) REFERENCES `S` (`SID`),
  CONSTRAINT `R_f2` FOREIGN KEY (`SID`, `VID`) REFERENCES `V` (`SID`, `VID`),
  CONSTRAINT `R_f3` FOREIGN KEY (`SID`, `VID`, `Col_1_to_3`) REFERENCES `L` (`SID`, `VID`, `LID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
  • Avez-vous l'esprit en montrant la requête trop?
  • Je n'ai pas l'esprit, mais que voulez-vous être à la recherche d'? J'ai l'impression que je suis susceptible d'en apprendre plus si je sais ce que c'est qui vous regarde...
  • Vous vous référez à col_1_to_3, mais je ne vois pas une telle colonne dans l'EXPLIQUER résultat. Si vous pouvez formuler la question, de sorte que c'est seulement à propos de l'expliquer, en d'autres termes supprimer les paragraphes qui parlent de la requête, alors nous n'avons pas besoin de la requête et la réponse est oui. Généralement, nous avons besoin de la requête, de schéma et de l'expliquer, sinon, nous sommes deviner.
  • Hmm.. R2.col_1_to_3 n'est en effet pas en EXPLIQUER la raison; où espérez-vous voir apparaître? Si possible, j'aimerais vraiment savoir ce que vous souhaitez rechercher dans la requête et le schéma... sinon je ne vais pas être plus informé de la prochaine fois que je suis dans cette position!
  • requête et le schéma ci-dessus, comme demandé.
  • C'est que pour expliquer cette requête?
  • Yep.

InformationsquelleAutor eggyal | 2012-04-13