The solution of MySQL sub table self increment ID problem

When we separate tables on MySQL, we will not be able to rely on the automatic increment of MySQL to produce only 1ID because the data has been spread across multiple tables.
Should try to avoid the use of self increment IP as the primary key, for the database table operation to bring great inconvenience.
There is a special feature in the postgreSQL, oracle, db2 databases -sequence. At any time, the database can obtain the number of records in the current table according to the number of records in the table and the step size. However, ES11en does not have such a sequence object.
The sequence feature can be implemented by the following methods to produce only 1ID:

1. Generate ID from MySQL table
For the insert (insert) operation, the first step is to get the 1 only id, which requires a table to create id specifically, insert 1 record, and get 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, when we need to insert data, we must use this table to generate id values. My php code works like this:

<?php 
function get_AI_ID() { 
 $sql = "insert into create_id (id) values('')"; 
 $this->db->query($sql); 
 return $this->db->insertID(); 
} 
?> 

This approach works well, but in the case of high concurrency, AUTO_INCREMENT of MySQL causes the entire database to slow down. If there is a self-increment field, MySQL maintains a self-increment lock, and innodb keeps a counter in memory to record the value of auto_increment. When a new row is inserted, a table lock is used to lock the counter until the bundle is inserted. Table locks can cause SQL blocking, which can significantly affect performance and may even reach the max_connections value.
innodb_autoinc_lock_mode: Three values can be set: 0, 1, 2
0: traditonal (table lock generated each time)
1: consecutive (default, when the number of rows can be predicted to use the new mode, if not use table lock, simple will get batch lock, ensure continuous insert)
2: interleaved (do not lock tables, one for each, maximum concurrency)
For myisam the table engine is traditional, and the table lock will be performed every time.

2. Generate ID from 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 the above 1 commentary
Use queue services, such as redis, memcacheq, etc., to pre-allocate a certain amount of ID in a queue. For each insert operation, get 1 ID from the queue first.
This way can have a planning of the ID distribution, but also bring economic effects, such as QQ number, a variety of beautiful number, clearly priced. Such as the site of userid, allow uid landing, launch a variety of colors, clearly marked price, for ordinary ID after random distribution.

<?php
 
class common {
 
 private $r;
 
 function construct() {
  $this->__construct();
 }
 
 public function __construct(){
  $this->r=new Redis();
  $this->r->connect('127.0.0.1', 6379);
 }
 
 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()); 

Monitor queue count and automatically replenish queue and fetch ES86en but not used