Acesta este cel de-al patrulea articol din seria de învățare a CREA VIZUALIZAREA SQL. Până în prezent, am făcut o mare parte din crearea și modificarea vizualizărilor folosind T-SQL. În această ultimă parte, vreau să arunc o privire mare asupra modului de lucru cu vizualizări indexate.ca întotdeauna, pentru a urmări împreună cu seria, este foarte recomandat să citiți mai întâi părțile anterioare și apoi aceasta., Acest lucru se datorează în primul rând faptului că am creat propria noastră bază de date și obiecte în ea de la zero pe care le vom folosi și în această parte, dar și pentru că va fi mult mai ușor să vedem imaginea de ansamblu.
Aici sunt ultimele trei bucăți de CREARE VIZUALIZARE SQL seria:
- Crearea de vederi în SQL Server
- Modificarea vizualizări în SQL Server
- Introducerea de date prin vederi în SQL Server
Deci, peste cap și am citit înainte de a continua cu acesta.
Introducere
primul lucru pe care îl vom face este să creăm o vizualizare indexată., Vom folosi, desigur, instrucțiunea CREATE VIEW SQL pentru acest lucru, așa cum am făcut-o de mai multe ori prin serie. Dar ideea generală, așa cum spune titlul, este să vezi cum să lucrezi cu vizualizări indexate, să vezi care sunt cerințele pentru adăugarea unui index la o vizualizare și cum să o faci programatic. Mai mult, pentru a explica avantajele vizualizărilor indexate, vom analiza planurile de execuție în SQL Server. Acestea sunt un instrument excelent pentru DBA și dezvoltatori atunci când vine vorba de găsirea și remedierea unui blocaj în interogarea care rulează lent.,
Fără alte formalități, să creați o vizualizare folosind CREAȚI o VIZUALIZARE SQL de mai jos și vezi ce face:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
UTILIZAREA SQLShackDB;
GO
CREAȚI o VIZUALIZARE dbo.,vEmployeeSalesOrders
cu SCHEMABINDING
ca
selectați angajații.EmployeeID,
produse.Produsid,
sumă(preț * cantitate) ca SaleTotal,
SaleDate
din dbo.Angajații
Alăturați-vă dbo.Vânzări pe angajați.EmployeeID = Vânzări.EmployeeID
JOIN dbo.Produse pe vânzări.ProductID = Produse.ProductID
grup de angajați.EmployeeID,
produse.ProductID,
vânzări.,SaleDate;
GO
|
Observați că acest punct de vedere are o CU SCHEMABINDING opțiune pe ea. Motivul pentru care are această opțiune activată este că atunci când creați indexuri pe vizualizări, acestea sunt stocate fizic în baza de date. Cu alte cuvinte, orice se bazează pe acest punct de vedere, în ceea ce privește tabelele, structura nu se poate schimba de la ceea ce facem referire.prin urmare, trebuie să fie legat de tabelele de bază, astfel încât să nu le putem modifica într-un mod care ar afecta definiția vizualizării., Dacă încercăm să le schimbăm în vreun fel, SQL Server va arunca o eroare spunând că această vizualizare depinde de ceva. Deci, uita-te la ea ca o cerință greu pentru crearea unui index pe o vizualizare.
Odată ce comanda este executată cu succes, ar trebui să vezi vEmployeeSalesOrders vedere în Vedere folder în Explorer Obiect așa cum se arată mai jos:
definiția de vedere este un pic mai complex de interogare. Am obținut un agregat în instrucțiunea SELECT urmată de clauza GROUP BY., Rețineți că atunci când avem un agregat în interogare, acesta adaugă numerele împreună, deci trebuie să avem clauza grup după.
practic, atunci când există un grup de într-o interogare, trebuie să grup de tot ceea ce este în lista select, cu excepția agregatului.,
Acum, am creat deja punctul de vedere, dar amintiți-vă că este întotdeauna o idee bună de a testa definiția vizualizării prin rularea doar SELECTAȚI o parte a CREA VIZUALIZAREA SQL pentru a vedea ce se întoarce:
se Deplasează pe, iată cum puteți verifica dacă Schema legat opțiune este activată sau dezactivată., Mergeți la Object Explorer, extindeți vizualizările, faceți clic dreapta pe Vizualizare și selectați Properties:
printre toate celelalte informații din fereastra View Properties, veți vedea dacă opțiunea schemă bound este setată la True sau False sub pagina generală.
crearea vizualizărilor indexate
Să mergem mai departe și să creăm un index pe vizualizarea noastră., 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
|
Dacă ne-am lovit butonul executare în SSMS, SQL Server va arunca o eroare spunând că indicele nu poate fi creat:
Aici e plin de eroare mesaj care nu poate fi văzut în fotografia de mai sus:
nu Poate crea index la vedere ‘SQLShackDB.dbo.vEmployeeSalesOrders ‘ deoarece Lista select nu include o utilizare corectă a COUNT_BIG. Luați în considerare adăugarea COUNT_BIG (*) pentru a selecta lista.,
avem nevoie de COUNT_BIG în acest caz, având în vedere faptul că folosim GROUP BY în opinia noastră.
în general, dacă folosim agregate precum COUNT, SUM, AVG etc. în lista de selectare a indexului, trebuie să includem și COUNT_BIG pentru a crea un index pe acesta.
exact asta vom face.,folosind CREAȚI o VIZUALIZARE SQL și se adaugă COUNT_BIG să selectați listă folosind scriptul de mai jos:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
UTILIZAREA SQLShackDB;
GO
ALTER VEDERE dbo.,vEmployeeSalesOrders
cu SCHEMABINDING
ca
selectați angajații.EmployeeID,
produse.ProductID
SUM(pret * cantitate) CA SaleTotal,
SaleDate,
COUNT_BIG(*) CA RecordCount
DE la dbo.Angajații
Alăturați-vă dbo.Vânzări pe angajați.EmployeeID = Vânzări.EmployeeID
JOIN dbo.Produse pe vânzări.ProductID = Produse.ProductID
grup de angajați.EmployeeID,
produse.ProductID,
vânzări.,SaleDate;
GO
|
Dacă vă întrebați de ce acest lucru se întâmplă în acest caz, răspunsul este că SQL Server are nevoie de o modalitate de urmărire numărul de înregistrare, care ne sunt de cotitură pentru index și acest lucru este, de asemenea, unul dintre un întreg grămadă de limitări cu crearea indexate de vedere.până acum, totul arată bine. Am schimbat cu succes definiția vizualizării noastre:
acum, putem reveni la crearea unui index pe Vizualizare executând scriptul folosit anterior încă o dată., De data aceasta, operațiunea se va finaliza fără probleme. Dacă mergem la Obiect Explorer și extinde Indici folder din punctul nostru de vedere, vom vedea nou-create index:
De aici, dacă ne-faceți clic dreapta pe index și selectați Proprietăți, în General pagină, puteți vedea numele de vedere, index de nume, tip, coloane cheie, etc.,:
Dacă am trece la Depozit, veți vedea că are un Filegroup pentru că este fizic stocate într-o bază de date:
în Plus, dacă vom trece la Fragmentare, ar trebui să spun că în Total fragmentarea este zero la sută pentru că mai avem doar câteva înregistrări în tabelele noastre:
în Căutarea pentru o analiză detaliată, dar distractiv și ușor de citit grund pe menținerea și monitorizarea SQL indici? Verificați întreținerea indexului SQL.,
ștergerea indexurilor
înainte de a merge mai departe, să vedem cum putem șterge un index. Cel mai simplu mod este să faceți clic dreapta pe indexul din Object Explorer și să utilizați opțiunea ștergere. Dar, în cazul în care trebuie să renunțați la mai mulți indici simultan, instrucțiunea DROP INDEX vine la îndemână. Asta vom face, pentru că, la urma urmei, aceasta este o serie T-SQL despre învățarea instrucțiunii 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
|
atunci Când aveți nevoie pentru a drop mai multe indicii, doar specifica toate nume, separate printr-o virgulă.acum, că am scăpat de index, să generăm câteva date aleatorii în tabelul nostru, astfel încât să putem privi planul de execuție și să vedem cum SQL Server preia date sub capotă. Analiza planului de execuție va arăta diferența în modul în care performanța este afectată de rularea interogării cu și fără un index pe Vizualizare.,
utilizați scriptul de mai jos pentru a insera 50000 de înregistrări aleatorii în tabelul de vânzări:
nu vă voi parcurge scriptul în detalii, dar este practic o buclă care va executa de 50000 de ori și va introduce date aleatorii în tabelul de vânzări., pentru a verifica dacă înregistrările au fost introduse cu succes, executați următoarea instrucțiune SELECT care va conta toate înregistrările din tabelul de Vânzare:
|
1
2
3
4
|
UTILIZAREA SQLShackDB;
GO
SELECT COUNT(*) DIN Vânzări
|
numărul revenit arată că există 50006 rânduri în tabelul de Vânzare., Acesta este numărul de înregistrări pe care tocmai l-am generat + 6, pe care am avut inițial:
Analiza planurile de execuție
Acum, că avem niște date în tabelul nostru, putem demonstra utilizarea unui indice la vedere. Să interogăm vizualizarea vEmployeeSalesOrders și să vedem cum preia SQL Server datele., 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;
|
Acest script revenit 23814 rânduri, dar ce e mai important, acesta a generat planul de execuție a interogării. Amintiți-vă că am scăzut anterior indicele pe punctul nostru de vedere. Deci, chiar acum nu există nici un index pe punctul nostru de vedere. Prin urmare, SQL Server va face câteva masă scanează după cum se arată mai jos:
Acest lucru este destul de mult cel mai rău lucru din baza de date pe lume, mai ales pe mese, cu o cantitate mare de date., Este bine să aveți scanări de masă cu o cantitate mică de date, de exemplu, cazul cu angajații și tabelele noastre de produse, dar este rău pentru tabelul de vânzări, deoarece are înregistrări 50k+.cel mai simplu mod de a scăpa de scanările tabelelor este de a crea un index pe acesta, deoarece accelerează dramatic lucrurile. Deci, ceea ce vom face pentru a rezolva această problemă este re-executa script-ul pentru crearea index unic grupate pe vizualizarea vEmployeeSalesOrders.
acum, dacă tocmai am re-rula instrucțiunea SELECT, nu vor exista diferențe, chiar dacă tocmai am creat indexul. De ce este asta?, Pentru că folosesc SQL Server Express edition în scopul acestei serii și numai în edițiile Enterprise și Developer ale SQL Server, Optimizatorul de interogare va lua în considerare indexul.
nu vă faceți griji, deoarece putem forța SQL Server să utilizeze un index atunci când generează planuri de execuție. Acest lucru se face folosind opțiunea NOEXPAND., NOEXPAND se aplică numai pentru indexate vedere:
|
1
|
SELECT * FROM dbo.vEmployeeSalesOrders CU (NOEXPAND)
|
astfel, am forțat SQL Server pentru a utiliza cluster de index care înseamnă, practic, nu folosesc fond al sistemului tabele atunci când preluarea de date., După cum se poate observa mai jos, am făcut unele progrese prin eliminarea unui număr de operațiuni:
de fapt, putem executeboth SELECTAȚI afirmațiile simultan și compara rezultatele uitandu-se la planurile de execuție:
vrei să te uiți la asta? Dacă comparăm costul interogării primei declarații SELECT (w / o index 95%) cu cea de-a doua declarație SELECT (w/ index 5%), aș spune că este un câștig imens de performanță folosind un singur index.,
concluzie
indexurile sunt mari, deoarece acestea accelera performanța și cu un index pe o vizualizare ar trebui să accelereze într-adevăr performanța, deoarece indicele este stocat în baza de date. Indexarea atât a vizualizărilor, cât și a tabelelor este una dintre cele mai eficiente modalități de a îmbunătăți performanța interogărilor și a aplicațiilor care le utilizează.
aș dori să încheie lucrurile și să termin această serie de învățare a instrucțiunii CREATE VIEW SQL cu acest articol. Am acoperit destul de mult totul despre crearea și modificarea vizualizărilor cu T-SQL., Am început cu instrucțiunea CREATE VIEW SQL, am creat câteva vizualizări, le-am modificat, le-am șters și multe altele.sper că această serie de învățare a instrucțiunii CREATE VIEW SQL a fost informativă pentru dvs. și vă mulțumesc că ați citit-o.,
CREAȚI o VIZUALIZARE SQL: Introducerea de date prin vederi în SQL Server
CREAȚI o VIZUALIZARE SQL: De lucru cu vizualizări indexate în SQL Server
- Autor
- Mesajele Recente
El a scris pe larg pe ambele SQL Colibă și ApexSQL Centrul de Soluții, pe teme variind de la client tehnologii, cum ar fi rezoluție 4K și temelor, eroare de manipulare a indicelui de strategii, și de monitorizare a performanței.
Bojan lucrează la ApexSQL în Nis, Serbia, ca parte integrantă a echipei concentrându-se pe proiectarea, dezvoltarea, testarea și următoarea generație de instrumente de baze de date, inclusiv MySQL și SQL Server, și atât stand-alone de instrumente și integrarea în Visual Studio, SSMS, și VSCode.,
Vezi mai multe despre Bojan la LinkedIn
Vezi toate posturile de Bojan Petrovic
- Cod Visual Studio pentru MySQL și MariaDB de dezvoltare – 13 August 2020
- SQL UPDATE sintaxa explicat – 10 iulie, în 2020,
- CREARE VIZUALIZARE SQL: de Lucru cu vizualizări indexate în SQL Server – 24 Martie, în 2020,