IS NULL 的性能优化有关问题

IS NULL 的性能优化问题
select distinct A.id from A left outer join B on B.A_id = A.id
  where B.A_id is null

如题，以上sql在大数据量的时候（A表7万多条，B表4万多条，inner join匹配条数为3万余条）在DB2中执行的非常缓慢，平均时间会在上1000S以上...
网上查了一下，似乎原因在于DB2的查询优化器在null值的行数很大时会自动选择全表扫描而不是索引扫描?
如此一来如何优化？

PS:问题说白了就是在于取A与B差集的方法中性能最强的是哪种，当前我做过一下改动：
   select distinct A.id from A left outer join B on B.A_id = A.id
  where A.id not in (select A.id from org inner join B on B.A_id = A.id)
即用NOT IN 代替 IS NULL...
  是否会有更优化的方案？在线等大神解答
------解决思路----------------------
建立1个冗余字段保存COALESCE(A_id,0)，在此字段上建立索引试试
------解决思路----------------------
(B.A_id IS NULL OR (NOT (B.some_attribue <> 0 OR B.colOther IS NULL)
AND D.first_column IS NOT NULL AND NOT (D.second_column <> 0 OR D.second_column IS NULL) AND (D.third_colum <> 0 OR D.third_colum IS NULL)))

1 B.A_id IS NULL
2 B.some_attribue <> 0
3 B.colOther IS NULL
4 D.first_column IS NOT NULL
5 D.second_column <> 0
6 D.second_column IS NULL
7 D.third_colum <> 0
8 D.third_colum IS NULL

(1 OR (NOT (2 OR 3)AND 4 AND NOT (5 OR 6) AND (7 OR 8)))

(1 OR (NOT 2 AND NOT 3 AND 4 AND NOT 5 AND NOT 6 AND (7 OR 8)))

1

UNION

NOT 2 AND NOT 3 AND 4 AND NOT 5 AND NOT 6 AND 7

UNION

NOT 2 AND NOT 3 AND 4 AND NOT 5 AND NOT 6 AND  8

B.A_id IS NULL
UNION
B.some_attribue = 0 AND B.colOther IS NOT NULL AND D.first_column IS NOT NULL AND D.second_column = 0  AND D.second_column IS NOT NULL AND D.third_colum <> 0
UNINO
B.some_attribue = 0 AND B.colOther IS NOT NULL AND D.first_column IS NOT NULL AND D.second_column = 0  AND D.second_column IS NOT NULL AND D.third_colum IS NULL

先简化 where条件不要按照业务逻辑写sql

拆分三句每句单独优化 where顺序或是 left 改 inner

试试吧

------解决思路----------------------

引用:

引用:可能是因为varchar是可变量字段, 用作索引需要更多cpu运算量, 而bigint是固定量的字段, 因此, 改为bigint后效率马上改善了。

如果可以的话, 用char也试试。
嗯有可能是这样...
但无所谓了，上头已经说了这种方法不可行，改变字段类型涉及太广，被否决了...

IS NULL 的性能优化有关问题

相关推荐