一、核心概念

自增主键不连续（ID空洞）是指自增序列中出现”跳号”现象，如：1, 2, 3, 5, 6, 8, 10…

关键特性：

  • ✅ 这是正常现象，不是Bug
  • ✅ MySQL设计上不保证连续性，只保证唯一性和递增性
  • ⚠️ 已分配的ID不会回收重用

二、产生空洞的8种场景

场景1：事务回滚（最常见） 

-- 会话A
BEGIN;
INSERT INTO orders (user_id, amount) VALUES (1001, 99.99);  -- 分配ID=100
SELECT LAST_INSERT_ID();  -- 返回100
ROLLBACK;  -- 回滚事务

-- 会话B
INSERT INTO orders (user_id, amount) VALUES (1002, 88.88);  -- 分配ID=101（不是100）

原因：

  • InnoDB在分配自增ID时，立即递增计数器
  • 即使事务回滚，计数器也不会回退
  • 这是性能优化设计：避免回滚时的锁竞争

源码原理：

// ha_innobase::write_row
int write_row(uchar *record) {
    // 1. 获取自增ID（此时计数器已递增）
    auto_inc = get_auto_increment();  // 假设获得100
    
    // 2. 写入数据
    error = row_insert_for_mysql(record);
    
    // 3. 如果事务回滚
    if (rollback) {
        // 数据行删除，但计数器不回退！
        // 下一次分配将是101
    }
}

场景2：插入失败（唯一键冲突） 

CREATE TABLE user (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(100) UNIQUE KEY
);

INSERT INTO user VALUES (1, 'test@example.com');  -- ID=1, 成功

INSERT INTO user (email) VALUES ('test@example.com');  -- 尝试分配ID=2，但失败
-- ERROR 1062: Duplicate entry 'test@example.com' for key 'email'

INSERT INTO user (email) VALUES ('new@example.com');   -- 分配ID=3（跳过2）

流程：

1. 申请自增ID: 2
2. 计数器递增: AUTO_INCREMENT = 3
3. 检查唯一约束: 发现email重复
4. 抛出异常，插入失败
5. ID=2被浪费，下次从3开始

场景3：批量插入失败 

-- 批量插入，部分成功，部分失败
INSERT INTO user (email) VALUES 
('user1@example.com'),  -- ID=1, 成功
('user2@example.com'),  -- ID=2, 成功
('user1@example.com'),  -- ID=3, 失败（重复）
('user3@example.com');  -- ID=4, 可能失败（取决于MySQL版本）

-- 结果：ID序列为 1, 2, (3被跳过), 4或(4也被跳过)

注意：MySQL 5.7+ 批量插入遇到错误会中止后续行的插入。

场景4：DELETE操作 

INSERT INTO orders (user_id, amount) VALUES (1001, 99.99);  -- ID=1
INSERT INTO orders (user_id, amount) VALUES (1002, 88.88);  -- ID=2
INSERT INTO orders (user_id, amount) VALUES (1003, 77.77);  -- ID=3

-- 删除ID=2
DELETE FROM orders WHERE id = 2;

-- 继续插入
INSERT INTO orders (user_id, amount) VALUES (1004, 66.66);  -- ID=4（不会复用2）

特点：

  • DELETE只删除数据行，不影响自增计数器
  • 已删除的ID永久空缺
  • 这是最常见的业务场景

场景5：REPLACE/INSERT … ON DUPLICATE KEY UPDATE 

CREATE TABLE config (
    id INT AUTO_INCREMENT PRIMARY KEY,
    key_name VARCHAR(50) UNIQUE,
    value VARCHAR(100)
);

INSERT INTO config (key_name, value) VALUES ('max_connections', '100');  -- ID=1

-- REPLACE会先DELETE后INSERT
REPLACE INTO config (key_name, value) VALUES ('max_connections', '200');
-- 内部流程：
--   1. DELETE id=1
--   2. INSERT新记录，分配ID=2

SELECT * FROM config;
-- 结果：id=2, key_name='max_connections', value='200'
-- ID=1被浪费

REPLACE的ID消耗：

for (int i = 0; i < 100; i++) {
    jdbcTemplate.update(
        "REPLACE INTO config (key_name, value) VALUES (?, ?)",
        "key1", "value" + i
    );
}
// 执行100次REPLACE，消耗200个ID（1次DELETE + 1次INSERT）

