다음을 통해 공유

XML 인덱스(SQL Server)

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

  • XML 열에 대한 쿼리는 워크로드에서 일반적입니다. 데이터를 수정하는 동안 XML 인덱스 유지 관리 비용을 고려해야 합니다.

  • XML 값은 상대적으로 크고 검색된 부분은 상대적으로 작습니다. 인덱스를 빌드하면 런타임에 전체 데이터를 구문 분석하지 않고 효율적인 쿼리 처리를 위해 인덱스 조회에 이점을 얻을 수 있습니다.

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

  • 기본 XML 인덱스

  • 보조 XML 인덱스

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

비고

데이터베이스 옵션이 xml 데이터 형식에 맞게 제대로 설정되어 있지 않으면 XML 인덱스를 생성하거나 수정할 수 없습니다. 자세한 내용은 XML 열에서 Full-Text 검색 사용을 참조하세요.

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)이 런타임에 파쇄됩니다. 그런 다음 exist() 메서드에서 (/PD:ProductDescription/@ProductModelID[.="19"] 표현식을 평가합니다. 이 런타임 조각화는 열에 저장된 인스턴스의 크기와 수에 따라 비용이 많이 들 수 있습니다.

애플리케이션 환경에서 XML BLOB(Binary Large Objects)에 대한 쿼리가 일반적인 경우 xml 타입 열을 인덱싱하는 데 도움이 됩니다. 그러나 데이터를 수정하는 동안 인덱스 유지 관리와 관련된 비용이 발생합니다.

기본 XML 인덱스

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

기본 XML 인덱스는 데이터 형식 열에 있는 XML BLOB xml 의 조각화된 지속형 표현입니다. 열의 각 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 인스턴스를 다시 구성하는 데 필요한 모든 정보를 저장합니다.)

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

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

기본 XML 인덱스와 관련하여 기본 테이블의 각 XML 이진 큰 개체 인스턴스를 파쇄하는 대신 각 XML 이진 큰 개체에 해당하는 인덱스의 행이 메서드에 exist() 지정된 식에 대해 순차적으로 검색됩니다. 경로가 인덱 <Summary> 스의 Path 열에 있으면 해당 하위 트리와 함께 요소가 기본 XML 인덱스에서 검색되고 메서드의 query() 결과로 XML 이진 큰 개체로 변환됩니다.

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

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

보조 XML 인덱스

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

  • PATH 보조 XML 인덱스

  • VALUE 보조 XML 인덱스

  • PROPERTY 보조 XML 인덱스

다음은 하나 이상의 보조 인덱스를 만들기 위한 몇 가지 지침입니다.

  • 워크로드가 XML 열에서 경로 식을 크게 사용하는 경우 PATH 보조 XML 인덱스는 워크로드 속도를 높일 수 있습니다. 가장 일반적인 경우는 Transact-SQL의 WHERE 절에 있는 XML 열에 exist() 메서드를 사용하는 것입니다.

  • 워크로드가 경로 식을 사용하여 개별 XML 인스턴스에서 여러 값을 검색하는 경우 PROPERTY 인덱스의 각 XML 인스턴스 내에서 경로를 클러스터링하는 것이 유용할 수 있습니다. 이 시나리오는 일반적으로 개체의 속성을 가져오고 기본 키 값을 알 수 있는 경우 속성 모음 시나리오에서 발생합니다.

  • 워크로드에 해당 값이 포함된 요소 또는 특성 이름을 모르고 XML 인스턴스 내의 값을 쿼리하는 작업이 포함된 경우 VALUE 인덱스를 만들 수 있습니다. 일반적으로 //author[last-name="Howard"]와 같은 하위 축의 조회에서, <author> 요소가 계층의 어느 수준에서든 발생할 수 있습니다. /book [@* = "novel"]과 같은 와일드카드 쿼리에서도 발생합니다. 여기서 쿼리는 값이 "novel"인 일부 특성이 있는 책> 요소를 찾<습니다.

PATH 보조 XML 인덱스

쿼리가 일반적으로 형식 열에 xml 경로 식을 지정하는 경우 PATH 보조 인덱스가 검색 속도를 높일 수 있습니다. 이 항목의 앞부분에서 설명한 대로 WHERE 절에 exist() 메서드를 지정하는 쿼리가 있는 경우 기본 인덱스가 유용합니다. 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> 있는 일부 특성이 있는 요소를 찾습니다.

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

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> 은 알려져 있지만 XML 인스턴스의 아무 곳에나 요소의 <telephoneNumber> 자식으로 표시할 수 있습니다. 이러한 종류의 쿼리는 특정 값을 기반으로 하는 인덱스 조회의 이점을 얻을 수 있습니다.

PROPERTY 보조 인덱스

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

PROPERTY 인덱스는 PK가 기본 테이블의 기본 키인 기본 XML 인덱스의 열(PK, 경로 및 노드 값)을 기반으로 합니다.

예를 들어 제품 모델 19의 경우, 다음 쿼리는 ProductModelID 및 ProductModelName 속성 값을 value() 메서드를 사용하여 검색합니다. 기본 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 인덱스에 대한 정보 가져오기

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

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

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

또한 참조하십시오

sys.dm_db_index_physical_stats(Transact-SQL)
XML 데이터(SQL Server)

  • Last updated on