这篇文章给大家介绍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外键的坏处有哪些就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。
原创文章，作者：carmelaweatherly，如若转载，请注明出处：https://blog.ytso.com/204163.html