Udostępnij za pośrednictwem

Indeksy XML w SQL Server

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Indeksy XML można tworzyć w kolumnach typu danych XML . Indeksują wszystkie tagi, wartości i ścieżki dla wystąpień XML w kolumnie, co poprawia wydajność zapytań. Aplikacja może korzystać z indeksu XML w następujących sytuacjach:

  • Zapytania dotyczące kolumn XML są powszechne w Twojej obsłudze. Należy rozważyć koszt konserwacji indeksu XML podczas modyfikowania danych.

  • Wartości XML są stosunkowo duże, a pobrane części są stosunkowo małe. Tworzenie indeksu pozwala uniknąć parsowania całych danych w czasie wykonywania i zapewnia korzyści z wyszukiwania indeksów w celu wydajnego przetwarzania zapytań.

Począwszy od programu SQL Server 2022 (16.x) i nowszych wersji, a także w usługach Azure SQL Database i Azure SQL Managed Instance, możesz użyć kompresji XML , aby skompresować dane XML poza wierszem zarówno dla kolumn XML, jak i indeksów. Kompresja XML zmniejsza wymagania dotyczące pojemności magazynu danych.

Indeksy XML należą do następujących kategorii:

  • Podstawowy indeks XML
  • Pomocniczy indeks XML

Pierwszy indeks w kolumnie typu xml musi być podstawowym indeksem XML. Przy użyciu podstawowego indeksu XML obsługiwane są następujące typy indeksów pomocniczych: PATH, VALUE i PROPERTY. W zależności od typu zapytań te indeksy pomocnicze mogą pomóc zwiększyć wydajność zapytań.

Uwaga / Notatka

Nie można utworzyć ani zmodyfikować indeksu XML, chyba że opcje bazy danych są ustawione poprawnie do pracy z typem danych XML . Aby uzyskać więcej informacji, zobacz Use Full-Text Search with XML Columns (Używanie wyszukiwania Full-Text z kolumnami XML).

Wystąpienia XML są przechowywane w kolumnach typu XML jako dużych obiektów binarnych (BLOB). Te wystąpienia XML mogą być duże, a przechowywana reprezentacja binarna wystąpień typu danych XML może być maksymalnie 2 GB. Bez indeksu te duże obiekty binarne są niszczone w czasie wykonywania w celu oceny zapytania. To rozdrobnienie może być czasochłonne. Rozważmy na przykład następujące zapytanie:

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

Aby wybrać wystąpienia XML, które spełniają warunek w klauzuli WHERE, binarny plik XML dużych rozmiarów (BLOB) w każdym wierszu tabeli Production.ProductModel jest rozdrobniony podczas wykonywania. Następnie wyrażenie (/PD:ProductDescription/@ProductModelID[.="19"]) w metodzie exist() jest obliczane. To rozdrobnienie w czasie wykonywania może być kosztowne, w zależności od rozmiaru i liczby wystąpień przechowywanych w kolumnie.

Jeśli wykonywanie zapytań dotyczących dużych obiektów binarnych XML (BLOB) jest typowe w środowisku aplikacji, pomaga indeksować kolumny typu xml . Istnieje jednak koszt związany z utrzymaniem indeksu podczas modyfikowania danych.

Podstawowy indeks XML

Podstawowy indeks XML indeksuje wszystkie tagi, wartości i ścieżki w wystąpieniach XML w kolumnie XML. Aby utworzyć podstawowy indeks XML, tabela, w której występuje kolumna XML, musi mieć indeks klastrowany w kluczu podstawowym tabeli. Program SQL Server używa tego klucza podstawowego do skorelowania wierszy w podstawowym indeksie XML z wierszami w tabeli zawierającej kolumnę XML.

Podstawowy indeks XML jest rozczłonkowaną i utrwaloną reprezentacją obiektów BLOB XML w kolumnie typu danych xml Indeks tworzy wiele wierszy danych dla każdego binarnego obiektu XML (BLOB) w kolumnie. Liczba wierszy w indeksie jest w przybliżeniu równa liczbie węzłów w dużym obiekcie binarnym XML. Gdy zapytanie pobiera pełne wystąpienie XML, program SQL Server udostępnia wystąpienie z kolumny XML. Zapytania w wystąpieniach XML używają podstawowego indeksu XML i mogą zwracać wartości skalarne lub poddrzewa XML przy użyciu samego indeksu.

