Indici XML (SQL Server)

Si applica a:SQL ServerDatabase SQL diAzure Istanza gestita di SQL di Azure

È possibile creare indici XML sulle colonne con tipo di dati xml . Tutti i tag, i valori e i percorsi delle istanze XML presenti nella colonna vengono indicizzati, migliorando le prestazioni delle query. Per le applicazioni in uso l'utilizzo di un indice XML può risultare vantaggioso nelle situazioni seguenti:

  • Le query sulle colonne XML sono frequenti nel carico di lavoro. È necessario tenere in considerazione il costo di manutenzione dell'indice XML durante la modifica dei dati.

  • I valori XML sono di grandi dimensioni mentre le parti recuperate sono relativamente piccole. Compilando un indice sarà possibile evitare l'analisi completa dei dati in fase di esecuzione ed eseguire ricerche basate sull'indice per una efficiente elaborazione delle query.

A partire da SQL Server 2022 (16.x) e versioni successive e nel database SQL di Azure e in Istanza gestita di SQL di Azure, è possibile usare la compressione XML per comprimere i dati XML all'esterno delle righe per colonne e indici XML. La compressione XML riduce i requisiti di capacità di archiviazione dei dati.

Gli indici XML rientrano nelle categorie seguenti:

  • Indice XML primario
  • Indice XML secondario

Il primo indice nella colonna di tipo xml deve essere l'indice XML primario, il cui utilizzo consente di supportare i tipi di indici secondari seguenti: PATH, VALUE e PROPERTY. In base al tipo di query, questi indici secondari possono facilitare il miglioramento delle prestazioni delle query.

Nota

Non è possibile creare o modificare un indice XML a meno che le opzioni del database siano correttamente impostate per l'uso del tipo di dati xml . Per altre informazioni, vedere Utilizzo della ricerca full-text con colonne XML.

Le istanze XML vengono archiviate nelle colonne di tipo xml come oggetti BLOB (Binary Large Object). Tali istanze XML possono essere di grandi dimensioni e la rappresentazione binaria archiviata delle istanze del tipo di dati xml può raggiungere dimensioni massime di 2 GB. Senza un indice, questi oggetti BLOB vengono suddivisi in fase di esecuzione per valutare una query. Questa suddivisione può richiedere molto tempo. Ad esempio, si consideri la query seguente:

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

Per selezionare le istanze XML che soddisfano la condizione della clausola WHERE , gli oggetti BLOB XML in ogni riga della tabella Production.ProductModel vengono suddivisi in fase di esecuzione. In seguito, viene valutata l'espressione (/PD:ProductDescription/@ProductModelID[.="19"]) nel metodo exist() . La suddivisione in fase di esecuzione può essere costosa, a seconda delle dimensioni e del numero di istanze archiviate nella colonna.

Se l'esecuzione di query sugli oggetti BLOB XML è un processo comune nell'ambiente di lavoro specifico, può facilitare l'indicizzazione delle colonne di tipo xml . Tuttavia, è previsto un costo associato alla gestione dell'indice durante la modifica dei dati.

Indice XML primario

L'indice XML primario consente di indicizzare tutti i tag, i valori e i percorsi contenuti nelle istanze XML di una colonna XML. Per creare un indice XML primario, è necessario che la tabella contenente la colonna XML da indicizzare includa un indice cluster nella chiave primaria. SQL Server usa questa chiave primaria per correlare le righe nell'indice XML primario con le righe della tabella che contiene la colonna XML.

L'indice XML primario è una rappresentazione suddivisa e persistente degli oggetti BLOB XML contenuti nella colonna con tipo di dati xml . Per ogni BLOB XML contenuto nella colonna, l'indice crea diverse righe di dati. Il numero di righe dell'indice corrisponde approssimativamente al numero di nodi del BLOB XML. Quando una query recupera l'istanza XML completa, SQL Server fornisce l'istanza dalla colonna XML. Le query all'interno delle istanze XML utilizzano l'indice XML primario e possono restituire valori scalari o sottoalberi XML utilizzando l'indice stesso.

