Shulou(Shulou.com)06/01 Report--

This article mainly explains "what are the problems related to database primary key". The explanation in this article is simple and clear, easy to learn and understand. Please follow the idea of Xiaobian and go deep into it slowly to study and learn "what are the problems related to database primary key" together.

1 Should every table have a self-increasing primary key?

Not necessarily self-increasing the primary key can speed up the insertion of rows, for the table space utilization advantage, fragmentation is not obvious.

However, for some content, such as queries based on uid are very frequent and concentrated, if you do not use self-increasing primary keys, but use uid+id as a composite primary key, the query efficiency will increase, but insertion and fragmentation will increase. But if the database storage type is ssd, then this problem does not exist.

Therefore, in most cases, it is correct for tables to have self-increasing primary keys.

2. Is the self-increasing primary key unique in business?

not necessarily

Under single table structure, yes.

In the case of multiple tables, it is not necessary to have certain strategies, such as setting different suffixes, the same interval, etc.

3. Can self-increasing primary keys involve business?

This is not recommended.

For example, a table can have a self-increasing primary key, and the table is unique. When querying and updating based on id, the operation can be simplified. However, generally speaking, when there is a relationship with the business and uniqueness is required, it should be maintained by the business itself, such as using formats or algorithms, hash generation, etc.

4. How to maintain the uniqueness of the primary key maintained by the business in the case of multiple tables?

Maintain self-increasing key interval segments, the server takes one segment at a time, optimistic lock update. This requires additional tables or policies to maintain this field.

Based on algorithm A, fixed time prefix, such as: yMMddHmmss + table number mod value + random number, reduces the possibility of collision by increasing the number of bits. A table field has a uniqueness constraint (but sometimes this constraint is unreliable). If a duplicate field value exception is thrown during insertion, the insertion is regenerated.

Based on algorithm B, fixed time prefix, such as: yMMddHmmss + fixed number of bits collision self-increment N+ random number. There is no need to increase the number of bits to reduce the likelihood of collisions. When insert throws a duplicate field value exception, N++, reinsert until there is no longer a conflict. After that, N is used as the infix, and N is cached in the server. After restarting, this infix is continued. If repeated exceptions occur, N++ performs the same operation again. The mod value of N does not need to be mentioned intentionally.

Based on infix management, that is, reporting infixes to the central server, we can understand that there are places caching the id relationship of the server, and dynamically allocate infixes.

Other methods, there are many, have not been used, do not repeat. Algorithm B, simple, less communication, and limited number of collisions. Algorithm A, there are an infinite number of collisions, although the percentage is very, very low. However, in high concurrency situations, algorithm B is more violent than algorithm A when initializing.

Interval segment and infix management introduce the concept of central node, which is more dependent, but relatively reliable, and more common in the industry.

Thank you for reading, the above is the content of "what are the problems related to database primary key", after learning this article, I believe that everyone has a deeper understanding of what are the problems related to database primary key, and the specific use needs to be verified by practice. Here is, Xiaobian will push more articles related to knowledge points for everyone, welcome to pay attention!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.