mysql 外來鍵索引入門介紹，為什麼工作中很少有人使用？

發表於2024-02-12
MySql索引

背景

以前工作學習中，一直被告誡不要使用外來鍵，所以也沒有仔細整理過。

這裡記錄一下筆記。

外來鍵

是什麼？

MySQL 的外來鍵（Foreign Key）是一種關係型資料庫中用於建立表與表之間關聯關係的重要工具。

外來鍵定義了兩個表之間的引用關係，它連線了兩個表，使它們之間建立起一定的聯絡。

外來鍵用於維護表與表之間的一致性和完整性，確保資料的準確性和可靠性。

如何定義

在建立表時，可以使用 FOREIGN KEY 關鍵字來定義外來鍵。外來鍵通常與 REFERENCES 關鍵字一起使用，用於指定引用的表和列。

外來鍵通常關聯到另一個表的主鍵列，這樣它就能確保引用的資料是一致的。

CREATE TABLE 表名 (
    列1 資料型別,
    列2 資料型別,
    ...
    FOREIGN KEY (外來鍵列) REFERENCES 關聯表名(關聯列)
);

級聯操作

外來鍵還可以定義級聯操作，包括 CASCADE、SET NULL、SET DEFAULT 和 NO ACTION。

這些操作定義了在主表中進行更新或刪除操作時，對應的外來鍵列在從表中的行的處理方式。

例如，CASCADE 表示主表的更新或刪除操作將在從表上進行相應的級聯操作。

CREATE TABLE 表1 (
    列1 資料型別 PRIMARY KEY
);
CREATE TABLE 表2 (
    列2 資料型別,
    列3 資料型別,
    列4 資料型別,
    FOREIGN KEY (列2) REFERENCES 表1(列1) ON DELETE CASCADE
);

外來鍵的限制

  • 外來鍵的引用列必須是唯一索引（Unique Index）或主鍵，以確保引用的資料是唯一的。
  • 外來鍵列和引用列的資料型別必須相同。
  • InnoDB 儲存引擎支援外來鍵，而 MyISAM 不支援。

外來鍵的優缺點：

優點：

  1. 資料一致性： 外來鍵可以確保表與表之間的關聯關係，維護資料的一致性。透過外來鍵約束，可以避免插入或更新無效的引用，保持資料的完整性。
  2. 資料完整性： 外來鍵可以防止誤刪除關聯的資料。如果有關聯關係的資料存在，刪除主表中的記錄時，外來鍵約束可以阻止刪除，或者透過級聯操作刪除相關的從表資料。
  3. 關聯查詢： 外來鍵使得關聯查詢更加方便。可以透過 JOIN 操作輕鬆地獲取關聯表的資訊，提高查詢的靈活性。

缺點：

  1. 效能開銷： 外來鍵可能引入一定的效能開銷。特別是在大規模的資料庫中，維護外來鍵關係可能會影響插入、更新和刪除操作的效能。
  2. 複雜性： 外來鍵關係可能增加資料庫結構的複雜性。在設計資料庫時，需要仔細考慮外來鍵的引入，以及相關的級聯操作和約束。
  3. 不同資料庫支援不同： 不同的資料庫管理系統對外來鍵的支援程度和實現方式可能有所不同。在切換資料庫引擎或遷移資料時，可能會遇到相容性問題。

簡單例子

mysql 5.7

建立外來鍵約束

當建立兩張表，其中一張是使用者表，另一張是使用者擴充表，可以使用外來鍵來建立它們之間的關聯。

以下是一個具體的例子：

-- 建立使用者表
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(255) NOT NULL
);

-- 建立使用者擴充表，並新增外來鍵關聯
CREATE TABLE user_extra (
    user_id INT PRIMARY KEY,
    remark VARCHAR(255),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

後設資料查詢

可以透過 sql 查詢對應的引用資訊

SELECT 
    TABLE_NAME,
    COLUMN_NAME,
    CONSTRAINT_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM 
    information_schema.KEY_COLUMN_USAGE
WHERE 
    REFERENCED_TABLE_SCHEMA = 'test';

資料如下：

+------------+-------------+-------------------+-----------------------+------------------------+
| TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME   | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+------------+-------------+-------------------+-----------------------+------------------------+
| user_extra | user_id     | user_extra_ibfk_1 | users                 | id                     |
+------------+-------------+-------------------+-----------------------+------------------------+

初始化資料

insert into users(id, username) values (1, 'u1');
insert into user_extra(user_id, remark) values (1, 'u1-ex');

如下:

mysql> select * from users;
+----+----------+
| id | username |
+----+----------+
|  1 | u1       |
+----+----------+
1 row in set (0.00 sec)

mysql> select * from user_extra;
+---------+--------+
| user_id | remark |
+---------+--------+
|       1 | u1-ex  |
+---------+--------+
1 row in set (0.00 sec)

測試刪除級聯

我們直接把 user 表刪除

delete from users where id = 1;

此時 user_extra 也同步被清空了。

mysql> select * from user_extra;
Empty set (0.00 sec)

小結

外來鍵對於保證資料一致性，還是比較方便的。

但是為什麼現在工作中很少見到呢？

不建議使用的情況：

在某些情況下，可能會有人不太建議使用外來鍵，主要基於以下考慮：

  1. 效能最佳化： 有些資料庫設計師和開發者更傾向於手動管理關聯關係，以更好地控制效能。手動維護關聯關係可能允許更靈活的最佳化策略。
  2. 水平分片： 在水平分片的情況下，外來鍵可能會增加複雜性。某些資料庫系統對分片上的外來鍵支援可能有限，因此在這種情況下可能會選擇不使用外來鍵。
  3. 微服務架構： 在微服務架構中，服務之間的解耦是一個關鍵設計原則。有些團隊認為外來鍵引入了過多的服務之間的耦合，因此選擇在微服務架構中限制外來鍵的使用。

每一種技術都有適合的場景，我們結合自己的業務來實現。

擴充閱讀

MySQL Index 索引系統講解

本文由部落格一文多發平臺 OpenWrite 釋出！

相關文章