Wolne zapytania SQL potrafią zamrozić całą aplikację, zdenerwować użytkowników i przyprawić o ból głowy nawet doświadczonego developera. Dobra wiadomość: w zdecydowanej większości przypadków problem ma konkretną, możliwą do zidentyfikowania przyczynę. Według badań Percona ponad 80% problemów z wydajnością baz danych wynika z zaledwie kilku powtarzających się błędów. W tym artykule omawiamy 7 najczęstszych powodów, dla których query SQL działa za wolno, i pokazujemy, jak każdy z nich naprawić.
1. Brak indeksów lub źle dobrane indeksy
To najczęstsza przyczyna wolnych zapytań SQL. Jeśli tabela nie ma indeksu na kolumnie używanej w klauzuli WHERE, JOIN lub ORDER BY, baza danych wykonuje pełne przeszukiwanie tabeli (ang. full table scan). Przy milionach wierszy oznacza to sekundy, a nawet minuty oczekiwania.
Jak sprawdzić, czy brakuje indeksu?
W MySQL wystarczy wywołać:
…
Jeśli w kolumnie type zobaczysz wartość ALL, baza przeszukuje każdy wiersz. Wartość ref lub eq_ref oznacza, że indeks jest używany.
Co zrobić?
Dodaj indeks na kolumnie, która pojawia się w warunku filtrowania:
…
Pamiętaj jednak, że nadmiar indeksów też szkodzi. Każdy indeks spowalnia operacje INSERT, UPDATE i DELETE, bo baza musi aktualizować struktury indeksów przy każdej zmianie danych. Złotą zasadą jest tworzenie indeksów tylko tam, gdzie naprawdę są potrzebne i regularny przegląd nieużywanych indeksów (w MySQL możesz to sprawdzić przez widok sys.schema_unused_indexes).
2. Użycie SELECT * zamiast konkretnych kolumn
SELECT * to pozornie wygodny skrót, który w praktyce generuje niepotrzebne obciążenie. Pobierasz wszystkie kolumny, nawet te, których nie używasz. Przy tabelach z kolumnami typu TEXT, BLOB lub dużymi polami JSON ilość przesyłanych danych może być gigantyczna.
Przykład problemu
…
Precyzyjne listowanie kolumn pozwala bazie danych skorzystać z indeksów pokrywających (covering indexes), czyli takich, które zawierają wszystkie potrzebne dane bez konieczności sięgania do głównej tabeli. To jeden z najprostszych kroków w optymalizacji zapytań SQL, który nie wymaga żadnych zmian w schemacie bazy.
3. Nieoptymalne złączenia tabel (JOIN)
Złączenia to potężne narzędzie, ale łatwo je wykorzystać w sposób, który eksploduje złożonością obliczeniową. Dwa typowe błędy to złączanie tabel bez indeksów na kolumnach łączących oraz wykonywanie złączeń kartezjańskich przez przypadek (brak warunku ON).
Na co zwrócić uwagę?
- Upewnij się, że obie strony złączenia mają indeks na kolumnie używanej w
ON. - Unikaj złączeń na kolumnach z funkcjami, np.
ON YEAR(a.data) = YEAR(b.data), bo to uniemożliwia użycie indeksu. - Sprawdź kolejność tabel w zapytaniu: wiele optymalizatorów baz danych radzi sobie z tym automatycznie, ale w złożonych zapytaniach warto zacząć od najmniejszej tabeli.
Złączenie bez indeksu na kolumnie łączącej może zamienić zapytanie działające w milisekundach w zapytanie działające w minutach.
Przykład poprawionego złączenia
…
4. Funkcje na kolumnach w klauzuli WHERE
To jeden z tych błędów, które łatwo przeoczyć. Jeśli opakowujesz kolumnę w funkcję w klauzuli WHERE, optymalizator bazy danych nie jest w stanie użyć indeksu na tej kolumnie.
Klasyczny przykład
…
Ten sam problem dotyczy konwersji typów. Jeśli kolumna jest typu INT, a porównujesz ją do wartości tekstowej (WHERE id = '42'), baza może wykonać niejawną konwersję i zignorować indeks. W MySQL możesz to zweryfikować poleceniem EXPLAIN szukając frazy Using index lub jej braku w kolumnie Extra.
5. Brak paginacji i pobieranie zbyt dużych zestawów danych
Query SQL za wolno odpowiada często dlatego, że po prostu próbuje przetworzyć i przesłać zbyt dużo danych na raz. Pobieranie setek tysięcy wierszy w jednym zapytaniu jest rzadko uzasadnione, a zawsze kosztowne.
Jak to naprawić?
Używaj LIMIT i OFFSET do stronicowania wyników:
…
Pamiętaj jednak, że duże wartości OFFSET też są kosztowne. Przy OFFSET 100000 baza musi przetworzyć 100 000 wierszy, żeby je pominąć. Lepszym rozwiązaniem jest paginacja kluczem (keyset pagination):
…
Ta technika jest liniowo szybsza niezależnie od tego, jak głęboko stronicujesz wyniki.
6. Problemy z blokadami i transakcjami (locki)
Wolne zapytania SQL to nie zawsze kwestia samego zapytania. Czasem query czeka, bo inny proces trzyma blokadę na tabeli lub wierszu. W środowiskach produkcyjnych z dużą liczbą równoległych połączeń to bardzo częsta przyczyna spadku wydajności.
Jak wykryć blokady w MySQL?
…
Jeśli widzisz wiele procesów w stanie Waiting for table metadata lock lub Lock wait timeout exceeded, masz problem z blokadami.
Co można zrobić?
- Skracaj czas trwania transakcji: otwieraj je jak najpóźniej, zamykaj jak najszybciej.
- Unikaj wykonywania długich operacji (np. wysyłania e-maili, callów do API) wewnątrz otwartej transakcji.
- Rozważ zmianę poziomu izolacji transakcji na
READ COMMITTED, jeśli twoja aplikacja na to pozwala, bo eliminuje część blokad odczytu. - W MySQL z silnikiem InnoDB korzystaj z blokad na poziomie wiersza zamiast blokad tabelarycznych.
7. Zduplikowane lub zagnieżdżone podzapytania (subqueries)
Podzapytania w klauzuli WHERE lub SELECT mogą być bardzo wygodne, ale bywa, że są wykonywane osobno dla każdego wiersza tabeli nadrzędnej. To tzw. korelowane podzapytanie (correlated subquery) i potrafi dramatycznie spowalniać całe zapytanie.
Przykład problemu
…
Przy 10 000 klientów baza wykona 10 000 osobnych podzapytań.
Lepsze rozwiązanie
…
W przypadku bardziej złożonych zapytań warto rozważyć użycie CTE (Common Table Expressions, czyli WITH), które poprawiają czytelność i często pozwalają optymalizatorowi lepiej zaplanować wykonanie zapytania.
Jak systematycznie diagnozować wolne zapytania SQL?
Zamiast działać na ślepo, używaj narzędzi. Oto sprawdzony zestaw:
EXPLAIN i EXPLAIN ANALYZE
Podstawowe narzędzie w każdej bazie danych SQL. W MySQL i PostgreSQL dodaj EXPLAIN przed zapytaniem, żeby zobaczyć plan wykonania. EXPLAIN ANALYZE (PostgreSQL) lub EXPLAIN FORMAT=JSON (MySQL 8+) dają jeszcze więcej szczegółów, łącznie z rzeczywistymi czasami.
Slow Query Log w MySQL
Włącz logowanie wolnych zapytań, żeby mieć stały monitoring:
…
Logi analizuj narzędziem pt-query-digest z pakietu Percona Toolkit. To jedno z najskuteczniejszych narzędzi do identyfikacji kandydatów do optymalizacji.
Inne przydatne narzędzia
- pgBadger (PostgreSQL): analiza logów i generowanie raportów HTML
- MySQL Workbench: wizualny plan zapytania z kolorowym oznaczeniem kosztownych operacji
- Datadog / New Relic: monitorowanie wydajności zapytań w czasie rzeczywistym w środowiskach produkcyjnych
Regularny przegląd Slow Query Loga powinien być stałym elementem utrzymania każdej bazy danych produkcyjnej, nie tylko reakcją na kryzys.
Przyspiesz bazę danych: dobre praktyki na co dzień
Optymalizacja zapytań SQL to proces ciągły, nie jednorazowe działanie. Kilka zasad, które warto wdrożyć od razu:
- Analizuj plan wykonania każdego nowego, złożonego zapytania przed wdrożeniem na produkcję.
- Aktualizuj statystyki tabel regularnie (
ANALYZE TABLEw MySQL), żeby optymalizator miał aktualne dane o rozkładzie wartości. - Monitoruj wzrost danych: zapytanie, które działa świetnie przy 10 000 wierszy, może być katastrofalne przy 10 milionach. Testuj z realistycznym rozmiarem danych.
- Korzystaj z cache: dla często powtarzanych, niezmiennych wyników rozważ Redis lub Memcached zamiast uderzania w bazę danych za każdym razem.
- Normalizuj schemat z głową: zbyt duża normalizacja prowadzi do kosztownych złączeń, zbyt mała do duplikacji danych. Denormalizacja wybranych tabel pod kątem odczytu (read-optimized tables) to legalna i skuteczna technika.
Chcesz opanować SQL od podstaw do zaawansowanych technik?
Jeśli wolne zapytania SQL to dla Ciebie wciąż zagadka albo dopiero zaczynasz przygodę z bazami danych, warto zbudować solidne fundamenty. Na platformie VITA znajdziesz kurs SQL: Praktyczny Przewodnik po Bazach Danych, który przeprowadzi Cię przez wszystko: od podstaw składni, przez projektowanie schematów, aż po zaawansowane techniki optymalizacji i indeksowania.
Kurs jest dostępny w ramach abonamentu VITA. Możesz zacząć już dziś, korzystając z 7 dni pełnego dostępu za darmo do wszystkich kursów na platformie, bez żadnych kodów i bez zobowiązań. Anuluj kiedy chcesz.
Rozpocznij darmowy trial i sprawdź kurs SQL na vita.edu.pl/abonament