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

아티클
07/12/2024

적용 대상: SQL Server 2017(14.x) 이상 Azure SQL 데이터베이스 Azure SQL Managed Instance

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

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

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

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 기술과 완전히 통합할 수 있습니다. 예를 들어 다음과 같은 작업을 수행할 수 있습니다.

고유하게 컴파일된 CHECK 제약 조건을 사용하여 메모리 최적화 테이블에 저장된 JSON 문서의 구조의 유효성을 검사합니다.
계산 열을 사용하여 JSON 문서에 저장된 값을 표시하고 강력하게 입력합니다.
메모리 최적화 인덱스를 사용하여 JSON 문서의 값을 인덱싱합니다.
JSON 문서의 값을 사용하거나 결과를 JSON 텍스트로 서식 지정하는 SQL 쿼리를 고유하게 컴파일합니다.

JSON 열 유효성 검사

문자열 열에 저장된 JSON 문서 내용을 검증하는 고유하게 컴파일된 CHECK 제약 조건을 추가하여 메모리 최적화 테이블에 저장된 JSON 텍스트의 서식이 올바르게 지정되었는지 확인할 수 있습니다.

다음 예에서는 ProductJSON 열이 있는 테이블Tags을 만듭니다. 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 열에서 다음과 같은 두 값을 노출하는 방법을 보여 줍니다.

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

이 예제에서는 계산된 열 MadeIn과 Cost가 열 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 값은 계산 열을 사용하여 노출되고 강력하게 형식화되어야 합니다.

표준 비클러스터형 및 해시 인덱스를 모두 사용하여 JSON 열의 값을 인덱싱할 수 있습니다.

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

다음 예제에서는 두 개의 계산 열을 사용하여 JSON 값을 노출하는 테이블을 작성합니다. 이 예제에서는 한 JSON 값에 비클러스터형 인덱스와 다른 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,
    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_VALUE, OPENJSON 및 JSON_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 데이터베이스에서 기본 제공 JSON 지원에 대한 시각적 소개는 다음 비디오를 참조하세요.

NoSQL과 관계형 세계 간의 브리지로서의 JSON

다음을 통해 공유