谈谈我对MySQL外键字段必须索引的一些理解

最新推荐文章于 2024-10-12 12:22:25 发布

蛙鳜鸡鹳狸猿

最新推荐文章于 2024-10-12 12:22:25 发布

阅读量1.2w

点赞数 6

分类专栏： # MySQL / MariaDB 文章标签： MySQL 外键索引父表子表

本文链接：https://blog.csdn.net/sweeper_freedoman/article/details/61426736

版权

MySQL / MariaDB 专栏收录该内容

87 篇文章 2 订阅

订阅专栏

前两天被朋友问到一个有关MySQL外键的问题，一下子触及到了知识的盲点，当时有些懵圈。支持事务的InnoDB成为MySQL的默认存储引擎后，在高并发的系统中，很少见到MySQL表使用外键（我参与的几个项目数据库中都没有使用外键）。当时只能边猜想边查资料进行一些解释，不过，这个问题最终并没有完整地解决，所以在这里做一个记录，谈谈我自己的理解。如果有人无意中看到并且有其他见解，可以共同探讨研究。

首先是朋友遇到的问题，如果（父表）字段没有索引，创建外键会失败。于是，就引出一个MySQL创建外键关系的重要条件：

父表关联字段必须显式创建索引（单字段索引或位于最左位置的组合索引）；子表外键字段也是索引字段（如果不是索引字段建立外键关系的时候MySQL会隐式地为该字段创建一个普通索引）。即外键相关字段最后一定会是索引字段。这里对此过程和结论再做一个简单验证。

①首先创建一张示意父表和一张示意子表，其中父表没有索引（甚至没有主键）。

mysql> CREATE TABLE `tab_father` (
    -> `pk_left` INT(11) UNSIGNED NOT NULL,
    -> `pk_right` INT(11) UNSIGNED NOT NULL,
    -> `ts` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
    -> )
    -> COLLATE='utf8_general_ci'
    -> ENGINE=InnoDB
    -> ;
Query OK, 0 rows affected (0.12 sec)

