MySQL 三种外键实现方式以及性能比较

·  阅读 950

   最近在看关于 MySQL 外键的知识,发现关于这个话题的争论比较多。有观点说数据库不应该使用外键,也有观点说数据库应该使用外键约束来保证数据一致性以及引用完整性,还有观点提出用触发器代替外键。记得刚入行的时候,有前辈提过用逻辑外键代替物理外键维护数据一致性。具体哪种实现方式更好没有定论,狐疑之中,决定亲自测试,看看哪种外键实现方式性能更好。

  首先需要说明,MySQL 支持以下五种外键类型:

  • CASCADE
  • SET NULL
  • RESTRICT
  • NO ACTION
  • SET DEFAULT

  但 InnoDB 不允许 DDL 中包含 SET DEFAULT 语句,NO ACTION 效果与 RESTRICT 等价,SET NULL 则要求子表中定义外键的 column 不能设置为 NOT NULL ,其效果与 CASCADE 类似。所以,我们的测试值针对 RESTRICTCASCADE 两种类型。

由于目前只有 InnoDB 支持外键,所以比较测试都使用 InnoDB 引擎

⒈ 测试

⓵ 数据准备

  以客户和订单的关系创建数据表,针对三种外键实现方式分别创建三张客户表和三张订单表,订单和客户通过客户 ID 关联。

  我们首先对 RESTRICT 类型的外键进行测试,所谓 RESTRICT 类型,也就是 InnoDB 的默认外键类型,是指如果要删除或更新的父表中的记录在子表中有与之相关联的记录存在,则禁止对父表中的记录进行删除或更新(对于更新的情况,如果更新的列中不包括与子表中外键关联的列,则更新操作不受影响)。

create database test;
use test;
create table customer (
    id  int auto_increment primary key comment '主键',
    name    char(16) not null default '' comment '客户'
) engine = innodb default charset = 'utf8mb4' collate = 'utf8mb4_general_ci';

create table `order` (
    id  int auto_increment primary key comment '主键',
    customer_id int not null default 0 comment '客户 ID'
) engine = innodb default charset = 'utf8mb4' collate = 'utf8mb4_general_ci';

create table customer_fk (
    id  int auto_increment primary key comment '主键',
    name    char(16) not null default '' comment '客户'
) engine = innodb default charset = 'utf8mb4' collate = 'utf8mb4_general_ci';

create table order_fk (
    id  int auto_increment primary key comment '主键',
    customer_id int not null default 0 comment '客户 ID',
    constraint customer_id_fk
        foreign key (customer_id) references customer_fk(id)
            on update restrict
            on delete restrict
) engine = innodb default charset = 'utf8mb4' collate = 'utf8mb4_general_ci';

create table customer_trigger (
    id  int auto_increment primary key comment '主键',
    name    char(16) not null default '' comment '客户'
) engine = innodb default charset = 'utf8mb4' collate = 'utf8mb4_general_ci';

create table order_trigger (
    id  int auto_increment primary key comment '主键',
    customer_id int not null default 0 comment '客户 ID'
) engine = innodb default charset = 'utf8mb4' collate = 'utf8mb4_general_ci';
# 定义触发器
delimiter //
# 如果客户存在关联的订单,则禁止删除客户
create trigger customer_del before delete on customer_trigger for each row
    begin
        if (select count(*) from order_trigger where order_trigger.customer_id = OLD.id) != 0 then
            signal sqlstate '45000' set mysql_errno = 30001, message_text = 'Can\'t delete record. Foreign key exists in child table!';
        end if;
    end//
# 如果客户存在关联的订单,则禁止变更客户 ID
create trigger customer_update before update on customer_trigger for each row
    begin
        if (OLD.id != NEW.id and (select count(*) from order_trigger where order_trigger.customer_id = OLD.id) != 0) then
            signal sqlstate '45000' set mysql_errno = 30001, message_text = 'Can\'t update record. Foreign key updates to child table restricted!';
        end if;
    end //
# 不允许向订单表中写入不存在的客户 ID
create trigger order_insert before insert on order_trigger for each row
    begin
        if (select count(*) from customer_trigger where customer_trigger.id = NEW.customer_id) = 0 then
            signal sqlstate '45000' set mysql_errno = 30001, message_text = 'Can\'t insert record. Foreign parent key does not exist!';
        end if;
    end //
# 更新订单信息时,不允许将订单的客户 ID 变更为客户表中不存在的客户 ID
create trigger order_update before update on order_trigger for each row
    begin
        if (select count(*) from customer_trigger where customer_trigger.id = NEW.customer_id) = 0 then
            signal sqlstate '45000' set mysql_errno = 30001, message_text = 'Can\'t update record. Foreign parent key does not exist!';
        end if;
    end //
