SQL Performance UNION vs OR
je viens de lire une partie d'un article d'optimisation et segfaulted sur la déclaration suivante:
lors de L'utilisation de SQL remplacer les instructions en utilisant
ORavec unUNION:select username from users where company = ‘bbc’ or company = ‘itv’;à:
select username from users where company = ‘bbc’ union select username from users where company = ‘itv’;
rapide EXPLAIN:
en utilisant OR:
en utilisant UNION:
Ne pas dire UNION dans doublez le travail?
si je comprends UNION peut être plus performant pour certains RDBMSes et certains schémas de table, ce n'est pas catégoriquement true comme le suggère l'auteur.
Question
ai-je tort?
5 réponses
soit l'article que vous avez lu utilisait un mauvais exemple, soit vous avez mal interprété leur point.
select username from users where company = 'bbc' or company = 'itv';
c'est l'équivalent de:
select username from users where company IN ('bbc', 'itv');
MySQL peut utiliser un index sur company pour cette requête très bien. Il n'y a pas besoin de faire de syndicat.
La plus délicate cas où vous avez un OR condition qui implique deux colonnes.
select username from users where company = 'bbc' or city = 'London';
supposons qu'il y ait un index sur company et un index séparé sur city. Étant donné que MySQL utilise généralement un seul index par table dans une requête donnée, quel index devrait-il utiliser? Si il utilise l'index sur company, il faudrait quand même faire un scan de table pour trouver les lignes où city c'est Londres. Si il utilise l'index sur city, il devrait faire un scan de table pour les lignes où company est bbc.
UNION la solution est pour ce type de cas.
select username from users where company = 'bbc'
union
select username from users where city = 'London';
maintenant chaque sous-requête peut utiliser l'index pour sa recherche, et les résultats de la sous-requête sont combinées par l' UNION.
un utilisateur anonyme a proposé un montage à ma réponse ci-dessus, mais un modérateur a rejeté le montage. Ça aurait dû être un commentaire, pas un montage. L'affirmation de l'édition proposée était que UNION doit trier le jeu de résultats pour éliminer les lignes dupliquées. Cela rend la requête fonctionne plus lentement et l'optimisation d'index est donc un lavage.
ma réponse est que les index aident à réduire le résultat à un petit nombre de lignes avant que L'UNION se produit. En fait, L'UNION élimine les doublons, mais pour ce faire, elle n'a qu'à trier le petit ensemble de résultats. Il peut y avoir des cas où les clauses WHERE correspondent à une partie importante de la table, et le tri pendant L'UNION est aussi coûteux que le simple balayage de la table. Mais il est plus courant que le résultat soit réduit par les recherches indexées, donc le tri est beaucoup moins coûteux que le scan de table.
la différence dépend des données table, et les termes de recherche en cours. La seule façon de déterminer la meilleure solution pour une requête donnée est d'essayer les deux méthodes le profileur de requête MySQL et comparent leurs performances.
ce n'est pas la même requête.
Je n'ai pas beaucoup d'expérience avec MySQL, donc je ne suis pas sûr de ce que l'optimiseur de requête fait ou ne fait pas, Mais voici mes pensées de mon arrière-plan général (principalement MS sql server).
typiquement, l'analyseur de requête peut prendre les deux requêtes ci-dessus et en faire exactement le même plan (si elles étaient les mêmes), donc cela n'aurait pas d'importance. Je dirais qu'il n'y a pas de différence de performance entre ces requêtes (qui sont équivalent)
select distinct username from users where company = ‘bbc’ or company = ‘itv’;
et
select username from users where company = ‘bbc’
union
select username from users where company = ‘itv’;
Maintenant, la question est, y aurait-il une différence entre les requêtes suivantes, dont je ne sais pas, mais je soupçonne que l'optimiseur va le rendre plus comme la première requête
select username from users where company = ‘bbc’ or company = ‘itv’;
et
select username from users where company = ‘bbc’
union all
select username from users where company = ‘itv’;
Cela dépend de ce que l'optimiseur finit en fonction de la taille des données, les index, la version du logiciel, etc.
je suppose que l'utilisation ou donnerait à l'optimiseur une meilleure chance de trouver des efficacités, puisque tout est dans un seul énoncé logique.
aussi, le syndicat a un peu de frais généraux, puisqu'il crée un reset set (pas de doublons). Chaque déclaration dans L'UNION devrait s'exécuter assez rapidement si est indexé... pas assurez-vous qu'il faudrait vraiment faire double le travail.
Bas de ligne
sauf si vous avez vraiment un besoin brûlant de presser chaque peu de vitesse hors de votre requête, il est probablement préférable d'aller avec la forme qui communique le mieux votre intention... la OU
mise à Jour
j'ai aussi destinée à mentionner. Je crois que la requête suivante donnera de meilleures performances que dans le cas OU (c'est aussi la forme que je préfère):
select username from users where company in ('bbc', 'itv');
Dans presque tous les cas, le union ou union all version va faire deux balayages complets de la table users.
or version est beaucoup mieux dans la pratique, car il ne numérisera la table qu'une fois. Il permettra également d'utiliser un indice qu'une seule fois, si disponible.
l'énoncé original semble juste erroné, pour à peu près n'importe quelle base de données et n'importe quelle situation.
la réponse de Bill Karwin est assez juste. Quand les deux parties de L'énoncé ou a son propre index, il est préférable de faire l'union parce qu'une fois que vous avez un petit sous-ensemble de résultats, il est plus facile de les trier et d'éliminer les doublons. Le coût Total est presque inférieur à l'utilisation d'un seul indice (pour l'une des colonnes) et le balayage de tableau pour l'autre colonne (parce que mysql utilise seulement un indice pour une colonne).
cela dépend de la structure de la table et des besoins en général, mais dans les grandes tables l'union a donné pour moi les meilleurs résultats.