Dostrajanie wydajności bazy danych SQL dla programistów

Dostrajanie wydajności SQL może być niezwykle trudnym zadaniem, szczególnie podczas pracy z danymi o dużej skali, gdzie nawet najmniejsza zmiana może mieć dramatyczny (pozytywny lub negatywny) wpływ na wydajność.

W średnich i dużych firmach większość dostrajania wydajności SQL będzie obsługiwana przez administratora bazy danych (DBA). Ale uwierz mi, jest wielu programistów, którzy muszą wykonywać zadania typu DBA. Co więcej, w wielu firmach, które widziałem, które mają administratorów baz danych, często mają trudności z dobrą współpracą z programistami — stanowiska wymagają po prostu różnych sposobów rozwiązywania problemów, co może prowadzić do nieporozumień między współpracownikami.

Co więcej, struktura korporacyjna może również odgrywać pewną rolę. Załóżmy, że zespół DBA znajduje się na 10. piętrze ze wszystkimi swoimi bazami danych, podczas gdy programiści znajdują się na 15. piętrze, a nawet w innym budynku pod całkowicie osobną strukturą raportowania — w takich warunkach z pewnością ciężko jest ze sobą współpracować.

W tym artykule chciałbym osiągnąć dwie rzeczy:

1. Zapewnij programistom kilka technik dostrajania wydajności SQL po stronie programistów.
2. Wyjaśnij, jak programiści i administratorzy baz danych mogą efektywnie współpracować.

Dostrajanie wydajności SQL (w bazie kodu): Indeksy

Jeśli jesteś nowicjuszem w bazach danych i nawet zadajesz sobie pytanie „Co to jest dostrajanie wydajności SQL?”, powinieneś wiedzieć, że indeksowanie jest skutecznym sposobem dostrajania bazy danych SQL, który często jest zaniedbywany podczas projektowania. Zasadniczo indeks jest strukturą danych, która poprawia szybkość operacji pobierania danych z tabeli bazy danych, zapewniając szybkie losowe wyszukiwania i wydajny dostęp do uporządkowanych rekordów. Oznacza to, że po utworzeniu indeksu możesz wybierać lub sortować wiersze szybciej niż wcześniej.

Indeksy są również używane do definiowania klucza podstawowego lub indeksu unikatowego, który zagwarantuje, że żadne inne kolumny nie będą miały takich samych wartości. Oczywiście indeksowanie baz danych to obszerny, interesujący temat, do którego nie mogę oddać sprawiedliwości tym krótkim opisem (ale tutaj jest bardziej szczegółowy opis).

Jeśli jesteś nowy w indeksach, polecam skorzystać z tego diagramu podczas konstruowania zapytań:

Zasadniczo celem jest indeksowanie głównych kolumn wyszukiwania i porządkowania.

Zwróć uwagę, że jeśli Twoje tabele są stale uszkadzane przez INSERT , UPDATE i DELETE , powinieneś zachować ostrożność podczas indeksowania — możesz w efekcie zmniejszyć wydajność, ponieważ wszystkie indeksy muszą zostać zmodyfikowane po tych operacjach.

Co więcej, administratorzy baz danych często opuszczają swoje indeksy SQL przed wykonaniem wsadowego wstawiania ponad miliona wierszy, aby przyspieszyć proces wstawiania. Po wstawieniu partii ponownie tworzą indeksy. Pamiętaj jednak, że usunięcie indeksów wpłynie na każde zapytanie uruchomione w tej tabeli; dlatego to podejście jest zalecane tylko podczas pracy z pojedynczą, dużą wstawką.

SQL Tuning: plany wykonania w SQL Server

Przy okazji: narzędzie Execution Plan w SQL Server może być przydatne do tworzenia indeksów.

Jego główną funkcją jest graficzne przedstawienie metod pobierania danych wybranych przez optymalizator zapytań SQL Server. Jeśli nigdy wcześniej ich nie widziałeś, jest szczegółowy opis przejścia.

Aby pobrać plan wykonania (w SQL Server Management Studio), po prostu kliknij „Uwzględnij rzeczywisty plan wykonania” (CTRL + M) przed uruchomieniem zapytania.

