SQL – pisanie wydajnych zapytań

Poniżej przedstawiam zasady pisania zapytań SQL, które jak najszybciej generują wyniki, z których korzystam w codziennej pracy:

  1. Pobieramy tylko te kolumny i wiersze, które są nam niezbędne. Najprostsza zasada generowania efektywnych zapytań. Nie piszemy SELECT *, wybieramy tylko interesujące nas kolumny. To samo z wierszami – filtrujemy wartości tylko do tych mających wpływ na wynik. SELECT * wybieram tylko wtedy, gdy sprawdzam zawartość tabeli – nazwy kolumn i typy danych – zawsze wtedy z klauzulą LIMIT np. SELECT * FROM orders LIMIT 100
  2. Użycie indeksów. Nałożenie indeksu jest jak ponumerowanie kartek w zeszycie. Zajmuje sporo czasu, ale potem wyszukanie danych w nim jest błyskawiczne. Warto zatem nakładać indeksy na kolumny, które często występują w zapytaniach. Nie nakładamy indeksów na kolumny, które rzadko są filtrowane w zapytaniach – niepotrzebnie obciąża to bazę. Ponadto pamiętajmy, że update’y i inserty do tabeli z zindeksowanymi kolumnami trwają dłużej – trzeba wybrać optymalne rozwiązanie np. usunąć indeks przed insertem i ponownie go nałożyć po skończonej operacji.
  3. Unikanie podzapytań (subqueries). Lista zalet używania podzapytań jest bardzo krótka. Zmniejszają czytelność kodu i zwiększają znacznie czas zapytania. Jeśli to możliwe, korzystamy z JOIN-ów, które są optymalne wydajnościowo. Aby zwiększyć czytelność, ułatwić debugowanie i w dużej części przypadków przyspieszyć kod – stosujemy klauzulę WITH.
  4. Unikanie UNION. Użycie UNION może być pułapką, która może bardzo zwiększyć czas generowania wyników. Sprawdzenie czy nie występują takie same wiersze w dwóch wielkich tabelach znacznie obciąża bazę. Kiedy nie ma ryzyka uzyskania duplikatów, zawsze korzystamy z UNION ALL.
  5. Unikanie działań na kolumnach datetime. Działania na kolumnach datetime zawsze obciążają bazę bardziej niż inne typy danych, ponieważ zawierają wiele różnych wartości i nie są możliwe do efektywnej kompresji przez silniki baz danych. Warto podzielić sobie kolumnę datetime na kolumnę dat i osobno kolumnę czasu lub zrezygnować z tej ostatniej, jeśli nie jest potrzebna.
  6. Rozważne warunki w WHERE. W tej klauzuli tam gdzie to tylko możliwe korzystamy z funkcji IN, zamiast powtarzania OR dla tej samej kolumny. Baza będzie wtedy przeszukiwana n-krotnie mniej razy. Np. zamiast SELECT customer_name FROM customers WHERE id = 1 or id = 2, piszemy SELECT customer_name FROM customers WHERE id IN (1,2)
  7. Unikanie LIKE. Czasem łatwo dla pewności dodać warunek np. name like '%NOWAK%'. Jeżeli mamy pewność danych w tabeli, piszemy name = 'NOWAK'
  8. Użycie zdefiniowanych wcześniej funkcji i procedur. Po pewnym czasie nasze zapytania w pracy są najczęściej bardzo podobne. Można wtedy zdefiniować je w funkcję lub procedury (obsługują transakcje), także z parametrami. Szczególnie wzrost wydajności będzie widoczny w przypadku wywoływania złożonych zapytań.
  9. Unikanie DISTINCT. DISTINCT podobnie jak w przypadku UNION stosujemy tylko wtedy gdy jest to konieczne. Wyszukiwanie duplikatów zawsze obciąża bazę.
  10. Łączenie tylko po JOIN. Użycie przestarzałego sposobu łączenia tabel po klauzuli WHERE jest także mniej wydajne.
  11. EXISTS zamiast IN w podzapytaniach. Jeżeli naprawdę musimy użyć podzapytania w klauzuli WHERE, zamiast SELECT name FROM orders WHERE customer_id IN (SELECT customer_id FROM customers), piszemy SELECT name FROM orders WHERE EXISTS (SELECT customer_id FROM customers)
  12. Na koniec, ogólna rada aby ustawić uruchamianie kosztownych wydajnościowo kodów w nocy 🙂