一、外键使用
在MySQL 3.23版本后，InnoDB引擎类型的表支持了外键约束，外键主要用于引用和参照完整性的约束检查。外键的好处：可以使得两张表关联，保证数据的一致性和实现一些级联操作。但是外键的使用是有一些必要条件的：
1. 两个表必须是InnoDB表，MyISAM表暂时不支持外键（据说以后的版本有可能支持，但至少目前不支持）；
2. 外键列必须建立了索引，MySQL 4.1.2以后的版本在建立外键时会自动创建索引，但如果在较早的版本则需要显式建立；
3. 被外键引用的列必须是主键或唯一索引，不然则无法创建外键；
4. 外键关系的两个表的列必须是数据类型相似，也就是可以相互转换类型的列，比如int和tinyint可以，而int和char则不可以；
在InnoDB存储引擎中，对于一个外键列，如果没有显示地对这个列加索引，InnoDB存储引擎会自动对其加一个索引，因为这样可以避免表锁。这比Oracle数据库做得好，Oracle数据库不会自动添加索引，用户必须自己手动添加，这也导致了Oracle数据库中可能产生死锁。
外键的定义语法：
|
1 2 3 4 |
[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...) REFERENCES tbl_name (index_col_name, ...) [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}] [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}] |
该语法可以在CREATE TABLE和ALTER TABLE时使用，如果不指定CONSTRAINT symbol，MySQL会自动生成一个名字。对于ON DELETE、ON UPDATE表示事件触发限制。下面通过CREATE TABLE语法进行一个简单的外键创建操作：
|
1 2 3 4 5 6 7 8 9 10 11 12 |
# 创建parent表; create table parent( tag_id int primary key auto_increment not null, tag_name varchar(20) ); # 创建child表; create table child( article_id int primary key auto_increment not null, article_tag int(11), CONSTRAINT tag_at FOREIGN KEY (article_tag) REFERENCES parent(tag_id) ); |
对于child表，可以看出我在创建时并没有为article_tag创建一个索引。但是通过show create table可以发现InnoDB存储引擎自动为外键约束的列article_tag添加了索引。
|
1 2 3 4 5 6 7 |
CREATE TABLE `child` ( `article_id` int(11) NOT NULL AUTO_INCREMENT, `article_tag` int(11) DEFAULT NULL, PRIMARY KEY (`article_id`), KEY `tag_at` (`article_tag`), CONSTRAINT `tag_at` FOREIGN KEY (`article_tag`) REFERENCES `parent` (`tag_id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 |
一般来说，称被引用的表为父表，引用的表为子表。外键定义时ON DELETE和ON UPDATE表示在对父表进行DELETE或UPDATE操作时，对子表所做的操作，可定义的子表操作有：
- CASCADE（跟随外键改动）：表示当父表发送DELETE或UPDATE操作时，对相应的子表中的数据也进行DELETE和UPDATE操作。
- SET NULL（设空值）：表示当父表发送DELETE或UPDATE操作时，对相应的子表中的数据更新为NULL值，但子表对应的列必须允许为NULL。
- SET DEFAULT（设默认值）：表示当父表发送DELETE或UPDATE操作时，对相应的子表中的数据更新为默认值。
- NO ACTION（无动作，默认的）：表示当父表发送DELETE或UPDATE操作时，抛出错误，不允许这类操作发生。
- RESTRICT（限制外表中的外键改动）：表示当父表发送DELETE或UPDATE操作时，抛出错误，不允许这类操作发生。如果定义外键时没有指定ON DELETE或ON UPDATE，RESTRICT就是默认的外键设置。
在其他数据库中，如Oracle数据库中，有一种称为延时检查（deferred check）的外键约束，即检查在SQL语句运行完成后再进行。而目前MySQL数据库的外键约束都是即时检查，因此从上面的定义可以看出，在MySQL数据库中NO ACTION和RESTRICT的功能是相同的。
对于参照完整性约束，外键能起到一个非常好的作用。但是对于数据的导入操作时，外键往往导致在外键约束的检查上花费大量时间。因此MySQL数据库的外键是即时检查的，所以对导入的每一行都会进行外键检查。但是用户可以在导入过程中忽视外键的检查，如：
|
1 2 3 4 5 6 7 |
mysql> set foreign_key_checks=0; Query OK, 0 rows affected (0.00 sec) mysql> LOAD DATA .... mysql> set foreign_key_checks=1; Query OK, 0 rows affected (0.00 sec) |
另外，如果想查看整个MySQL实例创建了哪些外键约束，可以查看information_schema.referential_constraints表，如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> select * from information_schema.referential_constraints\G *************************** 1. row *************************** CONSTRAINT_CATALOG: def CONSTRAINT_SCHEMA: tempdb CONSTRAINT_NAME: fk__student__id UNIQUE_CONSTRAINT_CATALOG: def UNIQUE_CONSTRAINT_SCHEMA: tempdb UNIQUE_CONSTRAINT_NAME: PRIMARY MATCH_OPTION: NONE UPDATE_RULE: RESTRICT DELETE_RULE: RESTRICT TABLE_NAME: student REFERENCED_TABLE_NAME: teacher |
重点字段解释：
constraint_schema：约束（外键）所在的库名。
constraint_name：约束名（外键名）。
unique_constraint_schema：被引用约束所在库名。
unique_constraint_name：被引用约束名。
二、外键与锁
对于外键值的插入或更新，首先需要检查父表中的记录，既SELECT父表。但是对于父表的SELECT操作，不是使用一致性非锁定读的方式，因为这会发生数据不一致的问题，因此这时使用的是SELECT…LOCK IN SHARE MODE方式，即主动对父表加一个S锁。如果这时父表上已经这样加X锁，子表上的操作会被阻塞，如下：
先创建测试环境，如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
# 创建parent表; create table parent( tag_id int primary key auto_increment not null, tag_name varchar(20) ); # 创建child表; create table child( article_id int primary key auto_increment not null, article_tag int(11), CONSTRAINT tag_at FOREIGN KEY (article_tag) REFERENCES parent(tag_id) ); # 插入数据; insert into parent(tag_name) values('mysql'); insert into parent(tag_name) values('oracle'); insert into parent(tag_name) values('mariadb'); |
开始测试
|
1 2 3 4 5 6 7 |
# Session A mysql> begin mysql> delete from parent where tag_id = 3; # Session B mysql> begin mysql> insert into child(article_id,article_tag) values(1,3); #阻塞 |
第二列是外键，执行该语句时被阻塞。
在上述的例子中，两个会话中的事务都没有进行COMMIT或ROLLBACK操作，而会话B的操作会被阻塞。这是因为tag_id为3的父表在会话中已经加了一个X锁，而此时在会话B中用户又需要对父表中tag_id为3的行加一个S锁，这时INSERT的操作会被阻塞。设想如果访问父表时，使用的是一致性的非锁定读，这时Session B会读到父表有tag_id=3的记录，可以进行插入操作。但是如果会话A对事务提交了，则父表中就不存在tag_id为3的记录。数据在父、子表就会存在不一致的情况。若这时用户查询INNODB_LOCKS表，会看到如下结果：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
mysql> select * from information_schema.innodb_locks\G *************************** 1. row *************************** lock_id: 3359:35:3:4 lock_trx_id: 3359 lock_mode: S lock_type: RECORD lock_table: `test`.`parent` lock_index: PRIMARY lock_space: 35 lock_page: 3 lock_rec: 4 lock_data: 3 *************************** 2. row *************************** lock_id: 3358:35:3:4 lock_trx_id: 3358 lock_mode: X lock_type: RECORD lock_table: `test`.`parent` lock_index: PRIMARY lock_space: 35 lock_page: 3 lock_rec: 4 lock_data: 3 2 rows in set, 1 warning (0.00 sec) |
从锁结构可以看出，对于parent表加了两个锁，一个S锁和一个X锁。
三、平常需要使用外键吗？
数据库的诸多设计，帐号，权限，约束，触发器，都是为 C/S 结构设计的，是以 C 端不可信做为假设前提的。B/S 模式安全边界前移到 web 服务层，应用与数据库之间是可信的，应用自行完成这些功能更加灵活。所以能不用就不用。
1. 互联网行业应用不推荐使用外键：用户量大，并发度高，为此数据库服务器很容易成为性能瓶颈，尤其受IO能力限制，且不能轻易地水平扩展；若是把数据一致性的控制放到事务中，也即让应用服务器承担此部分的压力，而引用服务器一般都是可以做到轻松地水平的伸缩。
2. 数据库服务器的性能不是问题，所以不用过多考虑性能的问题；另外，使用外键可以降低开发成本，借助数据库产品自身的触发器可以实现表与关联表之间的数据一致性和更新；最后一点，使用外键的方式，还可以做到开发人员和数据库设计人员的分工，可以为程序员承担更多的工作量。
为何说外键有性能问题：
1. 数据库需要维护外键的内部管理；
2. 外键等于把数据的一致性事务实现全部交给数据库服务器完成；
3. 有了外键，当做一些涉及外键字段的增，删，更新操作之后，需要触发相关操作去检查，而不得不消耗资源；
4. 外键还会因为需要请求对其他表内部加锁而容易出现死锁情况；
