Перевод статьи подготовлен в преддверии старта курса «MS SQL Server разработчик».

---

Проблема

При настройке производительности SQL Server часто возникает вопрос, как ограничение внешних ключей (foreign key) влияет на производительность модификации данных. Все понимают, что внешние ключи необходимы для обеспечения ссылочной целостности, но может есть какой-то другой способ с лучшей производительностью?

В этой статье мы рассмотрим достоинства и недостатки использования ограничения CHECK для обеспечения ссылочной целостности вместо обычного внешнего ключа.

Решение

Прежде чем углубиться в детали, вы можете здесь вспомнить про различные ограничения целостности.

В этом примере мы будем использовать простой внешний ключ из таблицы main_fk в таблицу lookup_fk и преобразуем этот внешний ключ в CHECK-ограничение в таблице main_cc.

Пример с внешним ключом

Создадим типичное отношение с внешним ключом.

create table lookup_fk (
    id int not null, 
    description varchar(10));
 
create table main_fk (
    id int not null identity (1, 1), 
    lookupid int,
    col1 int,
    col2 int,
    col3 varchar(100));
 
alter table lookup_fk 
add constraint pk_lookup primary key clustered (id);
 
alter table main_fk 
add constraint pk_main primary key clustered (id);
 
alter table main_fk 
add constraint fk_main_lookup foreign key 
    (lookupid) REFERENCES lookup_fk (id) on update cascade on delete cascade;
 
create index ix_main_fk_lookupid on main_fk (lookupid);

Пример с CHECK-ограничением

Структуру предыдущей таблицы можно повторить, используя ограничение CHECK вместо внешнего ключа. Обратите внимание, что для CHECK необходимо явно указать список значений, которые входят в это ограничение. Эти значения необходимо обновлять на лету (как мы увидим это позже), но для нашего эксперимента мы просто перечислим эти значения заранее.

create table lookup_cc (
    id int not null, 
    description varchar(10));
 
create table main_cc (
    id int not null  identity (1, 1), 
    lookupid int,
    col1 int,
    col2 int,
    col3 varchar(100));
 
alter table lookup_cc 
add constraint pk_lookup_cc primary key clustered (id);
 
alter table main_cc 
add constraint pk_main_cc primary key clustered (id);
 
alter table main_cc 
add constraint ck_main_cc_lookupid check 
      ((lookupid IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19)));
 
create index ix_main_cc_lookupid on main_cc (lookupid);

Вставляем данные в таблицу

Теперь, когда мы создали таблицы, можно загрузить тестовые данные в main — и lookup-таблицы.

declare @cnt integer
declare @incnt integer
select @cnt=1
 
while @cnt < 20
begin
    insert into lookup_fk
    values (@cnt, 'count ' + cast(@cnt as varchar));
    
    insert into lookup_cc
    values (@cnt, 'count ' + cast(@cnt as varchar));
 
    select @incnt=1
    while @incnt < 100
    begin
        insert into main_fk (lookupid,col1,col2,col3)
        values (@cnt, @cnt, @cnt, 'dummydatadummydatadummydatadummydata' + cast(@cnt as varchar));
        insert into main_cc (lookupid,col1,col2,col3)
        values (@cnt, @cnt, @cnt, 'dummydatadummydatadummydatadummydata' + cast(@cnt as varchar));
        select @incnt=@incnt+1
    end
    select @cnt=@cnt+1
end

Проверка ссылочной целостности

Первое, что нам нужно сделать, — это убедиться в работоспособности ссылочной целостности. Вышеприведенный скрипт вставляет в lookup-таблицу значения id до 19, поэтому давайте попробуем вставить в main-таблицу значение lookupid больше 19.

insert into main_fk (lookupid,col1,col2,col3) values 
       (20,15,15,'dummydatadummydatadummydatadummydata' + cast(15 as varchar));
       
insert into main_cc (lookupid,col1,col2,col3) values 
       (20,15,15,'dummydatadummydatadummydatadummydata' + cast(15 as varchar));

В обоих случаях мы получаем похожие сообщения об ошибках.

Msg 547, Level 16, State 0, Line 40
The INSERT statement conflicted with the FOREIGN KEY constraint "fk_main_lookup". 
The conflict occurred in database "test", table "dbo.lookup_fk", column 'id'.

