Simple principle
There is a field that can be used as a keyword, you should use multiple fields as much as possible.
The primary key field should try to use a numerical or length of character type, which is not appropriate to take a character type or length.
If you want to make a master keyword using multiple fields, it will add data processing in software development, such as query modification or delete, there are more than two, especially in modifying or deleting tables. The software will increase the bug.
Under normal circumstances, the occupancy space of multiple fields is definitely larger than a single field, including CPU processing and disk I / O operations when the primary critical query, will increase the load of the server, is particularly obvious during the amount of data above millions.
There is a test like this
A table 1 million record, three fields, a (numeric), B (character type 10), C date type
Retrieve 1000 line records
Conditions with a field, the maximum time is 1.344 seconds, the minimum time is 1.156 seconds
Conditions with B field, the maximum time is 1.391 second, the minimum time is 1.172 seconds
Conditions with C field, the maximum time is 1.843 seconds, the minimum time is 1.532 seconds
Dated data to do keywords will not have much impact on performance, but the date-type main keyword will bring great inconvenience in software development, such as:
You cannot directly character query on the date, you need to convert the character to date data and then query. Date data can save time can also be time, if a field does not require a time, it is also necessary to process it in time data. Because there is a time data, it may generate incorrect results for the query.
In addition, the date-based time is a primary keyword is an ideal inference. As the amount of business data increases the expansion of business functions, the operator increases challenge this keyword, and the time will be due to the time of the database server. Impact, it is likely that the server time is wrong or causes database data disorders when modified with the actual time.
Persistence principle
A primary key should be lasting, specifically that the primary keyword can determine the least atom of business needs (foreseeable or possible) of business needs (foreseeable or possible) in the future, if due to future business function expansion If the primary keyword fails or to force the data content to be maintained, it will bring very serious consequences. The most prone to such erroneous tables is the primary keyword with multiple fields and is quite close to the business, only considered the current analysis of the business situation, and think that these fields are sufficient.
Can serve as other outbursted principles
The primary key should be a field that is often queried, and the current table is associated with other tables.
The fields of frequent queries are analyzed by database performance, and the index generated by the primary key is the fastest index type, so it should be as soon as possible to use the primary keyword index when query.
Meaningful principle
In the actual simple business form, the primary key should be as practical, while the main keyword in the system's basic core table and large data larger business table should be considered in terms of speed performance. The main keyword is mainly for software development and database query debugging, business is more likely to understand, and the database is clearer with the actual business.
