fbpx

Najważniejsze wskazówki dotyczące pisania zapytań SQL dla analityka danych

WŁAŚCIWA KOLEJNOŚĆ DZIAŁAŃ to

Poprawność, czytelność, a następnie optymalizacja.

Obowiązuje tu standardowe ostrzeżenie przed przedwczesną optymalizacją. Unikaj dostrajania zapytania SQL, dopóki nie dowiesz się, że zapytanie zwróci dane, których szukasz. Priorytet optymalizacji należy ustawić tylko wtedy, gdy jest ono uruchamiane często (np. zasilanie popularnego pulpitu nawigacyjnego) lub jeśli zapytanie przechodzi przez dużą liczbę wierszy. Ogólnie rzecz biorąc, przed martwieniem się o wydajność należy nadać priorytet dokładności (czy zapytanie daje zamierzone wyniki) i czytelności (czy inni mogą łatwo zrozumieć i zmodyfikować kod).

Spraw, aby stogi siana były jak najmniejsze przed poszukiwaniem igieł.

Prawdopodobnie już wchodzimy w optymalizację, ale celem powinno być poinformowanie bazy danych, aby przeskanowała minimalną liczbę wartości niezbędnych do pobrania wyników.


SQL ma deklaratywny charakter. Zamiast mówić bazie danych, jak pobierać rekordy, wystarczy powiedzieć bazie danych, które rekordy są potrzebne, a baza danych powinna znaleźć najbardziej efektywny sposób uzyskania tych informacji. W związku z tym wiele porad dotyczących poprawy wydajności zapytań polega po prostu na pokazaniu ludziom, jak korzystać z narzędzi w SQL, aby wyrazić swoje potrzeby z większą precyzją. Dodatkowo każdy silnik bazy (dostawca np. PostgreSQL, MySQL czy MS SQL) działa odrobinę inaczej, więc optymalizacja w każdym z nich może być inna.


Sprawdzimy ogólną kolejność wykonywania zapytań i dołączymy wskazówki dotyczące zmniejszenia przestrzeni wyszukiwania. Następnie porozmawiamy o trzech podstawowych narzędziach, które należy dodać do paska narzędziowego: INDEX, EXPLAIN i WITH.

1. Najpierw poznaj swoje dane.

Zapoznaj się z danymi przed napisaniem pojedynczego wiersza kodu, aby upewnić się, że kolumna naprawdę zawiera oczekiwane dane.
Rozwiń w edytorze zapytań interesującą, Cię tabelę i zobacz, jakie ma kolumny. Możesz także użyć zapytania:

Select *
From nazwa_tabeli
Limit 100

Warto także korzystać z dokumentacji bazy danych (jeśli w ogóle ona jest), aby dowiedzieć się, jaka jest zawartość poszczególnych tabel.

2. Opracowywanie zapytania

  • Metoda każdego z nas będzie się różnić, ale oto przykładowy przepływ pracy, który należy zastosować podczas opracowywania zapytania.
  • Jak wyżej, przestudiuj metadane kolumny i tabeli. Jeśli używasz natywnego edytora zapytań, możesz również wyszukać fragmenty kodu SQL zawierające kod SQL dla tabeli i kolumn, z którymi pracujesz. Urywki pozwalają zobaczyć, jak inni analitycy wysyłają zapytania do danych. Możesz też rozpocząć zapytanie od istniejącego pytania SQL.
  • Aby poznać wartości tabeli, SELECT * z tabel, z którymi pracujesz, i OGRANICZ wyniki. Zachowaj limit zastosowany podczas udoskonalania kolumn (lub dodawania kolejnych kolumn za pomocą powiązań).
  • Zawęź kolumny do minimalnego zestawu wymaganego do udzielenia odpowiedzi na twoje pytanie.
  • Zastosuj wszystkie filtry do tych kolumn.
  • Jeśli chcesz zagregować dane, zagreguj niewielką liczbę wierszy i potwierdź, że agregacje są zgodne z oczekiwaniami.
  • Gdy kwerenda zwróci potrzebne wyniki, poszukaj sekcji kwerendy, które mają zostać zapisane jako wspólne wyrażenie tabeli (CTE – Common Table Expressions) w celu hermetyzacji tej logiki.

