In MySQL, storicamente GROUP BY è stato utilizzato anche per fornire l’ordinamento. Se una query specificata GROUP BY, il risultato è stato ordinato come se ORDER BY fosse presente nella query.

MySQL here implicitly sorts the results from GROUP BY (i.e. in the absence of ASC or DESC designators for GROUP BY columns ).

MySQL also supported explicit sorting with GROUP BY (i.e. by using explicit ASC or DESC designators for GROUP BY columns).

Questo è cambiato in 8.0 poiché non supporta più o impliciti o espliciti di selezione per il GRUPPO. In questo post del blog, spiegherò perché questo cambiamento si è reso necessario e anche il lavoro svolto come precursore di questo cambiamento.

GROUP BY in MySQL

Per raggruppare un insieme di righe, MySQL optimizer sceglie metodi diversi. Uno di questi è ordinare le righe prima di raggrupparle. Questo rende facile raggruppare un gruppo dopo l’altro. Diventa anche economico se esiste un indice che potrebbe essere utilizzato per ottenere righe ordinate. Se non esiste un indice, MySQL optimizer potrebbe comunque decidere di eseguire l’ordinamento esterno (filesort) prima del raggruppamento.

Come si vede nell’esempio, prima di aggiungere indice per la tabella MySQL utilizza esterne smistamento per fare GRUPPO. Per la query di esempio ho forzato il piano usando SQL_BIG_RESULT(poiché MySQL non sceglierà questo piano per il set di dati che abbiamo). Ma MySQL userebbe questo piano per raggruppare in assenza di un indice per ottenere righe ordinate e l’utilizzo della tabella temporanea diventa costoso a causa del gran numero di gruppi. Una volta aggiunto l’indice, ricorre all’utilizzo dell’indice per fare GROUP BY.

Ma avere ordinato le righe prima del raggruppamento non è una necessità. Optimizer può decidere di utilizzare una tabella temporanea per farlo. Ogni riga in questa tabella sarebbe una riga raggruppata e con ogni riga in entrata, la riga corrispondente a quel gruppo nella tabella viene aggiornata. L’ordinamento non è necessario qui. Tuttavia, poiché ci si aspettava che GROUP BY in MySQL ordinasse, è stato costretto a ordinare le righe raggruppate anche in questo caso .

Nella query di esempio, possiamo vedere che sebbene tabella temporanea viene utilizzato, MySQL esegue ancora l’ordinamento esterno. Gli utenti dovrebbero specificare esplicitamente ORDER BY NULL per far sapere a MYSQL che GROUP BY non deve essere ordinato. Quindi era necessaria una sintassi non standard (ORDER BY NULL) per contrastare l’effetto di un’altra estensione non standard (GROUP BY sorting). È molto più pulito ora che abbiamo eliminato quel disordine.

Rimozione dell’ordinamento implicito per GROUP BY

Qualche tempo fa stavo cercando di correggere il bug 71804. Il reporter si aspettava che MySQL non eseguisse il file non necessario-ordinamento che stava facendo per GROUP BY. Provare a creare una patch per il bug ci ha fatto capire che l’ottimizzazione di questa particolare situazione non è molto semplice a causa del supporto per l’ordinamento implicito ed esplicito fornito da quel GRUPPO. Quindi abbiamo concluso che prima che questa ottimizzazione potesse essere eseguita, dovremmo ri-factoring del codice relativo all’ordinamento per GROUP BY.

Il primo passo per farlo è stato rimuovere l’ordinamento implicito per GROUP BY. Come menzionato nel manuale utente qui, è stato deciso di rimuovere il supporto per esso qualche tempo fa . È stato fatto come parte della funzione indice decrescente in 8.0.

Come visto nell’esempio precedente, l’ordinamento non viene eseguita la query. Di conseguenza, le righe raggruppate non vengono ordinate nel risultato finale. Se gli utenti necessitano di righe ordinate, devono specificare ORDER BY nella query.

In MySQL 5.7 e sotto le versioni, gli utenti trovano il seguente avviso nel manuale.

“GROUP BYordina implicitamente per impostazione predefinita (ovvero, in assenza di ASCo DESCdesignatori per GROUP BY colonne). Tuttavia, affidarsi implicita GROUP BY ordinamento (che è, per l’ordinamento, in assenza di ASC o DESC designatori) o esplicita di selezione per il GROUP BY (che è, utilizzando esplicito ASC o DESC designatori per GROUP BY colonne) è deprecato. Per produrre un determinato ordinamento, fornire una clausolaORDER BY. “

Rimozione dell’ordinamento esplicito per GROUP BY

Quando si trattava di rimuovere l’ordinamento esplicito, era un po ‘ più complicato farlo. Non potremmo rimuoverlo a meno che MySQL non supportasse ORDER BY con ROLLUP. Il ROLLUP con ORDER BY non era consentito in MySQL 5.7 e versioni precedenti. Quindi, in alternativa, gli utenti userebbero GROUP BY ASC/DESC per ottenere dati ordinati con ROLLUP (sebbene l’ordinamento fosse molto restrittivo con righe super aggregate sempre posizionate dopo le righe utilizzate per calcolarle in caso di ASC e viceversa per DESC). Abbiamo dovuto rimuovere questa limitazione prima di rimuovere il supporto per l’ordinamento esplicito per GROUP BY.

MySQL ora consente di ORDINARE con ROLLUP. Ho spiegato in dettaglio su come fare uso di questo miglioramento qui. Come spiegato nello stesso blog, se gli utenti vogliono esattamente lo stesso ordine di ordinamento dei NULL di quello di MySQL 5.7 per il ROLLUP, dovrebbero usare la funzione GROUPING() per riscrivere la query in modo semplice.

Quindi in breve abbiamo fatto le seguenti cose come precursori per rimuovere l’ordinamento esplicito per GROUP BY.

1. Aggiunta della funzione GROUPING ()

2. Rimozione dell’ordinamento implicito per GROUP BY

3. Consentendo ORDER BY con ROLLUP

E infine abbiamo rimosso l’ordinamento esplicito per GROUP BY in MySQL 8.0.13.

Abbiamo chiesto il parere delle comunità qualche tempo fa. Abbiamo concluso che gli utenti che erano a conoscenza di questa estensione non standard fornita da MySQL stavano bene con l’andare via.

Conclusione

Anche se abbiamo ancora un po ‘ di lavoro da fare prima di risolvere bug 71804, siamo felici che abbiamo ottenuto questo fatto. Fateci sapere i vostri pensieri. Grazie per aver usato MySQL!