SQL Magazine 7 Artikel – SQL Server: Optimieren Sie Ihre Abfragen mit indizierten Ansichten

admin 0 Comments Articles

SQL Magazine 7 Artikel – SQL Server: Optimieren Sie Ihre Abfragen mit indizierten Ansichten

Fragen als erledigt markieren

Der Zweck dieses Artikels ist es, das Konzept der indizierten Ansichten in SQL Server vorzustellen und zu zeigen, wie man diese Art von Ansicht implementiert und verwendet, um Abfragen zu optimieren.

Ansichtskonzept

Ansichten werden auch als „virtuelle Tabellen“ bezeichnet, da sie eine Alternative zur Verwendung von Tabellen für den Datenzugriff darstellen. Ein View ist nichts anderes als eine SELECT-Anweisung, die in einem Objekt gekapselt ist. Die Syntax für die Erstellung einer Ansicht ist in Listing 1 dargestellt.

CREATE VIEW nome_da_visão ...) ] AS subconsulta;

Ein Beispiel für die Erstellung und Verwendung einer Ansicht finden Sie in Listing 1.

Use NorthWind go create view vi_vendas_mes As Select ano = datepart(yyyy,OrderDate), mes = datepart(mm,OrderDate), qtde_total = sum(Quantity) from Orders o inner join od on o.OrderId = od.OrderId group by datepart(yyyy,o.OrderDate), datepart(mm,o.OrderDate) go select * from vi_vendas_mês go ano mes qtde_total contador ----------- ----------- ----------- -------------------- 1996 7 1462 59 1996 8 1322 69 1996 9 1124 57 1996 10 1738 73 1996 11 1735 66

Zu den Vorteilen der Verwendung von Views gehören:

• Vereinfachung des Codes: Sie können komplexe SELECTs einmal schreiben, sie im View kapseln und von dort aus auslösen, als ob es sich um eine beliebige Tabelle handeln würde;
• Sicherheitsaspekte: Nehmen wir an, Sie haben vertrauliche Informationen in einigen Tabellen und möchten daher, dass nur einige Benutzer Zugang zu ihnen haben. Auf einige Spalten in diesen Tabellen müssen jedoch alle Benutzer zugreifen können. Eine wirksame Lösung für dieses Problem besteht darin, eine Ansicht zu erstellen und die sensiblen Spalten auszublenden. Auf diese Weise kann man die Zugriffsrechte auf die ursprüngliche Tabelle unterdrücken und den Zugriff auf den View freigeben;
• Möglichkeit der Abfrageoptimierung durch Implementierung indizierter Views.

Indizierte Views in der Praxis

Views kapseln SELECT-Anweisungen, d.h. immer wenn sie ausgelöst werden, werden die mit ihnen verbundenen SELECT-Anweisungen ausgeführt. Ansichten erstellen keine Repositories für die Daten, die sie zurückgeben (wie es die Tabelle tut). Nun, es wäre toll, wenn wir das Ergebnis des SELECT-Befehls, das in der Ansicht gefunden wurde, in einer Tabelle „materialisieren“ könnten, indem wir Indizes erstellen, um den Zugriff darauf zu erleichtern. Nun, die indizierten Ansichten tun genau das. Die Ausführung eines SELECT in einer indizierten Ansicht hat die gleiche Wirkung wie die Ausführung eines SELECT in einer herkömmlichen Tabelle.

Der Hauptzweck indizierter Ansichten besteht darin, die Leistung zu steigern, und der Vorteil von SQL Server besteht darin, dass Ausführungspläne die indizierte Ansicht als Mittel zum Zugriff auf Daten berücksichtigen können, selbst wenn der Ansichtsname in der Abfrage nicht explizit angegeben wurde. Dies ist in der Enterprise Edition von SQL Server 2000 möglich, wo der Befehlsoptimierer Daten direkt in der indizierten Ansicht auswählen kann (anstatt die in der Tabelle vorhandenen Rohdaten auszuwählen), wie wir im Folgenden sehen werden.

