如何为商品表设置合适的索引？过滤/非聚集索引咨询

一、当前场景所需索引数量及最优设计 #

针对你的三个查询场景，结合性能与写入开销的平衡，建议创建3个针对性的部分过滤索引，同时可以替换掉你已创建的I_PRODUCT_ACTIVE_NONC索引（它无法高效覆盖任何一个查询场景）：

1. 针对“查询活跃商品”场景
   该查询仅过滤status=1，需要返回name, price, categoryid，最优索引是包含所有返回字段的部分覆盖索引：

   CREATE INDEX idx_active_products_cover ON products() 
   INCLUDE (name, price, categoryid) 
   WHERE status = 1;
   

   这个索引仅存储活跃商品的目标字段，查询时无需回表，直接从索引获取数据，性能最优。

2. 针对“按价格过滤活跃商品”场景
   查询条件为price > 100 AND status=1，返回name, price，需以price为索引键（范围查询字段），包含name的部分索引：

   CREATE INDEX idx_active_price_filter ON products(price) 
   INCLUDE (name) 
   WHERE status = 1;
   

   索引仅包含活跃商品的price和name，查询时通过price范围快速定位数据，避免回表。

3. 针对“按价格+分类过滤活跃商品”场景
   查询条件为price > 100 AND categoryid=4 AND status=1，返回name, price, categoryid，需将等值条件categoryid放在索引键前列，范围条件price在后，包含name的部分索引：

   CREATE INDEX idx_active_cat_price_filter ON products(categoryid, price) 
   INCLUDE (name) 
   WHERE status = 1;
   

   查询时先通过categoryid=4快速缩小范围，再在该范围内筛选price>100的数据，直接从索引获取所有返回字段，无需回表。

二、多过滤索引的合理性 #

带WHERE status=1的部分过滤索引是完全合理的，核心优势在于：

• 索引体积更小：仅存储活跃商品数据，减少磁盘占用与扫描开销；
• 查询性能更高：避免扫描非活跃商品的无效数据；
• 写入开销更低：相比全表索引，仅在活跃商品变更时维护索引，对写入性能的影响更小。

但需注意避免过度创建：仅针对高频查询场景创建索引，若某些查询频率极低，可考虑复用现有索引（例如低频次的价格+分类查询，若已存在idx_active_price_filter，虽性能略差，但无需额外建索引），平衡查询性能与写入维护成本。

三、颜色、尺寸等字段的索引处理原则 #

颜色、尺寸等字段的索引设计需绑定具体查询场景，核心原则如下：

1. 优先结合status=1创建部分索引：你的查询基本针对活跃商品，部分索引能大幅降低索引体积；
2. 按“等值条件在前，范围条件在后”排列索引键：
   • 若查询是“活跃商品+颜色=红色”，创建：

     CREATE INDEX idx_active_color ON products(color) 
     INCLUDE (返回字段) 
     WHERE status = 1;
     

   • 若查询是“活跃商品+颜色=红色+尺寸=L+价格>200”，创建：

     CREATE INDEX idx_active_color_size_price ON products(color, size, price) 
     INCLUDE (返回字段) 
     WHERE status = 1;
     

3. 优先做覆盖索引：让索引包含查询所需的所有返回字段，避免回表操作；
4. 避免盲目单字段索引：若没有单独针对颜色/尺寸的高频查询，无需为每个字段单独建索引，而是针对多条件组合查询创建联合索引。

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