Оптимизация обработки JSON с помощью выполняющейся в памяти OLTP

  • Статья

Область применения: SQL Server 2017 (14.x) и более поздних версий Управляемого экземпляра Базы данныхSQL Azure SQL Azure

База данных SQL Azure и SQL Server позволяют работать с текстами в формате JSON. Чтобы повысить производительность запросов, обрабатывающих данные JSON, можно хранить документы JSON в таблицах, оптимизированных для памяти, с помощью стандартных строковых столбцов (тип nvarchar ). Хранение данных JSON в таблицах, оптимизированных для памяти, повышает производительность запросов с помощью доступа к данным без блокировки в памяти.

Хранение документов JSON в таблицах, оптимизированных для памяти

В примере ниже показана таблица Product, оптимизированная для памяти, с двумя столбцами JSON — Tags и 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

Оптимизация обработки JSON с помощью дополнительных функций выполнения в памяти

Вы можете полностью интегрировать функции JSON с существующими технологиями OLTP в памяти. Вы сможете выполнять следующее:

Проверка столбцов JSON

Вы можете добавить встроенные скомпилированные ограничения CHECK, которые проверяют содержимое документов JSON, хранящихся в строковом столбце, чтобы обеспечить правильное форматирование текста JSON, хранящегося в таблицах, оптимизированных для памяти.

В следующем примере создается таблица Product с JSON-столбцом Tags. Столбец 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:

  • Страна или регион, где производится продукт.
  • себестоимость производства товара.

В этом примере вычисляемые столбцы MadeIn и Cost обновляются каждый раз, когда изменяется документ JSON, сохраненный в столбце Data.

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 и хэш-индекс — в другом.

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, компиляция в машинный код повышает производительность этих запросов и снижает количество циклов ЦП, необходимых для их выполнения.

В следующем примере показана скомпилированная в собственном коде процедура, использующая несколько функций 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

Далее

Наглядные инструкции по встроенной поддержке JSON в SQL Server и базе данных SQL Azure см. в следующих видео.