第05期：外键到底能不能用？

头像头饰
爱可生开源社区
    阅读 9 分钟
    1

    SQL调优 头图.png

    这篇主要说明表属性 - 外键。

    外键的设计初衷是为了在数据库端保证对逻辑上相关联的表数据在操作上的一致性与完整性。

    外键在大部分企业写的开发规范里会直接规避掉！外键有优缺点，也并不是说每种场景都不适用，完全没有必要一刀切。外键到底能不能用？下面会针对不同的场景来告诉你答案。

    一、外键的优缺点

    优点：

    • 精简关联数据，减少数据冗余

      避免后期对大量冗余处理的额外运维操作。

    • 降低应用代码复杂性，减少了额外的异常处理

      相关数据管理全由数据库端处理。

    • 增加文档的可读性

      特别是在表设计开始，绘制 ER 图的时候，逻辑简单明了，可读性非常强。

    缺点：

    • 性能压力

      外键一般会存在级联功能，级联更新，级联删除等等。在海量数据场景，造成很大的性能压力。比如插入一条新记录，如果插入记录的表有 10 个外键，那势必要对关联的 10 张表逐一检查插入的记录是否合理，延误了正常插入的记录时间。并且父表的更新会连带子表加上相关的锁。

    • 其他功能的灵活性不佳

      比如，表结构的更新等。

    二、外键的使用

    外键参照动作列表：

    • CASCADE：级联，子表跟随父表更新外键值
    • SET NULL：子表更随主表更新外键值为 NULL
    • RESTRICT/ 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，也就是父表更新的话，会级联更新子表的外键为 NULL
mysql-(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)

-- 更新引用表 f1
mysql-(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_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)

    从以上结果看出，关闭外键检查后，导入时间和没有外键的表 f6_no_fk 差不多。

    场景三：并发少，事物块简单。

    接下来再看下简单的事物块提交方式，我简单写了一个每 500 条记录提交一次的存储过程。

    DELIMITER $$
CREATE DEFINER=`ytt`@`127.0.0.1` PROCEDURE `sp_generate_data`(IN `tb_name` VARCHAR(64), IN `f_number` INT)
begin
declare 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)

-- 关闭外键检查
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)

    从测试的结果来看，有外键和没有外键的检索时间在这样的场景下也相差无几。

    场景四：主表的外键引用字段类型要扩充，原来的数据溢出，没法保存更大的值。

    比如此时字段 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: 0

mysql-(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: 0

mysql-(ytt_fk/3305)->alter table f1 change r2 r2 bigint;
Query OK, 100000 rows affected (0.73 sec)
Records: 100000  Duplicates: 0  Warnings: 0

mysql-(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: 0

mysql-(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 1
mysql-(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: 0


mysql-(ytt_fk/3305)->select sys.ps_thread_id(connection_id()) as cid;
+------+
| cid  |
+------+
|   47 |
+------+
1 row in set (0.00 sec)

    总共有 11 个锁，也就简单的执行了下 Update，而且更新的只是一行。

    -- SESSION 2 
mysql-((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 就不太适合用外键；可以把外键功能放在数据库之外实现。

    杨涛涛自媒体.png

    mysql数据库
    爱可生开源社区
    426 声望207 粉丝

    成立于 2017 年，以开源高质量的运维工具、日常分享技术干货内容、持续的全国性的社区活动为社区己任；目前开源的产品有：SQL审核工具 SQLE，分布式中间件 DBLE、数据传输组件DTLE。

    « 上一篇
    分布式 | ddl 失败在 dble 中排查过程
    下一篇 »
    【爱可生公开课】如何应对让大家头疼的MySQL hash sharding扩容？

    引用和评论

    推荐阅读
    头像
    Oracle 迁移到 OB 过程中有哪些不兼容的 DDL 语法？

    爱可生开源社区阅读 46

    头像
    在Java程序中监听mysql的binlog

    huan1993赞 11阅读 2k

    头像
    Mysql数据库sql优化案例

    杜若赞 5阅读 2.1k

    头像
    数据集汇总｜18个电影/音乐数据集汇总，覆盖影片/歌曲推荐、电影评价、歌词识别、音乐流派······

    超神经HyperAI赞 3阅读 1.1k

    头像
    使用 MongoDB 构建 AI：Gradient Accelerator Block 如何在几秒钟内让您从零开发 AI

    MongoDB数据平台赞 1阅读 12.9k

    头像
    MongoDB Atlas与YoMio.AI近乎完美适配:推理更快速、查询更灵活、场景更丰富

    MongoDB数据平台赞 1阅读 12.4k

    头像
    MongoDB 8.0已全面可用

    MongoDB数据平台阅读 11.9k

    0 条评论
    得票最新