MySql InnoDb 中的行锁定是否应用了外键约束和索引？
快问。
与我有 25 年数据库经验的朋友交谈时，他告诉我是否在数据库中使用外键约束；例如，当将表写入消息表时，它将锁定父表上的相关行（例如用户）。
这是真的吗？
他还说，将索引应用于外键列应该可以克服这种锁定，这是真的吗？
我很担心我的网站流量正在增长，我可以想象这是一个问题！
谢谢！
Quick question.
Talking to my friend with 25 years db experience he was telling me if you use foreign key constraints in a db; when a table is being written to for example, a table for messages, it will lock out the relative row on the parent table for say, users.
Is this true?
Also he said that applying indexes to the Foreign Key Columns should overcome this locking out, is this true?
I am concerned as my website traffic is growing and I can imagine this being an issue!
Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我就此写了一篇博客文章 - http://www .mysqlperformanceblog.com/2010/09/20/instrumentation-and-the-cost-of-foreign-keys/ - 该演示正如您的朋友所描述的那样。父行上有锁定！
现在解释“索引”和“键”之间的区别：
来自 MySQL 手册，“InnoDB 需要外键索引和引用的键，以便外键检查可以快速并且不需要表扫描。”。也就是说，如果你在添加外键时没有添加索引，InnoDB会自动添加。
无论多少索引都无法阻止我的博客文章中描述的父级锁定。
I wrote a blog post on this - http://www.mysqlperformanceblog.com/2010/09/20/instrumentation-and-the-cost-of-foreign-keys/ - the demo is just as your friend described. There is locking on parent rows!
Now for the part about explaining the difference between an "index" and a "key":
From the MySQL manual, "InnoDB requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan.". That is, if you don't add an index when you add your foreign key, InnoDB will automatically.
No amount of indexing prevents the parent locking described in my blog post.
看看http://www.mysqlperformanceblog。 com/2006/12/12/innodb-locking-and-foreign-keys/
have a look at http://www.mysqlperformanceblog.com/2006/12/12/innodb-locking-and-foreign-keys/