Schrittweise Erstellung einer indizierten Ansicht

1. Bei der Konfiguration der Umgebung besteht der erste Schritt darin, den Status einiger Parameter in der Sitzung festzulegen, in der die Ansicht erstellt und verwendet werden soll, denn da die indizierte Ansicht in einer Tabelle „materialisiert“ ist, kann nichts ihr Ergebnis beeinträchtigen. Stellen Sie sich zum Beispiel folgendes Szenario vor:
2. Eine bestimmte Einstellung, die das Ergebnis eines SELECT beeinflusst (z.B. concat_null_yelds_null), wird gesetzt, bevor die indizierte Ansicht erstellt wird;
3. Die indizierte Ansicht wird erstellt; beachten Sie, dass das Ergebnis der Ansicht auf der Festplatte entsprechend der im vorherigen Schritt gesetzten Einstellung „materialisiert“ wird;
4. Dann wird die Einstellung deaktiviert und die indizierte Ansicht wird vom Benutzer ausgeführt. Da die Ansicht materialisiert wurde, erhalten wir ein Ergebnis, das nicht mit der aktuellen Konfiguration übereinstimmt.

Zum Beispiel zeigt Listing 2 den Unterschied im Ergebnis eines Befehls, wenn die Eigenschaft concat_null_yields_null geändert wird.

set concat_null_yields_null ON print null + 'abc' -------------------------------------- Set concat_null_yields_null OFF print null + 'abc' -------------------------------------- abc

Stellen Sie sich vor, was passieren würde, wenn die indizierte Ansicht mit aktivierter Eigenschaft concat_null_yields_null erstellt würde, die aktuelle Sitzung aber mit deaktivierter Eigenschaft – der gleiche SELECT würde zu unterschiedlichen Ergebnissen führen!

Dieses Problem wurde auf einfache Weise gelöst – um indizierte Ansichten zu erstellen und zu verwenden, ist es zwingend erforderlich, die Umgebung entsprechend einer Liste von Standardwerten zu konfigurieren. Auf diese Weise ist es unmöglich, andere Ergebnisse zu erzielen, da die Ansicht einfach nicht funktioniert, wenn eine der Einstellungen auf einen nicht standardmäßigen Wert eingestellt ist.

Tabelle 1 zeigt diese Einstellungen und ihre jeweiligen Standardwerte.

(*) Die ID wird im Befehl sp_configure verwendet. Was die einzelnen Einstellungen bewirken, erfahren Sie im Abschnitt „Erforderliche Einstellungen für indizierte Ansichten“. Es gibt zwei Möglichkeiten, den Wert einer Konfiguration zu ändern:

• Direkt in der Sitzung: Führen Sie den Befehl set ON | OFF aus
• Ändern Sie die bestehende Servervoreinstellung: Führen Sie sp_Configure ‚user options‘, . Die Identifikationsnummern der einzelnen Konfigurationen sind in Tabelle 1 aufgeführt.
  Anmerkung: AritHabort hat die ID 64 und Quoted_Identifier hat die ID 256. Um z.B. Quoted_Identifier + AritHabort zu verknüpfen, würden wir sp_cofigure ausführen und als Parameter das Ergebnis von 64+256 (=320) übergeben: sp_configure ‚user options‘, 320. Eine vollständige Auflistung der den einzelnen Konfigurationen zugewiesenen IDs finden Sie unter http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_config_9b1q.asp.

Um den Status der einzelnen Parameter in Tabelle 1 zu überprüfen, verwenden Sie die Funktion SessionProperty(‚Parametername‘) oder den Befehl DBCC UserOptions.

Setzen Sie auf diese Weise alle Parameter entsprechend der Spalte „Erforderlicher Status für indizierte Ansichten“ in Tabelle 1 – wenn dies nicht geschieht, erlaubt SQL Server Ihnen nicht, die indizierte Ansicht zu erstellen/auszuführen.

