Daily Learning 20170224-Library sub-table global ID generation

Due to the data volume and IO efficiency factors, many projects to the data-supported database will take the form of a library. One problem that needs to be resolved after using the sub-Library is the generation of primary keys. Primary keys between multiple tables cannot be supported with the database itself, since primary keys generated between tables are duplicated. So there is another way to get the primary key ID.

Generally speaking, there are three kinds of solutions: Oracle sequence: Seq.nextval based on third party Oracle to gain an ID advantage: simple and available disadvantage: need to rely on third party Oracle database MySQL ID interval isolation: Different libraries set different starting values and step lengths, such as 2 MySQL, you can set one to generate only odd, and another generation even. or 1 units with 0~10 billion, another one with 10~20 billion. Advantages: Use MySQL self-increasing ID disadvantage: high operation and maintenance costs, data expansion needs to reset the step. 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, the allocation advantage in memory: simple and efficient disadvantages: unable to guarantee the order of self-increase

In view of extensibility and maintainability, we have adopted a third scenario. The implementation is: we set get ID step is L, a total of n table allocation ID. After initialization, the ID values in n tables are 0,l, 2l,3l ... When an application obtains an ID from any table, the ID in the table adds a NL to the original ID value.

For example, the step is 100, a total of 4 ID tables initialized, 4 table values are, 1:0, 2:100, 3:200, 4:300 assuming that the application gets the ID from table 2, then the four table values become, 1:0, 2:500, 3:200, 4:300

This has several benefits: implementing a globally unique ID. Does not affect the expansion of the business database. Getting IDs is disaster-tolerant, and a single table cannot be accessed without affecting the global.