Zapytanie, które w zeszłym roku trwało milisekundy, dzisiaj może zająć kilka minut, po prostu dlatego, że tabela się rozrosła. Optymalizacja zapytań SQL to mniej sprytne sztuczki, a bardziej powtarzalny proces: dowiedz się, co robi baza danych, usuń zmarnowaną pracę i sprawdź, czy pomogło.

Przetestowaliśmy każdą poniższą technikę na milionie wygenerowanych wierszy w SQLite 3.50. Czasy są najlepsze z kilku przebiegów z jednego laptopa przy użyciu bazy danych w pamięci. Nie będą pasować do Twojego systemu, a PostgreSQL, MySQL i SQL Server mają własne optymalizatory i formaty planów. Skoncentruj się na wielkości każdego ulepszenia i zasadach, które za nim stoją.

Wyniki w skrócie

Konfiguracja testowa

CREATE TABLE customers (
  id INTEGER PRIMARY KEY, name TEXT, city TEXT
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY, customer_id INT, status TEXT,
  created TEXT, total INT, note TEXT
);
-- 50,000 customers and 1,000,000 orders with random values

Prosty proces optymalizacji

1. Znajdź istotne zapytania. Korzystaj z dzienników wolnych zapytań lub monitorowania. Zapytanie trwające 200 ms i uruchamiane tysiąc razy na minutę może mieć większe znaczenie niż 10-sekundowy raport miesięczny.
2. Przeczytaj plan. Zobacz, co naprawdę robi baza danych.
3. Zmień jedną rzecz. Dodaj indeks lub przepisz część zapytania.
4. Zmierz ponownie na realistycznych danych. Plan obejmujący 100 wierszy testowych niewiele mówi o milionie.
5. Obserwuj z biegiem czasu. Plany mogą się zmieniać w miarę zwiększania się ilości danych i zmiany statystyk.

Przeczytaj plan wykonania

Plan wykonania to strategia bazy danych dotycząca wykonywania zapytania krok po kroku, obejmująca używane przez nią indeksy i kolejność łączenia tabel. Poprzedź zapytanie, aby je zobaczyć:

Zauważ, że EXPLAIN ANALYZE w PostgreSQL naprawdę uruchamia zapytanie, więc zachowaj ostrożność, używając go z UPDATE lub DELETE. Oto, czego należy szukać w pierwszej kolejności:

Pełne skanowanie nie jest automatycznie złe. Jeśli zapytanie zajmuje większość małej tabeli, najszybszym wyborem może być skanowanie. W PostgreSQL EXPLAIN ANALYZE porównaj także szacunkowe wiersze z rzeczywistymi wierszami. Duża różnica często oznacza, że ​​statystyki tabeli są nieaktualne.

Dodaj odpowiedni indeks

To zapytanie wyszukuje zamówienia jednego klienta:

SELECT id, total FROM orders WHERE customer_id = 123;

Przed dodaniem indeksu plan zakładał pełne skanowanie:

SCAN orders
-- 20 rows in 73 ms

Następnie dodaliśmy indeks w kolumnie filtra:

CREATE INDEX idx_orders_customer ON orders(customer_id);

SEARCH orders USING INDEX idx_orders_customer (customer_id=?)
-- 20 rows in 0.01 ms

Te same 20 wierszy wróciło tysiące razy szybciej, ponieważ baza danych nie czytała już miliona wierszy, aby je znaleźć. Żadna inna technika opisana w tym artykule nie zapewniła nam większej wygranej. Dobrymi pierwszymi kandydatami są kolumny używane w WHERE, JOIN i ORDER BY, zwłaszcza gdy zawężają wyniki do kilku wierszy. Nasz przewodnik po indeksach SQL wyjaśnia, jak one działają.

Nie zawijaj indeksowanych kolumn w funkcjach

Oto dwa sposoby na znalezienie zamówień marcowych, z indeksem na created:

