Question Rejoindre la sous-requête


Je suis un utilisateur MySQL old-school et j'ai toujours préféré JOIN sur sous-requête. Mais de nos jours tout le monde utilise des sous-requêtes et je déteste ça, je ne sais pas pourquoi.

Je manque de connaissances théoriques pour juger par moi-même s'il y a une différence. Est-ce qu'une sous-requête est aussi bonne qu'une JOIN et donc il n'y a pas de quoi s'inquiéter?


631
2018-04-05 06:22


origine


Réponses:


Extrait du manuel MySQL (13.2.10.11 Réécriture des sous-requêtes en tant qu'annonces):

Un LEFT [OUTER] JOIN peut être plus rapide qu'une sous-requête équivalente car le serveur pourrait mieux l'optimiser, ce qui n'est pas spécifique au serveur MySQL.

Donc, les sous-requêtes peuvent être plus lentes que celles de gauche [OUTER] JOINS, mais à mon avis leur force est une lisibilité légèrement supérieure.


98
2017-09-20 08:09



Les sous-requêtes sont la façon logiquement correcte de résoudre les problèmes du formulaire, "Obtenir les faits de A, conditionnellement aux faits de B". Dans de tels cas, il est plus logique de coller B dans une sous-requête que de faire une jointure. Il est également plus sûr, d'un point de vue pratique, puisque vous ne devez pas être prudent pour obtenir des faits dupliqués de A en raison de plusieurs matchs contre B.

En pratique, cependant, la réponse revient généralement à la performance. Certains optimiseurs aspirent les citrons lorsqu'ils reçoivent une jointure par rapport à une sous-requête, et d'autres aspirent les citrons dans l'autre sens, et ceci est spécifique à l'optimiseur, spécifique à la version du SGBD et propre à la requête.

Historiquement, les jointures explicites gagnent généralement, ce qui explique que les optimiseurs s'améliorent constamment. Je préfère donc écrire les requêtes d'une manière cohérente d'un point de vue logique, puis restructurer si les contraintes de performance le justifient.


695
2018-04-05 06:26



Dans la plupart des cas JOINs sont plus rapides que les sous-requêtes et il est très rare qu'une sous-requête soit plus rapide.

Dans JOINs Le SGBDR peut créer un plan d'exécution mieux adapté à votre requête et prévoir quelles données doivent être chargées pour gagner du temps, contrairement à la sous-requête où toutes les requêtes seront exécutées et toutes les données chargées pour le traitement.

La bonne chose dans les sous-requêtes est qu'elles sont plus lisibles que JOINs: c'est pourquoi la plupart des nouvelles personnes SQL les préfèrent; c'est la manière facile; mais en ce qui concerne la performance, les JOINS sont meilleurs dans la plupart des cas, même s'ils ne sont pas difficiles à lire.


330
2018-04-05 06:39



Utilisez EXPLAIN pour voir comment votre base de données exécute la requête sur vos données. Il y a un énorme "ça dépend" dans cette réponse ...

PostgreSQL peut réécrire une sous-requête à une jointure ou une jointure à une sous-requête quand elle pense que l'un est plus rapide que l'autre. Tout dépend des données, des index, de la corrélation, de la quantité de données, de la requête, etc.


114
2018-04-05 07:37



Tout d'abord, pour comparer les deux premières, vous devez distinguer les requêtes avec des sous-requêtes à:

  1. une classe de sous-requêtes qui ont toujours une requête équivalente correspondante écrite avec des jointures
  2. une classe de sous-requêtes qui ne peuvent pas être réécrites à l'aide de jointures

Pour le premier cours des requêtes un bon SGBDR verra les jointures et les sous-requêtes comme équivalentes et produira les mêmes plans de requête.

Ces jours-ci, même mysql fait cela.

Pourtant, parfois ce n'est pas le cas, mais cela ne signifie pas que les jointures seront toujours gagnantes - j'ai eu des cas lors de l'utilisation de sous-requêtes dans mysql amélioration des performances. (Par exemple, si quelque chose empêche mysql planner d'estimer correctement le coût et si le planificateur ne voit pas la variante de jointure et la sous-requête comme étant identiques, les sous-requêtes peuvent surpasser les jointures en forçant un certain chemin).

La conclusion est que vous devriez tester vos requêtes pour les variantes de jointure et de sous-requête si vous voulez être sûr de celui qui fonctionnera le mieux.

Pour la deuxième classe la comparaison n'a aucun sens car ces requêtes ne peuvent pas être réécrites à l'aide de jointures et, dans ce cas, les sous-requêtes sont une manière naturelle d'effectuer les tâches requises et vous ne devez pas les discriminer.


39
2018-05-28 09:33



