Структуры хранения #6 – JSON-индексы

Автор: Hugo Kornelis, Storage structures 6 – JSON indexes;

Пришло время продолжить серию статей о внутреннем устройстве структур хранения. Я уже рассмотрел более стандартные типы хранения в частях, посвящённых дисковому строчному хранению, columnstore индексам, оптимизированным для памяти структурам и оптимизированным для памяти columnstore, а в прошлом выпуске этой серии я начал рассматривать специализированные структуры хранения, изучив XML-индексы.

Microsoft представила ограниченную поддержку JSON в SQL Server 2016. Однако только в SQL Server 2025 появились собственный тип данных json и JSON-индексы. Итак, давайте посмотрим, как они работают «под капотом».

До сих пор я не работал с JSON-данными. Поэтому у меня ещё нет демонстрационных баз данных с данными этого типа. Поскольку я ленив, я попросил Redgate Assistant (новый ознакомительный ИИ-инструмент, входящий в состав Redgate SQLPrompt) сгенерировать для меня скрипт. Потребовалось несколько попыток, прежде чем он сделал это правильно, и полученные данные довольно нереалистичны, потому что он выбрал столбцы, которые на самом деле не содержат данные, предлагаемые схемой JSON, но для демонстрации это сойдёт. Также приношу извинения за длинный скрипт — я попросил Redgate Assistant сделать структуру JSON более интересной после того, как он сначала создал базовый вариант, и он действительно перестарался. Но я решил оставить всё как есть, чтобы у меня была интересная демонстрационная таблица для экспериментов. Чтобы ограничить размер, Redgate Assistant добавил TOP 25 (что, конечно, должно было быть TOP (25)).

Из-за длины скрипта (почти 400 строк!) я решил не вставлять SQL сюда. Вместо этого я загрузил его на свой сайт. Пожалуйста, нажмите эту ссылку, чтобы загрузить скрипт настройки.

Создание индекса

Учитывая сходство между XML и JSON, я ожидаю, что JSON-индексы также будут иметь сходство с XML-индексами. И хотя это действительно так, есть и большие различия. Мы сразу видим первое, когда хотим создать индекс. Синтаксис CREATE JSON INDEX вообще не упоминает первичные или вторичные индексы. Вместо этого он включает только одну опцию, специфичную для этого типа индекса: OPTIMIZE_FOR_ARRAY_SEARCH.

Не оптимизирован для поиска по массивам

Значение по умолчанию для OPTIMIZE_FOR_ARRAY_SEARCH — off. Итак, давайте сначала создадим индекс с этой опцией по умолчанию.

CREATE JSON INDEX ix_json_CustomerProfile_ProfileData
ON dbo.CustomerProfile (ProfileData);

Когда вы выполните это с включённой опцией «включить план выполнения» плюс среда выполнения (он же «фактический» план выполнения), вы увидите не один, а два плана выполнения:

Первый план выполнения не требует особых объяснений. Верхнее левое Clustered Index Scan считывает CustomerID (первичный ключ) и ProfileData (индексированный JSON-столбец) из таблицы. Затем Nested Loops передаёт ProfileData в оператор Table Valued Function, который анализирует JSON и возвращает таблицу с одной строкой для каждого узла в JSON с шестью столбцами: path, array_indexes, value, status, full_path и full_value. Мы вернёмся к этим столбцам позже. Оптимизатор ожидает 50 узлов на входную строку; я предполагаю, что это жёстко заданная оценка. В этом случае фактическое среднее значение ещё больше: 121 на входную строку.

Полученные семь столбцов (шесть из Table Valued Function плюс CustomerID) затем передаются в оператор Sort, который упорядочивает их по path, array_indexes и value, чтобы оптимизировать обработку оператора JSON Index Insert, который, как следует из названия, вставляет полученные строки в новый индекс с именем ix_json_CustomerProfile_ProfileData (имя, которое я указал), но определённый на таблице с именем json_index_874134555_1216000 (числа, скорее всего, будут другими в вашей системе). Это выдаёт, что JSON-индексы используют тот же метод создания и индексации внутренней таблицы узлов, которая хранит «разобранное» представление JSON с одной строкой для каждого узла.

