外鍵的設計初衷是為了在數(shù)據(jù)庫端保證對邏輯上相關(guān)聯(lián)的表數(shù)據(jù)在操作上的一致性與完整性。

外鍵在大部分企業(yè)寫的開發(fā)規(guī)范里會直接規(guī)避掉！外鍵有優(yōu)缺點，也并不是說每種場景都不適用，完全沒有必要一刀切。外鍵到底能不能用？下面會針對不同的場景來告訴你答案。

一、外鍵的優(yōu)缺點

優(yōu)點：

• 精簡關(guān)聯(lián)數(shù)據(jù)，減少數(shù)據(jù)冗余避免后期對大量冗余處理的額外運維操作。
• 降低應用代碼復雜性，減少了額外的異常處理相關(guān)數(shù)據(jù)管理全由數(shù)據(jù)庫端處理。
• 增加文檔的可讀性特別是在表設計開始，繪制 ER 圖的時候，邏輯簡單明了，可讀性非常強。

缺點：

• 性能壓力外鍵一般會存在級聯(lián)功能，級聯(lián)更新，級聯(lián)刪除等等。在海量數(shù)據(jù)場景，造成很大的性能壓力。比如插入一條新記錄，如果插入記錄的表有 10 個外鍵，那勢必要對關(guān)聯(lián)的 10 張表逐一檢查插入的記錄是否合理，延誤了正常插入的記錄時間。并且父表的更新會連帶子表加上相關(guān)的鎖。
• 其他功能的靈活性不佳比如，表結(jié)構(gòu)的更新等。

二、外鍵的使用

外鍵參照動作列表：

• CASCADE：級聯(lián)，子表跟隨父表更新外鍵值
• SET NULL：子表更隨主表更新外鍵值為 NULL
• RESTRICT/ NO ACTION：默認，限制父表改動外鍵值
• SET DEFAULT：目前產(chǎn)生的效果和 RESTRICT 相同。

那先來簡單看看 MySQL 里外鍵的用法。MySQL 外鍵僅有 InnoDB 和 NDB 兩種引擎支持，這里只關(guān)注 InnoDB。

本次示例 MySQL 的版本為最新版 8.0.19

示例

下面 f1 是父表，f2、f3、f6 分別代表不同類型的外鍵表，也就是子表。

-- 引用基礎表，也就是父表mysql-(ytt_fk/3305)->create table f1(id int primary key, r1 int, r2 int, r3 int,key idx_r1(r1),key idx_u1 (r2,r3));Query OK, 0 rows affected (0.02 sec)-- 隨著參照表級聯(lián)更新外鍵表，也就是父表更新的話，會級聯(lián)更新子表的外鍵mysql-(ytt_fk/3305)->create table f2(id int primary key, f1_r1 int, mark int, constraint f1_fk_r1 foreign key (f1_r1) references f1(r1) on update cascade);Query OK, 0 rows affected (0.02 sec)-- 隨著參照表更新外鍵值為 NULL，也就是父表更新的話，會級聯(lián)更新子表的外鍵為 NULLmysql-(ytt_fk/3305)->create table f3 (id int primary key, f1_id int, foreign key (f1_id) references f1(id) on update set null);Query OK, 0 rows affected (0.02 sec)-- 多個鍵值外鍵。子表的可以引用父表非主鍵的其他鍵mysql-(ytt_fk/3305)->create table f6 ( id int auto_increment primary key, f1_r2 int, f1_r3 int, foreign key (f1_r2,f1_r3) references f1(r2,r3));Query OK, 0 rows affected (0.02 sec)

場景一：強烈要求數(shù)據(jù)一致性，程序弱化，數(shù)據(jù)庫端強化，表結(jié)構(gòu)改動小，并發(fā)不高的場景。

用一條記錄驗證表 f2 和 f6。從功能性角度來看，外鍵的優(yōu)勢很明顯，在數(shù)據(jù)庫端完全滿足了數(shù)據(jù)完整性校驗。

