外键，是用来保证数据参照完整性的。mysql5.7中，数据库表的默认存储引擎为InnoDB，InnoDB存储引擎完整支持外键约束。

    外键约束定义如下：

[CONSTRAINT [symbol] FOREIGN KEY
[index_name] (index_col_name, ...)
REFERENCES tbl_name (index_col_name, ...)
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION

 

    定义中，reference_option有RESTRICT、CASCADE、SET NULL、NO ACTION四个参数，每个对应作用不同：

 

    1，RESTRICT：当父表发生dml中的delete或update操作时，抛出错误，提示不允许此类操作发生。当没指定option时，这是默认设置。

 

    2，CASCADE：当父表发生dml中的delete或update操作时，子表对应的数据也被delete或update。

 

    3，SET NULL：当父表发生dml中的delete或update操作时，子表对应的数据被更新为NULL值。

   

    4，NO ACTION：作用和RESTRICT一样。

 

外键重点知识

    我们先建立基本父表和子表，并写入数据：

mysql> create table person(
    -> person_id int primary key,
    -> person_name varchar(20
));
Query OK, 0 rows affected (0.02 sec)

mysql> create table job(
    -> job_id int primary key,
    -> person_id int,
    -> job_name varchar(30
),
    -> constraint fk_person_id foreign key(person_id) references person(person_id)
    -> )
;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into person values (1,'李白');
Query OK, 1 row affected (0.00 sec)

mysql> insert into person values (2,'杜甫');
Query OK, 1 row affected (0.01 sec)

mysql> insert into job values (1,1,'诗人');
Query OK, 1 row affected (0.00 sec)

 

重点1

 

    mysql InnoDB存储引擎对于建立的外键的列，默认会自动建立索引，这就可以很好的避免外键列上无索引而导致的死锁问题出现。

 

mysql> show index from job\G;
*************************** 1. row ***************************
        Table: job
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: job_id
    Collation: A
  Cardinality: 1
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: job
   Non_unique: 1
     Key_name: fk_person_id
 Seq_in_index: 1
  Column_name: person_id
    Collation: A
  Cardinality: 1
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
2 rows in set (0.00 sec)

 

 重点2

 

    由于MySQL的外键检查是即时检查的，因此子表中导入的每一行都是即时进行外键检查。对于大批量的导入操作，将花费太多时间进行外键检查，为了节约时间，我们在导入数据时可以设置忽视外键的检查。

 

mysql> show variables like 'foreign_key_checks';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| foreign_key_checks | ON    |
+--------------------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> set foreign_key_checks='OFF';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'foreign_key_checks';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| foreign_key_checks | OFF   |
+--------------------+-------+
1 row in set, 1 warning (0.00 sec)

 

 重点3

 

    当子表中进行外键值得插入或者更新时。为了保证数据一致性，此时会对父表请求加上一个SHARE模式的锁（即S锁）。所以当父表中存在X锁时（例如表中存在update或者delete操作时），子表上的操作会被阻塞。

 

    A会话更新person表：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update person set person_name='白居易' where person_id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>

 

    B会话插入数据，发现阻塞：

mysql> insert into job values(2,2,'诗人');

 

    当A会话rollback后，发现B会话执行成功：

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into job values(2,2,'诗人');
Query OK, 1 row affected (1 min 58.11 sec)

 

    在使用mysql外键过程中，注意以上几点，基本上在很好使用外键完整约束的基础上，保障好数据库的性能。

    

注意点：oracle外键创建后，默认是不会在外键约束上建立索引，所以在oracle数据库中，经常出现因外键问题导致的性能低下，作为dba需要特别注意！