Id generator design in horizontal database Segmentation

Overview

 

The ID generator is an independent database named id_generator. The table design principle in the database is that each table corresponds to an external table that needs to obtain the ID. Many external tables that need to obtain the ID cannot share a table in the ID generator. The table name rules are: ID _ [external_table_names]. The table structure is as follows:

CREATE TABLE `id_user` (

`AutoID` INT(11) NOT NULL AUTO_INCREMENT,

PRIMARY KEY (`AutoID`)

)

COLLATE='utf8_general_ci'

ENGINE=InnoDB

ROW_FORMAT=DEFAULT

AUTO_INCREMENT=1

 

 

Implementation Principle

 

The ID Generator program is built in the data access layer Dal of each application server. That is to say, the ID Generator program in each application server is an independent and complete body.

 

The ID generator internally maintains a "first-in-first-out" queue, which also serves as a cache. Each time the ID generator is called to obtain the ID, an ID is retrieved from the queue and sent to the caller.

 

When the ID generator is initialized, it first writes 500 data records to the database in batches, then extracts the last 500 IDs, and then writes the "first-in-first-out" queue for the caller to use. When the queue is empty, the system writes the last 500 IDs to the database 500 times and writes them to the queue.

 

Key Technical Points

 

To ensure that the IDS written to the database in batches are correct, you must enable database transactions when writing data to the database in batches and ensure data correctness through database transactions.

 

When an application server is restarted, the ID value maintained in the ID generator queue will also be lost, but this will not produce duplicate IDs, and the lost IDs will be voided, when the ID generator squadron column is empty, it will immediately obtain the latest batch of IDs from the database for the caller to use. If the ID is lost, the result is that the primary key ID value of the target table requiring the ID is inconsistent with the total record value of the table, the total record value of the table is smaller than the value of the table's primary key ID.

 

Performance Test Data

 

Scenario 1: compare 500 IDs and 1000 IDs obtained once, and enable connection of a database once.

 

500Items	1000Items
60 ms	122 Ms
58 Ms	119 Ms
63 MS	141

 

 

Scenario 2: Compare 5000 IDs and 10000 IDs obtained 10 times and call 10 times in sequence. Connection of 10 databases is enabled sequentially.

 

5000Items	10000Items
653 Ms	963 Ms
745 Ms	875 Ms
591 Ms	915 Ms

 

 

Scenario 3: Five threads obtain the comparison of 500 IDs and 1000 IDs respectively, and one thread starts the connection of one database.

 

500Items	1000Items
143 ~ 219 Ms	425 ~ 479 Ms
171 ~ 225 ms	402 ~ 460 Ms
165 ~ 244 Ms	380 ~ 481 Ms

 

 

Conclusion

From the test data, the performance of the ID generator is still satisfactory, and the implementation of the entire solution is relatively simple, and the maintenance is also simple.