MySQL 水平拆分之后，自动增长的ID有什么好的解决办法？

Question

当单表的数据量过大时，会采用MySQL进行水平拆分，请问原先的自动增长的ID有什么好的解决办法？

Answer 1

After horizontal splitting, the data of the same table is placed in different libraries. It is no longer possible to rely on the auto_increment of the database itself to achieve ID uniqueness. IDs generated between multiple libraries will cause conflicts. .
So the ID should not be assigned by the database, so what should be assigned? I think there are two situations:

• If the application accesses the backend MySQL through database middleware, then the ID should be generated by the middleware

• If there is no middleware, the ID is generated by the application

But whether it is an application or middleware, there will definitely be multiple applications (multiple clients), and middleware, middleware generally will not deploy a single instance, so there will be a single point problem(single point of failure), middleware is deployed in a cluster in a production environment.

Then the problem will be much clearer. No matter which of the above situations, what you actually need is a global ID generator.

Global generators can be implemented in many ways

• Get ID from public database

• Put the ID generation strategy on the zookeeper cluster and get the global ID on the zookeeper cluster

That’s the basic strategy. There is one more little detail.

Global IDs are best divided into tables. One table corresponds to a global ID context, and different tables go to different global ID contexts.

Another thing, whether it is middleware or application, when fetching the global ID, don’t just fetch one at a time . That performance will be too low. A better way is to every time Get a segment of ID . For example, if application 1 gets the ID 1-50, then it no longer needs to get the ID before the 50 IDs are used up; if application 2 also gets the ID, then it will get the ID. To ID 51-100, this idea is a bit like storing food for winter.

Answer 2

My current known methods:
1. Modify the original auto-incrementing column to a non-auto-incrementing primary key. Maintain the primary key yourself

2. Horizontal splitting means splitting existing data, which means that the data in the split table will not change. Newly added data will still be added automatically. (Note that you cannot set auto-increment to fill in blank ids)

3. Make a unified algorithm. The auto-incrementing ID needs to be calculated and written instead of automatically maintained

Answer 3

I have had such a need before.
This is how I handle it: remove the auto-increment of the primary key column, generate the auto-increment sequence value through the incr of redis, and specify the value of the id when inserting

Answer 4

1. Make the function of auto-incrementing ID into a small module using a table and a stored procedure.

2. When there is data INSERT in the split table, this stored procedure is called to apply for a new ID.

Answer 5

After splitting the table, it is best to generate the primary key yourself. There are many open source primary key generation strategy algorithms, such as Twitter's snowflake, etc.
If you do not want to change the program, set the starting point of the auto-increment ID in each cluster (auto_increment_offset) and The ID auto-increment step (auto_increment_increment) staggers the starting point of each cluster to achieve the effect of relatively segmenting the ID to achieve global uniqueness. The advantage is that it is simple to implement and transparent to applications. The disadvantage is that it will not be easy to expand if routing is done based on ID in the future