fbpx

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

by  on  in Linux, 程式設計 • 0 Comments

簡介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 筆資料）。

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

發佈留言