MySQL 分表後自增 ID 衝突解決方案：三大架構模式深度對比

問題背景

分表（Sharding）後，各分表獨立維護 AUTO_INCREMENT，導致全域 ID 重複。MySQL 原生不支援 Sequence（Oracle/PostgreSQL 有），需自行實現全域唯一 ID 生成器。

方案一：資料庫單表生成 ID（集中式）

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

function get_AI_ID() {
    $sql = "INSERT INTO create_id (id) VALUES ('')";
    $this->db->query($sql);
    return $this->db->insertID();
}

優點：實現簡單、ID 連續。
缺點：高併發下 AUTO_INCREMENT 表鎖成為瓶頸（MyISAM 全表鎖，InnoDB 表鎖/行鎖視 innodb_autoinc_lock_mode 而定）。

InnoDB 自增鎖模式調優

• 0：傳統模式，每次插入皆表鎖。
• 1（預設）：連續模式，Simple Insert 批量鎖，其餘表鎖。
• 2：交錯模式，無表鎖，並發最高，但 ID 可能不連續（主從複制需 ROW 格式）。

方案二：Redis 原子計數器（高效能）

$r = new Redis();
$r->connect('127.0.0.1', 6379);

function get_next_autoincrement($r, $timeout=60) {
    // 簡易分布式鎖防止併發衝突
    $lockKey = 'serial:lock';
    $start = time();
    while ($r->get($lockKey)) {
        if (time() - $start > $timeout) return false;
        sleep(1);
    }
    $id = $r->incr('serial');
    if ($id > 1) return $id;

    // 首次初始化：從 DB 讀取最大 ID
    if ($r->setnx($lockKey, 1)) {
        $r->expire($lockKey, 300);
        $maxId = $db->query("SELECT MAX(id) FROM user_posts")->fetchColumn();
        $r->set('serial', ++$maxId);
        $r->del($lockKey);
        return $maxId;
    }
    return 0; // 獲取鎖失敗
}

優點：極高併發效能、單機/集群通用。
缺點：引入外部依賴、需處理 Redis 故障降級。

方案三：預分配 ID 隊列（號段模式）

批量從資料庫或 Redis 取得一段 ID（如 10000~19999），本地記憶體消費，用完再取下一段。

class IdGenerator {
    private $r, $queueKey = 'id_queue', $step = 1000;

    public function __construct(Redis $r) { $this->r = $r; }

    public function nextId() {
        $id = $this->r->lPop($this->queueKey);
        if ($id !== false) return (int)$id;
        return $this->refillAndPop();
    }

    private function refillAndPop() {
        // 單機鎖或 Redis SETNX 保護
        $lock = $this->r->set('id_lock', 1, ['nx', 'ex' => 10]);
        if (!$lock) { usleep(10000); return $this->nextId(); }

        $maxId = $db->query("SELECT MAX(id) FROM user_posts")->fetchColumn();
        $start = $maxId + 1;
        $ids = range($start, $start + $this->step - 1);
        $this->r->rPush($this->queueKey, ...$ids);
        $this->r->del('id_lock');
        return $this->r->lPop($this->queueKey);
    }
}

優點：減少跨網絡呼叫、可規劃靚號、業務彈性大。
缺點：ID 不連續、需監控隊列長度自動補充。

選型建議

總結

分表後放棄資料庫原生自增，改用「集中式生成」或「分散式演算法（Snowflake/Redis/號段）」是主流做法。根據業務量級、基礎設施成熟度、ID 連續性需求綜合選型。