mysql-(ytt_fk/3305)->insert into f1 values (1,10,100,1000);Query OK, 1 row affected (0.00 sec)mysql-(ytt_fk/3305)->insert into f2 values (1,1);Query OK, 1 row affected (0.01 sec)mysql-(ytt_fk/3305)->insert into f6 values (1,100,1000);Query OK, 1 row affected (0.00 sec)-- 更新引用表 f1mysql-(ytt_fk/3305)->update f1 set id = 2 where id =1;Query OK, 1 row affected (0.01 sec)Rows matched: 1 Changed: 1 Warnings: 0-- f2 也成功級聯(lián)更新mysql-(ytt_fk/3305)->select * from f2;+----+-------+| id | f1_id |+----+-------+| 1 | 2 |+----+-------+1 row in set (0.00 sec)-- 引用表 r2 字段不允許更新，因為表 f6 有針對字段 r2 的外鍵約束。mysql-(ytt_fk/3305)->update f1 set r2 = 11 ;ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`ytt_fk`.`f6`, CONSTRAINT `f6_ibfk_1` FOREIGN KEY (`f1_r2`, `f1_r3`) REFERENCES `f1` (`r2`, `r3`))

場景二：頻繁的數(shù)據(jù)裝載，但是也嚴格要求數(shù)據(jù)庫端保證數(shù)據(jù)一致性。

這里只驗證表 f6，同時克隆一張新表 f6_no_fk，除了沒有外鍵，表結(jié)構(gòu)和 f6 一樣。導入 400W 條樣例數(shù)據(jù)。

-- 導入 f6，有外鍵，時間 32 秒多。mysql-(ytt_fk/3305)->load data infile '/var/lib/mysql-files/f1_sub.dat' into table f6;Query OK, 4000000 rows affected (32.57 sec)Records: 4000000 Deleted: 0 Skipped: 0 Warnings: 0-- 導入 f6_no_fk，沒有外鍵，時間 25 秒多。mysql-(ytt_fk/3305)->load data infile '/var/lib/mysql-files/f1_sub.dat' into table f6_no_fk;Query OK, 4000000 rows affected (25.95 sec)Records: 4000000 Deleted: 0 Skipped: 0 Warnings: 0

從上面看到，單獨的測試導入 400W 條記錄，帶有外鍵的表比非外鍵的表時間上沒有優(yōu)勢。那針對上面的場景優(yōu)化下，關(guān)閉外鍵檢查參數(shù)，導入完成后，再開啟。

mysql-(ytt_fk/3305)->truncate f6;Query OK, 0 rows affected (0.04 sec)-- 關(guān)閉外鍵檢查。mysql-(ytt_fk/3305)->set foreign_key_checks=0;Query OK, 0 rows affected (0.00 sec)-- 重新導入，時間28秒多。mysql-(ytt_fk/3305)->load data infile '/var/lib/mysql-files/f1_sub.dat' into table f6;Query OK, 4000000 rows affected (28.42 sec)Records: 4000000 Deleted: 0 Skipped: 0 Warnings: 0-- 開啟外鍵檢查。mysql-(ytt_fk/3305)->set foreign_key_checks=1;Query OK, 0 rows affected (0.00 sec)

從以上結(jié)果看出，關(guān)閉外鍵檢查后，導入時間和沒有外鍵的表 f6_no_fk 差不多。

場景三：并發(fā)少，事物塊簡單。

接下來再看下簡單的事物塊提交方式，我簡單寫了一個每 500 條記錄提交一次的存儲過程。

DELIMITER $$CREATE DEFINER=`ytt`@`127.0.0.1` PROCEDURE `sp_generate_data`(IN `tb_name` VARCHAR(64), IN `f_number` INT)begindeclare i int default 0;set @@autocommit=0;while i < f_number DO set @stmt = concat("insert into ",tb_name,"(f1_r2,f1_r3) values (ceil(rand()*10),ceil(rand()*10))"); prepare s1 from @stmt; execute s1; set i = i + 1; if mod(i,500)=0 THEN commit; end if;end while;drop prepare s1;commit;set @@autocommit=1;end$$DELIMITER ;

接下來插入 100W 條記錄，

