MySql InnoDB中的行锁定与外键约束和索引的应用？

Question

快速问题。

我和一个有25年数据库经验的朋友交谈时，他告诉我如果在数据库中使用外键约束，例如写入消息表时，将锁定父表（如用户表）上的相关行。

这是真的吗？

此外，他说将外键列应用索引可以克服此锁定，这是真的吗？

我很担心我的网站流量正在增长，我可以想象这可能成为一个问题！

谢谢！

- Stefan P

---

我不能对父表锁定发表任何意见，但我知道为外键列建立索引是个好主意。特别是因为你可能会在连接中使用这些键。这样做可以将查询性能提高数倍。 - Stephen

在集群服务器中，使用联接仍然需要小心谨慎（仅在必要时），否则可能会导致全表扫描，从而对性能造成严重影响。 - zanlok

Answer 1

我写了一篇关于这个问题的博客文章 - http://www.mysqlperformanceblog.com/2010/09/20/instrumentation-and-the-cost-of-foreign-keys/ - 演示与你的朋友描述的一样。父行会发生锁定！

现在来解释“索引”和“键”的区别：

• 键是高级关系属性，匹配对应。
• 索引是一种实现细节。

从MySQL手册可知，“ InnoDB 需要在外键和参考键上创建索引，以便外键检查可以快速运行且不需要扫描整个表。”也就是说，如果在添加外键时没有添加索引，则InnoDB将自动添加。

无论有多少索引，都不能防止我博客中描述的父行锁定问题。

- Morgan Tocker

---

@morgan-tocker，是否可以通过创建仅用于提供子表具有FK的主键的最新列表的表来减轻锁定问题？例如：表C有两个子表（A和E）。 我的想法是创建两个单列表（B和D），它们包含对C的外键（通过在C上触发器保持最新）。如果A / E外键到B / D而不是C，那么这是否会防止一个被锁定而另一个保持外键约束的值？ - danielcraigie

通常情况下，通过规范化可以减少锁定；因为锁的最小粒度是行级别。 - Morgan Tocker

Answer 2

请看http://www.mysqlperformanceblog.com/2006/12/12/innodb-locking-and-foreign-keys/

- The Scrum Meister

---

谢谢，我之前看过这个。它让我更好地理解了，但我不认为它直接回答了问题，因为我的朋友是一个数据库专家，所以我想听听他的个人意见，以及是否有解决方案？ - Stefan P