Erstellen der indizierten Ansicht

Wir werden eine Ansicht erstellen, um die täglich verkaufte Menge in der Tabelle „Order Details“ in der NorthWind-Datenbank zu summieren. Siehe Listing 3.

use NorthWind go create view vi_vendas_mes with SchemaBinding as select ano = datepart(yyyy,OrderDate), mes = datepart(mm,OrderDate), qtde_total = sum(Quantity), contador = count_big(*) from dbo.Orders o inner join dbo. od on o.OrderId = od.OrderId group by datepart(yyyy,o.OrderDate),datepart(mm,o.OrderDate) go

Bei der Erstellung indizierter Views sind einige Besonderheiten zu beachten:

• Der View muss deterministisch sein. Eine Ansicht ist deterministisch, wenn wir in ihrem Code nur deterministische Funktionen verwenden. Derselbe SELECT-Befehl, der wiederholt auf einer indizierten Ansicht ausgeführt wird (unter Berücksichtigung einer statischen Basis), kann keine unterschiedlichen Ergebnisse liefern. Deterministische Funktionen gewährleisten, dass das Ergebnis einer Funktion unverändert bleibt, egal wie oft sie ausgeführt wird. Die Funktion DatePart zum Beispiel ist deterministisch, da sie für ein bestimmtes Datum immer das gleiche Ergebnis liefert. Die Funktion getdate() wird bei jeder Ausführung einen anderen Wert zurückgeben. Die vollständige Liste der deterministischen Funktionen von SQL Server 2000 finden Sie unter http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_08_95v7.asp
• Prüfen Sie, ob es keine Syntaxeinschränkungen gibt. Die unten aufgeführten Klauseln, Funktionen und Abfragetypen können das Schema einer indizierten Ansicht nicht integrieren:
  • MIN, MAX,TOP
  • VARIANCE,STDEV,AVG
  • COUNT(*)
  • SUM auf Spalten, die Nullwerte zulassen
  • DISTINCT
  • ROWSET-Funktion
  • Ableitende Tabellen, Self-Joins, Unterabfragen, outer joins
  • DISTINCT
  • UNION
  • Float, text, ntext und image
  • COMPUTE und COMPUTE BY
  • HAVING, CUBE und ROLLUP

Es ist erforderlich, die indizierten Ansichten mit SchemaBinding zu erstellen. Um den Inhalt der Ansicht konsistent zu halten, dürfen Sie die Struktur der Tabellen, aus denen die Ansicht stammt, nicht ändern. Um diese Art von Problem zu vermeiden, ist es zwingend erforderlich, SchemaBinding zu verwenden, wenn indizierte Ansichten erstellt werden, da diese Option es nicht erlaubt, die Tabellenstruktur zu ändern, ohne die Ansicht vorher zu löschen.

Um die GROUP BY-Klausel zu verwenden, ist es zwingend erforderlich, die Funktion COUNT_BIG(*) einzuschließen. Die Funktion count_big(*) tut dasselbe wie count(*), gibt aber einen Wert vom Typ bigint (8 Bytes) zurück.

Informieren Sie immer den Eigentümer der Objekte, auf die in der indizierten Ansicht verwiesen wird. Verwenden Sie select * from dbo.Orders anstelle von select * from Orders, da es möglich ist, Tabellen mit demselben Namen, aber unterschiedlichen Eigentümern zu haben. Da die Option schemabinding obligatorisch ist, benötigt SQL Server die genaue Objektspezifikation, um die Schemaänderung einzuschränken.

Erstellen eines Clusterindexes in der Ansicht (Materialisierung)

Die in Punkt 2 erstellte Ansicht verhält sich noch nicht wie eine indizierte Ansicht, da das Ergebnis des Select-Befehls noch nicht in eine Tabelle materialisiert wurde. Sie können diese Aussage bestätigen, indem Sie den Befehl sp_spaceused in Query Analyzer ausführen, der die Anzahl der Zeilen und den von den Tabellen belegten Speicherplatz zurückgibt (Listing 4).