3. Ogólna kolejność wykonywania zapytań

Zanim przejdę do poszczególnych wskazówek dotyczących pisania kodu SQL, ważne jest, aby mieć poczucie, w jaki sposób bazy danych będą wykonywać zapytania. Różni się to od kolejności czytania (od lewej do prawej, od góry do dołu) używanej do redagowania zapytania. Optymalizatory zapytań mogą zmieniać kolejność poniższej listy, ale ten ogólny cykl życia zapytania SQL warto pamiętać podczas pisania SQL. Użyjemy kolejności wykonania, aby pogrupować wskazówki dotyczące pisania dobrego SQL, które nastąpią.

Ogólna zasada jest następująca: im wcześniej na tej liście możesz wyeliminować dane, tym lepiej.

  1. FROM (i JOIN) pobierają tabele, do których odwołuje się zapytanie. Te tabele reprezentują maksymalną przestrzeń wyszukiwania określoną przez zapytanie. Jeśli to możliwe, ogranicz tę przestrzeń wyszukiwania przed przejściem do przodu.
  2. WHERE filtruje dane.
  3. GROUP BY agreguje dane.
  4. HAVING odfiltrowuje zagregowane dane, które nie spełniają kryteriów.
  5. SELECT pobiera kolumny (a następnie deduplikuje wiersze, jeśli wywoływany jest DISTINCT).
  6. UNION scala wybrane dane w zestaw wyników.
  7. ORDER BY sortuje wyniki.

Tak jak wspominałem wcześniej optymalizator zapytań dla konkretnej bazy danych, może oferować inny plan zapytań.

4. Niektóre wskazówki dotyczące zapytań (nie reguły)

Poniższe wskazówki są wytycznymi, a nie zasadami, które mają na celu uniknięcie trudności w konstruowaniu zapytań. Każda baza danych obsługuje SQL inaczej, ma nieco inny zestaw funkcji i przyjmuje różne podejścia do optymalizacji zapytań.

5. Komentowanie kodu, dlaczego warto to robić.

Pomóż ludziom (w tym sobie za trzy miesiące), dodając komentarze, które wyjaśniają różne części kodu. Najważniejszą rzeczą do uchwycenia tutaj jest „dlaczego”. Na przykład oczywiste jest, że poniższy kod odfiltrowuje zamówienia o identyfikatorze większym niż 10, ale powodem, dla którego to robi, jest to, że pierwsze 10 zamówień jest używanych do testowania.

SELECT id, nazwa_produktu
FROM zamowienia
-- dlaczego odfiltrowujesz? Id>10 odfiltrowuje zamówienia testowe
WHERE id > 10

Trudność polega na tym, że wprowadzasz trochę kosztów utrzymania: jeśli zmienisz kod, musisz upewnić się, że komentarz jest nadal aktualny i aktualny. Ale to niewielka cena za czytelny kod.

6. Najważniejsze wskazówki dotyczące języka SQL dla funkcji FROM

Łączenie tabel za pomocą słowa kluczowego ON

Chociaż możliwe jest „połączenie” dwóch tabel za pomocą klauzuli WHERE (to znaczy, aby wykonać niejawne sprzężenie, takie jak SELECT * FROM a,b WHERE a.foo = b.bar), zamiast tego powinieneś użyć JOIN
SELECT
z.id,
z.wartosc,
p.klient
FROM zamowienia AS z
JOIN produkty AS p ON z.product_id = p.id

Głównie dla czytelności, ponieważ składnia JOIN + ON odróżnia sprzężenia od WHERE przeznaczonych do filtrowania wyników.

7. Aliasowanie wielu tabel

