Индексирование данных JSONIndex JSON data

Применимо к:Applies to: даSQL ServerSQL Server (все поддерживаемые версии) yesSQL ServerSQL Server (all supported versions) ДаБаза данных SQL AzureAzure SQL DatabaseYesБаза данных SQL AzureAzure SQL DatabaseПрименимо к:Applies to: даSQL ServerSQL Server (все поддерживаемые версии) yesSQL ServerSQL Server (all supported versions) ДаБаза данных SQL AzureAzure SQL DatabaseYesБаза данных SQL AzureAzure SQL Database

JSON не является встроенным типом данных в SQL Server и базе данных SQL, а SQL Server не имеет пользовательских индексов JSON.In SQL Server and SQL Database, JSON is not a built-in data type, and SQL Server does not have custom JSON indexes. Запросы к документам JSON можно оптимизировать с помощью обычных индексов.You can optimize your queries over JSON documents, however, by using standard indexes.

Индексы баз данных делают операции фильтрации и сортировки более эффективными.Database indexes improve the performance of filter and sort operations. Без индексов SQL Server пришлось бы сканировать всю таблицу при каждом запросе данных.Without indexes, SQL Server has to perform a full table scan every time you query data.

Индексация свойств JSON с помощью вычисляемых столбцовIndex JSON properties by using computed columns

При хранении данных JSON в SQL Server результаты запросов обычно нужно фильтровать или сортировать по одному или нескольким свойствам документов JSON.When you store JSON data in SQL Server, typically you want to filter or sort query results by one or more properties of the JSON documents.

ПримерExample

В этом примере предполагается, что таблица AdventureWorks SalesOrderHeader включает столбец Info, содержащий различные сведения в формате JSON о заказах на продажу.In this example, assume that the AdventureWorks SalesOrderHeader table has an Info column that contains various information in JSON format about sales orders. Например, он содержит сведения о клиенте и менеджере по продажам, адреса доставки и выставления счетов и т. д.For example, it contains information about customer, sales person, shipping and billing addresses, and so forth. Значения из столбца Info можно использовать для фильтрования заказов на продажу по клиентам.You want to use values from the Info column to filter sales orders for a customer.

Запрос оптимизацииQuery to optimize

С помощью индекса можно оптимизировать запросы, например, указанного ниже типа.Here's an example of the type of query that you want to optimize by using an index.

SELECT SalesOrderNumber,
    OrderDate,
    JSON_VALUE(Info, '$.Customer.Name') AS CustomerName
FROM Sales.SalesOrderHeader
WHERE JSON_VALUE(Info, '$.Customer.Name') = N'Aaron Campbell' 

Пример индексаExample index

Чтобы ускорить применение фильтров или предложений ORDER BY к свойству в документе JSON, можно применить те же индексы, которые уже используются в других столбцах.If you want to speed up your filters or ORDER BY clauses over a property in a JSON document, you can use the same indexes that you're already using on other columns. При этом напрямую на свойства в документах JSON ссылаться нельзя.However, you can't directly reference properties in the JSON documents.

  1. Сначала необходимо создать "виртуальный столбец", возвращающий значения, которые нужно использовать для фильтрации.First, you have to create a "virtual column" that returns the values that you want to use for filtering.
  2. а затем создать индекс в этом виртуальном столбце.Then you have to create an index on that virtual column.

В следующем примере создается вычисляемый столбец, который можно использовать для индексирования.The following example creates a computed column that can be used for indexing. Затем в новом вычисляемом столбце создается индекс.Then it creates an index on the new computed column. В этом примере создается столбец, содержащий имя клиента, которое хранится в документах JSON по пути $.Customer.Name.This example creates a column that exposes the customer name, which is stored in the $.Customer.Name path in the JSON data.

ALTER TABLE Sales.SalesOrderHeader
ADD vCustomerName AS JSON_VALUE(Info,'$.Customer.Name')

CREATE INDEX idx_soh_json_CustomerName
ON Sales.SalesOrderHeader(vCustomerName)  

Дополнительные сведения о вычисляемых столбцахMore info about the computed column

Вычисляемый столбец не сохраняетсяThe computed column is not persisted. Он вычисляется только в том случае, если индекс необходимо перестроить.It's computed only when the index needs to be rebuilt. и не занимает дополнительное место в таблице.It does not occupy additional space in the table.

