建立外鍵Foreign Key可以確保關聯式資料表的資料的完整性，避免掉孤兒資料的產生。
簡單的舉一個例子，有兩張資料表分別是部門資料表及員工資料表。 員工資料表會有一欄位紀錄他所屬的部門，所以該欄位會存放部門資料表的部門代碼。當這重要關聯性沒有透過外鍵來綁定時，資料庫去刪除部門資料表資料 時並不會去檢查員工資料表是否還有員工資料是對應到即將刪除的部門資料。因此一旦部門資料被刪掉，則之前關聯該筆資料的員工馬上變成無主的員工了。
為了避免掉這一種情況設定外鍵Foreign Key來確保資料完整性就變得很重要，但相對的是資料的增刪修都得驗證過才能動作，因此也會耗損資料庫資源，會寫 這一篇文也是因為無意間發現造成效能問題的原因居然是Foreign Key。
首先我先Demo一下資料表可以對自己做外鍵關聯嗎?這是我一個同事問我的，因此我也藉此一併紀錄下來。一般會自我關聯的狀況我直覺想到的就是跟階層有關的資料表，因此我就透過階層資料表來做自我關聯，如下圖所示我建立一張資料表叫tbSelfFroegin，簡單的3個欄位分別是ID(單位代碼)及OID(父單位代碼)及DepName(單位名稱) 。然後簡單的給了4筆資料，這4筆資料是階層的關係，就董事長室→總經理室→資訊室→研發組。
建立完成後我將該資料表的OID欄位Foreign到資料表自己的ID欄位，如下圖所示。
下圖可以看見FK已經建立成功。
資料表內容如下，一共四筆資料。
這時我試圖刪除該資料表ID=1的紀錄，由下圖可以看見無法順利刪除資料。透過簡易測試，證明資料表是可以對自己建立Foreign Key的。
本文開始
因為本篇文章是探討是否該對Foreign Key欄位建立索引，因此我會建立四張資料表，兩兩一組來比對。下圖中我建立這兩張資料表tbPro_NIX及tbCon_NIX，屆時tbCon_NIX會有一欄位(ProID)Foregin到tbPro_NIX的主鍵(ID)。但該Foreign Key欄位 [不會] 建立索引。
下圖中我建立這兩張資料表tbPro_IX及tbCon_IX，屆時tbCon_IX會有一欄位(ProID)Foregin到tbPro_IX的主鍵(ID)。 但該Foreign Key欄位 [會] 建立索引。
下圖顯示我們已經建立四張資料表。
接下來兩兩一組建立關聯。tbPro_NIX跟tbCon_NIX一組，tbPro_IX跟tbCon_IX。
下圖可以看見我們建立出來的兩個Foreign Key。
接下來先在 tbPro_NIX 及 tbPro_IX 塞入1000筆紀錄。
下三張圖是我在 tbCon_NIX 及 tb_Con_IX 塞入100萬筆資料。
我先將 tbCon_NIX 及 tbCon_IX 的Froeign Key(ProID欄位) 單數號刪除，砍了50萬筆資料。所以砍完後ProID欄位 只剩雙位數 了。
完成上面部屬後，接下來我Delete掉tbPro_NIX及tbPro_IX主索引編號為501的資料，由於SQL知道tbPro_NIX及tbPro_IX兩張資料表有被tbCon_NIX及tbCon_IX關聯，因此刪除資料時會去tbCon_NIX及tbCon_IX 檢查是否還有資料關聯編號501這一筆紀錄。
在下圖中紅色圈選出我們可以看到Delete tbPro_NIX時SQL在檢查tbCon_NIX (無索引) 需耗費3439個Page的IO。而刪除tbPro_IX時SQL在檢查tbCon_IX (有索引) 只需要4個Page的IO。
看一下Delete兩張資料表的執行計畫，可以發現由於有被外鍵關聯，所以刪除時SQL會檢查tbCon_NIX及tbCon_IX資料表。而有針對外鍵ProID欄位建索引的SQL可以利用該索引快速Seek來檢查資料，反之的是用Scan來掃整張資料表。
之前上一些SQL效能調教課程時，講師有提到也該針對Foreign Key建立索引，但當時老師提到的是Foreign Key欄位也是常常會被搜尋的欄位。當時自己的理解是以為Foreign Key欄位可能相對比較會拿來當Where的條件式，結果應該是個人理解錯誤了。
日前好友給了一張執行計畫，該執行計畫顯示某張Table被多張Table關聯，其中一些還是記Log的資料表。該執行計畫顯示該Table在刪除資料時SQL會到其他關連資料表用Scan的方式確認資料而導致效能問題產生。
我是ROCK
rockchang@mails.fju.edu.tw
