MySQL distributed implementation ID self-increment

Due to the amount of data and the efficiency of the IO, many projects will take a sub-database table for the data-supported databases. One problem that needs to be solved after using the Sub-Library table is the generation of the primary key. The primary key between multiple tables cannot be supported with the self-increment primary key of the database itself, because the primary key generated between the different tables is duplicated. So there are other ways to get the primary key ID.

In general, there are three main solutions:

1. Oracle sequence: Seq.nextval based on third-party Oracle to get an ID advantage: simple to use disadvantage: rely on third-party Oracle databases
2. MySQL ID interval isolation: Different libraries set different starting values and steps, such as 2 MySQL, you can set one to generate an odd number, and the other to generate even. or 1 units with 0~10 billion, the other one with 10~20 billion. Advantage: using MySQL self-increment ID disadvantage: The operation and maintenance cost is relatively high, data expansion needs to set the step size.
3. Based on database update + memory allocation: Maintain an ID in the database, get the next ID, the database will be id=id+100 where id=xx, get 100 IDs, in memory allocation advantage: Simple and efficient disadvantage: the self-increment order cannot be guaranteed

Considering extensibility and maintainability, we took a third scenario. The specific implementation is:

1. We set the Get ID step to L, there are altogether n table allocation IDs.
2. After initialization, the ID values in n tables are 0,l, 2l,3l ...
3. When an app gets an ID from any table, the ID in this table will increase NL on the original ID value.

For example, the step size is 100, a total of 4 ID tables

1. After initialization, the values in 4 tables are, 1:0, 2:100, 3:200, 4:300, respectively
2. Assuming that the application gets the ID from table 2, the value of the four tables becomes, 1:0, 2:500, 3:200, 4:300

This has several benefits:

1. A globally unique ID is implemented.
2. Does not affect the expansion of the business database.
3. Get ID is disaster tolerant, single table unreachable does not affect global.

MySQL distributed implementation ID self-increment