min introduksjon til databaser og PostgreSQL var for webapplikasjonsutvikling og statistisk analyse. Jeg lærte akkurat NOK SQL for å få spørringene til å returnere de riktige svarene. På grunn Av Mitt arbeid Med PostGIS (OG FOSS4G) ble jeg venner Med Paul Ramsey. Vi er nå medarbeidere På Crunchy Data, og han hjelper meg opp MIN SQL-fu. En av de første leksjonene han lærte meg var «Prøv å bruke joins i stedet for subqueries.»

Dagens innlegg skal jobbe gjennom dette rådet, Da Paul og Jeg jobber gjennom NOEN SQL.

Hva Vi Prøver å Svare

jeg prøver å lage noen nye undervisnings-og snakkematerialer om BRUK AV SQL i datavitenskap, og jeg jobbet med noen pre-analyse data manipulasjon. Jeg hadde et bord, fire_weather, som er en delmengde av værbordet, og jeg vil finne alle oppføringene i vær som ikke er i fire_weather. Mitt første instinkt var å skrive en subquery, men dette virket som en grei og enkel spørring for å følge Pauls» bruk en bli med » råd.

Feil Sammenføyningsspørring

jeg startet med denne sammenføyningsspørringen:

 select count(weather.*) from weather, fire_weather where weather.id != fire_weather.id;

Og det fungerte ikke (ellers ville jeg ikke skrive dette blogginnlegget). Det viser seg at dette gjør et kryss delta der vi ender opp med alle parvise kombinasjoner av alle rader i begge tabellene.

Den Riktige Sammenføyningsspørringen

Så På dette punktet slår Jeg Opp (I motsetning til å ringe Opp på telefonen) Paul Og vi begynner å diskutere hvordan du gjør riktig sammenføyning. Det viser seg at riktig syntaks er:

select count(weather.*)from weather left join fire_weather on weather.id = fire_weather.idwhere fire_weather.id is null;

I Utgangspunktet gjør du en venstre ytre sammenføyning, og gir deg alle rader fra værbordet og bare fire_weather-oppføringene som passer. Deretter filtrerer du ut alle postene der det er kamper for fire_weather. Ganske enkelt å forstå, men ikke veldig sett som, som ved bruk av settteori (som er grunnlaget for relasjoner i relasjonsdatabasesystemer). Så igjen har vi nå en arbeidsgruppe spørring.

Analyser Dette

som en del av min reise til større forståelse AV SQL I PostgreSQL, har jeg blitt en stor fan AV EXPLAIN ANALYZE for for timings og ser på spørringsplanen. Bare ut av nysgjerrighet bestemmer jeg meg for å se på timingen og spørringsplanen for sammenføyningsspørringen. Her er utgangen, og det tok omtrent 7 millisekunder med en noe komplisert spørringsplan:

Aggregate (cost=1358.58..1358.59 rows=1 width=8) (actual time=6.780..6.781 rows=1 loops=1)
-> Hash Anti Join (cost=1168.76..1358.58 rows=1 width=64) (actual time=1.648..6.218 rows=6802 loops=1)
Hash Cond: (weather.id = fire_weather.id)
-> Seq Scan on weather (cost=0.00..159.05 rows=8205 width=68) (actual time=0.008..3.157 rows=8205 loops=1)
-> Hash (cost=785.56..785.56 rows=30656 width=4) (actual time=1.609..1.609 rows=1403 loops=1)
Buckets: 32768 Batches: 1 Memory Usage: 306kB
-> Seq Scan on fire_weather (cost=0.00..785.56 rows=30656 width=4) (actual time=0.008..1.399 rows=1403 loops=1)
Planning Time: 0.110 ms
Execution Time: 6.807 ms

Nå Subquery

Og nå ønsket jeg å se hvordan min opprinnelige ide for en subquery ville utføre. Her er subquery måten å svare på det samme spørsmålet:

select count(weather.*) from weather where id not in (select id from fire_weather);

Mer Analyse

du bør se hvorfor denne spørringen appellerte til meg, det er veldig satt basert og veldig enkelt å skrive. Når jeg ser på denne spørringen med explain analyze får jeg:

Aggregate (cost=1052.02..1052.03 rows=1 width=8) (actual time=7.458..7.459 rows=1 loops=1)
-> Seq Scan on weather (cost=862.20..1041.76 rows=4102 width=64) (actual time=1.139..6.727 rows=6802 loops=1)
Filter: (NOT (hashed SubPlan 1))
Rows Removed by Filter: 1403
SubPlan 1
-> Seq Scan on fire_weather (cost=0.00..785.56 rows=30656 width=4) (actual time=0.004..0.643 rows=1403 loops=1)
Planning Time: 0.068 ms
Execution Time: 7.497 ms

Spørringssammenligning

så vi ender med en veldig enkel plan og timings som er omtrent det samme som sammenføyningen. Paul og jeg diskuterte hvorfor timings kan være så like, og vi kom opp med minst to grunner:

  1. datasettet har svært få rader (8k), slik at subquery-ytelsen kan forringes med et større datasett.
  2. min maskin har nvme diskstasjoner som gir sekvensiell tilgang en enda større ytelsesforskjell.

Et Annet Sett Basert Subquery

Endelig Paul, kom opp med en mer sett basert spørring for å svare på det samme spørsmålet:

select * from weather exceptselect weather.* from weather join fire_weather on weather.id = fire_weather.id

Denne bruker en NY SQL-klausul, BORTSETT FRA, som er en del av set operation query combiners. En stor tilbakeholdenhet på disse spørringene er at spørringene på hver side av unntaksbestemmelsen må returnere de samme kolonnene og datatypene. Dette forklarer hvorfor denne spørringen ikke kan returnere totalt radantall. Men denne spørringen viste seg å være verre i ytelse og en mye mer komplisert spørringsplan:

HashSetOp Except (cost=0.00..1984.13 rows=8205 width=74) (actual time=10.382..11.171 rows=6802 loops=1)
-> Append (cost=0.00..1532.86 rows=16410 width=74) (actual time=0.021..5.867 rows=9608 loops=1)
" -> Subquery Scan on ""*SELECT* 1"" (cost=0.00..241.10 rows=8205 width=44) (actual time=0.021..2.300 rows=8205 loops=1)"
-> Seq Scan on weather (cost=0.00..159.05 rows=8205 width=40) (actual time=0.006..0.537 rows=8205 loops=1)
" -> Subquery Scan on ""*SELECT* 2"" (cost=261.61..1209.71 rows=8205 width=44) (actual time=1.796..2.788 rows=1403 loops=1)"
-> Hash Join (cost=261.61..1127.66 rows=8205 width=40) (actual time=1.795..2.583 rows=1403 loops=1)
Hash Cond: (fire_weather.id = weather_1.id)
-> Seq Scan on fire_weather (cost=0.00..785.56 rows=30656 width=4) (actual time=0.008..0.413 rows=1403 loops=1)
-> Hash (cost=159.05..159.05 rows=8205 width=40) (actual time=1.768..1.768 rows=8205 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 765kB
-> Seq Scan on weather weather_1 (cost=0.00..159.05 rows=8205 width=40) (actual time=0.002..0.518 rows=8205 loops=1)
Planning Time: 0.127 ms
Execution Time: 11.975 ms

Overraskelsen Vri

da tenkte jeg litt mer om spørringen Paul foreslo og innså at vi egentlig ikke trengte å bli med på høyre side av unntaksbestemmelsen. Siden fire_weather inneholder alle de samme kolonnene som været, kan vi bare bruke kolonnene vi vil ha og få svaret vi forventet.

select id from weather exceptselect id from fire_weather;

Nå har dette fint sett syntaks som gjør det veldig enkelt å forstå. Hvis vi ønsket å faktisk få tellingen som i de andre spørringene, kan vi pakke inn spørringen i EN CTE.

with count_me as (select id from weather exceptselect id from fire_weather)select count(*) from count_me;

med denne gyldne billetten får vi 6 ms spørringstider og en spørringsplan som er renere, men ikke enkleste. Jeg bør merke seg at renslighet og enkelhet ikke er viktige faktorer i å vurdere en spørr plan.

HashSetOp Except (cost=0.00..1624.68 rows=8205 width=8) (actual time=4.834..5.313 rows=6802 loops=1)
-> Append (cost=0.00..1527.52 rows=38861 width=8) (actual time=0.004..3.248 rows=9608 loops=1)
-> Subquery Scan on "*SELECT* 1" (cost=0.00..241.10 rows=8205 width=8) (actual time=0.004..1.903 rows=8205 loops=1)
-> Seq Scan on weather (cost=0.00..159.05 rows=8205 width=4) (actual time=0.003..1.001 rows=8205 loops=1)
-> Subquery Scan on "*SELECT* 2" (cost=0.00..1092.12 rows=30656 width=8) (actual time=0.003..0.554 rows=1403 loops=1)
-> Seq Scan on fire_weather (cost=0.00..785.56 rows=30656 width=4) (actual time=0.002..0.409 rows=1403 loops=1)
Planning Time: 0.246 ms
Execution Time: 5.897 ms

Mine Siste Takeaways

Her er de siste leksjonene jeg vil forlate deg med fra denne lille øvelsen.

  1. aldri eyeball query ganger-disse var alle samme hastighet i øyet mitt. Forklar analysere er din venn.
  2. Skriv spørringen på den måten som gir mest mening, og gjør deretter timings. Hvis det ikke er bra, så se på et alternativ (sannsynligvis blir med)
  3. Det er flere måter å komme frem til det samme svaret i SQL – det «riktige» svaret vil være svært situasjonelt avhengig. Et par ting som vil påvirke resultatet:
    1. datastørrelsen – en spørring kan slutte å være «ok» når datastørrelsen vokser
    2. Indekser
    3. diskhastigheten
    4. Minnestørrelse
    5. Prosessorhastighet
    6. hvor ofte du planlegger å utføre spørringen
  4. Til Slutt, tid brukt på å forbedre SQL kunnskap og ferdigheter vil lønne seg vakkert. Selv om du ikke skriver de mest effektive spørringene, er de fortsatt vanligvis raskere enn å skrive mye prosesskode. Etter hvert som JEG lærer FLERE OG FLERE SQL-mønstre, er jeg mer overrasket over koden jeg kan erstatte med noen FÅ LINJER MED SQL (og jeg får vanligvis en stor ytelsesforbedring).

Legg igjen en kommentar

Din e-postadresse vil ikke bli publisert.