메모리 내 OLTP를 통해 JSON 처리 최적화

  • 아티클

적용 대상: SQL Server 2017(14.x) 이상 Azure SQL DatabaseAzure SQL Managed Instance

SQL Server 및 Azure SQL Database를 통해 JSON으로 서식이 지정된 텍스트로 작업할 수 있습니다. JSON 데이터를 처리하는 쿼리의 성능을 높이기 위해 표준 문자열 열(nvarchar 형식)을 사용하여 메모리 최적화 테이블에 JSON 문서를 저장할 수 있습니다. 메모리 최적화 테이블에 JSON 데이터를 저장하면 잠금 없는 메모리 내 데이터 액세스를 사용하여 쿼리 성능이 향상됩니다.

메모리 최적화 테이블에 JSON 저장

다음 예제에서는 두 개의 JSON 열이 있는 메모리 최적화 Product 테이블을 보여 줍니다Data. Tags

CREATE SCHEMA xtp;
GO

CREATE TABLE xtp.Product (
    ProductID INT PRIMARY KEY NONCLUSTERED, --standard column
    Name NVARCHAR(400) NOT NULL, --standard column
    Price FLOAT, --standard column
    Tags NVARCHAR(400), --JSON stored in string column
    Data NVARCHAR(4000) --JSON stored in string column
)
WITH (MEMORY_OPTIMIZED = ON);
GO

추가 메모리 내 기능을 사용하여 JSON 처리 최적화

JSON 기능을 기존 메모리 내 OLTP 기술과 완전히 통합할 수 있습니다. 예를 들어 다음과 같은 작업을 수행할 수 있습니다.

JSON 열 유효성 검사

문자열 열에 저장된 JSON 문서의 콘텐츠의 유효성을 검사하는 고유하게 컴파일된 CHECK 제약 조건을 추가하여 메모리 최적화 테이블에 저장된 JSON 텍스트의 형식이 올바르게 지정되도록 할 수 있습니다.

다음 예제에서는 JSON 열Tags이 있는 테이블을 만듭니다Product. 열에는 Tags 함수를 사용하여 열의 ISJSON JSON 텍스트의 유효성을 검사하는 CHECK 제약 조건이 있습니다.

DROP TABLE IF EXISTS xtp.Product;
GO
CREATE TABLE xtp.Product (
    ProductID INT PRIMARY KEY NONCLUSTERED,
    Name NVARCHAR(400) NOT NULL,
    Price FLOAT,
    Tags NVARCHAR(400)
        CONSTRAINT [Tags should be formatted as JSON] CHECK (ISJSON(Tags) = 1),
    Data NVARCHAR(4000)
)
WITH (MEMORY_OPTIMIZED = ON);
GO

고유하게 컴파일된 CHECK 제약 조건을 JSON 열이 포함된 기존 테이블에 추가할 수도 있습니다.

ALTER TABLE xtp.Product
    ADD CONSTRAINT [Data should be JSON]
        CHECK (ISJSON(Data)=1);

계산 열을 사용하여 JSON 값 노출

계산 열을 사용하면 JSON 텍스트의 값을 노출하고 JSON 텍스트에서 값을 다시 가져오지 않고 JSON 구조를 다시 구문 분석하지 않고 해당 값에 액세스할 수 있습니다. 이러한 방식으로 노출되는 값은 강력한 형식으로 계산된 열에 물리적으로 유지됩니다. 지속형 계산 열을 사용하여 JSON 값에 액세스하면 JSON 문서의 값에 직접 액세스하는 것보다 더 빠릅니다.

다음 예제에서는 JSON Data 열에서 다음과 같은 두 값을 노출하는 방법을 보여 줍니다.

  • 제품이 만들어지는 국가/지역입니다.
  • 제품 제조 비용.

이 예제에서는 계산된 열이 열 MadeInCost 에 저장된 Data JSON 문서가 변경될 때마다 업데이트됩니다.

