高读量场景下含复合主键的SQL表设计方案选型咨询

我需要创建一个仅包含customerid和zipcode两列的SQL表，两列组合可确保行的唯一性。该表将存储约20万条数据，读操作频繁且每日仅执行一次写操作，核心查询需求为根据多个zipcode获取对应的customerid（示例语句：Select customerid from dbo.customerzipcode where zipcode in (<multiple zipcodes>)）。现有以下三种设计方案，想咨询哪种方案更高效：

方案1 #

• 创建包含customerid和zipcode两列的表
• 为这两列创建复合主键

方案2 #

• 创建包含id、customerid和zipcode三列的表
• id设为自增标识列并作为主键
• 为customerid和zipcode创建唯一约束

方案3 #

• 创建包含id、customerid和zipcode三列的表
• 为zipcode单独创建非聚集索引

方案对比与结论 #

从核心查询需求（频繁根据多个zipcode查询customerid）和数据特征（20万条、读多写少）来看，方案1是最优选择，理由如下：

1. 索引效率最大化
   复合主键默认会创建聚集索引（SQL Server默认规则），若将zipcode放在复合主键的前列（即(zipcode, customerid)），查询时直接通过聚集索引就能获取所需的customerid，无需回表操作，完全匹配你的查询场景，效率最高。如果主键顺序反过来，zipcode的查询效率会大幅下降，所以主键列顺序一定要优先放查询条件列。

2. 存储空间最优
   方案1仅保留业务必需的两列，相比方案2、3省去了冗余的id列，20万条数据能减少IO开销，进一步提升查询速度。

3. 唯一性约束直接生效
   复合主键本身就强制了customerid和zipcode的组合唯一性，无需额外创建唯一约束，表结构更简洁。

其他方案的明显不足：

• 方案2：自增id作为聚集主键后，customerid+zipcode的唯一约束会生成非聚集索引，查询zipcode时需要先通过非聚集索引定位id，再回表获取customerid，多了一次IO操作，效率低于方案1，还额外占用了id列的存储空间。
• 方案3：仅为zipcode创建非聚集索引，同样存在回表问题（除非创建包含customerid的覆盖索引），且未强制customerid和zipcode的组合唯一性，无法保证数据唯一性要求。

内容的提问来源于stack exchange，提问作者Karthick Trichy Chandrasekaran