场景6：自增锁模式2下的批量插入 

SET GLOBAL innodb_autoinc_lock_mode = 2;  -- 交错模式

-- 会话A：批量插入
INSERT INTO orders (user_id) 
SELECT user_id FROM users LIMIT 1000;  -- 预分配1000个ID

-- 会话B：单条插入（并发执行）
INSERT INTO orders (user_id) VALUES (9999);  -- 可能插入到A的ID范围内

-- 如果会话A的INSERT最终只成功了800条
-- 剩余200个ID会被浪费

原因：

  • 模式2下，ID是预分配的
  • 如果实际插入行数少于预分配数量，多余的ID被浪费

场景7：AUTO_INCREMENT手动修改 

-- 当前ID=100
ALTER TABLE orders AUTO_INCREMENT = 200;

-- 下一次插入
INSERT INTO orders (user_id) VALUES (1001);  -- ID=200
-- ID 101-199 被跳过

使用场景：

  • 数据迁移后对齐ID
  • 预留ID范围给特定业务

场景8：MySQL服务重启（MySQL 5.7及以下） 

-- MySQL 5.7行为
INSERT INTO orders (user_id) VALUES (1001);  -- ID=100
-- 当前 AUTO_INCREMENT = 101

-- 重启MySQL服务
-- ...

-- 重启后，MySQL重新计算AUTO_INCREMENT
SELECT MAX(id) + 1 FROM orders;  -- 101
-- 但如果之前有DELETE操作，可能导致：

DELETE FROM orders WHERE id = 100;
-- 重启MySQL
-- AUTO_INCREMENT 重新计算为 SELECT MAX(id) + 1 = 假设之前MAX是99
-- 下一次插入可能是ID=100（复用了！）

MySQL 8.0改进：

  • 自增值持久化到Redo Log
  • 重启后不会丢失
  • 避免了ID回退问题

三、空洞对业务的影响

影响1：ID不连续导致的误解 

// 错误示例：依赖ID连续性的分页
@GetMapping("/orders/page/{page}")
public List<Order> getOrdersByPage(@PathVariable int page) {
    int pageSize = 100;
    int startId = (page - 1) * pageSize + 1;  // 假设ID连续
    int endId = page * pageSize;
    
    return jdbcTemplate.query(
        "SELECT * FROM orders WHERE id BETWEEN ? AND ?",
        new OrderMapper(), startId, endId
    );
    // 问题：如果ID有空洞，每页记录数不一致
}

正确做法：

// 使用LIMIT OFFSET分页
public List<Order> getOrdersByPage(int page) {
    int pageSize = 100;
    int offset = (page - 1) * pageSize;
    
    return jdbcTemplate.query(
        "SELECT * FROM orders ORDER BY id LIMIT ? OFFSET ?",
        new OrderMapper(), pageSize, offset
    );
}

影响2：统计数据不准确 

// 错误：通过ID范围估算记录数
long estimatedCount = maxId - minId + 1;  // 不准确！

// 正确：使用COUNT
long actualCount = jdbcTemplate.queryForObject(
    "SELECT COUNT(*) FROM orders", Long.class
);

影响3：安全风险（订单号泄露） 

-- 如果订单号直接使用自增ID
-- 用户可以通过ID猜测订单总量
-- 订单ID: 1000001, 1000002 → 推断日订单量

解决方案：

// 使用独立的订单号生成策略
String orderNo = String.format("ORD%s%08d", 
    DateUtil.format(new Date(), "yyyyMMdd"),
    snowflakeId.nextId()
);
// 订单号: ORD202511021234567890

四、如何避免或减少空洞

方法1：减少事务回滚 

// 差：先插入再校验
@Transactional
public void createOrder(OrderDTO dto) {
    Order order = new Order();
    order.setUserId(dto.getUserId());
    orderMapper.insert(order);  // 已分配ID
    
    // 校验失败，回滚
    if (!validateStock(dto.getProductId())) {
        throw new RuntimeException("库存不足");  // 导致ID浪费
    }
}