-- Function on the column
SELECT id FROM orders WHERE substr(created, 1, 7) = '2026-03';
-- SCAN orders USING COVERING INDEX idx_orders_created
-- 84,961 rows in 96 ms

-- Plain range
SELECT id FROM orders
WHERE created >= '2026-03-01' AND created < '2026-04-01';
-- SEARCH orders USING COVERING INDEX idx_orders_created (created>? AND created<?)
-- 84,961 rows in 33 ms

Oba zwracają identyczne wiersze, ale pierwszy trwał około trzy razy dłużej. Plan pokazuje dlaczego: przy funkcji w kolumnie baza danych nie może przeskoczyć do pozycji w posortowanym indeksie, więc musi przejść SCAN przez każdy wpis. Wersja asortymentowa może SEARCH trafić prosto we właściwe miejsce. Warunek, który może używać indeksu w ten sposób, nazywa się sargable.

Ten sam problem pojawia się w przypadku tych typowych wzorców:

To ostatnie też sprawdziliśmy. Wygenerowano wiodący symbol wieloznaczny SCAN customers, ponieważ indeks posortowany od pierwszej litery nie może pomóc w dopasowaniu na końcu.

Indeksy złożone

Gdy zapytanie filtruje według dwóch kolumn, indeks jednokolumnowy wykonuje tylko część zadania:

SELECT id FROM orders
WHERE status = 'paid'
  AND created >= '2026-03-01' AND created < '2026-03-08';

-- Using only the created index: 6,198 rows in 19 ms

CREATE INDEX idx_status_created ON orders(status, created);

-- SEARCH orders USING COVERING INDEX idx_status_created
--   (status=? AND created>? AND created<?)
-- 6,198 rows in 2.2 ms

Kolejność kolumn ma ogromne znaczenie. Indeks na (status, created) działa jak książka telefoniczna posortowana według nazwiska, a następnie imienia. Można wyszukiwać samo nazwisko, ale nie samo imię. Nasze zapytanie dotyczące samego status mogłoby skorzystać z tego indeksu. Samo filtrowanie na podstawie created generalnie nie pozwala na jego efektywne wykorzystanie. Ogólna zasada polega na tym, że kolumny testowane z równością należy umieszczać na pierwszym miejscu, a kolumny zakresu po nich.

Plan ten mówi również o indeksie obejmującym, co oznacza, że ​​indeks zawierał wszystko, czego potrzebowało zapytanie, więc sama tabela nie musiała być czytana. Wybranie tylko tych kolumn, których potrzebujesz, zwiększa to prawdopodobieństwo, co jest kolejnym powodem, aby tego unikać SELECT *.

Szybsza paginacja

Używanie OFFSET do głębokich stron jest ukrytym kosztem, ponieważ baza danych nadal musi przejść obok wszystkich pominiętych wierszy:

-- Page deep into the results
SELECT id FROM orders ORDER BY id LIMIT 20 OFFSET 500000;
-- 8.1 ms

-- Keyset pagination: continue from the last id seen
SELECT id FROM orders WHERE id > 500000 ORDER BY id LIMIT 20;
-- 0.01 ms

Paginacja zestawu kluczy pozostaje szybka niezależnie od tego, jak głęboko się zagłębisz. Zapobiega także przesuwaniu się wierszy między stronami po dodaniu nowych danych. Kompromis polega na tym, że użytkownicy nie mogą od razu przejść do 5000 stron, co rzadko jest realną potrzebą. W tabelach z szerokimi rzędami lub z większą liczbą wierszy do pominięcia różnica może być znacznie większa niż w naszym teście.

Pisz prostsze zapytania

