使用記憶體內部 OLTP 最佳化 JSON 處理

發行項
05/04/2023

適用於： SQL Server 2017 (14.x) 和更新版本 Azure SQL 資料庫 Azure SQL 受控執行個體

SQL Server 和 Azure SQL Database 可讓您使用格式化為 JSON 的文字。若要提升處理 JSON 資料之查詢的效能，您可以使用標準字串資料行 (nvarchar 類型)，將 JSON 文件儲存到經記憶體最佳化的資料表中。將 JSON 資料儲存在經記憶體最佳化的資料表時，透過利用無鎖定的記憶體內部資料存取，即可提高查詢效能。

將 JSON 儲存到記憶體最佳化資料表

下列範例示範具有 Tags 和 Data 這兩個 JSON 資料行的記憶體最佳化 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 文件中值的索引。
原生編譯 SQL 查詢，以使用 JSON 文件中的值，或將結果格式化為 JSON 文字。

驗證 JSON 資料行

您可以新增原生編譯的 CHECK 條件限制式，以驗證儲存在字串資料行中的 JSON 文件內容，以確保儲存在經記憶體最佳化的資料表中的 JSON 文字格式正確。

下列範例會建立一份含有 JSON 資料行 Tags 的 Product 資料表。 Tags 資料行具有 CHECK 條件約束，使用 ISJSON 函式來驗證資料行中的 JSON 文字。

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 資料行的兩個值：

產品的製作國家/地區。
產品製造成本。

在此範例中，每次 Data 資料行中所儲存的 JSON 文件變更時，都會更新計算資料行 MadeIn 和 Cost。

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 值上建立 NONCLUSTERED 索引，並在另一個值上建立 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_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 Database 中內建 JSON 支援的觀看式簡介，請參閱下列影片：

NoSQL 與關聯式領域之間的橋樑 JSON

共用方式為