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.
Vous devez vous connecter pour publier un commentaire.
Dépend de ce que vous allez et ce que la requête est.
Généralement, pour chaque ligne d'EXPLIQUER qui a un
Using where
, vous devez avoir l'aide d'un index (possible keys
etkeys
colonne). Ce sont vos filtres et comprendre, le cas et SUR. Avoir direUsing index
est encore mieux. Cela signifie qu'il y a un index de couverture, et MySQL peut récupérer les données directement à partir de l'index plutôt que de se rendre à la ligne dans la table de données.Les lignes où il n'y a pas de
Using where
, et c'est le retour d'un grand nombre de lignes doit être regardé. Ces sont de retour les valeurs de toutes les lignes dans la table. Je ne sais pas ce que votre question est, donc, je ne sais pas s'être alarmé ici. Essayez de filtrer les résultats à réduire la taille et d'en améliorer les performances.En général, vous devriez essayer d'éviter de voir
Using filesort
ouUsing temporary
, si ceux-ci sont seulement les mauvais si vous n'êtes pas les attendait.Filesort apparaît généralement avec la clause ORDER. En général, vous voulez que MySQL à utiliser un index de couverture (
Using index
), de sorte que les lignes sont renvoyées déjà dans l'ordre à partir du serveur. Si ils ne le sont pas, MySQL doit les commander par la suite, à l'aide de filesort.Using temporary
peut être mauvais lorsqu'il s'agit de tables dérivées parce qu'ils n'ont pas d'index. Il semble que vous avez explicitement créé une table temporaire avec des index, donc, ici, il n'est pas mauvais. Parfois, votre seul choix est d'utiliser une table dérivée, et doncUsing temporary
.