SQLite3表使用类随机主键替代自增主键避免数据泄露及高效查询方案咨询

直接选择带时间序的类随机ID是最优解，既满足对外不暴露业务量级、无法被枚举的要求，也不会出现传统完全随机ID的性能问题：

• 优先选用UUIDv7：这是IETF标准化的带时间戳的UUID格式，前48位为毫秒级时间戳，后段为随机数，整体随时间单调递增，完全无连续规律。SQLite 3.37.0及以上版本支持内置生成，表结构可直接定义为：

  CREATE TABLE mytable (
    id BLOB PRIMARY KEY DEFAULT (uuid7()),
    -- 其他业务字段
  );
  

  存为16字节二进制格式比字符串存储省60%以上空间，主键查询效率和自增ID基本持平。低版本SQLite可在服务端生成UUIDv7二进制串后再写入库中。
• 也可自定义64位类雪花ID：结构可设置为「40位时间戳+10位服务实例标识+14位自增序列号」，总长度和SQLite默认的INTEGER主键一致，既保证写入有序，也完全满足数百万条数据的量级需求。

完全无规律的随机主键（如UUIDv4）会因为SQLite的聚簇索引特性，导致插入时频繁出现页分裂，写入性能下降30%~50%，同时数据离散会降低缓存命中率，影响查询效率。成熟的优化方案如下：

• 首选方案是更换为前文提到的有序随机ID，保证主键整体单调递增，插入操作始终追加到索引末尾，完全规避页分裂问题，性能和自增ID差异小于5%，是目前业界的通用解法，无额外接入成本。
• 若必须使用完全随机主键，可通过SQLite参数优化降低性能影响：
  • 调大页大小：执行PRAGMA page_size = 8192;（需在建库前设置生效），降低页分裂频率
  • 开启WAL日志模式：执行PRAGMA journal_mode = WAL;，可将随机写入性能提升2倍以上
  • 数百万条数据量级下，只要是主键等值查询，性能依然可以稳定在毫秒级，影响可以忽略。

针对你遇到的场景，除了上述有序随机主键方案外，也可根据现有业务结构选择更适配的方案，完全规避之前遇到的全表扫描、空间浪费问题：

• 方案1（零侵入现有表结构）：自增ID偏移混淆
  服务端存储固定的私有偏移量（如仅服务端可知的8位随机数OFFSET = 12093847），下发给客户端的对外ID为base62(id + OFFSET)，Base62编码为0-9、大小写字母组成的字符串，完全无连续规律，无法反推原始自增ID。客户端回传后，服务端先做Base62解码再减去偏移量即可拿到原始ID，直接走主键索引查询，无额外开销，也不需要新增索引，空间利用率100%。
• 方案2（最优性能）：有序随机ID作为主键
  直接将UUIDv7/类雪花ID作为主键存储，下发给客户端的就是该ID明文，不需要做任何加密转换，客户端回传后直接走聚簇索引查询，效率最高，也完全没有泄露业务量级、被枚举的风险。

避坑提示：不要采用自增ID加密后下发、回传后解密匹配的方案，不管是全表扫描还是新增加密字段索引，都会带来不必要的性能或空间损耗，上述两个方案均可以完全规避该问题。

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