一、自增主键的前世今生

如果把数据库比作图书馆，主键就是每本书的专属编号。MySQL的AUTO_INCREMENT就像一位尽责的图书管理员，每当有新书入库（插入数据），它就会自动给出一串连续的数字编号。但在实际使用中，我们常会发现这个编号序列出现"断层"：

-- 创建示例表（MySQL 8.0+）
CREATE TABLE user_order (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_no VARCHAR(20) NOT NULL,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB AUTO_INCREMENT=1001;

-- 连续插入三条订单
INSERT INTO user_order (order_no) VALUES ('DD20230728001'),('DD20230728002'),('DD20230728003');

-- 查询结果可能显示：
-- | id  | order_no      | create_time          |
-- | 1001| DD20230728001 | 2023-07-28 10:00:00  |
-- | 1002| DD20230728002 | 2023-07-28 10:00:01  |
-- | 1003| DD20230728003 | 2023-07-28 10:00:02  |

但当某天你突然发现订单ID从1003直接跳到了1010，这就像图书馆的编号突然出现断档，让人不禁怀疑：我的数据去哪了？

二、跳号的元凶

2.1 事务回滚导致跳号

START TRANSACTION;
INSERT INTO user_order (order_no) VALUES ('DD20230728004'); -- 分配id=1004
ROLLBACK; -- 回滚后id不回收

-- 下次插入时会跳过1004
INSERT INTO user_order (order_no) VALUES ('DD20230728005'); -- id=1005

2.2 批量插入的隐藏陷阱

-- 批量插入5条记录
INSERT INTO user_order (order_no) VALUES 
('DD20230728006'),
('DD20230728007'),
('DD20230728008'),
('DD20230728009'),
('DD20230728010');

-- 实际可能占用id=1006-1010
-- 若中途服务器重启，可能只保留部分id

2.3 手动插入的破坏力

-- 强制指定id=1015
INSERT INTO user_order (id, order_no) VALUES (1015, 'DD20230728011');

-- 后续自增值自动跳到1016
INSERT INTO user_order (order_no) VALUES ('DD20230728012'); -- id=1016

2.4 主从复制的暗流涌动

在MySQL 5.7的主从架构中，如果主库设置binlog_format=STATEMENT，从库执行批量插入时可能因为执行计划不同导致自增值差异。

2.5 存储引擎的个性差异

MyISAM引擎在表锁机制下能保持绝对连续，而InnoDB为了并发性能采用更宽松的策略，这也是为什么生产环境推荐使用InnoDB但仍然会出现跳号的原因。

三、防御工事建设指南

3.1 事务隔离级别调优

-- 修改事务隔离级别为READ COMMITTED
SET GLOBAL transaction_isolation = 'READ-COMMITTED';

3.2 自增锁模式配置（MySQL 8.0+）

-- 设置自增锁模式为连续模式
SET @@global.innodb_autoinc_lock_mode = 1;

3.3 无符号整型防护

ALTER TABLE user_order MODIFY id BIGINT UNSIGNED AUTO_INCREMENT;

3.4 主键种子维护

-- 定期修正自增值
ALTER TABLE user_order AUTO_INCREMENT = (SELECT MAX(id)+1 FROM user_order);

3.5 业务层双保险

# Python示例：使用Redis生成订单号
import redis

r = redis.Redis()
def generate_order_no():
    date_str = datetime.now().strftime("%Y%m%d")
    seq = r.incr(f"order:{date_str}")
    return f"DD{date_str}{seq:05d}"

四、主键管理的策略

4.1 分布式ID生成方案

// Snowflake算法实现示例
public class SnowflakeIdWorker {
    private final long twepoch = 1288834974657L;
    private final long workerIdBits = 5L;
    // ...其他实现细节
    
    public synchronized long nextId() {
        long timestamp = timeGen();
        if (timestamp < lastTimestamp) {
            throw new RuntimeException("时钟回拨异常");
        }
        // ...生成逻辑
    }
}

4.2 复合主键的妙用

CREATE TABLE operation_log (
    shard_id TINYINT UNSIGNED,
    log_id BIGINT UNSIGNED,
    content TEXT,
    PRIMARY KEY (shard_id, log_id)
) ENGINE=InnoDB;

-- 每个分片独立维护自增值

五、实战场景攻防演练

5.1 电商订单系统

某电商平台采用「Redis自增+数据库双写」策略，既保证前端展示的订单号连续，又保留数据库自增id的性能优势。

5.2 物联网时序数据

使用「时间戳+设备ID+自增值」的复合主键方案，在保证查询效率的同时避免单表自增瓶颈。

六、技术方案优劣

方案 优点 缺点 适用场景
数据库自增 简单易用，索引效率高 存在跳号风险 单机中小型系统
UUID 全局唯一 存储空间大，查询效率低 分布式系统
Redis自增 灵活可控 需要维护缓存 需要连续业务编号
Snowflake 分布式唯一 存在时钟回拨问题 大型分布式系统

七、防踩坑特别行动指南

  1. 永远不要用自增主键作为业务流水号
  2. BIGINT UNSIGNED要从建表开始使用
  3. 定期检查information_schema.TABLES中的AUTO_INCREMENT值
  4. 使用pt-online-schema-change修改自增列时要锁表
  5. 主从复制环境下避免混合使用ROW和STATEMENT格式

八、终极解决方案矩阵

根据业务场景选择最优解：

  • 金融交易系统：数据库自增+业务流水号双轨制
  • 社交平台：Snowflake+哈希分表
  • 物联网应用：时间分片+设备ID复合主键
  • ERP系统：维护独立的编号生成服务