Oracle含大量空值列的映射表索引性能影响及空值弊端问询

表结构及示例数据：

table: mapping
----------------------------------------------------------
| Id | ref_id_1 | ref_id_2 | mapping_id_1 | mapping_id_2 |
----------------------------------------------------------
  1      34         45         null            100
  1      34         45         null            101
  1      34         46         null            100
  1      34         45         100             null
  1      34         45         101             null
  1      34         46         101             null

针对你提出的创建两个复合索引的方案，以及这类含大量空值列的场景，具体影响和弊端如下：

一、两个复合索引的性能影响 #

• 存储空间冗余与维护开销增加：由于mapping_id_1和mapping_id_2互斥为空，每个索引都会包含一半左右带空值的条目。Oracle B树索引默认存储空值，这意味着两个索引会重复存储ref_id_1/ref_id_2的组合，仅对应不同的空值列，导致索引磁盘占用接近翻倍。同时，插入、更新、删除数据时需要同时维护两个索引，IO开销直接增加，拖慢数据变更的速度。
• 索引选择与执行计划风险：如果查询明确过滤非空列（比如WHERE ref_id_1=34 AND ref_id_2=45 AND mapping_id_1 IS NOT NULL），Oracle能正确匹配对应索引，但如果查询仅指定ref_id_1和ref_id_2，优化器可能在两个索引间犹豫，甚至选错索引，导致执行效率下降。若需同时关联两张外键表，可能需要扫描两个索引或回表查询，进一步增加IO次数。
• 空值导致索引扫描效率降低：Oracle B树索引支持空值，但大量空值会降低叶子节点的数据密度，扫描索引时需要遍历更多节点，单次查询的IO次数增加。数据量越大，这种效率损耗越明显。

二、含大量空值列场景的弊端 #

• 数据冗余与维护复杂度提升：原本可拆分的两张表数据挤在一张表中，每条记录都存在一个空值列，浪费存储资源。同时，为保证mapping_id_1和mapping_id_2互斥为空的规则，可能需要额外添加检查约束或触发器，增加了数据维护的复杂度和出错概率。
• 查询逻辑复杂化：编写SQL时必须频繁处理空值判断，比如关联外键表时需用LEFT JOIN或CASE WHEN区分不同的mapping列，不仅增加SQL复杂度，还容易因疏忽导致逻辑错误（比如漏判空值引发关联失败）。
• 统计信息失真影响执行计划：Oracle收集统计信息时，大量空值会干扰列基数的估算，优化器可能基于错误的基数生成糟糕的执行计划。例如，优化器可能误判mapping_id_1基数很低，实际非空值基数却很高，最终选错连接方式或索引。
• 索引优化空间受限：由于两列互斥为空，无法构建包含两个mapping列的有效复合索引（只会加剧冗余），也难以通过覆盖索引减少回表操作。若要针对特定查询优化，只能创建更多专用索引，进一步增加索引数量和维护成本。

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