Freigeben über

XML-Indizes (SQL Server)

  • Artikel

XML-Indizes können für xml-Datentypspalten erstellt werden. Sie indizieren alle Tags, Werte und Pfade für die XML-Instanzen in der Spalte. Die Indizierung verbessert zudem die Abfrageleistung. Ihre Anwendung kann in folgenden Situationen von einem XML-Index profitieren:

  • In Ihren Arbeitsauslastungen sind Abfragen von XML-Spalten üblich. Die Wartungskosten für den XML-Index während der Datenänderung müssen berücksichtigt werden.

  • Ihre XML-Werte sind relativ groß, und die abgerufenen Teile sind relativ klein. Mit dem Erstellen eines Index wird vermieden, dass zur Laufzeit die gesamten Daten analysiert werden müssen. Davon profitieren Indexsuchen und die Abfrageverarbeitung wird effizienter.

XML-Indizes fallen in die folgenden Kategorien:

  • Primärer XML-Index

  • Sekundärer XML-Index

Der erste Index für die Spalte des Datentyps xml muss der primäre XML-Index sein. Mithilfe des primären XML-Index werden drei Arten sekundärer Indizes unterstützt: PATH, VALUE und PROPERTY. Abhängig vom Typ der Abfragen können diese sekundären Indizes die Abfrageleistung steigern.

Hinweis

Sie können einen XML-Index nur dann erstellen oder bearbeiten, wenn die Datenbankoptionen korrekt für die Arbeit mit dem xml-Datentyp festgelegt sind. Weitere Informationen finden Sie unter Verwenden der Volltextsuche mit XML-Spalten.

XML-Instanzen werden in Spalten vom Typ xml als BLOBs (Binary Large Objects) gespeichert. Diese XML-Instanzen können groß sein, und die gespeicherte binäre Darstellung von Instanzen vom Datentyp xml kann bis zu 2 GB groß sein. Ohne Index werden diese BLOBs zur Laufzeit aufgeteilt, um eine Abfrage auszuwerten. Diese Aufteilung kann zeitaufwändig sein. Angenommen, beispielsweise liegt die folgende Abfrage vor:

WITH XMLNAMESPACES ('https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS "PD")  
  
SELECT CatalogDescription.query('  
  /PD:ProductDescription/PD:Summary  
') as Result  
FROM Production.ProductModel  
WHERE CatalogDescription.exist ('/PD:ProductDescription/@ProductModelID[.="19"]') = 1

Um die XML-Instanzen auszuwählen, die die Bedingung in der WHERE -Klausel erfüllen, wird der XML-BLOB (Binary Large Object) in jeder Zeile der Production.ProductModel -Tabelle zur Laufzeit aufgeteilt. Dann wird der Ausdruck (/PD:ProductDescription/@ProductModelID[.="19"]) in der exist() -Methode ausgewertet. Diese Aufteilung zur Laufzeit kann abhängig von der Größe und Anzahl der in der Spalte gespeicherten Instanzen kostenintensiv sein.

Wenn das Abfragen von XML-BLOBs in Ihrer Anwendungsumgebung häufig vorkommt, ist das Indizieren der Spalten des Datentyps xml hilfreich. Das Verwalten des Indexes während der Datenänderung verursacht jedoch auch Kosten.

Primärer XML-Index

Der primäre XML-Index indiziert alle Tags, Werte und Pfade innerhalb der XML-Instanzen in einer XML-Spalte. Damit ein primärer XML-Index erstellt werden kann, muss die Tabelle, in der die XML-Spalte enthalten ist, einen gruppierten Index für den Primärschlüssel der Tabelle aufweisen. SQL Server verwendet diesen Primärschlüssel zum Korrelieren von Zeilen im primären XML-Index mit Zeilen in der Tabelle, in der die XML-Spalte enthalten ist.

Der primäre XML-Index ist eine aufgeteilte und persistente Darstellung der XML-BLOBs in der xml-Datentypspalte. Für jeden XML-BLOB in der Spalte erstellt der Index mehrere Datenzeilen. Die Anzahl der Zeilen im Index entspricht ungefähr der Anzahl der Knoten im XML-BLOB. Wenn eine Abfrage die vollständige XML-Instanz abruft, stellt SQL Server die Instanz aus der XML-Spalte bereit. Abfragen innerhalb der XML-Instanzen verwenden den primären XML-Index und können Skalarwerte oder XML-Teilbäume zurückgeben, indem der Index selbst verwendet wird.

Jede Zeile speichert die folgenden Knoteninformationen:

  • Tagname, z. B. einen Element- oder Attributnamen.

  • Knotenwert.

  • Knotentyp, z. B. Elementknoten, Attributknoten oder Textknoten.

  • Informationen zur Dokumentreihenfolge, die durch einen internen Knotenbezeichner dargestellt wird.

  • Pfad von jedem Knoten zum Stamm der XML-Struktur. Diese Spalte wird in der Abfrage nach path-Ausdrücken durchsucht.

  • Primärschlüssel der Basistabelle. Der Primärschlüssel der Basistabelle wird im primären XML-Index für den Rückwärtsjoin mit der Basistabelle dupliziert, und die maximale Anzahl von Spalten im Primärschlüssel der Basistabelle ist auf 15 beschränkt.

Diese Knoteninformationen werden zum Auswerten und Erstellen der XML-Ergebnisse für eine angegebene Abfrage verwendet. Zu Optimierungszwecken werden der Tagname und die Knotentypinformationen als ganze Zahlen codiert; die Path-Spalte verwendet die gleiche Codierung. Pfade werden außerdem in umgekehrter Reihenfolge gespeichert, damit eine Pfadzuordnung erfolgen kann, wenn nur das Pfadsuffix bekannt ist. Beispiel:

  • //ContactRecord/PhoneNumber , wobei nur die beiden letzten Schritte bekannt sind.

oder

  • /Book/*/Title ; dabei wird das Platzhalterzeichen (*) in der Mitte des Ausdrucks angegeben.

Der Abfrageprozessor verwendet den primären XML-Index für Abfragen, die xml-Datentypmethoden beinhalten und entweder Skalarwerte oder die XML-Teilbäume vom primären Index selbst wiedergeben. (Dieser Index speichert alle notwendigen Informationen, um die XML-Instanz zu rekonstruieren).

Die folgende Abfrage gibt beispielsweise Zusammenfassungsinformationen zurück, die in der CatalogDescription``xml Spalte typ in der ProductModel Tabelle gespeichert sind. Die Abfrage gibt Informationen nur für Produktmodelle zurück <Summary> , deren Katalogbeschreibung auch die <Features> Beschreibung speichert.

WITH XMLNAMESPACES ('https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS "PD")SELECT CatalogDescription.query('  /PD:ProductDescription/PD:Summary') as ResultFROM Production.ProductModelWHERE CatalogDescription.exist ('/PD:ProductDescription/PD:Features') = 1

Hinsichtlich des primären XML-Indexes werden die einzelnen XML-BLOB-Instanzen in der Basistabelle nicht aufgeteilt, sondern die Zeilen in dem Index, der dem jeweiligen XML-BLOB entspricht, werden sequenziell nach dem in der exist() -Methode angegebenen Ausdruck durchsucht. Wenn der Pfad in der Path-Spalte im Index gefunden wird, wird das <Summary> Element zusammen mit seinen Unterstrukturen aus dem primären XML-Index abgerufen und als Ergebnis der query() -Methode in ein binäres großes XML-Objekt konvertiert.

Beachten Sie, dass der primäre XML-Index beim Abrufen einer vollständigen XML-Instanz nicht verwendet wird. Die folgende Abfrage ruft z. B. die gesamte XML-Instanz aus der Tabelle ab, die die Produktionsanweisungen für ein bestimmtes Produktmodell beschreibt.

USE AdventureWorks2012;SELECT InstructionsFROM Production.ProductModel WHERE ProductModelID=7;

Sekundäre XML-Indizes

Wenn Sie die Suchleistung verbessern möchten, können Sie sekundäre XML-Indizes erstellen. Ein primärer XML-Index muss zuerst beendet werden, bevor Sie sekundäre Indizes erstellen können. Die folgenden Typen werden unterschieden:

  • Sekundärer XML PATH-Index

  • Sekundärer XML VALUE-Index

  • Sekundärer XML PROPERTY-Index

Es folgen einige Richtlinien zum Erstellen eines oder mehrerer sekundärer Indizes:

  • Wenn bei Ihrer Arbeitsauslastung im erheblichen Ausmaß Pfadausdrücke für XML-Spalten verwendet werden, führt der sekundäre XML-Index des Typs PATH wahrscheinlich zu einer Beschleunigung Ihrer Arbeitsauslastung. Der häufigste Fall ist das Verwenden der exist() -Methode für XML-Spalten in der WHERE-Klausel von Transact-SQL.

  • Wenn bei Ihrer Arbeitsauslastung mehrere Werte aus einzelnen XML-Instanzen mithilfe von Pfadausdrucken abgerufen werden, kann das Gruppieren der Pfade innerhalb jeder XML-Instanz im PROPERTY-Index nützlich sein. Diese Situation tritt üblicherweise in einem Eigenschaftsbehälterszenario auf, wenn die Eigenschaften eines Objekts abgerufen werden und dessen Primärschlüsselwert bekannt ist.

  • Wenn bei Ihrer Arbeitsauslastung das Abfragen von Werten innerhalb von XML-Instanzen vorkommt, ohne dass dabei die Element- oder Attributnamen bekannt sind, die diese Werte enthalten, können Sie den VALUE-Index erstellen. Dies geschieht in der Regel bei nachgeordneten Achsen, z. B. //author[last-name="Howard"], bei denen <Autorenelemente> auf jeder Ebene der Hierarchie auftreten können. Es tritt auch in Platzhalterabfragen auf, z. B. /book [@* = "novel"], wobei die Abfrage nach Buchelementen> sucht<, die über ein Attribut mit dem Wert "novel" verfügen.

Sekundärer XML PATH-Index

Wenn Ihre Abfragen im Allgemeinen path-Ausdrücke für Spalten des Typs xml angeben, beschleunigt ein sekundärer PATH-Index möglicherweise die Suche. Wie bereits zuvor in diesem Thema beschrieben, ist der primäre Index hilfreich, wenn Sie Abfragen verwenden, die die exist() -Methode in der WHERE-Klausel angeben. Wenn Sie einen sekundären PATH-Index hinzufügen, können Sie die Suchleistung in solchen Abfragen möglicherweise verbessern.

Zwar vermeidet ein primärer XML-Index das Aufteilen des XML-BLOBs zur Laufzeit, er bietet jedoch möglicherweise nicht die optimale Leistung für Abfragen, die auf path-Ausdrücken basieren. Da alle Zeilen im primären XML-Index, die einem XML-BLOB entsprechen, sequenziell nach großen XML-Instanzen durchsucht werden, kann diese sequenzielle Suche langsam sein. In diesem Fall kann ein sekundärer Index, der auf den Pfad- und Knotenwerten im primären Index aufbaut, die Indexsuche erheblich beschleunigen. Im sekundären PATH-Index sind die path- und node-Werte Schlüsselspalten, die effizientere Suchläufe beim Suchen nach Pfaden ermöglichen. Der Abfrageoptimierer kann den PATH-Index für Ausdrücke wie die im folgenden Beispiel gezeigten verwenden:

  • /root/Location ; gibt nur den Pfad an.

oder

  • /root/Location/@LocationID[.="10"] ; gibt den Pfad- und Knotenwert an.

Die folgende Abfrage zeigt, in welchen Fällen der PATH-Index hilfreich ist:

WITH XMLNAMESPACES ('https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS "PD")  
  
SELECT CatalogDescription.query('  
  /PD:ProductDescription/PD:Summary  
') AS Result  
FROM Production.ProductModel  
WHERE CatalogDescription.exist ('/PD:ProductDescription/@ProductModelID[.="19"]') = 1

In der Abfrage entsprechen der path-Ausdruck /PD:ProductDescription/@ProductModelID und der Wert "19" in der exist() -Methode den Schlüsselfeldern des PATH-Indexes. Auf diese Weise wird die direkte Suche im PATH-Index ermöglicht und eine bessere Suchleistung als bei einer sequenziellen Suche nach path-Werten im primären Index bereitgestellt.

Sekundärer XML VALUE-Index

Wenn Abfragen wertbasiert sind, z.B. /Root/ProductDescription/@*[. = "Mountain Bike"] oder //ProductDescription[@Name = "Mountain Bike"], und der Pfad nicht vollständig angegeben wird oder einen Platzhalter enthält, erzielen Sie möglicherweise schnellere Ergebnisse, indem Sie einen sekundären XML-Index erstellen, der auf Knotenwerten im primären XML-Index basiert.

Die Schlüsselspalten des VALUE-Indexes sind der Knotenwert und der Pfad des primären XML-Indexes. Wenn die Arbeitslast das Abfragen von Werten aus XML-Instanzen umfasst, ohne dass die Element- oder Attributnamen bekannt sind, die die Werte enthalten, kann der VALUE-Index hilfreich sein. Der folgende Ausdruck profitiert z. B. vom Vorhandensein eines VALUE-Indexes:

  • //author[LastName="someName"] wenn Sie den Wert des <LastName> Elements kennen, aber das <author> übergeordnete Element kann überall auftreten.

  • /book[@* = "someValue"] wobei die Abfrage nach dem <book> Element sucht, das über ein Attribut mit dem Wert "someValue"verfügt.

Die folgende Abfrage gibt ContactID aus der Contact -Tabelle zurück. Die WHERE -Klausel gibt einen Filter an, der nach Werten in der AdditionalContactInfo``xml Typspalte sucht. Die Kontakt-IDs werden nur zurückgegeben, wenn der entsprechende XML-BLOB mit den zusätzlichen Kontaktinformationen eine bestimmte Rufnummer enthält. Da das <telephoneNumber> Element an einer beliebigen Stelle im XML-Code angezeigt werden kann, gibt der Pfadausdruck die absteigende Oder-Selbst-Achse an.

WITH XMLNAMESPACES (  
  'https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo' AS CI,  
  'https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes' AS ACT)  
  
SELECT ContactID   
FROM   Person.Contact  
WHERE  AdditionalContactInfo.exist('//ACT:telephoneNumber/ACT:number[.="111-111-1111"]') = 1

In diesem Fall ist der Suchwert für <number> bekannt, kann aber an einer beliebigen Stelle im XML-instance als untergeordnetes Element des <telephoneNumber> Elements angezeigt werden. Diese Art der Abfrage kann möglicherweise von einer Indexsuche profitieren, die auf einem bestimmten Wert basiert.

Sekundärer PROPERTY-Index

Abfragen, die einen oder mehrere Werte aus einzelnen XML-Instanzen abrufen, können möglicherweise von einem PROPERTY-Index profitieren. Dieses Szenario tritt auf, wenn Sie Objekteigenschaften mithilfe der value() -Methode des xml Typs abrufen und wenn der Primärschlüsselwert des Objekts bekannt ist.

Der PROPERTY-Index basiert auf Spalten (PK, Pfad- und Knotenwert) des primären XML-Indexes, wobei PK der Primärschlüssel der Basistabelle ist.

Die folgende Abfrage ruft z. B. für Produktmodell 19die folgenden ProductModelID - und ProductModelName -Attributwerte mithilfe der value() -Methode ab. Der PROPERTY-Index kann eine schnellere Ausführung als die Verwendung des primären XML-Indexes oder der anderen sekundären XML-Indizes bereitstellen.

WITH XMLNAMESPACES ('https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS "PD")  
  
SELECT CatalogDescription.value('(/PD:ProductDescription/@ProductModelID)[1]', 'int') as ModelID,  
       CatalogDescription.value('(/PD:ProductDescription/@ProductModelName)[1]', 'varchar(30)') as ModelName          
FROM Production.ProductModel     
WHERE ProductModelID = 19

Mit Ausnahme der weiter unten in diesem Thema beschriebenen Unterschiede ähnelt das Erstellen eines XML-Index für einexml Typspalte dem Erstellen eines Indexes für eine Spalte ohnexml Typ. Die folgenden Transact-SQL-DDL-Anweisungen können zum Erstellen und Verwalten von XML-Indizes verwendet werden:

Abrufen von Informationen zu XML-Indizes

XML-Indexeinträge erscheinen in der Katalogsicht sys.indexes, wobei der Index den "Typ" 3 besitzt Die Namensspalte enthält den Namen des XML-Index.

XML-Indizes sind auch in der Katalogsicht sys.xml_indexes aufgezeichnet. Diese enthält alle Spalten von sys.indexes und einige spezielle Spalten, die für XML-Indizes nützlich sind. Der Wert NULL in der Spalte secondary_type zeigt einen primären XML-Index an; die Werte 'P', 'R' und 'V' stehen für sekundäre PATH-, PROPERTY- bzw. VALUE-XML-Indizes.

Der von XML-Indizes verwendete Speicherplatz kann in der Tabellenwertfunktion sys.dm_db_index_physical_statsgefunden werden. Sie stellt für alle Indextypen Informationen bereit, wie z. B. zur Anzahl der belegten Datenträgerseiten, zur durchschnittlichen Zeilengröße in Byte und zur Anzahl der Datensätze. Dieses schließt auch XML-Indizes ein. Diese Informationen sind für jede Datenbankpartition verfügbar. XML-Indizes verwenden das Partitionierungsschema und die Partitionierungsfunktion der Basistabelle.

Weitere Informationen

sys.dm_db_index_physical_stats (Transact-SQL)
XML-Daten (SQL Server)