Вычисляемый столбец необходимо создавать с тем же выражением, которое вы планируете использовать в запросах — в данном случае выражение JSON_VALUE(Info, '$.Customer.Name').It's important that you create the computed column with the same expression that you plan to use in your queries - in this example, the expression is JSON_VALUE(Info, '$.Customer.Name').

Запросы переписывать не нужно.You don't have to rewrite your queries. Если выражения используются с функцией JSON_VALUE, как показано в предыдущем примере запроса, SQL Server определяет наличие эквивалентного вычисляемого столбца с тем же выражением и по возможности применяет индекс.If you use expressions with the JSON_VALUE function, as shown in the preceding example query, SQL Server sees that there's an equivalent computed column with the same expression and applies an index if possible.

План выполнения для этого примераExecution plan for this example

Вот план выполнения запроса в этом примере:Here's the execution plan for the query in this example.

Снимок экрана: план выполнения для этого примера.Screenshot showing the execution plan for this example.

Вместо полного табличного сканирования SQL Server применяет оператор Index Seek к некластеризованному индексу и выявляет строки, отвечающие указанным условиям.Instead of a full table scan, SQL Server uses an index seek into the nonclustered index and finds the rows that satisfy the specified conditions. Затем он выполняет поиск ключей по таблице SalesOrderHeader, чтобы получить другие указанные в запросе столбцы — в этом примере это столбцы SalesOrderNumber и OrderDate.Then it uses a key lookup in the SalesOrderHeader table to fetch the other columns that are referenced in the query - in this example, SalesOrderNumber and OrderDate.

Дополнительная оптимизация индекса с включенными столбцамиOptimize the index further with included columns

При добавлении требуемых столбцов в индекс такого дополнительного поиска по таблице можно избежать.If you add required columns in the index, you can avoid this additional lookup in the table. Эти столбцы можно добавить как стандартные включенные столбцы, как показано в следующем примере, дополняющем предыдущий пример CREATE INDEX.You can add these columns as standard included columns, as shown in the following example, which extends the preceding CREATE INDEX example.

CREATE INDEX idx_soh_json_CustomerName
ON Sales.SalesOrderHeader(vCustomerName)
INCLUDE(SalesOrderNumber,OrderDate)

В данном случае SQL Server не считывает дополнительные данные из таблицы SalesOrderHeader, так как все необходимое уже включено в некластеризованный индекс JSON.In this case SQL Server doesn't have to read additional data from the SalesOrderHeader table because everything it needs is included in the nonclustered JSON index. Этот тип индекса является хорошим способом объединения данных JSON и столбцов в запросах и создания оптимальных индексов для вашей рабочей нагрузки.This type of index is a good way to combine JSON and column data in queries and to create optimal indexes for your workload.

Индексы JSON — это индексы с учетом сортировкиJSON indexes are collation-aware indexes

Важной особенностью индексов на основе JSON является то, что эти индексы учитывают параметры сортировки.An important feature of indexes over JSON data is that the indexes are collation-aware. Результатом выполнения функции JSON_VALUE, которая применяется при создании вычисляемого столбца, является текстовое значение, которое наследует параметры сортировки из входного выражения.The result of the JSON_VALUE function that you use when you create the computed column is a text value that inherits its collation from the input expression. Таким образом, значения в индексе упорядочиваются согласно правилам сортировки, определенным в исходных столбцах.Therefore, values in the index are ordered using the collation rules defined in the source columns.

Чтобы продемонстрировать, что индексы учитывают параметры сортировки, в следующем примере создается простая таблица коллекций с первичным ключом и данными в формате JSON.To demonstrate that the indexes are collation-aware, the following example creates a simple collection table with a primary key and JSON content.

CREATE TABLE JsonCollection
 (
  id INT IDENTITY CONSTRAINT PK_JSON_ID PRIMARY KEY,
  json NVARCHAR(MAX) COLLATE SERBIAN_CYRILLIC_100_CI_AI
  CONSTRAINT [Content should be formatted as JSON]
  CHECK(ISJSON(json)>0)
 ) 

Указанная выше команда задает для столбца JSON сортировку по сербской кириллице.The preceding command specifies the Serbian Cyrillic collation for the JSON column. В следующем примере таблица заполняется и создается индекс по свойству name.The following example populates the table and creates an index on the name property.

