Whether foreign keys are used to look at business scenarios, as well as development costs, are roughly listed at what time and when not suitable for use:
1. Internet industry applications do not recommend the use of foreign keys: large user volume, high concurrency, this database server can easily become a performance bottleneck, especially by the IO capability limit, and can not easily expand horizontally; If you put the control of data consistency into a transaction, you let the application server bear this part of the pressure, and the reference server is generally easy to do the horizontal scaling;
2. Traditional Industries
1>. The number of software applications is limited, in other words, controllable;
2> The data volume of the database server is generally not very large, and the active data is limited;
Combined with the above 2 sentence description, that is, the performance of the database server is not a problem, so do not worry about the performance of the problem, in addition, the use of foreign keys can reduce development costs, with the help of the database product itself trigger can achieve the data consistency and update between the table and the associated table, and finally, the use of foreign keys, can also be done by the Developer and Database Designer Division of labor, you can take more workload for programmers;
Why foreign keys have performance issues:
1. The database needs to maintain the internal management of foreign keys;
2. Foreign key is equal to the data consistency transaction implementation, all to the database server to complete;
3. With the foreign key, as some of the foreign key fields involved in the increase, delete, update operation, the need to trigger the relevant operation to check, and have to consume resources;
4. The foreign key will also be prone to deadlock situations because of the need to request a lock inside other tables, the size of the large amount of data is not suitable for foreign keys, small can be used, the use of security, reliability is important to use the foreign key, otherwise it can not be used. Specific situation specifically solved, because there are contradictions, the amount of data, but also requires high reliability, such as bank finance, chip production, etc., still need the presence of foreign keys. Can solve the contradiction through hardware promotion such as San+raid.
Requires high concurrency, not suitable for foreign keys, and some even relational databases are not used, and even the database is not.
Use of database foreign keys