DROP TABLE IF EXISTS xtp.Product;
GO
CREATE TABLE xtp.Product (
    ProductID INT PRIMARY KEY NONCLUSTERED,
    Name NVARCHAR(400) NOT NULL,
    Price FLOAT,
    Data NVARCHAR(4000),
    MadeIn AS CAST(JSON_VALUE(Data, '$.MadeIn') AS NVARCHAR(50)) PERSISTED,
    Cost AS CAST(JSON_VALUE(Data, '$.ManufacturingCost') AS FLOAT) PERSISTED
)
WITH (MEMORY_OPTIMIZED = ON);
GO

JSON 열의 인덱스 값

메모리 최적화 인덱스를 사용하여 JSON 열의 값을 인덱싱할 수 있습니다. 앞의 예제에 설명된 대로 인덱싱된 JSON 값은 계산 열을 사용하여 노출되고 강력한 형식이어야 합니다.

표준 NONCLUSTERED 및 HASH 인덱스를 모두 사용하여 JSON 열의 값을 인덱싱할 수 있습니다.

  • NONCLUSTERED 인덱스는 일부 JSON 값으로 행 범위를 선택하거나 JSON 값을 기준으로 결과를 정렬하는 쿼리를 최적화합니다.
  • HASH 인덱스는 찾을 정확한 값을 지정하여 단일 행 또는 몇 개의 행을 선택하는 쿼리를 최적화합니다.

다음 예제에서는 두 개의 계산 열을 사용하여 JSON 값을 노출하는 테이블을 작성합니다. 이 예제에서는 한 JSON 값에 비클러스터형 인덱스와 다른 JSON 값에 HASH 인덱스가 만들어집니다.

DROP TABLE IF EXISTS xtp.Product;
GO
CREATE TABLE xtp.Product (
    ProductID INT PRIMARY KEY NONCLUSTERED,
    Name NVARCHAR(400) NOT NULL,
    Price FLOAT,
    Data NVARCHAR(4000),
    MadeIn AS CAST(JSON_VALUE(Data, '$.MadeIn') AS NVARCHAR(50)) PERSISTED,
    Cost AS CAST(JSON_VALUE(Data, '$.ManufacturingCost') AS FLOAT) PERSISTED,
    INDEX [idx_Product_MadeIn] NONCLUSTERED (MadeIn)
)
WITH (MEMORY_OPTIMIZED = ON);
GO

ALTER TABLE Product ADD INDEX [idx_Product_Cost] NONCLUSTERED HASH (Cost)
    WITH (BUCKET_COUNT = 20000);

JSON 쿼리의 네이티브 컴파일

프로시저, 함수 및 트리거에 기본 제공 JSON 함수를 사용하는 쿼리가 포함된 경우 네이티브 컴파일은 이러한 쿼리의 성능을 높이고 실행하는 데 필요한 CPU 주기를 줄입니다.

다음 예제에서는 여러 JSON 함수를 사용하는 고유하게 컴파일된 프로시저를 보여 줍니다. JSON_VALUEOPENJSONJSON_MODIFY

CREATE PROCEDURE xtp.ProductList (@ProductIds NVARCHAR(100))
WITH SCHEMABINDING, NATIVE_COMPILATION
AS BEGIN
    ATOMIC WITH (TRANSACTION ISOLATION LEVEL = snapshot, LANGUAGE = N'English')

    SELECT ProductID,
        Name,
        Price,
        Data,
        Tags,
        JSON_VALUE(data, '$.MadeIn') AS MadeIn
    FROM xtp.Product
    INNER JOIN OPENJSON(@ProductIds)
        ON ProductID = value
END;
GO

CREATE PROCEDURE xtp.UpdateProductData (
    @ProductId INT,
    @Property NVARCHAR(100),
    @Value NVARCHAR(100)
)
WITH SCHEMABINDING, NATIVE_COMPILATION
AS BEGIN
    ATOMIC WITH (TRANSACTION ISOLATION LEVEL = snapshot, LANGUAGE = N'English')

    UPDATE xtp.Product
    SET Data = JSON_MODIFY(Data, @Property, @Value)
    WHERE ProductID = @ProductId;
END
GO

다음 단계

SQL Server 및 Azure SQL Database의 기본 제공 JSON 지원에 대한 시각적 소개는 다음 비디오를 참조하세요.