基本説明

外部キー制約は参照整合性(Referential Integrity)のためのRDBMSの機能です。他のテーブルのIDなどを参照している場合に、存在しないIDを誤ってレコードに入れてしまわないように制限します。

この制約を定義すると新しいレコードを入れたり既存のレコードを更新・削除する時に参照が正しいかどうかチェックするため、一般的にパフォーマンスが低下してしまいます。

しかしSQL Serverではパフォーマンスが上がる場合があります。以下は参考記事。

参考
1. https://explainextended.com/2009/10/15/constraints-and-the-optimizer-in-sql-server-foreign-key/
2. https://www.experts-exchange.com/articles/4293/Can-Foreign-key-improve-performance.html
3. https://stackoverflow.com/questions/507179/does-foreign-key-improve-query-performance

この記事では、参考記事の解説と実際に試してみた知見を共有します。

解説

参考記事1と名前を合わせて、以下のようなテーブルがあるとします。

t_primary: 参照される側のテーブル
t_primary.id: t_primaryのPRIMARY KEY
t_foreign: 参照する側のテーブル
t_foreign.pid: t_primary.idを参照するt_foreignのカラム。外部キー制約付き
db: t_primary、t_foreignを持つDB

外部キー制約が信用できるのであれば、t_foreign.pidに出現する値は全てt_primary.idに存在することがわかります。

この時、次のようなクエリを実行プラン付きで実行します。

SELECT f.*
FROM db.t_primary p
JOIN db.t_foreign f
ON p.id = f.pid

このクエリは、t_foreign.pidの値がt_primary.idに存在するt_foreignのレコードを抽出します。

実行プランを見てみると確かにt_foreignだけをスキャンしており、t_primaryを見に行っていません。これはわざわざJOINしなくとも、t_foreign.pidの中にはt_primary.idに存在する値のみが入っていることが保証されているからです。特に難しいことなんてなく、人間から見てもごく自然な話ですね。

この後にt_foreign.pidの外部キー制約を外してから再度実行すると、t_primaryも見に行く実行プランになります。もしかしたらt_foreign.pidにはt_primary.idに存在しない値が入っており、JOINでレコードが減る可能性があるからです。

再度外部キー制約を付けてさらに信頼できる状態にすると実行プランはまたt_primaryを見に行かない最適されたものに戻ります。

ちなみに、外部キー制約を付けただけでは元に戻りません！　制約自体は付けたり外したりできるので、それだけでは既存のデータが制約を守られているかは保証されないからです。後述しますが、信頼できるかどうかは別にフラグを持っています。

次の二つのクエリも上と同様の処理を表現しているクエリです。これも実行プランが最適化されます。

SELECT f.*
FROM db.t_foreign f
WHERE f.pid IN
(
    SELECT id
    FROM db.t_primary
)

SELECT f.*
FROM db.t_foreign f
WHERE exists
(
    SELECT *
    FROM db.t_primary p
    WHERE f.pid = p.id
)

知見

上記のことを実際に再現しようと試行錯誤していてわかったこといくつかがあります。

t_primaryのカラムをSELECTすると最適化されない

t_primaryの方もSELECTするのであれば当然そっちも見に行くので、外部キー制約が付いていない場合と実行プランが変わりません。

SELECT *
FROM db.t_primary p
JOIN db.t_foreign f
ON p.id = f.pid

ただそうなると最初のクエリって要するにt_foreignだけをSELECTするのと一緒だってことですよね。

SELECT *
FROM db.t_foreign

つまりこの最適化とは、JOINやWHEREをスキップしているだけです。

人間がわかり切っていることを最適化してくれるということは、自分でクエリ書いている（クエリビルダーを使っている場合も）ならあまり恩恵なさそうな…。ORMとかなら効いてくるんでしょうか？　ただ本当に外部キー制約が信頼し得るかどうかわからない場合はJOINした方が確実という意味では、最初の書き方の方がいいですね。

「外部キー制約を適用」が「はい」でなければ制約が働かない

本題と関係ないんですが、これを試している時にSQL Server Management StudioでキーにFKが入っているので制約が効いているのかと思ったらこのオプションがONでないと動きませんでした。当然最適化も働きません。

「レプリケーションに対して適用」が「はい」でなければ制約が信頼できる状態にならない

同じくManagement Studioの話。これが無いと最適化が働かないようです。

「作成または再度有効化するときに既存データを確認」が「はい」でなければ制約が信頼できる状態にならない

同じくManagement Studioの話。外部キー制約を戻す際はこれを「はい」にして既存データもチェックしてもらわないと最適化が働きません。

外部キーが信頼できる状態かどうか確認する方法

USE db
SELECT name, is_not_trusted
FROM   sys.foreign_keys

is_not_trusted=0なら最適化が働きます。

参考元：https://stackoverflow.com/questions/35892500/sql-server-foreign-key-still-not-trusted-after-check-check

雑感

もともと「外部キー制約を足すとパフォーマンスが上がる」と言われたので調査していたんですが、これだけなら最適化が効いてくるパターンってあまり無いような気がします。実際はもっと別のところで速くなっていたりするんでしょうか？　多くのデータを入れたり別パターンで試してみるとまた違うのかもしれません。