MYSQL外键的坏处有哪些
发布时间：2021-12-20 12:15:05 所属栏目：移动互联 来源：互联网
导读：这篇文章给大家介绍MYSQL外键的坏处有哪些，内容非常详细，感兴趣的小伙伴们可以参考借鉴，希望对大家能有所帮助。 1、前段时间处理新耀的TX锁问题，发现由于是外键导致了INSERT INTO堵塞，现把外键造成INSERT INTO 插入不了，给大家分享下。 测试环境： 背景
|
这篇文章给大家介绍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外键的坏处有哪些就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。 （编辑：财气旺网 - 海宁网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