In ogni riga vengono archiviate le informazioni seguenti:

  • Nome di tag, ad esempio un nome di elemento o di attributo.

  • Valore di nodo.

  • Tipo di nodo, ad esempio un nodo elemento, un nodo attributo o un nodo testo.

  • Informazioni sull'ordine dei dati nel documento, rappresentate da un identificatore di nodo interno.

  • Percorso da ogni nodo al nodo radice dell'albero XML. In questa colonna viene eseguita la ricerca delle espressioni di percorso della query.

  • Chiave primaria della tabella di base. La chiave primaria della tabella di base viene duplicata nell'indice XML primario per eseguire un join all'indietro con la tabella di base e il numero massimo di colonne nella chiave primaria della tabella di base è limitato a 15.

Le informazioni sul nodo vengono utilizzate per valutare e costruire i risultati XML di una query specificata. A scopo di ottimizzazione, il nome di tag e le informazioni sul tipo di nodo vengono codificati come valori integer e per la colonna Path viene utilizzata la stessa codifica. Inoltre, i percorsi vengono archiviati in ordine inverso per consentirne la corrispondenza quando è noto solo il relativo suffisso. Ad esempio:

  • //ContactRecord/PhoneNumber in cui sono noti solo gli ultimi due passaggi

OPPURE

  • /Book/*/Title dove il carattere * jolly viene specificato al centro dell'espressione.

Query Processor usa l'indice XML primario per le query che implicano metodi con tipo di dati XML e restituisce valori scalari o sottoalberi XML dall'indice primario stesso. In tale indice vengono archiviate tutte le informazioni necessarie per ricostruire l'istanza XML.

Ad esempio, la query seguente restituisce informazioni di riepilogo archiviate nella colonna di tipo CatalogDescriptionxml nella tabella ProductModel . La query restituisce <Summary> informazioni solo per i modelli di prodotto la cui descrizione del catalogo archivia anche la <Features> descrizione.

;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

Per quanto riguarda l'indice XML primario, anziché shredding di ogni istanza di oggetto binario binario di grandi dimensioni nella tabella di base, le righe nell'indice che corrispondono a ogni oggetto binario XML di grandi dimensioni vengono eseguite in sequenza per l'espressione specificata nel exist() metodo . Se il percorso viene trovato nella colonna Path nell'indice, l'elemento <Summary> insieme ai relativi sottoalberi viene recuperato dall'indice XML primario e convertito in un oggetto binario xml di grandi dimensioni come risultato del query() metodo .

L'indice XML primario non viene utilizzato durante il recupero di un'istanza XML completa. Ad esempio, la query seguente recupera dalla tabella l'intera istanza XML che descrive le istruzioni di produzione per un modello di prodotto specifico.

USE AdventureWorks2022;

SELECT Instructions
FROM   Production.ProductModel
WHERE  ProductModelID = 7;

Indici XML secondari

Per migliorare le prestazioni di ricerca, è possibile creare indici XML secondari. A tale scopo, deve esistere innanzitutto un indice XML primario, prima di poterne creare di secondari. Tipi di indici secondari:

  • Indice XML secondario PATH

  • Indice XML secondario VALUE

  • Indice XML secondario PROPERTY

Di seguito sono riportate alcune linee guida per la creazione di uno o più indici di questo tipo:

  • Se nel carico di lavoro viene fatto un utilizzo significativo di espressioni di percorso sulle colonne XML, per migliorare le prestazioni di lavoro è possibile utilizzare l'indice XML secondario PATH. Il caso più comune è l'uso exist() del metodo sulle colonne XML nella clausola WHERE di Transact-SQL.

  • Se il carico di lavoro recupera più valori da singole istanze XML utilizzando espressioni di percorso, può essere utile eseguire il clustering dei percorsi nell'ambito di ogni istanza XML nell'indice PROPERTY. Questa situazione si presenta in genere negli scenari che prevedono l'utilizzo di contenitori di proprietà, quando vengono recuperate le proprietà di un oggetto di cui è noto il valore della chiave primaria.

  • Se il carico di lavoro richiede l'esecuzione di query per il recupero di valori nelle istanze XML, senza conoscere i nomi degli elementi o attributi che contengono tali valori, sarà possibile creare l'indice VALUE. Ciò si verifica in genere con le ricerche degli assi discendenti, ad esempio //author[last-name="Howard"], dove <author> gli elementi possono verificarsi a qualsiasi livello della gerarchia. Si verifica anche nelle query con caratteri jolly, ad esempio /book [@* = "novel"], in cui la query cerca <book> gli elementi con un attributo con il valore "novel".

Indice XML secondario PATH

Se in genere le query specificano espressioni di percorso nelle colonne di tipo xml , un indice secondario PATH potrebbe velocizzare la ricerca. Come descritto in precedenza in questo articolo, l'indice primario è utile quando si dispone di query che specificano exist() il metodo nella clausola WHERE. Se si aggiunge un indice secondario PATH, è possibile migliorare le prestazioni della ricerca in tali query.

Sebbene un indice XML primario consenta di evitare la suddivisione dei BLOB XML in fase di esecuzione, potrebbe non garantire prestazioni ottimali per le query basate su espressioni di percorso. Poiché in tutte le righe dell'indice XML primario corrispondenti a un BLOB XML viene eseguita la ricerca sequenziale di istanze XML di grandi dimensioni, tale ricerca potrebbe risultare lenta. In tal caso, l'utilizzo di un indice secondario basato sui valori di percorso e di nodo dell'indice primario può velocizzare in modo significativo la ricerca nell'indice. Nell'indice secondario PATH, i valori di percorso e di nodo sono colonne chiave che consentono ricerche di percorsi più efficienti. Query Optimizer può utilizzare l'indice PATH per espressioni analoghe alle seguenti:

  • /root/Location che specifica solo un percorso

OPPURE

  • /root/Location/@LocationID[.="10"] in cui vengono specificati sia il valore di percorso che il valore di nodo.

Nella query seguente viene illustrato il caso in cui è utile l'indice PATH:

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

Nella query l'espressione di percorso /PD:ProductDescription/@ProductModelID e il valore "19" nel metodo exist() corrispondono ai campi chiave dell'indice PATH. In tal modo, è possibile eseguire una ricerca diretta nell'indice PATH e ottenere prestazioni di ricerca migliori rispetto a quelle della ricerca sequenziale di valori di percorso nell'indice primario.

Indice XML secondario VALUE

Se le query sono basate su valori, /Root/ProductDescription/@*[. = "Mountain Bike"] ad esempio o //ProductDescription[@Name = "Mountain Bike"]e il percorso non è completamente specificato o include un carattere jolly, è possibile ottenere risultati più veloci creando un indice XML secondario basato sui valori del nodo nell'indice XML primario.

Le colonne chiave dell'indice VALUE sono il valore di nodo e il percorso dell'indice XML primario. Se il carico di lavoro implica l'esecuzione di query per valori da istanze XML senza conoscere i nomi di elemento o di attributo che contengono tali valori, un indice VALUE può risultare utile. Ad esempio, un indice VALUE risulta vantaggioso per l'espressione seguente:

  • //author[LastName="someName"] dove si conosce il valore dell'elemento, ma l'elemento <LastName><author> padre può verificarsi ovunque.

  • /book[@* = "someValue"] dove la query cerca l'elemento <book> con un attributo con il valore "someValue".

La query seguente restituisce ContactID dalla tabella Contact . La clausola WHERE specifica un filtro che esegue la ricerca di valori nella colonna di tipo AdditionalContactInfoxml . Gli ID dei contatti vengono restituiti solo se il BLOB XML con le informazioni aggiuntive corrispondenti include un numero di telefono specifico. Poiché l'elemento telephoneNumber può essere visualizzato in qualsiasi punto del codice XML, l'espressione di percorso specifica l'asse discendente o 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;

In questo caso, il valore di ricerca per <number> è noto, ma può essere visualizzato ovunque nell'istanza XML come elemento figlio dell'elemento telephoneNumber . Per questo tipo di query può risultare utile eseguire una ricerca nell'indice basata su un valore specifico.

INDICE SECONDARIO PROPERTY

Per le query che recuperano uno o più valori da singole istanze XML può essere utile un indice PROPERTY. Questo scenario si verifica quando si recuperano le proprietà dell'oggetto usando il value() metodo del tipo xml e quando il valore della chiave primaria dell'oggetto è noto.

L'indice PROPERTY è basato su colonne (PK, path e valore del nodo) dell'indice XML primario in cui PK è la chiave primaria della tabella di base.

Ad esempio, per il modello di prodotto 19, la query seguente recupera i valori degli attributi ProductModelID e ProductModelName tramite il metodo value() . Anziché utilizzare l'indice XML primario o gli altri indici XML secondari, l'indice PROPERTY consente di velocizzare l'esecuzione.

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

Ad eccezione delle differenze descritte più avanti in questo articolo, la creazione di un indice XML in unacolonna di tipo xml è simile alla creazione di un indice in una colonna di tipo non xml . Per creare e gestire indici XML, è possibile usare le istruzioni DDL Transact-SQL seguenti:

Compressione XML

Si applica a: SQL Server 2022 (16.x) e versioni successive, database SQL di Azure e Istanza gestita di SQL di Azure.

L'abilitazione della compressione XML modifica il formato di archiviazione fisica dei dati associati al tipo di dati XML in un formato binario compresso, ma non modifica la sintassi o la semantica dei dati XML. Le modifiche dell'applicazione non sono necessarie quando una o più tabelle sono abilitate per la compressione XML.

Solo il tipo di dati XML è interessato dalla compressione XML. I dati XML vengono compressi con l'algoritmo di compressione Xpress. Tutti gli indici XML esistenti vengono compressi usando la compressione dei dati. La compressione dei dati è abilitata internamente per gli indici XML quando è abilitata la compressione XML.

La compressione XML può essere abilitata side-by-side con la compressione dei dati nelle stesse tabelle.

Gli indici XML non ereditano la proprietà di compressione della tabella. Per comprimere gli indici, è necessario abilitare in modo esplicito la compressione XML sugli indici XML.

Gli indici XML secondari non ereditano la proprietà di compressione dell'indice XML primario.

Per impostazione predefinita, al momento della creazione dell'indice XML la compressione XML è impostata su OFF.

Ottenere informazioni sugli indici XML

Le voci di indice XML vengono visualizzate nella vista sys.indexes del catalogo con l'indice type di 3. La colonna del nome contiene il nome dell'indice XML.

Gli indici XML vengono registrati anche nella vista sys.xml_indexesdel catalogo . Contiene tutte le colonne di sys.indexes e alcune specifiche utili per gli indici XML. Il valore NULL nella colonna secondary_type indica un indice XML primario. I valori Pe RV sono rispettivamente PATH, PROPERTY e VALUE secondari.

Per ottenere informazioni sullo spazio usato dagli indici XML è possibile usare la funzione con valori di tabella sys.dm_db_index_physical_stats. Fornisce informazioni, ad esempio il numero di pagine di dati occupate, le dimensioni medie delle righe in byte e il numero di record per tutti i tipi di indice. inclusi gli indici XML. Tali informazioni sono disponibili per ogni partizione del database. Gli indici XML utilizzano lo stesso schema di partizionamento e la stessa funzione di partizionamento della tabella di base.

Passaggi successivi