MSDN Documentation pour SQL Server dit 

De nombreuses instructions Transact-SQL qui incluent des sous-requêtes peuvent être formulées en tant que jointures. D'autres questions peuvent être posées uniquement avec des sous-requêtes. Dans Transact-SQL, il n'y a généralement aucune différence de performance entre une instruction qui inclut une sous-requête et une version sémantiquement équivalente qui ne l'est pas. Cependant, dans certains cas où l'existence doit être vérifiée, une jointure donne de meilleures performances. Sinon, la requête imbriquée doit être traitée pour chaque résultat de la requête externe afin d'assurer l'élimination des doublons. Dans de tels cas, une approche de jointure donnerait de meilleurs résultats.

donc si vous avez besoin de quelque chose comme

select * from t1 where exists select * from t2 where t2.parent=t1.id

essayez d'utiliser la jointure à la place. Dans d'autres cas, cela ne fait aucune différence.

Je dis: Créer les fonctions pour les sous-requêtes, éliminez le problème de l'encombrement et vous permet d'implémenter une logique supplémentaire aux sous-requêtes. Je recommande donc de créer des fonctions pour les sous-requêtes autant que possible.

Clutter dans le code est un gros problème et l'industrie travaille à l'éviter depuis des décennies.


22
2017-11-16 09:50



