XML 인덱스(SQL Server)

아티클
08/05/2023

적용 대상:SQL Server Azure SQL DatabaseAzure SQL Managed Instance

XML 데이터 형식 열에 XML 인덱스를 만들 수 있습니다. 열의 XML 인스턴스에 대한 모든 태그, 값 및 경로를 인덱싱하고 쿼리 성능을 향상합니다. 애플리케이션은 다음과 같은 상황에서 XML 인덱스를 활용할 수 있습니다.

XML 열에 대한 쿼리는 워크로드에서 일반적입니다. 데이터를 수정하는 동안 XML 인덱스 유지 관리 비용을 고려해야 합니다.
XML 값은 상대적으로 크고 검색된 부분은 상대적으로 작습니다. 인덱스를 작성하면 런타임 시 전체 데이터의 구문 분석이 방지되므로 인덱스 조회를 통해 쿼리를 효율적으로 처리할 수 있습니다.

SQL Server 2022(16.x) 이상 버전부터 Azure SQL Database 및 Azure SQL Managed Instance에서 XML 압축을 사용하여 XML 열과 인덱스 모두에 대해 행이 다른 XML 데이터를 압축할 수 있습니다. XML 압축은 데이터 스토리지 용량 요구 사항을 줄입니다.

XML 인덱스는 다음 범주로 분류됩니다.

기본 XML 인덱스
보조 XML 인덱스

xml 형식 열의 첫 번째 인덱스가 기본 XML 인덱스여야 합니다. 기본 XML 인덱스를 사용하면 다음과 같은 유형의 보조 인덱스가 지원됩니다. PATH, VALUE 및 PROPERTY. 이러한 보조 인덱스는 쿼리 유형에 따라 쿼리 성능을 향상시키는 데 도움이 될 수 있습니다.

참고 항목

xml 데이터 형식을 사용하기 위해 데이터베이스 옵션이 올바르게 설정되지 않으면 XML 인덱스 만들기 또는 수정이 불가능합니다. 자세한 내용은 XML 열에 전체 텍스트 검색 사용을 참조하세요.

XML 인스턴스는 XML 형식 열에 BLOB(큰 이진 개체)로 저장됩니다. 이러한 XML 인스턴스는 클 수 있으며 xml 데이터 형식 인스턴스의 저장된 이진 표현은 최대 2GB일 수 있습니다. 인덱스가 없으면 이러한 이진 큰 개체는 런타임에 파쇄되어 쿼리를 평가합니다. 이 조각화는 시간이 오래 걸릴 수 있습니다. 예를 들어 다음과 같은 쿼리를 고려해 보겠습니다.

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

절의 조건을 충족하는 XML 인스턴스를 선택하려면 테이블의 Production.ProductModel 각 행에 있는 WHERE XML BLOB(Binary Large Object)이 런타임에 파쇄됩니다. 그런 다음 메서드의 식 (/PD:ProductDescription/@ProductModelID[.="19"])이 exist() 계산됩니다. 이러한 런타임 단편화는 열에 저장된 인스턴스의 크기와 수에 따라 비용이 많이 들 수 있습니다.

애플리케이션 환경에서 XML BLOB(Binary Large Objects)을 쿼리하는 것이 일반적인 경우 xml 형식 열을 인덱싱하는 데 도움이 됩니다. 그러나 데이터 수정 시에는 인덱스를 유지 관리하는 비용이 듭니다.

기본 XML 인덱스

기본 XML 인덱스는 XML의 XML 인스턴스 내에 있는 모든 태그, 값 및 경로를 인덱싱합니다. 기본 XML 인덱스를 만들려면 XML 열이 발생하는 테이블에 테이블의 기본 키에 클러스터형 인덱스가 있어야 합니다. SQL Server는 이 기본 키를 사용하여 주 XML 인덱스의 행과 XML 열이 포함된 테이블의 행을 상호 연결합니다.

기본 XML 인덱스는 xml 데이터 형식 열의 XML BLOB을 지속적인 단편 형태로 표현한 것입니다. 열의 각 XML BLOB(Binary Large Object)에 대해 인덱스는 여러 행의 데이터를 만듭니다. 인덱스의 행 수는 XML 이진 큰 개체의 노드 수와 거의 같습니다. 쿼리가 전체 XML 인스턴스를 검색하면 SQL Server는 XML 열에서 인스턴스를 제공합니다. XML 인스턴스 내의 쿼리는 기본 XML 인덱스를 사용하며 인덱스 자체를 사용하여 스칼라 값 또는 XML 하위 트리를 반환할 수 있습니다.

