Po testach wykonano zapytanie warunkowe na tabeli zawierającej ponad 4 miliony rekordów, a czas zapytania wynosił nawet 40 sekund. Dlatego bardzo ważne jest, jak poprawić efektywność zapytań SQL o instrukcje. Oto kilka metod optymalizacji zapytań, które są szeroko rozpowszechniane w Internecie:
Przede wszystkim, gdy wolumen danych jest duży, powinieneś unikać skanowania całej tabeli i rozważyć budowanie indeksów na kolumnach w miejscu i kolejności według nich, co znacznie przyspiesza pobieranie danych. Jednak są sytuacje, w których indeksowanie nie działa:
1. Staraj się unikać używania operatorów != lub <> w klauzuli where, w przeciwnym razie silnik porzuci użycie indeksów i wykona pełne skanowanie tabel.
2. Staraj się unikać oceny wartości zerowej dla pól w klauzuli where, w przeciwnym razie silnik porzuci użycie indeksów i wykona pełne skanowanie tabel, takie jak:
Wybierz id z T, gdzie num jest null
Możesz ustawić domyślną wartość 0 na numerze, upewnić się, że w kolumnie num w tabeli nie ma wartości null, a następnie zapytać w ten sposób:
wybierz id z T, gdzie num=0
3. Staraj się unikać używania OR w klauzuli where do łączenia warunków, w przeciwnym razie silnik porzuci używanie indeksu i wykona pełne skanowanie tabeli, na przykład:
Wybierz id z T, gdzie num=10 lub num=20
Możesz zapytać w ten sposób:
Wybierz id z T, gdzie num=10
Union All
Wybierz id z T, gdzie num=20
4. Następujące zapytanie również skutkuje pełnym skanowaniem tabeli:
Wybierz id od T, gdzie nazwa typu '%abc%'
Aby zwiększyć efektywność, rozważ wyszukiwanie pełnotekstowe.
5. Należy stosować ostrożnie i nie w tabeli, w przeciwnym razie prowadzi to do pełnego skanowania tabeli, na przykład:
Wybierz id z T, gdzie num in(1,2,3)
Dla wartości ciągłych, jeśli możesz użyć pomiędzy, nie używaj w:
Wybierz id z T, gdzie num między 1 a 3
6. Jeśli użyjesz parametru w klauzuli where, spowoduje to również przeskanowanie całej tabeli. Ponieważ SQL rozwiązuje tylko lokalne zmienne w czasie działania, ale optymalizator nie może przesunąć wyboru planów dostępu do czasu uruchomienia; Musi być wybrany podczas kompilacji. Jednak jeśli plan dostępu zostanie ustanowiony w czasie kompilacji, wartość zmiennej pozostaje nieznana i dlatego nie może być użyta jako element wejściowy do wyboru indeksu. Następujące oświadczenia zostaną zeskanowane w całości:
Wybierz id z T, gdzie num=@num
Możesz wymusić zapytanie do użycia indeksu:
Wybierz id z t with(index(index(index name)) gdzie num=@num
7. Staraj się unikać wyrażania pól w klauzuli where, co spowoduje, że silnik porzuci korzystanie z indeksu i wykona pełne skanowanie tabeli. Na przykład:
Wybierz id z T, gdzie num/2=100
powinno się zmienić na:
Wybierz id z T, gdzie num=100*2
8. Staraj się unikać wykonywania operacji funkcyjnych na polach w klauzuli where, co spowoduje porzucenie używania indeksów i wykonanie pełnego skanowania tabel. Na przykład:
Wybierz id z T, gdzie substring(name,1,3)='abc' – ID nazwy zaczynające się od abc
Wybierz ID z T, gdzie datediff(day,createdate,'2005-11-30′)=0–'2005-11-30′ generowany id
powinno się zmienić na:
Wybierz ID od T, gdzie nazwa typu 'abc%'
Wybierz ID z T, gdzie stworzono>='2005-11-30′ i createdate<'2005-12-1′
9. Nie wykonuj funkcji, operacji arytmetycznych ani innych operacji wyrażeń po lewej stronie "=" w klauzuli where, w przeciwnym razie system może nie być w stanie poprawnie wykorzystać indeksu.
10. Używając pola indeksu jako warunku, jeśli indeks jest indeksem złożonym, pierwsze pole w indeksie musi być użyte jako warunek zapewniający, że system używa tego indeksu, w przeciwnym razie indeks nie będzie używany, a kolejność pól powinna być jak najbardziej zgodna z kolejnością indeksu.
11. Nie pisz bezsensownych zapytań, takich jak generowanie pustej struktury tabeli:
Wybierz col1,col2 do #t z T, gdzie 1=0
Ten typ kodu nie zwraca żadnego zestawu wyników, ale zużywa zasoby systemowe, więc powinien zostać zmieniony na coś takiego:
Utwórz tabelę #t(...)
12. Często dobrym wyborem jest użycie isty zamiast w:
Wybierz num z A, gdzie num w (wybierz num z b)
Zastąp to następującym stwierdzeniem:
Wybierz num z A gdzie istnieje (wybierz 1 z B, gdzie num = A.num)
Na co zwracać uwagę przy budowaniu indeksu:
1. Nie wszystkie indeksy są ważne dla zapytań, SQL opiera się na danych w tabeli, aby zoptymalizować zapytanie; gdy kolumna indeksu zawiera dużą ilość duplikacji danych, zapytania SQL mogą nie używać indeksu, na przykład tabela ma pola płci, mężczyzna, żeńska prawie połowę każde, wtedy nawet jeśli indeks jest zbudowany na podstawie płci, nie będzie to miało wpływu na efektywność zapytań.
2. Im więcej indeksów, tym lepiej, indeks może z pewnością poprawić efektywność odpowiedniego selekcji, ale także obniża efektywność wstawiania i aktualizacji, ponieważ indeks może być odbudowywany podczas wstawiania lub aktualizacji, więc sposób budowy indeksu wymaga starannego przemyślenia, w zależności od konkretnej sytuacji. Najlepiej nie mieć więcej niż 6 indeksów w tabeli, a jeśli jest ich zbyt wiele, warto rozważyć, czy konieczne jest budowanie indeksów na rzadko używanych kolumnach.
3. Unikaj jak najczęstszej aktualizacji klastrowanych kolumn danych indeksowych, ponieważ kolejność klastrowanych kolumn indeksowanych to fizyczna kolejność zapisów tabel, a gdy wartość kolumny się zmieni, dostosowuje się kolejność wszystkich rekordów tabeli, co pochłonie znaczne zasoby. Jeśli system aplikacyjny musi często aktualizować kolumny indeksu klastrowego, musi rozważyć, czy indeks powinien być zbudowany jako indeks klastrowany.
Inne kwestie warte uwagi:
1. Staraj się używać pól liczbowych i nie projektować pól zawierających jedynie informacje numeryczne jako znaki, co obniży wydajność zapytań i połączeń oraz zwiększy narzut pamięci. Wynika to z faktu, że silnik porównuje każdy znak w ciągu pojedynczo podczas przetwarzania zapytań i połączeń, podczas gdy dla typów numerycznych wystarczy porównać znaki tylko raz.
2. Nie używaj wyboru * z t nigdzie, zastąp "*" na konkretną listę pól i nie zwracaj pól, które nie są używane.
3. Staraj się używać zmiennych tabelowych zamiast tabel tymczasowych. Jeśli zmienna tabelowa zawiera dużą ilość danych, należy zauważyć, że indeks jest bardzo ograniczony (tylko główny indeks klucza).
4. Unikaj częstego tworzenia i usuwania tabel tymczasowych, aby zmniejszyć zużycie zasobów tabel systemowych.
5. Tabele tymczasowe nie są bezużyteczne, a ich odpowiednie użycie może uczynić niektóre procedury bardziej efektywnymi, na przykład gdy trzeba wielokrotnie odwoływać się do dużej tabeli lub zbioru danych w powszechnie używanej tabeli. Jednak w przypadku jednorazowych wydarzeń najlepiej użyć tabeli eksportu.
6. Przy tworzeniu tabeli tymczasowej, jeśli ilość danych wstawionych naraz jest duża, można użyć select into zamiast create table, aby uniknąć powodującego zwiększenie szybkości dużej liczby logów; Jeśli ilość danych nie jest duża, aby zmniejszyć zasoby tabeli systemowej, najpierw utworzyć tabelę, a następnie wstawić.
7. Jeśli używana jest tabela tymczasowa, koniecznie usuń wszystkie tabele tymczasowe na końcu procedury przechowywanej, najpierw obciąć tabelę, a następnie ją usunąć, aby uniknąć długiego zablokowania tabeli systemowej.
8. Staraj się unikać używania kursora, ponieważ jego efektywność jest słaba; jeśli dane obsługiwane przez kursor przekraczają 10 000 linii, powinieneś rozważyć przepisanie.
9. Przed zastosowaniem metody kursorowej lub tymczasowej tabeli, należy najpierw poszukać rozwiązań opartych na zbiorach, które rozwiązują problem, a metoda oparta na zbiorach jest zazwyczaj skuteczniejsza.
10. Podobnie jak w tabelach tymczasowych, kursor nie jest bezużyteczny. Używanie FAST_FORWARD kursorów dla małych zbiorów danych jest często lepsze niż inne metody przetwarzania wiersz po wierszu, zwłaszcza jeśli trzeba odwoływać się do kilku tabel, aby uzyskać potrzebne dane. Procedury zawierające "sumę" w zbiorze wyników są zazwyczaj szybsze niż te wykonywane kursorem. Jeśli czas na rozwój pozwoli, można wypróbować zarówno metody oparte na kursorach, jak i na zbiorach, aby sprawdzić, która działa lepiej.
11. Ustaw USTAW NOCOUNT ON na początku wszystkich procedur i wyzwalaczy, a na końcu ustaw USTAW NOCOUNT OFF. Nie ma potrzeby wysyłania DONE_IN_PROC wiadomości do klienta po wykonaniu każdego polecenia procedury przechowywanej i wyzwalania.
12. Staraj się unikać zwracania dużych danych do klienta; jeśli wolumen danych jest zbyt duży, powinieneś rozważyć, czy odpowiadające zapotrzebowanie jest rozsądne.
13. Starać się unikać dużych operacji transakcyjnych i poprawić zdolność współbieżności systemu.
|
Opublikowano 11.05.2018 13:57:06
|
|
|
Opublikowano 17.05.2018 10:12:27