Второй план выполнения затем начинается с другого Clustered Index Scan, на этот раз читающего кластерный индекс, который мы только что создали. Он возвращает пять столбцов: Uniq1001 (это столбец уникализации, что выдаёт, что кластерный индекс, созданный в первом плане выполнения, определён как неуникальный), json_path, json_array_index, sql_value и posting_1. Опять же, я вернусь к столбцам таблицы узлов позже.

Затем эти данные сортируются по posting_1, json_path, json_array_index, sql_value и Uniq1001 перед вставкой в другой JSON-индекс, также на той же внутренней таблице узлов. В моей системе он назван json_index_posting_col_nci. И я подозреваю, что в вашей тоже.

На первый взгляд создание JSON-индекса имеет эффект, аналогичный созданию первичного XML-индекса плюс одного вторичного XML-индекса, но без выбора типа вторичного индекса.

Оптимизирован для поиска по массивам

Давайте теперь также посмотрим на планы выполнения для CREATE JSON INDEX, который добавляет предложение OPTIMIZE_FOR_ARRAY_SEARCH = ON. К счастью, Redgate Assistant подумал наперёд и дал мне таблицу с двумя JSON-столбцами, поэтому на этот раз мы можем использовать другой (SQL Server не позволяет создавать два JSON-индекса на одном столбце).

CREATE JSON INDEX ix_json_CustomerProfile_ActivityData
ON dbo.CustomerProfile (ActivityData)
WITH (OPTIMIZE_FOR_ARRAY_SEARCH = ON);

В этом случае мы видим даже три плана выполнения. Первые два являются точными копиями планов выполнения выше, за исключением того, что теперь используется столбец ActivityData, а внутренняя таблица узлов называется json_index_874134555_1216001. Вы заметите сходство с тем, как SQL Server называет таблицы узлов для XML-индексов.

Третий план выполнения затем выглядит как копия второго. Однако имя созданного индекса другое: json_index_search_optimization_nci. Кроме того, Clustered Index Scan теперь возвращает ещё один столбец из таблицы узлов: status; и Sort затем применяет другой порядок сортировки: json_path, sql_value, json_array_index, status и Uniq1001. Как и в предыдущих планах, это делается для оптимизации вставок, так что это будет соответствовать индексированным столбцам, что, надеюсь, будет подтверждено ниже, когда мы рассмотрим структуру различных таблиц.

Таким образом, можно сказать, что добавление предложения OPTIMIZE_FOR_ARRAY_SEARCH = ON имеет эффект, аналогичный созданию дополнительного вторичного XML-индекса для XML-индексов.

Структура хранения

Как показано выше, реализация JSON-индекса на высоком уровне очень похожа на XML-индексы: внутренняя и скрытая «таблица узлов» с кластерным индексом и одним или двумя дополнительными некластерными индексами. Однако столбцы в таблице узлов и определения индексов отличаются.

Запрос ниже выводит детали таблиц узлов и их индексов для любой таблицы, имеющей один или несколько индексированных XML- или JSON-столбцов. (Вы можете дополнительно добавить OR o.object_id = OBJECT_ID ('dbo.CustomerProfile'), чтобы также получить информацию об обычных индексах в той же таблице).

SELECT     o.object_id               AS [Object ID],
           SCHEMA_NAME (o.schema_id) AS [Schema name],
           o.name                    AS [Object name],
           o.parent_object_id        AS [Parent object ID],
           o.type_desc               AS [Object type],
           i.name                    AS [Index name],
           i.type_desc               AS [Index type]
FROM       sys.indexes AS i
INNER JOIN sys.objects AS o
   ON      o.object_id = i.object_id
WHERE      o.parent_object_id = OBJECT_ID ('dbo.CustomerProfile');

Вот результат, который я получил в своей системе. Опять же, значения Object ID и, следовательно, Object name будут другими в вашей системе.

Таблица узлов

Чтобы узнать, какие столбцы включены в таблицу узлов и как определён кластерный индекс, мы можем использовать тот же запрос, который я использовал в предыдущем выпуске:

