PAYATHON 2026

用于 zipcode 查询时，Range 与 list 哪种设计更好？

支付小周

结论

优先采用直接保存 zipcode 的明细表。不同 carrier 的服务列表不要合并，COD 和 prepaid 也不必拆成两张表。

每条记录表示“某个 carrier 在某种 payment method 下支持某个 zipcode”。几千甚至几万条记录对常规关系型数据库而言规模很小，建立复合索引后，就能快速判断是否支持配送。

只有当承运商明确按连续区间提供服务，而且区间长期稳定时，才值得采用 Range 设计。仅仅为了把 4,139 条数据压缩到 1,593 条，不值得增加查询和维护的复杂度。

推荐的表结构

zipcode 是标识符，不是用于计算的数值。即使当前国家的邮编都由数字组成，也建议使用 varchar 保存，避免丢失前导零，同时兼容未来可能接入的其他国家或地区。

CREATE TABLE carrier (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE carrier_serviceability (
    carrier_id BIGINT NOT NULL,
    country_code CHAR(2) NOT NULL,
    zipcode VARCHAR(16) NOT NULL,
    payment_method VARCHAR(16) NOT NULL,

    PRIMARY KEY (
        carrier_id,
        country_code,
        zipcode,
        payment_method
    ),

    FOREIGN KEY (carrier_id) REFERENCES carrier(id),

    CHECK (payment_method IN ('COD', 'PREPAID'))
);

主要查询通常以 zipcode 为入口，因此还需要增加一个查询索引：

CREATE INDEX idx_serviceability_lookup
ON carrier_serviceability (
    country_code,
    zipcode,
    payment_method,
    carrier_id
);

主键可以防止同一 carrier 重复导入相同的服务记录，额外索引用于快速判断“这个 zipcode 能否使用这种付款方式”。

如果以后要增加信用卡、钱包或分期等付款方式，可以将 payment_method 拆成独立的字典表，不再依赖固定的 CHECK 列表。

查询是否可以配送

如果只需要得到“可以”或“不可以”，使用 EXISTS：

SELECT EXISTS (
    SELECT 1
    FROM carrier_serviceability
    WHERE country_code = 'IN'
      AND zipcode = '560001'
      AND payment_method = 'COD'
) AS can_deliver;

数据库找到第一条匹配记录后就会停止搜索，不必读取所有 carrier。

如果数据库不能直接返回布尔值，也可以使用计数：

SELECT CASE
    WHEN COUNT(*) > 0 THEN 1
    ELSE 0
END AS can_deliver
FROM carrier_serviceability
WHERE country_code = 'IN'
  AND zipcode = '560001'
  AND payment_method = 'COD';

不过，只判断记录是否存在时，通常应优先使用 EXISTS。

查询具体由哪些 carrier 配送

即使当前业务不关心具体的承运商，也应该保留 carrier_id。以后比较运费、选择配送时效、切换失败订单或追踪订单时，都需要这些信息。

SELECT c.id, c.name
FROM carrier_serviceability AS s
JOIN carrier AS c
  ON c.id = s.carrier_id
WHERE s.country_code = 'IN'
  AND s.zipcode = '560001'
  AND s.payment_method = 'PREPAID';

如果预先合并所有 carrier 的 zipcode，虽然还能判断是否可以配送，但无法选择实际承运商，也无法安全地删除或更新某一家 carrier 的服务范围。

为什么不建议优先使用 Range

Range 设计可以减少行数，但会带来一些实际问题：

  • zipcode 本质上是编码，数值相邻并不代表它们属于同一个服务区域。
  • carrier 新增或取消一个 zipcode 时，可能需要拆分或合并多个区间。
  • 多个区间可能发生重叠，需要额外处理去重和约束。
  • start <= zipcode AND end >= zipcode 包含两个范围条件，普通复合 B-tree 索引未必能达到等值查询的效率。
  • 导入新清单后，还要重新生成正确的区间。
  • 排查问题时，明细记录比压缩后的区间更直观。

例如，Range 查询通常只能写成：

SELECT EXISTS (
    SELECT 1
    FROM carrier_serviceability_range
    WHERE carrier_id = 10
      AND payment_method = 'COD'
      AND zipcode_start <= 560001
      AND zipcode_end >= 560001
) AS can_deliver;

这种设计并非不可用，但节省几千行数据通常不足以抵消额外的维护成本。现代数据库处理数千条带索引的明细记录没有压力，不能只根据“压缩后减少了多少行”判断设计是否合理。

什么时候可以使用 Range

满足以下条件时，可以考虑单独保存范围：

  • carrier 提供的原始数据本身就是范围，而不是离散清单；
  • zipcode 长度固定，并且可以可靠地转换为可比较的数值；
  • 区间数量远少于明细数量；
  • 服务范围不经常变更；
  • 已经使用实际数据库验证过范围查询的执行计划。

此时可以建立统一的范围表：

CREATE TABLE carrier_serviceability_range (
    id BIGINT PRIMARY KEY,
    carrier_id BIGINT NOT NULL,
    country_code CHAR(2) NOT NULL,
    payment_method VARCHAR(16) NOT NULL,
    zipcode_start VARCHAR(16) NOT NULL,
    zipcode_end VARCHAR(16) NOT NULL,

    FOREIGN KEY (carrier_id) REFERENCES carrier(id),

    CHECK (zipcode_start <= zipcode_end)
);

使用字符串比较范围时，必须保证 zipcode 的长度固定、格式统一，否则字典序可能不符合预期。如果使用 PostgreSQL，而且邮编能够安全地映射为整数，还可以评估原生 range 类型和 GiST 索引。是否更快，需要结合真实数据和查询计划确认。

数据导入和更新建议

carrier 提供新清单后，最好先将数据导入临时表。完成格式校验和去重，再通过事务替换该 carrier 对应的数据：

BEGIN;

DELETE FROM carrier_serviceability
WHERE carrier_id = 10;

INSERT INTO carrier_serviceability (
    carrier_id,
    country_code,
    zipcode,
    payment_method
)
SELECT
    carrier_id,
    country_code,
    zipcode,
    payment_method
FROM carrier_serviceability_staging
WHERE carrier_id = 10;

COMMIT;

正式导入前，还要统一处理空格、大小写、zipcode 长度和非法字符。如果更新期间不能出现短暂的数据空档，可以采用版本号、批次号，或者先插入新版本再切换。

总体而言，“统一明细表 + carrier_id + payment_method + 复合索引”是更稳妥的方案。查询简单，更新直观，也保留了以后选择 carrier 所需的信息。只有真实数据规模和性能测试表明明细表已经成为瓶颈时，才有必要引入 Range。

备注：内容仅供参考。