讯享网

 <p class="f_center"><img src="https://nimg.ws.126.net/?url=http%3A%2F%2Fdingyue.ws.126.net%2F2020%2F0527%2F7759ffdbp00qayzt9002fc000zk00f4m.png&thumbnail=660x&quality=80&type=jpg"/><br/></p><p>外键的<strong>设计初衷</strong>是为了在数据库端保证对逻辑上相关联的表数据在操作上的一致性与完整性。</p><p>外键在大部分企业写的开发规范里会<strong>直接规避掉！</strong>外键有优缺点，也并不是说每种场景都不适用，完全没有必要一刀切。外键到底能不能用？下面会针对不同的场景来告诉你答案。</p><p><h5>一、外键的优缺点</h5></p><p><strong>优点：</strong></p><p><ul><li></p><p>精简关联数据，减少数据冗余</p><p>避免后期对大量冗余处理的额外运维操作。</p><p></li><li></p><p>降低应用代码复杂性，减少了额外的异常处理</p><p>相关数据管理全由数据库端处理。</p><p></li><li></p><p>增加文档的可读性</p><p>特别是在表设计开始，绘制 ER 图的时候，逻辑简单明了，可读性非常强。</p><p></li></ul></p><p><strong>缺点：</strong></p><p><ul><li></p><p>性能压力</p><p>外键一般会存在级联功能，级联更新，级联删除等等。在海量数据场景，造成很大的性能压力。比如插入一条新记录，如果插入记录的表有 10 个外键，那势必要对关联的 10 张表逐一检查插入的记录是否合理，延误了正常插入的记录时间。并且父表的更新会连带子表加上相关的锁。</p><p></li><li></p><p>其他功能的灵活性不佳</p><p>比如，表结构的更新等。</p><p></li></ul></p><p><h5>二、外键的使用</h5></p><p><strong>外键参照动作列表：</strong><br/></p><p><ul><li></p><p>CASCADE：级联，子表跟随父表更新外键值</p><p></li><li></p><p>SET NULL：子表更随主表更新外键值为 NULL</p><p></li><li></p><p>RESTRICT/ NO ACTION：<strong>默认</strong>，限制父表改动外键值</p><p></li><li></p><p>SET DEFAULT：目前产生的效果和 RESTRICT 相同。</p><p></li></ul></p><p>那先来简单看看 MySQL 里外键的用法。MySQL 外键仅有 InnoDB 和 NDB 两种引擎支持，这里只关注 InnoDB。</p><p><blockquote>本次示例 MySQL 的版本为最新版 8.0.19</blockquote></p><p><strong>示例</strong><br/>下面 f1 是父表，f2、f3、f6 分别代表不同类型的外键表，也就是子表。</p><p><ol><li></p><p>1. </p><p></li><li></p><p>2. </p><p></li><li></p><p>3. </p><p></li><li></p><p>4. </p><p></li><li></p><p>5.<br/></p><p></li><li></p><p>6. </p><p></li><li></p><p>7. </p><p></li><li></p><p>8. </p><p></li><li></p><p>9. </p><p></li><li></p><p>10.<br/></p><p></li><li></p><p>11.<br/></p><p></li><li></p><p>12. </p><p></li><li></p><p>13. </p><p></li><li></p><p>14. </p><p></li><li></p><p>15. </p><p></li><li></p><p>16.<br/></p><p></li><li></p><p>17. </p><p></li><li></p><p>18. </p><p></li><li></p><p>19. </p><p></li><li></p><p>20. </p><p></li></ol></p><p><strong>场景一：强烈要求数据一致性，程序弱化，数据库端强化，表结构改动小，并发不高的场景。</strong></p><p>用一条记录验证表 f2 和 f6。从功能性角度来看，外键的优势很明显，在数据库端完全满足了数据完整性校验。</p><p><ol><li></p><p>1. </p><p></li><li></p><p>2. </p><p></li><li></p><p>3.<br/></p><p></li><li></p><p>4. </p><p></li><li></p><p>5. </p><p></li><li></p><p>6.<br/></p><p></li><li></p><p>7. </p><p></li><li></p><p>8. </p><p></li><li></p><p>9.<br/></p><p></li><li></p><p>10. </p><p></li><li></p><p>11. </p><p></li><li></p><p>12. </p><p></li><li></p><p>13. </p><p></li><li></p><p>14.<br/></p><p></li><li></p><p>15. </p><p></li><li></p><p>16. </p><p></li><li></p><p>17. </p><p></li><li></p><p>18. </p><p></li><li></p><p>19. </p><p></li><li></p><p>20. </p><p></li><li></p><p>21. </p><p></li><li></p><p>22. </p><p></li><li></p><p>23.<br/></p><p></li><li></p><p>24. </p><p></li><li></p><p>25. </p><p></li><li></p><p>26. </p><p></li></ol></p><p><strong>场景二：频繁的数据装载，但是也严格要求数据库端保证数据一致性。</strong></p><p>这里只验证表 f6，同时克隆一张新表 f6_no_fk，除了没有外键，表结构和 f6 一样。导入 400W 条样例数据。</p><p><ol><li></p><p>1. </p><p></li><li></p><p>2. </p><p></li><li></p><p>3. </p><p></li><li></p><p>4. </p><p></li><li></p><p>5.<br/></p><p></li><li></p><p>6. </p><p></li><li></p><p>7. </p><p></li><li></p><p>8. </p><p></li><li></p><p>9. </p><p></li></ol></p><p>从上面看到，单独的测试导入 400W 条记录，带有外键的表比非外键的表时间上没有优势。那针对上面的场景优化下，关闭外键检查参数，导入完成后，再开启。</p><p><ol><li></p><p>1. </p><p></li><li></p><p>2. </p><p></li><li></p><p>3.<br/></p><p></li><li></p><p>4. </p><p></li><li></p><p>5. </p><p></li><li></p><p>6. </p><p></li><li></p><p>7.<br/></p><p></li><li></p><p>8. </p><p></li><li></p><p>9. </p><p></li><li></p><p>10. </p><p></li><li></p><p>11. </p><p></li><li></p><p>12.<br/></p><p></li><li></p><p>13. </p><p></li><li></p><p>14. </p><p></li><li></p><p>15. </p><p></li></ol></p><p>从以上结果看出，关闭外键检查后，导入时间和没有外键的表 f6_no_fk 差不多。</p><p><strong>场景三：并发少，事物块简单。</strong></p><p>接下来再看下简单的事物块提交方式，我简单写了一个每 500 条记录提交一次的存储过程。</p><p><ol><li></p><p>1. </p><p></li><li></p><p>2. </p><p></li><li></p><p>3. </p><p></li><li></p><p>4. </p><p></li><li></p><p>5. </p><p></li><li></p><p>6. </p><p></li><li></p><p>7.<br/></p><p></li><li></p><p>8. </p><p></li><li></p><p>9. </p><p></li><li></p><p>10. </p><p></li><li></p><p>11. </p><p></li><li></p><p>12. </p><p></li><li></p><p>13. </p><p></li><li></p><p>14. </p><p></li><li></p><p>15. </p><p></li><li></p><p>16. </p><p></li><li></p><p>17. </p><p></li><li></p><p>18. </p><p></li><li></p><p>19. </p><p></li><li></p><p>20. </p><p></li></ol></p><p>接下来插入 100W 条记录，</p><p><ol><li></p><p>1. </p><p></li><li></p><p>2. </p><p></li><li></p><p>3. </p><p></li><li></p><p>4.<br/></p><p></li><li></p><p>5. </p><p></li><li></p><p>6. </p><p></li><li></p><p>7. </p><p></li><li></p><p>8.<br/></p><p></li><li></p><p>9. </p><p></li><li></p><p>10. </p><p></li><li></p><p>11. </p><p></li><li></p><p>12.<br/></p><p></li><li></p><p>13. </p><p></li><li></p><p>14. </p><p></li><li></p><p>15. </p><p></li><li></p><p>16.<br/></p><p></li><li></p><p>17. </p><p></li><li></p><p>18. </p><p></li></ol></p><p>从测试的结果来看，有外键和没有外键的检索时间在这样的场景下也相差无几。</p><p><strong>场景四：主表的外键引用字段类型要扩充，原来的数据溢出，没法保存更大的值。</strong></p><p>比如此时字段 r2 定义的数据类型不合适了，需要更改为大点的，比如以下，直接修改会报错，</p><p><ol><li></p><p>1. </p><p></li><li></p><p>2. </p><p></li><li></p><p>3.<br/></p><p></li><li></p><p>4. </p><p></li><li></p><p>5. </p><p></li></ol></p><p>那怎么改呢？需要先把外键删掉，修改完了类型，再加上约束。这种场景就不太适合用外键。</p><p><ol><li></p><p>1. </p><p></li><li></p><p>2. </p><p></li><li></p><p>3. </p><p></li><li></p><p>4.<br/></p><p></li><li></p><p>5. </p><p></li><li></p><p>6. </p><p></li><li></p><p>7. </p><p></li><li></p><p>8.<br/></p><p></li><li></p><p>9. </p><p></li><li></p><p>10. </p><p></li><li></p><p>11. </p><p></li><li></p><p>12.<br/></p><p></li><li></p><p>13. </p><p></li><li></p><p>14. </p><p></li><li></p><p>15. </p><p></li></ol></p><p><strong>场景五：子表有触发器需求来更新必要的字段。</strong></p><p>那关于这点就是，子表的触发器不会随着父表的更新级联应用，也就是此时触发器失效。举个例子，往 f2 上添加一个 before update 触发器。</p><p><ol><li></p><p>1. </p><p></li><li></p><p>2. </p><p></li><li></p><p>3. </p><p></li><li></p><p>4.<br/></p><p></li><li></p><p>5. </p><p></li><li></p><p>6. </p><p></li><li></p><p>7.<br/></p><p></li><li></p><p>8. </p><p></li><li></p><p>9. </p><p></li><li></p><p>10. </p><p></li><li></p><p>11. </p><p></li><li></p><p>12. </p><p></li><li></p><p>13. </p><p></li><li></p><p>14. </p><p></li><li></p><p>15.<br/></p><p></li><li></p><p>16. </p><p></li><li></p><p>17.<br/></p><p></li><li></p><p>18. </p><p></li><li></p><p>19. </p><p></li><li></p><p>20. </p><p></li><li></p><p>21.<br/></p><p></li><li></p><p>22. </p><p></li><li></p><p>23. </p><p></li><li></p><p>24. </p><p></li><li></p><p>25. </p><p></li><li></p><p>26. </p><p></li><li></p><p>27. </p><p></li><li></p><p>28. </p><p></li><li></p><p>29. </p><p></li><li></p><p>30.<br/></p><p></li><li></p><p>31. </p><p></li><li></p><p>32. </p><p></li><li></p><p>33. </p><p></li><li></p><p>34. </p><p></li><li></p><p>35.<br/></p><p></li><li></p><p>36. </p><p></li><li></p><p>37. </p><p></li><li></p><p>38. </p><p></li><li></p><p>39. </p><p></li><li></p><p>40. </p><p></li><li></p><p>41. </p><p></li><li></p><p>42. </p><p></li><li></p><p>43. </p><p></li></ol></p><p><strong>场景六：父表为分区表，有外键的需求。</strong></p><p>那针对分区表，暂时不支持子表以分区表为父表的外键。</p><p><ol><li></p><p>1. </p><p></li><li></p><p>2. </p><p></li><li></p><p>3.<br/></p><p></li><li></p><p>4. </p><p></li><li></p><p>5. </p><p></li><li></p><p>6. </p><p></li><li></p><p>7.<br/></p><p></li><li></p><p>8. </p><p></li><li></p><p>9. </p><p></li><li></p><p>10. </p><p></li></ol></p><p><strong>场景七：日常并发很高的场景，应该尽量减少相关事务锁的范围和量级。</strong></p><p>那举个简单例子，看看有外键情况下，父表更新，子表级联加锁的情形。</p><p><ol><li></p><p>1. </p><p></li><li></p><p>2. </p><p></li><li></p><p>3. </p><p></li><li></p><p>4.<br/></p><p></li><li></p><p>5. </p><p></li><li></p><p>6. </p><p></li><li></p><p>7. </p><p></li><li></p><p>8.<br/></p><p></li><li></p><p>9.<br/></p><p></li><li></p><p>10. </p><p></li><li></p><p>11. </p><p></li><li></p><p>12. </p><p></li><li></p><p>13. </p><p></li><li></p><p>14. </p><p></li><li></p><p>15. </p><p></li><li></p><p>16. </p><p></li></ol></p><p>总共有 11 个锁，也就简单的执行了下 Update，而且更新的只是一行。</p><p><ol><li></p><p>1. </p><p></li><li></p><p>2. </p><p></li><li></p><p>3. </p><p></li><li></p><p>4. </p><p></li><li></p><p>5. </p><p></li><li></p><p>6. </p><p></li><li></p><p>7. </p><p></li><li></p><p>8. </p><p></li></ol></p><p>查看锁的细化，父有 f1 有 5 个锁，子表 f6 有 6 个锁。这都是 MySQL 为了保证数据一致性强制加的，这点在 TPS 要求比较高的场景肯定不合适，</p><p><ol><li></p><p>1. </p><p></li><li></p><p>2. </p><p></li><li></p><p>3. </p><p></li><li></p><p>4. </p><p></li><li></p><p>5. </p><p></li><li></p><p>6. </p><p></li><li></p><p>7. </p><p></li><li></p><p>8. </p><p></li><li></p><p>9. </p><p></li><li></p><p>10. </p><p></li><li></p><p>11. </p><p></li><li></p><p>12. </p><p></li><li></p><p>13. </p><p></li><li></p><p>14. </p><p></li><li></p><p>15. </p><p></li><li></p><p>16. </p><p></li><li></p><p>17. </p><p></li></ol></p><p><h5>三、外键的限制：</h5></p><p>1. 仅有 InnoDB 和 NDB 引擎支持。</p><p>2. 不支持虚拟列。</p><p>3. 不支持临时表。</p><p>4. 外键列以及引用列数据类型、字符集、校对规则都得一致。</p><p>5. 外键列以及引用列都必须建立索引。</p><p>6. 外键引用多个列的，列顺序必须一致。</p><p>7. 大对象字段不能作为引用列。</p><p>8. constraint 命名必须在单个 database 里唯一。</p><p>9. 外键级联更新操作不会触发子表上的触发器。</p><p>10. 不支持分区表。</p><p><h5>总结</h5></p><p>本文主要从几个例子来演示了，外键是否应该使用以及在哪些场景下使用，让大家了解外键的详细需求。</p><p>从上面我描述的几个场景来说，场景 1,2,3 很适合用外键；场景 4,5,6,7 就不太适合用外键；可以把外键功能放在数据库之外实现。</p><p><strong>关于 MySQL 的技术内容，你们还有什么想知道的吗？赶紧</strong><strong>留言告诉小编吧！</strong></p><p class="f_center"><img src="https://nimg.ws.126.net/?url=http%3A%2F%2Fdingyue.ws.126.net%2F2020%2F0527%2F6a6dced4p00qaz01q004tc000zk00m8m.png&thumbnail=660x&quality=80&type=jpg"/><br/></p>