Msg 547, Level 16, State 0, Line 41
The INSERT statement conflicted with the CHECK constraint "ck_main_cc_lookupid". 
The conflict occurred in database "test", table "dbo.main_cc", column 'lookupid'.

Записи в таблицы не добавляются.

Тестируем INSERT

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

insert into main_fk (lookupid,col1,col2,col3) values 
     (15,15,15,'dummydatadummydatadummydatadummydata' + cast(15 as varchar));
go
 
insert into main_cc (lookupid,col1,col2,col3) values 
     (15,15,15,'dummydatadummydatadummydatadummydata' + cast(15 as varchar));
go

Через SQL Profiler можно увидеть, что вставка в таблицу с CHECK выполняется с меньшим количеством чтений (reads), чем в таблицу с внешним ключом, поскольку не требуется делать запрос в таблицу lookup1.



Также эту информацию можно посмотреть, включив set statistics io on перед выполнением запроса. Здесь можно увидеть, что второй запрос обращается только к main-таблице.

Table 'lookup_fk'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, 
      lob physical reads 0, lob read-ahead reads 0.
Table 'main_fk'. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, 
      lob physical reads 0, lob read-ahead reads 0.

Table 'main_cc'. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, 
      lob physical reads 0, lob read-ahead reads 0.

Тестируем UPDATE

Давайте проведем аналогичный тест для UPDATE. Изменим значение lookupid для одной записи в каждой из main-таблиц.

update main_fk set lookupid=10 where id=231;
go
 
update main_cc set lookupid=10 where id=231;
go

UPDATE, так же как и INSERT, работает немного быстрее и выполняет меньше операций чтения.



Используя информацию, полученную через set statistics io on, можно дополнительно подтвердить, что для внешнего ключа тратятся дополнительные ресурсы при чтении lookup-таблицы.

Table 'lookup_fk'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, 
      lob physical reads 0, lob read-ahead reads 0.
Table 'main_fk'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, 
      lob physical reads 0, lob read-ahead reads 0.
Table 'main_cc'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, 
      lob physical reads 0, lob read-ahead reads 0.

Тестируем DELETE

Выполнение удаления с использованием нашей новой структуры с CHECK явно будет сложнее, чем с обычным внешним ключом. Внешний ключ в таблице main_fk был создан с использованием on delete cascade, поэтому для удаления записи из lookup_fk (а затем и из main_fk) нам достаточно выполнить удаление из lookup_fk.

begin transaction;
   delete from lookup_fk where id=19;
commit transaction;

С CHECK нам придется повозиться намного больше, поскольку записи должны быть удалены из обеих таблиц вручную, а ограничение CHECK в таблице main_cc нужно создать заново, удалив соответствующие значения lookupid. Ниже приведены DML и DDL для всех этих команд.

begin transaction;
   delete from lookup_cc where id=19;
   delete from main_cc where lookupid=19;
   alter table main_cc drop constraint ck_main_cc_lookupid ;
   alter table main_cc with nocheck add constraint ck_main_cc_lookupid 
         check ((lookupid IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18)));
commit transaction;

Через SQL Profiler мы видим, что несмотря большее количество чтений у CHECK (примерно на 10%), выполняется он в два раза быстрее (duration) и использует меньше CPU.


Примечание: RowCount не включает в себя каскадные удаления.

Хотелось бы также отметить, что добавление значения в таблицу lookup_cc, вероятно, приведет к такой же ситуации, как и для DELETE, так как в таблице main_cc придется удалить и заново создать CHECK-ограничение.

Итог

Мы увидели, что, по крайней мере, для такого простого случая, можно улучшить производительность, используя ограничение CHECK. Было бы интересно посмотреть на это в более крупном масштабе, чтобы проверить, будет ли такая же картина. Тем не менее я думаю, что использование этого метода, в конечном итоге, зависит от того, как часто вы добавляете / удаляете значения в lookup-таблице по сравнению с тем, сколько данных добавляется в main-таблицу, так как частые изменения значений lookupid действительно добавляют сложности.

---

Использование Microsoft SQL Server в Linux

---