关于网友提出的“ (DB2)IS NULL 的性能优化问题”问题疑问，本网通过在网上对“ (DB2)IS NULL 的性能优化问题”有关的相关答案进行了整理，供用户进行参考，详细问题解答如下：
问题： (DB2)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:
不应该用VARCHAR做主健, 记录越多, 速度越慢, VARCHAR是给大量文字记录备注等使用的, 设计表时应该想想。
解决方案2: (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
试试吧
解决方案3: 建立1个冗余字段保存COALESCE(A_id,0)，在此字段上建立索引试试
以上介绍了“ (DB2)IS NULL 的性能优化问题”的问题解答，希望对有需要的网友有所帮助。
本文网址链接：http://www.codes51.com/itwd/2034392.html
