Optymalizacja baz danych i zapytań SQL

Optymalizacja baz danych i zapytań SQL to kluczowy element sprawnego działania aplikacji, szczególnie gdy ilość danych rośnie. Nawet dobrze zaprojektowana baza może działać wolno bez odpowiednich technik optymalizacyjnych. Ten kurs pokaże Ci, jak analizować, wykrywać i rozwiązywać najczęstsze problemy z wydajnością.

>>Spis treści

1. Dlaczego optymalizacja jest ważna?
2. Indeksowanie danych
   • Rodzaje indeksów
   • Tworzenie i usuwanie indeksów
   • Kiedy indeksy przeszkadzają?
3. Optymalizacja zapytań SQL
   • Analiza planu wykonania (EXPLAIN)
   • Unikanie typowych błędów
   • Pisanie wydajnych zapytań
4. Normalizacja a denormalizacja danych
5. Tuning bazy danych i parametry konfiguracyjne
6. Monitorowanie wydajności
7. Praktyczne zadania
8. Podsumowanie

>>Dlaczego optymalizacja jest ważna?

• Duże ilości danych mogą znacząco spowolnić nawet dobrze zaprojektowaną bazę.
• Wydajne zapytania to krótszy czas odpowiedzi i mniejsze zużycie zasobów serwera.
• Niska wydajność bazy może prowadzić do spadku satysfakcji użytkowników, błędów i kosztów utrzymania.

>>Indeksowanie danych

Indeks to specjalna struktura danych (np. drzewo B-drzewiaste), która przyspiesza wyszukiwanie i sortowanie danych w tabeli. Działa jak spis treści w książce.

>>>Rodzaje indeksów

• Indeks podstawowy (PRIMARY) – na kluczu głównym, zawsze unikalny.
• Indeksy unikalne (UNIQUE) – zapewniają unikalność wartości w kolumnie.
• Indeksy zwykłe – przyspieszają wyszukiwanie, ale mogą mieć powtarzające się wartości.
• Indeksy złożone (wielokolumnowe) – na kilku kolumnach jednocześnie.
• Indeksy pełnotekstowe – do wyszukiwania tekstów.

>>>Tworzenie i usuwanie indeksów

Tworzenie indeksu:

CREATE INDEX idx_nazwisko ON Pracownik(nazwisko);

• CREATE INDEX – polecenie tworzące indeks.
• idx_nazwisko – nazwa indeksu.
• ON Pracownik(nazwisko) – tabela i kolumna, na której tworzony jest indeks.

Usuwanie indeksu:

DROP INDEX idx_nazwisko ON Pracownik;

>>>Kiedy indeksy przeszkadzają?

• Zbyt wiele indeksów spowalnia operacje INSERT, UPDATE, DELETE (każda zmiana wymaga aktualizacji indeksów).
• Indeksy zajmują dodatkowe miejsce na dysku.
• Indeksy nie pomagają przy operacjach na małych tabelach.

>>Optymalizacja zapytań SQL

>>>Analiza planu wykonania (EXPLAIN)

Polecenie EXPLAIN pokazuje, jak baza zamierza wykonać zapytanie – czy użyje indeksu, jak będzie łączyć tabele itd.

Przykład:

EXPLAIN SELECT * FROM Pracownik WHERE nazwisko = 'Kowalski';

• Sprawdź, czy w kolumnie "key" pojawia się nazwa indeksu.

>>>Unikanie typowych błędów

• SELECT * (wszystkie kolumny) – pobieraj tylko potrzebne kolumny.
• Brak WHERE – zapytania bez warunków mogą przetwarzać całą tabelę.
• Zbyt złożone podzapytania lub zagnieżdżone JOINy bez indeksów.
• Filtrowanie po kolumnie bez indeksu.

>>>Pisanie wydajnych zapytań

• Używaj WHERE, by ograniczyć liczbę przetwarzanych rekordów.
• Łącz tabele po indeksowanych kolumnach (kluczach głównych/obcych).
• Unikaj funkcji na kolumnach w WHERE (np. WHERE LOWER(nazwisko) = 'kowalski') – indeksy wtedy nie działają.
• Stosuj LIMIT, jeśli nie potrzebujesz wszystkich wyników.

>>Normalizacja a denormalizacja danych

• Normalizacja – dzielenie danych na tabele, by unikać powielania informacji i zapewnić spójność (zazwyczaj do 3NF).
• Denormalizacja – celowe powielanie danych w tabelach (np. trzymanie nazwy produktu przy zamówieniu) w celu przyspieszenia odczytu danych, kosztem spójności i miejsca.

Kiedy warto denormalizować?

• Gdy odczyty są znacznie częstsze niż zapisy.
• Gdy optymalizacja zapytań jest ważniejsza niż minimalizacja powielania danych.

>>Tuning bazy danych i parametry konfiguracyjne

• Cache (buforowanie) – bazy danych posiadają wbudowane mechanizmy buforowania wyników zapytań i stron danych.
• Konfiguracja pamięci operacyjnej – wielkość buforów, liczba połączeń.
• Partycjonowanie tabel – dzielenie dużych tabel na mniejsze części (np. wg daty).
• Replikacja – kopiowanie danych na różne serwery, by rozłożyć obciążenie.

>>Monitorowanie wydajności

• Używaj wbudowanych narzędzi (np. SHOW PROCESSLIST, pg_stat_activity).
• Loguj czas wykonania zapytań.
• Analizuj statystyki indeksów i fragmentację tabel.
• Wdrażaj alerty przy długotrwałych zapytaniach.

>>Praktyczne zadania

>>>Zadanie 1: Tworzenie indeksu

Stwórz indeks na kolumnie "email" w tabeli "Uzytkownicy".

CREATE INDEX idx_email ON Uzytkownicy(email);

>>>Zadanie 2: Analiza zapytania

Sprawdź plan wykonania poniższego zapytania:

SELECT * FROM Produkty WHERE nazwa = 'Laptop';

EXPLAIN SELECT * FROM Produkty WHERE nazwa = 'Laptop';

>>>Zadanie 3: Optymalizacja zapytania

Masz zapytanie:

SELECT * FROM Zamowienie WHERE YEAR(data_zamowienia) = 2025;

Jak je poprawić, by lepiej wykorzystać indeks na kolumnie data_zamowienia?

SELECT * FROM Zamowienie WHERE data_zamowienia >= '2025-01-01' AND data_zamowienia < '2026-01-01';

>>>Zadanie 4: Wydajność a liczba indeksów

Wyjaśnij, dlaczego zbyt wiele indeksów może negatywnie wpłynąć na wydajność bazy.

>>>Zadanie 5: Denormalizacja

Podaj przykład sytuacji, kiedy warto zastosować denormalizację.

>>Podsumowanie

Optymalizacja baz danych i zapytań SQL pozwala obsługiwać większą ilość danych szybciej i taniej. Korzystaj z indeksów, analizuj plany wykonania, dbaj o strukturę bazy i monitoruj jej działanie – to klucz do sukcesu każdego większego projektu informatycznego!