Наша команда-партнер Artmisto
> SQL > [Tłumaczenie] Wskazówki dotyczące optymalizacji indeksu
W zeszłym tygodniu w pracy pracowałem nad optymalizacją pracy bazy danych MS SQL i natrafiłem na listę przydatnych wskazówek dotyczących optymalizacji indeksów w bazie danych. Poniżej znajduje się tłumaczenie tego artykułu. Wiele z tych wskazówek jest oczywistych, ale nie zawsze praktycznych.
Oryginał: http://www.mssqlcity.com/Tips/tipInd.htm
- Spróbuj utworzyć indeks na kolumnach często używanych w wyrażeniach WHERE, ORDER BY, GROUP BY.
Te kolumny są idealnymi kandydatami do tworzenia indeksów. Powinieneś bardzo uważnie analizować swoje zapytania, aby uniknąć tworzenia niepotrzebnych indeksów. - Twoje indeksy powinny być jak najmniejsze.
Ze względu na fakt, że każdy indeks zajmuje miejsce na dysku, spróbuj zminimalizować rozmiar klucza indeksu, aby uniknąć nadmiernego rozmiaru bazy danych. Zmniejszy to liczbę odczytów wymaganych do odczytu indeksu i zwiększy ogólną wydajność indeksu. - Usuń indeksy, które nie są używane.
Ponieważ każdy indeks zajmuje miejsce na dysku i spowalnia wstawianie, usuwanie i aktualizowanie wierszy w tabelach, należy usunąć nieużywane indeksy. Za pomocą Kreatora indeksów można określić indeksy, które nie są używane przez zapytania. - Spróbuj utworzyć indeksy w kolumnach, które mają wartości całkowite, a nie wartości znaków.
Ponieważ wartości całkowite są zwykle mniejsze niż wartości znaków (rozmiar typu INT wynosi 4 bajty, rozmiar rozmiaru BIGINT wynosi 8 bajtów), można zmniejszyć liczbę stron indeksu używanych do przechowywania kluczy indeksu. Zmniejszy to liczbę odczytów wymaganych do odczytania indeksu i zwiększenia ogólnej wydajności. - Ogranicz liczbę indeksów, jeśli aplikacja często aktualizuje dane.
Ponieważ Każdy indeks zajmuje miejsce na dysku i spowalnia dodawanie, usuwanie i aktualizowanie wierszy, dlatego należy tworzyć nowe indeksy dopiero po przeanalizowaniu wykorzystania danych, typu i częstotliwości wykonywanych zapytań oraz sposobu, w jaki zapytania będą korzystać z nowych indeksów. W większości przypadków korzyści z tworzenia nowych indeksów przeważają nad wadami, takimi jak dodatkowa przestrzeń i powolne modyfikacje danych. Unikaj jednak stosowania nadmiarowych indeksów, twórz je tylko wtedy, gdy jest to konieczne. W przypadku tabel używanych wyłącznie do odczytu można zwiększyć liczbę indeksów. - Upewnij się, że indeks, który próbujesz utworzyć, jeszcze nie istnieje.
Pamiętaj, że podczas tworzenia klucza podstawowego lub unikalnego klucza SQL Server automatycznie tworzy indeks w kolumnach, które uczestniczą w kluczu. Jeśli określisz inną nazwę indeksu, możesz wielokrotnie tworzyć indeksy dla tej samej kolumny. - Utwórz indeks klastrowany zamiast indeksu nieklastrowanego, aby zwiększyć wydajność zapytań zwracających zakres wartości, a także zapytań zawierających wyrażenia GROUP BY lub ORDER BY i zwracających posortowane wyniki.
Ponieważ Każda tabela może zawierać tylko jeden indeks klastrowany, dlatego należy starannie wybrać kolumny indeksu. Spróbuj przeanalizować wszystkie zapytania, wybierz najczęściej używane i uwzględnij w indeksie klastra tylko te kolumny, które zapewniają największą przewagę wydajności. - Twórz indeksy nieklastrowane, aby zwiększyć wydajność zapytań, które zwracają mniej wierszy, a indeksy mają dobrą selektywność.
Tabele mogą zawierać do 249 indeksów nieklastrowanych, w przeciwieństwie do indeksu klastra, który może być tylko jeden dla każdej tabeli. Należy jednak bardzo ostrożnie tworzyć indeksy nieklastrowane, jak w przypadku indeksów klastrów, ponieważ każdy indeks zajmuje miejsce na dysku i wpływa na szybkość modyfikacji danych. - Utwórz indeks klastrowany dla kolumn, które nie są często aktualizowane.
Ponieważ liście w nieklastrowanym drzewie indeksów zawierają klucz indeksu klastra, więc jeśli tabela ma indeks klastrowany, to za każdym razem, gdy kolumny w niej są aktualizowane, wszystkie indeksy nieklastrowane również będą aktualizowane. - Utwórz indeks klastrowy w oparciu o jedną kolumnę lub jak najmniejszą liczbę.
Ponieważ indeksy nieklastrowane zawierają klucz indeksu klastra w liściach drzewa, a indeksy nieklastrowane używają wyszukiwania klastra, a następnie utworzenie indeksu klastra w pojedynczej kolumnie zmniejszy rozmiar nie tylko indeksu klastra, ale także wszystkich klastrów. - Unikaj tworzenia indeksów klastrowych w kolumnach automatycznego przyrostu.
Na przykład, jeśli tabela ma automatyczny przyrostowy klucz podstawowy, a indeks klastrowy jest tworzony w tej kolumnie, to za każdym razem, gdy zostanie wstawiony do tej tabeli, wiersze zostaną dodane na końcu tabeli. A kiedy wstawionych jest wiele linii, może się zdarzyć gorący punkt „ „Hot spot” występuje, gdy wiele żądań próbuje odczytać i zapisać dane w jednym obszarze jednocześnie. „Hot spot” występuje, ponieważ urządzenia wejściowe są wąskim gardłem.
Zauważ, że domyślnie SQL Server tworzy indeks klastrowany na kolumnach, które są zawarte w kluczu podstawowym. W takim przypadku należy wyraźnie wskazać, że chcesz utworzyć indeks nieklastrowany dla tych kolumn. - Utwórz indeks klastrowany dla każdej tabeli.
Jeśli utworzysz tabelę bez indeksu klastrowego, dane zostaną zapisane w niewłaściwym miejscu. Taka struktura nazywana jest grupą. Za każdym razem, gdy dane są wstawiane do takiej tabeli, wiersze zostaną dodane na końcu tabeli. W takim przypadku ponownie może wystąpić „gorący punkt”. Aby poprawić wydajność zapytań równoległych, należy utworzyć indeksy klastrowane dla każdej tabeli. - Nie twórz indeksów w kolumnach o niskiej selektywności.
Na przykład nie twórz indeksu na kolumnach, które zawierają wiele zduplikowanych danych, na przykład dla kolumny „Gender”, która może zawierać tylko wartości „Mężczyzna” i „Kobieta”, ponieważ w tym przypadku wady dodatkowego miejsca na dysku twardym i powolna modyfikacja przeważają nad zaletami szybkości wyszukiwania takiego indeksu. - Jeśli utworzysz indeks złożony, spróbuj uporządkować klucze indeksu w kolejności malejącej selektywności, tj. klucz o najwyższej selektywności powinien być najbardziej lewy.
Kolejność kolumn w indeksach złożonych jest bardzo ważna, może zwiększyć szansę wykorzystania indeksu w zapytaniach. - Jeśli utworzysz indeks złożony, umieść klucze indeksu na początku, które są często używane w wyrażeniach WHERE.
Kolejność kolumn w indeksach złożonych jest bardzo ważna. Indeks zostanie użyty w zapytaniu tylko wtedy, gdy kolumny znajdujące się w WHERE są lewymi klawiszami indeksu. Na przykład, jeśli utworzysz złożony indeks „Nazwa, Wiek”, to podczas wyszukiwania według nazwy indeks zostanie użyty, a podczas wyszukiwania według wieku nie będzie używany. - Utwórz indeks dla kolumn, które są często używane w połączeniach.
Może to znacznie zwiększyć wydajność zapytań za pomocą JOIN w tych kolumnach. - Rozważ utworzenie podstawowej liczby całkowitej klucz zastępczy.
Każda tabela musi zawierać klucz podstawowy (unikalny identyfikator wiersza w tabeli). Zastępczy klucz podstawowy to pole, które ma unikalną wartość, ale nie odnosi się do samego rekordu, a użytkownicy nigdy nie powinni widzieć ani zmieniać takiego klucza podstawowego. Niektórzy programiści używają zastępczych kluczy podstawowych, inni wykorzystują dane samych pól jako klucz podstawowy. Jeśli klucz podstawowy zawiera wiele pól i ma duży rozmiar, rozważ utworzenie klucza zastępczego. Może to zwiększyć wydajność zapytań. - Rozważ utworzenie indeksów we wszystkich kolumnach, które są najczęściej używane w klauzuli WHERE zawierającej operatory OR.
Jeśli kwerenda zawierająca operator OR jest zawarta w klauzuli WHERE i jeśli żaden z nich nie jest objęty indeksem, zapytanie będzie musiało całkowicie przeskanować tabelę lub indeks klastrowany. Dlatego w takich przypadkach należy utworzyć indeksy na wszystkich takich kolumnach, co znacznie zwiększy wydajność zapytań. - Jeśli aplikacja bardzo często wykonuje kwerendy w tabeli, należy rozważyć utworzenie indeksu obejmującego wszystkie kolumny wymagane w zapytaniu.
Indeks obejmujący jest indeksem zawierającym wszystkie kolumny z zapytania. Tworzenie takich indeksów może zwiększyć wydajność, ponieważ wszystkie dane dla zaprov będą już zawarte w indeksie i nie muszą pobierać z tabeli. Indeksy pokrycia mogą znacznie zwiększyć wydajność zapytań, ponieważ oszczędzają na dużej liczbie operacji we / wy. - Okresowo należy używać instrukcji DBCC DBREINDEX, aby odbudować wszystkie indeksy we wszystkich tabelach, aby zmniejszyć fragmentację.
Pofragmentowane dane mogą spowodować, że SQL Server wygeneruje dodatkowe niepotrzebne odczyty, więc zapytania na mocno rozdrobnionych tabelach będą bardzo złe. Warto więc okresowo odbudowywać indeksy, aby zmniejszyć fragmentację. Spróbuj uruchomić DBCC DBREINDEX w momentach, gdy baza nie jest mocno obciążona. - Użyj instrukcji DBCC INDEXDERRAG do defragmentacji indeksów tabeli i widoku.
Ta instrukcja pojawiła się w SQL Server 2000. W przeciwieństwie do DBCC DBREINDEX, ta instrukcja nie blokuje danych na długo, a zatem nie będzie blokować wykonania zapytania. Spróbuj, jeśli to możliwe, użyć DBCC INDEXDERRAG zamiast DBCC DBREINDEX. - Spróbuj użyć opcji SORT_IN_TEMPDB podczas tworzenia indeksu, gdy tempdb nie znajduje się na tym samym dysku, co twoja baza danych.
Parametr SORT_IN_TEMPDB został wprowadzony w SQL Server 2000. Podczas tworzenia indeksu za pomocą tego parametru SQL Server używa tempdb do sortowania danych podczas tworzenia indeksu zamiast bieżącej bazy danych. Ta opcja umożliwia szybkie utworzenie indeksu, ale zwiększa ilość miejsca na dysku używanego podczas tworzenia indeksu. - Użyj Kreatora tworzenia śledzenia w profilu serwera SQL przy użyciu opcji „Skanowanie tożsamości dużych tabel”, aby zdefiniować tabele w bazie danych, które wymagają indeksów.
Wyniki pokażą, które tabele nie używają indeksów.
55.755786 37.617633
