簡介
在某些情況下，我們設計 Database 並沒有使用 Foreign Key (外來鍵/參照鍵) 來限制資料表欄位，使用 Foreign Key 是被推薦的作法，至少在 Database Schema 防止與阻擋了不合法的資料寫入，避免當程式設計或執行有錯誤時，造成資料錯誤。
除此之外，我們還可以藉由 Foreign Key 加速 Join 效率，為了證明 Foreign Key 對 Table Join 是有益的，我們進行以下實驗：
實驗環境
- OS: CentOS 6.4
- DBMS: MySql 5.1
- MySql Engine: InnoDB
對照組（以 nf_ 開頭作為 Table Prefix）
不使用 Foreign Key，nf_user 新增 2000 個使用者，nf_group 新增 200 個群組，每個使用者分別隸屬三個群組（nf_user_group_ref 共用 6000 筆資料）。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
CREATE TABLE `nf_user` ( `username` varchar(100) NOT NULL COMMENT '帳號名稱', `password` varchar(32) NOT NULL COMMENT '登入密碼', `description` varchar(100) , PRIMARY KEY (`username`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='網域帳號'; CREATE TABLE `nf_group` ( `groupname` varchar(100) NOT NULL COMMENT '群組名稱', `description` varchar(100) , PRIMARY KEY (`groupname`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='網域群組'; CREATE TABLE `nf_user_group_ref` ( `groupname` varchar(100) NOT NULL COMMENT '群組', `username` varchar(100) NOT NULL COMMENT '帳號', `description` varchar(100) , PRIMARY KEY (`username`, `groupname`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='群組成員名單'; |
實驗組（以 uf_ 開頭作為 Table Prefix）
使用 Foreign Key，如同對照組 uf_user 新增 2000 個使用者，uf_group 新增 200 個群組，每個使用者分別隸屬三個群組（uf_user_group_ref 共用 6000 筆資料）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
CREATE TABLE `uf_user` ( `username` varchar(100) NOT NULL COMMENT '帳號名稱', `password` varchar(32) NOT NULL COMMENT '登入密碼', `description` varchar(100) , PRIMARY KEY (`username`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='網域帳號'; CREATE TABLE `uf_group` ( `groupname` varchar(100) NOT NULL COMMENT '群組名稱', `description` varchar(100) , PRIMARY KEY (`groupname`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='網域群組'; CREATE TABLE `uf_user_group_ref` ( `groupname` varchar(100) NOT NULL COMMENT '群組', `username` varchar(100) NOT NULL COMMENT '帳號', `description` varchar(100) , PRIMARY KEY (`username`, `groupname`), FOREIGN KEY (groupname) REFERENCES uf_group(groupname) ON DELETE CASCADE, FOREIGN KEY (username) REFERENCES uf_user(username) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='群組成員名單'; |
開始實驗
實驗過程我們利用「set profiling=1;」開啟 MySql Profile 記錄，執行 Query 後可以透過「show profiles;」來調閱每個 Query 的執行時間，這裡的執行時間可以記錄到小數點第八位。順帶一提，執行 SELECT 時我們加入了「SQL_NO_CACHE」關鍵字避免 MySql 使用 Cache 影響到我們的實驗結果。
我們先對「對照組」進行實驗（沒有建立 Foreign Key），分別採用「user Table JOIN user_group_ref Table」與「group Table 來 JOIN user_group_ref Table」，並且分別測試 INNER JOIN 與 LEFT JOIN 的執行效率，執行的 Query 如下：
|
1 2 3 4 |
SELECT SQL_NO_CACHE COUNT(*) FROM nf_user LEFT JOIN nf_user_group_ref ON nf_user.username = nf_user_group_ref.username; SELECT SQL_NO_CACHE COUNT(*) FROM nf_user INNER JOIN nf_user_group_ref ON nf_user.username = nf_user_group_ref.username; SELECT SQL_NO_CACHE COUNT(*) FROM nf_group LEFT JOIN nf_user_group_ref ON nf_group.groupname = nf_user_group_ref.groupname; SELECT SQL_NO_CACHE COUNT(*) FROM nf_group INNER JOIN nf_user_group_ref ON nf_group.groupname = nf_user_group_ref.groupname; |
接著我們透過「show profiles;」指令來查看執行結果，測試數據如下：
|
1 2 3 4 5 6 7 8 9 10 |
mysql> show profiles; +----------+------------+-----------------------------------------------------------------------------------------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+-----------------------------------------------------------------------------------------------------------------------------+ | 1 | 0.01028600 | SELECT SQL_NO_CACHE COUNT(*) FROM uf_group LEFT JOIN uf_user_group_ref ON uf_group.groupname = uf_user_group_ref.groupname | | 2 | 0.00762200 | SELECT SQL_NO_CACHE COUNT(*) FROM uf_group INNER JOIN uf_user_group_ref ON uf_group.groupname = uf_user_group_ref.groupname | | 3 | 1.27091800 | SELECT SQL_NO_CACHE COUNT(*) FROM nf_group LEFT JOIN nf_user_group_ref ON nf_group.groupname = nf_user_group_ref.groupname | | 4 | 0.01653800 | SELECT SQL_NO_CACHE COUNT(*) FROM nf_group INNER JOIN nf_user_group_ref ON nf_group.groupname = nf_user_group_ref.groupname | +----------+------------+-----------------------------------------------------------------------------------------------------------------------------+ 8 rows in set (0.00 sec) |
從上面的數據中可以發現 INNER JOIN 確實比 LEFT JOIN 快上許多（接近一倍），此外在沒有使用 Foreign Key 的情況下，若是 LEFT JOIN 的順序不是 PRIMARY KEY (username, groupname) 所指定的方向，速度就會比較慢（明顯花了超過一秒）。我們可以透過 MyuSql「EXPLAIN」命令來分析 Query 語法，分析方法如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
mysql> EXPLAIN SELECT SQL_NO_CACHE COUNT(*) FROM nf_group LEFT JOIN nf_user_group_ref ON nf_group.groupname = nf_user_group_ref.groupname; +----+-------------+-------------------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------------+-------+---------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | nf_group | index | NULL | PRIMARY | 302 | NULL | 194 | Using index | | 1 | SIMPLE | nf_user_group_ref | index | NULL | PRIMARY | 604 | NULL | 5952 | Using index | +----+-------------+-------------------+-------+---------------+---------+---------+------+------+-------------+ 2 rows in set (0.00 sec) mysql> EXPLAIN SELECT SQL_NO_CACHE COUNT(*) FROM nf_user LEFT JOIN nf_user_group_ref ON nf_user.username = nf_user_group_ref.username; +----+-------------+-------------------+-------+---------------+---------+---------+---------------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------------+-------+---------------+---------+---------+---------------------------+------+-------------+ | 1 | SIMPLE | nf_user | index | NULL | PRIMARY | 302 | NULL | 1837 | Using index | | 1 | SIMPLE | nf_user_group_ref | ref | PRIMARY | PRIMARY | 302 | jointest.nf_user.username | 1 | Using index | +----+-------------+-------------------+-------+---------------+---------+---------+---------------------------+------+-------------+ 2 rows in set (0.00 sec) |
上述 EXPLAIN 的報表中 rows 表示執行 Query 時 MySql Engine 走訪到的筆數（越高表示成本越高，速度就越慢）。兩者除了 rows 不同之外，在 ref 與 possible_keys 這兩個欄位也有差異，可以發現使用「group LEFT JOIN user_group_ref」時並沒有使用 PRIMARY，因此速度就比較慢了。
接下來我們繼續對有建立 Foreign Key 的「實驗駔」進行測試，嘗試執行以下 Query：
|
1 2 3 4 |
SELECT SQL_NO_CACHE COUNT(*) FROM uf_user LEFT JOIN uf_user_group_ref ON uf_user.username = uf_user_group_ref.username; SELECT SQL_NO_CACHE COUNT(*) FROM uf_user INNER JOIN uf_user_group_ref ON uf_user.username = uf_user_group_ref.username; SELECT SQL_NO_CACHE COUNT(*) FROM uf_group LEFT JOIN uf_user_group_ref ON uf_group.groupname = uf_user_group_ref.groupname; SELECT SQL_NO_CACHE COUNT(*) FROM uf_group INNER JOIN uf_user_group_ref ON uf_group.groupname = uf_user_group_ref.groupname; |
執行完後使用「show profiles;」查看執行的時間，執行結果如下：
|
1 2 3 4 5 6 7 8 9 10 |
mysql> show profiles; +----------+------------+-----------------------------------------------------------------------------------------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+-----------------------------------------------------------------------------------------------------------------------------+ | 1 | 0.02017200 | SELECT SQL_NO_CACHE COUNT(*) FROM uf_user LEFT JOIN uf_user_group_ref ON uf_user.username = uf_user_group_ref.username | | 2 | 0.01507300 | SELECT SQL_NO_CACHE COUNT(*) FROM uf_user INNER JOIN uf_user_group_ref ON uf_user.username = uf_user_group_ref.username | | 3 | 0.00959300 | SELECT SQL_NO_CACHE COUNT(*) FROM uf_group LEFT JOIN uf_user_group_ref ON uf_group.groupname = uf_user_group_ref.groupname | | 4 | 0.00741800 | SELECT SQL_NO_CACHE COUNT(*) FROM uf_group INNER JOIN uf_user_group_ref ON uf_group.groupname = uf_user_group_ref.groupname | +----------+------------+-----------------------------------------------------------------------------------------------------------------------------+ 12 rows in set (0.00 sec) |
看來用了 Foreign Key 建立的資料表，無論用和種方向進行 LEFT JOIN 速度表現都不錯。
結論
假設在實務上沒有使用 Foreign Key，那麼就一定要犧牲 LEFT JOIN 的效率嗎？那也不一定，在上述實驗中，如果想要讓沒有建立 Foreign Key 的資料表獲得最高的效率，也可以在資料表中定義適合的「UNIQUE KEY」來加速。但個人還是比較推薦使用 Foreign Key 這樣的方式，畢竟我們使用的是關聯式資料庫（DBMS），不用資料庫提供的關聯特性也說不過去。
在關聯式資料庫設計的原則中，我們應當盡可能設計出嚴謹的 Schema，避免程式設計師錯誤的邏輯程式破壞到資料庫中的資料，降低日後系統維護的成本。