• Wybierz tylko te kolumny, których potrzebujesz. SELECT * wysyła więcej danych i może uniemożliwić indeksowi pokrycie zapytania.
• Wcześnie filtruj. Użyj WHERE, aby usunąć wiersze przed grupowaniem, a HAVING tylko w przypadku warunków dotyczących agregatów. Zobacz SQL GROUP BY i HAVING.
• Użyj LIMIT, jeśli potrzebujesz tylko kilku wierszy.
• Preferuj UNION ALL, jeśli nie chcesz usuwać duplikatów. Nasz test UNION vs UNION ALL pokazuje, jakie to oszczędza.
• Unikaj niepotrzebnego DISTINCT. Często polega to na ukrywaniu złączenia, które powoduje duplikaty.
• Do sprawdzania istnienia użyj EXISTS. Może zatrzymać się na pierwszym dopasowaniu, jak wyjaśnia nasz przewodnik po podzapytaniach SQL.
• Uważaj na N+1 zapytań. Jedno zapytanie na wiersz w pętli, zwykle powodowane przez kod aplikacji, często stanowi prawdziwy problem. Pojedyncze zapytanie łączące lub wsadowe może zastąpić setki podróży w obie strony.

Dołącza

Indeksuj kolumny, do których dołączasz. Wygenerowaliśmy raport dotyczący całkowitych wydatków klientów w jednym mieście i okazało się, że plan był dokładnie taki, jakiego można było oczekiwać:

SELECT c.name, SUM(o.total)
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.city = 'Goa'
GROUP BY c.id;

-- SCAN c
-- SEARCH o USING COVERING INDEX idx_cover (customer_id=?)
-- 9,900 rows in 34 ms

Skanuje klientów, a następnie wykorzystuje indeks, aby znaleźć zamówienia każdego z nich. Bez indeksu na orders.customer_id baza danych musiałaby wykonać znacznie więcej pracy dla każdego klienta. Indeks klucza obcego jest jednym z najpowszechniejszych i najcenniejszych dodatków, jakie można wprowadzić. Aby odświeżyć wiedzę na temat podstaw, zobacz Wyjaśnienie złączeń SQL.

Kiedy indeksy Cię kosztują

Indeksy nie są darmowe, więc nie dodawaj ich dla każdej kolumny:

• Wolniejsze zapisywanie. Każde wstawienie, aktualizacja i usunięcie musi także zaktualizować każdy indeks.
• Więcej miejsca i pamięci. Indeksy mogą rosnąć w dużych tabelach.
• Niewielkie korzyści w przypadku kolumn nieselektywnych. Indeks w kolumnie „prawda” lub „fałsz” może nie pomóc, jeśli większość wierszy jest zgodna.
• Nadmiarowość. Indeks w (a) jest często niepotrzebny, jeśli już go masz w (a, b).

Sprawdzaj okresowo statystyki użytkowania i usuwaj indeksy, z których nie korzystasz. I utrzymuj świeże statystyki za pomocą ANALYZE lub odpowiednika bazy danych, ponieważ optymalizator opiera się na nich przy wyborze dobrego planu. Czasami powolną częścią jest raczej rywalizacja niż zapytanie. Długotrwałe transakcje trzymające blokady mogą sprawić, że szybkie zapytanie będzie wydawać się powolne.

Typowe błędy

• Optymalizacja bez pomiaru. Wiele pewnych domysłów okazuje się błędnych.
• Testowanie na małych danych. Plany i harmonogramy zmieniają się dramatycznie w miarę powiększania się tabel.
• Zaufanie wskazówce dotyczącej innej bazy danych. Porady dotyczące jednego silnika mogą nie mieć zastosowania w przypadku innego.
• Dodawanie indeksów na ślepo. Każdy spowalnia zapis.
• Ignorowanie efektu pierwszego uruchomienia. Pamięci podręczne przyspieszają drugie uruchomienie, więc uruchamiaj je kilka razy.

Podsumowanie

Większość powolnych zapytań ma zwykłe przyczyny: brak indeksu, funkcja w kolumnie lub pobieranie zbyt dużej ilości danych. Weź w tym tygodniu najwolniejsze prawdziwe zapytanie, uruchom na nim EXPLAIN i poszukaj pełnego skanowania na dużym stole. Napraw jedną rzecz, zmierz ponownie i zapisuj, co zmieniłeś.

Najczęstsze pytania

Ucz się dalej