Toto je čtvrtý článek ze série o učení VYTVOŘIT ZOBRAZENÍ SQL. Zatím jsme udělali hodně vytváření a změny zobrazení pomocí T-SQL. V této poslední části se chci podívat na to, jak pracovat s indexovanými pohledy.
jako vždy, abyste mohli sledovat spolu se sérií, doporučujeme nejprve přečíst předchozí části a poté tento., Je to především proto, že jsme vytvořili náš vlastní ukázkové databáze a objekty v ní od začátku, že budeme používat v této části, ale také proto, že bude mnohem jednodušší vidět velký obrázek.
Zde jsou předchozí tři kusy VYTVOŘIT ZOBRAZENÍ SQL série:
- Vytváření názorů v SQL Server
- změna zobrazení v SQL Server
- Vkládání dat prostřednictvím zobrazení v SQL Server
přes hlavu a přečíst si těch, před pokračováním s tímto jeden.
Úvod
první věc, kterou uděláme, je vytvořit indexovaný pohled., K tomu samozřejmě použijeme příkaz CREATE VIEW SQL, jako jsme to udělali mnohokrát v sérii. Ale obecná myšlenka, jak název říká, je vidět, jak pracovat s indexovanými pohledy, zjistit, jaké jsou požadavky na přidání indexu do zobrazení a jak to udělat programově. Kromě toho, abychom vysvětlili výhody indexovaných pohledů, budeme se dívat na plány poprav v SQL Serveru. Oni jsou skvělý nástroj pro Dba a vývojáře, pokud jde o nalezení a upevnění překážkou v pomalý běh dotaz.,
Bez dalších okolků, pojďme vytvořit zobrazení pomocí tlačítka VYTVOŘIT ZOBRAZENÍ SQL příkazu z níže a uvidíte, co to dělá:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
POUŽÍT SQLShackDB;
VYTVOŘIT POHLEDU dbo.,vEmployeeSalesOrders
S SCHEMABINDING
VYBERTE Zaměstnanců.Employeid,
produkty.ProductID,
SUM(cena * množství) jako SaleTotal,
SaleDate
od dbo.Zaměstnanci
připojte se k dbo.Prodej zaměstnanců.Employeid = Sales.Employeid
připojte se k dbo.Produkty na prodej.ProductID = Produkty.ProductID
skupina podle zaměstnanců.Employeid,
produkty.ProductID,
prodej.,
GO
|
Všimněte si, že tento pohled má možnost SCHEMABINDING na něm. Důvodem, proč má tuto možnost zapnutou, je to, že při vytváření indexů na zhlédnutí se ve skutečnosti fyzicky ukládají do databáze. Jinými slovy, cokoli, na co se tento názor spoléhá, pokud jde o tabulky, struktura se nemůže změnit od toho, co odkazujeme.
proto musí být vázána na podkladové tabulky, abychom je nemohli modifikovat způsobem, který by ovlivnil definici zobrazení., Pokud se je pokusíme jakýmkoli způsobem změnit, server SQL Server způsobí chybu, že tento pohled závisí na něčem. Podívejte se na to jako na tvrdý požadavek na vytvoření indexu v zobrazení.
Jakmile je příkaz spuštěn úspěšně, měli byste vidět vEmployeeSalesOrders zobrazit v rámci Zobrazení složky v Průzkumník Objektů, jak je uvedeno níže:
definici zobrazení je trochu složitější dotaz. Dostali jsme agregát ve výběrovém prohlášení následovaný klauzulí skupina podle., Pamatujte, že když máme v dotazu agregát, sčítá čísla dohromady, takže musíme mít skupinu podle klauzule.
v podstatě, když je v dotazu skupina, musíme seskupit vše, co je v seznamu select kromě agregátu.,
Nyní, už jsem si vytvořil názor, ale pamatujte, že je to vždy dobrý nápad vyzkoušet definice pohledu spuštění pouze VYBRAT část VYTVOŘIT ZOBRAZENÍ SQL příkaz, aby viděli, co to vrací:
Pohybující se na, zde je návod, jak si můžete zkontrolovat, zda Schématu vázán možnost je povolena nebo zakázána., Přes hlavu na Objekt aplikace Explorer, rozbalte Zobrazení, klepněte pravým tlačítkem myši na zobrazení a vyberte možnost Vlastnosti:
Mezi všechny ostatní informace v Zobrazení Vlastnosti okno, uvidíte, jestli Schématu vázán možnost je nastavena na hodnotu True nebo False podle Obecné stránky.
vytváření indexovaných zobrazení
pojďme dál a vytvořte index na našem pohledu., 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(Číslozaměstnance, ProductID, SaleDate);
|
hit tlačítko Spustit v SSMS, SQL Server bude hodit chyba říká, že index nemůže být vytvořen:
Zde je úplný chybovou zprávu, která nemůže být viděn v záběru výše:
Nelze vytvořit index na zobrazení ‚SQLShackDB.dbo.vEmployeeSalesOrders‘, protože jeho Seznam select nezahrnuje správné použití COUNT_BIG. Zvažte přidání COUNT_BIG (*) pro výběr seznamu.,
v tomto případě potřebujeme COUNT_BIG vzhledem k tomu, že podle našeho názoru používáme skupinu.
obecně, pokud používáme agregáty jako COUNT, SUM, AVG atd. v seznamu výběru indexu musíme také zahrnout COUNT_BIG, abychom na něm vytvořili index.
to je přesně to, co budeme dělat.,pomocí VYTVOŘIT ZOBRAZENÍ SQL a přidat COUNT_BIG do vyberte seznam pomocí skriptu z níže:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
POUŽÍT SQLShackDB;
ZMĚNIT ZOBRAZENÍ dbo.,vEmployeeSalesOrders
S SCHEMABINDING
VYBERTE Zaměstnanců.Employeid,
produkty.ProductID,
SUM(cena * množství) JAKO SaleTotal,
SaleDate,
COUNT_BIG(*) JAKO počet záznamů
OD dbo.Zaměstnanci
připojte se k dbo.Prodej zaměstnanců.Employeid = Sales.Employeid
připojte se k dbo.Produkty na prodej.ProductID = Produkty.ProductID
skupina podle zaměstnanců.Employeid,
produkty.ProductID,
prodej.,SaleDate;
|
Pokud vás zajímá, proč se to děje v tomto případě, odpověď je, protože SQL Server potřebuje způsob, jak najít číslo záznamu, který chceme přeměnit na index a to je také jeden z celé řady omezení vytváření indexovaných zobrazení.
zatím vše vypadá dobře. Úspěšně jsme změnili definici z našeho pohledu:
Nyní se můžeme vrátit k vytváření indexu na zobrazení vykonáváním dříve používané scénář ještě jednou., Tentokrát bude operace dokončena hladce. Pokud půjdeme do Objektu Explorer a rozšířit Indexy složky našeho pohledu, podíváme se na nově vytvořený index:
Z tu, když jsme klepněte pravým tlačítkem myši na index a vyberte Vlastnosti, v rámci Obecné stránce, můžete vidět zobrazení jméno, index, název, typ, klíčové sloupce, atd.,:
Pokud se přepneme do Skladu, uvidíte, že to má Filegroup, protože to je fyzicky uložen v databázi:
Kromě toho, pokud bychom přejít k Fragmentaci, je třeba říci, že Celková fragmentace je nula procent, protože máme jen málo záznamů v našich tabulkách:
Hledáte podrobné, ale zábavné a snadné ke čtení nátěr na udržování a sledování SQL indexy? Podívejte se na údržbu indexu SQL.,
mazání indexů
než půjdeme dál, uvidíme, jak můžeme index odstranit. Nejjednodušší je kliknout pravým tlačítkem myši na index v Průzkumníku objektů a použít možnost Odstranit. Ale v případě, že potřebujete klesnout více indexů najednou, prohlášení o poklesu indexu se hodí. To je to, co budeme dělat, protože koneckonců, toto je řada T-SQL o učení příkazu 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;
|
Když potřebujete shodit více indexy, stačí zadat všechny názvy odděleny čárkami.
Generování náhodných dat
Nyní, že jsme se zbavili index, pojďme vytvářet nějaké náhodné údaje v naší tabulce, takže se můžeme podívat na provedení plánu a vidět, jak SQL Server načítá data pod kapotou. Analýza plánu provádění ukáže rozdíl v tom, jak je výkon ovlivněn spuštěním dotazu s indexem a bez něj v zobrazení.,
pomocí skriptu zespodu vložte 50000 náhodných záznamů do prodejní tabulky:
nebudu vás podrobně procházet skriptem, ale je to v podstatě smyčka, která provede 50000krát a vloží náhodná data do prodejní tabulky., zkontrolovat, zda záznamy byly vloženy úspěšně, spusťte následující příkaz SELECT, který bude počítat všechny záznamy z Prodeje tabulka:
|
1
2
3
4
|
POUŽÍT SQLShackDB;
SELECT COUNT(*) Z Prodeje
|
počet vrátil se ukazuje, že tam jsi 50006 řádky v tabulce Prodeje., To je počet záznamů, které jsme právě generované + 6, že původně jsme měli:
Analýza plnění plánů
Nyní, že máme nějaké údaje v naší tabulce, můžeme skutečně prokázat použití indexu na zobrazení. Podívejme se na zobrazení vEmployeeSalesOrders a uvidíme, jak SQL Server načte 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;
|
Tento skript vrátil 23814 řádky, ale co je důležitější, generuje provádění plánu dotazu. Nezapomeňte, že jsme dříve snížili index podle našeho názoru. Takže, právě teď není žádný index na našem pohledu. Proto SQL Server bude dělat pár prohledávání tabulky, jak je uvedeno níže:
To je to nejhorší, co v databázi světě, zejména na tabulky s velké množství dat., Je to v pořádku mít tabulky skenování s malým množstvím dat, např. případ s našimi zaměstnanci a produkty tabulky, ale je to špatné pro prodejní tabulky, protože má 50k + záznamy.
nejjednodušší způsob, jak se zbavit skenů tabulky, je vytvořit na něm index, protože dramaticky urychluje věci. Takže, co uděláme pro vyřešení tohoto problému, je znovu spustit skript pro vytvoření jedinečného seskupeného indexu v zobrazení vEmployeeSalesOrders.
Nyní, pokud právě znovu spustíme příkaz SELECT, nebudou žádné rozdíly, i když jsme právě vytvořili index. Proč?, Protože používám SQL Server Express edition pro účely této série a pouze v podnikových a vývojářských vydáních SQL Serveru bude optimalizátor dotazů skutečně brát index v úvahu.
žádné starosti, protože můžeme skutečně donutit SQL Server používat index při generování plánů provádění. To se provádí pomocí možnosti NOEXPAND., NOEXPAND platí pouze pro indexovaná zobrazení:
|
1
|
SELECT * FROM dbo.vEmployeeSalesOrders S (NOEXPAND)
|
Stejně jako to, že jsme nuceni SQL Server použít seskupený index, což v podstatě znamená, nepoužívejte podkladové tabulky při načítání dat., Jak lze vidět níže, jsme udělali nějaký pokrok tím, že odstraní řadu operací:
Ve skutečnosti, můžeme executeboth SELECT současně a porovnat výsledky při pohledu na provádění plánů:
By ses na to podívat? Porovnáme-li dotaz, náklady na první SELECT (w/o index 95%), na druhém SELECT (w/ index 5%), řekl bych, že to je obrovský výkon zisk pomocí jediného indexu.,
Závěr
Indexy jsou skvělé, protože oni urychlit výkon a s indexem na názor, mělo by to urychlit výkon, protože index je uložen v databázi. Indexování zobrazení i tabulek je jedním z nejúčinnějších způsobů, jak zlepšit výkon dotazů a aplikací, které je používají.
chtěl bych zabalit věci a dokončit tuto sérii učení příkazu CREATE VIEW SQL s tímto článkem. Do značné míry jsme pokryli vše o vytváření a změně zobrazení pomocí T-SQL., Začali jsme s příkazem CREATE VIEW SQL, vytvořili několik pohledů, změnili je, odstranili a mnohem více.
doufám, že tato řada učení příkaz CREATE VIEW SQL byl pro vás informativní a děkuji vám za přečtení.,
VYTVOŘIT ZOBRAZENÍ SQL: Vkládání dat prostřednictvím zobrazení v SQL Server,
VYTVOŘIT ZOBRAZENÍ SQL: Práce s indexovaná zobrazení SQL Server,
- Autora
- Poslední Příspěvky
napsal rozsáhle jak na SQL Shack, tak na řešení ApexSQL Center, na témata od klientských technologií, jako je rozlišení 4K a theming, manipulace s chybami až po indexové strategie a monitorování výkonu.
Bojan pracuje v ApexSQL v Nis, Srbsko nedílnou součástí týmu, se zaměřením na navrhování, vyvíjení a testování další generace z databáze nástrojů včetně MySQL a SQL Server, a oba stand-alone nástroje a integrace do Visual Studio, SSMS, a VSCode.,
více o Bojan na LinkedIn
Zobrazit všechny příspěvky od Bojan Petrovic
- Visual Studio Code pro MySQL a MariaDB rozvoj – 13. srpna 2020
- AKTUALIZACE SQL syntaxe vysvětlil – 10. července 2020
- VYTVOŘIT ZOBRAZENÍ SQL: Práce s indexovaná zobrazení SQL Server – 24. Března 2020