JSON 데이터 인덱싱
적용 대상:
SQL Server 2016(13.x) 이상 Azure SQL DatabaseAzure SQL Managed Instance
SQL Server 및 SQL Database에서 JSON은 기본 제공 데이터 형식이 아니며 SQL Server에는 사용자 지정 JSON 인덱스가 없습니다. 그러나 표준 인덱스를 사용하여 JSON 문서에 대해 쿼리를 최적화할 수 있습니다.
데이터베이스 인덱스는 필터 및 정렬 작업의 성능을 향상시킵니다. 인덱스를 사용하지 않으면 SQL Server는 데이터를 쿼리할 때마다 전체 테이블을 검색해야 합니다.
계산 열을 사용하여 JSON 속성 인덱싱
SQL Server에 JSON 데이터를 저장하는 경우 일반적으로 JSON 문서의 하나 이상의 속성을 기준으로 쿼리 결과를 필터링하거나 정렬하려고 합니다.
예
이 예제에서는 AdventureWorks SalesOrderHeader 테이블에 Info 판매 주문에 대한 JSON 형식의 다양한 정보가 포함된 열이 있다고 가정합니다. 예를 들어 이 열에는 고객, 영업 사원, 배송 및 대금 청구 주소 등에 대한 비정형 데이터가 포함됩니다. Info 열의 값을 사용하여 고객의 판매 주문을 필터링할 수 있습니다.
기본적으로 사용된 Info 열은 존재하지 않으며, 다음 코드를 사용하여 AdventureWorks 데이터베이스에 만들 수 있습니다. 이는 샘플 데이터베이스의 AdventureWorksLT 시리즈에는 적용되지 않습니다.
IF NOT EXISTS(SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('[Sales].[SalesOrderHeader]') AND name = 'Info')
ALTER TABLE [Sales].[SalesOrderHeader] ADD [Info] NVARCHAR(MAX) NULL
GO
UPDATE h
SET [Info] =
(
SELECT [Customer.Name] = concat(p.FirstName, N' ', p.LastName),
[Customer.ID] = p.BusinessEntityID,
[Customer.Type] = p.[PersonType],
[Order.ID] = soh.SalesOrderID,
[Order.Number] = soh.SalesOrderNumber,
[Order.CreationData] = soh.OrderDate,
[Order.TotalDue] = soh.TotalDue
FROM [Sales].SalesOrderHeader AS soh
INNER JOIN [Sales].[Customer] AS c ON c.CustomerID = soh.CustomerID
INNER JOIN [Person].[Person] AS p ON p.BusinessEntityID = c.CustomerID
WHERE soh.SalesOrderID = h.SalesOrderID FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
)
FROM [Sales].SalesOrderHeader AS h;
최적화할 쿼리
다음은 인덱스로 최적화하려는 쿼리 형식의 예입니다.
SELECT SalesOrderNumber,
OrderDate,
JSON_VALUE(Info, '$.Customer.Name') AS CustomerName
FROM Sales.SalesOrderHeader
WHERE JSON_VALUE(Info, '$.Customer.Name') = N'Aaron Campbell'
예제 인덱스
JSON 문서의 속성에 대한 필터 또는 ORDER BY 절의 속도를 높이려면 다른 열에서 이미 사용 중인 것과 동일한 인덱스를 사용할 수 있습니다. 그러나 JSON 문서에서 속성을 직접 참조할 수는 없습니다.
- 먼저 필터링에 사용할 값을 반환하는 “가상 열”을 만들어야 합니다.
- 그런 다음 해당 가상 열에 인덱스 만들기를 수행해야 합니다.
다음 예제에서는 인덱싱에 사용할 수 있는 계산 열을 만듭니다. 그런 다음 새 계산 열에서 인덱스를 만듭니다. 이 예제에서는 JSON 데이터의 경로에 $.Customer.Name 저장된 고객 이름을 노출하는 열을 만듭니다.
ALTER TABLE Sales.SalesOrderHeader
ADD vCustomerName AS JSON_VALUE(Info,'$.Customer.Name')
CREATE INDEX idx_soh_json_CustomerName
ON Sales.SalesOrderHeader(vCustomerName)
이 문은 다음 경고를 반환합니다.
Warning! The maximum key length for a nonclustered index is 1700 bytes.
The index 'vCustomerName' has maximum length of 8000 bytes.
For some combination of large values, the insert/update operation will fail.
함수는 JSON_VALUE 최대 8,000바이트(예: NVARCHAR(4000) 형식)의 텍스트 값을 반환할 수 있습니다. 그러나 1700바이트보다 긴 값은 인덱싱할 수 없습니다. 인덱싱된 계산 열에 1700바이트보다 긴 값을 입력하려고 하면 DML 작업이 실패합니다. 런타임 오류입니다.
성능을 향상시키려면 계산 열을 사용하여 노출한 값을 적용 가능한 가장 작은 형식으로 캐스팅해 보세요. 문자열 형식 대신 int 및 datetime2 형식을 사용합니다.
계산 열에 대한 자세한 정보
계산 열이 유지되지 않습니다. 인덱스를 다시 작성해야 하는 경우에만 계산됩니다. 테이블의 추가 공간을 차지하지 않습니다.
쿼리에서 사용할 식과 동일한 식을 사용하여 계산 열을 만드는 것이 중요합니다. 이 예제에서는 식이 다음과 같습니다 JSON_VALUE(Info, '$.Customer.Name').
쿼리를 다시 작성할 필요가 없습니다. 위 예제 쿼리와 같이 JSON_VALUE 함수가 포함된 식을 사용하는 경우 SQL Server는 같은 식을 사용하는 동일한 계산 열이 있는지 확인한 후 해당하는 경우 인덱스를 적용합니다.
이 예제의 실행 계획
다음은 이 예제의 쿼리에 대한 실행 계획입니다.
SQL Server는 전체 테이블을 검색하지 않고 비클러스터형 인덱스에서 인덱스 검색하여 지정된 조건을 충족하는 행을 찾습니다. 그런 다음, 테이블의 키 조회를 SalesOrderHeader 사용하여 쿼리에서 참조되는 다른 열(이 예제 SalesOrderNumber 에서는 )을 OrderDate가져옵니다.
포괄 열을 사용하여 추가로 인덱스 최적화
인덱스에 필요한 열을 추가하는 경우 테이블에서 이 추가 조회를 방지할 수 있습니다. 위의 CREATE INDEX 예제를 확장하는 다음 예제처럼 이러한 열을 표준형 포괄 열로 추가할 수 있습니다.
CREATE INDEX idx_soh_json_CustomerName
ON Sales.SalesOrderHeader(vCustomerName)
INCLUDE(SalesOrderNumber,OrderDate)
이 경우 SQL Server는 필요한 모든 것이 비클러스터형 JSON 인덱스로 포함되어 있으므로 테이블에서 추가 데이터를 SalesOrderHeader 읽을 필요가 없습니다. 이 유형의 인덱스는 쿼리에서 JSON 및 열 데이터를 결합하고 워크로드에 가장 적합한 인덱스를 만드는 좋은 방법입니다.
JSON 인덱스는 데이터 정렬 인식 인덱스입니다.
JSON 데이터에 대한 인덱스의 중요한 기능은 인덱스가 데이터 정렬을 인식한다는 것입니다. 계산 열을 만들 때 사용하는 함수의 JSON_VALUE 결과는 입력 식에서 데이터 정렬을 상속하는 텍스트 값입니다. 따라서 인덱스의 값은 원본 열에 정의된 데이터 정렬 규칙을 사용하여 정렬됩니다.
인덱스가 데이터 정렬을 인식한다는 것을 보여주기 위해 다음 예제에서는 기본 키와 JSON 콘텐츠가 있는 단순한 컬렉션 테이블을 만듭니다.
CREATE TABLE JsonCollection
(
id INT IDENTITY CONSTRAINT PK_JSON_ID PRIMARY KEY,
json NVARCHAR(MAX) COLLATE SERBIAN_CYRILLIC_100_CI_AI
CONSTRAINT [Content should be formatted as JSON]
CHECK(ISJSON(json)>0)
)
앞의 명령은 JSON 열에 대한 세르비아 키릴 자모 데이터 정렬을 지정합니다. 다음 예제에서는 테이블을 채우고 이름 속성에 인덱스를 만듭니다.
INSERT INTO JsonCollection
VALUES
(N'{"name":"Иво","surname":"Андрић"}'),
(N'{"name":"Андрија","surname":"Герић"}'),
(N'{"name":"Владе","surname":"Дивац"}'),
(N'{"name":"Новак","surname":"Ђоковић"}'),
(N'{"name":"Предраг","surname":"Стојаковић"}'),
(N'{"name":"Михајло","surname":"Пупин"}'),
(N'{"name":"Борислав","surname":"Станковић"}'),
(N'{"name":"Владимир","surname":"Грбић"}'),
(N'{"name":"Жарко","surname":"Паспаљ"}'),
(N'{"name":"Дејан","surname":"Бодирога"}'),
(N'{"name":"Ђорђе","surname":"Вајферт"}'),
(N'{"name":"Горан","surname":"Бреговић"}'),
(N'{"name":"Милутин","surname":"Миланковић"}'),
(N'{"name":"Никола","surname":"Тесла"}')
GO
ALTER TABLE JsonCollection
ADD vName AS JSON_VALUE(json,'$.name')
CREATE INDEX idx_name
ON JsonCollection(vName)
위의 명령은 JSON $.name 속성의 값을 나타내는 계산 열vName에 표준 인덱스를 만듭니다. 세르비아어 키릴 자모 코드 페이지에서 문자의 순서는 'А', ''','В','','Д','','Е'등입니다. 인덱스의 항목 순서는 함수의 JSON_VALUE 결과가 원본 열에서 데이터 정렬을 상속하기 때문에 세르비아 키릴 자모 규칙을 준수합니다. 다음 예제에서는 이 컬렉션을 쿼리하고 이름을 기준으로 결과를 정렬합니다.
SELECT JSON_VALUE(json,'$.name'),*
FROM JsonCollection
ORDER BY JSON_VALUE(json,'$.name')
실제 실행 계획을 보면 비클러스터형 인덱스에서 정렬된 값을 사용함을 확인할 수 있습니다.
쿼리에 절이 ORDER BY 있지만 실행 계획은 Sort 연산자를 사용하지 않습니다. JSON 인덱스는 이미 세르비아어 키릴 자모 규칙에 따라 정렬됩니다. 따라서 SQL Server는 결과가 이미 정렬된 비클러스터형 인덱스를 사용합니다.
그러나 식의 ORDER BY 데이터 정렬을 변경하는 경우(예: 함수 뒤를 JSON_VALUE 추가하는 COLLATE French_100_CI_AS_SC 경우) 다른 쿼리 실행 계획을 얻게 됩니다.
인덱스 값 순서는 프랑스어 데이터 정렬 규칙을 따르지 않으므로 SQL Server는 정렬 결과에 대한 인덱스를 사용할 수 없습니다. 따라서 프랑스어 데이터 정렬 규칙을 사용하여 결과를 정렬하는 Sort 연산자를 추가합니다.
다음 단계
Microsoft 비디오
참고 항목
이 섹션의 일부 비디오 링크는 현재 작동하지 않을 수 있습니다. Microsoft는 이전에 Channel 9에 있던 콘텐츠를 새 플랫폼으로 마이그레이션하고 있습니다. 비디오가 새 플랫폼으로 마이그레이션되면 링크를 업데이트할 예정입니다.
SQL Server 및 Azure SQL Database의 기본 제공 JSON 지원에 대한 시각적 소개는 다음 비디오를 참조하세요.
피드백
출시 예정: 2024년 내내 콘텐츠에 대한 피드백 메커니즘으로 GitHub 문제를 단계적으로 폐지하고 이를 새로운 피드백 시스템으로 바꿀 예정입니다. 자세한 내용은 다음을 참조하세요. https://aka.ms/ContentUserFeedback
다음에 대한 사용자 의견 제출 및 보기