SELECT     c.name         AS ColumnName,
           t.name         AS Datatype,
           CASE
               WHEN c.max_length = -1 THEN
                   'MAX'
               ELSE
                   CAST (c.max_length AS varchar(30))
           END            AS MaxLength,
           ic.key_ordinal AS [Clustered index column]
FROM       sys.indexes       AS i
INNER JOIN sys.columns       AS c
   ON      c.object_id    = i.object_id
INNER JOIN sys.types         AS t
   ON      t.user_type_id = c.user_type_id
LEFT JOIN  sys.index_columns AS ic
  ON       ic.object_id   = i.object_id
  AND      ic.index_id    = i.index_id
  AND      ic.column_id   = c.column_id
WHERE      i.name = N'ix_json_CustomerProfile_ActivityData'
AND        i.type_desc   = N'CLUSTERED'
ORDER BY   c.column_id;

Вот результаты:

Поскольку это внутренняя таблица, к ней нельзя обратиться напрямую. Если только я не подключусь через выделенное административное соединение (DAC). На рабочих серверах это соединение следует использовать только в случае крайней необходимости, для восстановления после определённых аварийных ситуаций, когда обычные подключения больше невозможны. Но на моём тестовом и демонстрационном ноутбуке я думаю, что могу оправдать использование DAC для изучения внутреннего устройства этой таблицы. Вот скриншот, показывающий часть вывода.

Если вы сравните этот вывод с JSON в столбце ActivityData строки с CustomerID 1, вы можете начать интерпретировать, что означают некоторые из этих столбцов.

Моё первое наблюдение — интересное отличие от XML-индексов. В то время как таблица узлов XML включает строку для каждой строки, таблица узлов JSON содержит только строку для каждой пары «имя/значение». Пустые элементы, такие как пустой массив "orders" на скриншоте выше, полностью отсутствуют в таблице узлов.

На основе сравнения данных в таблице узлов с JSON, а также некоторых дополнительных экспериментов, которые я не буду подробно описывать, я вывел следующие значения для столбцов в таблице узлов:

Столбец	Описание
json_path	Путь к паре «имя/значение», где все части разделены точками, имена массивов дополняются символом #, а последняя часть всегда является именем пары «имя/значение».
json_array_index	Для элементов в массиве это номер (с нуля), указывающий порядковую позицию в массиве. Для элементов не в массиве — NULL. Для вложенных массивов индекс вычисляется путём сдвига каждого следующего уровня влево на 8 байт и выполнения побитового OR (что эквивалентно умножению каждого следующего уровня на 4 294 967 296 и сложению значений). Тип данных varbinary(512) позволяет иметь 4 294 967 296 элементов в каждом массиве и 64 уровня вложенности. Я не тестировал, что происходит при превышении этих лимитов. Однако я попробовал запрос, который запрашивает 4 294 967 297-й элемент, и это вызывает ошибку. Поэтому я предполагаю, что JSON-строка, содержащая массив с более чем 4 294 967 296 элементами, также вызовет ошибку.
sql_value	Это значение в паре «имя/значение» или значение литерала массива.
status	Я думаю, это представляет тип данных значения в JSON-строке. Значения, которые я наблюдал до сих пор: 0 для числовых данных (и значений null), 1 для логических данных, 2 для данных больших объектов и 4 для строковых данных.
full_json_path	Если полный путь к паре «имя/значение» превышает 630 символов, то столбец json_path хранит усечённую версию пути. Полный путь затем хранится в этом столбце. Если полный путь составляет 630 или менее символов, этот столбец остаётся NULL.
full_json_value	Если полное значение пары «имя/значение» превышает пределы хранения типа данных sql_variant (8016 байт), то столбец sql_value хранит усечённую версию значения. Полное значение затем хранится в этом столбце. Если полное значение помещается в столбец sql_value, этот столбец остаётся NULL.
posting_n	Один или несколько столбцов со значениями первичного ключа соответствующей строки данных.

