Oracle查询索引选择问题：第二条查询未使用复合索引payment_id_idx原因咨询

Oracle优化器采用基于成本的计算规则（CBO）选择执行计划，会统计索引结构、数据分布等信息，计算不同扫描路径的成本，最终选择成本最低的方案，两个查询的索引选择差异核心原因如下：

为什么Query2选择主键索引 #

• AUDIT_ID是主键，对应索引为唯一B树索引，针对AUDIT_ID = 'xxx'的等值查询，唯一索引最多返回1条匹配记录，扫描成本极低：仅需要1次索引定位拿到对应ROWID，再1次回表即可获取整行数据，回表后仅需判断1行记录的PAYMENT_IDENTIFICATION_ID是否符合条件，该过滤成本几乎可以忽略。
• 对比走payment_id_idx复合索引的成本：复合索引的条目包含PAYMENT_IDENTIFICATION_ID、AUDIT_ID两个字段加ROWID，单条索引条目大小远大于主键索引，相同数量的索引条目需要更多的索引块存储，扫描索引的IO成本更高。且因为查询的是*需要获取不在索引中的ACCOUNT_NUMBER字段，同样需要1次回表，综合成本比走主键索引更高，所以优化器选择了主键索引。

为什么Query1选择复合索引 #

• Query1的AUDIT_ID是<> 不等值查询，无法利用主键索引的唯一性快速定位，走主键索引需要扫描全量索引条目再过滤，成本极高。
• 复合索引payment_id_idx的前缀是PAYMENT_IDENTIFICATION_ID，可以直接定位到所有PAYMENT_IDENTIFICATION_ID = 'ID124'的索引条目，再在索引内过滤AUDIT_ID <>的条件，仅需扫描小范围的索引块，后续回表的成本也很低，所以优化器选择了该复合索引。

补充验证方法 #

你可以通过添加hint强制Query2走复合索引，对比两者的执行成本：

SELECT /*+ INDEX(AUDIT_LOG payment_id_idx) */ * 
FROM AUDIT_LOG
WHERE PAYMENT_IDENTIFICATION_ID = 'ID124'
AND AUDIT_ID='ecfdc2c3-87eb-48c9-b53c';

对比执行计划的COST列，能明显看到走复合索引的成本高于走主键索引。如果你的查询只需要返回索引内包含的AUDIT_ID、PAYMENT_IDENTIFICATION_ID字段，不需要回表获取ACCOUNT_NUMBER，那么优化器会优先选择复合索引，不需要回表的成本低于主键索引。

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