jest to czwarty artykuł z serii nauki instrukcji CREATE VIEW SQL. Do tej pory zrobiliśmy wiele tworzenia i zmieniania widoków za pomocą T-SQL. W tej ostatniej części chcę przyjrzeć się, jak pracować z zindeksowanymi widokami.
jak zawsze, aby śledzić serię, zaleca się najpierw przeczytać poprzednie części, a potem tę., Dzieje się tak przede wszystkim dlatego, że stworzyliśmy własną przykładową bazę danych i obiekty w niej od podstaw, które będziemy używać również w tej części, ale także dlatego, że będzie znacznie łatwiej zobaczyć duży obraz.
oto trzy poprzednie części serii CREATE VIEW SQL:
- tworzenie widoków w SQL Server
- modyfikowanie widoków w SQL Server
- wstawianie danych przez widoki w SQL Server
więc przejrzyj je i przeczytaj je przed kontynuacją tego.
wprowadzenie
pierwszą rzeczą, którą zrobimy, jest utworzenie widoku zindeksowanego., Oczywiście użyjemy do tego instrukcji CREATE VIEW SQL, tak jak robiliśmy to wiele razy w serii. Ale ogólna idea, jak mówi tytuł, polega na tym, aby zobaczyć, jak pracować z zindeksowanymi widokami, zobaczyć, jakie są wymagania dotyczące dodawania indeksu do widoku i jak to zrobić programowo. Ponadto, aby wyjaśnić zalety zindeksowanych widoków, przyjrzymy się planom egzekucji w SQL Server. Są one doskonałym narzędziem dla baz danych i programistów, jeśli chodzi o znajdowanie i naprawianie wąskiego gardła w wolno działającym zapytaniu.,
bez dalszych ceregieli, stwórzmy przedstawienie za pomocą tworzyć zobaczyć instrukcję SQL na dole i zobacz, co robi:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
stosować SQLShackDB;
przejdź
utwórz widok dbo.,vEmployeeSalesOrders
WITH SCHEMABINDING
AS
SELECT Employees.EmployeeID,
Products.ProductID,
SUM(price * quantity) AS Salet,
Saled
FROM dbo.Pracownicy
dołączają do dbo.Sprzedaż pracowników.EmployeeID = Sales.EmployeeID
dołącz do dbo.Produkty w sprzedaży.ProductID = Produkty.ProductID
Grupa według pracowników.EmployeeID,
Products.ProductID,
sprzedaż.,SaleDate;
GO
|
zauważ, że ten widok ma opcję z SCHEMABINDING. Powodem, dla którego ta opcja jest włączona, jest to, że podczas tworzenia indeksów w widokach, są one fizycznie przechowywane w bazie danych. Innymi słowy, wszystko, na czym opiera się ten Widok, jeśli chodzi o tabele, struktura nie może się zmienić od tego, o czym mówimy.
dlatego musi ona być powiązana z bazowymi tabelami, abyśmy nie mogli ich modyfikować w sposób, który wpłynąłby na definicję widoku., Jeśli spróbujemy je zmienić w jakikolwiek sposób, SQL Server wyrzuci błąd mówiąc, że ten widok zależy od czegoś. Przyjrzyj się więc temu jako trudnemu wymogowi tworzenia indeksu w widoku.
Po pomyślnym wykonaniu polecenia powinieneś zobaczyć widok vEmployeeSalesOrders w folderze Views w Object Explorer, jak pokazano poniżej:
definicja widoku jest nieco bardziej skomplikowana. Mamy agregat w instrukcji SELECT, a następnie klauzulę GROUP BY., Pamiętaj, że gdy mamy agregat w zapytaniu, dodaje on liczby razem, więc musimy mieć klauzulę GROUP BY.
zasadniczo, gdy w zapytaniu znajduje się grupa według, musimy pogrupować według wszystkiego, co znajduje się na liście select, z wyjątkiem agregatu.,
teraz, już utworzyłem widok, ale pamiętaj, że zawsze dobrym pomysłem jest przetestowanie definicji widoku, uruchamiając tylko wybraną część instrukcji CREATE VIEW SQL, aby zobaczyć, co zwraca:
przechodząc dalej, oto jak możesz sprawdzić, czy opcja związana ze schematem jest włączona lub wyłączona., Przejdź do Eksploratora obiektów, rozwiń widoki, kliknij prawym przyciskiem myszy na widoku i wybierz Właściwości:
wśród wszystkich innych informacji w oknie Właściwości widoku zobaczysz, czy opcja związana ze schematem jest ustawiona na True lub False na stronie Ogólne.
Tworzenie zindeksowanych widoków
przejdźmy dalej i stwórz indeks na naszym widoku., Consider the script from below for creating a clustered index on the vEmployeeSalesOrders view:
|
1
2
3
4
5
6
|
USE SQLShackDB;
GO
CREATE UNIQUE CLUSTERED INDEX CAK_vEmployeesSalesOrders
ON dbo.,vEmployeeSalesOrders(EmployeeID, ProductID, SaleDate);
GO
|
Jeśli naciśniemy przycisk Execute w SSMS, SQL Server wyświetli błąd, że indeks nie może zostać utworzony:
oto pełny komunikat o błędzie, którego nie można zobaczyć w powyższym ujęciu:
nie można utworzyć indeksu w widoku 'sqlshackdb.dbo.vEmployeeSalesOrders', ponieważ jego lista select nie zawiera właściwego użycia COUNT_BIG. Rozważ dodanie COUNT_BIG (*) do listy wyboru.,
potrzebujemy COUNT_BIG w tym przypadku, biorąc pod uwagę fakt, że naszym zdaniem używamy GROUP BY.
ogólnie rzecz biorąc, jeśli używamy agregatów takich jak COUNT, SUM, AVG itp. w liście select indeksu musimy również uwzględnić COUNT_BIG, aby utworzyć na nim indeks.
właśnie to zrobimy.,z pomocą tworzenia zobaczyć instrukcję SQL i dodaj funkcja count_big w liście wyboru za pomocą skryptu poniżej:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
stosować SQLShackDB;
przejdź
zmień widok dbo.,vEmployeeSalesOrders
WITH SCHEMABINDING
AS
SELECT Employees.EmployeeID,
Products.ProductID,
SUM(price * quantity) AS Salet,
SaleDate,
COUNT_BIG(*) as RecordCount
z dbo.Pracownicy
dołączają do dbo.Sprzedaż pracowników.EmployeeID = Sales.EmployeeID
dołącz do dbo.Produkty w sprzedaży.ProductID = Produkty.ProductID
Grupa według pracowników.EmployeeID,
Products.ProductID,
sprzedaż.,SaleDate;
GO
|
Jeśli zastanawiasz się, dlaczego tak się dzieje w tym przypadku, odpowiedź brzmi, ponieważ SQL Server potrzebuje sposobu śledzenia numeru rekordu, który zwracamy dla indeksu i jest to również jedno z wielu ograniczeń związanych z tworzeniem zindeksowanych widoków.
na razie wszystko wygląda dobrze. Udało nam się zmienić definicję naszego widoku:
teraz możemy wrócić do tworzenia indeksu w widoku, wykonując wcześniej użyty skrypt jeszcze raz., Tym razem operacja zakończy się płynnie. Jeśli przejdziemy do Object Explorer i rozwiń folder Indexes naszego widoku, zobaczymy nowo utworzony indeks:
stąd, jeśli klikniemy prawym przyciskiem myszy na indeksie i wybierzemy właściwości, pod stroną ogólną zobaczysz nazwę widoku, nazwę indeksu, typ, kolumny kluczy itp.,:
Jeśli przełączymy się na Storage, zobaczysz, że ma on grupę plików, ponieważ jest fizycznie przechowywany w bazie danych:
jeśli przełączymy się na fragmentację, powinno się powiedzieć, że całkowita fragmentacja wynosi zero procent, ponieważ mamy tylko kilka rekordów w naszych tabelach:
szukasz szczegółowego, ale przyjemnego i łatwego do odczytania podkładu na temat utrzymywania i monitorowania indeksów SQL? Sprawdź konserwację indeksu SQL.,
usuwanie indeksów
zanim przejdziemy dalej, zobaczmy, jak możemy usunąć indeks. Najprostszym sposobem jest kliknięcie prawym przyciskiem myszy na indeksie w Object Explorer i użycie opcji Usuń. Ale w przypadku, gdy musisz upuścić wiele indeksów na raz, przydatne jest polecenie Drop INDEX. To właśnie zamierzamy zrobić, ponieważ w końcu jest to seria T-SQL o nauce instrukcji CREATE VIEW SQL.,
Use the script from below to drop the CAK_vEmployeesSalesOrders index:
|
1
2
3
4
5
|
USE SQLShackDB;
GO
DROP INDEX CAK_vEmployeesSalesOrders ON dbo.,vEmployeeSalesOrders;
GO
|
gdy chcesz upuścić wiele indeksów, podaj wszystkie nazwy oddzielone przecinkiem.
generowanie losowych danych
teraz, gdy pozbyliśmy się indeksu, wygenerujmy losowe DANE w naszej tabeli, abyśmy mogli przyjrzeć się planowi wykonania i zobaczyć, jak SQL Server pobiera dane pod maską. Analiza planu wykonania pokaże różnicę w tym, jak na wydajność wpływa uruchomienie zapytania z indeksem i bez niego w widoku.,
Użyj skryptu od dołu, aby wstawić 50000 losowych rekordów do tabeli Sprzedaży:
Nie będę cię szczegółowo omawiał skryptu, ale jest to w zasadzie pętla, która wykona 50000 razy i wstawi losowe DANE do tabeli Sprzedaży., aby sprawdzić, czy rekordy zostały wprowadzone pomyślnie, wykonaj następującą instrukcję SELECT, która zliczy wszystkie rekordy z tabeli Sprzedaży:
| iv id=”1aaa82ff25″
1
2
3
4
|
użyj sqlshackdb;
idź
Wybierz liczbę(*) ze sprzedaży
|
zwrócona liczba pokazuje, że w tabeli sprzedaży jest 50006 wierszy., Jest to liczba rekordów, które właśnie wygenerowaliśmy + 6, które początkowo mieliśmy:
analiza planów wykonania
teraz, gdy mamy pewne dane w naszej tabeli, możemy naprawdę zademonstrować użycie indeksu w widoku. Zapytajmy widok vemployeesalesorders i zobaczmy, jak SQL Server pobiera dane., Before executing the SELECT statement from below, make sure to include the Actual Execution Plan as shown below:
|
1
2
3
4
|
USE SQLShackDB;
GO
SELECT * FROM dbo.,vEmployeeSalesOrders;
|
Ten skrypt zwrócił 23814 wierszy, ale co ważniejsze, wygenerował plan wykonania zapytania. Pamiętaj, że wcześniej upuściliśmy indeks na nasz widok. Więc, w tej chwili nie ma żadnego indeksu w naszym widoku. Dlatego SQL Server wykona kilka skanów tabeli, jak pokazano poniżej:
jest to najgorsza rzecz w świecie bazy danych, szczególnie w przypadku tabel z dużą ilością danych., Dobrze jest mieć skany tabeli z niewielką ilością danych, np. przypadek z naszymi pracownikami i tabelami produktów, ale jest to złe dla tabeli sprzedaży, ponieważ ma 50k + rekordów.
najprostszym sposobem pozbycia się skanowania tabeli jest utworzenie na nim indeksu, ponieważ znacznie przyspiesza to działanie. Tak więc, co zrobimy, aby rozwiązać ten problem, to ponownie uruchomić skrypt do tworzenia unikalnego klastrowego indeksu w widoku vEmployeeSalesOrders.
teraz, jeśli po prostu ponownie uruchomimy instrukcję SELECT, nie będzie żadnych różnic, mimo że właśnie stworzyliśmy indeks. Dlaczego?, Ponieważ używam SQL Server Express edition na potrzeby tej serii i tylko w wersjach Enterprise i Developer SQL Server optymalizator zapytań rzeczywiście weźmie indeks pod uwagę.
nie martw się, ponieważ możemy zmusić SQL Server do użycia indeksu podczas generowania planów wykonawczych. Odbywa się to za pomocą opcji NOEXPAND., NOEXPAND stosuje się tylko do zindeksowanych widoków:
|
1
|
wybierz * z dbo.vEmployeeSalesOrders WITH (NOEXPAND)
|
w ten sposób zmusiliśmy SQL Server do użycia klastrowego indeksu, co w zasadzie oznacza, że nie używaj bazowych tabel podczas pobierania danych., Jak widać poniżej, poczyniliśmy pewne postępy, eliminując szereg operacji:
w rzeczywistości możemy wykonywać polecenia SELECT jednocześnie i porównywać wyniki, patrząc na plany wykonania:
Czy mógłbyś na to spojrzeć? Jeśli porównamy koszt zapytania pierwszej instrukcji SELECT (w/o index 95%) z drugą instrukcją SELECT (W / index 5%), powiedziałbym, że jest to ogromny wzrost wydajności przy użyciu jednego indeksu.,
podsumowanie
indeksy są świetne, ponieważ przyspieszają wydajność, a z indeksem w widoku powinno to naprawdę przyspieszyć wydajność, ponieważ indeks jest przechowywany w bazie danych. Indeksowanie zarówno widoków, jak i tabel jest jednym z najskuteczniejszych sposobów poprawy wydajności zapytań i aplikacji z nich korzystających.
chciałbym zakończyć tę serię nauki instrukcji CREATE VIEW SQL z tym artykułem. Omówiliśmy prawie wszystko na temat tworzenia i zmieniania widoków za pomocą T-SQL., Zaczęliśmy od instrukcji CREATE VIEW SQL, stworzyliśmy kilka widoków, zmieniliśmy je, usunęliśmy i wiele więcej.
mam nadzieję, że ta seria nauki instrukcji CREATE VIEW SQL była dla Ciebie pouczająca i dziękuję za przeczytanie jej.,
Utwórz widok SQL: wstawianie danych przez widoki w SQL Server
Utwórz widok SQL: praca z indeksowanymi widokami w SQL Server
- Autor
- Ostatnie posty
napisał obszernie zarówno na SQL Shack i ApexSQL Solution Center, na tematy począwszy od technologii klienckich, takich jak rozdzielczość 4K i theming, obsługa błędów do strategii indeksowania i monitorowanie wydajności.
Bojan pracuje w ApexSQL w Nis, Serbia jako integralna część zespołu skupiającego się na projektowaniu, rozwijaniu i testowaniu nowej generacji narzędzi bazodanowych, w tym MySQL i SQL Server, oraz zarówno samodzielnych narzędzi i integracji z Visual Studio, SSMS i VSCode.,
Zobacz więcej o Bojan na LinkedIn
Zobacz wszystkie posty Bojan Petrovic
- Visual Studio Code for MySQL and MariaDB development – 13 sierpnia, 2020
- wyjaśniono składnię aktualizacji SQL – 10 lipca 2020
- Utwórz widok SQL: praca z indeksowanymi widokami w SQL Server – 24 marca 2020