Udostępnij za pomocą

Optymalizowanie przetwarzania JSON przy użyciu olTP w pamięci

Dotyczy: SQL Server 2017 (14.x) i nowsze wersjeAzure SQL DatabaseAzure SQL Managed Instancebaza danych SQL w Microsoft Fabric

Program SQL Server i usługa Azure SQL Database umożliwiają pracę z tekstem sformatowanymi w formacie JSON. Aby zwiększyć wydajność zapytań, które przetwarzają dane JSON, można przechowywać dokumenty JSON w tabelach zoptymalizowanych pod kątem pamięci przy użyciu standardowych kolumn ciągów (nvarchar type). Przechowywanie danych JSON w tabelach zoptymalizowanych pod kątem pamięci zwiększa wydajność zapytań przy użyciu dostępu do danych bez blokady w pamięci.

Przechowywanie danych JSON w tabelach zoptymalizowanych pod kątem pamięci

W poniższym przykładzie przedstawiono tabelę Product zoptymalizowaną pod kątem pamięci z dwiema kolumnami JSON, Tags i Data:

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

Optymalizowanie przetwarzania JSON przy użyciu dodatkowych funkcji w pamięci

Możesz w pełni zintegrować funkcje JSON z istniejącymi technologiami OLTP w pamięci. Można na przykład wykonać następujące czynności:

Weryfikowanie kolumn JSON

Możesz dodać natywnie skompilowane ograniczenia CHECK, które weryfikują zawartość dokumentów JSON przechowywanych w kolumnie ciągu, aby upewnić się, że tekst JSON przechowywany w tabelach zoptymalizowanych pod kątem pamięci jest poprawnie sformatowany.

Poniższy przykład tworzy tabelę Product z kolumną JSON Tags. Kolumna Tags ma ograniczenie CHECK, które używa funkcji ISJSON do sprawdzania poprawności tekstu JSON w kolumnie.

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

Możesz również dodać natywnie skompilowane ograniczenie CHECK do istniejącej tabeli zawierającej kolumny JSON.

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

Uwidacznianie wartości JSON przy użyciu obliczonych kolumn

Obliczone kolumny umożliwiają uwidacznianie wartości z tekstu JSON i uzyskiwanie dostępu do tych wartości bez ponownego pobierania wartości z tekstu JSON i bez ponownego analizowania struktury JSON. Wartości, które zostały uwidocznione w ten sposób, są mocno typizowane i fizycznie utrwalane w obliczonych kolumnach. Uzyskiwanie dostępu do wartości JSON przy użyciu utrwanych kolumn obliczeniowych jest szybsze niż uzyskiwanie bezpośredniego dostępu do wartości w dokumencie JSON.

W poniższym przykładzie pokazano, jak uwidocznić następujące dwie wartości z kolumny Data JSON:

  • Kraj/region, w którym jest wykonany produkt.
  • Koszt produkcji produktu.

W tym przykładzie obliczone kolumny MadeIn i Cost są aktualizowane za każdym razem, gdy dokument JSON przechowywany w kolumnie Data się zmienia.

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

Indeksy wartości w kolumnach JSON

Wartości w kolumnach JSON można indeksować przy użyciu indeksów zoptymalizowanych pod kątem pamięci. Wartości JSON, które są indeksowane, muszą być uwidocznione i silnie typizowane przy użyciu obliczonych kolumn, zgodnie z opisem w poprzednim przykładzie.

Wartości w kolumnach JSON można indeksować przy użyciu zarówno standardowych indeksów NONCLUSTERED, jak i HASH.

  • Indeksy NONCLUSTERED optymalizują zapytania, które wybierają zakresy wierszy według pewnych wartości JSON lub sortują wyniki według wartości JSON.
  • Indeksy skrótów optymalizują zapytania, które wybierają jeden lub kilka wierszy, podając dokładną wartość, jaką należy znaleźć.

W poniższym przykładzie utworzono tabelę, która uwidacznia wartości JSON przy użyciu dwóch obliczonych kolumn. W tym przykładzie tworzony jest indeks NONCLUSTERED dla jednej wartości JSON oraz indeks HASH dla drugiej wartości 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);

Natywna kompilacja zapytań JSON

Jeśli procedury, funkcje i wyzwalacze zawierają zapytania korzystające z wbudowanych funkcji JSON, kompilacja natywna zwiększa wydajność tych zapytań i zmniejsza cykle procesora CPU wymagane do ich uruchomienia.

Poniższy przykład przedstawia natywnie skompilowaną procedurę, która używa kilku funkcji JSON: JSON_VALUE, OPENJSONi 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

Treści powiązane

Aby zapoznać się z wizualnym wprowadzeniem do wbudowanej obsługi kodu JSON, zobacz następujące wideo:

  • Last updated on