Sous-requêtes et jointures

Question

J'ai remanié une section lente d'une application que nous avons héritée d'une autre entreprise pour utiliser une jointure interne au lieu d'une sous-requête :

WHERE id IN (SELECT id FROM ...)

La requête remaniée est environ 100 fois plus rapide. (~50 secondes à ~0,3) Je m'attendais à une amélioration, mais quelqu'un peut-il expliquer pourquoi elle est si radicale ? Les colonnes utilisées dans la clause where étaient toutes indexées. Est-ce que SQL exécute la requête dans la clause where une fois par ligne ou quelque chose comme ça ?

Mise à jour - Expliquez les résultats :

La différence se situe dans la deuxième partie de la requête "where id in ()" -

2   DEPENDENT SUBQUERY  submission_tags ref st_tag_id   st_tag_id   4   const   2966    Using where

contre 1 ligne indexée avec la jointure :

    SIMPLE  s   eq_ref  PRIMARY PRIMARY 4   newsladder_production.st.submission_id  1   Using index

Answer 1

Une "sous-requête corrélée" (c'est-à-dire une sous-requête dont la condition "where" dépend des valeurs obtenues à partir des lignes de la requête contenante) sera exécutée une fois pour chaque ligne. Une sous-requête non corrélée (une sous-requête dans laquelle la condition where est indépendante de la requête contenue) s'exécutera une fois au début. Le moteur SQL fait cette distinction automatiquement.

Mais, ouais, explain-plan te donnera les détails cochons.

Answer 2

Vous exécutez la sous-requête une fois pour chaque ligne alors que la jointure se fait sur les index.

Answer 3

Voici un exemple de la façon dont les sous-requêtes sont évaluées dans MySQL 6.0 .

Le nouvel optimiseur convertira ce type de sous-requêtes en jointures.

Answer 4

Lancez le plan d'explication sur chaque version, il vous dira pourquoi.

Answer 5

Avant que les requêtes ne soient exécutées sur l'ensemble de données, elles sont soumises à un optimiseur de requêtes, qui tente d'organiser la requête de manière à supprimer le plus rapidement possible un maximum de tuples (lignes) de l'ensemble de résultats. Souvent, lorsque vous utilisez des sous-requêtes (en particulier les mauvaises), les tuples ne peuvent pas être retirés du jeu de résultats avant que la requête externe ne commence à s'exécuter.

Sans voir la requête, il est difficile de dire ce qui était si mauvais dans l'original, mais je pense que c'était quelque chose que l'optimiseur ne pouvait pas améliorer. L'exécution de 'explain' vous montrera la méthode de l'optimiseur pour récupérer les données.