Может ли внешний ключ с проверочным ограничением, без создания индексов, повысить производительность запросов ? Ответ на этот вопрос является утвердительным и вот почему. Оптимизатор может использовать внешний ключ (foreign key) и проверочное ограничение (check constraint) для создания более эффективного плана, на основании ограничений внешнего ключа.
Вот пример.
Создадим две таблицы:
Create Table sales
(
CustomerID INT Primary key
);
Create Table SalesOrderdetail
(
SalesOrderID int Primary key,
CustomerID int Not Null
Constraint FTCustomerID
References sales (CustomerID)
);
Заполним их тестовыми значениями :
insert into sales (CustomerID)
values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)
insert into SalesOrderdetail(SalesOrderID,CustomerID)
values (1,1),(2,1),(3,1),(4,2),(5,2),(6,2),(7,3),(8,3),(9,3),(10,4),(11,4),(12,4),(13,5),(14,5),(15,5)
Рассмотрим, что делает движок базы данных при наличии внешнего ключа. Перед выполнением запроса надо включить фактический план выполнения.
Запрос:
select so.* from SalesOrderdetail as so
where exists (select * from sales as s
where so.CustomerID=s.CustomerID)
План запроса 1:
|--Clustered Index Scan(OBJECT:([AdventureWorks].[dbo].[SalesOrderdetail].[PK__SalesOrd__B14003C2184C96B4] AS [so]))
Графический план запроса
В этом плане видно, что оптимизатор не затронул таблицу Sales, несмотря на то, что она фигурирует в запросе на получение данных. Было произведено обращение только к SalesOrderdetail. Это произошло потому что, оптимизатор знает, что при наличии внешнего ключа и проверки ограничения, таблица продаж должна ссылаться только на существующих клиентов, т.е. в данном случае можно не обращаться к таблице Sales.
А что произойдет, если отключить ограничение внешнего ключа ?
Alter Table SalesOrderdetail nocheck constraint FTCustomerID;
GO
select so.* from SalesOrderdetail as so
where exists (select * from sales as s
where so.CustomerID=s.CustomerID)
GO
План запроса 2:
|--Nested Loops(Inner Join, OUTER REFERENCES:([so].[CustomerID]))
|--Clustered Index Scan(OBJECT:([AdventureWorks].[dbo].[SalesOrderdetail].[PK__SalesOrd__B14003C2184C96B4] AS [so]))
|--Clustered Index Seek(OBJECT:([AdventureWorks].[dbo].[sales].[PK__sales__A4AE64B80169315C] AS [s]), SEEK:([s].[CustomerID]=[AdventureWorks].[dbo].[SalesOrderdetail].[CustomerID] as [so].[CustomerID]) ORDERED FORWARD)
Графический план запроса
Это новый план старого запроса.
Так как ограничение внешнего ключа отключено, оптимизатор не знает, имеет ли SalesOrderdetail корректные ссылки на столбец CustomerID таблицы Sales. Поэтому происходит обращение к Sales и применение оператора EXISTS. Как следствие, имеем не самый оптимальный план выполнения.
Теперь включим ограничение внешнего ключа:
Alter table SalesOrderdetail check constraint FTCustomerID;
И снова выполним запрос и посмотрим на план. Если вы ожидали увидеть такой план как на план 1, то это не так. План будет построен по 2 сценарию.
Почему так произошло ?
Это произошло потому, что это ограничение внешнего ключа является "не надежным" (not trusted). Оптимизатор, при построении данного плана не принимает во внимание это ограничение. Внешний ключ не является надежным, т.к. в таблице SalesOrderdetail могут быть записи с таким CustomerID, который отсутствует в Sales. Когда мы включили ограничение внешнего ключа, то оно не проверило уже существующие данные, которые могут быть не корректны ! Например, в SalesOrderdetail может быть значение CustomerID отсутствующее в Sales.
Можно проверить статус внешнего ключа, запустив следующий код:
select name,is_not_trusted from sys.foreign_keys where name= 'FTCustomerID'
(Кстати, многие разработчики и администраторы были удивлены количеством «не надежных» внешних ключей когда запустили этот запрос на своих базах).
Результат запроса подтвердит то, что исследуемый внешний ключ имеет статус “not trusted”. Для изменения статуса на противоположный нужно использовать опцию WITH CHECK, как показано ниже.
Alter table SalesOrderdetail WITH CHECK check constraint FTCustomerID;
Теперь внешний ключ имеет статус «trusted», в этом можно убедиться запустив вышеприведенный запрос из sys.foreign_keys , где для этого ключа в поле is_not_trusted будет значение 0.
И если снова запустить наш тестовый запрос, то мы увидим план 1.
Продолжим опыты, сделаем так, что столбец CustomerID может допускать значения NULL.
drop table SalesOrderdetail
go
Create Table SalesOrderdetail
(
SalesOrderID int Primary key,
CustomerID int Null
Constraint FTCustomerID
References sales (CustomerID)
);
Снова запускаем тестовый запрос и снова видим не самый оптимальный план 2. Оптимизатор выполняет оператор EXISTS для таблицы Sales, несмотря на то, что ключ определен как «надежный». Причиной такого поведения является столбец CustomerID, допускающий значения NULL.
Для возвращения к оптимальному плану нужно изменить запрос следующим образом :
select so.* from SalesOrderdetail as so
where exists (select * from sales as s
where so.CustomerID=s.CustomerID)
and so.customerID is not null
Оптимизатор точно знает, что ни одна запись с CustomerID = NULL не должна быть получена, поэтому не выполняет оператор EXISTS.
Как видно из этой статьи, внешний ключ и проверочное ограничение могут повысить производительность запроса.
Однако, потенциально большую опасность представляют т.н. "не надежные" внешние ключи, т.к. они могут нарушить целостность вашей БД.
Выявить такие ключи можно при помощи следующего запроса :
SELECT
name,
is_not_trusted f
FROM sys.foreign_keys
WHERE is_not_trusted = 1
Все вышеописанное справедливо и для подобных тестовых запросов:
select so.* from SalesOrderdetail as so
where so.CustomerID IN (select s.CustomerID from sales as s)
select so.* from SalesOrderdetail as so
JOIN sales as s ON so.CustomerID =s.CustomerID
Оптимизатор работает точно так же, как и в случае тестового запроса, приведенного автором.
Есть также особенность при использовании внешних ключей с ограничениями, которые были определены с параметром NOT FOR REPLICATION. Если внешний ключ был определен с параметром NOT FOR REPLICATION, то он всегда будет в состоянии «not trusted». Изменить поведение этого ключа можно только пересоздав его без опции NOT FOR REPLICATION.
Комментариев нет:
Отправить комментарий