Następnie pojawi się trzecia zakładka o nazwie „Plan wykonania”. Możesz zobaczyć wykryty brakujący indeks. Aby go utworzyć, wystarczy kliknąć prawym przyciskiem myszy w planie wykonania i wybrać „Brakujące szczegóły indeksu…”. To takie proste!

( Kliknij aby powiększyć )

Dostrajanie SQL: unikaj pętli kodowania

Wyobraź sobie scenariusz, w którym 1000 zapytań kolejno uderza w Twoją bazę danych. Coś jak:

 for (int i = 0; i < 1000; i++) { SqlCommand cmd = new SqlCommand("INSERT INTO TBL (A,B,C) VALUES..."); cmd.ExecuteNonQuery(); }

Powinieneś unikać takich pętli w swoim kodzie. Na przykład możemy przekształcić powyższy fragment kodu, używając unikalnej INSERT lub UPDATE z wieloma wierszami i wartościami:

 INSERT INTO TableName (A,B,C) VALUES (1,2,3),(4,5,6),(7,8,9) -- SQL SERVER 2008 INSERT INTO TableName (A,B,C) SELECT 1,2,3 UNION ALL SELECT 4,5,6 -- SQL SERVER 2005 UPDATE TableName SET A = CASE B WHEN 1 THEN 'NEW VALUE' WHEN 2 THEN 'NEW VALUE 2' WHEN 3 THEN 'NEW VALUE 3' END WHERE B in (1,2,3)

Upewnij się, że klauzula WHERE unika aktualizacji przechowywanej wartości, jeśli jest ona zgodna z istniejącą wartością. Taka trywialna optymalizacja może radykalnie zwiększyć wydajność zapytań SQL, aktualizując tylko setki wierszy zamiast tysięcy. Na przykład:

 UPDATE TableName SET A = @VALUE WHERE B = 'YOUR CONDITION' AND A <> @VALUE -- VALIDATION

Dostrajanie SQL: unikaj skorelowanych podzapytań SQL

Skorelowane podzapytanie to takie, które używa wartości z zapytania nadrzędnego. Ten rodzaj zapytania SQL ma tendencję do uruchamiania wiersz po wierszu, raz dla każdego wiersza zwróconego przez zapytanie zewnętrzne, co zmniejsza wydajność zapytania SQL. Nowi programiści SQL są często przyłapywani na konstruowaniu swoich zapytań w ten sposób — ponieważ zwykle jest to łatwa droga.

Oto przykład skorelowanego podzapytania:

 SELECT c.Name, c.City, (SELECT CompanyName FROM Company WHERE ID = c.CompanyID) AS CompanyName FROM Customer c

W szczególności problem polega na tym, że zapytanie wewnętrzne ( SELECT CompanyName… ) jest uruchamiane dla każdego wiersza zwróconego przez zapytanie zewnętrzne ( SELECT c.Name… ). Ale po co w kółko przechodzić przez Company dla każdego wiersza przetwarzanego przez zapytanie zewnętrzne?

Bardziej wydajną techniką dostrajania wydajności SQL byłaby refaktoryzacja skorelowanego podzapytania jako sprzężenia:

 SELECT c.Name, c.City, co.CompanyName FROM Customer c LEFT JOIN Company co ON c.CompanyID = co.CompanyID

W tym przypadku, na początku przechodzimy przez tabelę Company tylko raz i JOIN ją z tabelą Customer . Od tego momentu możemy efektywniej wybierać potrzebne nam wartości ( co.CompanyName ).

Dostrajanie SQL: Wybierz oszczędnie

Jedną z moich ulubionych wskazówek dotyczących optymalizacji SQL jest unikanie SELECT * ! Zamiast tego należy indywidualnie uwzględnić określone kolumny, których potrzebujesz. Znowu brzmi to prosto, ale wszędzie widzę ten błąd. Rozważ tabelę z setkami kolumn i milionami wierszy — jeśli aplikacja naprawdę potrzebuje tylko kilku kolumn, nie ma sensu przeprowadzać zapytań o wszystkie dane. To ogromne marnotrawstwo zasobów. ( Więcej problemów znajdziesz tutaj. )

Na przykład:

 SELECT * FROM Employees

vs.

 SELECT FirstName, City, Country FROM Employees

Jeśli naprawdę potrzebujesz każdej kolumny, wyraźnie wypisz każdą kolumnę. To nie jest reguła, ale raczej sposób na zapobieganie przyszłym błędom systemowym i dodatkowe dostrajanie wydajności SQL. Na przykład, jeśli używasz INSERT... SELECT... , a tabela źródłowa zmieniła się przez dodanie nowej kolumny, możesz napotkać problemy, nawet jeśli ta kolumna nie jest potrzebna w tabeli docelowej, np:

 INSERT INTO Employees SELECT * FROM OldEmployees Msg 213, Level 16, State 1, Line 1 Insert Error: Column name or number of supplied values does not match table definition.

Aby uniknąć tego rodzaju błędu z SQL Server, powinieneś zadeklarować każdą kolumnę osobno:

 INSERT INTO Employees (FirstName, City, Country) SELECT Name, CityName, CountryName FROM OldEmployees

Należy jednak zauważyć, że w niektórych sytuacjach użycie SELECT * może być odpowiednie. Na przykład z tabelami tymczasowymi — co prowadzi nas do następnego tematu.

Dostrajanie SQL: mądre wykorzystanie tabel tymczasowych (#Temp)

Tabele tymczasowe zwykle zwiększają złożoność zapytania. Jeśli twój kod można napisać w prosty, bezpośredni sposób, sugerowałbym unikanie tabel tymczasowych.

Ale jeśli masz procedurę składowaną z pewnymi manipulacjami danymi, których nie można obsłużyć za pomocą pojedynczego zapytania, możesz użyć tabel tymczasowych jako pośredników, aby pomóc w wygenerowaniu wyniku końcowego.

Kiedy musisz dołączyć do dużej tabeli i istnieją warunki na tej tabeli, możesz zwiększyć wydajność bazy danych, przesyłając dane do tabeli tymczasowej, a następnie wykonując na niej sprzężenie. Twoja tabela tymczasowa będzie miała mniej wierszy niż oryginalna (duża), więc łączenie zakończy się szybciej!

Decyzja nie zawsze jest prosta, ale ten przykład da ci sens w sytuacjach, w których możesz chcieć użyć tabel tymczasowych:

Wyobraź sobie tabelę klientów z milionami rekordów. Musisz dołączyć w określonym regionie. Możesz to osiągnąć, używając SELECT INTO , a następnie łącząc się z tabelą temp:

 SELECT * INTO #Temp FROM Customer WHERE RegionID = 5 SELECT r.RegionName, t.Name FROM Region r JOIN #Temp t ON t.RegionID = r.RegionID

( Uwaga: niektórzy programiści SQL również unikają używania SELECT INTO do tworzenia tabel tymczasowych, mówiąc, że to polecenie blokuje bazę danych tempdb, uniemożliwiając innym użytkownikom tworzenie tabel tymczasowych. Na szczęście zostało to naprawione w wersji 7.0 i nowszych ).

Jako alternatywę dla tabel tymczasowych możesz rozważyć użycie podzapytania jako tabeli:

 SELECT r.RegionName, t.Name FROM Region r JOIN (SELECT * FROM Customer WHERE RegionID = 5) AS t ON t.RegionID = r.RegionID

Ale poczekaj! Wystąpił problem z tym drugim zapytaniem. Jak opisano powyżej, powinniśmy uwzględnić w naszym podzapytaniu tylko te kolumny, których potrzebujemy (tzn. nie używać SELECT * ). Biorąc to pod uwagę:

 SELECT r.RegionName, t.Name FROM Region r JOIN (SELECT Name, RegionID FROM Customer WHERE RegionID = 5) AS t ON t.RegionID = r.RegionID

Wszystkie te fragmenty kodu SQL zwrócą te same dane. Ale w przypadku tabel tymczasowych moglibyśmy na przykład utworzyć indeks w tabeli tymczasowej, aby poprawić wydajność. Jest tu trochę dobrej dyskusji na temat różnic między tabelami tymczasowymi a podzapytaniami.

Wreszcie, kiedy skończysz z tabelą tymczasową, usuń ją, aby wyczyścić zasoby bazy danych tempdb, zamiast czekać na jej automatyczne usunięcie (tak jak będzie, gdy połączenie z bazą danych zostanie zakończone):

 DROP TABLE #temp

Dostrajanie SQL: „Czy mój rekord istnieje?”

Ta technika optymalizacji SQL dotyczy użycia EXISTS() . Jeśli chcesz sprawdzić, czy rekord istnieje, użyj EXISTS() zamiast COUNT() . Podczas gdy COUNT() skanuje całą tabelę, zliczając wszystkie wpisy pasujące do twojego warunku, EXISTS() zakończy działanie, gdy tylko zobaczy żądany wynik. Zapewni to lepszą wydajność i wyraźniejszy kod.

 IF (SELECT COUNT(1) FROM EMPLOYEES WHERE FIRSTNAME LIKE '%JOHN%') > 0 PRINT 'YES'

vs.

 IF EXISTS(SELECT FIRSTNAME FROM EMPLOYEES WHERE FIRSTNAME LIKE '%JOHN%') PRINT 'YES'

Dostrajanie wydajności SQL za pomocą SQL Server 2016

Jak zapewne wiedzą administratorzy baz danych pracujący z SQL Server 2016, w tej wersji nastąpiła ważna zmiana w zakresie ustawień domyślnych i zarządzania zgodnością. Jako główna wersja zawiera oczywiście nowe optymalizacje zapytań, ale kontrola nad tym, czy są one używane, jest teraz uproszczona za pośrednictwem sys.databases.compatibility_level .

Dostrajanie wydajności SQL (w biurze)

Administratorzy baz danych SQL (DBA) i programiści często ścierają się w kwestiach związanych i niezwiązanych z danymi. Zaczerpnięte z mojego doświadczenia, oto kilka wskazówek (dla obu stron), jak się dogadać i efektywnie współpracować.

Ćwierkać

Optymalizacja bazy danych dla programistów:

1. Jeśli Twoja aplikacja nagle przestanie działać, może to nie być problem z bazą danych. Na przykład może masz problem z siecią. Zbadaj trochę, zanim oskarżysz DBA!

2. Nawet jeśli jesteś modelarzem danych SQL typu ninja, poproś administratora baz danych o pomoc w tworzeniu diagramu relacyjnego. Mają wiele do przekazania i zaoferowania.

3. DBA nie lubią szybkich zmian. To naturalne: muszą analizować bazę danych jako całość i badać wpływ wszelkich zmian pod każdym kątem. Wdrożenie prostej zmiany w kolumnie może zająć tydzień — ale dzieje się tak dlatego, że błąd może zmaterializować się jako ogromne straty dla firmy. Bądź cierpliwy!

4. Nie proś administratorów baz danych SQL o wprowadzenie zmian w danych w środowisku produkcyjnym. Jeśli chcesz mieć dostęp do produkcyjnej bazy danych, musisz odpowiadać za wszystkie własne zmiany.

Optymalizacja bazy danych dla administratorów baz danych SQL Server:

1. Jeśli nie lubisz, gdy ludzie pytają Cię o bazę danych, daj im panel stanu w czasie rzeczywistym. Deweloperzy zawsze są podejrzliwi co do stanu bazy danych, a taki panel mógłby oszczędzić wszystkim czas i energię.

2. Pomóż programistom w środowisku testowania/zapewniania jakości. Ułatw sobie symulację serwera produkcyjnego za pomocą prostych testów na danych ze świata rzeczywistego. Będzie to znaczna oszczędność czasu zarówno dla innych, jak i dla Ciebie.

3. Deweloperzy spędzają cały dzień na systemach z często zmienianą logiką biznesową. Spróbuj zrozumieć, że świat jest bardziej elastyczny i umieć złamać niektóre zasady w krytycznym momencie.

4. Bazy danych SQL ewoluują. Nadejdzie dzień, w którym będziesz musiał przenieść swoje dane do nowej wersji. Deweloperzy liczą na znaczącą nową funkcjonalność z każdą nową wersją. Zamiast odmawiać akceptacji zmian, zaplanuj z wyprzedzeniem i przygotuj się na migrację.