IS NULL 的性能优化问题

[qhy223]

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...
是否会有更优化的方案？ 在线等大神解答

[wwwwb]

where COALESCE(B.A_id,0)=0 试试

[qhy223]

引用 1 楼 wwwwb 的回复:

where COALESCE(B.A_id,0)=0 试试

你好 这个也试过了，和is null的效率类似。但有意思的是我在IBM 官网上查到了一个类似你的答案的帖子，不过是说的ORACLE...在你说的这个方法之上还说同时要建立一个索引，类似于下面这个： create index test on B(COALESCE(A_id,0))，但DB2似乎是不允许这类语法的，索引无法建立...

[qhy223]

顶起来 看明天有没有高人解答

[qhy223]

谢谢LS两位的回答。 现在更新一下我的状态： 我主楼给出的sql是我任务中涉及的sql的一部分，因为之前我认为引起该sql效率缓慢的原因是is null的查询，所以只给出了关于is null的部分，事实也证明这是拖慢该任务中涉及sql的效率的一大因素，但在我如主楼给出的优化之后，证实了还有其他部分在拖慢效率。 现在我给出整个sql： SELECT DISTINCT A.id FROM A LEFT OUTER JOIN B ON B.A_id =A.id LEFT OUTER JOIN C ON B.id=c.B_id LEFT OUTER JOIN D ON C.D_id=d.id WHERE (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))) 帖子之前提到的一大拖慢效率的因素是上面加粗的部分，原因和改进我已在主楼给出，where里如果只有改进后的，则效率从20分钟变成6秒； 但接下来的问题是，一旦将改进后的与后面的子句OR（条件1&&条件2&&条件3&&条件4）一起，效率又会大大降低，所以我之前的改进几乎做了无用功... 随后我发现，如果将B.A_id的字段类型从VARCHAR(250) 改为 BIGINT，整个sql的效率依然可以大幅提升，甚至没有去掉OR子句，也没有做我提到的改进，就是上面这个sql，仅仅改变一个字段类型，就解决问题了... 所以我的问题是，为何仅改变一个字段类型就可以解决所有问题？是因为DB2的索引机制要求字段需要是BIGINT？

[WWWWA]

建立1个冗余字段保存COALESCE(A_id,0)，在此字段上建立索引试试

[ACMAIN_CHM]

select id from A where not exists (select 1 from B where A_id = A.id) 在B(A_id )创建索引

[yumao]

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

[u010119615]

(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 试试吧

[yumao]

引用 8 楼 qhy223 的回复:

引用 7 楼 kensum2099 的回复:可能是因为varchar是可变量字段, 用作索引需要更多cpu运算量, 而bigint是固定量的字段, 因此, 改为bigint后效率马上改善了。 如果可以的话, 用char也试试。 嗯 有可能是这样... 但无所谓了，上头已经说了这种方法不可行，改变字段类型涉及太广，被否决了...

不应该用VARCHAR做主健, 记录越多, 速度越慢, VARCHAR是给大量文字记录备注等使用的, 设计表时应该想想。

[qhy223]

引用 7 楼 kensum2099 的回复:

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

嗯 有可能是这样... 但无所谓了，上头已经说了这种方法不可行，改变字段类型涉及太广，被否决了...

[qhy223]

引用 10 楼 kensum2099 的回复:

引用 8 楼 qhy223 的回复:引用 7 楼 kensum2099 的回复:可能是因为varchar是可变量字段, 用作索引需要更多cpu运算量, 而bigint是固定量的字段, 因此, 改为bigint后效率马上改善了。 如果可以的话, 用char也试试。 嗯 有可能是这样... 但无所谓了，上头已经说了这种方法不可行，改变字段类型涉及太广，被否决了.……

事实上varchar的这个字段不是主键，起到的是外键的作用，与相关表进行关联，但问题是相对应的表里的字段类型是BIGINT，唯独这张表的时候变成了VARCHAR...

[qhy223]

引用 9 楼 u010119615 的回复:

(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……

这个目前来看是正解，因为目前和同事研究得到的最优方案就是这个思路的，详细的我稍后再贴上。

[g30000]

好东西,谢谢大师

[li72li72]

怎么没有贴出来？

[qhy223]

不好意思，好久远的事情了，记得当时是想下班后回家再贴，但很显然我给忘的一干二净了。还可以找到，但需要时间了，已经离职，这段记忆封存很久了。