共計(jì) 7222 個(gè)字符，預(yù)計(jì)需要花費(fèi) 19 分鐘才能閱讀完成。

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

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

測(cè)試環(huán)境：

背景：MySQL 版本 5.6

隔離級(jí)別是 RC

存儲(chǔ)引擎使用的 INNODB

測(cè)試如下：

查看隔離級(jí)別：

mysql show variables like %iso%

+—————+—————-+

| Variable_name | Value          |

+—————+—————-+

| tx_isolation  | READ-COMMITTED |

+—————+—————-+

1 row in set (0.00 sec)

創(chuàng)建測(cè)試表 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)主表上面有一個(gè)索引，引用表 t_fk1 上面有 2 個(gè)索引一個(gè)是主鍵，另外一個(gè)是外鍵字段上面有一個(gè)索引（和 ORACLE 不同，ORACLE 不會(huì)自動(dòng)添加）

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)

會(huì)話 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

會(huì)話 2（執(zhí)行失敗，超時(shí)后事務(wù)回滾）：

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

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

查看會(huì)話 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 會(huì)話堵塞了 172 會(huì)話

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 |

+—————-+—————-+————————————–+—————–+—————–+————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————+

請(qǐng)求的鎖信息，會(huì)話 172 在資源：123:3:2 請(qǐng)求 S 記錄數(shù)，會(huì)話 171 在資源：123:3:2 持有 X 記錄數(shù)（結(jié)合上面的查詢(xún)結(jié)果得出），也就是當(dāng)向外鍵表插入記錄時(shí)，需要申請(qǐng)對(duì)應(yīng)主表該索引值上面的 S 鎖，但是由于主表目前根據(jù)該索引值在做 UPDATE 語(yǔ)句且事務(wù)沒(méi)有提交（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ù)庫(kù)的主外鍵來(lái)滿(mǎn)足業(yè)務(wù)邏輯和數(shù)據(jù)的一致性，最好是在業(yè)務(wù)設(shè)計(jì)層面來(lái)考慮這些。

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

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