Java多客户端场景下如何实现人员限制名单的优雅过滤

原方案核心问题 #

最初设想的NOT IN子查询过滤方案存在两个典型认知误区：

• 所谓「IN子句参数数量上限」仅出现在手动把ID列表硬拼接进SQL的场景，只要不用硬拼参数的写法，这个问题完全可以规避
• 随着限制表数据量增长出现的性能下滑，本质是NOT IN语法本身的查询优化器缺陷，以及索引设计不合理导致的，不是「存客户端和限制号码映射关系」这个思路本身错了

落地方案 #

存储层设计 #

直接新建独立的关联映射表client_person_restrictions即可，仅保留3个核心字段：

• client_id：客户端唯一标识，建普通索引
• identity_number：被限制的人员身份号码
• updated_at：规则更新时间，用于缓存失效判断

给(client_id, identity_number)建联合主键，天然支持数据去重，不管接口重复传入多少次相同身份号码，都不会产生脏数据。写入逻辑直接用数据库原生upsert能力即可，不需要提前做数据存在性校验。

写入接口逻辑 #

POST /persons/restrictions接口不需要做复杂的状态判断，逻辑固定为两步：

1. 从请求鉴权上下文提取当前操作的client_id，禁止前端直接传客户端ID，避免越权
2. 当入参restricted = true时，批量写入传入的identityNumbers和当前client_id的映射关系（已存在的记录自动跳过）；当入参restricted = false时，批量删除当前client_id下、身份号码在传入列表中的映射记录

批量操作单次处理1000条以内的入参完全不会有性能瓶颈，用数据库批量写入/删除能力，接口响应可以稳定在百毫秒级。

查询层改造（彻底解决性能问题） #

完全抛弃NOT IN写法，改用LEFT JOIN加空值判断的方式做过滤，SQL示例：

SELECT p.*
FROM persons p
LEFT JOIN client_person_restrictions r
  ON p.identity_number = r.identity_number
  AND r.client_id = ?
WHERE r.identity_number IS NULL

这个写法的优势非常明显：

• 完全不会触发IN子句的参数数量上限，不需要把限制号码列表手动拼入SQL
• 依托联合主键的索引能力，哪怕限制表总数据量达到千万级，数据库也会先通过client_id索引快速定位到当前客户端的所有限制规则，再做关联匹配，不会出现全表扫描，查询性能稳定在毫秒级。

极端大流量场景优化 #

如果遇到单客户端配置的限制号码超过10万条、且查询QPS极高的场景，可以加一层轻量缓存进一步提效：

• 首次查询某客户端的人员列表时，将该客户端下所有被限制的身份号码存入Redis的Set结构，key规则为restriction:client:{clientId}
• 每次调用限制规则更新接口时，直接删除对应客户端的缓存key，下次查询自动拉取最新库表数据重建缓存即可，不需要做复杂的缓存增量更新，维护成本极低
• 查询时根据缓存大小选择执行路径：限制号码数小于1000条时，直接查询全量人员列表后在内存做过滤，性能高于数据库关联查询；限制号码数大于1000条时，继续走上述LEFT JOIN的数据库查询逻辑，避免内存占用过高。

避坑提醒 #

以下方案看起来取巧，实际维护成本极高，不建议使用：

• 不要在persons表加JSON类型字段存储限制该人员的客户端列表，更新时会出现严重的行锁冲突，查询也无法走索引，数据量稍大就会出现性能雪崩
• 不要用布隆过滤器、位图等结构存储限制规则，解除限制的逻辑复杂度会指数级上升，极易出现数据不一致
• 不要手动把限制号码拼接成IN子句传入SQL，不仅有参数长度限制，还会导致SQL预编译缓存无法命中，同时存在SQL注入风险

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