-- 外鍵表寫入總時間為 1 分 14 秒mysql> call sp_generate_data('f6',1000000);Query OK, 0 rows affected (1 min 14.14 sec)-- 非外鍵表寫入時間為 1 分 8 秒mysql> call sp_generate_data('f6_no_fk',1000000);Query OK, 0 rows affected (1 min 8.45 sec)-- 關(guān)閉外鍵檢查mysql> set foreign_key_checks=0;Query OK, 0 rows affected (0.00 sec)-- 時間為 1 分 4 秒mysql> call sp_generate_data('f6',1000000);Query OK, 0 rows affected (1 min 4.28 sec)mysql> set foreign_key_checks=1;Query OK, 0 rows affected (0.00 sec)

從測試的結(jié)果來看，有外鍵和沒有外鍵的檢索時間在這樣的場景下也相差無幾。

場景四：主表的外鍵引用字段類型要擴充，原來的數(shù)據(jù)溢出，沒法保存更大的值。

比如此時字段 r2 定義的數(shù)據(jù)類型不合適了，需要更改為大點的，比如以下，直接修改會報錯，

mysql-(ytt_fk/3305)->alter table f1 change r2 r2 bigint;ERROR 3780 (HY000): Referencing column 'f1_r2' and referenced column 'r2' in foreign key constraint 'f6_ibfk_1' are incompatible.mysql-(ytt_fk/3305)->alter table f6 change f1_r2 f1_r2 bigint;ERROR 3780 (HY000): Referencing column 'f1_r2' and referenced column 'r2' in foreign key constraint 'f6_ibfk_1' are incompatible.

那怎么改呢？需要先把外鍵刪掉，修改完了類型，再加上約束。這種場景就不太適合用外鍵。

mysql-(ytt_fk/3305)->alter table f6 drop constraint f6_ibfk_1;Query OK, 0 rows affected (0.00 sec)Records: 0 Duplicates: 0 Warnings: 0mysql-(ytt_fk/3305)->alter table f6 change f1_r2 f1_r2 bigint;Query OK, 0 rows affected (0.04 sec)Records: 0 Duplicates: 0 Warnings: 0mysql-(ytt_fk/3305)->alter table f1 change r2 r2 bigint;Query OK, 100000 rows affected (0.73 sec)Records: 100000 Duplicates: 0 Warnings: 0mysql-(ytt_fk/3305)->alter table f6 add foreign key (f1_r2,f1_r3) references f1(r2,r3);Query OK, 0 rows affected (0.03 sec)Records: 0 Duplicates: 0 Warnings: 0

場景五：子表有觸發(fā)器需求來更新必要的字段。

那關(guān)于這點就是，子表的觸發(fā)器不會隨著父表的更新級聯(lián)應用，也就是此時觸發(fā)器失效。舉個例子，往 f2 上添加一個 before update 觸發(fā)器。

-- 前置更新觸發(fā)器CREATE TRIGGER `tr_af_update` BEFORE UPDATE ON `f2` FOR EACH ROW set new.mark = new.f1_r1;mysql-(ytt_fk/3305)->insert into f2 values (1,10,5);Query OK, 1 row affected (0.00 sec)mysql-(ytt_fk/3305)->select * from f2;+----+-------+------+| id | f1_r1 | mark |+----+-------+------+| 1 | 10 | 5 |+----+-------+------+1 row in set (0.00 sec)-- 更新父表，mysql-(ytt_fk/3305)->update f1 set r1 = 2 where r1 = 10;Query OK, 5133 rows affected (0.15 sec)Rows matched: 5133 Changed: 5133 Warnings: 0-- 子表 f2對應的級聯(lián)做了更改，但是觸發(fā)器動作沒執(zhí)行。mysql-(ytt_fk/3305)->select * from f2;+----+-------+------+| id | f1_r1 | mark |+----+-------+------+| 1 | 2 | 5 |+----+-------+------+1 row in set (0.00 sec)-- 正常的操作應該這樣mysql-(ytt_fk/3305)->update f2 set id = 2;Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0-- mark字段對應的克隆成了f1_r1字段的值。mysql-(ytt_fk/3305)->select * from f2;+----+-------+------+| id | f1_r1 | mark |+----+-------+------+| 2 | 2 | 2 |+----+-------+------+1 row in set (0.00 sec)

場景六：父表為分區(qū)表，有外鍵的需求。

那針對分區(qū)表，暫時不支持子表以分區(qū)表為父表的外鍵。

