共计 7222 个字符，预计需要花费 19 分钟才能阅读完成。
这篇文章给大家介绍 MYSQL 外键的坏处有哪些，内容非常详细，感兴趣的小伙伴们可以参考借鉴，希望对大家能有所帮助。
1、前段时间处理新耀的 TX 锁问题，发现由于是外键导致了 INSERT INTO 堵塞，现把外键造成 INSERT INTO 插入不了，给大家分享下。
测试环境：
背景：MySQL 版本 5.6
隔离级别是 RC
存储引擎使用的 INNODB
测试如下：
查看隔离级别：
mysql show variables like %iso%
+—————+—————-+
| Variable_name | Value |
+—————+—————-+
| tx_isolation | READ-COMMITTED |
+—————+—————-+
1 row in set (0.00 sec)
创建测试表 t_pri1,t_fk1 且分别插入记录：
mysql create table t_pri1(id int primary key,name varchar(20));
Query OK, 0 rows affected (0.03 sec)
mysql create table t_fk1(id int primary key,name varchar(20),pid int ,foreign key(pid) references t_pri1(id));
Query OK, 0 rows affected (0.02 sec)
mysql
mysql
mysql insert into t_pri1 values(1, wuhan
Query OK, 1 row affected (0.01 sec)
mysql insert into t_pri1 values(2, hubei
Query OK, 1 row affected (0.00 sec)
mysql insert into t_pri1 values(3, hubei1
Query OK, 1 row affected (0.00 sec)
mysql insert into t_pri1 values(4, hubei2
Query OK, 1 row affected (0.00 sec)
mysql insert into t_fk1 values(1, wuhan ,1);
Query OK, 1 row affected (0.00 sec)
mysql insert into t_fk1 values(2, wuhan1 ,2);
Query OK, 1 row affected (0.01 sec)
可以发现主表上面有一个索引，引用表 t_fk1 上面有 2 个索引一个是主键，另外一个是外键字段上面有一个索引（和 ORACLE 不同，ORACLE 不会自动添加）
mysql insert into t_fk1 values(3, wuhan1 ,3);
Query OK, 1 row affected (0.00 sec)
mysql show index from t_fk1;
+——-+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+——-+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+
| t_fk1 | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | |
| t_fk1 | 1 | pid | 1 | pid | A | 3 | NULL | NULL | YES | BTREE | | |
+——-+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+
2 rows in set (0.00 sec)
mysql show index from t_pri1;
+——–+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+——–+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+
| t_pri1 | 0 | PRIMARY | 1 | id | A | 4 | NULL | NULL | | BTREE | | |
+——–+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+
1 row in set (0.00 sec)
会话 1 执行成功但是事务未提交：
mysql begin
–
Query OK, 0 rows affected (0.00 sec)
mysql update t_pri1 set name= zls where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
会话 2（执行失败，超时后事务回滚）：
mysql insert into t_fk1 values(4, zls1 ,1);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
查看会话 1 的事务信息如下：
mysql select * from information_schema.innodb_trx \G
*************************** 1. row ***************************
trx_id: 579835
trx_state: RUNNING
trx_started: 2017-09-03 23:28:16
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 3
trx_mysql_thread_id: 171
trx_query: select * from information_schema.innodb_trx
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 2
trx_lock_memory_bytes: 360
trx_rows_locked: 1– 只锁定了一行记录
trx_rows_modified: 1
trx_concurrency_tickets: 0
trx_isolation_level: READ COMMITTED
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 10000
trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.00 sec)
查看锁阻塞信息：171 会话堵塞了 172 会话
mysql SELECT
– r.trx_id waiting_trx_id,
– r.trx_mysql_thread_id waiting_thread,
– r.trx_query waiting_query,
– b.trx_id blocking_trx_id,
– b.trx_mysql_thread_id blocking_thread,
– b.trx_query blocking_query
– FROM information_schema.innodb_lock_waits w
– INNER JOIN information_schema.innodb_trx b
– ON b.trx_id = w.blocking_trx_id
– INNER JOIN information_schema.innodb_trx r
– ON r.trx_id = w.requesting_trx_id;
+—————-+—————-+————————————–+—————–+—————–+————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————+
| waiting_trx_id | waiting_thread | waiting_query | blocking_trx_id | blocking_thread | blocking_query |
+—————-+—————-+————————————–+—————–+—————–+————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————+
| 579836 | 172 | insert into t_fk1 values(4, zls1 ,1) | 579835 | 171 | SELECT
r.trx_id waiting_trx_id,
r.trx_mysql_thread_id waiting_thread,
r.trx_query waiting_query,
b.trx_id blocking_trx_id,
b.trx_mysql_thread_id blocking_thread,
b.trx_query blocking_query
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b
ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r
ON r.trx_id = w.requesting_trx_id |
+—————-+—————-+————————————–+—————–+—————–+————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————+
请求的锁信息，会话 172 在资源：123:3:2 请求 S 记录数，会话 171 在资源：123:3:2 持有 X 记录数（结合上面的查询结果得出），也就是当向外键表插入记录时，需要申请对应主表该索引值上面的 S 锁，但是由于主表目前根据该索引值在做 UPDATE 语句且事务没有提交（lock_space,lock_page,lock_rec 相同代表相同的锁资源）：
mysql select * from information_schema.innodb_locks \G
*************************** 1. row ***************************
lock_id: 579836:123:3:2
lock_trx_id: 579836
lock_mode: S
lock_type: RECORD
lock_table: `test`.`t_pri1`
lock_index: PRIMARY
lock_space: 123
lock_page: 3
lock_rec: 2
lock_data: 1
*************************** 2. row ***************************
lock_id: 579835:123:3:2
lock_trx_id: 579835
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t_pri1`
lock_index: PRIMARY
lock_space: 123
lock_page: 3
lock_rec: 2
lock_data: 1
2 rows in set (0.00 sec)
小结：MySQL 和 ORACLE 一样不要使用数据库的主外键来满足业务逻辑和数据的一致性，最好是在业务设计层面来考虑这些。
解放数据库，让数据库就做简单的 DML 和存储功能就好。
关于 MYSQL 外键的坏处有哪些就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。
