in MySQL werd historisch gezien groep door ook gebruikt om sortering aan te bieden. Als een query opgegeven groep door, het resultaat werd gesorteerd alsof volgorde door aanwezig was in de query.
|
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 sec)
mysql-5.7> INVOEGEN IN de t-WAARDEN (4,1),(3,2),(1,4),(2,2),(1,1),(1,5),(2,6),(2,1),(1,3),(3,4),(4,5),(3,6);
Query OK, 12 rijen beïnvloed (0.02 seconden)
Records: 12 Duplicaten: 0 Waarschuwingen: 0
mysql-5.7> SELECT id, SUM(cnt) VAN t-GROEP DOOR id;
+——+———-+
| id | SOM(cnt) |
+——+———-+
| 1 | 13 |
| 2 | 9 |
| 3 | 12 |
| 4 | 6 |
+——+———-+
4 rows in set (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 | SOM(cnt) |
+——+———-+
| 4 | 6 |
| 3 | 12 |
| 2 | 9 |
| 1 | 13 |
+——+———-+
4 rijen in set, 1 waarschuwing (0.00 sec)
|
Dit is veranderd in een 8,0 als het niet langer ondersteunt, hetzij uitdrukkelijk of impliciet vermeld is het sorteren van de GROEP DOOR. In deze blogpost zal ik uitleggen waarom deze verandering noodzakelijk werd en ook het werk dat gedaan werd als voorloper van deze verandering.
groeperen door in MySQL
om een reeks rijen te groeperen, kiest MySQL optimizer verschillende methoden. Een van hen is om de rijen te sorteren voordat ze worden gegroepeerd. Dit maakt het gemakkelijk om de ene groep na de andere te groeperen. Het wordt ook goedkoop als er een index is die kan worden gebruikt om gesorteerde rijen te krijgen. Als er geen index is, kan MySQL optimizer nog steeds beslissen om externe (filesort) sortering uit te voeren alvorens te groeperen.
|
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> UITLEGGEN SELECTEER SQL_BIG_RESULT id, SUM(cnt) VAN t-GROEP DOOR id \G
*************************** 1. rij ***************************
id: 1
select_type: SIMPLE
table: t
partities: NULL
volgorde: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: 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
type: index
possible_keys: id
key: id
key_len: 10
ref: NULL
rijen: 12
gefilterd: 100.00
Extra: Index
1 rij in set, 1 waarschuwing (0.00 sec)
|
zoals te zien is in het voorbeeld, gebruikt MySQL voor het toevoegen van index aan de tabel, externe sortering om te groeperen door. Voor de voorbeeldquery heb ik het plan geforceerd met behulp van SQL_BIG_RESULT (omdat MySQL dit plan niet zal kiezen voor de dataset die we hebben). Maar MySQL zou dit plan gebruiken om te groeperen in de afwezigheid van een index om gesorteerde rijen te krijgen en het gebruik van tijdelijke tabel wordt kostbaar vanwege het grote aantal groepen. Zodra de index is toegevoegd, het toevlucht tot het gebruik van de index te doen groep door.
maar rijen sorteren voor groeperen is geen noodzaak. Optimizer kan besluiten om gebruik te maken van een tijdelijke tafel om het te doen. Elke rij in deze tabel zou een gegroepeerde rij zijn en met elke inkomende rij wordt de rij die overeenkomt met die groep in de tabel bijgewerkt. Sorteren is hier niet nodig. Echter, als groep door in MySQL werd verwacht om te sorteren, werd het gedwongen om de gegroepeerde rijen te sorteren, zelfs in dit geval .
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
mysql-5.7> ALTER TABLE t DROP INDEX id;
Query OK, 0 rijen beïnvloed (0.01 sec)
Records: 0 duplicaten: 0 Waarschuwingen: 0
mysql-5.7> EXPLAIN SELECT id, SUM(cnt) FROM T GROUP BY id \G
*************************** 1. rij ***************************
id: 1
select_type: EENVOUDIG
tabel: t
partities: NULL
type: ALL
possible_keys: NULL
sleutel: NULL
key_len: NULL
ref: NULL
rijen: 12
gefilterd: 100.00
Extra: gebruik van tijdelijk; gebruik van bestandssort
1 rij in set, 1 waarschuwing (0.00 sec)
|
in de voorbeeldquery kunnen we zien dat, hoewel tijdelijke tabel wordt gebruikt, MySQL nog steeds doet externe sortering. Gebruikers zouden expliciet De volgorde van NULL moeten specificeren om MYSQL te laten weten dat de groep niet hoeft te sorteren. Dus een niet-standaard (ORDER BY NULL) syntaxis was nodig om het effect van een andere niet-standaard extensie (groep door Sorteren) tegen te gaan. Het is veel schoner nu we die rotzooi hebben geëlimineerd.
verwijderen van impliciete sortering voor groep door
enige tijd geleden probeerde ik bug 71804 te repareren. De verslaggever verwachtte dat MySQL niet de onnodige file-Sorteer het deed voor groep door te doen. Proberen om een patch te maken voor de bug deed ons beseffen dat het optimaliseren van deze specifieke situatie niet erg eenvoudig is vanwege de ondersteuning voor impliciete en expliciete sortering van die groep op voorwaarde. Dus we concludeerden dat voordat deze optimalisatie kon worden gedaan, moeten we opnieuw factoring code met betrekking tot Sorteren voor groep door.
de eerste stap om dat te doen was het verwijderen van de impliciete sortering voor de groep door. Zoals vermeld in de gebruikershandleiding hier, werd besloten om de ondersteuning voor het enige tijd terug te verwijderen . Het is gedaan als onderdeel van de aflopende index functie in 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> SELECT id, SUM(cnt) VAN t-GROEP DOOR id;
+——+———-+
| id | SOM(cnt) |
+——+———-+
| 4 | 6 |
| 3 | 12 |
| 1 | 13 |
| 2 | 9 |
+——+———-+
4 rows in set (0.00 sec)
mysql> UITLEGGEN SELECT id, SUM(cnt) VAN t-GROEP DOOR id \G
*************************** 1. rij ***************************
id: 1
select_type: eenvoudige
tabel: t
partities: NULL
type: ALL
possible_keys: NULL
– toets: NULL
key_len: NULL
ref: NULL
rijen: 12
gefilterd: 100.00
Extra: Het gebruik van tijdelijke
1 row in set, 1 waarschuwing (0.00 sec)
|
Zoals te zien is in het bovenstaande voorbeeld, het sorteren is niet uitgevoerd voor de query. Als gevolg hiervan worden gegroepeerde rijen niet gesorteerd in het eindresultaat. Als gebruikers gesorteerde rijen nodig hebben, moeten ze de volgorde opgeven in de query.
In MySQL 5.7 en onder versies, gebruikers vinden de volgende waarschuwing in de handleiding.
“GROUP BY sorteert impliciet standaard (dat wil zeggen, bij afwezigheid van ASC of DESC aanwijzers voor GROUP BY kolommen). Echter, met een beroep op de impliciete GROUP BY sorteren (dat is, het sorteren in de afwezigheid van ASC of DESC – aanduidingen) of expliciete sorteren voor het GROUP BY (dat is, door het gebruik van expliciete ASC of DESC aanduidingen voor GROUP BY kolommen) is verouderd. Om een bepaalde sorteervolgorde te produceren, moet een ORDER BY clausule worden opgegeven. “
verwijderen van expliciete sortering voor groep door
toen het ging om het verwijderen van expliciete sortering, was het een beetje lastiger om het te doen. We konden het niet verwijderen, tenzij MySQL ondersteunde volgorde door met ROLLUP. ROLLUP met ORDER BY was niet toegestaan in MySQL 5.7 en eerdere versies. Dus, als alternatief, gebruikers zouden gebruiken groep door ASC / DESC om gesorteerde gegevens met ROLLUP (hoewel de sortering was zeer restrictief met super aggregate rijen altijd geplaatst na de rijen gebruikt om ze te berekenen in het geval van ASC en vice versa voor DESC). We moesten deze beperking opheffen voordat we de ondersteuning voor expliciete sortering voor groep door verwijderden.
MySQL staat nu volgorde toe met ROLLUP. Ik heb hier uitvoerig uitgelegd hoe we van deze verbetering gebruik kunnen maken. Zoals uitgelegd in dezelfde blog, als gebruikers exact dezelfde sorteervolgorde van NULLs willen als die van MySQL 5.7 voor ROLLUP, moeten ze de GROUPING() functie gebruiken om de query op een eenvoudige manier te herschrijven.
dus in het kort hebben we de volgende dingen gedaan als voorlopers van het verwijderen van expliciete sortering voor groep door.
1. Het toevoegen van groeperen () functie
2. Verwijdering van impliciete sortering voor groep met
3. Het toestaan van volgorde door met ROLLUP
en tot slot hebben we expliciete sortering voor groep verwijderd door in MySQL 8.0.13.
we hebben enige tijd geleden om de mening van de gemeenschappen gevraagd. We concludeerden dat gebruikers die zich bewust waren van deze niet-standaard extensie die MySQL verstrekt waren prima met het weggaan.
conclusie
hoewel we nog wat werk te doen hebben voordat we bug 71804 repareren, zijn we blij dat we dit gedaan hebben. Laat ons uw mening weten. Bedankt voor het gebruik van MySQL!