sp_SpaceUsed vi_vendas_mes -------------------------------------------------------------------------------------- Server: Msg 15235, Level 16, State 1, Procedure sp_spaceused, Line 91 Views do not have space allocated.

Beachten Sie in Listing 5, dass die Verarbeitung der Ansicht rein logisch ist, so dass der Wert von Physical Reads Null ist. Beachten Sie die Werte, die in Logical Reads und Physical Reads aufgezeichnet wurden (1672+0+4+0=1676) – wir werden diese Werte in unseren zukünftigen Vergleichen verwenden.

Set statistics io ON Select * from vi_vendas_mesgo---------------------------------------------------------ano mes qtde_total contador ----------- ----------- ------------- -------------------- 1996 7 1462 591996 8 1322 691996 9 1124 57.....(23 row(s) affected)Table 'Order Details'. Scan count 830, logical reads 1672, physical reads 0, read-ahead reads 0.Table 'Orders'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0.

Um zu prüfen, ob die Ansicht indiziert (materialisiert) werden kann, d. h. ob sie innerhalb der für indizierte Ansichten erforderlichen Standards und Einstellungen erstellt wurde, sollte das Ergebnis des folgenden SELECT gleich 1 sein.

select ObjectProperty(object_id('vi_vendas_mes'),'IsIndexable')

Wenn die Voraussetzungen erfüllt sind, können wir nun den Index erstellen. Die Syntax hat das gleiche Format wie bei der Erstellung von Indizes in herkömmlichen Tabellen:

create unique clustered index pk_ano_mes on vi_vendas_mes (ano,mes)

Beachten Sie, dass der Cluster-Index erforderlich ist, weil er Seiten von Daten erzeugt. Sie können Nicht-Cluster-Indizes in indizierten Ansichten erst erstellen, nachdem Sie den Cluster-Index erstellt haben.

Jetzt ist das in der Ansicht gefundene SELECT materialisiert worden, was mit dem Befehl sp_spaceused im Query Analyzer (Listing 6) nachgewiesen werden kann.

sp_SpaceUsed vi_vendas_mêsgo-----------------------------------------------------Name Rows Reserved data index_size Unusedvi_vendas_mes 23 24 KB 8 KB 16 KB 0 KB

Verwendung indizierter Ansichten

Eine Möglichkeit, auf eine indizierte Ansicht (wie auch auf eine herkömmliche Ansicht) zuzugreifen, besteht darin, ihren Namen im SELECT-Befehl zu referenzieren:

select * from vi_vendas_mes

Vergleichen Sie das Volumen der verschobenen Seiten in Listing 5 (1672+4=1676) mit dem in Listing 7 (2+0=2). Der Unterschied ist beträchtlich – die Erstellung der indizierten Ansicht reduzierte die erforderliche Gesamt-E/A um 1674 Seiten.

Set statistics io ON select * from vi_vendas_mes go --------------------------------------------------------- ano mes qtde_total contador ----------- ----------- ------------- -------------------- 1996 7 1462 59 1996 9 1124 57 ..... (23 row(s) affected) Table 'vi_vendas_mes'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.

Schauen wir uns ein weiteres Beispiel an. Abbildung 1 zeigt den Ausführungsplan einer Abfrage. Bestätigen Sie, dass die Ansicht ausgewählt wurde, obwohl sie in der SELECT-Zeile nicht vorhanden war.

Bei der Erstellung des Abfrageausführungsplans stellte der Optimierer fest, dass in vi_sales_mes bereits vorab zusammengefasste Daten für die Abfrage vorhanden waren, und wählte die Daten direkt in der indizierten Ansicht aus.

