宝安网站建设哪家好,wordpress只显示到菜单,河源市住房城乡和建设局网站,免费seo免费培训外键的设计初衷是为了在数据库端保证对逻辑上相关联的表数据在操作上的一致性与完整性。外键在大部分企业写的开发规范里会直接规避掉#xff01;外键有优缺点#xff0c;也并不是说每种场景都不适用#xff0c;完全没有必要一刀切。外键到底能不能用#xff1f;下面会针对…外键的设计初衷是为了在数据库端保证对逻辑上相关联的表数据在操作上的一致性与完整性。外键在大部分企业写的开发规范里会直接规避掉外键有优缺点也并不是说每种场景都不适用完全没有必要一刀切。外键到底能不能用下面会针对不同的场景来告诉你答案。一、外键的优缺点优点精简关联数据减少数据冗余避免后期对大量冗余处理的额外运维操作。降低应用代码复杂性减少了额外的异常处理相关数据管理全由数据库端处理。增加文档的可读性特别是在表设计开始绘制 ER 图的时候逻辑简单明了可读性非常强。缺点性能压力外键一般会存在级联功能级联更新级联删除等等。在海量数据场景造成很大的性能压力。比如插入一条新记录如果插入记录的表有 10 个外键那势必要对关联的 10 张表逐一检查插入的记录是否合理延误了正常插入的记录时间。并且父表的更新会连带子表加上相关的锁。其他功能的灵活性不佳比如表结构的更新等。二、外键的使用外键参照动作列表CASCADE级联子表跟随父表更新外键值SET NULL子表更随主表更新外键值为 NULLRESTRICT/ NO ACTION默认限制父表改动外键值SET DEFAULT目前产生的效果和 RESTRICT 相同。那先来简单看看 MySQL 里外键的用法。MySQL 外键仅有 InnoDB 和 NDB 两种引擎支持这里只关注 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)-- 随着参照表级联更新外键表也就是父表更新的话会级联更新子表的外键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也就是父表更新的话会级联更新子表的外键为 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)场景一强烈要求数据一致性程序弱化数据库端强化表结构改动小并发不高的场景。用一条记录验证表 f2 和 f6。从功能性角度来看外键的优势很明显在数据库端完全满足了数据完整性校验。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 也成功级联更新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))场景二频繁的数据装载但是也严格要求数据库端保证数据一致性。这里只验证表 f6同时克隆一张新表 f6_no_fk除了没有外键表结构和 f6 一样。导入 400W 条样例数据。-- 导入 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 条记录带有外键的表比非外键的表时间上没有优势。那针对上面的场景优化下关闭外键检查参数导入完成后再开启。mysql-(ytt_fk/3305)-truncate f6;Query OK, 0 rows affected (0.04 sec)-- 关闭外键检查。mysql-(ytt_fk/3305)-set foreign_key_checks0;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_checks1;Query OK, 0 rows affected (0.00 sec)从以上结果看出关闭外键检查后导入时间和没有外键的表 f6_no_fk 差不多。场景三并发少事物块简单。接下来再看下简单的事物块提交方式我简单写了一个每 500 条记录提交一次的存储过程。DELIMITER $$CREATE DEFINERytt127.0.0.1 PROCEDURE sp_generate_data(IN tb_name VARCHAR(64), IN f_number INT)begindeclare i int default 0;set autocommit0;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 autocommit1;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)-- 关闭外键检查mysql set foreign_key_checks0;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_checks1;Query OK, 0 rows affected (0.00 sec)从测试的结果来看有外键和没有外键的检索时间在这样的场景下也相差无几。场景四主表的外键引用字段类型要扩充原来的数据溢出没法保存更大的值。比如此时字段 r2 定义的数据类型不合适了需要更改为大点的比如以下直接修改会报错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场景五子表有触发器需求来更新必要的字段。那关于这点就是子表的触发器不会随着父表的更新级联应用也就是此时触发器失效。举个例子往 f2 上添加一个 before update 触发器。-- 前置更新触发器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对应的级联做了更改但是触发器动作没执行。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)场景六父表为分区表有外键的需求。那针对分区表暂时不支持子表以分区表为父表的外键。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场景七日常并发很高的场景应该尽量减少相关事务锁的范围和量级。那举个简单例子看看有外键情况下父表更新子表级联加锁的情形。-- 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 个锁也就简单的执行了下 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 为了保证数据一致性强制加的这点在 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. 外键列以及引用列数据类型、字符集、校对规则都得一致。5. 外键列以及引用列都必须建立索引。6. 外键引用多个列的列顺序必须一致。7. 大对象字段不能作为引用列。8. constraint 命名必须在单个 database 里唯一。9. 外键级联更新操作不会触发子表上的触发器。10. 不支持分区表。总结本文主要从几个例子来演示了外键是否应该使用以及在哪些场景下使用让大家了解外键的详细需求。从上面我描述的几个场景来说场景 1,2,3 很适合用外键场景 4,5,6,7 就不太适合用外键可以把外键功能放在数据库之外实现。关于 MySQL 的技术内容你们还有什么想知道的吗赶紧留言告诉小编吧
