mysql - 数据库表之间设置外键是否可以提高查询效率？

Answer 1

Foreign keys are constraints. Since they are constraints, they will definitely add additional overhead. For example, in the common examples in the book, the student course selection system, student table and course schedule, there will definitely be a student course association table in the middle. If you add a foreign key constraint, when you delete a course, you will definitely first check whether a student has chosen the course. Otherwise, the course chosen by the student after you delete it will not be found and an error will occur.

I don’t understand why there is an anti-pattern. Omitting foreign key constraints can make the database design simpler and more flexible, or execute more efficiently, but you still have to pay the corresponding price in other aspects. You must add additional code to manually maintain referential integrity. . Originally, a constraint key in the database could solve the problem, but now we have to write a piece of code to maintain it. Moreover, manually writing code to maintain judgment is also a constraint, and there will also be performance losses, but it is just at the logical level, and it will not be much faster than the database.

Answer 2

Foreign keys must use innodb, which is slower, but myisam is faster. Foreign keys can be solved using related queries

Answer 3

Foreign keys and things like paradigms should only exist in textbooks. Banning foreign keys and denormalization is what should be done. There is no connection between foreign keys and query efficiency. The function is only to establish data connections, so why not restrict data connections through logic.

Answer 4

Some companies create tables without adding foreign keys

Answer 5

I’ve never added a foreign key since I started working :D Having too many dependencies would be troublesome, but it shouldn’t affect performance. I’ve never seen any documentation about adding foreign keys to improve efficiency. I'm a rookie, don't criticize me, haha, I can't afford to get hurt. Passing by, passing by

Answer 6

External Jian is mainly to maintain the integrity and consistency of data. For example, if there is no external key relationship between the user table and the user order table, you can insert it into the order table. But this order form belongs to that user, how do you know. Then such data becomes a lonely ghost. There is a relationship between external health. When inserting, you must require relevant users in the user table to insert. In the same way, when you delete the data in the user table, if there is a reference to the order table, you cannot delete it. This ensures data consistency and integrity.

Answer 7

Foreign key constraints are mainly to ensure data consistency at the database level and are not helpful for performance improvement. Because inserting and updating data requires checking foreign keys, theoretical performance will decrease. In oralce, foreign keys will increase in the master-slave table. Competition for main table locks has a negative impact on performance.

In actual projects, it is not recommended to use foreign keys. On the one hand, it reduces the complexity of development (if there are foreign keys, the operation of the master-slave table must first operate on the master table). On the other hand, foreign keys are very troublesome when processing data.

It is a normal functional requirement to check the consistency of data at the application level. For example, in the scenario where students choose courses, the courses must not be entered, but selected from the system through drop-down or search methods, which can ensure that they are legal. Course ID, so there is no need to rely on foreign keys in the database to check.