利用 MySQL 事務特性實現高並發安全的全局自增 ID 生成器

背景痛點

• 單表 `AUTO_INCREMENT` 簡單但**不支持分表/分庫** (ID 會重複)。
• UUID 無序、長、不利於索引性能 (B+ 樹頁面分裂)。
• Redis/雪花算法引入外部依賴，運維成本高。
• **核心需求**：全局唯一、有序遞增、高並發安全、純數據庫實現、支持分表分庫。

方案一：單表行鎖 + 存儲過程 (強一致、易理解)

1. 元數據表設計

CREATE TABLE `auto_id` (
  `id_name` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '業務標識，如 order_id, user_id',
  `id` BIGINT(20) NOT NULL DEFAULT 0 COMMENT '當前最大 ID',
  PRIMARY KEY (`id_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='全局自增 ID 生成器';

2. 存儲過程：原子獲取並遞增

DELIMITER //
DROP PROCEDURE IF EXISTS `get_increment_id`//
CREATE PROCEDURE `get_increment_id`(
    IN `p_id_name` VARCHAR(20),
    IN `p_start_val` BIGINT,    -- 初始值/步長
    OUT `p_out_id` BIGINT
)
BEGIN
    DECLARE v_old_id BIGINT DEFAULT 0;
    
    START TRANSACTION;
    
    -- 核心：SELECT ... FOR UPDATE 獲取排他鎖 (X鎖)
    -- 阻塞其他事務讀寫此行，保證並發安全
    SELECT `id` INTO v_old_id FROM `auto_id` WHERE `id_name` = p_id_name FOR UPDATE;
    
    IF v_old_id IS NULL THEN
        -- 首次初始化
        INSERT INTO `auto_id` (`id_name`, `id`) VALUES (p_id_name, p_start_val);
        SET p_out_id = p_start_val;
    ELSE
        -- 遞增更新
        UPDATE `auto_id` SET `id` = `id` + 1 WHERE `id_name` = p_id_name;
        SET p_out_id = v_old_id + 1;
    END IF;
    
    COMMIT;
END//
DELIMITER ;

3. PHP 調用示例

$key = 'order_id';
$start = 1000000; // 起始 ID

// 1. 調用存儲過程
$sql = "CALL get_increment_id('{$key}', {$start}, @id)";
$db->execute($sql);

// 2. 獲取返回值
$ret = $db->query("SELECT @id AS new_id");
$newId = $ret[0]['new_id'];

echo "生成全局 ID: {$newId}";

核心機制解析

• `SELECT ... FOR UPDATE`：在事務中對讀取行加**排他鎖 (X-Lock)**。其他事務嘗試讀取/更新同一行會**阻塞等待**，直到當前事務 `COMMIT`。
• 事務原子性：讀取→計算→更新→提交 作為不可分割單元，杜絕重複 ID。
• 行級鎖：InnoDB 默認行鎖，不同 `id_name` (不同業務) 互不干擾，並發性能好。

方案二：利用 `AUTO_INCREMENT` + `LAST_INSERT_ID()` (無需存儲過程、極簡)

1. 輔助表 (每個 ID 類型一張表)

CREATE TABLE `seq_order_id` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM; -- MyISAM 表級鎖下 LAST_INSERT_ID 也安全，InnoDB 同樣適用

2. 兩條 SQL 完成獲取

-- 1. 更新觸發自增，並將新值設置到會話上下文
UPDATE `seq_order_id` SET `id` = LAST_INSERT_ID(`id` + 1);

-- 2. 直接讀取會話上下文中的新 ID (無需查表，極快)
SELECT LAST_INSERT_ID();

原理

• `LAST_INSERT_ID(expr)`：將 `expr` 設置為當前會話的 `LAST_INSERT_ID` 值，**不依賴於表查詢**。
• 會話隔離：每個連接的 `LAST_INSERT_ID` 互不影響，天然線程安全。
• 無需顯式事務/鎖：單條 `UPDATE` 原子性由存儲引擎保證。

優缺點對比

生產環境優化建議

1. 批量獲取 (Step 模式)：一次獲取一段 ID (如 1000 個)，應用本地分配，減少 DB 交互 99.9%。修改存儲過程 `UPDATE ... SET id = id + p_step`，返回起始 ID。
2. 雙主/集群模式：配置 `auto_increment_offset` 和 `auto_increment_increment` 避免雙寫衝突。
3. 監控告警：監控 `auto_id` 表最大值，預留空間防溢出。
4. 備選方案：極高並發 (萬級 QPS) 建議上 Redis 段號模式或雪花算法。

總結

純 MySQL 實現全局自增 ID，**方案二 (LAST_INSERT_ID) 代碼最少、性能最強、原理最簡**，推薦優先使用。方案一適合需集中管理所有業務 ID、或已有存儲過程規範的系統。關鍵在於利用數據庫**原子操作與會話隔離特性**，避免應用層鎖與外部依賴。