EXISTS vs IN dans les sous-requêtes MySQL : laquelle est la plus performante ?

Sous-requêtes avec EXISTS vs IN dans MySQL : une comparaison des performances

Les sous-requêtes jouent un rôle crucial dans l'extraction de données spécifiques d'une base de données. Deux méthodes de sous-requête courantes sont EXISTS et IN. Bien que les deux puissent obtenir des résultats similaires, ils présentent des caractéristiques de performances distinctes.

Considérez les deux requêtes suivantes :

Méthode 1 :

SELECT *
FROM tracker
WHERE reservation_id IN (
    SELECT reservation_id
    FROM tracker
    GROUP BY reservation_id
    HAVING
        (
            method = 1
            AND type = 0
            AND Count(*) > 1
        )
        OR (
            method = 1
            AND type = 1
            AND Count(*) > 1
        )
        OR (
            method = 2
            AND type = 2
            AND Count(*) > 0
        )
        OR (
            method = 3
            AND type = 0
            AND Count(*) > 0
        )
        OR (
            method = 3
            AND type = 1
            AND Count(*) > 1
        )
        OR (
            method = 3
            AND type = 3
            AND Count(*) > 0
        )
);

Méthode 2 :

SELECT *
FROM tracker t
WHERE EXISTS (
    SELECT reservation_id
    FROM tracker t3
    WHERE
        t3.reservation_id = t.reservation_id
    GROUP BY reservation_id
    HAVING
        (
            METHOD = 1
            AND TYPE = 0
            AND COUNT(*) > 1
        )
        OR (
            METHOD = 1
            AND TYPE = 1
            AND COUNT(*) > 1
        )
        OR (
            METHOD = 2
            AND TYPE = 2
            AND COUNT(*) > 0
        )
        OR (
            METHOD = 3
            AND TYPE = 0
            AND COUNT(*) > 0
        )
        OR (
            METHOD = 3
            AND TYPE = 1
            AND COUNT(*) > 1
        )
        OR (
            METHOD = 3
            AND TYPE = 3
            AND COUNT(*) > 0
        )
);

En termes de performances, méthode 2 surpasse considérablement la méthode 1, prenant moins d'une seconde à exécuter, contre plus de 10 secondes. Pour comprendre la raison de cet écart, nous devons approfondir le fonctionnement interne de chaque méthode.

EXISTE vs IN : différences clés

• EXISTE : Vérifie si au moins une ligne correspond à la sous-requête. Si c'est le cas, cela renvoie vrai ; sinon, il renvoie false. Il repose sur l'existence des lignes plutôt que sur la récupération des lignes elles-mêmes.
• IN : Compare la valeur de la requête externe à chaque ligne de la sous-requête. Si une correspondance est trouvée, elle renvoie vrai ; sinon, il renvoie false. Il s'exécute sur toutes les lignes de la sous-requête.

Considérations relatives aux performances

• Taille de la sous-requête : Lorsque la sous-requête renvoie une grande nombre de lignes, IN peut devenir cher car il se compare à toutes. À l'inverse, EXISTS n'a besoin de trouver qu'une seule ligne correspondante, ce qui le rend plus efficace pour les sous-requêtes volumineuses.
• Valeurs nulles : EXISTS peut gérer les valeurs nulles plus efficacement que IN. Lorsqu'une sous-requête avec IN renvoie null, elle peut propager la valeur null à la requête externe. Cependant, EXISTS traite null comme faux.
• Optimisations : MySQL peut optimiser EXISTS à l'aide d'index, tandis que IN peut nécessiter des optimisations supplémentaires, telles que l'utilisation de matérialisation ou de vues matérialisées.

Conclusion

En général, EXISTS est recommandé lorsque la sous-requête devrait renvoyer un grand nombre de lignes ou si des valeurs nulles sont impliquées. Pour les petites sous-requêtes, IN peut être plus performant. Il est toujours conseillé d'utiliser un plan d'explication pour déterminer la meilleure approche pour une requête spécifique.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!