Optymalizacja zapytań SQL: poznaj 9 sekretnych praktyk
Chcesz, aby Twoje zapytania SQL były szybsze? Sprawdź w jaki sposób napisać bardziej wydajne zapytania SQL.
SQL, czyli Structured Query Language, to język programowania używany do zarządzania relacyjnymi bazami danych. Służy do tworzenia, modyfikowania, oraz uzyskiwania danych z baz danych. SQL umożliwia precyzyjne zapytania, aktualizacje i operacje na danych, co sprawia, że jest nieodłącznym narzędziem dla programistów i administratorów baz danych, ma jednak kilka swoich przywar. Oto 9 sekretnych praktyk, dzięki którym optymalizacja zapytań SQL stanie się faktem.
- Pobieraj tylko potrzebne kolumny
- Używaj CASE zamiast UPDATE do aktualizacji kolumn warunkowych
- Ogranicz do minimum zapytania dotyczące dużych tabel
- Wstępnie przygotuj swoje dane
- Wykonuj usuwanie i aktualizacje partiami
- Użyj tabel tymczasowych, aby poprawić wydajność kursora
- Używaj funkcji z wartościami przechowywanymi w tabeli zamiast funkcji skalarnych
- Użyj partycjonowania, aby uniknąć przenoszenia dużych ilości danych
- Używaj procedur składowanych dla wydajności, używaj ORM dla wygody
1. Pobieraj tylko potrzebne kolumny
Powszechnym zwyczajem w SQL jest używanie SELECT * w zapytaniu, ponieważ wypisywanie wszystkich potrzebnych kolumn jest pracochłonne. Poza tym czasami te kolumny mogą się zmieniać z biegiem czasu, więc dlaczego nie zrobić tego po prostu w najprostszy sposób?
Ale co się stanie, jeśli wykonasz zapytanie do wszystkich kolumn w tabeli zawierającej sto lub więcej kolumn? Takie olbrzymy pojawiają się dosyć często i nie zawsze możliwe jest przerobienie ich na bardziej rozsądny schemat. Czasami jedynym sposobem na okiełznanie tej bestii jest wybranie podzbioru kolumn, dzięki czemu inne zapytania nie będą pozbawione zasobów.
Można używać SELECT * podczas prototypowania zapytania, ale wszystkie zapytania SQL, które trafiają do produkcji, powinny wybierać tylko rzeczywiście potrzebne kolumny.
2. Używaj CASE zamiast UPDATE do aktualizacji kolumn warunkowych
Kolejną rzeczą, którą często robią programiści, jest użycie UPDATE… WHERE do ustawienia wartości jednej kolumny na podstawie wartości innej kolumny, np. UPDATE Users SET Users.Status=”Legacy” WHERE Users.ID<1000. To podejście jest proste i intuicyjne, ale czasami dodaje niepotrzebny dodatkowy krok.
Na przykład, jeśli wstawisz dane do tabeli, a następnie użyjesz UPDATE, aby je zmienić, używając wcześniej przytoczonego zapytania, będą to dwie oddzielne transakcje. Jeśli masz miliony wierszy, dodatkowe transakcje mogą spowodować wiele niepotrzebnych operacji.
Lepszym rozwiązaniem w przypadku tak dużej operacji jest użycie instrukcji CASE w zapytaniu SQL w celu ustawienia wartości kolumny podczas samej operacji wstawiania rekordu. W ten sposób obsłużysz zarówno początkowe wstawianie rekordu, jak i modyfikowanie danych tylko w jednym przebiegu.
3. Ogranicz do minimum zapytania dotyczące dużych tabel
Zapytania dotyczące tabel dowolnej wielkości nie są dobrym nawykiem. Z kolei zapytania dotyczące tabel zawierających setki milionów lub miliardy wierszy absolutnie nie są najlepszą praktyką.
Jeśli to możliwe, konsoliduj zapytania na dużych tabelach w możliwie najmniejszej liczbie odrębnych operacji. Na przykład, jeśli masz tabelę, w której chcesz najpierw wykonać zapytanie o jedną kolumnę, a następnie o inną, najpierw połącz ją w jedno zapytanie, a następnie upewnij się, że kolumny, do których odpytujesz, mają indeks pokrywający.
Jeśli zauważysz, że bierzesz ten sam podzbiór danych z dużej tabeli i uruchamiasz względem niego mniejsze zapytania, możesz przyspieszyć pracę sobie i innym. Możesz ten podzbiór utrwalić w innym miejscu i wysyłać do niego tylko te zapytania, które go dotyczą.
4. Wstępnie przygotuj swoje dane
Załóżmy, że Ty lub inne osoby rutynowo uruchamiacie raporty lub procedury składowane, które wymagają agregowania dużej ilości danych poprzez połączenie kilku dużych tabel. Zamiast za każdym razem ponownie uruchamiać łączenie, możesz zaoszczędzić sobie (i wszystkim innym) dużo pracy, wstępnie umieszczając potrzebne dane w oddzielnej tabeli tymczasowej, która została przygotowana specjalnie do tego celu. Raporty lub procedury można następnie uruchomić w oparciu o tę tabelę, więc wspólną dla nich pracę wystarczy wykonać tylko raz. Jeśli masz na to zasoby i obsługuje to Twoja baza danych, możesz użyć tabeli typu „In-memory”, aby jeszcze bardziej przyspieszyć to działanie.
5. Wykonuj usuwanie i aktualizacje partiami
Wyobraź sobie tabelę zawierającą miliardy wierszy, z których należy usunąć miliony rekordów. Naiwnym podejściem jest po prostu uruchomienie polecenia DELETE w transakcji. Wtedy jednak cała tabela zostanie zablokowana do czasu zakończenia transakcji.
Bardziej wyrafinowane podejście polega na wykonaniu operacji usuwania (lub aktualizacji) w partiach, które można przeplatać z innymi elementami. Każda transakcja staje się mniejsza i łatwiejsza w zarządzaniu, a wokół operacji i w jej trakcie mogą odbywać się inne prace.
Z punktu widzenia aplikacji jest to dobry przypadek użycia kolejki zadań, która może śledzić postęp operacji w sesjach i umożliwiać ich wykonywanie jako operacji w tle o niskim priorytecie.
6. Użyj tabel tymczasowych, aby poprawić wydajność kursora
W większości przypadków należy unikać kursorów – są powolne, blokują inne operacje, a wszystko, co osiągają, prawie zawsze można wykonać w inny sposób. Jeśli jednak z jakiegoś powodu utkniesz używając kursora, tabela tymczasowa może zmniejszyć problemy z wydajnością.
Na przykład, jeśli chcesz przeglądać tabelę w pętli i zmieniać kolumnę w oparciu o pewne obliczenia, możesz pobrać dane kandydatów, które chcesz zaktualizować, umieścić je w tabeli tymczasowej, przeglądać je kursorem, a następnie zastosować wszystkie aktualizacje w jednej operacji. W ten sposób możesz także podzielić przetwarzanie kursora na partie.
7. Używaj funkcji z wartościami przechowywanymi w tabeli zamiast funkcji skalarnych
Funkcje skalarne umożliwiają hermetyzację obliczeń we fragmencie kodu SQL przypominającym procedurę składowaną. Powszechną praktyką jest zwracanie wyników funkcji skalarnej jako kolumny w zapytaniu SELECT.
Jeśli często robisz to w Microsoft SQL Server, możesz uzyskać lepszą wydajność, używając zamiast tego funkcji z wartościami przechowywanymi w tabeli i używając CROSS APPLY w zapytaniu. Więcej informacji na temat mało omawianego operatora APPLY można znaleźć w tym module szkoleniowym w Microsoft Virtual Academy.
8. Użyj partycjonowania, aby uniknąć przenoszenia dużych ilości danych
SQL Server Enterprise oferuje opcję „partycjonowania”, która umożliwia dzielenie tabel bazy danych na wiele partycji. Jeśli masz tabelę, którą stale archiwizujesz w innej tabeli, możesz uniknąć używania INSERT/DELETE do przenoszenia danych i zamiast tego użyć SWITCH.
Na przykład, jeśli masz tabelę, która jest codziennie opróżniana do tabeli archiwalnej, możesz wykonać tę operację opróżniania i kopiowania za pomocą przełącznika, aby po prostu przypisać strony tabeli dziennej do tabeli archiwalnej. Proces przełączania zajmuje o rząd wielkości mniej czasu niż ręczne kopiowanie i usuwanie. Tutaj jest doskonały tutorial na temat korzystania z partycjonowania w ten sposób.
9. Używaj procedur składowanych dla wydajności, używaj ORM dla wygody
ORMS – obiektowo-relacyjne mappery – to narzędzia programistyczne generujące kod SQL programowo. Pozwalają one używać języka programowania aplikacji i jego metafor do tworzenia i utrzymania zapytań.
Wielu programistów baz danych nie lubi ORM-ów z zasady. Są one znane z generowania nieefektywnego i czasem nieoptymalizowanego kodu, a także z tego, że zmniejszają chęć programistów do nauki SQL i zrozumienia, jak zachowują się zapytania. Kiedy programista musi ręcznie napisać zapytanie, aby uzyskać najlepszą wydajność, nie wie, jak to zrobić.
Z drugiej strony, ORM-y znacznie ułatwiają pisanie i utrzymanie kodu bazy danych. Część bazy danych aplikacji nie jest oddzielona w innym obszarze, a napisana jest w sposób, który jest luźniej związany z logiką aplikacji.
Najbardziej sensowne jest używanie procedur składowanych do zapytań, które są często wywoływane, wymagają dobrej wydajności, prawdopodobnie nie będą często zmieniane (jeśli w ogóle) i muszą być analizowane pod kątem wydajności przez narzędzia profilowania bazy danych. Większość baz danych ułatwia uzyskanie takich statystyk zbiorczo dla procedury składowanej niż dla zapytania ad hoc. Procedury składowane są również łatwiejsze do zoptymalizowania przez planistę zapytań bazy danych.
Wadą przenoszenia większej części logiki bazy danych do procedur składowanych jest to, że logika ta jest o tyle bardziej ściśle związana z bazą danych. Procedury składowane mogą zmienić się z korzyści dla wydajności w ogromny dług techniczny. Jeśli zdecydujesz się później na migrację do innej technologii baz danych, łatwiej jest zmienić cel ORM niż przepisywać wszystkie procedury składowane. Ponadto, kod wygenerowany przez ORM może być sprawdzony pod kątem optymalizacji, a pamięć podręczna zapytań często pozwala na ponowne użycie najczęściej generowanych zapytań SQL. Jeśli ważna jest jakość utrzymania po stronie aplikacji, użyj ORM. Jeśli chodzi o wydajność po stronie bazy danych, użyj procedur składowanych.
Optymalizacja zapytań SQL. Podsumowanie
Wdrożenie tych dziewięciu sekretnych praktyk może być kluczowe dla optymalizacji zapytań SQL do Twojej bazy danych. Pamiętaj jednak, że każdy projekt jest unikalny, więc eksperymentuj, monitoruj wyniki i dostosuj te praktyki do specyfiki Twojego systemu. Zdobądź przewagę w wydajności SQL i pozwól Twojej bazie danych działać jak szybki, dobrze naoliwiony mechanizm.
