细分部分索引维护成本是否高于通用全量索引？两类方案有何取舍

你的判断是正确的，绝大多数场景下两个定向部分索引的维护开销要低于单个通用全量索引。

方案一：双定向部分索引的优劣势 #

优势 #

• 索引体积极小：仅存储满足WHERE needs_advice = 'financial' AND state = 'help'或者WHERE needs_advice = 'relationship' AND state = 'help'的行数据，只要符合这两个条件的行在全表占比不高，两个索引的总大小会远小于单个全量索引，内存命中率更高，对应场景的查询速度更快。
• 维护开销更低：只有当插入/更新/删除的行满足上述两个过滤条件中的任意一个时，才会触发对应索引的更新。其余不满足条件的行（比如state为非help值、needs_advice为其他类型）的变更完全不需要维护这两个索引，写入压力小很多。
• 查询性能更优：索引内没有任何冗余数据，查询对应场景时不需要过滤无关行，直接读取索引就能拿到目标结果。

劣势 #

• 灵活性差：仅能支撑你当前的两类固定查询场景，如果后续新增其他类型建议的查询需求，或者查询条件调整（比如要查state为pending的财务建议用户），现有索引完全无法生效，需要新增额外索引。
• 索引数量随查询场景增加而增长，如果后续新增多类查询场景，索引管理成本会上升。

方案二：单个通用全量索引的优劣势 #

优势 #

• 灵活性高：只要查询条件包含state和needs_advice的组合，不管字段值是什么，都可以用到这个索引，后续新增同类查询场景不需要额外建索引。
• 索引数量少，元数据管理成本低。

劣势 #

• 索引体积大：需要存储全表所有行的state和needs_advice字段值，包含大量你当前查询场景不需要的冗余数据，索引总大小远大于两个部分索引之和，内存占用高，查询时需要扫描更多索引页，性能不如部分索引。
• 维护开销高：不管行的state和needs_advice是什么值，只要这两个字段发生变更、或者有行插入删除，都需要更新这个全量索引，写入压力远高于部分索引方案。

关于维护开销判断的补充说明 #

只要符合部分索引过滤条件的行在全表占比低于50%，两个部分索引的总维护开销一定低于单个全量索引。就算极端场景下符合条件的行占比极高，两个部分索引的总维护开销最多和全量索引持平，不会更高。

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