The unique identifier (ID) of the database.
Background: In years of work, we have designed many system or product databases, including single-host databases, lan databases, and Internet databases. For different environments, the design considerations are different. Even in the same environment, there will be different designs for different services or data volumes. Recently, we have to design an Internet product database (MySQL service ). After the accumulation, a lot of analysis, comparison, and learning are carried out in the Table ID design stage, and the design of ID is more systematic and profound, summarize and share your learned and practical knowledge with you.
Primary Key id Selection
For relational databases, the primary key of each table is determined in the first step. The primary key is ID. In "Common Sense", int-Type Auto-incremental id, string-type uuid, and other unique business-related keys... They are all our primary key options. Does it mean that in a table, as long as the attribute columns with unique values can be used as the primary key or a more suitable primary key?
First, let's clarify several concepts:
Logical primary key (proxy primary key): In the database table, a field irrelevant to the business logic information in the current table is used as its primary key, or a "pseudo primary key ";
Business primary key (natural primary key): fields with business logic meanings are used as primary keys in database tables;
A common example is a user information table with attributes such as id, user name, and mobile phone number ..., The username and mobile phone number are unique as logon accounts ). The id can be used as the logical primary key, and the user name and mobile phone number can both be used as the business primary key. Can I select either of them, or even do I have selected a business primary key without a logical primary key?
First, let's take a look at the strong disagreement between the logical primary key and the Business primary key:
Therefore, the selection of the logical primary key and the Business primary key is not the result of making decisions based on different application scenarios and requirements.
What problems do we face if we use auto-increment IDs of the integer type as the primary key?
For tables with a large amount of data, the demand for horizontal table sharding is often involved in the later stage. In this case, this auto-incrementing primary key will become a hindrance. (In fact, there will also be a solution for this situation. Please refer to the article "database sharding design in youpai network architecture".
ID data type selection
Let's consider the choice of primary keys from another perspective: data type.
Integer type:
The integer type is often the best choice for the id column, because the efficiency is the highest and the auto-incrementing primary key of the database can be used.
String type
The string type consumes more space than the integer type, and is slower than the integer type. I mainly use Mysql. For more information about this topic, see P125, the third edition of High Performance MySQL.
My solution (MySQL): using auto-incremental id as the primary key to cope with insertion efficiency issues; using uuid as the logical id has many advantages of the logical primary key, it can also be used to cope with subsequent horizontal table shards.