О пользе ограничений в MSSQL

Мы привыкли, что ограничения (CONSTRAINT) — это способ указания допустимых значений для столбцов, ограничение уникальности и создание связей между таблицами. Это отличный способ поддерживать целостность данных, предотвращая некорректные операции. Но помимо этого, ограничения также помогают оптимизатору запросов генерировать более эффективный план выполнения. Как именно? Читайте ниже.

Есть такое мнение —  «Дайте MS SQL максимальную информацию о данных, которые вы храните». Что это значит? Во-первых, очевидные вещи: подбирайте наиболее подходящий тип данных (там, где нужен int, не стоит использовать bigint или float), указывайте размерность строковых или бинарных данных (nvarchar(10) вместо nvarchar(max)). А во-вторых — это ограничения: если значение в столбце может быть в пределах от 1 до 4, то укажите это явно, сервер вам только спасибо скажет!

Давайте рассмотрим пример реального запроса:

SELECT COUNT(*)
FROM [dbo].[requests] AS [r]
WHERE [r].[State] NOT IN (0, 6, 8)
  AND [r].[LastChange] > DATEADD(year, -5, GETDATE())
  AND [r].[CategoryId] IN (0, 1, 2, 3, 5)

Он собирает статистику по заявкам в системе за последние 5 лет в разрезе определённых категорий и статусов.

План выполнения выглядит вот так:

Планировщик выбрал следующий составной индекс:

CREATE
NONCLUSTERED INDEX [IX_requests_State_SubState] ON [dbo].[requests]
(
    [State] ASC,
    [SubState] ASC
)
INCLUDE([ClientId], [Id], [ExternalId], [LastChange], [CategoryId], [TypeId])

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

Выполним запрос ещё раз с включенным сбором статистики:

SET STATISTICS IO ON;

Получим следующий результат:

Table 'requests'. Scan count 10, logical reads 11268, physical reads 0, page server reads 0, read-ahead reads 0.

Физических чтений 0 — все данные подняты из буферного пула. При этом сервер выполнил 11268 логических чтений страниц по 8 КБ.

Если посмотреть в свойства оператора Index Seek, видно, что оптимизатору пришлось вычитать 659796 строк, чтобы затем отфильтровать их и сагрегировать в COUNT(*):

Добавляем ограничения

Мы договорились не добавлять новые индексы. Вместо этого поможем оптимизатору и сообщим ему больше информации о самой природе наших данных.

Колонка State хранит статус обработки сущности. Хоть физический тип данных в таблице — int, бизнес-логика допускает всего 11 фиксированных значений (от 0 до 10).

Зафиксируем это бизнес-правило на уровне реляционной схемы:

ALTER TABLE [dbo].[requests] WITH CHECK 
ADD CONSTRAINT [CK_requests_State] CHECK (State IN (0,1,2,3,4,5,6,7,8,9,10))

Мы явно сказали серверу: значений за пределами этого списка в таблице быть не может.

Снова выполняем исходный запрос:

Графически форма плана осталась прежней, но стрелка между Index Seek и агрегацией заметно похудела. Посмотрим на фактическое количество строк:

Количество прочитанных строк упало почти в 6 раз: 114496 вместо 659796.

Что показывает статистика ввода-вывода?

Table 'requests'. Scan count 27, logical reads 2301, physical reads 0, page server reads 0, read-ahead reads 0.

Объём логических чтений уменьшился пропорционально — с 11268 до 2301.

Почему так произошло? Обратите внимание на исходный предикат: [r].[State] NOT IN (0, 6, 8).

Когда ограничений нет, оптимизатор исходит из того, что в колонке типа int может лежать любое значение от -2147483648 до +2147483647. Чтобы исключить всего три точки, сервер строит несколько широких открытых диапазонов поиска по B-Tree индексу: (-∞, 0), (0, 6), (6, 8) и (8, +∞).

Внутри таких широких интервалов сервер вынужден просматривать огромные непрерывные цепочки страниц индекса.

После добавления ограничения оптимизатор выполняет упрощение предиката (Predicate Simplification). Зная полный домен допустимых значений, он понимает, что условие NOT IN (0, 6, 8) строго эквивалентно: [r].[State] IN (1, 2, 3, 4, 5, 7, 9, 10).

Вместо сканирования четырёх широких диапазонов оптимизатор выполняет серию точечных переходов по B-Tree. Все нерелевантные страницы индекса отсекаются ещё до чтения строк.

Также наличие ограничений вообще может позволить исключить сканирование таблицы:

SELECT COUNT(*)
FROM [dbo].[requests] AS [r]
WHERE [r].[State] IN (11) 
  AND [r].[LastChange] > DATEADD(year, -5, GETDATE())

Передав заведомо недопустимое значение в условие по полю State, сервер исключит эту ветку из вычислений:

Entity Framework

Зачастую такие поля, как State являются отражением перечислений из кода. Если вы используете Entity Framework с code-first подходом разработки базы данных, то вы можете указать ограничения:

modelBuilder.Entity<Request>()
    .ToTable(t => t.HasCheckConstraint("CK_requests_State", "[State] IN (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10)"));

Это можно автоматизировать с помощью библиотеки EFCore.CheckConstraints, которая смотрит на тип данных (enum), атрибуты валидации System.ComponentModel.DataAnnotations и генерирует миграции на их основе.

Заключение

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

Но у любых решений есть последствия. Как и с индексами, на каждый INSERT и UPDATE сервер будет тратить микросекунды на проверку ограничений, а добавление новых значений enum потребует миграции.