delimiter ;
# 向数据表中写入数据
insert into customer_table (name) values ('aaa'), ('bbb'), ('ccc'), ('ddd'), ('eee'), ('fff'), ('ggg');
insert into `order_table` (customer_id) select id from customer_table;
复制代码

  测试通过触发器设置的外键约束是否起作用

# 我们事先向客户表中添加了 7 条数据,所以客户 ID 最大为 7
mysql> delete from customer_trigger where id = 1;
ERROR 30001 (45000): Can't delete record. Foreign key exists in child table!
 
mysql> update customer_trigger set id = 8 where id = 1;
ERROR 30001 (45000): Can't update record. Foreign key updates to child table restricted!
 
mysql> insert into order_trigger (customer_id) values (18);
ERROR 30001 (45000): Can't insert record. Foreign parent key does not exist!
 
mysql> update order_trigger set customer_id = 18 where id = 2;
ERROR 30001 (45000): Can't update record. Foreign parent key does not exist!
复制代码

  由上述测试结果可以看出,我们通过触发器的方式设置的外键约束效果与物理外键完全相同。

  将外键约束类型由 RESTRICT 改为 CASCADE 。所谓 CASCADE 是指,对父表中的记录的删除或更新操作,也会自动删除或更新子表中相应的记录。SET NULL 的效果与之类似,只是 SET NULL 直接将子表中关联的记录的外键所在的 column 的值设置为了 NULL

alter table order_fk drop constraint customer_id_fk;
alter table order_fk add constraint customer_id_fk foreign key (customer_id) references customer_fk(id) on update cascade on delete cascade;

delimiter //
drop trigger if exists customer_del //
create trigger customer_del after delete on customer_trigger for each row
    begin
        if (select count(*) from order_trigger where order_trigger.customer_id = OLD.id) > 0 then
            delete from order_trigger where order_trigger.customer_id = OLD.id;
        end if ;
    end //

drop trigger if exists customer_update //
create trigger customer_update after update on customer_trigger for each row
    begin
        if (select count(*) from order_trigger where order_trigger.customer_id = old.id) > 0 then
            update order_trigger set order_trigger.customer_id = NEW.id where order_trigger.customer_id = OLD.id;
        end if ;
    end //
delimiter ;
复制代码

  测试修改外键约束类型之后的效果

# 删除 customer 
mysql> select count(*) from order_fk where customer_id = 1;
+----------+
| count(*) |
+----------+
|     8510 |
+----------+
1 row in set (0.01 sec)

mysql> delete from customer_fk where id = 1;
Query OK, 1 row affected (0.07 sec)

mysql> select count(*) from order_fk where customer_id = 1;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)
# 更新 customer
mysql> select count(*) from order_fk where customer_id = 7;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from order_fk where customer_id = 8;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> update customer_fk set id = 8 where id = 7;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select count(*) from order_fk where customer_id = 7;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.01 sec)

mysql> select count(*) from order_fk where customer_id = 8;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)
# 触发器类型外键的测试
# 删除 customer
mysql> select count(*) from order_trigger where customer_id = 1;
+----------+
| count(*) |
+----------+
|     8597 |
+----------+
1 row in set (0.00 sec)

mysql> delete from customer_trigger where id = 1;
Query OK, 1 row affected (0.07 sec)

mysql> select count(*) from order_trigger where customer_id = 1;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)
# 更新 customer
mysql> select count(*) from order_trigger where customer_id = 2;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from order_trigger where customer_id = 8;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> update customer_trigger set id = 8 where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select count(*) from order_trigger where customer_id = 2;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from order_trigger where customer_id = 8;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)
复制代码

⓶ 测试结果

  RESTRICT 类型的外键测试结果

BenchmarkBase-8      	     220	   5077064 ns/op
BenchmarkFK-8        	     231	   5128234 ns/op
BenchmarkTrigger-8   	     219	   5344537 ns/op

BenchmarkBase-8      	     237	   5360287 ns/op
BenchmarkFK-8        	     230	   5205076 ns/op
BenchmarkTrigger-8   	     223	   5359486 ns/op

BenchmarkBase-8      	     223	   5107497 ns/op
BenchmarkFK-8        	     237	   5341078 ns/op
BenchmarkTrigger-8   	     228	   5438774 ns/op

BenchmarkBase-8      	     237	   5087906 ns/op
BenchmarkFK-8        	     222	   5306044 ns/op
BenchmarkTrigger-8   	     249	   5317348 ns/op

BenchmarkBase-8      	     231	   5088855 ns/op
BenchmarkFK-8        	     234	   5270867 ns/op
BenchmarkTrigger-8   	     226	   5544769 ns/op
复制代码

  CASCADE 类型的外键测试结果

BenchmarkBase-8      	     249	   5188411 ns/op
BenchmarkFK-8        	     229	   5194273 ns/op
BenchmarkTrigger-8   	     220	   5454264 ns/op

