Quando usare STRAIGHT_JOIN con MySQL

Ho appena avuto una query abbastanza complessa con cui stavo lavorando e ci sono voluti 8 secondi per funzionare. EXPLAIN mostrava un ordine di tabella strano ei miei indici non venivano tutti utilizzati anche con il suggerimento FORCE INDEX. Mi sono imbattuto nella parola chiave di join STRAIGHT_JOIN e ho iniziato a sostituire alcune delle mie parole chiave INTERNE INNER con esso. Ho notato un notevole miglioramento della velocità. Alla fine ho appena sostituito tutte le mie parole chiave INTERNE INNER con STRAIGHT_JOIN per questa query e ora funziona in .01 secondi.

La mia domanda è quando usi STRAIGHT_JOIN e quando usi INNER JOIN? C’è qualche ragione per non usare STRAIGHT_JOIN se stai scrivendo buone query?

Non consiglierei di usare STRAIGHT_JOIN senza una buona ragione. La mia esperienza personale è che MySQL Query Optimizer sceglie un piano di query povero più spesso di quanto vorrei, ma non abbastanza spesso da doverlo bypassare in generale, il che è ciò che si farebbe se si utilizzasse sempre STRAIGHT_JOIN.

La mia raccomandazione è di lasciare tutte le query come JOIN regolari. Se si scopre che una query utilizza un piano di query sub-ottimale, suggerirei prima di provare a riscrivere o riformulare la query un po ‘per vedere se l’ottimizzatore sceglierà un piano di query migliore. Inoltre, per innodb almeno, assicurati che non sia solo che le statistiche dell’indice non sono aggiornate ( ANALYZE TABLE ). Ciò può causare all’ottimizzatore la scelta di un piano di query scadente. I suggerimenti sull’ottimizzatore dovrebbero generalmente essere l’ultima risorsa.

Un altro motivo per non utilizzare i suggerimenti di query è che la distribuzione dei dati può cambiare nel tempo o che la tua indicizzazione potrebbe cambiare, ecc. Man mano che la tabella cresce. I tuoi suggerimenti di ricerca ora sono ottimali, potrebbero diventare sub-ottimali nel tempo. Ma l’ottimizzatore non sarà in grado di adattare il piano di query a causa dei tuoi suggerimenti ormai obsoleti. Resta più flessibile se consenti all’ottimizzatore di prendere le decisioni.

Da riferimento MySQL JOIN :

“STRAIGHT_JOIN è simile a JOIN, tranne per il fatto che la tabella di sinistra viene sempre letta prima della tabella di destra e può essere utilizzata per quei (pochi) casi per i quali lo strumento di ottimizzazione dei join mette le tabelle nell’ordine sbagliato.”

MySQL non ha necessariamente bisogno di scegliere l’ordine di partecipazione in query complesse. Specificando una query complessa come straight_join, la query esegue i join nell’ordine in cui sono specificati. Posizionando la tabella come prima denominatore meno comune e specificando straight_join si è in grado di migliorare le prestazioni della query.

STRAIGHT_JOIN , usando questa clausola, puoi controllare l’ordine JOIN : quale tabella è scansionata nel ciclo esterno e quale è nel ciclo interno.

Ecco uno scenario che è appena uscito sul lavoro.

Considera tre tabelle, A, B, C.

A ha 3.000 file; B ha 300.000.000 di righe; e C ha 2.000 righe.

Le chiavi esterne sono definite: B (a_id), B (c_id).

Supponi di avere una query simile a questa:

 select a.id, c.id from a join b on b.a_id = a.id join c on c.id = b.c_id 

In base alla mia esperienza, MySQL potrebbe scegliere di passare C -> B -> A in questo caso. C è più piccolo di A e B è enorme, e sono tutti uguali.

Il problema è che MySQL non prende necessariamente in considerazione la dimensione dell’intersezione tra (C.id e B.c_id) vs (A.id e B.a_id). Se il join tra B e C restituisce altrettante righe come B, allora è una scelta molto scarsa; se l’avvio con A avesse filtrato in basso B su tutte le righe di A, sarebbe stata una scelta molto migliore.

