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

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

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

Пример

Создадим таблицу:

CREATE TABLE [TestTable](
    [Id] [int] IDENTITY(1,1) NOT NULL, 
    [Value] [nvarchar](max) NOT NULL, 
    [Status] [int] NOT NULL
)

Добавим в нее 1 миллион записей следующего вида:

Id Value Status
1 value 1
2 value 2
3 value 3 
4 value 1 
5 value 2 
6 value 3 
7 value 1 
8 value 2 
9 value 3
10 value 1

И выполним запрос, предварительно включив сбор статистики:

SET STATISTICS IO ON

SELECT tt.Status, COUNT(*)
FROM [TestTable] AS tt
WHERE tt.Status <> 1 
GROUP BY tt.Status

Запрос довольно жизненный - нам нужно количество записей по всем статусам кроме первого ("Посчитать количество заказов в каждом статусе, кроме статуса Открыто").

Получаем следующую статистику:

Table 'TestTable'. Scan count 9, logical reads 4590, physical reads 0, read-ahead reads 4038, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

А на самой табличке вот такой план:

Очевидный Table Scan - нам пришлось просмотреть всю таблицу, т.к. у нас нет индексов.

Давайте добавим индекс:

CREATE NONCLUSTERED INDEX [IX_TestTable_Status] ON [TestTable]
(
   [Status] ASC
)

И повторим запрос:

Table 'TestTable'. Scan count 2, logical reads 1501, physical reads 0, read-ahead reads 10, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Ожидаемо увидели Index Seek с интересным предикатом: Staus < 1 и Status > 1. Если вспомнить, что индексы - это дерево, а операции поиска по дереву выполняются достаточно быстро, то данный запрос выглядит вполне логично. Сервер преобразовал неравенство в два сравнения, которые может обработать наиболее эффективно. При этом, количество чтений так же уменьшилось.

А теперь давайте скажем серверу, что у нас в столбце Status могут быть только значения 1, 2, 3 и 4:

ALTER TABLE [TestTable] WITH CHECK ADD CONSTRAINT [CK_TestTable]
CHECK (([Status]=(4) OR [Status]=(3) OR [Status]=(2) OR [Status]=(1)))

И выполним запрос:

Table 'TestTable'. Scan count 3, logical reads 1508, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Как ни странно, в плане выполнения теперь нет ни одного упоминания о статусе "1". Вместо этого оптимизатор заменил предикат <> 1 на предикат IN (2, 3, 4), чего он раньше не делал до того момента, пока мы не добавили ограничение на столбец.

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

Рассмотрим еще один пример с другим запросом:

SET STATISTICS IO ON

SELECT tt.Status, COUNT(*)
FROM [TestTable] AS tt
WHERE tt.Status NOT IN (1,3)
GROUP BY tt.Status

Исключили еще один статус из рассмотрения. И уберем ограничение:

Table 'TestTable'. Scan count 2, logical reads 1501, physical reads 1, read-ahead reads 1959, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Оптимизатор опять заменяет NOT IN на серию сравнений "больше-меньше".

Добавим ограничение и выполним запрос:

Table 'TestTable'. Scan count 2, logical reads 757, physical reads 0, read-ahead reads 12, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Оптимизатор знает о том, что в столбце могут быть только значения из множества {1,2,3,4}, поэтому предикаты NOT IN (1, 3) и IN (2, 4) будут эквивалентны. При этом количество чтений уменьшилось в 2 раза.

Сводная таблица по количеству логических чтений

Предикат Без индекса и ограничения С индексом С индексом и ограничением
<> 1 4590 1501 1508
NOT IN (1,3) 4590 1501 757

Ссылки

  1. SET STATISTICS IO
  2. Create Check Constraint
blog comments powered by Disqus