// 优：先校验再插入
@Transactional
public void createOrder(OrderDTO dto) {
    // 先执行所有校验
    if (!validateStock(dto.getProductId())) {
        throw new RuntimeException("库存不足");  // 未消耗ID
    }
    
    // 校验通过后插入
    Order order = new Order();
    order.setUserId(dto.getUserId());
    orderMapper.insert(order);
}

方法2：使用INSERT IGNORE减少失败 

// 差：捕获异常导致ID浪费
try {
    jdbcTemplate.update(
        "INSERT INTO user (email) VALUES (?)", 
        "test@example.com"
    );
} catch (DuplicateKeyException e) {
    // ID已被消耗
}

// 优：使用INSERT IGNORE
int rows = jdbcTemplate.update(
    "INSERT IGNORE INTO user (email) VALUES (?)", 
    "test@example.com"
);
if (rows == 0) {
    // 已存在，但ID未被消耗
}

注意：这个优化在MySQL 8.0+效果明显，老版本INSERT IGNORE仍会消耗ID。

方法3：避免频繁REPLACE 

// 差：使用REPLACE（消耗2个ID）
jdbcTemplate.update(
    "REPLACE INTO config (key_name, value) VALUES (?, ?)",
    "key1", "value1"
);

// 优：使用INSERT ... ON DUPLICATE KEY UPDATE
jdbcTemplate.update(
    "INSERT INTO config (key_name, value) VALUES (?, ?) " +
    "ON DUPLICATE KEY UPDATE value = VALUES(value)",
    "key1", "value1"
);
// 不消耗额外ID

方法4：选择合适的自增锁模式 

-- 高并发场景，接受ID空洞
SET GLOBAL innodb_autoinc_lock_mode = 2;  -- 性能最优

-- 需要更少空洞（牺牲性能）
SET GLOBAL innodb_autoinc_lock_mode = 1;  -- 默认

方法5：使用独立的ID生成服务 

@Service
public class OrderService {
    @Autowired
    private IdGenerator idGenerator;
    
    public void createOrder(OrderDTO dto) {
        // 预先生成ID
        Long orderId = idGenerator.nextId();
        
        // 校验逻辑
        if (!validate(dto)) {
            return;  // 校验失败，未使用ID（可回收到ID池）
        }
        
        // 插入订单
        Order order = new Order();
        order.setId(orderId);
        orderMapper.insert(order);
    }
}

五、空洞检测与监控

SQL检测空洞 

-- 检测ID空洞
SELECT 
    id + 1 AS gap_start,
    next_id - 1 AS gap_end,
    next_id - id - 1 AS gap_size
FROM (
    SELECT id, LEAD(id) OVER (ORDER BY id) AS next_id
    FROM orders
) t
WHERE next_id - id > 1;

-- 结果示例
-- gap_start | gap_end | gap_size
-- 5         | 9       | 5
-- 15        | 19      | 5

空洞率监控 

-- 计算空洞率
SELECT 
    COUNT(*) AS total_records,
    MAX(id) AS max_id,
    MAX(id) - COUNT(*) AS total_gaps,
    ROUND((MAX(id) - COUNT(*)) / MAX(id) * 100, 2) AS gap_percentage
FROM orders;

-- 结果示例
-- total_records | max_id | total_gaps | gap_percentage
-- 8000          | 10000  | 2000       | 20.00%

告警策略：

  • 空洞率 > 30%：检查是否有大量事务回滚
  • 空洞率 > 50%：可能存在异常（需排查）

六、答题总结

面试回答框架：

  1. 核心原理：
    “MySQL自增主键只保证唯一性和递增性，不保证连续性。已分配的ID不会回收，这是性能优化的设计”

  2. 主要场景（列举3-5个）：
    “最常见的是事务回滚、插入失败、DELETE操作。比如事务获取ID后回滚，计数器不会回退；唯一键冲突也会浪费ID”

  3. 业务影响：
    “一般不影响功能，但不能依赖ID连续性做分页或统计。如果直接暴露给用户（如订单号），可能泄露业务数据”

  4. 优化建议：
    “减少事务回滚，先校验再插入；避免频繁REPLACE；业务主键建议使用分布式ID生成器，与数据库自增ID解耦”

关键点：

  • 理解自增ID的分配时机（获取时即递增）
  • 能列举常见的空洞产生场景
  • 知道空洞对业务的潜在影响
  • 掌握减少空洞的优化方法