Subquery vs join

Ho refactored una sezione lenta di un’applicazione che abbiamo ereditato da un’altra società per usare un inner join invece di una subquery come

where id in (select id from ... ) 

La query refactored viene eseguita circa 100 volte più velocemente. (~ 50 secondi a ~ 0,3) Mi aspettavo un miglioramento, ma qualcuno può spiegare perché è stato così drastico? Le colonne utilizzate nella clausola where sono state tutte indicizzate. SQL esegue la query nella clausola where una volta per riga o qualcosa del genere?

Aggiornamento – Spiega i risultati:

La differenza è nella seconda parte della query “where id in ()” –

 2 DEPENDENT SUBQUERY submission_tags ref st_tag_id st_tag_id 4 const 2966 Using where 

vs 1 riga indicizzata con il join:

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

Una “sottoquery correlata” (ovvero, una in cui la condizione where dipende dai valori ottenuti dalle righe della query contenente) verrà eseguita una volta per ogni riga. Una subquery non correlata (una in cui la condizione where è indipendente dalla query contenente) verrà eseguita una volta all’inizio. Il motore SQL rende automaticamente questa distinzione.

Ma, sì, spiegare piano ti darà i dettagli sporchi.

Esegui la sottoquery una volta per ogni riga mentre l’unione avviene sugli indici.

Ecco un esempio di come le subquery vengono valutate in MySQL 6.0 .

Il nuovo ottimizzatore convertirà questo tipo di subquery in join.

Esegui il piano di spiegazioni su ciascuna versione, ti dirà perché.

prima che le query vengano eseguite sul dataset, vengono sottoposte a un ottimizzatore di query, l’ottimizzatore tenta di organizzare la query in modo tale da rimuovere il maggior numero di tuple (righe) dal set di risultati il ​​più rapidamente ansible. Spesso quando si utilizzano le sottoquery (specialmente quelle cattive) le tuple non possono essere eliminate dal set di risultati fino a quando la query esterna non inizia a essere eseguita.

Senza vedere la query è difficile dire cosa ci sia di così male nell’originale, ma suppongo che fosse qualcosa che l’ottimizzatore non avrebbe potuto fare molto meglio. L’esecuzione di ‘spiega’ ti mostrerà il metodo di ottimizzazione per il recupero dei dati.

Di solito è il risultato dell’ottimizzatore che non è in grado di capire che la sottoquery può essere eseguita come join, nel qual caso esegue la subquery per ogni record nella tabella piuttosto che unirsi alla tabella nella sottoquery rispetto alla tabella che si sta interrogando. Alcuni dei database più “enterprise” sono migliori in questo, ma a volte lo mancano ancora.

Questa domanda è in qualche modo generale, quindi ecco una risposta generale:

Fondamentalmente, le query richiedono più tempo quando MySQL ha tonnellate di righe da ordinare.

Fai questo:

Esegui una SPIEGAZIONE su ognuna delle query (quella JOIN’ed, quindi quella Subquery) e pubblica qui i risultati.

Penso che vedere la differenza nell’interpretazione di MySQL di tali query sia un’esperienza di apprendimento per tutti.

La subquery dove deve eseguire 1 query per ogni riga restituita. L’inner join deve solo eseguire 1 query.

Guarda il piano di query per ogni query.

Dove in e Join può essere in genere implementato utilizzando lo stesso piano di esecuzione, quindi in genere non vi è alcun aumento della velocità tra le modifiche.

Optimizer non ha fatto un ottimo lavoro. Di solito possono essere trasformati senza alcuna differenza e l’ottimizzatore può farlo.

Probabilmente la sottoquery stava eseguendo una “scansione completa della tabella”. In altre parole, non usare l’indice e restituire troppe righe che il Where della query principale aveva bisogno di filtrare.

Solo un’ipotesi senza dettagli, ma questa è la situazione comune.

Con una sottoquery, è necessario rieseguire la seconda SELECT per ogni risultato e ogni esecuzione restituisce in genere 1 riga.

Con un join, il secondo SELECT restituisce molte più righe, ma devi eseguirlo solo una volta. Il vantaggio è che ora puoi partecipare ai risultati e unire le relazioni è ciò che un database dovrebbe essere bravo in. Ad esempio, forse l’ottimizzatore può individuare come sfruttare al meglio un indice ora.

Non è tanto la subquery come la clausola IN, anche se i join sono alla base del motore SQL di Oracle e funzionano molto rapidamente.

Tratto dal Manuale di riferimento ( 14.2.10.11 Riscrivere le sottoquery come unioni ):

Un JOINT [OUTER] SINISTRO può essere più veloce di una subquery equivalente poiché il server potrebbe essere in grado di ottimizzarlo meglio, un fatto che non è specifico per il solo server MySQL.

Quindi le subquery possono essere più lente di LEFT [OUTER] JOINS.