Podczas wykonywania zapytań do wielu tabel należy używać aliasów i używać tych aliasów w instrukcji SELECT, dzięki czemu baza danych (i czytnik) nie muszą analizować, która kolumna należy do której tabeli. Należy pamiętać, że jeśli w wielu tabelach znajdują się kolumny o tej samej nazwie, należy jawnie odwoływać się do nich za pomocą nazwy tabeli lub aliasu.


Unikaj
SELECT
tytul,
nazwisko,
imie
FROM ksiazki
LEFT JOIN autorzy ON ksiazki.autor_id = autorzy.id

Używaj
SELECT
k.tytul,

a.nazwisko,
a.imie
FROM kasiazki AS k
LEFT JOIN autorzy AS a ON k.author_id = a.id

Jest to trywialny przykład, ale gdy liczba tabel i kolumn w zapytaniu wzrośnie, czytelnicy nie będą musieli śledzić, która kolumna znajduje się, w której tabeli. Zapytania mogą się zepsuć, jeśli dołączysz tabelę o niejednoznacznej nazwie kolumny (np. obie tabele zawierają pole o nazwie Created_At).

8. Najlepsze rozwiązania SQL dla WHERE

Filtruj z WHERE przed HAVING

Użyj klauzuli WHERE do filtrowania zbędnych wierszy, dzięki czemu nie musisz obliczać tych wartości w pierwszej kolejności. Dopiero po usunięciu nieistotnych wierszy oraz po agregacji tych wierszy i zgrupowaniu ich należy dołączyć klauzulę HAVING do odfiltrowywania agregacji.

9. Unikanie funkcji w kolumnach w klauzulach WHERE

Użycie funkcji w kolumnie w klauzuli WHERE może naprawdę spowolnić kwerendę, ponieważ funkcja ta sprawia, że kwerenda jest non-sargable (tj. uniemożliwia bazie danych używanie indeksu w celu przyspieszenia zapytania). Zamiast używać indeksu do przechodzenia do odpowiednich wierszy, funkcja w kolumnie wymusza na bazie danych uruchomienie funkcji w każdym wierszu tabeli.
I pamiętaj, że operator konkatenacji || jest również funkcją, więc nie powinno się używać concat stringów do filtrowania wielu kolumn. Zamiast tego używaj wielu warunków:

Unikaj
SELECT
nazwa,
pseudonim
FROM bohaterowie
WHERE nazwa || pseudonim = 'MisKolargol'

Używaj
SELECT
nazwa,
pseudonim
FROM bohaterowie
WHERE
nazwa = 'Mis'
AND
pseudonim = 'Kolargol'

10. Używaj = LIKE

Dobrze jest wiedzieć, że LIKE porównuje znaki i może być sparowany z operatorami wieloznacznymi, takimi jak %, podczas gdy operator = porównuje ciągi i liczby dla dokładnych dopasowań. = może korzystać z indeksowanych kolumn. Nie dotyczy to wszystkich baz danych, ponieważ LIKE może używać indeksów (jeśli istnieją dla pola), o ile unikasz poprzedzania wyszukiwanego terminu operatorem symbolu wieloznacznego, %. Co prowadzi nas do następnego punktu:
Unikanie umieszczania symboli wieloznacznych w instrukcjach WHERE
Używanie symboli wieloznacznych do wyszukiwania może być kosztowne. Preferuj dodawanie symboli wieloznacznych na końcu ciągów. Poprzedzenie ciągu symbolem wieloznacznym może prowadzić do pełnego skanowania tabeli.

Unikaj
SELECT column FROM table WHERE col LIKE "%wizar%"

Używaj
SELECT column FROM table WHERE col LIKE "wizar%"

11. Używaj EXISTS w IN

Jeśli chcesz tylko zweryfikować istnienie wartości w tabeli, używaj EXISTS zamiast IN, ponieważ proces EXISTS kończy się, gdy tylko znajdzie wartość wyszukiwania, podczas gdy IN przeskanuje całą tabelę. IN powinien być używany do znajdowania wartości na listach.

Podobnie, działają NOT EXISTS i NOT IN.