각 행은 다음 노드 정보를 저장합니다.

요소 또는 특성 이름과 같은 태그 이름입니다.
노드 값입니다.
요소 노드, 특성 노드 또는 텍스트 노드와 같은 노드 유형
내부 노드 식별자가 나타내는 문서 순서 정보입니다.
각 노드에서 XML 트리의 루트로 가는 경로입니다. 이 열은 쿼리에서 경로 식을 검색합니다.
기본 테이블의 기본 키. 기본 테이블의 기본 키는 기본 테이블과의 백 조인에 대한 기본 XML 인덱스에 중복되며 기본 테이블의 기본 키에 있는 최대 열 수는 15개로 제한됩니다.

이 노드 정보는 지정된 쿼리에 대한 XML 결과를 평가하고 생성하는 데 사용됩니다. 최적화를 위해 태그 이름 및 노드 형식 정보는 정수 값으로 인코딩되고 경로 열은 동일한 인코딩을 사용합니다. 또한 경로 접미사만 알려진 경우 일치하는 경로를 허용하도록 경로가 역순으로 저장됩니다. 예:

//ContactRecord/PhoneNumber 여기서 마지막 두 단계만 알려져 있습니다.

/Book/*/Title - 와일드카드 문자(*)가 식 중간에 지정되어 있는 경우

쿼리 프로세서는 xml 데이터 형식 메서드를 포함하는 쿼리에 기본 XML 인덱스를 사용하고 기본 인덱스 자체에서 스칼라 값 또는 XML 하위 트리를 반환합니다. (이 인덱스는 XML 인스턴스를 다시 구성하는 데 필요한 모든 정보를 저장합니다.)

예를 들어 다음 쿼리는 CatalogDescription테이블의 xml ProductModel 형식 열에 저장된 요약 정보를 반환합니다. 이 쿼리는 카탈로그 설명에 <Features> 설명도 저장되어 있는 제품 모델에 대해서만 <Summary> 정보를 반환합니다.

;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

기본 XML 인덱스에 관해 기본 테이블에서 각 XML BLOB 인스턴스를 단편화하는 대신, 각 XML BLOB에 해당하는 인덱스의 행이 exist() 메서드에 지정된 식에 대해 순서대로 검색됩니다. 경로를 인덱스의 경로 열에서 찾은 경우 <Summary> 요소와 그 하위 트리는 기본 XML 인덱스에서 함께 검색되고 query() 메서드의 결과로 XML BLOB로 변환됩니다.

전체 XML 인스턴스를 검색할 때는 기본 XML 인덱스가 사용되지 않습니다. 예를 들어 다음 쿼리는 테이블에서 특정 제품 모델에 대한 제조 지침을 설명하는 전체 XML 인스턴스를 검색합니다.

USE AdventureWorks2022;

SELECT Instructions
FROM   Production.ProductModel
WHERE  ProductModelID = 7;

보조 XML 인덱스

검색 성능을 향상시키기 위해 보조 XML 인덱스를 만들 수 있습니다. 보조 인덱스를 만들려면 먼저 기본 XML 인덱스가 있어야 합니다. 유형은 다음과 같습니다.

PATH 보조 XML 인덱스
VALUE 보조 XML 인덱스
PROPERTY 보조 XML 인덱스

다음은 하나 이상의 보조 인덱스를 만드는 데에 대한 몇 가지 지침입니다.

작업에서 XML 열에 경로 식이 중요하게 사용되는 경우 PATH 보조 XML 인덱스는 작업 속도를 높일 수 있습니다. 가장 일반적인 경우는 Transact-SQL의 exist() WHERE 절에서 XML 열에 메서드를 사용하는 것입니다.
작업에 경로 식을 사용하여 개별적인 XML 인스턴스로부터 여러 값을 검색하는 경우 PROPERTY 인덱스에 있는 각 XML 인스턴스 내의 경로를 클러스터링하면 도움이 될 수 있습니다. 이 시나리오는 일반적으로 개체의 속성을 가져오고 기본 키 값을 알 수 있는 경우 속성 모음 시나리오에서 발생합니다.
워크로드에 해당 값이 포함된 요소 또는 특성 이름을 모르고 XML 인스턴스 내의 값을 쿼리하는 작업이 포함된 경우 VALUE 인덱스를 만들 수 있습니다. 이러한 경우는 <author> 요소가 계층 구조의 어느 수준에서도 발생할 수 있는 //author[last-name="Howard"] 같은 하위 항목 축 조회 시에 일반적으로 발생합니다. 또한 쿼리가 "novel" 값이 있는 일부 특성을 포함하는 <book> 요소를 찾는 /book [@* = "novel"] 같은 와일드카드 쿼리에서도 발생합니다.

PATH 보조 XML 인덱스

쿼리에서 일반적으로 xml 유형 열에 경로 식을 지정하는 경우에는 PATH 보조 인덱스로 검색 속도를 높일 수도 있습니다. 이 문서의 앞에서 설명한 대로 기본 인덱스는 exist() 메서드를 WHERE 절에 지정하는 쿼리가 있는 경우에 유용합니다. PATH 보조 인덱스를 추가하는 경우 이러한 쿼리에서 검색 성능을 향상시킬 수도 있습니다.

기본 XML 인덱스는 런타임에 XML 이진 큰 개체를 분쇄하지 않아도 되지만 경로 식에 따라 쿼리에 최상의 성능을 제공하지 못할 수 있습니다. XML 이진 큰 개체에 해당하는 기본 XML 인덱스의 모든 행은 큰 XML 인스턴스에 대해 순차적으로 검색되므로 순차 검색 속도가 느려질 수 있습니다. 이 경우 기본 인덱스의 경로 값과 노드 값에 보조 인덱스를 만들면 인덱스 검색의 속도가 현저히 빨라질 수 있습니다. PATH 보조 인덱스의 경로 및 노드 값은 경로를 검색할 때 보다 효율적인 검색을 허용하는 키 열입니다. 쿼리 최적화 프로그램은 다음과 같은 식에 PATH 인덱스를 사용할 수 있습니다.

/root/Location 경로만 지정하는

/root/Location/@LocationID[.="10"] 경로와 노드 값이 모두 지정된 위치입니다.

다음 쿼리는 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;

쿼리에서 /PD:ProductDescription/@ProductModelID 메서드의 경로 식 "19" 및 exist() 값은 PATH 인덱스의 키 필드에 해당합니다. 이렇게 하면 PATH 인덱스를 직접 검색할 수 있으며 기본 인덱스의 경로 값에 대한 순차 검색보다 더 나은 검색 성능을 제공합니다.

VALUE 보조 XML 인덱스

예를 들어 쿼리가 값을 기반으로 하는 /Root/ProductDescription/@*[. = "Mountain Bike"] 또는 //ProductDescription[@Name = "Mountain Bike"]이고 경로가 완전히 지정되지 않거나 와일드카드를 포함하는 경우에는 기본 XML 인덱스의 노드 값에 보조 XML 인덱스를 만들어 더 빠른 결과를 얻을 수 있습니다.

VALUE 인덱스의 키 열은 주 XML 인덱스의 (노드 값 및 경로)입니다. 값을 포함하는 요소 또는 특성 이름을 알 필요 없이 XML 인스턴스에서 해당 값을 쿼리하는 작업이 포함되는 경우 VALUE 인덱스가 유용합니다. 예를 들어 다음 식은 VALUE 인덱스가 있으면 도움이 됩니다.

//author[LastName="someName"] - <LastName> 요소의 값은 알지만 <author> 부모가 아무 곳에서나 발생할 수 있는 경우.
/book[@* = "someValue"] - 쿼리가 "someValue" 값이 있는 일부 특성을 가진 <book> 요소를 찾는 경우.

다음 쿼리에서는 ContactID 테이블에서 Contact 를 반환합니다. 이 절은 WHERE xml 형식 열에서 값을 찾는 필터를 AdditionalContactInfo지정합니다. 연락처 ID는 해당 추가 연락처 정보 XML 이진 큰 개체에 특정 전화 번호가 포함된 경우에만 반환됩니다. telephoneNumber 요소가 XML의 아무 위치에서나 나타날 수 있기 때문에 경로 식은 하위 또는 자체 축을 지정합니다.

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

이 경우 <number>에 대한 검색 값을 알지만 이 값은 telephoneNumber 요소의 자식으로 XML 인스턴스의 아무 위치에서나 나타날 수 있습니다. 이러한 유형의 쿼리를 사용하면 특정 값에 기반한 인덱스 조회의 장점을 활용할 수 있습니다.

PROPERTY 보조 인덱스

개별 XML 인스턴스에서 하나 이상의 값을 검색하는 쿼리는 PROPERTY 인덱스의 장점을 활용할 수 있습니다. 이 시나리오는 xml 형식의 메서드를 사용하여 value() 개체 속성을 검색하고 개체의 기본 키 값이 알려진 경우에 발생합니다.

PK가 기본 테이블의 기본 키인 기본 XML 인덱스의 열(PK, 경로 및 노드 값)에 PROPERTY XML 인덱스를 만듭니다.

예를 들어 제품 모델의 19경우 다음 쿼리는 메서드를 ProductModelID 사용하여 value() 특성 값과 ProductModelName 특성을 검색합니다. 기본 XML 인덱스 또는 다른 보조 XML 인덱스를 사용하는 대신 PROPERTY 인덱스는 더 빠른 실행을 제공할 수 있습니다.

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

이 문서의 뒷부분에서 설명하는 차이점을 제외하면xml 유형 열에 XML 인덱스를 만드는 것은 비xml 유형 열에 인덱스를 만드는 것과 비슷합니다. 다음 Transact-SQL DDL 문은 XML 인덱스의 작성 및 관리에 사용될 수 있습니다.

XML 압축

적용 대상: SQL Server 2022(16.x) 이상 버전, Azure SQL Database 및 Azure SQL Managed Instance.

XML 압축을 사용하도록 설정하면 XML 데이터 형식 과 연결된 데이터의 실제 스토리지 형식이 압축된 이진 형식으로 변경되지만 XML 데이터 구문 또는 의미 체계는 변경되지 않습니다. 하나 이상의 테이블에 XML 압축이 설정될 경우 애플리케이션을 변경할 필요는 없습니다.

XML 데이터 형식만 XML 압축의 영향을 받습니다. XML 데이터는 Xpress 압축 알고리즘을 사용하여 압축됩니다. 기존 XML 인덱스는 모두 데이터 압축을 사용하여 압축됩니다. XML 압축을 사용하도록 설정하면 XML 인덱스에 대해 내부적으로 데이터 압축이 사용됩니다.

XML 압축은 동일한 테이블에서 데이터 압축과 함께 사용할 수 있습니다.

XML 인덱스는 테이블의 압축 속성을 상속하지 않습니다. 인덱스를 압축하려면 XML 인덱스에 대해 XML 압축을 명시적으로 사용하도록 설정해야 합니다.

보조 XML 인덱스는 기본 XML 인덱스의 압축 속성을 상속하지 않습니다.

기본적으로 인덱스를 만들 때 XML 인덱스의 XML 압축 설정은 OFF로 설정됩니다.

XML 인덱스에 대한 정보 가져오기

XML 인덱스 항목은 인덱스 type이 3인 카탈로그 뷰 sys.indexes에 표시됩니다. 이름 열에는 XML 인덱스의 이름이 포함됩니다.

XML 인덱스는 또한 카탈로그 뷰 sys.xml_indexes에 기록됩니다. 여기에는 sys.indexes의 모든 열과 XML 인덱스에 유용한 특정 열이 포함됩니다. secondary_type 열에 있는 NULL 값은 기본 XML 인덱스를 나타냅니다. P, R 및 V 값은 각각 PATH, PROPERTY 및 VALUE 보조 XML 인덱스를 나타냅니다.

XML 인덱스의 공간 사용은 테이블 반환 함수 sys.dm_db_index_physical_stats 찾을 수 있습니다. 이 함수는 모든 인덱스 유형에 대해 사용된 데이터 페이지 수, 평균 행 크기(바이트) 및 레코드 수와 같은 정보를 제공합니다. 여기에는 XML 인덱스도 포함됩니다. 이 정보는 각 데이터베이스 파티션에 사용할 수 있습니다. XML 인덱스는 기본 테이블과 동일한 파티션 구성표 및 파티션 함수를 사용합니다.