SQL volgorde van bewerkingen – In welke volgorde voert MySQL queries uit?
Klik om te beginnen, gratis.
Kennis van de bits en bytes van de volgorde van een SQL query kan zeer waardevol zijn, omdat het het proces van het schrijven van nieuwe queries kan vergemakkelijken, terwijl het ook zeer gunstig kan zijn wanneer u probeert een SQL query te optimaliseren.
Als u op zoek bent naar de korte versie, dit is de logische volgorde van bewerkingen, ook wel bekend als de volgorde van uitvoering, voor een SQL query:
- VAN, inclusief JOINs
- WHERE
- GROUP BY
- HAVING
- WINDOW functies
- SELECT
- DISTINCT
- UNION
- ORDER BY
- LIMIT en OFFSET
Maar de werkelijkheid is niet zo eenvoudig of rechtlijnig. Zoals we al zeiden, definieert de SQL-standaard de volgorde van uitvoering voor de verschillende SQL-queryclausules. Moderne databases zijn echter al bezig om die standaardvolgorde aan te tasten door een aantal optimalisatietrucs toe te passen die de werkelijke volgorde van uitvoering kunnen veranderen, hoewel ze uiteindelijk hetzelfde resultaat moeten opleveren als wanneer ze de query in de standaardvolgorde zouden uitvoeren.
Waarom zouden ze dat doen? Nou, het kan dom zijn als de database eerst alle gegevens zou ophalen die in de FROM-clausule worden genoemd (inclusief de JOIN’s), voordat wordt gekeken naar de WHERE-clausule en de bijbehorende indexen. Die tabellen kunnen veel gegevens bevatten, dus je kunt je voorstellen wat er gebeurt als de database-optimalisator zich zou houden aan de traditionele volgorde van bewerkingen van een SQL-query.
Laten we eens kijken naar elk van de SQL-query-onderdelen volgens hun uitvoeringsvolgorde.
FROM en JOIN’s
De tabellen die in de FROM-clausule worden genoemd (inclusief JOIN’s), worden eerst geëvalueerd, om de volledige werkset te bepalen die relevant is voor de query. De database voegt de gegevens uit alle tabellen samen, volgens de JOINs ON-clausules, terwijl ook gegevens uit de subquery’s worden opgehaald, en maakt misschien zelfs enkele tijdelijke tabellen aan om de gegevens op te slaan die uit de subquery’s in deze clausule worden teruggezonden.
In veel gevallen zal de database-optimalisator er echter voor kiezen om eerst het WHERE-gedeelte te evalueren, om te zien welk deel van de werkverzameling kan worden weggelaten (bij voorkeur met behulp van indexen), zodat de gegevensverzameling niet te veel wordt opgeblazen als dat niet echt nodig is.
WERE-clausule
De WHERE-clausule wordt als tweede geëvalueerd, na de FROM-clausule. We hebben de werkende gegevensverzameling op zijn plaats, en nu kunnen we de gegevens filteren volgens de voorwaarden in de WHERE-clausule.
Deze voorwaarden kunnen verwijzingen bevatten naar de gegevens en tabellen uit de FROM-clausule, maar kunnen geen verwijzingen bevatten naar aliassen die in de SELECT-clausule zijn gedefinieerd, omdat die gegevens en die aliassen in die context nog niet mogen “bestaan”, omdat die clausule nog niet door de database is geëvalueerd.
Ook zou een veel voorkomende valkuil voor de WHERE-clausule zijn om te proberen geaggregeerde waarden in de WHERE-clausule uit te filteren, bijvoorbeeld met deze clausule: “WHERE sum(available_stock) > 0”. Dit statement zal de query uitvoering doen mislukken, omdat aggregaties later in het proces geëvalueerd zullen worden (zie de GROUP BY sectie hieronder). Als u filtervoorwaarden wilt toepassen op geaggregeerde gegevens, moet u de HAVING-clausule gebruiken en niet de WHERE-clausule.
GROUP BY-clausule
Nu we de gegevensverzameling hebben gefilterd met de WHERE-clausule, kunnen we de gegevens aggregeren op basis van een of meer kolommen die voorkomen in de GROUP BY-clausule. Het groeperen van de gegevens is eigenlijk het opsplitsen in verschillende brokken of emmers, waarbij elke emmer een sleutel heeft en een lijst van rijen die overeenkomen met die sleutel. Als je geen GROUP BY-clausule hebt, is het alsof je alle rijen in één grote emmer stopt.
Als je de gegevens eenmaal hebt geaggregeerd, kun je nu aggregatiefuncties gebruiken om een waarde per groep terug te geven voor elk van de emmers. Dergelijke aggregatiefuncties omvatten COUNT, MIN, MAX, SUM en andere.
HAVING-clausule
Nu we de gegevens hebben gegroepeerd met behulp van de GROUP BY-clausule, kunnen we de HAVING-clausule gebruiken om sommige emmers eruit te filteren. De voorwaarden in de HAVING-clausule kunnen verwijzen naar de aggregatiefuncties, dus het voorbeeld dat niet werkte in de WHERE-clausule hierboven, zal prima werken in de HAVING-clausule: “HAVING sum(available_stock) > 0”.
Omdat we de gegevens al hebben gegroepeerd, hebben we op dit punt geen toegang meer tot de oorspronkelijke rijen, dus we kunnen alleen voorwaarden toepassen om hele emmers te filteren, en niet afzonderlijke rijen in een emmer.
Ook kunnen, zoals we in eerdere secties hebben vermeld, aliassen die in de SELECT-clausule zijn gedefinieerd, niet meer in de sectie worden benaderd, omdat ze nog niet door de database zijn geëvalueerd (dit geldt voor de meeste databases).
Vensterfuncties
Als je Window-functies gebruikt, is dit het punt waar ze worden uitgevoerd. Net als het groeperingsmechanisme, voeren Window functies ook een berekening uit op een set rijen. Het belangrijkste verschil is dat bij het gebruik van Window functies, elke rij zijn eigen identiteit zal behouden en niet zal worden gegroepeerd in een emmer van andere soortgelijke rijen.
Window functies kunnen alleen worden gebruikt in de SELECT of de ORDER BY clausule. U kunt aggregatiefuncties binnen de Window-functies gebruiken, bijvoorbeeld:
SUM(COUNT(*)) OVER ()
SELECT-clausule
Nu we klaar zijn met het verwijderen van rijen uit de gegevensverzameling en het groeperen van de gegevens, kunnen we de gegevens selecteren die we uit de query naar de clientkant willen halen. U kunt kolom namen, aggregaties en subqueries gebruiken in de SELECT clausule. Als je een verwijzing naar een aggregatiefunctie gebruikt, zoals COUNT(*) in de SELECT-clausule, is dat slechts een verwijzing naar een aggregatie die al heeft plaatsgevonden toen de groepering plaatsvond. De aggregatie zelf vindt dus niet plaats in de SELECT-clausule, maar dit is slechts een verwijzing naar de resultatenset.
DISTINCT keyword
De syntaxis van het DISTINCT keyword is een beetje verwarrend, omdat het keyword in de SELECT-clausule vóór de kolomnamen komt te staan. Maar de eigenlijke DISTINCT operatie vindt plaats na de SELECT. Bij gebruik van het DISTINCT trefwoord zal de database rijen met dubbele waarden weggooien uit de rijen die overblijven nadat de filtering en aggregaties hebben plaatsgevonden.
UNION trefwoord
Het UNION trefwoord combineert de resultaatreeksen van twee queries tot één resultaatreeks. In de meeste databases kunt u kiezen tussen UNION DISTINCT (waarmee dubbele rijen uit de gecombineerde resultaatverzameling worden verwijderd) of UNION ALL (waarmee alleen de resultaatverzamelingen worden gecombineerd zonder enige controle op doublures).
U kunt sorteren (ORDER BY) en beperken (LIMIT) toepassen op de resultaatverzameling van de UNION, op dezelfde manier waarop u dat op een gewone query kunt doen.
ORDER BY-clausule
Sorteren vindt plaats zodra de database de hele resultaatverzameling gereed heeft (na filtering, groepering, verwijdering van dubbels). Zodra we dat hebben, kan de database nu de resultaatverzameling sorteren met kolommen, geselecteerde aliassen, of aggregatiefuncties, zelfs als ze geen deel uitmaken van de geselecteerde gegevens. De enige uitzondering is het gebruik van het trefwoord DISTINCT, dat voorkomt dat wordt gesorteerd op een niet-geselecteerde kolom, omdat in dat geval de volgorde van de resultatenverzameling ongedefinieerd zal zijn.
U kunt ervoor kiezen om de gegevens te sorteren met een aflopende (DESC) of een oplopende (ASC) volgorde. De volgorde kan uniek zijn voor elk van de orderonderdelen, dus het volgende is geldig: ORDER BY firstname ASC, age DESC
LIMIT en OFFSET
In de meeste use cases (met uitzondering van een paar zoals rapportage), zouden we alle rijen behalve de eerste X rijen van het resultaat van de query willen weggooien. De LIMIT clausule, die wordt uitgevoerd na het sorteren, staat ons toe om precies dat te doen. Bovendien kunt u kiezen vanaf welke rij de gegevens moeten worden opgehaald en hoeveel er moeten worden uitgesloten, door gebruik te maken van een combinatie van de LIMIT en OFFSET sleutelwoorden. Het volgende voorbeeld zal 50 rijen ophalen vanaf rij #100: LIMIT 50 OFFSET 100
Klik om te beginnen, gratis.