Dette er den fjerde artikel i en serie af læring OPRET UDSIGT SQL-sætning. Indtil videre har vi gjort en stor del af at skabe og ændre synspunkter ved hjælp af T-s .l. I denne sidste del vil jeg se et stort kig på, hvordan man arbejder med indekserede visninger.
som altid, for at følge med serien, anbefales det stærkt at læse de foregående dele først og derefter denne., Dette skyldes primært, at vi oprettede vores egen prøvedatabase og objekter i den fra bunden, som vi også bruger i denne del, men også fordi det vil være meget lettere at se det store billede.
Her er de tre foregående stykker af SKABE UDSIGT SQL-serien:
- Oprettelse af visninger i SQL Server
- at Ændre synspunkter i SQL Server
- Indsættelse af data via synspunkter i SQL Server
Så, gå over og læs dem, inden du fortsætter med denne ene.
introduktion
den første ting, vi vil gøre, er at oprette en indekseret visning., Vi vil selvfølgelig bruge create vie.s .l-sætningen til dette, som vi gjorde mange gange gennem serien. Men den generelle id., som titlen siger, er at se, hvordan man arbejder med indekserede visninger, se, hvad kravene er for at tilføje et indeks til en visning, og hvordan man gør det programmatisk. For at forklare fordelene ved indekserede visninger vil vi desuden se på henrettelser planer i s .l Server. De er et godt værktøj til DBA ‘ er og udviklere, når det kommer til at finde og rette en flaskehals i den langsomme løbende forespørgsel.,
Uden videre, lad os oprette en visning, der bruger OPRET UDSIGT SQL-sætning fra nedenfor og se, hvad det betyder:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
BRUG SQLShackDB;
GO
OPRET UDSIGT dbo.,vEmployeeSalesOrders
WITHITH SCHEMABINDING
AS
vælg medarbejdere.EmployeeID,
produkter.ProductID,
SUM(pris * mængde) SOM SaleTotal,
SaleDate
FRA dbo.Medarbejdere
Bliv medlem af DBO.Salg på medarbejdere.EmployeeID = Salg.EmployeeID
Bliv medlem af DBO.Produkter på salg.ProductID = Produkter . Produktion
gruppe efter ansatte.EmployeeID,
produkter.ProductID,
salg.,SaleDate;
GO
|
Bemærk, at denne opfattelse har en MED SCHEMABINDING løsning på det. Grunden til, at det har denne indstilling tændt er fordi når du opretter indekser på Visninger, de faktisk fysisk få gemt i databasen. Med andre ord, alt, hvad denne opfattelse er afhængig af, hvad angår tabellerne, kan strukturen ikke ændre sig fra det, vi refererer til.
derfor skal det være bundet til de underliggende tabeller, så vi ikke kan ændre dem på en måde, der vil påvirke visningsdefinitionen., Hvis vi forsøger at ændre dem på nogen måde, kaster s .l Server en fejl, der siger, at denne visning afhænger af noget. Så se på det som et hårdt krav for at oprette et indeks på en visning.
Når kommandoen er udført, skal du se vEmployeeSalesOrders se under Synspunkter mappe i Object Explorer, som vist nedenfor:
definitionen af Den opfattelse, at det er lidt mere kompleks forespørgsel. Vi fik et aggregat i SELECT-sætningen efterfulgt af gruppen efter klausul., Husk, når vi har et aggregat i forespørgslen, tilføjer det tallene sammen, så vi skal have gruppen efter klausul.grundlæggende, når der er en gruppe efter i en forespørgsel, skal vi gruppere efter alt, hvad der er i valglisten undtagen aggregatet.,
Nu, jeg har allerede oprettet den opfattelse, men husk at det altid er en god idé at afprøve definitionen af den opfattelse, ved at køre kun VÆLGE en del af det at SKABE UDSIGT SQL-sætning for at se, hvad den returnerer:
Bevæger sig på, her er hvordan du kan kontrollere, om Skemaet bundet af indstillingen er aktiveret eller deaktiveret., Gå over til Object e .plorer, Udvid visninger, Højreklik på visningen og vælg Egenskaber:
blandt alle andre oplysninger i vinduet Vis egenskaber ser du, om indstillingen Schema bound er indstillet til True eller False under den generelle side.
oprettelse af indekserede visninger
lad os gå videre og oprette et indeks på vores visning., 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(Medarbejderid, ProductID, SaleDate);
GO
|
Hvis vi ramte Udføre knappen i SSMS, SQL Server vil smide en fejl at sige, at indekset ikke kan være skabt:
Her er den fulde fejlmeddelelse, der ikke kan ses i billedet ovenfor:
kan Ikke oprette indeks på udsigt ‘SQLShackDB.dbo.vEmployeeSalesOrders ‘ fordi dens select liste ikke indeholder en korrekt brug af COUNT_BIG. Overvej at tilføje COUNT_BIG ( * ) for at vælge liste.,
Vi har brug for COUNT_BIG i dette tilfælde, da vi bruger GROUP BY efter vores mening.
generelt, hvis vi bruger aggregater som COUNT, SUM, AVG osv. i indeksets select list skal vi også inkludere COUNT_BIG for at oprette et indeks på det.
det er præcis, hvad vi skal gøre.,hjælp CREATE VIEW SQL-sætning og tilføje COUNT_BIG til at vælge listen ved at bruge scriptet nedenfor:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
BRUG SQLShackDB;
GO
ÆNDRE UDSIGT dbo.,vEmployeeSalesOrders
WITHITH SCHEMABINDING
AS
vælg medarbejdere.EmployeeID,
produkter.ProductID,
SUM(pris * mængde) SOM SaleTotal,
SaleDate,
COUNT_BIG(*) SOM RecordCount
FRA dbo.Medarbejdere
Bliv medlem af DBO.Salg på medarbejdere.EmployeeID = Salg.EmployeeID
Bliv medlem af DBO.Produkter på salg.ProductID = Produkter . Produktion
gruppe efter ansatte.EmployeeID,
produkter.ProductID,
salg.,SaleDate;
GO
|
Hvis du spekulerer på, hvorfor dette sker i denne sag, svaret er, fordi SQL Server har brug for en måde at spore antallet af posten, at vi er ved at blive til indekset, og dette er også én af en hel flok af begrænsninger i forbindelse med oprettelse af indekserede udsigt.
indtil videre ser alt godt ud. Vi har med succes ændret definitionen af vores opfattelse:
nu kan vi komme tilbage til at oprette et indeks på visningen ved at udføre det tidligere anvendte script en gang til., Denne gang afsluttes operationen glat. Hvis vi går til Object Explorer og udvide Indeks mappe, efter vores mening, vi vil se den nyligt oprettede indeks:
Fra her, hvis vi højre-klik på indekset, og vælg Egenskaber i henhold til de Generelle side, kan du se navn, indeks navn, type, centrale søjler osv.,:
Hvis vi skifter over til Opbevaring, du vil se, at det har en Filegroup, fordi det er fysisk lagret i en database:
Desuden hvis vi skifte over til en Fragmentering, det vil sige, at for Total fragmentering er nul procent, fordi vi har kun et par poster i vores tabeller:
Leder du efter en detaljeret, men sjovt og let at læse primer på vedligeholdelse og overvågning af SQL indekser? Tjek s .l inde.vedligeholdelse.,
sletning af indekser
før vi går videre, lad os se, hvordan vi kan slette et indeks. Den nemmeste måde er at højreklikke på indekset i Object e .plorer og bruge indstillingen Slet. Men hvis du har brug for at droppe flere indekser på .n gang, er DROP inde. – erklæringen praktisk. Det er hvad vi skal gøre, for det er trods alt en T-s .l-serie om at lære s .l-sætningen CREATE vie..,
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
|
Når du har brug for til at slippe flere indekser, du skal bare angive alle navne adskilt af et komma.
generering af tilfældige data
nu, hvor vi slap af indekset, lad os generere nogle tilfældige data i vores tabel, så vi kan se på eksekveringsplanen og se, hvordan s .l Server henter data under hætten. Analyse af eksekveringsplanen viser forskellen i, hvordan ydelsen påvirkes ved at køre forespørgslen med og uden et indeks på visningen.,
Bruge scriptet nedenfor til at indsætte 50000 tilfældige registreringer i Salgs-tabellen:
jeg har ikke tænkt mig at gå dig gennem script i detaljer, men det er dybest set en løkke, der vil udføre 50000 gange og indsætte tilfældige data i tabellen Salg., for at kontrollere, om de optegnelser, der var indsat korrekt, kan du udføre følgende SELECT-sætning, der vil tælle alle poster fra tabellen Salg:
|
1
2
3
4
|
BRUG SQLShackDB;
GO
SELECT COUNT(*) FRA Salg
|
Det tal, der returneres, viser, at der er 50006 rækker i tabellen Salg., Det er antallet af poster, som vi lige har genereret + 6, at vi i første omgang havde:
Analysere udførelse planer
Nu, at vi har nogle data i vores tabel, skal vi virkelig kan demonstrere brugen af et indeks på den opfattelse. Lad os forespørge vemployeesalesorders se og se, hvordan s .l Server henter data., 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;
|
Dette script tilbage 23814 rækker, men hvad der er mere vigtigt, er det genererede sql forespørgslen. Husk, at vi tidligere faldt indekset på vores opfattelse. Så lige nu er der ingen indeks på vores opfattelse. Derfor, SQL-Server, vil gøre et par table-scanninger som vist nedenfor:
Dette er stort set det værste i databasen verden, specielt på borde med en stor mængde data., Det er okay at have bordscanninger med en lille mængde data, f.eks. tilfældet med vores medarbejdere og Produktborde, men det er dårligt for Salgstabellen, fordi den har 50k+ poster.
den nemmeste måde at slippe af med bordscanningerne er at oprette et indeks på det, fordi det dramatisk fremskynder tingene. Så hvad vi vil gøre for at løse dette problem er at udføre scriptet igen for at oprette det unikke grupperede indeks på vemployeesalesorders-visningen.
nu, hvis vi bare kører SELECT-sætningen igen, er der ingen forskelle, selvom vi lige har oprettet indekset. Hvorfor?, Fordi jeg bruger SQL Server Express edition for formålet med denne serie, og kun i Virksomheden og Udvikler versioner af SQL Server vil den Query Optimizer faktisk tage indekset i betragtning.
ingen bekymringer, fordi vi faktisk kan tvinge s .l Server til at bruge et indeks, når vi genererer eksekveringsplaner. Dette gøres ved at bruge noe .pand-indstillingen., NOEXPAND gælder kun til indekseret synspunkter:
|
1
|
VÆLG * FRA dbo.vEmployeeSalesOrders MED (NOEXPAND)
|
Bare gerne, at vi er tvunget SQL Server til at bruge grupperet indeks, som dybest set betyder, brug ikke de underliggende tabeller, når der hentes data., Som det kan ses nedenfor, har vi gjort nogle fremskridt ved at fjerne en række af operationer:
I virkeligheden, kan vi executeboth SELECT-sætninger, der samtidigt og sammenligne resultaterne ved at se på udførelse planer:
Ville du se på det? Hvis vi sammenligner forespørgselsomkostningerne for den første SELECT-erklæring (w/o-indeks 95%) med den anden SELECT-erklæring (w/ inde.5%), vil jeg sige, at det er en enorm præstationsgevinst ved hjælp af et enkelt indeks.,
konklusion
indekser er store, fordi de fremskynde ydeevnen og med et indeks på en visning det bør virkelig fremskynde ydeevnen, fordi indekset er gemt i databasen. Indeksering af både visninger og tabeller er en af de mest effektive måder at forbedre ydelsen på forespørgsler og applikationer, der bruger dem.
Jeg vil gerne pakke tingene op og afslutte denne serie med at lære CREATE vie.s .l-sætningen med denne artikel. Vi har stort set dækket alt om at skabe og ændre synspunkter med T-s .l., Vi startede med CREATE vie.s .l-sætningen, skabte et par visninger, ændrede dem, slettet og meget mere.
Jeg håber, at denne serie med at lære s .l-sætningen CREATE vie.har været informativ for dig, og jeg takker dig for at have læst den.,
OPRET UDSIGT SQL: Indsættelse af data via synspunkter i SQL Server
OPRET UDSIGT SQL: At arbejde med indekseret synspunkter i SQL Server
- Forfatter
- Seneste Indlæg
Han har skrevet udførligt om både SQL-Shack, og de ApexSQL løsningscenter, om emner, der spænder fra klient teknologier som 4K-opløsning og theming, fejlhåndtering til indeks strategier, og overvågning af ydeevne.
Bojan arbejder på ApexSQL i Nis, Serbien som en integreret del af det team, der fokuserer på at designe, udvikle og afprøve næste generation af database-værktøjer, herunder MySQL og SQL Server, og både stand-alone værktøjer og integrationer i Visual Studio, SSMS, og VSCode.,
Se mere om Bojan på LinkedIn
Vis alle indlæg af Bojan Petrovic
- Visual Studio Kode for MySQL og MariaDB udvikling – August 13, 2020
- SQL UPDATE syntaks forklaret – juli 10, 2020
- OPRET UDSIGT SQL: Arbejde med indekseret synspunkter i SQL Server – 24 Marts 2020