MYSQL外键的坏处有哪些

发布时间：2021-11-16 09:17:00 作者：柒染
来源：亿速云阅读：124

这篇文章给大家介绍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> 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;

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| 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;

+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| 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;

+----------------+----------------+--------------------------------------+-----------------+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| 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外键的坏处有哪些就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。

MYSQL外键的坏处有哪些

相关阅读