如何优化该SQL查询？为何建索引后优化器仍走全表扫描

• 负向条件索引利用率低：你在indcont_key_1字段上用的是<>不等值判断，B+树索引对<>、NOT IN、NOT EXISTS这类负向条件的过滤支持很差，除非indcont_key_1 = 'DN'的记录占全表80%以上，否则数据库通过索引筛选后回表取数的成本，会远高于直接全表扫描，优化器会主动放弃该索引。
• 现有索引不满足覆盖查询要求：你创建的索引仅包含ind_no、indcont_key_1两个字段，但查询需要从tmp.req_index_cont_t表取ind_no、indcont_key_1、delete_date三个字段，走该索引必须经过回表操作，随机IO成本过高时优化器不会选择索引。
• 关联表无匹配索引拖累执行计划：两表关联时如果被驱动表tmp.req_index_t没有针对过滤条件、关联字段建适配索引，优化器会重新估算全路径执行成本，大概率会选择全表扫描两张表做hash join的执行路径。
• 冗余语法干扰成本估算：SQL中同时写了distinct和覆盖所有返回字段的group by，两者语义完全重复，会让优化器额外增加去重计算逻辑，干扰成本估算准确性。
• 表数据量过小：如果tmp.req_index_cont_t总数据量低于1万行，全表扫描的顺序IO效率远高于走二级索引+回表的随机IO效率，此时选全表扫描是最优执行策略，不需要调整。

• 调整tmp.req_index_cont_t的索引为覆盖索引，按(indcont_key_1, ind_no, delete_date)顺序建索引：等值/过滤条件列放最前，关联列放中间，查询需要返回的字段放最后，走索引时不需要回表就能拿到所有需要的字段，执行成本会大幅降低。如果indcont_key_1 = 'DN'的记录占比很低，可将<>'DN'的负向条件改写为IN (所有非DN的合法值)，转为正向等值查询后索引利用率会明显提升。
• 给关联表tmp.req_index_t建适配覆盖索引，按(ind_state, ind_no, item_no, item_type, delete_date)顺序建索引：过滤字段ind_state放最前，关联字段ind_no放第二位，后续跟上查询需要返回的其他字段，两表关联时都不需要回表，关联成本会降到最低。
• 清理冗余语法：当前SQL的group by已经覆盖所有返回字段，本身就会实现去重效果，直接删掉多余的distinct即可，减少优化器不必要的计算开销。
• 如果是统计信息过时导致优化器误判，手动更新两张表的统计信息后再用explain查看执行计划；如果确认索引执行效率更高但优化器未选择，可临时加索引hint强制走索引验证效果。

-- 重建req_index_cont_t表的覆盖索引
CREATE INDEX tmp.REQ_CONT_T_IDX 
ON tmp.req_index_cont_t (indcont_key_1, ind_no, delete_date);

-- 给req_index_t表建适配的覆盖索引
CREATE INDEX tmp.REQ_INDEX_T_IDX
ON tmp.req_index_t (ind_state, ind_no, item_no, item_type, delete_date);

-- 优化后的查询SQL
select
    rit.item_no as item_no,
    rit.item_type as item_type,    
    rit.ind_no as ind_no,
    rit.delete_date as req_ind_delete_date,
    indcnt.delete_date as req_ind_cont_delete_date
from
    tmp.req_index_cont_t indcnt
inner join tmp.req_index_t rit
    on rit.ind_no = indcnt.ind_no
where
    indcnt.indcont_key_1 <> 'DN'
    and rit.ind_state = 'Approved'
group by    
    rit.item_no,
    rit.item_type,
    rit.ind_no,
    indcnt.delete_date,
    rit.delete_date;

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