Beachten Sie, dass die in Abbildung 1 ausgeführte Abfrage mit der in der Ansicht vi_sales_mes identisch ist. Der Zugriff des Optimierers auf die Ansicht ist jedoch unabhängig von der Ähnlichkeit zwischen der ausgeführten Abfrage und der Ansichtsabfrage. Bei der Auswahl der indizierten Ansicht durch den Abfrageprozessor wird nur das Kosten-Nutzen-Verhältnis berücksichtigt. Auf diese Weise müssen die ausgeführten Abfragen nicht mit dem View identisch sein (siehe Abbildung 3).

Es müssen jedoch einige Regeln beachtet werden, damit der indizierte View vom Abfrageoptimierer berücksichtigt wird:

• Der im View vorhandene Join muss in der Abfrage „enthalten“ sein: Wenn die Abfrage einen Join zwischen den Tabellen A und B durchführt und der View einen Join zwischen A und C, wird der View im Ausführungsplan nicht ausgelöst. Wenn die Abfrage jedoch einen Join zwischen A, B und C durchführt, kann der View ausgelöst werden.
• Die in der Abfrage festgelegten Bedingungen müssen mit den Bedingungen im View übereinstimmen: Im SELECT in Abbildung 2 wird der indizierte View vi_sales_mes nicht berücksichtigt, weil die Where-Klausel im View-Code nicht vorhanden war, was dazu führte, dass Zeilen mit Quantity <= 5 im Join berechnet wurden.

Wenn die Abfrage dagegen die Bedingung where where sum(Menge) > 5 enthält, wird die Ansicht vi_sales_mes im Ausführungsplan berücksichtigt, da die Abfragebedingung eine Teilmenge der in der Ansicht vorhandenen SELECT ist.

Spalten mit Aggregationsfunktionen in der Abfrage müssen in der Definition der Ansicht „enthalten“ sein: Wenn die Ansicht die Spalte qtde=sum(Menge) zurückgibt und die Abfrage eine Spalte vlr_unit=sum(Einheitspreis) hat, wird die Ansicht nicht berücksichtigt.

Abbildung 3 zeigt einen SELECT-Befehl, mit dem Sie die Intelligenz des Befehlsoptimierers unter Beweis stellen können – die Berechnung von AVG(Menge) wurde durch die Division von SUM(Menge) / Count_Big(*) ersetzt, dargestellt durch das Symbol „Skalar berechnen“. Das Prädikat where sum(Quantity) > 1500 (dargestellt durch das Filter-Symbol) wird ebenfalls berücksichtigt.

Allgemeine Überlegungen zur Verwendung indizierter Ansichten:

• Indizierte Ansichten können in jeder Version von SQL Server 2000 erstellt werden. Nur in der Enterprise Edition werden sie jedoch automatisch vom Abfrageoptimierer ausgewählt.
• In anderen Versionen als Enterprise müssen Sie den NoExpand-Hinweis verwenden, um auf die indizierte Ansicht als herkömmliche Tabelle zuzugreifen. Wenn NoExpand nicht verwendet wird, wird die indizierte Ansicht als „normale“ Ansicht betrachtet.
  Hinweis: Der Expand-Hinweis bewirkt das Gegenteil von NoExpand: Er behandelt die indizierte Ansicht wie eine „normale“ Ansicht und zwingt SQL Server, die SELECT-Anweisung während der Laufzeitphase auszuführen.
• Die Wartung einer indizierten Ansicht erfolgt automatisch (wie bei Indizes); sie erfordert keine zusätzliche Synchronisierung. Aufgrund seiner eigenen Eigenschaft (er speichert in der Regel vorverdichtete Daten) ist seine Aktualisierung tendenziell etwas langsamer als die herkömmlicher Indizes.
• Die Verwendung indizierter Sichten in OLTP-Basen erfordert Vorsicht, denn obwohl sie eine hohe Leistung bei Abfragen bieten, verursachen sie Overhead bei den Prozessen, die die in der Sicht verbundenen Tabellen ändern. In Situationen, in denen eine hohe Schreibleistung mit häufigen Aktualisierungen erforderlich ist, wird die Erstellung von indizierten Ansichten nicht empfohlen.
• Wie bei Indizes analysiert der Optimierer den Ansichtscode in Enterprise-Versionen als Teil des Prozesses der Auswahl des besten Ausführungsplans für eine Abfrage. Wenn es jedoch viele indizierte Ansichten gibt, die für dieselbe Abfrage ausgeführt werden können, kann sich diese Auswahlzeit erheblich verlängern, da alle Ansichten analysiert werden. Verwenden Sie daher bei der Implementierung der Ansichten den gesunden Menschenverstand.

