Index XML (SQL Server)

Les index XML peuvent être créés sur des colonnes de type de données xml. Ils indexent toutes les balises, valeurs et chemins sur les instances XML dans la colonne et bénéficient des performances des requêtes. Votre application peut tirer parti d’un index XML dans les situations suivantes :

• Les requêtes sur les colonnes XML sont courantes dans votre charge de travail. Le coût de maintenance des index XML pendant la modification des données doit être pris en compte.

• Vos valeurs XML sont relativement volumineuses et les parties récupérées sont relativement petites. La création de l’index évite d’analyser l’ensemble des données au moment de l’exécution et bénéficie des recherches d’index pour un traitement efficace des requêtes.

Les index XML appartiennent aux catégories suivantes :

• Index XML principal

• Index XML secondaire

Le premier index de la xml colonne de type doit être l’index XML principal. À l’aide de l’index XML principal, les types d’index secondaires suivants sont pris en charge : PATH, VALUE et PROPERTY. En fonction du type de requêtes, ces index secondaires peuvent contribuer à améliorer les performances des requêtes.

Remarque

Vous ne pouvez pas créer ou modifier un index XML, sauf si les options de base de données sont correctement définies pour l’utilisation du xml type de données. Pour plus d’informations, consultez Utiliser Full-Text recherche avec des colonnes XML.

Les instances XML sont stockées dans des colonnes de type xml sous forme de grands objets binaires (BLOBs). Ces instances XML peuvent être volumineuses et la représentation binaire stockée des instances de type de xml données peut atteindre 2 Go. Sans index, ces objets volumineux binaires sont déchiquetés au moment de l’exécution pour évaluer une requête. Ce déchiquetage peut être chronophage. 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 sélectionner les instances XML qui répondent à la condition dans la clause, l’objet blob (Binary Binary Large Object) XML dans chaque ligne de table Production.ProductModel est déchiqueté au moment de l’exécutionWHERE. Ensuite, l’expression (/PD:ProductDescription/@ProductModelID[.="19"]) dans la exist() méthode est évaluée. Ce déchiquetage pendant l’exécution peut être coûteux, selon la taille et le nombre d’instances stockées dans la colonne.

Si l’interrogation d’objets binaires de grande taille XML (BLOBs) est courante dans votre environnement d’application, il est utile d’indexer les colonnes de type xml. Toutefois, il existe un coût associé à la maintenance de l’index pendant la modification des données.

Index XML principal

L’index XML principal indexe toutes les balises, valeurs et chemins d’accès dans les instances XML d’une colonne XML. Pour créer un index XML principal, la table dans laquelle la colonne XML se produit doit avoir un index cluster sur la clé primaire de la table. SQL Server utilise cette clé primaire pour mettre en corrélation les lignes de l’index XML principal avec les lignes de la table qui contient la colonne XML.

L’index XML principal est une représentation déchiquetée et persistante des BLOB XML dans la colonne de type de données xml. Pour chaque objet blob binaire XML dans la colonne, l’index crée plusieurs lignes de données. Le nombre de lignes de l’index est approximativement égal au nombre de nœuds dans l’objet volumineux binaire XML. Lorsqu’une requête récupère l’instance XML complète, SQL Server fournit l’instance à partir de la colonne XML. Les requêtes au sein d’instances XML utilisent l’index XML principal et peuvent retourner des valeurs scalaires ou des sous-arborescences XML à l’aide de l’index lui-même.

Chaque ligne stocke les informations de nœud suivantes :

• Nom de balise tel qu’un élément ou un nom d’attribut.

• Valeur du nœud.

• Type de nœud tel qu’un nœud d’élément, un nœud d’attribut ou un nœud de texte.

• Informations sur l’ordre des documents, représentées par un identificateur de nœud interne.

• Chemin d’accès de chaque nœud à la racine de l’arborescence XML. Cette colonne est recherchée pour des expressions de chemin d’accès dans la requête.

• Clé primaire de la table de base. La clé primaire de la table de base est dupliquée dans l’index XML principal pour une jointure back-end avec la table de base, et le nombre maximal de colonnes dans la clé primaire de la table de base est limité à 15.

Ces informations de nœud sont utilisées pour évaluer et construire des résultats XML pour une requête spécifiée. À des fins d’optimisation, le nom de balise et les informations de type de nœud sont encodés en tant que valeurs entières, et la colonne Path utilise le même encodage. De plus, les chemins d’accès sont stockés dans l’ordre inverse pour permettre de faire correspondre les chemins lorsque seul le suffixe de chemin d’accès est connu. Par exemple:

