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

This article mainly introduces the use of the primary key of mysql, has a certain reference value, friends in need can refer to. I hope you will learn a lot after reading this article. Next, let the editor take you to learn about it.

(for InnoDB engine) our actual production environment may use four types of attributes as primary keys:

(1)。 Self-increasing sequence

(2)。 Random values generated by the UUID () function

(3)。 The unique account name and string type registered by the user, and the general length is: 40 characters

(4)。 Generate similar self-incrementing values based on a set of mechanisms, such as sequence generators

So next, let's analyze the advantages and disadvantages of each of these four types of attributes as table primary keys:

(1) self-increasing sequence: new values are added in sequential patterns from small to large or from large to small; data types are also conducive to the comparison of primary key values; storage space is also relatively minimum, generally set to 4-byte INT type or 8-byte BIGINT type; if you want to split data horizontally, you can also set two parameters of mysqld instance: auto_increment_increment and auto_increment_offset In addition, the only disadvantage is that the self-increment sequence is a table-level global lock. When writing concurrently on a large scale in Series 5.0, bottlenecks are easy to occur because of the lock release mechanism, but Series 5.1 has been improved and basically does not have this problem.

(2) UUID () function: the value is random + fixed part, and the value generation is unordered, and the same part of the value generated on the same server is 77.8%. The number of generated value characters is 36, which takes up 36 bytes of storage space calculated according to utf8 coding. For horizontal data split support, no special setting is required.

(3) use the account name and string type registered by the user, and the generation of its value depends on the user's input, so the data is basically increased out of order, and the length of the string is also indefinite. The limit of the minimum and maximum length can only be controlled by the previous technology. Horizontal split support, no need to make special settings

(4) the architecture of sequence generator is similar to self-increasing sequence, but with the help of additional development effort and providing a third-party service, it can avoid the problem of word increasing global lock of self-increasing sequence and improve concurrency. Horizontal split of data can better support

(5) the probabilistic scenario of dual-master replication architecture: when the data of the primary server is executed successfully, but not replicated to the online standby server, the probability of problems does exist, and other types of practices must also be solved by manual intervention. There is no simple and reasonable automation method, and none of the above four methods can be avoided.

Through the analysis of the advantages and disadvantages of the four attribute values as the primary key, as well as comparing the excellent qualities needed by the primary key described above, if we do not consider the problem of horizontal split, which brings additional trouble in setting, then the self-increasing sequence is the best primary key field choice; when the user's registered account itself requires uniqueness and is not empty, it can be used as the primary key field choice. If the problem of horizontal splitting is considered, the architecture of self-increasing sequence generator is adopted, which is very easy to use and reliable, and the resulting value is the choice of the best primary key field.

Thank you for reading this article carefully. I hope the editor will share the main key of mysql and what is useful to everyone. At the same time, I also hope that you will support us, pay attention to the industry information channel, and find out if you encounter problems. Detailed solutions are waiting for you to learn!

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.