Każdy wiersz przechowuje następujące informacje o węźle:

  • Nazwa tagu, taka jak nazwa elementu lub atrybutu.

  • Wartość węzła.

  • Typ węzła, taki jak węzeł elementu, węzeł atrybutu lub węzeł tekstowy.

  • Informacje o kolejności dokumentu reprezentowane przez wewnętrzny identyfikator węzła.

  • Ścieżka z każdego węzła do korzenia drzewa XML. Ta kolumna jest wyszukiwana pod kątem wyrażeń ścieżki w zapytaniu.

  • Klucz podstawowy tabeli podstawowej. Klucz podstawowy tabeli podstawowej jest zduplikowany w podstawowym indeksie XML dla sprzężenia zwrotnego z tabelą podstawową, a maksymalna liczba kolumn w kluczu podstawowym tabeli bazowej jest ograniczona do 15.

Te informacje o węźle służą do oceniania i konstruowania wyników XML dla określonego zapytania. W celach optymalizacji nazwa tagu i informacje o typie węzła są kodowane jako wartości całkowite, a kolumna Path używa tego samego kodowania. Ponadto ścieżki są przechowywane w odwrotnej kolejności, aby umożliwić dopasowywanie ścieżek tylko wtedy, gdy jest znany tylko sufiks ścieżki. Przykład:

  • //ContactRecord/PhoneNumber gdzie znane są tylko dwa ostatnie kroki