Я понимаю, что приведённая выше таблица содержит некоторые предположения и пробелы. Пожалуйста, свяжитесь со мной, если вы можете предоставить дополнительную информацию!

Индексы

Давайте теперь посмотрим на индексы, создаваемые на этой таблице узлов. Кластерный индекс прост.

Кластерный индекс

Мы уже видели выше, что кластерный индекс в этой таблице узлов построен на комбинации трёх столбцов: json_path, json_array_index и sql_value. Первые два столбца позволяют легко выполнять поиск по заданному JSON-пути, а добавление sql_value облегчает поиск для запросов, где конкретный путь должен иметь конкретное значение.

Мы видим примеры в этих двух образцах запросов, где первый использует значение из простого объекта, а второй получает значение из пары «имя/значение» в массиве.

SELECT CustomerName,
       ActivityData
FROM   dbo.CustomerProfile
WHERE  JSON_VALUE (ActivityData,
                   '$.analytics.behavior.browsing.totalPageViews') = 102;

SELECT CustomerName,
       ActivityData
FROM   dbo.CustomerProfile WITH (INDEX = ix_json_CustomerProfile_ActivityData)
WHERE  JSON_VALUE (ActivityData,
                   '$.analytics.behavior.browsing.recentSessions[1].duration') = 10;

Два плана выполнения выглядят одинаково в графическом представлении, различия только в свойствах. Поэтому я покажу здесь только один из них:

Свойство Object оператора JSON Index Seek показывает, что он выполняет поиск в индексе ix_json_CustomerProfile_ActivityData во внутренней таблице sys.json_index_874134555_1216001. Это действительно кластерный индекс на таблице узлов.

Я был удивлён, увидев свойство Seek Predicates. По-видимому, JSON Index Seek использует только json_path (установленный в указанный путь) и json_array (установленный в NULL для первого запроса и в 1 для второго), чтобы найти все строки, имеющие узел с указанным путём. Несмотря на то, что столбец sql_value также индексируется, он не используется для дальнейшего сужения поиска.

В результате все 25 строк возвращаются оператору Filter, который затем удаляет 24 несовпадающие строки в первом запросе и даже все 25 во втором. Остальная часть плана выполнения довольно проста: Nested Loops передаёт столбец posting_1 (столбец первичного ключа) в Clustered Index Seek для получения ActivityData, которое запрашивается.

Причина, по которой второму запросу нужна подсказка, становится ясна, когда вы смотрите на свойство Estimated Number of Rows оператора Filter. Для первого запроса оптимизатор оценивает, что только 5,9 строк соответствуют фильтру. Для второго запроса оценка составляет 16,4. С такой высокой оценкой оптимизатор ожидает, что 16,4 выполнения Clustered Index Seek будут стоить больше, чем просто полное Clustered Index Scan таблицы, вычисление выражения JSON_VALUE непосредственно из самого JSON-столбца и последующая фильтрация по этому значению. Удивительно, но план выполнения, который возвращается, имеет оценку количества строк всего 1.

Я не пытался понять, откуда берутся эти оценки. Я также не пытался понять, почему фильтр по столбцу sql_value, третьему индексированному столбцу, не был передан в оператор JSON Index Seek. Это сделало бы план выполнения дешевле.

Некластерный индекс json_index_posting_col_nci

Каждый созданный JSON-индекс приводит не только к внутренней таблице узлов с кластерным индексом, но и как минимум к одному некластерному индексу на той же таблице узлов. Этот индекс всегда называется одинаково: json_index_posting_col_nci. Это название уже предполагает, что индекс построен на столбце(ах) posting, которые являются столбцами, связывающими обратно с первичным ключом базовой таблицы. Но давайте не будем предполагать. Давайте проверим.

SELECT     c.name         AS ColumnName,
           t.name         AS Datatype,
           CASE
               WHEN c.max_length = -1 THEN
                   'MAX'
               ELSE
                   CAST (c.max_length AS varchar(30))
           END            AS MaxLength,
           ic.key_ordinal AS [Clustered index column]
FROM       sys.indexes       AS i
INNER JOIN sys.columns       AS c
   ON      c.object_id    = i.object_id
