w MySQL, historycznie GROUP BY było również używane do sortowania. Jeśli zapytanie zostało określone GROUP BY, wynik został posortowany tak, jakby ORDER BY był obecny w zapytaniu.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
mysql-5.7> CREATE TABLE t (id INTEGER, cnt INTEGER);
Query OK, 0 rows affected (0.03 sek.)
mysql-5.7> WSTAW W WARTOŚCI t (4,1),(3,2),(1,4),(2,2),(1,1),(1,5),(2,6),(2,1),(1,3),(3,4),(4,5),(3,6);
Żądanie OK, 12 wierszy wpływ (0,02 sekundy)
Rekordów: 12 Duplikatów: 0 Ostrzeżeń: 0
mysql-5.7> WYBIERZ IDENTYFIKATOR, KWOTA(cnt) Z GRUPY t identyfikatora;
+——+———-+
| id | SUMA(cnt) |
+——+———-+
| 1 | 13 |
| 2 | 9 |
| 3 | 12 |
| 4 | 6 |
+——+———-+
4 wiersze w zbiorze (0.00 sec)
|
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).
|
1
2
3
4
5
6
7
8
9
10
|
mysql-5.7> SELECT id, SUM(cnt) FROM t GROUP BY id DESC;
+——+———-+
/ id/SUM (cnt) /
+——+———-+
| 4 | 6 |
| 3 | 12 |
| 2 | 9 |
| 1 | 13 |
+——+———-+
4 wiersze w zestawie, 1 Ostrzeżenie (0.00 sec)
|
to się zmieniło w 8.0, ponieważ nie obsługuje już ani ukrytego, ani jawnego sortowania grup według. W tym poście na blogu wyjaśnię, dlaczego ta zmiana stała się konieczna, a także pracę wykonaną jako prekursor tej zmiany.
Grupuj w MySQL
aby grupować zbiór wierszy, MySQL optimizer wybiera różne metody. Jednym z nich jest sortowanie wierszy przed ich grupowaniem. Ułatwia to grupowanie jednej grupy po drugiej. Staje się również niedrogi, jeśli istnieje indeks, który może być użyty do uzyskania posortowanych wierszy. Jeśli nie ma indeksu, MySQL optimizer może nadal zdecydować się na zewnętrzne (filesort) sortowanie przed grupowaniem.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
|
MySQL-5.7
*************************** 1. rząd ***************************
id: 1
Typ wyboru: prosty
tabela: T
sekcje: null
kolejność: wszystkie
możliwe klucze: null
klucz: null
key_len: null
link: NULL
rows: 12
filtered: 100.00
Extra: Using filesort
1 row in set, 1 warning (0.01 sec)
mysql-5.7> ALTER TABLE t ADD INDEX (id, cnt);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql-5.7> EXPLAIN SELECT id, SUM(cnt) FROM t GROUP BY id \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
partitions: NULL
Typ: index
possible_keys: id
klucz: id
key_len: 10
ref: null
wiersze: 12
filtrowane: 100.00
dodatkowe: użycie indeksu
1 wiersz w zestawie, 1 warning (0.00 sec)
|
jak widać w przykładzie, przed dodaniem indeksu do tabeli, MySQL używa zewnętrznego sortowania do grupowania. Dla przykładowego zapytania wymusiłem Plan za pomocą SQL_BIG_RESULT (ponieważ MySQL nie wybierze tego planu dla zbioru danych, który mamy). Ale MySQL użyłby tego planu do grupowania w przypadku braku indeksu, aby uzyskać posortowane wiersze, a użycie tymczasowej tabeli staje się kosztowne z powodu dużej liczby grup. Po dodaniu indeksu ucieka się do używania indeksu do grupowania.
ale posortowanie wierszy przed grupowaniem nie jest konieczne. Optimizer może zdecydować się na użycie tymczasowej tabeli, aby to zrobić. Każdy wiersz w tej tabeli będzie wierszem zgrupowanym, a przy każdym wierszu przychodzącym wiersz odpowiadający tej grupie w tabeli jest aktualizowany. Sortowanie nie jest tutaj potrzebne. Jednak ponieważ oczekiwano sortowania grup w MySQL, zmuszono go do sortowania pogrupowanych wierszy nawet w tym przypadku .
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
w MySQL-5.7> ALTER TABLE t drop INDEX id;
zapytanie OK, 0 dotkniętych wierszy (0.01 sec)
rekordów: 0 duplikaty: 0 Ostrzeżenia: 0
mysql-5.7> wyjaśnij wybierz id, sumę(cnt) z grupy t według id \g
*************************** 1. wiersz ***************************
id: 1
select_type: SIMPLE
table: t
partycje: NULL
type: ALL
possible_keys: NULL
key: null
key_len: NULL
ref: null
wiersze: 12
filtrowane: 100.00
Extra: Using temporary; Using filesort
1 wiersz w zestawie, 1 Ostrzeżenie (0.00 sec)
|
w przykładowym zapytaniu widzimy, że chociaż używana jest tabela tymczasowa, MySQL nadal wykonuje zewnętrzne sortowanie. Użytkownicy musieliby wyraźnie określić ORDER BY NULL, aby poinformować MYSQL, że GROUP BY nie musi sortować. Tak więc, aby przeciwdziałać efektowi innego niestandardowego rozszerzenia (grupa przez sortowanie), potrzebna była składnia niestandardowa (ORDER BY null). Jest o wiele czystsze teraz, kiedy wyeliminowaliśmy ten bałagan.
usunięcie domyślnego sortowania dla grupy przez
jakiś czas temu próbowałem naprawić błąd 71804. Reporter oczekiwał, że MySQL nie zrobi niepotrzebnego sortowania plików, które robił dla grup. Próba poprawienia błędu uświadomiła nam, że optymalizacja tej konkretnej sytuacji nie jest zbyt prosta ze względu na wsparcie dla niejawnego i jawnego sortowania tej grupy. Więc doszliśmy do wniosku, że zanim ta optymalizacja może być wykonana, powinniśmy być re-factoring kodu związanego z sortowaniem dla grupy według.
pierwszym krokiem było usunięcie domyślnego sortowania dla GROUP BY. Jak wspomniano w instrukcji obsługi tutaj, zdecydowano się usunąć wsparcie dla niego jakiś czas temu . Zostało to wykonane w ramach funkcji indeksowania malejącego w wersji 8.0.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
mysql> WYBIERZ identyfikator KWOTĘ(cnt) Z GRUPY t IDENTYFIKATORA;
+——+———-+
| id | SUMA(cnt) |
+——+———-+
| 4 | 6 |
| 3 | 12 |
| 1 | 13 |
| 2 | 9 |
+——+———-+
4 wierszy w zestawie (0,00 sekundy)
mysql> WYJAŚNIJ IDENTYFIKATOR WYBORU, KWOTĘ (cnt) Z GRUPY t identyfikatora \G
*************************** 1. rząd ***************************
id: 1
Typ wyboru: prosta
tabela: t
partycje: NULL
Typ: wszystkie
possible_keys: NULL
klucz: null
key_len: NULL
ref: NULL
wiersze: 12
filtrowane: 100.00
dodatkowe: za pomocą tymczasowy
1 wiersz w zestawie, 1 Ostrzeżenie (0.00 sec)
|
jak widać w powyższym przykładzie, sortowanie nie jest wykonywane dla zapytania. W rezultacie pogrupowane wiersze nie są sortowane w wyniku końcowym. Jeśli użytkownicy potrzebują posortowanych wierszy, muszą określić kolejność według W zapytaniu.
w MySQL 5.7 i wersje poniżej, użytkownicy znajdują następujące ostrzeżenie w instrukcji.
„GROUP BY domyślnie sortuje Domyślnie (to znaczy w przypadku brakuASC lubDESC oznaczenia kolumnGROUP BY). Jednakże, opierając się na niejawnym GROUP BY sortowaniu (to znaczy sortowaniu przy braku ASC lub DESC) lub jawnym sortowaniu dla GROUP BY (to znaczy przy użyciu jawnego ASC lub DESC oznaczenia dla kolumn GROUP BY są przestarzałe. Aby utworzyć daną kolejność sortowania, należy podać klauzulęORDER BY. „
usunięcie jawnego sortowania dla grupy przez
kiedy przyszło do usunięcia jawnego sortowania, było to nieco trudniejsze. Nie mogliśmy go usunąć, chyba że MySQL obsługiwał zamówienie przez ROLLUP. ROLLUP z ORDER BY nie był dozwolony w MySQL 5.7 i wcześniejszych wersjach. Tak więc, jako alternatywa, użytkownicy używali GROUP BY ASC / DESC do sortowania danych za pomocą ROLLUP (chociaż sortowanie było bardzo restrykcyjne, ponieważ wiersze super agregujące zawsze umieszczane były po wierszach używanych do obliczenia ich w przypadku ASC i odwrotnie dla DESC). Musieliśmy znieść to ograniczenie, zanim usunęliśmy wsparcie dla jawnego sortowania dla GROUP BY.
MySQL pozwala teraz zamawiać przez ROLLUP. Wyjaśniłem szczegółowo, w jaki sposób można skorzystać z tej poprawy. Jak wyjaśniono na tym samym blogu, jeśli użytkownicy chcą dokładnie taką samą kolejność sortowania wartości null jak w MySQL 5.7 dla ROLLUP, powinni użyć funkcji GROUPING (), aby ponownie napisać zapytanie w prosty sposób.
tak w skrócie zrobiliśmy następujące rzeczy jako prekursory usuwania jawnego sortowania dla grupy by.
1. Dodanie funkcji GROUPING ()
2. Usunięcie domyślnego sortowania dla grupy przez
3. Zezwalając na kolejność przez ROLLUP
i wreszcie usunęliśmy jawne sortowanie dla grupy Przez w MySQL 8.0.13.
pytaliśmy kiedyś o opinię społeczności. Doszliśmy do wniosku, że użytkownicy, którzy byli świadomi tego niestandardowego rozszerzenia, które dostarczył MySQL, nie mieli nic przeciwko temu, że zniknie.
wnioski
mimo, że mamy jeszcze trochę pracy do zrobienia zanim naprawimy błąd 71804, cieszymy się, że udało nam się to zrobić. Daj nam znać swoje myśli. Dziękujemy za korzystanie z MySQL!