关于主键和外键那些事

墨蓝 2023-02-27 12:28 21阅读 0赞

# 问题一 主外键和索引的区别 #

引用别人的一篇博客，写的可以。  
[https://blog.csdn.net/weirdowang/article/details/83181346?utm\_medium=distribute.pc\_relevant\_t0.none-task-blog-BlogCommendFromMachineLearnPai2-1.nonecase&depth\_1-utm\_source=distribute.pc\_relevant\_t0.none-task-blog-BlogCommendFromMachineLearnPai2-1.nonecase][https_blog.csdn.net_weirdowang_article_details_83181346_utm_medium_distribute.pc_relevant_t0.none-task-blog-BlogCommendFromMachineLearnPai2-1.nonecase_depth_1-utm_source_distribute.pc_relevant_t0.none-task-blog-BlogCommendFromMachineLearnPai2-1.nonecase]

# 问题二 关于，在Mysql中，外键是否会影响性能的问题？？？ #

**引用StackOverflow上的一段话**

> In MySQL, a foreign key requires an index. If an index already exists, the foreign key will use that index (even using the prefix of an existing multi-column index). If no index exists, defining the foreign key will build the index.

> So the size increase and time to create a foreign key is about the same as to create an index on the same column(s).

> The performance of a SELECT is not impacted significantly by the presence of a foreign key. Only a slight additional work for the optimizer, to consider the new indexes.

> The performance of updating is more, because for each foreign key, an INSERT/UPDATE/DELETE has to check to see if the constraint is satisfied. That means a primary key lookup to the referenced tables. This impact is measurable, and it is greater if the referenced tables are not in the buffer pool.

> Another impact is the locking. If I update a row in a child row that has a foreign key, InnoDB places a shared-lock on the referenced rows in the parent tables. That means no one can update those parent rows until I commit. If you have lots of threads updating child rows, then the parent rows may be locked most of the time, and this can make it hard to do concurrent work in the parent tables. Not so much a performance problem, but a concurrency problem.

> As with all “how does that perform” questions, the answer really depends on your workload. If you don’t have concurrent updates, for example, that issue may not effect you for all practical purposes. Testing it yourself with load testing is the only way to be sure. It’s not something anyone on StackOverflow can answer precisely for you.

原文地址：[ http://dba.stackexchange.com/questions/77906/what-is-the-cost-of-adding-a-foreign-key-in-mysql][http_dba.stackexchange.com_questions_77906_what-is-the-cost-of-adding-a-foreign-key-in-mysql]

**大概意思就是**

1.外键就想其他类型的索引一样，要说性能方面的影响，它影响的主要是写入操作（如，UPDATE/INSERT/DELETE）；

2.但它与单表索引不同的是，它会引用一张或多张父表，这样当对子表进行写入操作（UPDATE/INSERT）的时候，父表就会被加上“共享锁”，这样在对子表高并发进行写入操作的情况下，对父表的写入操作就会由于“共享锁”的存在，而会长时间不能得到更新！当然查询是可以的。

3.作者给出了一个观点：外键主要造成的问题，并不是影响性能多少的问题，而是一个并发访问的问题！

4.所以是否使用外键，关键是要看你对并发要求的高低了！

# 关于外键的父子表 #

A表必须有记录，B表才能有，这个时候A表是主键表，B表外键表。（A表里面的记录可以随便录入，但一般必须唯一，是主键；B表里面录入的东西就不是乱录了，必须A表里面有的记录，才能录入到B表中。B表里面出现的任何一条记录都必须在A表中找到与之对应的记录，当然它可以重复出现，如果是1：N的话）

例如：学生表，成绩表。学生表里面必须有某个学生的信息，成绩表里面才可能有这个学生的成绩。这个时候学生表示主键表，成绩表示外键表。如果学生表中一个学生都没有，成绩表里面也不能有，但是反之则可以。  
一句话，主键表限制外键表的数据。谁限制谁主键，谁被限制谁外键。

[https_blog.csdn.net_weirdowang_article_details_83181346_utm_medium_distribute.pc_relevant_t0.none-task-blog-BlogCommendFromMachineLearnPai2-1.nonecase_depth_1-utm_source_distribute.pc_relevant_t0.none-task-blog-BlogCommendFromMachineLearnPai2-1.nonecase]: https://blog.csdn.net/weirdowang/article/details/83181346?utm_medium=distribute.pc_relevant_t0.none-task-blog-BlogCommendFromMachineLearnPai2-1.nonecase&depth_1-utm_source=distribute.pc_relevant_t0.none-task-blog-BlogCommendFromMachineLearnPai2-1.nonecase
[http_dba.stackexchange.com_questions_77906_what-is-the-cost-of-adding-a-foreign-key-in-mysql]: http://dba.stackexchange.com/questions/77906/what-is-the-cost-of-adding-a-foreign-key-in-mysql