In genere si desidera eseguire i join in un ordine che riduce al minimo il numero di righe nel set risultante. Quindi, partendo da un piccolo tavolo e unendo in modo tale che anche l’unione risultante sarà piccola, è l’ideale. Le cose vanno a forma di pera se si inizia con un tavolino e unendolo a un tavolo più grande finisce proprio come il grande tavolo.

È comunque dipendente dalle statistiche. Se la distribuzione dei dati cambia, il calcolo potrebbe cambiare. Dipende anche dai dettagli di implementazione del meccanismo di join.

Ti dirò perché ho dovuto usare STRAIGHT_JOIN:

  • Ho riscontrato un problema di prestazioni con una query.
  • Semplificando la query, la query era improvvisamente più efficiente
  • Cercando di capire quale parte specifica stava portando il problema, non potevo. (2 a sinistra uniti erano lenti, e ognuno era indipendentemente veloce)
  • Ho quindi eseguito EXPLAIN con query sia lente che veloci (aggiungi uno dei join di sinistra)
  • Sorprendentemente, MySQL ha cambiato completamente gli ordini JOIN tra le 2 query.

Pertanto ho forzato uno dei join a essere straight_join per FORCE il join precedente da leggere per primo. Ciò ha impedito a MySQL di cambiare l’ordine di esecuzione e ha funzionato come un incantesimo!

Nella mia breve esperienza, una delle situazioni in cui STRAIGHT_JOIN ha ridotto la mia query da 30 secondi a 100 millisecondi è che la prima tabella del piano di esecuzione non era la tabella che ha l’ordine per colonne

 -- table sales (45000000) rows -- table stores (3) rows SELECT whatever FROM sales INNER JOIN stores ON sales.storeId = stores.id ORDER BY sales.date, sales.id LIMIT 50; -- there is an index on (date, id) 

Se l’ottimizzatore sceglie di colpire per primo i stores , causerà l’ Using index; Using temporary; Using filesort Using index; Using temporary; Using filesort Using index; Using temporary; Using filesort perché

se ORDER BY o GROUP BY contiene colonne di tabelle diverse dalla prima tabella nella coda di join, viene creata una tabella temporanea.

fonte

qui l’ottimizzatore ha bisogno di un piccolo aiuto dicendogli di colpire le sales prima usando

 sales STRAIGHT_JOIN stores 

Se la query termina con ORDER BY... LIMIT... , potrebbe essere ottimale riformulare la query per ingannare l’ottimizzatore affinché esegua il LIMIT prima del JOIN .

(Questa risposta non si applica solo alla domanda originale su STRAIGHT_JOIN , né si applica a tutti i casi di STRAIGHT_JOIN .)

A partire dall’esempio di @Accountant , questo dovrebbe essere più veloce nella maggior parte delle situazioni. (Ed evita di aver bisogno di suggerimenti).

 SELECT whatever FROM ( SELECT id FROM sales ORDER BY date, id LIMIT 50 ) AS x JOIN sales ON sales.id = x.id JOIN stores ON sales.storeId = stores.id ORDER BY sales.date, sales.id; 

Gli appunti:

  • Innanzitutto vengono caricati 50 ID. Questo sarà particolarmente veloce con INDEX(date, id) .
  • Quindi il ritorno alle sales ti consente di ottenere solo 30 “whatevers” senza trascinarli in una tabella temporanea.
  • poiché una subquery è, per definizione, non ordinata, è necessario ripetere l’ordine ORDER BY .
  • Sì, è più caotico. Ma è di solito più veloce.

Sono contraria all’uso dei colpi perché “anche se oggi è più veloce, potrebbe non riuscire a essere più veloce domani”.

 --use 120s, 18 million data explain SELECT DISTINCT d.taid FROM tvassist_recommend_list_everyday_diverse d, tvassist_taid_all t WHERE d.taid = t.taid AND t.client_version >= '21004007' AND t.utdid IS NOT NULL AND d.recommend_day = '20170403' LIMIT 0, 10000 --use 3.6s repalce by straight join explain SELECT DISTINCT d.taid FROM tvassist_recommend_list_everyday_diverse d STRAIGHT_JOIN tvassist_taid_all t on d.taid = t.taid WHERE t.client_version >= '21004007' AND d.recommend_day = '20170403' AND t.utdid IS NOT NULL LIMIT 0, 10000