MySQL table sharding auto-incremental ID Solution

MySQL table sharding auto-incremental ID Solution

After we perform the table sharding operation on MySQL, we cannot rely on the automatic increment of MySQL to generate a unique ID, because the data has been distributed to multiple tables.

Avoid using an auto-increment IP address as the primary key as much as possible, which will cause great inconvenience to the database table sharding operation.

Sequence is a special feature in postgreSQL, Oracle, and db2 databases. At any time, the database can obtain the next number of records in the current table based on the number of records and step size. However, MySQL does not have such a sequence object.

You can use the following method to generate a unique ID for the sequence feature:
1. generate an ID using the MySQL table
For insert, that is, the insert operation, the unique id is obtained first. A table is required to create an id, insert a record, and obtain the last inserted ID. The Code is as follows:

Create table 'ttlsa _ com'. 'create _ id '(
'Id' BIGINT (20) not null AUTO_INCREMENT PRIMARY KEY
) ENGINE = MYISAM

That is to say, when we need to insert data, this table must generate the id value. The method of my php code is as follows:

<? Php
Function get_AI_ID (){
$ SQL = "insert into create_id (id) values ('')";
$ This-> db-> query ($ SQL );
Return $ this-> db-> insertID ();
}
?>

This method works well, but in high concurrency, The AUTO_INCREMENT of MySQL will cause the whole database to slow down. If an auto-increment field exists, MySQL maintains an auto-increment lock. innodb stores a counter in the memory to record the auto_increment value. When a new row of data is inserted, A table lock is used to lock the counter until the end of the row is inserted. If one row and one row are inserted, but in the case of high concurrency, the table lock will cause SQL blocking and greatly affect the performance, it may also reach the max_connections value.
Innodb_autoinc_lock_mode: You can set three values: 0, 1, and 2.
0: traditonal (Table lock is generated every time)
1: consecutive)
2: interleaved)
The myisam Table engine is traditional and is locked every time.

2. generate an ID through redis

 

Function get_next_autoincrement_waitlock ($ timeout = 60 ){
$ Count = $ timeout> 0? $ Timeout: 60;
 
While ($ r-> get ("serial: lock ")){
$ Count ++;
Sleep (1 );
If ($ count> 10)
Return false;
}
 
Return true;
}
 
Function get_next_autoincrement ($ timeout = 60 ){
// First check if we are locked...
If (get_next_autoincrement_waitlock ($ timeout) = false)
Return 0;
 
$ Id = $ r-> incr ("serial ");
 
If ($ id> 1)
Return $ id;
 
// If ID = 1, we assume we do not have "serial" key...
 
// First we need to get lock.
If ($ r-> setnx ("serial: lock"), 1 ){
$ R-> expire ("serial: lock", 60*5 );
 
// Get max (id) from database.
$ Id = select_db_query ("select max (id) from user_posts ");
// Or alternatively:
// Select id from user_posts order by id desc limit 1
 
// Increase it
$ Id ++;
 
// Update Redis key
$ R-> set ("serial", $ id );
 
// Release the lock
$ R-> del ("serial: lock ");
 
Return $ id;
}
 
// Can not get lock.
Return 0;
}
 
$ R = new Redis ();
$ R-> connect ("127.0.0.1", "6379 ");
 
$ Id = get_next_autoincrement ();
If ($ id ){
$ SQL = "insert into user_posts (id, user, message) values ($ id, '$ user',' $ message ')"
$ Data = exec_db_query ($ SQL );
}

 

3. Queue Mode

In fact, this is also an explanation above.
Queue services, such as redis and memcacheq, are used to pre-allocate a certain amount of IDs in a queue. Each insertion Operation first obtains an ID from the queue. If the insertion fails, add this ID to the queue again, and monitor the number of queues. When the value is smaller than the threshold, elements are automatically added to the queue.

In this way, you can plan to allocate IDs and bring economic effects, such as QQ numbers, various nicknames, and clear the price. For example, the website's userid allows the uid to log on, and various nicknames are introduced. The price is clearly indicated. After the common IDs are disrupted, they are randomly allocated.

 

<? Php
 
Class common {
 
Private $ r;
 
Function construct (){
$ This->__ construct ();
}
 
Public function _ construct (){
$ This-> r = new Redis ();
$ This-> r-> connect ('2017. 0.0.1 ', 127 );
}
 
Function set_queue_id ($ ids ){
If (is_array ($ ids) & isset ($ ids )){
Foreach ($ ids as $ id ){
$ This-> r-> LPUSH ('Next _ autoincrement ', $ id );
}
}
}
 
Function get_next_autoincrement (){
Return $ this-> r-> LPOP ('Next _ autoincrement ');
}
 
}
 
$ Createid = array ();
While (count ($ createid) <20 ){
$ Num = rand (1000,4000 );
If (! In_array ($ num, $ createid ))
$ Createid [] = $ num;
}
 
$ Id = new common ();
$ Id-> set_queue_id ($ createid );
 
Var_dump ($ id-> get_next_autoincrement ());

Monitors the number of queues, automatically supplements the queue and obtains the id, but does not use

This article permanently updates the link address: