如何优化给定SQL查询的性能？附执行计划、索引及表数据信息

原查询信息 #

原SQL语句 #

SELECT COUNT(1)
FROM ACT_CARD_BANK
WHERE CARD_NO IN (SELECT CARD_NO
                  FROM XSHTEST.XSH_CARD_BANK
                  WHERE BIN_NO IN ('731018', '731023', '731024', '731025', '731026', '731027')
                    AND STATUS = '06')
  AND STATUS = '04';

表规模 #

• ACT_CARD_BANK：399,187,646行
• XSH_CARD_BANK：228,751,942行

统计信息收集情况 #

昨日已执行以下脚本重新收集XSH_CARD_BANK的统计信息：

exec dbms_stats.gather_table_stats(ownname => '$owner',tabname => 'XSH_CARD_BANK',estimate_percent => 0.1,method_opt=> 'for all indexed columns'); 

优化措施 #

1. 改写子查询为JOIN（规避IN子查询的性能瓶颈） #

大数据量下IN子查询易引发低效执行计划，改用JOIN结合DISTINCT（避免CARD_NO重复导致COUNT结果偏差）：

SELECT COUNT(DISTINCT acb.CARD_NO)
FROM ACT_CARD_BANK acb
JOIN XSHTEST.XSH_CARD_BANK xcb
  ON acb.CARD_NO = xcb.CARD_NO
WHERE xcb.BIN_NO IN ('731018', '731023', '731024', '731025', '731026', '731027')
  AND xcb.STATUS = '06'
  AND acb.STATUS = '04';

若CARD_NO在两张表中均为唯一键，可直接使用COUNT(1)替代COUNT(DISTINCT acb.CARD_NO)。

2. 优化索引策略 #

• 针对XSH_CARD_BANK：创建覆盖复合索引，让数据库直接通过索引过滤并获取所需数据，无需回表：

CREATE INDEX IDX_XSH_CB_BIN_STATUS_CARD ON XSHTEST.XSH_CARD_BANK (BIN_NO, STATUS, CARD_NO);

• 针对ACT_CARD_BANK：创建复合索引快速过滤STATUS='04'的记录，同时匹配CARD_NO：

CREATE INDEX IDX_ACT_CB_STATUS_CARD ON ACT_CARD_BANK (STATUS, CARD_NO);

3. 完善统计信息收集 #

仅收集XSH_CARD_BANK的统计信息不够，建议同步收集ACT_CARD_BANK的统计信息，并提高采样百分比（如1%）以获得更精准的统计数据，帮助生成最优执行计划：

exec dbms_stats.gather_table_stats(ownname => '$owner',tabname => 'ACT_CARD_BANK',estimate_percent => 1,method_opt=> 'for all indexed columns'); 

4. 用EXISTS替代IN #

EXISTS采用半连接逻辑，找到匹配项即停止扫描，在大数据量场景下可能带来更好的性能：

SELECT COUNT(1)
FROM ACT_CARD_BANK acb
WHERE acb.STATUS = '04'
  AND EXISTS (SELECT 1
              FROM XSHTEST.XSH_CARD_BANK xcb
              WHERE xcb.CARD_NO = acb.CARD_NO
                AND xcb.BIN_NO IN ('731018', '731023', '731024', '731025', '731026', '731027')
                AND xcb.STATUS = '06');

5. 核查执行计划扫描类型 #

检查执行计划是否存在全表扫描，若有则结合上述索引优化方案消除全表扫描，优先使用索引扫描提升效率。

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