12. Najważniejsze wskazówki dotyczące języka SQL dotyczące GROUP BY

Uporządkuj wiele grup według malejącej „różnorodności”

Tam, gdzie to możliwe, GROUP BY kolumn w kolejności malejącej „różnorodności”. Oznacza to, że najpierw grupuj według kolumn z bardziej unikalnymi wartościami (takimi jak identyfikatory lub numery telefonów), a następnie grupuj według kolumn o mniejszej liczbie różnych wartości (takich jak stan lub płeć).

13. Najlepsze wskazówki SQL dotyczące HAVING

Używaj HAVING tylko do filtrowania agregatów.

Przed HAVING odfiltruj wartości za pomocą klauzuli WHERE przed agregacją i grupowaniem tych wartości.

14. Najważniejsze wskazówki dotyczące języka SQL dla SELECT

SELECT/WYBIERZ kolumny, a nie gwiazdka.

Określ kolumny, które chcesz uwzględnić w wynikach (choć dobrze jest użyć *podczas pierwszego przeglądania tabel — pamiętaj, tylko aby OGRANICZYĆ wyniki).

15. Najlepsze praktyki SQL dla UNION

Preferuj UNION All niż UNION

Jeśli duplikaty nie stanowią problemu, UNION ALL nie odrzuci ich, a ponieważ ZADANIEM UNION ALL nie jest usuwanie duplikatów, zapytanie będzie bardziej wydajne.

16. Najlepsze praktyki SQL dla ORDER BY

Unikaj sortowania tam, gdzie to możliwe, szczególnie w podzapytaniach

Sortowanie jest drogie. Jeśli musisz sortować, upewnij się, że podzapytania nie są niepotrzebnie sortowane dane.

17. Najważniejsze wskazówki dotyczące języka SQL dla indeksu INDEX

Ta sekcja jest przeznaczona dla administratorów baz danych (i temat zbyt duży, aby zmieścić się w tym artykule). Jedną z najczęstszych rzeczy, na które napotykają ludzie, gdy występują problemy z wydajnością w zapytaniach do bazy danych, jest brak odpowiedniego indeksowania.
To, które kolumny należy indeksować, zazwyczaj zależy od kolumn, według których filtrujesz (tj. które kolumny zazwyczaj kończą się w klauzulach WHERE). Jeśli okaże się, że zawsze filtrujesz według wspólnego zestawu kolumn, rozważ indeksowanie tych kolumn.
Dodawanie indeksów
Indeksowanie kolumn z kluczami obcymi i kolumn często wysyłanych na zapytania może znacznie skrócić czas zapytań. Oto przykładowa instrukcja do utworzenia indeksu:
CREATE INDEX product_title_index ON products (title)

Dostępne są różne typy indeksów, najczęstszy typ indeksu używa B-tree, aby przyspieszyć pobieranie. Zapoznaj się z dokumentacją bazy danych na temat tworzenia indeksu.

18. Używanie indeksów częściowych

W przypadku szczególnie dużych zbiorów danych lub koślawych zestawów danych, w których niektóre zakresy wartości pojawiają się częściej, należy rozważyć utworzenie indeksu z klauzulą WHERE, aby ograniczyć liczbę indeksowanych wierszy. Indeksy częściowe mogą być również przydatne w przypadku zakresów dat, na przykład jeśli chcesz indeksować tylko dane z ostatniego tygodnia.
Używanie indeksów złożonych
W przypadku kolumn, które zazwyczaj łączą się w zapytaniach (takich jak last_name, first_name), należy rozważyć utworzenie indeksu złożonego. Składnia jest podobna do tworzenia pojedynczego indeksu. Na przykład:
CREATE index full_name_index ON client (last_name, first_name)

19. WYJAŚNIENIE

Poszukaj wąskich gardeł

