minun johdatus tietokantoihin ja PostgreSQL oli web-sovelluskehitykseen ja tilastolliseen analyysiin. Opin juuri tarpeeksi SQL saada kyselyt palata oikeat vastaukset. Postgisin (ja FOSS4G: n) kanssa tekemäni työn vuoksi ystävystyin Paul Ramseyn kanssa. Olemme nyt Crunchy datan työkavereita ja hän auttaa minua SQL-Fussa. Yksi ensimmäisistä opetuksista, joita hän opetti minulle, oli ”yritä käyttää liitoksia eikä alakiloja.”

tämän päivän viesti menee tämän neuvon kautta, sillä Paul ja minä työskentelemme jonkin SQL: n kautta.

mihin yritämme vastata

yritän luoda uutta opetus-ja puhemateriaalia SQL: n käytöstä datatieteessä ja työstin jonkin verran analyysiä edeltävää tietojen manipulointia. Minulla oli Taulukko, fire_weather, joka on osa sää taulukko, ja haluan löytää kaikki merkinnät sää, jotka eivät ole fire_weather. Minun alkuperäinen vaisto oli kirjoittaa subquery, mutta tämä tuntui suoraviivainen ja helppo kysely seurata Paulin ”käytä liittyä” neuvoja.

väärä Liittymispyyntö

aloitin tällä liittymispyynnöllä:

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

ja se ei toiminut (muuten en kirjoittaisi tätä blogikirjoitusta). On käynyt ilmi, että tämä ei rajat liittyä, jossa päädymme kaikki pairwise yhdistelmiä kaikkien rivien molemmissa taulukoissa.

the Proper Join-kysely

joten tässä vaiheessa minä löysään (vastakohtana puhelimen soittamiselle) Paulin ja alamme keskustella siitä, miten oikea liittyminen tehdään. Oikea syntaksi on:

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

periaatteessa voit tehdä vasen Ulompi liittyä, antaa sinulle kaikki rivit sää taulukko ja vain fire_weather merkinnät, jotka vastaavat. Sitten suodattaa kaikki tietueet, joissa on ottelut fire_weather. Melko yksinkertainen ymmärtää, mutta ei kovin joukko kuten, kuten käyttämällä joukko-oppi (joka on perusta suhteita relaatiotietokantajärjestelmissä). Toisaalta, meillä on nyt toimiva liittymiskysely.

analysoi tämä

osana matkaani kohti parempaa ymmärrystä SQL: stä PostgreSQL: ssä, olen tullut suureksi FANIKSENI selittämään ANALYZE for for ajoitukset ja tarkastelemaan kyselysuunnitelmaa. Vain uteliaisuudesta päätän katsoa ajoitus ja kyselyn suunnitelma liittyä kyselyn. Tässä on lähtö ja se kesti noin 7 millisekuntia hieman monimutkaisella kyselysuunnitelmalla:

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

nyt Alakerrasto

ja nyt halusin nähdä, miten alkuperäinen ideani alakerrasta toimisi. Tässä on subquery tapa vastata samaan kysymykseen:

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

enemmän analyysia

teidän pitäisi nähdä, miksi tämä kysely vetosi minuun, se on hyvin asetettu perustuu ja hyvin yksinkertainen kirjoittaa. Kun katson tätä kyselyä selittää analysoida saan:

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

Kyselyvertailu

joten päädymme hyvin yksinkertaiseen suunnitelmaan ja ajoituksiin, jotka ovat suunnilleen samat kuin liitoksella. Paul ja minä keskustelimme, miksi ajoitukset voivat olla niin samanlaisia ja keksimme ainakin kaksi syytä:

  1. aineistossa on hyvin vähän rivejä (8k), joten alakerroksen suorituskyky saattaa heiketä suuremmalla aineistolla.
  2. Koneellani on NVMe-Levykeasemat, jotka antavat peräkkäiselle pääsylle vielä suuremman suorituskykyeron.

toinen joukko-pohjainen Alakerta

lopulta Paul keksi vielä yhden joukon pohjaisen kyselyn vastatakseen samaan kysymykseen:

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

Tämä käyttää uutta SQL-lauseketta, paitsi, joka on osa asetettuja operaatiokyselykombinaattoreita. Yksi iso rajoitus näissä kyselyissä on, että kyselyt kummallakin puolella paitsi lausekkeen on palautettava samat sarakkeet ja tietotyypit. Tämä selittää, miksi tämä kysely ei voi palauttaa rivien kokonaismäärää. Mutta tämä kysely osoittautui huonommaksi suorituskyvyssä ja paljon monimutkaisemmaksi kyselysuunnitelmaksi:

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

yllätyskäänne

sitten mietin hieman lisää Paulin ehdottamaa kyselyä ja tajusin, että emme oikeastaan tarvinneet liitosta paitsi lausekkeen oikealle puolelle. Koska fire_weather sisältää kaikki samat sarakkeet kuin weather voimme vain käyttää sarakkeita haluamme ja saada vastauksen odotimme.

select id from weather exceptselect id from fire_weather;

nyt tässä on kiva set-syntaksi, mikä tekee siitä todella helposti ymmärrettävän. Jos halusimme todella saada laskennan kuten muissa kyselyissä, voimme kääriä kyselymme CTE: hen.

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

tällä kultaisella lipulla saamme 6 ms kyselyaikaa ja kyselysuunnitelmat, jotka ovat puhtaampia, mutta eivät yksinkertaisimpia. On huomattava, että siisteys ja yksinkertaisuus eivät ole avaintekijöitä kyselysuunnitelman arvioinnissa.

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

My Final Takeaways

Here are the final lessons I would like to leave you from this little exercise.

  1. ei koskaan silmämääräisiä kyselyaikoja-nämä olivat kaikki samaa nopeutta silmälleni. Selitä analysoi on ystäväsi.
  2. kirjoita kysely mielekkäimmällä tavalla ja tee sitten ajoitukset. Jos se ei ole hyvä niin etsiä vaihtoehto (luultavasti liittyy)
  3. on olemassa useita tapoja päästä sama vastaus SQL – ”oikea” vastaus tulee olemaan erittäin tilanneriippuvainen. Muutama asia, jotka vaikuttavat tulokseen:
    1. tiedon koko – kysely saattaa lakata olemasta ”ok”, kun tiedon koko kasvaa
    2. indeksit
    3. levyn nopeus
    4. muistin koko
    5. Prosessorin nopeus
    6. kuinka usein aiot suorittaa kyselyn
  4. lopuksi, aika parantaa SQL tietoja ja taitoja maksaa pois komeasti. Vaikka et kirjoita tehokkain kyselyt, ne ovat silti yleensä nopeammin kuin kirjallisesti paljon menettelysääntöjä. Kuten opin enemmän ja enemmän SQL kuvioita enemmän hämmästynyt Olen kaikki koodi voin korvata muutaman rivin SQL (ja saan yleensä valtava suorituskyky vauhtia).

Vastaa

Sähköpostiosoitettasi ei julkaista.