BenchmarkBase-8      	     220	   5065554 ns/op
BenchmarkFK-8        	     207	   5269882 ns/op
BenchmarkTrigger-8   	     226	   5494027 ns/op

BenchmarkBase-8      	     250	   5258943 ns/op
BenchmarkFK-8        	     235	   5437893 ns/op
BenchmarkTrigger-8   	     225	   5490976 ns/op

BenchmarkBase-8      	     237	   4883005 ns/op
BenchmarkFK-8        	     224	   5319227 ns/op
BenchmarkTrigger-8   	     225	   5670229 ns/op

BenchmarkBase-8      	     234	   5156666 ns/op
BenchmarkFK-8        	     230	   5435542 ns/op
BenchmarkTrigger-8   	     243	   5515869 ns/op
复制代码

⒉ 总结

  从上述测试结果来看,如果单从性能考虑,逻辑外键性能优于物理外键,而物理外键的性能优于触发器实现的外键。但如果再结合实际应用场景,则又需要具体情况具体分析。

  ☘ 先说逻辑外键,这种情况下,数据表只存储数据,任何数据表之间的关联关系都需要在应用中通过代码维护,这就导致数据表中的关联关系不明确。另外,通过代码维护关联关系,则任何可能影响关联关系的操作都需要在一个事务中完成,以保证数据的一致性,例如:

     ☬ 往父表中新增记录,同时在子表中新增关联记录

     ☬ 从父表中删除记录,同时删除子表中的关联记录

  测试中只是定义了客户和订单的关联关系,但实际应用中,客户还会关联购物车、收藏夹等记录,而订单也还会关联商品、支付记录等,这就会导致任何一个修改或删除操作都需要检查关联关系,更新关联关系,甚至删除关联关系。而这些检查/维护关联关系的操作增加了很多额外的磁盘 I/O 以及网络 I/O,在增加了代码复杂度的同时还可能会导致实际效果可能比物理外键差很多。

  ☘ 再说物理外键,这种实现方式在数据库层面明确了数据表之间的关联关系,同时,相对于逻辑外键,这种外键实现方式保证了更强的数据一致性。但是,使用物理外键的弊端在于:

     ☬ RESTRICT 类型的外键会在更新或删除关系表中的数据时检查外键约束的合法性

     ☬ CASCADE 类型的外键则会在更新或删除关系表中的数据时级联更新或删除关联的记录,这种操作对于关联关系比较简单并且数据量比较少的场景不会产生什么问题,但对于关联关系复杂或数据量比较大的场景,更新或删除操作可能会呈现指数级的放大效果

   另外,数据库写操作引起的关联关系检查也会给数据库造成额外的开销,对于那些对性能或并发要求高的场景会造成影响。并且,数据表之间的这种关联关系不利于数据库的水平扩容。

  ☘ 最后说说触发器实现的外键,其效果与物理外键完全相同,但语法却相对复杂,代码量也相对更多,并且性能还相对较差。对于级联更新或删除的情况,也会出现放大效果。这种类型的触发器更适合用在那些不支持外键,但又需要使用外键约束的引擎。

  最后附上测试代码:

package main

import (
	_ "github.com/go-sql-driver/mysql"
	"log"
	"testing"
	"time"
	"xorm.io/xorm"
)

type Order struct {
	Id          int
	CustomerId  int
}

type OrderFk struct {
	Id          int
	CustomerId  int
}

type OrderTrigger struct {
	Id          int
	CustomerId  int
}

var (
	engine  *xorm.Engine
	dsn     string = "root:root@tcp(127.0.0.1:3306)/test?charset=utf8"
)

func init() {
	var err error
	engine, err = xorm.NewEngine("mysql", dsn)

	if err != nil {
		log.Fatal(err)
	}

	engine.SetMaxOpenConns(25)
	engine.SetMaxIdleConns(25)
	engine.SetConnMaxLifetime(5 * time.Minute)
}

func BenchmarkBase(b *testing.B) {
	for i := 0; i < b.N; i ++ {
		if _, err := engine.Insert(&Order{CustomerId:5}); err != nil {
			panic(err)
		}
	}
}

func BenchmarkFK(b *testing.B) {
	for i := 0; i < b.N; i ++ {
		if _, err := engine.Insert(&OrderFk{CustomerId:5}); err != nil {
			panic(err)
		}
	}
}

func BenchmarkTrigger(b *testing.B) {
	for i := 0; i < b.N; i ++ {
		if _, err := engine.Insert(&OrderTrigger{CustomerId:5}); err != nil {
			panic(err)
		}
	}
}
复制代码
测试过程中同时使用了 gorm 和 xorm 两种对象关系映射模型,结果发现 xorm 的速度会稍快于 gorm
分类:
后端
标签:
收藏成功!
已添加到「」, 点击更改