Как работают JSON-индексы в SQL Server

Автор: Brent Ozar, Database Animations: How SQL Server’s JSON Indexes Work

По многочисленным просьбам в SQL Server 2025 добавили JSON-индексы. Вы можете съесть торт возможности хранить практически не имеющие схемы JSON-блобы как часть каждой строки и при этом получить его, быстро запрашивая конкретные значения.

(Обычная фраза — «иметь свой торт и съесть его», но вы когда-нибудь задумывались, что это перевёрнуто и не имеет смысла? На самом деле в обратном порядке — как «съесть свой торт и иметь его» — это имеет гораздо больше смысла, потому что представляет сценарий «лучшее из обоих миров». «Иметь торт и съесть его» подразумевает, что вы сначала имеете его, а затем едите, но как только вы его съели, вы больше не можете его иметь, так что — ладно, я сосредоточусь на обучении работе с базами данных. Двигаемся дальше.)

Давайте используем таблицу Users из базы данных Stack Overflow и представим, что столбец Users.Location хранится как JSON, вместо того чтобы позволять пользователям вводить что угодно. Представим, что мы заставляем их выбирать страну, провинцию и город. Затем используем одну из моих историй в Database Animations, чтобы проиллюстрировать боль от запросов к нему без JSON-индекса, а затем «тортовость» добавленного индекса:

До того как у нас появится JSON-индекс, нам приходится выполнять:

1. Интенсивную по чтению работу по сканированию каждой строки в таблице, и
2. Интенсивную по CPU работу по «вскрытию» строки и разбору данных JSON для определения каждого атрибута.

JSON-индекс превращает эту работу в простой поиск по индексу с поиском по ключу — ту же потрясающую, сокрушительную мощь и скорость, к которым вы привыкли при традиционных схемах таблиц и индексах. Вы можете съесть свой торт (не утруждая себя проектированием структур таблиц) и при этом иметь его (получить быстрые запросы — если только вы готовы проектировать индексы для поддержки ваших запросов).

Однако это работает только если:

• Вы действительно создаёте JSON-индексы.
• Вы фильтруете по индексированным атрибутам (вы можете фильтровать и по другим атрибутам, но как минимум вам нужно фильтровать по индексированному атрибуту, чтобы SQL Server мог сократить пространство поиска, прежде чем проверять остальные фильтры в вашем запросе).
• Индексированные столбцы селективны — они сокращают пространство поиска (в отличие от таких атрибутов, как Alive = Yes, которые не сокращают пространство поиска).
• Вы держите количество индексированных атрибутов на минимуме — скажем, максимум 5-10 атрибутов.

Это ограничение в 5-10 атрибутов удивляет разработчиков.

В конце концов, Microsoft в документации по CREATE JSON INDEX описывает это как практически неограниченное. Первый пример, который они используют, выглядит так:

CREATE JSON INDEX json_content_index ON dbo.Users (UserAttributes);

В этом примере ВСЁ, что мы запихиваем в этот JSON-«ящик для мусора», индексируется — включая в нашем примере выше то, хотят ли они Theme:Dark или Theme:Light. Однако это в конечном итоге создаёт индекс по каждому атрибуту, который мы помещаем в наш JSON. Рост размера взрывной, и не редкость увидеть, что размер индекса кратен размеру хранимых JSON-данных. (А вы думали, что JSON-данные неэффективны по размеру!) В документации нет никаких предупреждений об этом, но поверьте мне, накладные расходы впечатляющие. Если бы мы хранили все столбцы всей таблицы Users в JSON, а затем выполнили создание индекса в стиле Microsoft, посмотрите на накладные расходы по размеру:

Сама таблица занимает всего 3,9 ГБ, но JSON-индекс поверх этих данных — 6 ГБ, почти вдвое больше размера таблицы! Плюс, если вы изменяете любой атрибут в этой таблице — даже атрибуты, по которым вы не фильтруете, например Theme = Dark против Light, — вы платите штраф за производительность, чтобы поддерживать индекс Theme в актуальном состоянии.

Вместо этого вы хотите индексировать только те конкретные атрибуты, по которым вы будете регулярно фильтровать, как в примере, который я использую в анимации:

CREATE JSON INDEX json_content_index ON 
dbo.Users ('$.Location.Country', '$.Location.Province', '$.Location.City');

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