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外鍵的壞處有哪些就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。