0Pricing
SQL Interview Prep · Lekcja

Filtrowanie według wartości obliczanych

Dlaczego funkcje użyte na kolumnach uniemożliwiają korzystanie z indeksu i jak rekruterzy sprawdzają tę wiedzę

Filtrowanie według wartości obliczanych to bezpłatna lekcja SQL Interview Prep na CoddyKit. To lekcja 4 z 4. Możesz przeczytać całą lekcję poniżej za darmo — a potem ćwiczyć ją interaktywnie w przeglądarce z wbudowanym edytorem kodu i tutorem AI dostępnym 24/7. To część ścieżki edukacyjnej SQL Interview Prep, a Twój postęp synchronizuje się między webem a aplikacją CoddyKit. Kurs SQL Interview Prep zawiera 4 lekcji w sumie.

Dlaczego to pytanie rozróżnia poziomy

Pytanie brzmi niewinnie: to zapytanie jest poprawne, ale działa wolno — dlaczego? Często odpowiedź brzmi: klauzula WHERE opakowuje indeksowaną kolumnę w funkcję. Sprawia to, że predykat staje się niesargowalny: optymalizator nie może już użyć indeksu i musi przeskanować każdy wiersz.

Ta lekcja wyjaśnia sargowalność, pokazuje przekształcenia oczekiwane podczas rozmów kwalifikacyjnych i omawia, gdzie faktycznie należy umieścić filtr oparty na obliczeniach.

Sargowalność — jedna definicja

Sargowalny (Search ARGument ABLE) oznacza, że predykat może użyć indeksu, aby bezpośrednio przejść do pasujących wierszy. Praktyczna zasada jest następująca: indeksowana kolumna musi występować bez modyfikacji po jednej stronie porównania, a nie być ukryta wewnątrz funkcji lub wyrażenia.

  • Sargowalne: col = 5, col > 100, col LIKE 'abc%'
  • Niesargowalne: FUNC(col) = 5, col + 1 > 100

Antywzorzec funkcji na kolumnie

Celem jest znalezienie zamówień złożonych w 2024 roku. Opakowanie kolumny w YEAR() zmusza silnik do obliczenia roku dla każdego wiersza, zanim będzie można wykonać porównanie, dlatego indeks na order_date staje się bezużyteczny.

Zapytanie zwraca poprawny wynik, ale skanuje całą tabelę. W przypadku dużej tabeli oznacza to różnicę między milisekundami a minutami.

-- non-sargable: function on the indexed column
SELECT *
FROM orders
WHERE YEAR(order_date) = 2024;

Przepisz jako zakres

Rozwiązaniem jest pozostawienie order_date bez modyfikacji i wyrażenie warunku jako półotwartego zakresu. Teraz indeks na order_date może przejść bezpośrednio do początku 2024 roku i zatrzymać się na początku 2025 roku.

Wynik jest taki sam, ale zamiast pełnego skanowania wykonywane jest skanowanie zakresu indeksu. To przekształcenie zakresu jest najczęściej sprawdzoną poprawką dotyczącą sargowalności podczas rozmów kwalifikacyjnych.

-- sargable: column stays bare
SELECT *
FROM orders
WHERE order_date >= '2024-01-01'
  AND order_date <  '2025-01-01';

Działania arytmetyczne na kolumnie

Ten sam problem występuje w działaniach arytmetycznych. WHERE salary + bonus > 100000 lub WHERE price * 0.9 < 50 wykonują obliczenia na kolumnie i blokują użycie indeksu.

W miarę możliwości przenieś obliczenia na stronę stałej: przepisz price * 0.9 < 50 jako price < 50 / 0.9. Wartość stała zostanie obliczona raz, a price pozostanie bez modyfikacji i będzie można użyć jej w indeksie.

-- before: math on the column (non-sargable)
WHERE price * 0.9 < 50
-- after: math on the constant (sargable)
WHERE price < 50 / 0.9

Wariant wyszukiwania bez rozróżniania wielkości liter

WHERE LOWER(email) = 'a@b.com' jest niesargowalne względem zwykłego indeksu na email, ponieważ najpierw trzeba zamienić adres e-mail na małe litery dla każdego wiersza.

Istnieją dwa rozwiązania stosowane w środowisku produkcyjnym: przechowywanie znormalizowanej kopii zapisanej małymi literami i utworzenie na niej indeksu albo utworzenie indeksu funkcyjnego na LOWER(email), tak aby indeksowane było samo wyrażenie. Wspomnienie o opcji indeksu funkcyjnego świadczy o doświadczeniu praktycznym.

-- functional index makes the expression sargable
CREATE INDEX idx_email_lower ON users (LOWER(email));
SELECT * FROM users WHERE LOWER(email) = 'a@b.com';

Kiedy obliczenie jest rzeczywiście potrzebne

Czasami filtr rzeczywiście zależy od obliczonej wartości, której nie da się zastąpić zakresem, na przykład podczas filtrowania według ilorazu. Nadal nie można odwołać się do aliasu SELECT w WHERE, ponieważ WHERE jest obliczane przed listą SELECT.

Należy więc albo powtórzyć wyrażenie w WHERE, albo opakować zapytanie w podzapytanie / CTE i filtrować obliczoną kolumnę w zapytaniu zewnętrznym.

SELECT *
FROM (
  SELECT *, revenue / NULLIF(visits, 0) AS rev_per_visit
  FROM stats
) t
WHERE t.rev_per_visit > 2.5;

Agregaty trafiają do HAVING, nie do WHERE

Obliczenie będące agregacją w ogóle nie może znajdować się w WHERE, ponieważ WHERE filtruje pojedyncze wiersze, zanim nastąpi grupowanie. WHERE SUM(amount) > 1000 powoduje błąd.

