bevezetésem az adatbázisokba és a PostgreSQL-be webes alkalmazások fejlesztése és statisztikai elemzés céljából történt. Csak annyi SQL-t tanultam meg, hogy megkapjam a lekérdezéseket a helyes válaszok visszaadásához. Mivel a munkám PostGIS (és FOSS4G) összebarátkoztam Paul Ramsey. Most a Crunchy Data munkatársai vagyunk, és ő segít nekem az SQL-fu-ban. Az egyik első lecke, amelyet tanított nekem, az volt, hogy “próbáljon inkább a csatlakozásokat használni, mint az alkereséseket.”

a mai bejegyzés ezen a Tanácson fog dolgozni, mivel Paul és én néhány SQL-en dolgozunk.

mit próbálunk válaszolni

próbálok létrehozni néhány új tanítási és beszéd anyagok segítségével SQL data science és dolgoztam néhány előzetes elemzés adatok manipulálása. Volt egy asztalom, a fire_weather, ami az időjárási táblázat részhalmaza, és meg akarom találni az időjárás összes bejegyzését, amelyek nem szerepelnek a fire_weather-ben. A kezdeti ösztön az volt, hogy írjon egy subquery, de ez úgy tűnt, mint egy egyszerű és egyszerű lekérdezés követni Paul “use a join” tanácsot.

hibás csatlakozási lekérdezés

ezzel a csatlakozási lekérdezéssel kezdtem:

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

és nem működött (különben nem írnám ezt a blogbejegyzést). Kiderül, hogy ez nem egy kereszt csatlakozik, ahol a végén az összes páros kombinációi minden sor mindkét táblázatban.

a megfelelő csatlakozás lekérdezés

Tehát ezen a ponton lazítok (szemben a telefon csengetésével) Paul és elkezdjük megvitatni, hogyan kell csinálni a megfelelő csatlakozást. Kiderült, hogy a helyes szintaxis:

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

alapvetően egy bal oldali külső csatlakozást végez, megadva az időjárási táblázat összes sorát, és csak a fire_weather bejegyzéseket, amelyek megfelelnek. Ezután kiszűrjük az összes rekordot, ahol vannak mérkőzések fire_weather. Elég egyszerű megérteni, de nem nagyon beállított, mint a halmazelmélet (amely a relációs adatbázis-rendszerek kapcsolatainak alapja). Aztán megint, most van egy működő csatlakozási lekérdezésünk.

elemezze ezt

a PostgreSQL-ben az SQL jobb megértéséhez vezető utam részeként nagy rajongója lettem az EXPLAIN ANALYZE for időzítéseknek és a lekérdezési tervnek. Csak kíváncsiságból úgy döntök, hogy megnézem a csatlakozási lekérdezés időzítését és lekérdezési tervét. Itt van a kimenet, amely körülbelül 7 milliszekundumot vett igénybe egy kissé bonyolult lekérdezési tervvel:

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

most a

Allekérdezés, és most látni akartam, hogyan fog működni az eredeti allekérdezési ötletem. Itt van a lekérdezés módja ugyanazon kérdés megválaszolására:

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

további elemzés

látnod kell, hogy ez a lekérdezés miért tetszett nekem, nagyon beállított alapú és nagyon egyszerű írni. Amikor megnézem ezt a lekérdezést magyarázza elemezni kapok:

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

lekérdezés összehasonlítás

tehát egy nagyon egyszerű tervet és időzítést kapunk, amelyek nagyjából megegyeznek a csatlakozással. Paul és én megvitattuk, hogy miért lehetnek ilyen hasonlóak az időzítések, és legalább két okból álltunk elő:

  1. az adatkészletnek nagyon kevés sora van (8k), így az alkérdezés teljesítménye nagyobb adatkészlet esetén romolhat.
  2. a gépem NVMe lemezmeghajtókkal rendelkezik, így a szekvenciális hozzáférés még nagyobb teljesítménykülönbséget eredményez.

Another Set Based Subquery

végül Paul, jött egy másik készlet alapú lekérdezés válaszolni ugyanazt a kérdést:

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

ez egy új SQL-záradékot használ, kivéve, amely a set operation query combiners része. Ezeknek a lekérdezéseknek az egyik nagy korlátozása az, hogy az except záradék mindkét oldalán található lekérdezéseknek ugyanazokat az oszlopokat és adattípusokat kell visszaadniuk. Ez megmagyarázza, hogy ez a lekérdezés miért nem adhatja vissza a teljes sorszámot. De ez a lekérdezés rosszabb teljesítménynek és sokkal bonyolultabb lekérdezési tervnek bizonyult:

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

a meglepetés Twist

aztán gondoltam még egy kicsit a Paul által javasolt lekérdezésről, és rájöttem, hogy valójában nincs szükségünk a csatlakozásra az except záradék jobb oldalán. Mivel a fire_weather ugyanazokat az oszlopokat tartalmazza, mint az időjárás, csak a kívánt oszlopokat használhatjuk, és megkaphatjuk a várt választ.

select id from weather exceptselect id from fire_weather;

most ez szép sor szintaxis így nagyon könnyen érthető. Ha azt akartuk, hogy valóban a gróf, mint a többi lekérdezések tudjuk csomagolja a lekérdezés egy CTE.

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

ezzel az arany jegyrel 6 ms lekérdezési időt és egy lekérdezési tervet kapunk, amely tisztább, de nem legegyszerűbb. Meg kell jegyeznem, hogy a tisztaság és az egyszerűség nem kulcsfontosságú tényezők a lekérdezési terv értékelésében.

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

az utolsó Elvitelem

íme az utolsó leckék, amelyeket ebből a kis gyakorlatból szeretnék hagyni.

  1. soha szemgolyó lekérdezési idők – ezek mind ugyanolyan sebességgel a szemem. Magyarázza el, hogy az elemzés a barátod.
  2. írja be a lekérdezést a legértelmesebb módon, majd végezze el az időzítést. Ha ez nem jó, akkor keressen egy alternatívát (valószínűleg csatlakozik)
  3. többféle módon lehet elérni ugyanazt a választ az SQL – ben-a “helyes” válasz erősen helyzetfüggő lesz. Néhány dolog, ami befolyásolja az eredményt:
    1. az adatok mérete – a lekérdezés leállhat “ok”, mivel az adatok mérete növekszik
    2. indexek
    3. a lemez sebessége
    4. memória mérete
    5. processzor sebessége
    6. milyen gyakran tervezi végrehajtani a lekérdezést
  4. végül az SQL ismeretek és készségek fejlesztésére fordított idő szépen megtérül. Még akkor is, ha nem a leghatékonyabb lekérdezéseket írja, ezek általában gyorsabbak, mint sok eljárási kód írása. Ahogy egyre több és több SQL mintát tanulok, annál inkább csodálkozom azon a kódon, amelyet néhány sor SQL-vel helyettesíthetek (és általában hatalmas teljesítménynövekedést kapok).

Vélemény, hozzászólás?

Az e-mail-címet nem tesszük közzé.