多索引vs含非末尾范围列的单复合索引：月2000万订单选型咨询

优先选第一种方案：创建ProductId_ItemSizeId_DateOrdered_UserId单复合索引，原因如下：

• 覆盖两种查询场景，贴合索引最左匹配规则
  这个索引的前缀列ProductId, ItemSizeId, DateOrdered刚好对应必选查询条件，不管加不加UserId筛选都能高效利用：

  • 不带UserId时，用前三个列就能快速定位时间范围内的目标记录；
  • 带UserId时，四个列全匹配，能进一步缩小结果集，查询效率拉满。

• 避免冗余索引，降低存储与写入开销
  第二种方案的两个索引会带来双重负担：

  • 月增量2000万条的表，额外的索引会占用大量存储空间，长期下来存储成本会显著上升；
  • 每次插入、更新订单时，数据库要同时维护两个索引，直接拖慢写入性能——高增量场景下，写入性能的损耗是不可忽视的。
    而且第二个索引ProductId_ItemSizeId_UserId_DateOrdered在不带UserId的查询里基本没用，因为最左匹配到UserId就断了，没法高效利用DateOrdered的范围筛选，数据库大概率还是会走第一个索引，等于白建了。

额外提两个优化点：

• 如果查询不需要*所有列，尽量只查需要的字段，或者把索引做成覆盖索引（把需要的列加到索引的包含列里），避免回表查数据，能再提一波性能；
• 高增量表容易产生索引碎片，定期重建或重组索引，保证查询效率稳定。

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