XML-Indizes (SQL Server)

Artikel
08/04/2023

Gilt für:SQL Server Azure SQL-Datenbank Azure SQL Managed Instance

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.

Ab SQL Server 2022 (16.x) und höheren Versionen und in Azure SQL-Datenbank und in der verwalteten Azure SQL-Instanz können Sie XML-Komprimierung verwenden, um XML-Daten aus Zeilen für XML-Spalten und Indizes zu komprimieren. Die XML-Komprimierung reduziert die Anforderungen an die Datenspeicherung.

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-Indexes 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 2GB groß sein. Ohne Index werden diese BLOBs zur Laufzeit aufgeteilt, um eine Abfrage auszuwerten. Diese Aufteilung kann zeitaufwändig sein. Betrachten Sie beispielsweise die folgende Abfrage:

;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. Die Indexverwaltung 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, um Zeilen im primären XML-Index mit Zeilen in der Tabelle zu korrelieren, die die XML-Spalte enthalten.

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.

/Book/*/Title, wobei das Platzhalterzeichen (*) in der Mitte des Ausdrucks angegeben wird.

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 z.B. in der CatalogDescription-Spalte vom Typ xml gespeicherte Zusammenfassungsinformationen aus der ProductModel-Tabelle zurück. Die Abfrage gibt <Summary>-Informationen nur für Produktmodelle zurück, deren Katalogbeschreibung ebenfalls 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 Result
FROM Production.ProductModel
WHERE CatalogDescription.exist ('/PD:ProductDescription/PD:Features') = 1

Was den primären XML-Index betrifft, so wird nicht jede XML-Blobinstanz (Binary Large Object) in der Basistabelle zerlegt. Vielmehr werden die Zeilen im Index, die jedem XML-Blob entsprechen, sequenziell nach dem in der Methode exist() angegebenen Ausdruck durchsucht. Wenn der Pfad in der Pfadspalte 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 XML-Blob konvertiert.

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

USE AdventureWorks2022;

SELECT Instructions
FROM   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 die Verwendung 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. Diese Situation kann üblicherweise beim Durchsuchen von Nachfolgerachsen vorkommen, z. B. bei //author[last-name="Howard"], wobei die <author>-Elemente in jeder beliebigen Ebene der Hierarchie auftreten können. Sie kommt auch bei Abfragen mit Platzhaltern vor, z. B. bei /book [@* = "novel"], wobei die Abfrage nach <book>-Elementen sucht, die über ein beliebiges Attribut verfügen, das den Wert "novel" aufweist.

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 Artikel 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.

/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"], wobei der Wert des <LastName>-Elements bekannt ist, das übergeordnete <author>-Element jedoch an beliebiger Position auftreten kann.
/book[@* = "someValue"], wobei die Abfrage nach dem <book>-Element sucht, das ein Attribut mit dem Wert "someValue" aufweist.

Die folgende Abfrage gibt ContactID aus der Contact -Tabelle zurück. Die WHERE-Klausel gibt einen Filter an, der nach Werten in der AdditionalContactInfo-Spalte vom Typ xml 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 beliebiger Position in der XML auftreten kann, gibt der path-Ausdruck die descendant-or-self-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 dieser Situation ist der Suchwert für <number> bekannt, kann jedoch an beliebiger Position in der XML-Instanz 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 Artikel beschriebenen Unterschiede gleicht das Erstellen eines XML-Indexes für eine Spalte des Typs xml dem Erstellen eines Indexes für eine Spalte, die nicht den Typ xml aufweist. Die folgenden Transact-SQL-DDL-Anweisungen können zum Erstellen und Verwalten von XML-Indizes verwendet werden:

XML-Komprimierung

Gilt für: SQL Server 2022 (16.x) und höher, Azure SQL-Datenbank, Azure SQL Managed Instance

Die Aktivierung der XML-Komprimierung ändert das physische Speicherformat der Daten, die dem XML-Datentyp zugeordnet sind, in ein komprimiertes Binärformat, ändert aber nicht die Syntax oder Semantik der XML-Daten. Anwendungsänderungen sind nicht erforderlich, wenn eine oder mehrere Tabellen für die XML-Komprimierung aktiviert werden.

Nur der XML-Datentyp ist durch die XML-Komprimierung betroffen. XML-Daten werden mit dem Xpress-Komprimierungsalgorithmus komprimiert. Alle vorhandenen XML-Indizes werden mithilfe der Datenkomprimierung komprimiert. Die Datenkomprimierung wird intern für XML-Indizes aktiviert, wenn die XML-Komprimierung aktiviert ist.

Die XML-Komprimierung kann parallel zur Datenkomprimierung für dieselben Tabellen aktiviert werden.

XML-Indizes erben nicht die Komprimierungseigenschaft der Tabelle. Um Indizes zu komprimieren, müssen Sie die XML-Komprimierung in XML-Indizes explizit aktivieren.

Sekundäre XML-Indizes erben nicht die Komprimierungseigenschaft des primären XML-Indexes.

Standardmäßig wird die XML-Komprimierungseinstellung bei Erstellung eines Indexes auf OFF festgelegt.

Abrufen von Informationen zu XML-Indizes

XML-Indexeinträge werden in der Katalogansicht sys.indexes mit dem Indextyp (type) 3 angezeigt. Die Namensspalte enthält den Namen des XML-Index.

XML-Indizes werden auch in der Katalogsicht sys.xml_indexes aufgezeichnet. Diese enthält alle Spalten von sys.indexes und einige Sonderspalten, die für XML-Indizes nützlich sind. Der Wert NULL in der Spalte secondary_type steht für einen primären XML-Index. Die Werte P, R und V stehen jeweils für die sekundären XML-Indizes PATH, PROPERTY und VALUE.

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, z. B. die Anzahl von belegten Datenseiten, die durchschnittliche Zeilengröße in Byte und die Anzahl von Datensätzen. 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.