SQL Order of Operations – Missä järjestyksessä MySQL suorittaa kyselyt?
Klikkaa aloittaaksesi, ilmaiseksi.
SQL-kyselyn operaatiojärjestyksen bittien ja tavujen tunteminen voi olla erittäin arvokasta, sillä se voi helpottaa uusien kyselyjen kirjoittamista, mutta on myös erittäin hyödyllistä, kun yrität optimoida SQL-kyselyä.
Jos etsit lyhyttä versiota, kyseessä on SQL-kyselyn looginen suoritusjärjestys, joka tunnetaan myös suoritusjärjestyksenä:
- FROM, mukaan lukien JOINit
- WHERE
- GROUP BY
- HAVING
- WINDOW-funktiot
- SELECT
- DISTINCT
- UNION
- ORDER BY
- LIMIT ja OFFSET
.
Mutta todellisuus ei ole niin helppoa eikä suoraviivaista. Kuten sanoimme, SQL-standardi määrittelee eri SQL-kyselylausekkeiden suoritusjärjestyksen. Nykyaikaiset tietokannat kyseenalaistavat jo tuota oletusjärjestystä soveltamalla joitakin optimointitemppuja, jotka saattavat muuttaa todellista suoritusjärjestystä, vaikka niiden on lopulta palautettava sama tulos kuin jos ne suorittaisivat kyselyn oletussuoritusjärjestyksessä.
Miksi ne tekisivät niin? No, se voi olla hölmöä, jos tietokanta hakisi ensin kaikki FROM-lausekkeessa mainitut tiedot (mukaan lukien JOINit), ennen kuin se tarkastelee WHERE-lauseketta ja sen indeksejä. Näissä taulukoissa voi olla paljon tietoa, joten voitte kuvitella, mitä tapahtuisi, jos tietokannan optimoija pitäytyisi SQL-kyselyn perinteisessä suoritusjärjestyksessä.
Katsotaanpa kutakin SQL-kyselyn osaa niiden suoritusjärjestyksen mukaan.
FROM- ja JOIN-taulukot
FROM-lausekkeessa mainitut taulukot (JOIN-taulukot mukaan luettuina) arvioidaan ensimmäisenä, jotta saadaan selville koko se työjoukko, joka on olennainen kyselyn kannalta. Tietokanta yhdistää tiedot kaikista taulukoista JOINs ON -lausekkeiden mukaisesti ja hakee samalla tietoja myös alakyselyistä, ja se saattaa jopa luoda joitakin väliaikaisia taulukoita pitämään sisällään tässä lausekkeessa olevien alakyselyjen palauttamat tiedot.
Tietokannan optimoija päättää kuitenkin monissa tapauksissa arvioida WHERE-osan ensin nähdäkseen, mikä osa työjoukosta voidaan jättää pois (mieluiten indeksejä käyttäen), jotta se ei paisuttaisi tietojoukkoa liikaa, jos sen ei todellakaan ole pakko.
WHERE-lauseke
WHERE-lauseke arvioidaan toisena FROM-lausekkeen jälkeen. Meillä on toimiva tietokokonaisuus, ja nyt voimme suodattaa tiedot WHERE-lausekkeessa olevien ehtojen mukaan.
Nämä ehdot voivat sisältää viittauksia FROM-lausekkeessa oleviin tietoihin ja taulukoihin, mutta ne eivät voi sisältää viittauksia SELECT-lausekkeessa määritettyihin peitenimiin, koska nämä tiedot ja peitenimet eivät välttämättä ole vielä ”olemassa” tässä yhteydessä, koska tietokanta ei ole vielä arvioinut kyseistä lauseketta.
Yleinen WHERE-lausekkeen sudenkuoppa on myös se, että WHERE-lausekkeessa yritetään suodattaa aggregoidut arvot pois, esimerkiksi tällä lausekkeella: ”WHERE sum(available_stock) > 0”. Tämä lauseke epäonnistuu kyselyn suorittamisessa, koska aggregaatit arvioidaan myöhemmin prosessissa (katso GROUP BY -osio jäljempänä). Jos haluat soveltaa suodatusehtoa aggregoituihin tietoihin, kannattaa käyttää HAVING-lauseketta eikä WHERE-lauseketta.
GROUP BY-lauseke
Nyt kun olemme suodattaneet tietokokonaisuuden WHERE-lausekkeen avulla, voimme aggregoida tietoja yhden tai useamman GROUP BY-lausekkeessa esiintyvän sarakkeen mukaan. Ryhmittely tarkoittaa itse asiassa tietojen jakamista eri lohkoihin tai kauhoihin, joissa kussakin kauhassa on yksi avain ja luettelo kyseistä avainta vastaavista riveistä. GROUP BY -lausekkeen puuttuminen on kuin laittaisi kaikki rivit yhteen valtavaan ämpäriin.
Kun olet aggregoinut tiedot, voit nyt käyttää aggregointifunktioita palauttaaksesi ryhmäkohtaisen arvon kullekin ämpäriin. Tällaisia aggregaatiofunktioita ovat COUNT, MIN, MAX, SUM ja muut.
HAVING-lauseke
Nyt kun olemme ryhmitelleet tiedot GROUP BY-lausekkeen avulla, voimme käyttää HAVING-lauseketta joidenkin ämpäreiden suodattamiseen. HAVING-lausekkeen ehdot voivat viitata aggregaatiofunktioihin, joten esimerkki, joka ei toiminut edellä WHERE-lausekkeessa, toimii hienosti HAVING-lausekkeessa: ”HAVING sum(available_stock) > 0”.
Koska olemme jo ryhmitelleet tiedot, emme pääse enää tässä vaiheessa käsiksi alkuperäisiin riveihin, joten voimme soveltaa ehtoja vain kokonaisten ämpäreiden suodattamiseen emmekä yksittäisten rivien suodattamiseen ämpäreissä.
Myös, kuten aiemmissa kappaleissa mainittiin, SELECT-lausekkeessa määriteltyjä aliaksia ei voida käyttää tässä kappaleessa, koska tietokanta ei ole vielä arvioinut niitä (tämä pätee useimmissa tietokannoissa).
Ikkunafunktiot
Jos käytät ikkunafunktioita, tässä vaiheessa ne suoritetaan. Aivan kuten ryhmittelymekanismi, myös Window-funktiot suorittavat laskutoimituksen joukolle rivejä. Tärkein ero on se, että Window-funktioita käytettäessä jokainen rivi säilyttää oman identiteettinsä eikä sitä ryhmitellä muiden samankaltaisten rivien ämpäriin.
Window-funktioita voi käyttää vain joko SELECT- tai ORDER BY -lausekkeessa. Ikkunafunktioiden sisällä voi käyttää aggregointifunktioita, esimerkiksi:
SUM(COUNT(*)) OVER ()
SELECT-lauseke
Nyt kun olemme valmiita hylkäämään rivejä tietokokonaisuudesta ja ryhmittelemään tietoja, voimme valita ne tiedot, jotka haluamme noutaa kyselystä asiakkaan puolelle. SELECT-lausekkeen sisällä voi käyttää sarakkeiden nimiä, aggregaatioita ja alikyselyjä. Muista, että jos käytät SELECT-lausekkeessa viittausta aggregaatiofunktioon, kuten COUNT(*), se on vain viittaus aggregaatioon, joka tapahtui jo ryhmittelyn aikana, joten itse aggregaatiota ei tapahdu SELECT-lausekkeessa, vaan tämä on vain viittaus sen tulosjoukkoon.
DISTINCT-avainsana
DISTINCT-avainsanan syntaksi on hieman hämmentävä, koska avainsana ottaa SELECT-lausekkeessa paikkansa ennen sarakkeiden nimiä. Varsinainen DISTINCT-operaatio tapahtuu kuitenkin SELECT-lausekkeen jälkeen. DISTINCT-avainsanaa käytettäessä tietokanta hylkää duplikaattiarvoja sisältävät rivit niistä jäljellä olevista riveistä, jotka ovat jääneet jäljelle suodatuksen ja aggregoinnin jälkeen.
UNION-avainsana
UNION-avainsanalla yhdistetään kahden kyselyn tulosjoukot yhdeksi tulosjoukoksi. Useimmissa tietokannoissa voit valita joko UNION DISTINCT (joka hylkää päällekkäiset rivit yhdistetystä tulosjoukosta) tai UNION ALL (joka vain yhdistää tulosjoukot ilman päällekkäisyystarkastusta).
Voit soveltaa UNIONin tulosjoukkoon lajittelua (ORDER BY) ja rajoittamista (LIMIT) samalla tavalla kuin tavalliseen kyselyyn.
ORDER BY -lauseke
Lajittelu tapahtuu, kun tietokanta on saanut koko tulosjoukon valmiiksi (suodatuksen, ryhmittelyn ja päällekkäisyyksien poiston jälkeen). Kun tämä on saatu, tietokanta voi nyt lajitella tulosjoukon käyttämällä sarakkeita, valittuja aliaksia tai aggregointifunktioita, vaikka ne eivät olisikaan osa valittua dataa. Ainoa poikkeus on, kun käytetään DISTINCT-avainsanaa, joka estää lajittelun valitsemattoman sarakkeen perusteella, sillä tällöin tulosjoukon järjestys on määrittelemätön.
Voit valita, lajitteletko tiedot alenevassa (DESC) vai nousevassa (ASC) järjestyksessä. Järjestys voi olla yksilöllinen jokaiselle järjestysosalle, joten seuraava on voimassa: ORDER BY etunimi ASC, ikä DESC
LIMIT ja OFFSET
Useimmissa käyttötapauksissa (lukuun ottamatta muutamia, kuten raportointi), haluamme hylätä kaikki muut kuin kyselyn tuloksen X ensimmäistä riviä. LIMIT-lauseke, joka suoritetaan lajittelun jälkeen, mahdollistaa juuri tämän. Lisäksi LIMIT- ja OFFSET-avainsanojen yhdistelmällä voidaan valita, mistä rivistä tietojen haku aloitetaan ja kuinka monta riviä jätetään pois. Seuraava esimerkki hakee 50 riviä alkaen rivistä #100: LIMIT 50 OFFSET 100
Klikkaa aloittaaksesi, ilmaiseksi.