LUB

  • /Book/*/Title gdzie symbol wieloznaczny * jest określony w środku wyrażenia.

Procesor zapytań używa podstawowego indeksu XML dla zapytań obejmujących metody typów danych XML i zwraca wartości skalarne lub poddrzewa XML z samego indeksu podstawowego. (Ten indeks przechowuje wszystkie informacje niezbędne do odtworzenia wystąpienia XML).

Na przykład następujące zapytanie zwraca informacje podsumowania przechowywane w CatalogDescription kolumnie typu xml w ProductModel tabeli. Zapytanie zwraca <Summary> informacje tylko dla modeli produktów, których opis katalogu przechowuje również <Features> opis.

;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

Jeśli chodzi o podstawowy indeks XML, zamiast niszczać każde wystąpienie dużego obiektu binarnego XML w tabeli bazowej, wiersze w indeksie odpowiadającym każdemu binarnemu plikowi XML dużego obiektu są przeszukiwane sekwencyjnie dla wyrażenia określonego w metodzie exist() . Jeśli ścieżka zostanie znaleziona w kolumnie Ścieżka w indeksie, element <Summary> wraz z jego poddrzewami zostanie pobrany z podstawowego indeksu XML i przekonwertowany na duży obiekt binarny XML jako wynik działania metody query().

Podstawowy indeks XML nie jest używany podczas pobierania pełnego wystąpienia XML. Na przykład poniższe zapytanie pobiera z tabeli całe wystąpienie XML, które opisuje instrukcje produkcji dla określonego modelu produktu.

USE AdventureWorks2022;

SELECT Instructions
FROM   Production.ProductModel
WHERE  ProductModelID = 7;

Pomocnicze indeksy XML

Aby zwiększyć wydajność wyszukiwania, można utworzyć pomocnicze indeksy XML. Przed utworzeniem indeksów pomocniczych należy najpierw istnieć podstawowy indeks XML. Są to typy:

  • Pomocniczy indeks XML PATH

  • Pomocniczy indeks XML VALUE

  • Pomocniczy indeks XML WŁAŚCIWOŚCI

Poniżej przedstawiono wskazówki dotyczące tworzenia co najmniej jednego indeksu pomocniczego:

  • Jeśli obciążenie często używa wyrażeń ścieżki w kolumnach XML, pomocniczy indeks XML PATH może przyspieszyć jego przetwarzanie. Najczęstszym przypadkiem jest użycie exist() metody w kolumnach XML w klauzuli WHERE języka Transact-SQL.

  • Jeśli obciążenie pobiera wiele wartości z poszczególnych wystąpień XML za pomocą wyrażeń ścieżek, grupowanie ścieżek w każdym wystąpieniu XML w indeksie WŁAŚCIWOŚCI może być użyteczne. Ten scenariusz zwykle występuje w scenariuszu torby właściwości, gdy właściwości obiektu są pobierane, a jego wartość klucza podstawowego jest znana.

  • Jeśli obciążenie obejmuje wykonywanie zapytań dotyczących wartości w wystąpieniach XML bez znajomości nazw elementów lub atrybutów zawierających te wartości, możesz utworzyć indeks VALUE. Zjawisko to zwykle występuje przy wyszukiwaniu z użyciem osi zstępujących, takich jak //author[last-name="Howard"], w których elementy <author> mogą pojawić się na dowolnym poziomie hierarchii. Występuje również w zapytaniach wieloznacznych, takich jak /book [@* = "novel"], gdzie zapytanie wyszukuje <book> elementy, które mają jakiś atrybut o wartości "novel".

Pomocniczy indeks XML PATH

Jeśli zapytania zazwyczaj określają wyrażenia ścieżki w kolumnach typu xml , indeks pomocniczy PATH może być w stanie przyspieszyć wyszukiwanie. Jak opisano wcześniej w tym artykule, indeks podstawowy jest przydatny, gdy masz zapytania określające exist() metodę w klauzuli WHERE. Jeśli dodasz indeks pomocniczy PATH, możesz również zwiększyć wydajność wyszukiwania w takich zapytaniach.

Mimo że podstawowy indeks XML unika konieczności rozdrobnienia dużych obiektów binarnych XML w czasie wykonywania, może nie zapewnić najlepszej wydajności zapytań opartych na wyrażeniach ścieżki. Ponieważ wszystkie wiersze w podstawowym indeksie XML odpowiadające dużemu obiektowi XML są przeszukiwane sekwencyjnie dla dużych wystąpień XML, wyszukiwanie sekwencyjne może być powolne. W takim przypadku posiadanie indeksu pomocniczego utworzonego na podstawie wartości ścieżki i wartości węzłów w indeksie podstawowym może znacznie przyspieszyć wyszukiwanie indeksów. W pomocniczym indeksie PATH wartości ścieżki i węzła to kluczowe kolumny, które pozwalają na bardziej efektywne przeszukiwanie ścieżek. Optymalizator zapytań może używać indeksu PATH dla wyrażeń, takich jak pokazane w następujących kwestiach:

  • /root/Location które określają tylko ścieżkę

LUB

  • /root/Location/@LocationID[.="10"] gdzie określono zarówno ścieżkę, jak i wartość węzła.

Następujące zapytanie pokazuje, gdzie przydatny jest indeks 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;

W zapytaniu wyrażenie /PD:ProductDescription/@ProductModelID ścieżki i wartość "19" w exist() metodzie odpowiadają polam klucza indeksu PATH. Umożliwia to bezpośrednie wyszukiwanie w indeksie PATH i zapewnia lepszą wydajność wyszukiwania niż sekwencyjne wyszukiwanie wartości ścieżek w indeksie podstawowym.

Pomocniczy indeks XML VALUE

Jeśli zapytania są oparte na wartości, na przykład /Root/ProductDescription/@*[. = "Mountain Bike"] lub //ProductDescription[@Name = "Mountain Bike"], a ścieżka nie jest w pełni określona lub zawiera symbol wieloznaczny, możesz uzyskać szybsze wyniki, tworząc pomocniczy indeks XML utworzony na wartości węzłów w podstawowym indeksie XML.

Kluczowe kolumny indeksu VALUE to wartość węzła i ścieżka w podstawowym indeksie XML. Jeśli obciążenie obejmuje wykonywanie zapytań dotyczących wartości z wystąpień XML bez znajomości nazw elementów lub atrybutów zawierających wartości, indeks VALUE może być przydatny. Na przykład następujące wyrażenie będzie korzystać z indeksu VALUE:

  • //author[LastName="someName"] gdzie znasz wartość <LastName> elementu, ale <author> element nadrzędny może występować w dowolnym miejscu.

  • /book[@* = "someValue"] gdzie zapytanie szuka <book> elementu, który ma jakiś atrybut o wartości "someValue".

Poniższe zapytanie zwraca ContactID z Contact tabeli. Klauzula WHERE określa filtr, który wyszukuje wartości w kolumnie AdditionalContactInfo typu xml. Identyfikatory kontaktów są zwracane tylko wtedy, gdy odpowiedni dodatkowy plik binarny XML informacji kontaktowych zawiera określony numer telefonu. telephoneNumber Ponieważ element może występować w dowolnym miejscu w pliku XML, wyrażenie ścieżki określa oś potomek-lub-siebie.

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

W takiej sytuacji jest znana wartość <number> wyszukiwania, ale może być wyświetlana w dowolnym miejscu w wystąpieniu XML jako element podrzędny telephoneNumber elementu. Tego rodzaju zapytanie może korzystać z wyszukiwania indeksu na podstawie określonej wartości.

Indeks pomocniczy WŁAŚCIWOŚCI

Zapytania pobierające co najmniej jedną wartość z poszczególnych wystąpień XML mogą korzystać z indeksu WŁAŚCIWOŚCI. Ten scenariusz występuje, gdy pobierasz właściwości obiektu przy użyciu value() metody typu xml i gdy jest znana wartość klucza podstawowego obiektu.

Indeks PROPERTY jest oparty na kolumnach (PK, ścieżka i wartość węzła) podstawowego indeksu XML, gdzie PK jest kluczem podstawowym tabeli podstawowej.

Na przykład w przypadku modelu 19produktu następujące zapytanie pobiera wartości atrybutów ProductModelID i ProductModelName przy użyciu value() metody . Zamiast używać podstawowego indeksu XML lub innych pomocniczych indeksów XML, indeks PROPERTY może zapewnić szybsze wykonywanie.

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

Z wyjątkiem różnic opisanych w dalszej części tego artykułu tworzenie indeksu XML w kolumnie typu xml jest podobne do tworzenia indeksu w kolumnie typu innego niż xml. Następujące instrukcje Transact-SQL DDL mogą służyć do tworzenia indeksów XML i zarządzania nimi:

Kompresja XML

Dotyczy: SQL Server 2022 (16.x) i nowsze wersje, Azure SQL Database i Azure SQL Managed Instance.

Włączenie kompresji XML zmienia format magazynu fizycznego danych skojarzonych z typem danych XML na skompresowany format binarny, ale nie zmienia składni danych XML ani semantyki. Zmiany aplikacji nie są wymagane, gdy co najmniej jedna tabela jest włączona na potrzeby kompresji XML.

Tylko typ danych XML ma wpływ na kompresję XML. Dane XML są kompresowane za pomocą algorytmu kompresji Xpress. Wszystkie istniejące indeksy XML są kompresowane przy użyciu kompresji danych. Kompresja danych jest włączana wewnętrznie dla indeksów XML, gdy jest włączona kompresja XML.

Kompresja XML może być włączona równolegle z kompresją danych w tych samych tabelach.

Indeksy XML nie dziedziczą właściwości kompresji tabeli. Aby skompresować indeksy, należy jawnie włączyć kompresję XML w indeksach XML.

Pomocnicze indeksy XML nie dziedziczą właściwości kompresji podstawowego indeksu XML.

Domyślnie kompresja XML dla indeksów XML jest wyłączona podczas tworzenia indeksu.

Uzyskiwanie informacji o indeksach XML

Wpisy indeksu XML są wyświetlane w widoku sys.indexes wykazu z indeksem type3. Kolumna name zawiera nazwę indeksu XML.

Indeksy XML są również rejestrowane w widoku katalogu sys.xml_indexes. Zawiera on wszystkie kolumny sys.indexes i niektóre konkretne, które są przydatne w przypadku indeksów XML. Wartość NULL w kolumnie secondary_type wskazuje podstawowy indeks XML; wartości Pi RV oznaczają odpowiednio indeksy PATH, PROPERTY i VALUE pomocnicze indeksy XML.

Sposób wykorzystania przestrzeni przez indeksy XML można znaleźć w funkcji zwracającej wartości tabeli sys.dm_db_index_physical_stats. Zawiera informacje, takie jak liczba zajętych stron danych, średni rozmiar wiersza w bajtach i liczba rekordów dla wszystkich typów indeksów. Obejmuje to również indeksy XML. Te informacje są dostępne dla każdej partycji bazy danych. Indeksy XML używają tego samego schematu partycjonowania i funkcji partycjonowania tabeli podstawowej.

Dalsze kroki