Je pense que ce qui a été sous-souligné dans les réponses citées est la question de doublons et les résultats problématiques qui peuvent découler de cas particuliers (d'utilisation).

(bien que Marcelo Cantos le mentionne)

Je citerai l'exemple des cours Lagunita de Stanford sur SQL.

Table des étudiants

+------+--------+------+--------+
| sID  | sName  | GPA  | sizeHS |
+------+--------+------+--------+
|  123 | Amy    |  3.9 |   1000 |
|  234 | Bob    |  3.6 |   1500 |
|  345 | Craig  |  3.5 |    500 |
|  456 | Doris  |  3.9 |   1000 |
|  567 | Edward |  2.9 |   2000 |
|  678 | Fay    |  3.8 |    200 |
|  789 | Gary   |  3.4 |    800 |
|  987 | Helen  |  3.7 |    800 |
|  876 | Irene  |  3.9 |    400 |
|  765 | Jay    |  2.9 |   1500 |
|  654 | Amy    |  3.9 |   1000 |
|  543 | Craig  |  3.4 |   2000 |
+------+--------+------+--------+

Appliquer la table

(demandes faites à des universités et des majors spécifiques)

+------+----------+----------------+----------+
| sID  | cName    | major          | decision |
+------+----------+----------------+----------+
|  123 | Stanford | CS             | Y        |
|  123 | Stanford | EE             | N        |
|  123 | Berkeley | CS             | Y        |
|  123 | Cornell  | EE             | Y        |
|  234 | Berkeley | biology        | N        |
|  345 | MIT      | bioengineering | Y        |
|  345 | Cornell  | bioengineering | N        |
|  345 | Cornell  | CS             | Y        |
|  345 | Cornell  | EE             | N        |
|  678 | Stanford | history        | Y        |
|  987 | Stanford | CS             | Y        |
|  987 | Berkeley | CS             | Y        |
|  876 | Stanford | CS             | N        |
|  876 | MIT      | biology        | Y        |
|  876 | MIT      | marine biology | N        |
|  765 | Stanford | history        | Y        |
|  765 | Cornell  | history        | N        |
|  765 | Cornell  | psychology     | Y        |
|  543 | MIT      | CS             | N        |
+------+----------+----------------+----------+

Essayons de trouver les scores GPA pour les étudiants qui ont postulé CS majeure (indépendamment de l'université)

En utilisant une sous-requête:

select GPA from Student where sID in (select sID from Apply where major = 'CS');

+------+
| GPA  |
+------+
|  3.9 |
|  3.5 |
|  3.7 |
|  3.9 |
|  3.4 |
+------+

La valeur moyenne pour ce résultat est:

select avg(GPA) from Student where sID in (select sID from Apply where major = 'CS');

+--------------------+
| avg(GPA)           |
+--------------------+
| 3.6800000000000006 |
+--------------------+

En utilisant une jointure:

select GPA from Student, Apply where Student.sID = Apply.sID and Apply.major = 'CS';

+------+
| GPA  |
+------+
|  3.9 |
|  3.9 |
|  3.5 |
|  3.7 |
|  3.7 |
|  3.9 |
|  3.4 |
+------+

valeur moyenne pour ce résultat:

select avg(GPA) from Student, Apply where Student.sID = Apply.sID and Apply.major = 'CS';

+-------------------+
| avg(GPA)          |
+-------------------+
| 3.714285714285714 |
+-------------------+

Il est évident que la deuxième tentative donne des résultats trompeurs dans notre cas d'utilisation, étant donné qu'elle compte des doublons pour le calcul de la valeur moyenne. Il est également évident que l'utilisation de distinct avec la déclaration de jointure sera ne pas éliminer le problème, étant donné qu'il conservera par erreur un événement sur trois 3.9 But. Le bon cas est de rendre compte DEUX (2) occurrences du 3.9marquer étant donné que nous avons réellement DEUX (2) les étudiants ayant ce score qui respectent nos critères de recherche.

Il semble que dans certains cas, une sous-requête est le moyen le plus sûr d'aller, en plus des problèmes de performance.


19
2017-10-19 10:05



Exécuter sur une très grande base de données à partir d'un ancien CMS Mambo:

SELECT id, alias
FROM
  mos_categories
WHERE
  id IN (
    SELECT
      DISTINCT catid
    FROM mos_content
  );

0 seconde

SELECT
  DISTINCT mos_content.catid,
  mos_categories.alias
FROM
  mos_content, mos_categories
WHERE
  mos_content.catid = mos_categories.id;

~ 3 secondes

Un EXPLAIN montre qu'ils examinent exactement le même nombre de lignes, mais l'une prend 3 secondes et l'autre est presque instantanée. Morale de l'histoire? Si la performance est importante (quand n'est-ce pas?), Essayez-le de multiples façons et voyez lequel est le plus rapide.

Et...

SELECT
  DISTINCT mos_categories.id,
  mos_categories.alias
FROM
  mos_content, mos_categories
WHERE
  mos_content.catid = mos_categories.id;

0 seconde

Encore une fois, mêmes résultats, même nombre de rangées examinées. Ma conjecture est que DISTINCT mos_content.catid prend beaucoup plus de temps que DISTINCT mos_categories.id fait.


15
2017-10-20 22:27



Version MySQL: 5.5.28-0ubuntu0.12.04.2-log

J'avais aussi l'impression que JOIN est toujours mieux qu'une sous-requête dans MySQL, mais EXPLAIN est une meilleure façon de faire un jugement. Voici un exemple où les sous-requêtes fonctionnent mieux que JOINs.

Voici ma requête avec 3 sous-requêtes:

EXPLAIN SELECT vrl.list_id,vrl.ontology_id,vrl.position,l.name AS list_name, vrlih.position AS previous_position, vrl.moved_date 
FROM `vote-ranked-listory` vrl 
INNER JOIN lists l ON l.list_id = vrl.list_id 
INNER JOIN `vote-ranked-list-item-history` vrlih ON vrl.list_id = vrlih.list_id AND vrl.ontology_id=vrlih.ontology_id AND vrlih.type='PREVIOUS_POSITION' 
INNER JOIN list_burial_state lbs ON lbs.list_id = vrl.list_id AND lbs.burial_score < 0.5 
WHERE vrl.position <= 15 AND l.status='ACTIVE' AND l.is_public=1 AND vrl.ontology_id < 1000000000 
 AND (SELECT list_id FROM list_tag WHERE list_id=l.list_id AND tag_id=43) IS NULL 
 AND (SELECT list_id FROM list_tag WHERE list_id=l.list_id AND tag_id=55) IS NULL 
 AND (SELECT list_id FROM list_tag WHERE list_id=l.list_id AND tag_id=246403) IS NOT NULL 
ORDER BY vrl.moved_date DESC LIMIT 200;

EXPLAIN montre:

+----+--------------------+----------+--------+-----------------------------------------------------+--------------+---------+-------------------------------------------------+------+--------------------------+
| id | select_type        | table    | type   | possible_keys                                       | key          | key_len | ref                                             | rows | Extra                    |
+----+--------------------+----------+--------+-----------------------------------------------------+--------------+---------+-------------------------------------------------+------+--------------------------+
|  1 | PRIMARY            | vrl      | index  | PRIMARY                                             | moved_date   | 8       | NULL                                            |  200 | Using where              |
|  1 | PRIMARY            | l        | eq_ref | PRIMARY,status,ispublic,idx_lookup,is_public_status | PRIMARY      | 4       | ranker.vrl.list_id                              |    1 | Using where              |
|  1 | PRIMARY            | vrlih    | eq_ref | PRIMARY                                             | PRIMARY      | 9       | ranker.vrl.list_id,ranker.vrl.ontology_id,const |    1 | Using where              |
|  1 | PRIMARY            | lbs      | eq_ref | PRIMARY,idx_list_burial_state,burial_score          | PRIMARY      | 4       | ranker.vrl.list_id                              |    1 | Using where              |
|  4 | DEPENDENT SUBQUERY | list_tag | ref    | list_tag_key,list_id,tag_id                         | list_tag_key | 9       | ranker.l.list_id,const                          |    1 | Using where; Using index |
|  3 | DEPENDENT SUBQUERY | list_tag | ref    | list_tag_key,list_id,tag_id                         | list_tag_key | 9       | ranker.l.list_id,const                          |    1 | Using where; Using index |
|  2 | DEPENDENT SUBQUERY | list_tag | ref    | list_tag_key,list_id,tag_id                         | list_tag_key | 9       | ranker.l.list_id,const                          |    1 | Using where; Using index |
+----+--------------------+----------+--------+-----------------------------------------------------+--------------+---------+-------------------------------------------------+------+--------------------------+

La même requête avec JOINs est:

EXPLAIN SELECT vrl.list_id,vrl.ontology_id,vrl.position,l.name AS list_name, vrlih.position AS previous_position, vrl.moved_date 
FROM `vote-ranked-listory` vrl 
INNER JOIN lists l ON l.list_id = vrl.list_id 
INNER JOIN `vote-ranked-list-item-history` vrlih ON vrl.list_id = vrlih.list_id AND vrl.ontology_id=vrlih.ontology_id AND vrlih.type='PREVIOUS_POSITION' 
INNER JOIN list_burial_state lbs ON lbs.list_id = vrl.list_id AND lbs.burial_score < 0.5 
LEFT JOIN list_tag lt1 ON lt1.list_id = vrl.list_id AND lt1.tag_id = 43 
LEFT JOIN list_tag lt2 ON lt2.list_id = vrl.list_id AND lt2.tag_id = 55 
INNER JOIN list_tag lt3 ON lt3.list_id = vrl.list_id AND lt3.tag_id = 246403 
WHERE vrl.position <= 15 AND l.status='ACTIVE' AND l.is_public=1 AND vrl.ontology_id < 1000000000 
AND lt1.list_id IS NULL AND lt2.tag_id IS NULL 
ORDER BY vrl.moved_date DESC LIMIT 200;

et la sortie est:

+----+-------------+-------+--------+-----------------------------------------------------+--------------+---------+---------------------------------------------+------+----------------------------------------------+
| id | select_type | table | type   | possible_keys                                       | key          | key_len | ref                                         | rows | Extra                                        |
+----+-------------+-------+--------+-----------------------------------------------------+--------------+---------+---------------------------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | lt3   | ref    | list_tag_key,list_id,tag_id                         | tag_id       | 5       | const                                       | 2386 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | l     | eq_ref | PRIMARY,status,ispublic,idx_lookup,is_public_status | PRIMARY      | 4       | ranker.lt3.list_id                          |    1 | Using where                                  |
|  1 | SIMPLE      | vrlih | ref    | PRIMARY                                             | PRIMARY      | 4       | ranker.lt3.list_id                          |  103 | Using where                                  |
|  1 | SIMPLE      | vrl   | ref    | PRIMARY                                             | PRIMARY      | 8       | ranker.lt3.list_id,ranker.vrlih.ontology_id |   65 | Using where                                  |
|  1 | SIMPLE      | lt1   | ref    | list_tag_key,list_id,tag_id                         | list_tag_key | 9       | ranker.lt3.list_id,const                    |    1 | Using where; Using index; Not exists         |
|  1 | SIMPLE      | lbs   | eq_ref | PRIMARY,idx_list_burial_state,burial_score          | PRIMARY      | 4       | ranker.vrl.list_id                          |    1 | Using where                                  |
|  1 | SIMPLE      | lt2   | ref    | list_tag_key,list_id,tag_id                         | list_tag_key | 9       | ranker.lt3.list_id,const                    |    1 | Using where; Using index                     |
+----+-------------+-------+--------+-----------------------------------------------------+--------------+---------+---------------------------------------------+------+----------------------------------------------+

Une comparaison de rows colonne indique la différence et la requête avec JOINs utilise Using temporary; Using filesort.

Bien sûr, lorsque je lance les deux requêtes, la première est faite en 0.02 secondes, la seconde ne se termine pas même après 1 minute, donc EXPLAIN a expliqué ces requêtes correctement.

Si je n'ai pas l'INNER JOIN sur le list_tag table, c'est-à-dire si j'enlève

AND (SELECT list_id FROM list_tag WHERE list_id=l.list_id AND tag_id=246403) IS NOT NULL  

à partir de la première requête et de manière correspondante:

INNER JOIN list_tag lt3 ON lt3.list_id = vrl.list_id AND lt3.tag_id = 246403

à partir de la deuxième requête, EXPLAIN renvoie le même nombre de lignes pour les deux requêtes et ces deux requêtes s'exécutent également rapidement.


11
2018-06-06 18:25