Niektóre bazy danych, takie jak np. PostgreSQL, oferują wgląd w plan zapytań na podstawie kodu SQL. Po prostu poprzedź swój kod słowami kluczowymi EXPLAIN ANALYZE. Za pomocą tych poleceń można sprawdzać plany zapytań i szukać wąskich gardeł lub porównywać plany z jednej wersji zapytania z inną, aby sprawdzić, która wersja jest bardziej wydajna.
Oto przykładowe zapytanie przy użyciu przykładowej bazy danych dvdrental dostępnej dla PostgreSQL.

EXPLAIN ANALYZE SELECT title, release_year
FROM film
WHERE release_year > 2000;

Odpowiedź:
Seq Scan on film (cost=0.00..86.50 rows=1000 width=14) (actual time=0.007..0.388 rows=1000 loops=1)
Filter: ((release_year)::integer > 2000)
Planning Time: 0.082 ms
Execution Time: 0.716 ms

Zobaczysz milisekundy wymagane do planowania czasu, czasu wykonania, a także koszt, wiersze, szerokość, czasy, pętle, użycie pamięci i inne. Czytanie tych analiz jest w pewnym sensie sztuką, ale można ich używać do identyfikowania obszarów problemowych w zapytaniach (takich jak zagnieżdżone pętle lub kolumny, które mogłyby skorzystać z indeksowania), podczas ich udoskonalania.
Oto dokumentacja PostreSQL na temat korzystania z EXPLAIN.

20. WITH

Organizowanie zapytań za pomocą wspólnych wyrażeń tabel (CTE – Common Table Expressions)

Klauzula WITH służy do hermetyzacji logiki we wspólnym wyrażeniu tabeli (CTE). Oto przykład zapytania, które szuka produktów o najwyższym średnim przychodu na jednostkę sprzedaną w 2019 r., a także wartości maksymalnych i minimalnych.


WITH product_orders AS (
SELECT o.created_at AS order_date,
p.title AS product_title,
(o.subtotal / o.quantity) AS revenue_per_unit
FROM orders AS o
LEFT JOIN products AS p ON o.product_id = p.id
-- Filter out orders placed by customer service for charging customers
WHERE o.quantity > 0
)
SELECT product_title AS product,
AVG(revenue_per_unit) AS avg_revenue_per_unit,
MAX(revenue_per_unit) AS max_revenue_per_unit,
MIN(revenue_per_unit) AS min_revenue_per_unit
FROM product_orders
WHERE order_date BETWEEN '2019-01-01' AND '2019-12-31'
GROUP BY product
ORDER BY avg_revenue_per_unit DESC

Klauzula WITH sprawia, że kod jest czytelny, ponieważ główne zapytanie (czego faktycznie szukasz) nie jest przerywane przez długie zapytanie podrzędne.
Możesz również użyć CTE, aby uczynić SQL bardziej czytelnym, jeśli na przykład baza danych zawiera pola, które są niezgrabnie nazwane lub które wymagają odrobiny mungingu (zmian) danych, aby uzyskać przydatne dane. Na przykład CTE mogą być przydatne podczas pracy z polami JSON. Oto przykład wyodrębniania i konwertowania pól z obiektu blob JSON zdarzeń użytkownika.

WITH source_data AS (
SELECT events->'data'->>'name' AS event_name,
CAST(events->'data'->>'ts' AS timestamp) AS event_timestamp
CAST(events->'data'->>'cust_id' AS int) AS customer_id
FROM user_activity
)
SELECT event_name,
event_timestamp,
customer_id
FROM source_data

Czekam na Twoją opinię ?

Istnieją biblioteki książek na temat SQL, w tym tekście jest tylko kilka najistotniejszych elementów. Możesz, podzielić się swoimi doświadczeniami SQL z innymi napisz do mnie [email protected].


Opublikowano

w

przez

Komentarze

Jedna odpowiedź do „Najważniejsze wskazówki dotyczące pisania zapytań SQL dla analityka danych”

  1. […] Zapraszam także do przeczytania innego wpisu dotyczącego najbardziej przydatnych wskazówek do pisania zapytań SQL. […]

Dodaj komentarz

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *