fbpx

研究 MySQL 使用 Foreign Key 對 Join Table 的效率差異


簡介mysql

在某些情況下,我們設計 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 筆資料)。

實驗組(以 uf_ 開頭作為 Table Prefix)

使用 Foreign Key,如同對照組 uf_user 新增 2000 個使用者,uf_group 新增 200 個群組,每個使用者分別隸屬三個群組(uf_user_group_ref 共用 6000 筆資料)

開始實驗

實驗過程我們利用「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 如下:

接著我們透過「show profiles;」指令來查看執行結果,測試數據如下:

從上面的數據中可以發現 INNER JOIN 確實比 LEFT JOIN 快上許多(接近一倍),此外在沒有使用 Foreign Key 的情況下,若是 LEFT JOIN 的順序不是 PRIMARY KEY (username, groupname) 所指定的方向,速度就會比較慢(明顯花了超過一秒)。我們可以透過 MyuSql「EXPLAIN」命令來分析 Query 語法,分析方法如下:

上述 EXPLAIN 的報表中 rows 表示執行 Query 時 MySql Engine 走訪到的筆數(越高表示成本越高,速度就越慢)。兩者除了 rows 不同之外,在 ref 與 possible_keys 這兩個欄位也有差異,可以發現使用「group LEFT JOIN user_group_ref」時並沒有使用 PRIMARY,因此速度就比較慢了。

接下來我們繼續對有建立 Foreign Key 的「實驗駔」進行測試,嘗試執行以下 Query:

執行完後使用「show profiles;」查看執行的時間,執行結果如下:

看來用了 Foreign Key 建立的資料表,無論用和種方向進行 LEFT JOIN 速度表現都不錯。

結論

假設在實務上沒有使用 Foreign Key,那麼就一定要犧牲 LEFT JOIN 的效率嗎?那也不一定,在上述實驗中,如果想要讓沒有建立 Foreign Key 的資料表獲得最高的效率,也可以在資料表中定義適合的「UNIQUE KEY」來加速。但個人還是比較推薦使用 Foreign Key 這樣的方式,畢竟我們使用的是關聯式資料庫(DBMS),不用資料庫提供的關聯特性也說不過去。

在關聯式資料庫設計的原則中,我們應當盡可能設計出嚴謹的 Schema,避免程式設計師錯誤的邏輯程式破壞到資料庫中的資料,降低日後系統維護的成本。