INNER JOIN sys.types         AS t
   ON      t.user_type_id = c.user_type_id
INNER JOIN sys.index_columns AS ic
   ON      ic.object_id   = i.object_id
   AND     ic.index_id    = i.index_id
   AND     ic.column_id   = c.column_id
WHERE      i.object_id = OBJECT_ID ('sys.json_index_874134555_1216001')
AND        i.name             = N'json_index_posting_col_nci'
ORDER BY   c.column_id;

Вывод подтверждает, что этот индекс действительно построен только на столбце posting_1. Для базовой таблицы с составным первичным ключом это были бы все столбцы первичного ключа, названные posting_1, posting_2 и т. д. в таблице узлов.

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

Однако мне не удалось заставить оптимизатор действительно создать план запроса, использующий этот индекс. Каждый селективный запрос, который я пробовал, предпочитал использовать Clustered Index Seek основной таблицы, а затем обычные операторы Compute Scalar для извлечения запрошенных выражений JSON_VALUE. И это имеет смысл, если подумать. В конце концов, поиск по индексу (Index Seek) в этом индексе возвращал бы одну строку для каждой пары «имя/значение» в JSON. Так что даже несмотря на то, что относительно дорогую обработку функции JSON_VALUE можно было бы избежать, читая из таблицы узлов, большее количество строк делает это менее привлекательным.

Я не хотел тратить больше времени на поиск запроса, который действительно приводит к плану выполнения, использующему этот индекс. Если вы найдёте такой, пожалуйста, дайте мне знать.

Лично я думаю, что этот индекс был бы более полезен, если бы по крайней мере столбцы json_path и json_array_index были добавлены как второй и третий индексированные столбцы. Это сделало бы этот индекс чрезвычайно полезным для такого простого примера запроса:

SELECT JSON_VALUE (ActivityData,
                   '$.analytics.behavior.browsing.totalPageViews')
FROM   dbo.CustomerProfile WITH (INDEX = ix_json_CustomerProfile_ActivityData)
WHERE  CustomerID = 2;

Некластерный индекс json_index_search_optimization_nci

Если вы добавите опцию OPTIMIZE_FOR_ARRAY_SEARCH = ON при создании JSON-индекса, SQL Server создаст ещё один некластерный индекс во внутренней таблице узлов. Этот индекс всегда называется json_index_search_optimization_nci. Это имя намекает на предполагаемое использование индекса, но не на индексированные столбцы. Итак, давайте ещё раз выполним этот запрос:

SELECT     c.name         AS ColumnName,
           t.name         AS Datatype,
           CASE
               WHEN c.max_length = -1 THEN
                   'MAX'
               ELSE
                   CAST (c.max_length AS varchar(30))
           END            AS MaxLength,
           ic.key_ordinal AS [Index column]
FROM       sys.indexes       AS i
INNER JOIN sys.columns       AS c
   ON      c.object_id    = i.object_id
INNER JOIN sys.types         AS t
   ON      t.user_type_id = c.user_type_id
INNER JOIN sys.index_columns AS ic
   ON      ic.object_id   = i.object_id
   AND     ic.index_id    = i.index_id
   AND     ic.column_id   = c.column_id
WHERE      i.object_id = OBJECT_ID ('sys.json_index_874134555_1216001')
AND        i.name             = N'json_index_search_optimization_nci'
ORDER BY   c.column_id;

Вот результаты, которые я получил:

Мы видим, что этот индекс является составным индексом как минимум по четырём столбцам: json_path, sql_value, json_array_index и status в таком порядке. Кроме того, posting_1 (и другие столбцы posting_n, если они существуют) добавлен как включённый столбец (INCLUDE), чтобы предотвратить поиск по ключу (Key Lookup) при использовании этого индекса для извлечения первичного ключа строки.

Порядок столбцов в индексе, особенно размещение sql_value перед json_array_index, предполагает, что этот индекс может быть очень эффективным при поиске конкретного значения в массиве в JSON, где для индекса массива используется выражение с подстановочным знаком ([*]). Например, как в примере ниже.