• //ContactRecord/PhoneNumber où seules les deux dernières étapes sont connues

OU

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

Le processeur de requêtes utilise l’index XML principal pour les requêtes qui impliquent des méthodes de type de données xml et retourne des valeurs scalaires ou des sous-arborescences XML de l’index principal lui-même. (Cet index stocke toutes les informations nécessaires pour reconstruire l’instance XML.)

Par exemple, la requête suivante retourne des informations récapitulatives stockées dans la colonne de CatalogDescription``xml type de la ProductModel table. La requête retourne <Summary> des informations uniquement pour les modèles de produit dont la description du catalogue stocke également la <Features> description.

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  

En ce qui concerne l’index XML principal, au lieu de déchiqueter chaque instance d’objet volumineux XML dans la table de base, les lignes de l’index qui correspondent à chaque objet volumineux binaire XML sont recherchées séquentiellement pour l’expression spécifiée dans la exist() méthode. Si le chemin d’accès se trouve dans la colonne Path de l’index, l’élément <Summary>, ainsi que ses sous-arbres, est récupéré à partir de l’index XML principal et converti en un objet binaire volumineux XML en résultat de la méthode query().

Notez que l’index XML principal n’est pas utilisé lors de la récupération d’une instance XML complète. Par exemple, la requête suivante récupère à partir de la table l’instance XML entière qui décrit les instructions de fabrication d’un modèle de produit spécifique.

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

Index XML secondaires

Pour améliorer les performances de recherche, vous pouvez créer des index XML secondaires. Un index XML principal doit d’abord exister avant de pouvoir créer des index secondaires. Voici les types suivants :

• Index XML secondaire PATH

• INDEX XML SECONDAIRE DE VALEUR

• PROPRIÉTÉ de l'index XML secondaire

Voici quelques instructions pour créer un ou plusieurs index secondaires :

• Si votre charge de travail utilise de manière significative des expressions de chemin d'accès sur les colonnes XML, l’index XML secondaire PATH est susceptible d’accélérer votre charge de travail. Le cas le plus courant est l’utilisation de la méthode exist() sur les colonnes XML dans la clause WHERE de Transact-SQL.

• Si votre charge de travail récupère plusieurs valeurs à partir d’instances XML individuelles à l’aide d’expressions de chemin d’accès, le clustering de chemins d’accès au sein de chaque instance XML de l’index PROPERTY peut être utile. Ce scénario se produit généralement dans un scénario de conteneur de propriétés lorsque les propriétés d’un objet sont extraites et que sa valeur de clé primaire est connue.

• Si votre charge de travail implique l’interrogation de valeurs dans des instances XML sans connaître les noms d’éléments ou d’attributs qui contiennent ces valeurs, vous pouvez créer l’index VALUE. Cela se produit généralement avec des recherches d’axes descendants, telles que //author[last-name="Howard"], où <les éléments de l’auteur> peuvent se produire à n’importe quel niveau de la hiérarchie. Il se produit également dans des requêtes génériques, telles que /book [@* = « novel"], où la requête recherche <des éléments de livre> qui ont un attribut ayant la valeur « roman ».

Index XML secondaire PATH

Si vos requêtes spécifient généralement des expressions de chemin d’accès sur des colonnes de type xml, un index secondaire PATH peut être en mesure d’optimiser la recherche. Comme décrit précédemment dans cette rubrique, l’index principal est utile lorsque vous avez des requêtes qui spécifient exist() méthode dans la clause WHERE. Si vous ajoutez un index secondaire PATH, vous pouvez également améliorer les performances de recherche dans ces requêtes.

Bien qu’un index XML principal évite d’avoir à déchiqueter les objets volumineux binaires XML au moment de l’exécution, il peut ne pas fournir les meilleures performances pour les requêtes en fonction des expressions de chemin d’accès. Étant donné que toutes les lignes de l’index XML principal correspondant à un objet binaire binaire XML sont recherchées de manière séquentielle pour les grandes instances XML, la recherche séquentielle peut être lente. Dans ce cas, le fait d’avoir un index secondaire basé sur les valeurs de chemin d’accès et les valeurs de nœud dans l’index principal peut accélérer considérablement la recherche d’index. Dans l’index secondaire PATH, les valeurs de chemin d’accès et de nœud sont des colonnes clés qui permettent une recherche plus efficace lors de la recherche de chemins d’accès. L’optimiseur de requête peut utiliser l’index PATH pour les expressions telles que celles indiquées dans les éléments suivants :