mysql-(ytt_fk/3305)->create table f1_partition like f1;Query OK, 0 rows affected (0.02 sec)mysql-(ytt_fk/3305)->alter table f1_partition partition by key() partitions 4;Query OK, 0 rows affected (0.10 sec)Records: 0 Duplicates: 0 Warnings: 0mysql-(ytt_fk/3305)->create table f7 ( id int primary key, f1_partition_id int, foreign key (f1_partition_id) references f1_partition(id));ERROR 1506 (HY000): Foreign keys are not yet supported in conjunction with partitioning

場景七：日常并發(fā)很高的場景，應該盡量減少相關(guān)事務鎖的范圍和量級。

那舉個簡單例子，看看有外鍵情況下，父表更新，子表級聯(lián)加鎖的情形。

-- SESSION 1mysql-(ytt_fk/3305)->begin;Query OK, 0 rows affected (0.00 sec)mysql-(ytt_fk/3305)->update f1 set r2 = 101 where r2 = 100;Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql-(ytt_fk/3305)->select sys.ps_thread_id(connection_id()) as cid;+------+| cid |+------+| 47 |+------+1 row in set (0.00 sec)

總共有 11 個鎖，也就簡單的執(zhí)行了下 Update，而且更新的只是一行。

-- SESSION 2mysql-((none)/3305)->select count(*) from performance_schema.data_locks where thread_id = 47;+----------+| count(*) |+----------+| 11 |+----------+1 row in set (0.00 sec)

查看鎖的細化，父有 f1 有 5 個鎖，子表 f6 有 6 個鎖。

這都是 MySQL 為了保證數(shù)據(jù)一致性強制加的，這點在 TPS 要求比較高的場景肯定不合適，

mysql-((none)/3305)->select object_name,lock_type,lock_mode,lock_status,lock_data from performance_schema.data_locks where thread_id = 47 order by object_name;+-------------+-----------+---------------+-------------+------------------------+| object_name | lock_type | lock_mode | lock_status | lock_data |+-------------+-----------+---------------+-------------+------------------------+| f1 | TABLE | IX | GRANTED | NULL || f1 | RECORD | X | GRANTED | supremum pseudo-record || f1 | RECORD | X | GRANTED | 100, 100, 1 || f1 | RECORD | X,REC_NOT_GAP | GRANTED | 1 || f1 | RECORD | X,GAP | GRANTED | 101, 100, 1 || f6 | TABLE | IS | GRANTED | NULL || f6 | RECORD | S,REC_NOT_GAP | GRANTED | 100, 100, 12 || f6 | TABLE | IX | GRANTED | NULL || f6 | RECORD | X,REC_NOT_GAP | GRANTED | 12 || f6 | RECORD | X,REC_NOT_GAP | GRANTED | 101, 100, 12 || f6 | RECORD | S,GAP | GRANTED | 101, 100, 12 |+-------------+-----------+---------------+-------------+------------------------+11 rows in set (0.00 sec)

三、外鍵的限制：

1. 僅有 InnoDB 和 NDB 引擎支持。

2. 不支持虛擬列。

3. 不支持臨時表。

4. 外鍵列以及引用列數(shù)據(jù)類型、字符集、校對規(guī)則都得一致。

5. 外鍵列以及引用列都必須建立索引。

6. 外鍵引用多個列的，列順序必須一致。

7. 大對象字段不能作為引用列。

8. constraint 命名必須在單個 database 里唯一。

9. 外鍵級聯(lián)更新操作不會觸發(fā)子表上的觸發(fā)器。

10. 不支持分區(qū)表。

總結(jié)

本文主要從幾個例子來演示了，外鍵是否應該使用以及在哪些場景下使用，讓大家了解外鍵的詳細需求。

從上面我描述的幾個場景來說，場景 1,2,3 很適合用外鍵；場景 4,5,6,7 就不太適合用外鍵；可以把外鍵功能放在數(shù)據(jù)庫之外實現(xiàn)。

---

關(guān)于 MySQL 的技術(shù)內(nèi)容，你們還有什么想知道的嗎？趕緊留言告訴小編吧！

總結(jié)

以上是生活随笔為你收集整理的mysql 高版本检索外键_第05期：外键到底能不能用？的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。