MySQL分表自增ID解决方案

210次阅读

共计 2879 个字符，预计需要花费 8 分钟才能阅读完成。

当我们对 MySQL 进行分表操作后，将不能依赖 MySQL 的自动增量来产生唯一 ID 了，因为数据已经分散到多个表中。

应尽量避免使用自增 IP 来做为主键，为数据库分表操作带来极大的不便。

在 postgreSQL、Oracle、db2 数据库中有一个特殊的特性 —sequence。任何时候数据库可以根据当前表中的记录数大小和步长来获取到该表下一条记录数。然而，MySQL 是没有这种序列对象的。

可以通过下面的方法来实现 sequence 特性产生唯一 ID：
1. 通过 MySQL 表生成 ID
对于插入也就是 insert 操作，首先就是获取唯一的 id 了，就需要一个表来专门创建 id，插入一条记录，并获取最后插入的 ID。代码如下：

CREATE TABLE `ttlsa_com`.`create_id` (
`id` BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY
) ENGINE = MYISAM

也就是说，当我们需要插入数据的时候，必须由这个表来产生 id 值，我的 php 代码的方法如下：

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

这种方法效果很好，但是在高并发情况下，MySQL 的 AUTO_INCREMENT 将导致整个数据库慢。如果存在自增字段，MySQL 会维护一个自增锁，innodb 会在内存里保存一个计数器来记录 auto_increment 值，当插入一个新行数据时，就会用一个表锁来锁住这个计数器，直到插入结束。如果是一行一行的插入是没有问题的，但是在高并发情况下，那就悲催了，表锁会引起 SQL 阻塞，极大的影响性能，还可能会达到 max_connections 值。
innodb_autoinc_lock_mode：可以设定 3 个值：0、1、2
0：traditonal（每次都会产生表锁）
1：consecutive（默认, 可预判行数时使用新方式，不可时使用表锁，对于 simple insert 会获得批量的锁，保证连续插入）
2：interleaved（不会锁表，来一个处理一个，并发最高）
对于 myisam 表引擎是 traditional，每次都会进行表锁的。

2. 通过 redis 生成 ID

3. 队列方式

其实这也算是上面的一个解说
使用队列服务，如 redis、memcacheq 等等，将一定量的 ID 预分配在一个队列里，每次插入操作，先从队列中获取一个 ID，若插入失败的话，将该 ID 再次添加到队列中，同时监控队列数量，当小于阀值时，自动向队列中添加元素。

这种方式可以有规划的对 ID 进行分配，还会带来经济效应，比如QQ号码，各种靓号，明码标价。如网站的 userid, 允许 uid 登陆，推出各种靓号，明码标价，对于普通的 ID 打乱后再随机分配。

<?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());