INSERT INTO JsonCollection
VALUES
(N'{"name":"Иво","surname":"Андрић"}'),
(N'{"name":"Андрија","surname":"Герић"}'),
(N'{"name":"Владе","surname":"Дивац"}'),
(N'{"name":"Новак","surname":"Ђоковић"}'),
(N'{"name":"Предраг","surname":"Стојаковић"}'),
(N'{"name":"Михајло","surname":"Пупин"}'),
(N'{"name":"Борислав","surname":"Станковић"}'),
(N'{"name":"Владимир","surname":"Грбић"}'),
(N'{"name":"Жарко","surname":"Паспаљ"}'),
(N'{"name":"Дејан","surname":"Бодирога"}'),
(N'{"name":"Ђорђе","surname":"Вајферт"}'),
(N'{"name":"Горан","surname":"Бреговић"}'),
(N'{"name":"Милутин","surname":"Миланковић"}'),
(N'{"name":"Никола","surname":"Тесла"}')
GO
  
ALTER TABLE JsonCollection
ADD vName AS JSON_VALUE(json,'$.name')

CREATE INDEX idx_name
ON JsonCollection(vName)

Указанные выше команды создают стандартный индекс по значению vName вычисляемого столбца, которое соответствует свойству $.name.The preceding commands create a standard index on the computed column vName, which represents the value from the JSON $.name property. На странице кода сербской кириллицы буквы сортируются в следующем порядке: "А", "Б", "В", "Г", "Д", "Ђ", "Е" и т. д. Порядок элементов в индексе соответствует правилам сербской кириллицы, так как результат функции JSON_VALUE наследует параметры сортировки из исходного столбца.In the Serbian Cyrillic code page, the order of the letters is 'А','Б','В','Г','Д','Ђ','Е', etc. The order of items in the index is compliant with Serbian Cyrillic rules because the result of the JSON_VALUE function inherits its collation from the source column. Следующий пример запрашивает эту коллекцию и сортирует результаты по имени.The following example queries this collection and sorts the results by name.

SELECT JSON_VALUE(json,'$.name'),*
FROM JsonCollection
ORDER BY JSON_VALUE(json,'$.name')

Если посмотреть на фактический план выполнения, можно увидеть, что в нем используются отсортированные значения из некластеризованного индекса.If you look at the actual execution plan, you see that it uses sorted values from the nonclustered index.

Снимок экрана: план выполнения, в котором используются отсортированные значения из некластеризованного индекса.Screenshot showing an execution plan that uses sorted values from the non-clustered index.

Несмотря на то что запрос содержит предложение ORDER BY, в плане выполнения не используется оператор Sort.Although the query has an ORDER BY clause, the execution plan doesn't use a Sort operator. Индекс JSON уже упорядочен по правилам сербской кириллицы.The JSON index is already ordered according to Serbian Cyrillic rules. В связи с этим SQL Server может использовать некластеризованный индекс, в котором результаты уже отсортированы.Therefore SQL Server can use the nonclustered index where results are already sorted.

Но если порядок сортировки по выражению ORDER BY изменить, например добавить COLLATE French_100_CI_AS_SC после функции JSON_VALUE, план выполнения запроса станет совершенно иным.However, if you change the collation of the ORDER BY expression - for example, if you add COLLATE French_100_CI_AS_SC after the JSON_VALUE function - you get a different query execution plan.

Снимок экрана: другой план выполнения.Screenshot showing a different execution plan.

Поскольку порядок значений в индексе не соответствует правилам сортировки для французского языка, SQL Server не может использовать этот индекс для упорядочивания результатов.Since the order of values in the index is not compliant with French collation rules, SQL Server can't use the index to order results. В связи с этим он добавляет оператор Sort, который сортирует результаты по правилам сортировки для французского языка.Therefore, it adds a Sort operator that sorts results using French collation rules.

Дополнительные сведения о JSON в SQL Server и базе данных SQL AzureLearn more about JSON in SQL Server and Azure SQL Database

Видео МайкрософтMicrosoft videos

Наглядные инструкции по встроенной поддержке JSON в SQL Server и базе данных SQL Azure см. в следующих видео.For a visual introduction to the built-in JSON support in SQL Server and Azure SQL Database, see the following videos: