MYSQL外键的坏处有哪些

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