Index XML (SQL Server)

S’applique à : SQL Server Azure SQL Database Azure SQL Managed Instance

Des index XML peuvent être créés sur des colonnes de type xml . L'indexation porte sur les balises, les valeurs et les chemins d'accès rencontrés dans les instances XML de la colonne et contribue à l'optimisation des performances des requêtes. Votre application peut bénéficier d'un index XML dans les situations suivantes :

• Les requêtes portant sur des colonnes XML sont fréquentes dans votre charge de travail. Le coût de la maintenance des index XML au cours de la modification des données doit être pris en compte lors de l'évaluation des avantages.

• Vos valeurs XML sont relativement grandes et les parties récupérées relativement petites. En créant un index, vous n'avez plus à analyser l'ensemble des données lors de l'exécution et pouvez profiter de la recherche d'index pour accélérer le traitement des requêtes.

À compter de SQL Server 2022 (16.x) et versions ultérieures, et dans Azure SQL Database et Azure SQL Managed Instance, vous pouvez utiliser la compression XML pour compresser les données XML hors ligne pour les colonnes et les index XML. La compression XML réduit les besoins en capacité de stockage de données.

Les index XML sont classés en plusieurs catégories :

• Index XML primaires
• Index XML secondaires

Le premier index portant sur la colonne de type xml est obligatoirement l'index XML primaire. Par le biais de l'index XML primaire, les trois types d'index secondaires suivants sont pris en charge : PATH, VALUE, and PROPERTY. Selon le type de requêtes, ces index secondaires peuvent contribuer à améliorer les performances liées à l'exécution de requêtes.

Les instances XML sont stockées dans les colonnes de type xml sous forme de BLOB (Binary Large Objects, objets volumineux binaires). Ces instances XML peuvent donc être volumineuses et la représentation binaire stockée d'instances de type xml peut atteindre jusqu'à 2 Go. Sans index, ces objets sont fragmentés au moment de l'exécution du programme afin d'évaluer une requête, ce qui peut prendre du temps. Par exemple, considérez la requête suivante :

;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;

Pour pouvoir sélectionner les instances XML satisfaisant la condition stipulée dans la clause WHERE , le BLOB XML se trouvant dans chaque ligne de la table Production.ProductModel est fragmenté au moment de l'exécution. L'expression (/PD:ProductDescription/@ProductModelID[.="19"]) tirée de la méthode exist() est ensuite évaluée. Une telle fragmentation à l'exécution peut être coûteuse selon la taille et le nombre d'instances stockées dans la colonne.

Si l’exécution de requêtes sur des BLOB est courante dans l’environnement de votre application, cette méthodologie permet d’indexer les colonnes de type xml . En contrepartie, le coût associé à la gestion de l'index lors de la modification des données est également à prendre en compte.

Index XML primaires

L'index XML primaire indexe toutes les balises, valeurs et chemins d'accès rencontrés dans les instances XML d'une colonne XML. Pour créer un index XML primaire, la table contenant la colonne XML doit posséder un index cluster portant sur la clé primaire de la table. SQL Server utilise cette clé primaire pour corréler les lignes de l'index XML primaire avec des lignes dans la table qui contient la colonne XML.

L'index XML primaire correspond à une représentation fragmentée et persistante des objets blob XML inclus dans la colonne des données de type xml . Pour chacun de ces objets blob XML de la colonne, l'index crée plusieurs lignes de données. Le nombre de lignes dans l'index est presque égal au nombre de nœuds se trouvant dans l'objet blob XML. Lorsqu'une requête extrait l'intégralité de l'instance XML, SQL Server fournit l'instance à partir de la colonne XML. Les requêtes dans des instances XML utilisent l'index XML primaire et peuvent renvoyer des valeurs scalaires ou des sous-arborescences XML en se servant de l'index lui-même.

Chaque ligne stocke les informations suivantes relatives aux nœuds :

• Le nom de la balise tel que le nom d'un élément ou d'un attribut.

• La valeur du nœud.

• Le type de nœud, par exemple un nœud élément, un nœud attribut ou un nœud texte.

• Les informations sur l'ordre du document, représentées par un identifiant de nœud interne.

• Le chemin d'accès de chacun des nœuds vers la racine de l'arborescence XML. Cette colonne fait l'objet de recherches pour y trouver la présence d'expressions de chemin d'accès mentionnées dans la requête.

• La clé primaire de la table de base. Cette clé est copiée dans l'index XML primaire afin de pouvoir effectuer une jointure en retour avec la table de base et le nombre maximal de colonnes dans la clé primaire de la table de base est limité à 15.

Les informations de ce nœud sont utilisées afin d'évaluer et d'élaborer les résultats sous forme de données XML découlant d'une requête donnée. Pour des raisons d'optimisation, les informations relatives au nom de la balise et au type de nœud sont encodées sous forme de valeurs entières et la colonne Path s'appuie sur ce même encodage. En outre, les chemins d'accès sont stockés dans l'ordre inverse afin de pouvoir faire correspondre les chemins d'accès où seul le suffixe est connu. Par exemple :

• //ContactRecord/PhoneNumber , où seuls les deux derniers niveaux sont connus ;

OR

• /Book/*/Title où le caractère générique * est mentionné au milieu de l’expression.

Le processeur de requêtes utilise l'index XML primaire dans le cas de requêtes mettant en œuvre des xml Data Type Methods et renvoie les valeurs scalaires ou les sous-arborescences XML tirées de l'index primaire lui-même (cet index stocke toutes les informations nécessaires afin de reconstruire l'instance XML).

Par exemple, la requête suivante renvoie les informations sommaires stockées dans la colonne CatalogDescription de type xml et provenant de la table ProductModel. La requête ne renvoie les informations <Summary> que pour les modèles de produits dont la description de catalogue stocke également la description <Features>.

;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

Concernant l'index XML primaire, au lieu de fragmenter chaque instance d'objet blob XML se trouvant dans la table de base, les lignes de l'index correspondant à chaque objet blob XML sont soumises à des recherches séquentielles pour retrouver l'expression indiquée dans la méthode exist(). Si le chemin d'accès est retrouvé dans la colonne Chemin d’accès de l'index, l'élément <Summary> ainsi que ses sous-arborescences sont extraits de l'index XML primaire, puis convertis en objet blob XML suite à l'exécution de la méthode query().

L'index XML primaire n'est pas sollicité lors de la récupération d'une instance XML complète. Par exemple, la requête suivante extrait de la table l'instance XML tout entière décrivant les instructions de fabrication d'un modèle de produit donné.

USE AdventureWorks2022;

SELECT Instructions
FROM   Production.ProductModel
WHERE  ProductModelID = 7;

Index XML secondaires

Afin d'améliorer les performances lors des recherches, vous pouvez créer des index XML secondaires. Un index XML primaire doit être défini au préalable avant de pouvoir créer des index secondaires. Il en existe trois types différents :

• les index XML secondaires de type PATH (s'appuyant sur le chemin d'accès) ;

• les index XML secondaires de type VALUE (utilisant la valeur comme critère de recherche) ;

• les index XML secondaires de type PROPERTY (pour rechercher des données d'après leurs propriétés).

Voici quelques consignes pour vous aider à créer un ou plusieurs index secondaires :

• Si votre charge de travail utilise souvent des expressions de chemin sur les colonnes XML, l'index secondaire PATH a toutes les chances de l'accélérer. C’est ce que vous pouvez constater le plus souvent en cas d’utilisation de la méthode exist() sur des colonnes XML dans la clause WHERE de Transact-SQL.

• Si votre charge de travail récupère plusieurs valeurs d'instances XML individuelles en utilisant des expressions de chemin, le clustering des chemins pour chaque instance XML dans l'index PROPERTY peut s'avérer fort utile. Ce scénario se produit généralement avec un sac de propriétés où les propriétés d'un objet sont récupérées et la valeur de sa clé primaire est connue.

• Si votre charge de travail implique le lancement de requêtes sur des valeurs d'instances XML pour lesquelles vous ne connaissez pas les noms d'élément ou d'attribut, vous aurez peut-être intérêt à créer l'index VALUE. C’est généralement ce qui se produit en cas de recherches d’axes descendants telles que //author[last-name="Howard"], où les éléments <author> peuvent se trouver à tout niveau de la hiérarchie. Cela se rencontre aussi dans les requêtes basées sur des caractères génériques telles que /book [@* = "novel"], où la requête recherche les éléments <book> ayant des attributs avec la valeur "novel".

les index XML secondaires de type PATH (s'appuyant sur le chemin d'accès) ;

Si vos requêtes précisent habituellement les expressions de chemin d'accès sur les colonnes de type xml , un index secondaire de type PATH peut s'avérer à même d'accélérer la recherche. Comme nous l’avons vu précédemment dans cet article, l’index primaire est des plus utiles pour les requêtes indiquant la méthode exist() dans leur clause WHERE. Si vous ajoutez à présent un index secondaire de type PATH, il se peut que vous amélioriez encore la rapidité de la recherche lancée par de telles requêtes.

Bien qu'un index XML primaire évite de fragmenter les objets blob XML à l'exécution, il peut ne pas offrir les meilleurs temps de réponse pour les requêtes s'appuyant sur des expressions de chemin d'accès. Une recherche, lancée sur toutes les lignes constituant l'index XML primaire correspondant à un objet blob XML et s'opérant de façon séquentielle pour des instances XML volumineuses, peut s'avérer des plus lentes. Dans ce cas, un index secondaire construit sur les valeurs de chemin d'accès et sur celles des nœuds de l'index primaire peut accélérer de façon significative la recherche sur l'index. Dans le cas d'un index XML secondaire de type PATH, les valeurs de chemin d'accès et des nœuds correspondent à des colonnes clés permettant donc des recherches plus efficaces si ces dernières portent sur le chemin d'accès. Il se peut que l'optimiseur de requête utilise l'index de type PATH dans des expressions telles que celles mentionnées ci-dessous :

• /root/Location , n'indiquant que son chemin d'accès ;

OR

• /root/Location/@LocationID[.="10"] , où le chemin et la valeur du nœud sont précisés.

La requête suivante illustre un cas de figure où l'index de type PATH s'avère particulièrement utile :

;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;

Dans la requête, l'expression de chemin d'accès /PD:ProductDescription/@ProductModelID et sa valeur "19" se trouvant dans la méthode exist() correspondent aux champs de clé définis pour l'index de type PATH. Des recherches directes portant sur l'index PATH peuvent s'effectuer et offrir ainsi de meilleures performances que dans le cas d'une recherche séquentielle portant sur les valeurs de chemin d'accès de l'index primaire.

les index XML secondaires de type VALUE (utilisant la valeur comme critère de recherche) ;

Si des requêtes s'appuient sur les valeurs, par exemple /Root/ProductDescription/@*[. = "Mountain Bike"] ou //ProductDescription[@Name = "Mountain Bike"] et que le chemin n'est pas entièrement précisé ou qu'il contient un caractère générique, vous pourriez obtenir des résultats plus rapidement en construisant un index XML secondaire sur les valeurs des nœuds de l'index XML primaire.

Les colonnes clés (correspondant aux valeurs de nœud et aux chemins d'accès) de l'index de type VALUE sont tirées de l'index XML primaire. Si vous devez lancer des requêtes sur des valeurs provenant d'instances XML sans connaître le nom des éléments ou des attributs contenant les valeurs, il se peut que l'index de type VALUE vous soit particulièrement utile. Par exemple, l'expression suivante tire parti de l'utilisation d'un index de type VALUE :

• //author[LastName="someName"] où vous connaissez la valeur de l'élément <LastName>, mais où le parent <author> peut survenir n'importe où.

• /book[@* = "someValue"] où la requête recherche l'élément <book> dont un attribut possède la valeur "someValue".

La requête suivante renvoie ContactID à partir de la table Contact . La clause WHERE indique un filtre permettant de rechercher des valeurs dans la colonne AdditionalContactInfo de type xml. L'ID des contacts n'est renvoyé que si l'objet blob XML relatif aux informations complémentaires sur les contacts correspondants contient un numéro de téléphone spécifique. Puisque l'élément telephoneNumber peut apparaître n'importe où dans le document XML, l'expression du chemin d'accès indique l'axe descendant-or-self.

;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;

Dans ce cas, nous connaissons donc la valeur de recherche correspondant à <number> mais elle peut se trouver n'importe où dans l'instance XML en tant qu'enfant de l'élément telephoneNumber. Ce type de requête pourrait être plus efficace grâce à la recherche d'une valeur précise dans les index.

Index secondaire PROPERTY

Les requêtes chargées d'extraire plusieurs valeurs d'instances XML distinctes peuvent bénéficier de l'utilisation d'un index de type PROPERTY. Un scénario type de cette utilisation s’illustre quand vous récupérez les propriétés d’objets par le biais de la méthode value() de type xml et que la valeur de la clé primaire de l’objet en question est connue.

L'index PROPERTY se construit sur des colonnes (PK, Chemin d’accès et valeur du nœud) issues de l'index XML primaire, où PK correspond à la clé primaire de la table de base.

Par exemple, concernant le modèle de produit 19, la requête suivante extrait les valeurs des attributs ProductModelID et ProductModelName grâce à la méthode value() . Contrairement aux requêtes utilisant les index XML primaires ou tout autre index XML secondaire, l'index PROPERTY peut permettre une exécution plus rapide.

;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;

À part les différences décrites plus loin dans cette rubrique, la création d’un index XML portant sur une colonne de typexml est semblable à celle d’un index portant sur une colonne de type nonxml. Les instructions DDL Transact-SQL suivantes permettent de créer et de gérer les index XML :

• CREATE INDEX (Transact-SQL)
• ALTER INDEX (Transact-SQL)
• DROP INDEX (Transact-SQL)

Compression XML

S'applique à : SQL Server 2022 (16.x) et versions ultérieures, base de données Azure SQL, Azure SQL Managed Instance

L’activation de la compression XML modifie le format de stockage physique des données associées au type de données XML au format binaire compressé, mais ne modifie pas la syntaxe ni la sémantique des données XML. Aucune modification de l'application n'est requise lorsqu'une ou plusieurs tables sont activées pour la compression XML.

Seul le type de données XML est affecté par la compression XML. Les données XML sont compressées avec l’algorithme de compression Xpress. Tous les index XML existants sont compressés à l’aide de la compression des données. La compression des données est activée en interne pour les index XML lorsque la compression XML est activée.

La compression XML peut être activée côte à côte avec la compression de données sur les mêmes tables.

Les index XML n'héritent pas de la propriété de compression de la table. Pour compresser des index, vous devez activer explicitement la compression XML sur les index XML.

Les index XML secondaires n’héritent pas de la propriété de compression de l’index XML principal.

Par défaut, le paramètre de compression XML des index XML est défini sur OFF quand l’index est créé.

Obtention d'informations sur les index XML

Les entrées d'index XML apparaissent dans l'affichage catalogue sys.indexes, avec l’index type de 3. La colonne name contient le nom de l'index XML.

Les index XML sont également enregistrés dans l'affichage catalogue sys.xml_indexes. Il contient toutes les colonnes de sys.indexes, ainsi que d'autres plus spécifiques et utiles aux index XML. La valeur NULL de la colonne secondary_type indique qu'il s'agit d'un index XML primaire ; les valeurs P, R et V représentent respectivement les index XML secondaires PATH, PROPERTY et VALUE.

L’espace utilisé par les index XML figure dans la fonction table sys.dm_db_index_physical_stats, Il fournit des informations telles que le nombre de pages de données occupées, la taille moyenne des lignes en octets et le nombre d'enregistrements, pour tous les types d'index. Vous y trouvez également les index XML. Ces informations sont disponibles pour chaque partition de la base de données. Les index XML utilisent le même schéma et la même fonction de partitionnement que la table de base.

Étapes suivantes

• Créer des index XML
• ALTER INDEX (index XML sélectifs)
• sys.dm_db_index_physical_stats (Transact-SQL)
• Données XML (SQL Server)