Prestazioni SQL UNION vs OR

Ho appena letto parte di un articolo di ottimizzazione e segfault sulla seguente dichiarazione:

Quando si utilizzano le istruzioni di sostituzione SQL utilizzando OR con UNION :

 select username from users where company = 'bbc' or company = 'itv'; 

a:

 select username from users where company = 'bbc' union select username from users where company = 'itv'; 

Da una rapida EXPLAIN :

Utilizzando OR :

inserisci la descrizione dell'immagine qui

Utilizzando UNION :

inserisci la descrizione dell'immagine qui

Non significa che UNION fa il doppio del lavoro ?

Mentre apprezzo che UNION possa essere più performante per certi RDBMS e certi schemi di tabelle, questo non è categoricamente vero come i suggerimenti dell’autore.

Domanda

Ho sbagliato?

O l’articolo che hai letto ha usato un cattivo esempio, o hai erroneamente interpretato il loro punto.

 select username from users where company = 'bbc' or company = 'itv'; 

Questo è equivalente a:

 select username from users where company IN ('bbc', 'itv'); 

MySQL può usare un indice sulla company per questa query bene. Non c’è bisogno di fare UNION.

Il caso più complicato è dove si ha una condizione OR che coinvolge due colonne diverse .

 select username from users where company = 'bbc' or city = 'London'; 

Supponiamo che ci sia un indice sulla company e un indice separato sulla city . Dato che MySQL di solito usa solo un indice per tabella in una determinata query, quale indice dovrebbe usare? Se usa l’indice sulla company , dovrebbe comunque fare una scansione della tabella per trovare le righe in cui la city è Londra. Se utilizza l’indice sulla city , dovrebbe eseguire una scansione della tabella per le righe in cui la company è bbc.

La soluzione UNION è per questo tipo di caso.

 select username from users where company = 'bbc' union select username from users where city = 'London'; 

Ora ogni sottoquery può usare l’indice per la sua ricerca, ei risultati della sottoquery sono combinati UNION .


Un utente anonimo ha proposto una modifica alla mia risposta sopra, ma un moderatore ha rifiutato la modifica. Dovrebbe essere un commento, non una modifica. L’affermazione della modifica proposta era che UNION doveva ordinare il set di risultati per eliminare le righe duplicate. Ciò rende la query più lenta e l’ottimizzazione dell’indice è quindi un lavaggio.

La mia risposta è che gli indici aiutano a ridurre il risultato impostato su un numero limitato di righe prima che si verifichi l’UNION. UNION infatti elimina i duplicati, ma per farlo deve solo ordinare il piccolo set di risultati. Potrebbero esserci casi in cui le clausole WHERE corrispondono a una porzione significativa della tabella e l’ordinamento durante UNION è costoso quanto semplicemente facendo la scansione della tabella. Ma è più comune che il set di risultati venga ridotto dalle ricerche indicizzate, quindi l’ordinamento è molto meno costoso della scansione della tabella.

La differenza dipende dai dati nella tabella e dai termini ricercati. L’unico modo per determinare la soluzione migliore per una determinata query è provare entrambi i metodi nel profiler di query MySQL e confrontare le loro prestazioni.

Quelle non sono la stessa query.

Non ho molta esperienza con MySQL, quindi non sono sicuro di ciò che fa o non fa l’ottimizzatore di query, ma qui ci sono i miei pensieri dal mio background generale (principalmente ms sql server).

In genere, l’analizzatore di query può prendere le due query precedenti e creare da esse lo stesso piano esatto (se erano uguali), quindi non avrebbe importanza. Sospetto che non ci siano differenze di prestazioni tra queste query (che sono equivalenti)

 select distinct username from users where company = 'bbc' or company = 'itv'; 

e

 select username from users where company = 'bbc' union select username from users where company = 'itv'; 

Ora, la domanda è, ci sarebbe una differenza tra le seguenti domande, di cui in realtà non lo so, ma sospetterei che l’ottimizzatore renderebbe più simile alla prima query

 select username from users where company = 'bbc' or company = 'itv'; 

e

 select username from users where company = 'bbc' union all select username from users where company = 'itv'; 

Dipende da cosa fa l’ottimizzatore in base alla dimensione dei dati, indici, versione del software, ecc.

Direi che l’uso dell’OR darebbe all’ottimizzatore una migliore possibilità di trovare un po ‘di efficienza, poiché tutto è in una singola affermazione logica.

Inoltre, UNION ha un sovraccarico, poiché crea un set di ripristino (senza duplicati). Ogni dichiarazione nell’UNION dovrebbe essere eseguita abbastanza rapidamente se la società viene indicizzata … non è sicuro che stia facendo davvero il doppio del lavoro.

Linea di fondo

A meno che tu non abbia davvero bisogno di spremere ogni velocità dalla tua query, probabilmente è meglio andare con il modulo che meglio comunica la tua intenzione … la OR

Aggiornare

Intendevo anche menzionare IN. Credo che la seguente query fornirà prestazioni migliori rispetto all’OR (è anche la forma che preferisco):

select username from users where company in ('bbc', 'itv');

In quasi tutti i casi, l’ union o l’ union all versioni eseguirà due scansioni complete della tabella degli utenti.

La versione or è molto meglio in pratica, dal momento che eseguirà solo una scansione del tavolo una volta. Utilizzerà anche un indice solo una volta, se disponibile.

L’affermazione originale sembra sbagliata, per quasi tutti i database e qualsiasi situazione.

La risposta di Bill Karwin è giusta. Quando entrambe le parti dell’istruzione OR hanno il proprio indice, è meglio fare l’unione perché una volta che si ha un piccolo sottoinsieme di risultati, è più facile ordinarli ed eliminare i duplicati. Il costo totale è quasi inferiore all’utilizzo di un solo indice (per una colonna) e di una tabella per l’altra colonna (poiché mysql utilizza solo un indice per una colonna).

Dipende dalla struttura del tavolo e dai bisogni in generale, ma in grandi tabelle l’unione mi ha dato risultati migliori.