SELECT CustomerName,
       JSON_QUERY (ActivityData,
                   '$.metadata.quality.validationRules[*]' WITH ARRAY WRAPPER)
FROM   dbo.CustomerProfile WITH (FORCESEEK)
WHERE  JSON_CONTAINS (ActivityData, 
                      'phone_format', 
                      '$.metadata.quality.validationRules[*]') = 1;

Мне снова пришлось прибегнуть к подсказке, потому что оптимизатор, в данном случае правильно, оценивает, что все 25 строк будут совпадать. (И я снова вижу это странное поведение, что в плане с подсказкой оценки меняются).

План выполнения показывает Compute Scalar, ведущий в Nested Loops. Верхний вход — это Sort (Distinct Sort), ведущий в JSON Index Seek; нижний вход — это Clustered Index Seek.

Нам снова нужно посмотреть на свойства оператора JSON Index Seek, чтобы подтвердить, что он действительно нацелен на индекс json_index_search_optimization_nci:

Свойство Seek Predicates подтверждает, что выполняется прямой поиск на основе пути JSON и значения, которое мы ищем. Подстановочный знак [*] в выражении JSON означает, что мы хотим все записи, поэтому мы не можем фильтровать по третьему индексированному столбцу, json_array_index. Это также означает, что предикат по четвёртому индексированному столбцу, status, не может быть обработан в Seek Predicates и должен быть перемещён в выражение Predicate.

Оператор Sort, который следует за ним, нужен потому, что JSON позволяет массиву содержать один и тот же элемент дважды. В этом случае оператор JSON Index Seek вернул бы оба, поэтому мы получили бы две строки, возвращённые для одной и той же строки в базовой таблице. Sort (Distinct Sort) исправляет это, сортируя по первичному ключу, а затем удаляя любые найденные дубликаты. Остальная часть плана выполнения не содержит сюрпризов.

Интересно, что когда вы изменяете приведённый выше запрос, чтобы искать только в элементе массива [1] вместо подстановочного знака [*], индекс поиска по массивам всё ещё используется. Но теперь свойство Seek Predicates добавляет предикат json_array_index = 1, а это также означает, что предикат о том, что status не должен быть равен 1, теперь также перемещается из свойства Predicate в Seek Predicates. Кроме того, поскольку теперь мы указываем точный индекс массива, мы не можем получить одну и ту же строку более одного раза, поэтому оператор Sort (Distinct Sort) также исчезает.

Заключение

На уровне обзора архитектура JSON-индексов похожа на архитектуру XML-индексов: внутренняя таблица узлов, которая хранит «разобранное» содержимое JSON-столбца, с кластерным индексом и дополнительными некластерными индексами. Но при более глубоком рассмотрении выделяются в основном различия.

В то время как таблица узлов XML-индекса включает все узлы в XML, таблица узлов JSON-индекса включает только значения (пары «имя/значение» и безымянные значения в массиве).

Для XML-индекса у вас есть полный контроль над определяемыми некластерными индексами, выбирая, какие вторичные XML-индексы создавать. Для JSON-индекса один тип некластерного индекса создаётся всегда; другой тип может быть создан или нет, в зависимости от опции OPTIMIZE_FOR_ARRAY_SEARCH в операторе CREATE JSON INDEX.

Столбцы и значения во внутренней таблице узлов, а также индексированные столбцы в кластерном и некластерных индексах полностью различаются между ними, что также означает, что их варианты использования несопоставимы.

И наконец, хотя это явно не упоминалось в основном тексте, вы могли видеть из планов выполнения, что Microsoft решила создать специальные операторы для работы с JSON-индексами, такие как JSON Index Insert и JSON Index Seek. Для XML-индексов, которые также реализованы как обычные кластерные и некластерные индексы на внутренней таблице, план выполнения в таком случае показывает обычный оператор Index Insert или Index Seek, и вы можете увидеть, что целью является XML-индекс, только посмотрев под-свойство Index Kind в группе свойств Object.

Следующий выпуск этой серии о структурах хранения будет посвящён пространственным индексам. Если я не передумаю.