• /root/Location qui spécifient uniquement un chemin d’accès

OU

• /root/Location/@LocationID[.="10"] où le chemin d’accès et la valeur du nœud sont spécifiés.

La requête suivante montre où l’index PATH est 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 /PD:ProductDescription/@ProductModelID de chemin d’accès et la valeur "19" de la exist() méthode correspondent aux champs clés de l’index PATH. Cela permet une recherche directe dans l’index PATH et offre de meilleures performances de recherche que la recherche séquentielle des valeurs de chemin d’accès dans l’index principal.

INDEX XML SECONDAIRE VALUE

Si les requêtes sont basées sur des valeurs, par exemple /Root/ProductDescription/@*[. = "Mountain Bike"] ou //ProductDescription[@Name = "Mountain Bike"], et que le chemin d’accès n’est pas entièrement spécifié ou qu’il inclut un caractère générique, vous pouvez obtenir des résultats plus rapides en créant un index XML secondaire basé sur des valeurs de nœud dans l’index XML principal.

Les colonnes clés de l’index VALUE sont (valeur de nœud et chemin) de l’index XML principal. Si votre charge de travail implique d’interroger des valeurs à partir d’instances XML sans connaître les noms d’éléments ou d’attributs qui contiennent les valeurs, un index VALUE peut être utile. Par exemple, l’expression suivante bénéficie d’un index VALUE :

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

• /book[@* = "someValue"]où la requête recherche l’élément <>bookqui a un attribut ayant la valeur "someValue".

La requête suivante retourne ContactID à partir de la Contact table. La WHERE clause spécifie un filtre qui recherche des valeurs dans la colonne de AdditionalContactInfo``xml type. Les ID de contact sont retournés uniquement si l'objet binaire volumineux XML des informations de contact supplémentaires inclut un numéro de téléphone spécifique. Étant donné que l'élément <telephoneNumber> peut apparaître n'importe où dans le code XML, l'expression de chemin d'accès spécifie l'axe descendant-ou-soi.

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, la valeur de recherche pour <number> est connue, mais elle peut apparaître n’importe où dans l’instance XML en tant qu’enfant de l’élément <telephoneNumber>. Ce type de requête peut tirer parti d’une recherche d’index basée sur une valeur spécifique.

PROPRIÉTÉ INDEX SECONDAIRE

Les requêtes qui récupèrent une ou plusieurs valeurs à partir d’instances XML individuelles peuvent tirer parti d’un index PROPERTY. Ce scénario se produit lorsque vous récupérez des propriétés d’objet à l’aide de la méthode value() du xml type et lorsque la valeur de clé primaire de l’objet est connue.

L’index PROPERTY est basé sur des colonnes (PK, Path et valeur de nœud) de l’index XML principal où PK est la clé primaire de la table de base.

Par exemple, pour le modèle de produit 19, la requête suivante récupère les valeurs des attributs ProductModelID et ProductModelName à l'aide de la méthode value(). Au lieu d’utiliser l’index XML principal ou les autres index XML secondaires, l’index PROPERTY peut fournir 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  

À l’exception des différences décrites plus loin dans cette rubrique, la création d’un index XML sur unexml colonne de type est similaire à la création d’un index sur une colonne non dexml type. Les instructions DDL suivantes Transact-SQL peuvent être utilisées pour créer et gérer des index XML :

• CRÉER INDEX (Transact-SQL)

• ALTER INDEX (Transact-SQL)

• DROP INDEX (Transact-SQL)

Obtention d’informations sur les index XML

Les entrées d’index XML apparaissent dans la vue catalogue, sys.indexes, avec l’index « type » 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. Cela contient toutes les colonnes de sys.index et certaines colonnes spécifiques qui sont utiles pour les index XML. La valeur NULL dans la colonne, secondary_type, indique un index XML principal ; les valeurs « P », « R » et « V » sont respectivement des index XML secondaires PATH, PROPERTY et VALUE.

L’utilisation de l’espace des index XML se trouve dans la fonction table sys.dm_db_index_physical_stats. Il fournit des informations, telles que le nombre de pages de disque occupées, la taille moyenne des lignes en octets et le nombre d’enregistrements, pour tous les types d’index. Cela inclut également des index XML. Ces informations sont disponibles pour chaque partition de base de données. Les index XML utilisent le même schéma de partitionnement et la même fonction de partitionnement de la table de base.

Voir aussi

sys.dm_db_index_physical_stats (Transact-SQL)
Données XML (SQL Server)