SQL硬编码条件时用索引，关联小表时索引失效的问题求助

问题场景 #

• 大表t的name列已创建索引，硬编码IN条件查询时能正常使用索引，查询速度极快：

SELECT t.*
FROM table t
WHERE date = xyz
AND name in ('hardcoded_name_1','hardcoded_name_2', ...)
AND status = 1;

• 关联极小表t2查询时，t表未使用索引，执行计划显示对t做全表扫描，查询速度骤降：

SELECT t.*
FROM table t
JOIN (SELECT name2 from t2) on name = name2
WHERE date = xyz
AND status = 1;

• 执行计划如下：

SELECT STATEMENT ALL_ROWS 
  4 NESTED LOOPS 
    2 PARTITION RANGE ALL 
      1 TABLE ACCESS STORAGE FULL TABLE t.name 
  3 INDEX UNIQUE SCAN INDEX (UNIQUE) t2.name 

解决方法 #

• 强制使用索引：在查询中显式指定t表的name索引，跳过优化器的错误执行计划选择：

SELECT t.*
FROM table t /*+ INDEX(t idx_t_name) */
JOIN (SELECT name2 from t2) on t.name = name2
WHERE t.date = xyz
AND t.status = 1;

（把idx_t_name替换成你实际的索引名称）

• 改用EXISTS子查询：用EXISTS替代JOIN，让优化器更易识别小表t2的数据集，优先用t的索引匹配：

SELECT t.*
FROM table t
WHERE t.date = xyz
AND t.status = 1
AND EXISTS (
    SELECT 1 FROM t2 
    WHERE t2.name2 = t.name
);

• 手动转硬编码IN列表：因为t2数据量极小，先查询出t2的name2值，直接拼成硬编码的IN条件，复用原来高效的查询逻辑。

• 更新表统计信息：如果数据库统计信息过时，会导致优化器判断失误。更新t和t2的统计信息，让优化器准确评估数据量：

-- Oracle环境
EXEC DBMS_STATS.GATHER_TABLE_STATS('你的 schema 名', 't');
EXEC DBMS_STATS.GATHER_TABLE_STATS('你的 schema 名', 't2');

-- MySQL环境
ANALYZE TABLE t, t2;

• 指定驱动表顺序：部分数据库优化器可能默认用大表驱动小表，手动指定t2作为驱动表，比如Oracle用LEADING提示：

SELECT t.*
FROM table t
JOIN (SELECT name2 from t2) t_sub /*+ LEADING(t_sub t) */
on t.name = t_sub.name2
WHERE t.date = xyz
AND t.status = 1;

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