這篇文章給大家介紹MySQL外鍵的壞處有哪些，內(nèi)容非常詳細(xì)，感興趣的小伙伴們可以參考借鑒，希望對大家能有所幫助。

創(chuàng)新互聯(lián)公司2013年至今，是專業(yè)互聯(lián)網(wǎng)技術(shù)服務(wù)公司，擁有項(xiàng)目成都網(wǎng)站設(shè)計(jì)、成都做網(wǎng)站、外貿(mào)網(wǎng)站建設(shè)網(wǎng)站策劃，項(xiàng)目實(shí)施與項(xiàng)目整合能力。我們以讓每一個夢想脫穎而出為使命，1280元臨潁做網(wǎng)站,已為上家服務(wù),為臨潁各地企業(yè)和個人服務(wù),聯(lián)系電話:028-86922220

1、前段時(shí)間處理新耀的TX鎖問題，發(fā)現(xiàn)由于是外鍵導(dǎo)致了INSERT INTO堵塞，現(xiàn)把外鍵造成INSERT INTO 插入不了，給大家分享下。

測試環(huán)境：

背景：MySQL版本 5.6

隔離級別是RC

存儲引擎使用的INNODB

測試如下：

查看隔離級別：

mysql> show variables like '%iso%';

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

| Variable_name | Value          |

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

| tx_isolation  | READ-COMMITTED |

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

1 row in set (0.00 sec)

創(chuàng)建測試表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)

可以發(fā)現(xiàn)主表上面有一個索引，引用表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執(zhí)行成功但是事務(wù)未提交：

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（執(zhí)行失敗，超時(shí)后事務(wù)回滾）：

mysql> insert into t_fk1 values(4,'zls1',1);

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

查看會話1的事務(wù)信息如下：

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記錄數(shù)，會話171在資源：123:3:2持有X記錄數(shù)（結(jié)合上面的查詢結(jié)果得出），也就是當(dāng)向外鍵表插入記錄時(shí)，需要申請對應(yīng)主表該索引值上面的S鎖，但是由于主表目前根據(jù)該索引值在做UPDATE語句且事務(wù)沒有提交（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)

小結(jié)：MySQL和ORACLE一樣不要使用數(shù)據(jù)庫的主外鍵來滿足業(yè)務(wù)邏輯和數(shù)據(jù)的一致性，最好是在業(yè)務(wù)設(shè)計(jì)層面來考慮這些。

解放數(shù)據(jù)庫，讓數(shù)據(jù)庫就做簡單的DML和存儲功能就好。

關(guān)于MYSQL外鍵的壞處有哪些就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，可以學(xué)到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。