mysql> CREATE TABLE `tab_child` (
    -> `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    -> `col` INT(10) UNSIGNED NOT NULL,
    -> PRIMARY KEY (`id`)
    -> )
    -> COLLATE='utf8_general_ci'
    -> ENGINE=InnoDB
    -> ;
Query OK, 0 rows affected (1.09 sec)

②子表`col`字段关联父表`pk_right`字段 → 失败。

mysql> ALTER TABLE `tab_child`
    -> ADD CONSTRAINT `FK_tab_child_tab_father` FOREIGN KEY (`col`) REFERENCES `tab_father` (`pk_right`) ON UPDATE CASCADE ON DELETE CASCADE;
ERROR 1215 (HY000): Cannot add foreign key constraint

③为父表`pk_right`字段显式创建索引再创建外键 → 成功。

mysql> ALTER TABLE `tab_father`
    -> ADD INDEX `pk_right` (`pk_right`);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
mysql> ALTER TABLE `tab_child`
    -> ADD CONSTRAINT `FK_tab_child_tab_father` FOREIGN KEY (`col`) REFERENCES `tab_father` (`pk_right`) ON UPDATE CASCADE ON DELETE CASCADE;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

此时再翻过来看一下，子表的`col`字段也已经是索引字段了（MySQL隐式创建）。

mysql> SELECT c.TABLE_NAME, c.COLUMN_NAME, c.COLUMN_KEY
    -> FROM information_schema.`COLUMNS` c
    -> WHERE c.TABLE_NAME = 'tab_child'
    -> AND c.COLUMN_NAME = 'col';
+------------+-------------+------------+
| TABLE_NAME | COLUMN_NAME | COLUMN_KEY |
+------------+-------------+------------+
| tab_child  | col         | MUL        |
+------------+-------------+------------+
1 row in set (0.01 sec)

④初始化父表和子表，为父表创建联合主键；为子表创建一个唯一索引。

mysql> ALTER TABLE `tab_child`
    -> DROP FOREIGN KEY `FK_tab_child_tab_father`;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE `tab_child`
    -> DROP INDEX `FK_tab_child_tab_father`;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE `tab_child`
    -> ADD UNIQUE INDEX `col` (`col`);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE `tab_father`
    -> DROP INDEX `pk_right`;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE `tab_father`
    -> ADD PRIMARY KEY (`pk_left`, `pk_right`);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

⑤关联父表联合主键第二字段创建外键 → 失败。

mysql> ALTER TABLE `tab_child`
    -> ADD CONSTRAINT `FK_tab_child_tab_father` FOREIGN KEY (`col`) REFERENCES `tab_father` (`pk_right`) ON UPDATE CASCADE ON DELETE CASCADE;
ERROR 1215 (HY000): Cannot add foreign key constraint

⑥关联父表联合主键第一字段创建外键 → 成功。

mysql> ALTER TABLE `tab_child`
    -> ADD CONSTRAINT `FK_tab_child_tab_father` FOREIGN KEY (`col`) REFERENCES `tab_father` (`pk_left`) ON UPDATE CASCADE ON DELETE CASCADE;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

如此，外键字段为什么需要索引？主要原因就在于查询性能了。父子表之间在进行外键检查时，需要一条一条每条必查地校验，而且在对父表的SELECT操作时，为了避免产出数据不一致，使用的是一致性锁定读（SELECT …… LOCK IN SHARE MODE），主动加一个阻塞其他修改操作的读锁。如果没有索引，对于大一点的表而言那效率就非常低下了。参考官方文档https://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html，有一段具体的说明。

MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as thefirst columns in the same order. Such an index is created on the referencing table automatically if it does not exist. This index might be silently dropped later, if you create another index that can be used to enforce the foreign key constraint.index_name, if given, is used as described previously.

那么，就有另外一个问题了，为什么子表可以隐式地创建索引而父表需要显式地创建？这也就是博文开篇所言的没有完整解决的问题。按照个人理解可能存在以下方面的考虑吧。

①同样是性能原因考虑。

这里就涉及到MySQL创建外键的执行过程。按照上文的测试用例，可能会走这么一个流程（伪过程：具体先后顺序可能会有所出入）。

A.检查父表关联字段有没有创建可使用的有效索引（if not raise an err）；

B.检查子表父表字段属性的一致性，包括数据类型、长度、字符编码等（if not raise an err）；

C.检查子表外键字段是否创建索引（if not create it and continue）；

D.（如果子表有数据则进行）检查子表数据是否关联父表记录（if not raise an err）。

由此可见，创建外键关联一句简单的SQL，MySQL要做包括潜在的DDL、DQL在内的很多工作。从MySQL5.6开始，即便引进了Online DDL，以使得一些包括创建辅助索引（secondary index）在内的表结构修改的性能大大提高，但如果父子表字段都没有创建索引，而同时为父子表创建索引依然是非常低效的。

②安全性考虑。

这一点和第①点是一致的，即安全和性能永远都是数据库系统的命脉。任何一句对数据库进行实际修改操作（DDL，DML以及DCL）的SQL功能，如果它的逻辑以及执行过程比较复杂，那就应该简化或者甚至干掉这种需求以通过非SQL实现。

③父表操作权限限制。

对于权限控制级别比较高的项目，并不是每个user都有每张table的所有privilege。当对子表创建外键时，也就暗示该用户已经拥有子表创建索引的权限。所以，MySQL可以静默地帮助顺便创建一个索引。与此同时，父表有没有修改表结构的权限就不得而知了。MySQL为了避免多一步权限检查的麻烦，就干脆放弃对父表也隐式创建索引的包办。

④外键是建在子表上的工作逻辑。

没错，从逻辑上来说外键是建在子表上的，和父表字段仅仅是关联关系（In MySQL 5.7, creation of a foreign key constraint requires theREFERENCES privilege for the parent table）。因此，对子表包办隐式索引创建就显得合情合理；同时，父表的处理适宜的抛出一个err交给用户自己处理也符合工程设计。

⑤MySQL源码维护者设定如此。

开源的MySQL社区和现Oracle公司的MySQL开发团队经过考虑后，可能认为这是一种最理想的处理方式。

以此可见，对于外键功能的支持，MySQL是花了很大心力来实现的，并且其支持也仅限于InnoDB和Cluster NDB（7.3版本开始）存储引擎。在保证了外键关联字段都是索引字段（隐式+显式）以后，MySQL还额外地将有外键关联关系的父子表排除出InnoDB buffer pool的LRU管理。即：有外键约束关系的父子表不计入table definition cache，不用担心被挤出内存池。