高效填充SQL表ID间隙的方案（结合C++ unordered_map）

现有系统将SQL表userdata的32位有符号INT类型id及对应dataset加载到C++ unordered_map中，数据增删频繁（约10ms一次）且非全量加载。程序启动时读取MAX(id)作为计数器起始值，新增数据采用计数器自增生成id，已删除数据的id未被复用，导致id序列出现间隙，终将引发32位INT溢出。需实现高效填充最小id间隙的方案，同时兼顾内存数据（延迟数分钟保存）与SQL表的一致性。

针对需求中「批量返回所有空闲id」的要求，以下提供不同数据库兼容的SQL方案：

方案1：基于递归CTE生成连续序列（通用兼容） #

通过递归生成从1到当前MAX(id)的连续id序列，再排除已存在于userdata中的id，得到所有间隙：

WITH RECURSIVE id_range AS (
    SELECT 1 AS id
    UNION ALL
    SELECT id + 1 FROM id_range WHERE id < (SELECT COALESCE(MAX(id), 0) FROM userdata)
)
SELECT ir.id AS free_id
FROM id_range ir
LEFT JOIN userdata ud ON ir.id = ud.id
WHERE ud.id IS NULL
ORDER BY ir.id;

• 注：COALESCE(MAX(id), 0)用于处理表为空的情况，此时会返回从1开始的序列。

方案2：基于窗口函数定位间隙区间（高效版，适用于PostgreSQL/MySQL 8.0+） #

先通过窗口函数LEAD定位每个id与下一个id的间隙区间，再生成区间内的所有空闲id：

-- PostgreSQL版本
WITH id_gaps AS (
    -- 定位中间间隙
    SELECT 
        id + 1 AS start_gap,
        LEAD(id) OVER (ORDER BY id) - 1 AS end_gap
    FROM userdata
    UNION ALL
    -- 处理从1开始的缺失（若最小id大于1）
    SELECT 1, MIN(id) - 1 FROM userdata WHERE MIN(id) > 1
    UNION ALL
    -- 处理MAX(id)之后的连续id（可选，若需要同时生成新id）
    SELECT (SELECT COALESCE(MAX(id), 0) + 1), (SELECT COALESCE(MAX(id), 0) + 100)
)
SELECT generate_series(start_gap, end_gap) AS free_id
FROM id_gaps
WHERE start_gap <= end_gap
ORDER BY free_id;

-- MySQL 8.0+版本（需替换generate_series为递归生成）
WITH RECURSIVE id_gaps AS (
    SELECT 
        id + 1 AS start_gap,
        LEAD(id) OVER (ORDER BY id) - 1 AS end_gap
    FROM userdata
    UNION ALL
    SELECT 1, MIN(id) - 1 FROM userdata WHERE MIN(id) > 1
    UNION ALL
    SELECT (SELECT COALESCE(MAX(id), 0) + 1), (SELECT COALESCE(MAX(id), 0) + 100)
),
gap_series AS (
    SELECT start_gap AS free_id, end_gap FROM id_gaps WHERE start_gap <= end_gap
    UNION ALL
    SELECT free_id + 1, end_gap FROM gap_series WHERE free_id < end_gap
)
SELECT free_id
FROM gap_series
ORDER BY free_id;

• 该方案避免了生成全量连续序列，在数据量较大时性能更优；
• 最后一个UNION ALL可按需添加，用于批量生成新的自增id（当间隙耗尽时）。

思路1：优先复用内存空闲id，再查SQL间隙 #

1. 在内存中维护一个空闲id队列：当程序内删除dataset时，直接将对应的id加入队列；
2. 新增数据时优先从队列头部取id，取到后直接使用；
3. 队列空时，执行SQL查询最小空闲id（可复用下方简化SQL），检查unordered_map中是否存在该id（避免内存延迟同步导致的冲突），若不存在则使用，否则继续查询下一个间隙；
4. 定期同步队列与SQL表：每隔一段时间清理队列中已被SQL表占用的id。

快速查询最小空闲id的SQL #

SELECT MIN(ir.id) AS smallest_free_id
FROM (
    SELECT 1 AS id
    UNION ALL
    SELECT id + 1 FROM userdata
) ir
LEFT JOIN userdata ud ON ir.id = ud.id
WHERE ud.id IS NULL
AND ir.id <= (SELECT COALESCE(MAX(id), 0) FROM userdata);

思路2：批量预取空闲id，结合事务锁避免冲突 #

1. 一次性从SQL查询一批空闲id（比如100个）存入内存vector；
2. 每次新增时从vector头部取id，使用前通过SELECT id FROM userdata WHERE id = ? FOR UPDATE加锁，确保该id未被其他操作占用；
3. 若锁成功且unordered_map中无该id，则使用；若锁失败或内存中存在，则丢弃该id，取下一个；
4. vector耗尽后重新查询批量空闲id。

一致性关键注意点 #

• 无论采用哪种方案，使用空闲id前必须同时检查SQL表和内存unordered_map：只有两者均无该id时，才能安全使用；
• 内存数据延迟保存时，需在同步到SQL时处理冲突：若发现SQL中已存在内存记录的id，则调整内存数据，改用其他空闲id；
• 若业务允许，可考虑将id类型升级为64位INT，从根源上缓解溢出问题。

内容的提问来源于stack exchange，提问作者Honey55