Site icon Soul & Shell Blog

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

簡介

在某些情況下,我們設計 Database 並沒有使用 Foreign Key (外來鍵/參照鍵) 來限制資料表欄位,使用 Foreign Key 是被推薦的作法,至少在 Database Schema 防止與阻擋了不合法的資料寫入,避免當程式設計或執行有錯誤時,造成資料錯誤。

除此之外,我們還可以藉由 Foreign Key 加速 Join 效率,為了證明 Foreign Key 對 Table Join 是有益的,我們進行以下實驗:

實驗環境

對照組(以 nf_ 開頭作為 Table Prefix)

不使用 Foreign Key,nf_user 新增 2000 個使用者,nf_group 新增 200 個群組,每個使用者分別隸屬三個群組(nf_user_group_ref 共用 6000 筆資料)。

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 筆資料)

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 如下:

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;」指令來查看執行結果,測試數據如下:

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 語法,分析方法如下:

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:

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;」查看執行的時間,執行結果如下:

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,避免程式設計師錯誤的邏輯程式破壞到資料庫中的資料,降低日後系統維護的成本。

Exit mobile version