mysql 高版本检索外键_第05期:外键到底能不能用?
外鍵的設計初衷是為了在數(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)容,希望文章能夠幫你解決所遇到的問題。
- 上一篇: 星巴克一杯咖啡多少钱啊?
- 下一篇: “瑟瑟凉海风”下一句是什么