Subqueries vs jointures

J’ai refait une partie lente d’une application héritée d’une autre entreprise pour utiliser une jointure interne au lieu d’une sous-requête comme

where id in (select id from ... ) 

La requête refactorisée s’exécute environ 100 fois plus rapidement. (~ 50 secondes à ~ 0,3) Je m’attendais à une amélioration, mais quelqu’un peut-il expliquer pourquoi c’était si drastique? Les colonnes utilisées dans la clause where ont toutes été indexées. Est-ce que SQL exécute la requête dans la clause where une fois par ligne ou quelque chose?

Mise à jour – Expliquer les résultats:

La différence est 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 

vs 1 ligne indexée avec la jointure:

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

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

Mais, oui, expliquer-plan vous donnera les détails sales.

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

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.

Exécutez le plan d’explication sur chaque version, cela vous dira pourquoi.

Avant que les requêtes ne soient exécutées sur le jeu de données via l’optimiseur de requêtes, l’optimiseur tente d’organiser la requête de manière à pouvoir supprimer autant de tuples (lignes) du jeu de résultats que possible. Souvent, lorsque vous utilisez des sous-requêtes (en particulier des sous-requêtes), les tuples ne peuvent pas être supprimés de l’ensemble de résultats tant que la requête externe n’a pas commencé à s’exécuter.

Sans avoir vu la requête, il était difficile de dire ce qui était si mal à propos de l’original, mais je pense que l’optimiseur ne pourrait pas faire mieux. En cours d’exécution ‘explain’ vous montrera la méthode d’optimisation pour récupérer les données.

En général, l’optimiseur n’est pas en mesure de déterminer si la sous-requête peut être exécutée en tant que jointure. Dans ce cas, elle exécute la sous-requête pour chaque enregistrement de la table. Certaines des bases de données les plus “entresockets” sont meilleures à cela, mais elles manquent encore parfois.

Cette question est un peu générale, alors voici une réponse générale:

Fondamentalement, les requêtes sont plus longues lorsque MySQL a des tonnes de lignes à sortinger.

Faites ceci:

Exécutez un EXPLAIN sur chacune des requêtes (celle jointe, puis la sous-requête) et publiez les résultats ici.

Je pense que voir la différence dans l’interprétation de MySQL de ces requêtes serait une expérience d’apprentissage pour tout le monde.

La sous-requête where doit exécuter 1 requête pour chaque ligne renvoyée. La jointure interne doit simplement exécuter 1 requête.

Examinez le plan de requête pour chaque requête.

Où dans et Join peuvent généralement être implémentés en utilisant le même plan d’exécution, donc généralement, il n’y a aucune accélération de changer entre eux.

Optimizer n’a pas fait du très bon travail. Ils peuvent généralement être transformés sans aucune différence et l’optimiseur peut le faire.

La sous-requête exécutait probablement une “parsing complète de la table”. En d’autres termes, ne pas utiliser l’index et renvoyer trop de lignes que la requête Where devait filtrer.

Juste une supposition sans détails bien sûr, mais c’est la situation commune.

Avec une sous-requête, vous devez réexécuter le 2ème SELECT pour chaque résultat, et chaque exécution retourne généralement 1 ligne.

Avec une jointure, le 2nd SELECT renvoie beaucoup plus de lignes, mais vous ne devez l’exécuter qu’une seule fois. L’avantage est que maintenant, vous pouvez vous joindre aux résultats, et rejoindre une relation est ce que la firebase database est censée être bonne. Par exemple, l’optimiseur peut peut-être déterminer comment tirer le meilleur parti d’un index maintenant.

Ce n’est pas tant la sous-requête que la clause IN, bien que les jointures soient à la base d’au moins le moteur SQL d’Oracle et qu’elles s’exécutent extrêmement rapidement.

Tiré du Manuel de référence ( 14.2.10.11 Réécriture des sous-requêtes en tant que jointures ):

Une jointure LEFT [OUTER] peut être plus rapide qu’une sous-requête équivalente, car le serveur peut être en mesure de l’optimiser davantage, ce qui n’est pas spécifique au seul serveur MySQL.

Les sous-requêtes peuvent donc être plus lentes que LEFT [OUTER] JOINS.