The primary key in mysql is divided into: 1. Auto-increment sequence; 2. Random value generated by UUID function; 3. Unique account name registered by the user; 4. Generate similar auto-increment value based on a set of mechanisms , such as sequence generator.
(For InnoDB engine) Our actual production environment may use four types of attributes as primary keys:
(1). Auto-increment sequence;
(2). Random value generated by UUID() function;
(3). Unique account name registered by the user, string type, generally length: 40 characters;
(4). Generate values similar to auto-increment based on a set of mechanisms, such as sequence generators;
Then let’s analyze the advantages and disadvantages of each of these four types of attributes as table primary keys. :
(1)Auto-increment sequence: Add new values in the sequential pattern from small to large or from large to small; the data type is also conducive to primary key value comparison; the storage space is also relatively small Minimum, generally set to: 4-byte INT type or 8-byte BIGINT type; if you want to split the data horizontally, you can also set two parameters of the mysqld instance: auto_increment_increment and auto_increment_offset; in addition, the only disadvantage That is, the auto-increment sequence is a table-level global lock. When large-scale concurrent writing occurs in the 5.0 series, bottlenecks are prone to occur due to lock release mechanism issues. However, the 5.1 series has been improved and this problem basically does not exist;
(2)UUID() function: The value is a fixed part of randomness, the value generation is unordered, and the same part of the value generated on the same server is 77.8%; the generated value characters The number is 36, calculated according to utf8 encoding, and the storage space occupied is 36 bytes; for data horizontal split support, no special settings are required;
(3)Use the account name registered by the user, characters String type , the generation of its value depends on user input. Therefore, the data is basically added in an unordered manner, and the length of the string is also uncertain. The restriction of the shortest and maximum length value can only be controlled through the previous technology, and horizontal splitting is supported. , no special settings are required;
(4)The architecture of the sequence generator is similar to the auto-increment sequence, but requires additional development workload and the provision of a third-party service. It avoids the problem of word-increasing global locks in auto-increment sequences, improves concurrency, and can better support horizontal splitting of data;
(5) Probabilistic scenarios encountered by dual-master replication architecture: the master server When the data is successfully executed but not copied to the online backup server, the probability of problems does exist. Other types of methods must also be solved by manual intervention. There is no simple and reasonable automated method. The above four methods cannot be avoided;
Through the analysis of the advantages and disadvantages of the four attribute values as the primary key, and comparing the excellent qualities required for the primary key as we explained earlier, if the problem of horizontal splitting is not considered, which will cause additional setup trouble, the auto-increment sequence is The best choice of primary key field; in the scenario where the user's registered account itself requires uniqueness and non-empty, it can be used as the choice of primary key field; if the issue of horizontal splitting is considered, the architecture of the auto-increment sequence generator is used, which is very easy Useful and reliable implementation, the value generated is the best choice of primary key field;
The above is the detailed content of What are the primary keys in mysql?. For more information, please follow other related articles on the PHP Chinese website!