Filtry dotyczące agregatów należy umieszczać w HAVING, które jest wykonywane po GROUP BY. Wiedza o tym, która klauzula widzi dane obliczenie, jest częstym pytaniem dotyczącym kolejności wykonywania zapytania.

SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 1000;

Jak rekruterzy sprawdzają tę wiedzę

Pokazują wolne zapytanie z funkcją zastosowaną na kolumnie i proszą o jego przyspieszenie bez zmiany wyniku. Należy:

  • rozpoznać zastosowanie funkcji na kolumnie jako przypadek niesargowalny
  • przepisać zapytanie tak, aby kolumna pozostała bez modyfikacji (zakres lub obliczenia po stronie stałej)
  • jeśli nie da się wykonać takiego przekształcenia, zaproponować indeks funkcyjny lub przechowywaną kolumnę obliczaną

Wspomnienie o EXPLAIN w celu potwierdzenia, że plan zmienił się ze skanowania sekwencyjnego na skanowanie indeksu, dopełnia odpowiedź.

Świadomość kompromisów

Warto zachować równowagę: indeksy i indeksy funkcyjne przyspieszają odczyt, ale spowalniają zapis i zajmują miejsce. W przypadku małej tabeli pełne skanowanie jest w porządku, a dodanie indeksu byłoby niepotrzebnym wysiłkiem.

Doświadczona odpowiedź powinna uwzględniać kontekst: jeśli kolumna znajduje się w dużej tabeli i często filtruje się po niej w ten sposób, należy uczynić predykat sargowalnym lub dodać indeks funkcyjny; w przeciwnym razie należy pozostawić go bez zmian. Podczas rozmów kwalifikacyjnych kontekst jest ważniejszy od dogmatycznych reguł.

Indeksy funkcyjne sprawiają, że obliczenia są sargowalne

Czasami rzeczywiście trzeba filtrować przekształconą wartość — na przykład podczas porównywania bez uwzględniania wielkości liter. Zamiast rezygnować z indeksów, należy utworzyć indeks wyrażenia (funkcyjny) dla dokładnie tego wyrażenia, którego używa się do filtrowania.

  • Optymalizator może wtedy użyć indeksu, nawet jeśli kolumna jest opakowana funkcją.
  • Wyrażenie indeksu musi dokładnie odpowiadać wyrażeniu predykatu.
-- index the expression you filter on
CREATE INDEX idx_users_lower_email ON users (lower(email));

-- now this predicate stays sargable
SELECT * FROM users WHERE lower(email) = 'amy@example.com';

Szybkie sprawdzenie

Wskaż predykat, dla którego optymalizator może użyć indeksu.

Podsumowanie

Najważniejsze informacje:

  • Predykat jest sargowalny, gdy indeksowana kolumna występuje bez modyfikacji, a nie wewnątrz funkcji lub działania arytmetycznego
  • Przepisz YEAR(col) = 2024 jako półotwarty zakres, a obliczenia przenieś na stronę stałej
  • W przypadku wyrażeń, których nie da się uniknąć, użyj indeksu funkcyjnego lub przechowywanej kolumny obliczanej
  • Nie można użyć aliasu SELECT w WHERE; agregaty należy umieszczać w HAVING

Klasyczne pytanie dotyczy wolnego zapytania, a klasycznym rozwiązaniem jest pozostawienie kolumny bez modyfikacji.

Często zadawane pytania

Czy lekcja „Filtrowanie według wartości obliczanych” jest bezpłatna?

Tak — pełny tekst „Filtrowanie według wartości obliczanych” jest dostępny za darmo tutaj w sieci. Aby ćwiczyć ją interaktywnie (wbudowany edytor kodu i tutor AI dostępny 24/7) i odblokować resztę kursu SQL Interview Prep, przejdź na CoddyKit PRO. Kurs SQL Interview Prep zawiera 4 lekcji w sumie.

Co nauczysz się w „Filtrowanie według wartości obliczanych”?

Dlaczego funkcje użyte na kolumnach uniemożliwiają korzystanie z indeksu i jak rekruterzy sprawdzają tę wiedzę Ćwiczysz SQL Interview Prep z praktycznym kodem, który uruchamiasz bezpośrednio w przeglądarce, a tutor AI dostępny 24/7 odpowiada na Twoje pytania podczas pracy nad lekcją.

Czy potrzebuję doświadczenia, aby zacząć SQL Interview Prep?

Nie wymagamy żadnego doświadczenia. SQL Interview Prep w CoddyKit jest strukturyzowany dla początkujących i zaawansowanych użytkowników, więc możesz zacząć tutaj lub od początku i uczyć się w swoim tempie. To lekcja 4 z 4.

Ile czasu zajmuje lekcja „Filtrowanie według wartości obliczanych”?

Większość lekcji CoddyKit trwa około 5–10 minut. Każda lekcja to mały, interaktywny krok, dzięki czemu robisz systematyczne postępy i zawsze wracasz dokładnie do tego samego miejsca — na webie i w aplikacji.

Czy mogę pisać i uruchamiać kod w tej lekcji SQL Interview Prep?

Tak. Każda lekcja SQL Interview Prep zawiera wbudowany edytor kodu, więc piszesz i uruchamiasz prawdziwy kod bezpośrednio w przeglądarce i od razu otrzymujesz sprzężenie zwrotne od AI — bez konfiguracji na komputerze.

Wszystkie lekcje w tym kursie

  1. Pierwszeństwo AND/OR i używanie nawiasów
  2. BETWEEN, IN i granice włączne
  3. LIKE, symbole wieloznaczne i znaki ucieczki
  4. Filtrowanie według wartości obliczanych
← Powrót do SQL Interview Prep