Erforderliche Einstellungen für indizierte Ansichten

ANSI_NULLS

Definiert, wie Vergleiche mit Nullwerten durchgeführt werden (Listing 8).

set ANSI_NULLS ON declare @var char(10) set @var = null if @var = null print 'VERDADEIRO' else print 'FALSO' ------------------------------------- FALSO set ANSI_NULLS OFF declare @var char(10) set @var = null if @var = null print 'VERDADEIRO' else print 'FALSO' -------------------------------------- VERDADEIRO

ANSI_PADDING

Bestimmt, wie char-, varchar-, binary- und varbinary-Spalten gespeichert werden sollen, wenn ihr Inhalt kleiner ist als die in der Tabellenstruktur definierte Größe. SQL Server 2000 ist standardmäßig so eingestellt, dass ansi_padding eingeschaltet bleibt (=ON); unter dieser Bedingung gelten die folgenden Regeln:

• Beim Aktualisieren von Char-Spalten wird am Ende der Zeichenfolge ein Leerzeichen hinzugefügt, wenn sie kleiner ist als die in der Spaltenstruktur definierte Größe. Die gleiche Regel gilt für binäre Spalten (in diesem Fall wird der Raum durch eine Folge von Nullen gefüllt)
• Varchar- oder varbinary-Spalten folgen nicht der obigen Regel: Sie behalten immer ihre ursprüngliche Größe.

ARITHABORT

Wenn aktiviert, bricht die Abfrageausführung ab, wenn eine Division durch Null oder eine Art von Überlauf auftritt.

QUOTED_IDENTIFIER

Wenn aktiviert, erlaubt die Verwendung von doppelten Anführungszeichen zur Angabe von Namen von Tabellen, Spalten usw. – Auf diese Weise können diese Namen Leerzeichen und Sonderzeichen enthalten.

CONCAT_NULL_YELDS_NULL

Steuert das Ergebnis der Verkettung von Strings mit Nullwerten. Wenn aktiviert, wird festgelegt, dass diese Verknüpfung einen Nullwert zurückgeben muss; andernfalls wird die Zeichenkette selbst zurückgegeben.

ANSI_WARNINGS

Wenn aktiviert, wird die Generierung von Fehlermeldungen festgelegt, wenn:

1. Sie Verdichtungsfunktionen verwenden und Nullwerte im Abfragebereich gefunden werden;
2. Divisionen durch Null oder arithmetischer Überlauf gefunden werden.

NUMERIC_ROUNDABORT

Steuert, wie SQL Server vorgehen soll, wenn bei arithmetischen Operationen ein Verlust der numerischen Genauigkeit auftritt. Wenn der Parameter aktiviert ist und eine Variable mit einer Genauigkeit von zwei Dezimalstellen einen Wert mit drei Dezimalstellen erhält, wird die Operation abgebrochen. Wenn der Parameter deaktiviert ist, wird der Wert auf zwei Dezimalstellen abgeschnitten.

Fazit

Wenn es um Abfrageoptimierung geht, sind indizierte Ansichten eine gute Wahl, um die Leistung zu steigern. Bewerten Sie also gründlich die Abfragen, die mit Zusammenfassungen zu tun haben und mit einer gewissen Häufigkeit ausgeführt werden, und entscheiden Sie sich für die Erstellung indizierter Ansichten. Das Ergebnis ist es wert!