119-从is null能否走索引说起

老虎刘谈oracle性能优化 2025-03-25

252

这是小技巧, 可能很多有经验的DBA已经知道了, 特别是参加过我索引专题培训的学员们. 今天再展开来讲讲.

有下面这样一个简单SQL, object_id字段上有索引, 请问是否会走索引:

select * from t1 where object_id is null;

思考一下.

......

回答这个问题, 有两个条件还要知道:

1. 使用的什么数据库 ;

2. 满足object_id is null 条件的记录数有多少.

老虎刘的回答是:

如果不是oracle数据库, 而且满足object_id is null的记录数比较少的时候,默认就是可以走索引的. 如果不走索引, 也可以通过hint强制使用. db2和sql server我没测过, mysql/postgresql/国产库, 都可以.

但如果是oracle数据库,即使满足object_id is null的记录数比较少, 也不会走索引, 使用hint强制也不行. 因为oracle的BTree索引比较特殊, 不保存全是null的条目. object_id = null的记录就没有保存在索引中, 所以也就没办法走索引. 这是oracle数据库的索引跟其他数据库不同的地方, 很多从其他库转到oracle数据库的DBA和开发人员, 这个特性需要特别注意.

如何解决这个问题?

只需要来点小技巧, 那就是创建一个联合索引(不需要改写SQL):

create index idx_t1_object_id on t1(object_id,0);

如果object_id 是null ,但是后面的0 不是null, 这个记录就会保存在索引里面, 能走索引也就理所当然了. 这个时候如果再不走索引, 使用hint就一定没问题了. 原来object_id单字段索引可以删掉.

我还见过有人这样解决这个问题:

创建 nvl(object_id,-1) 函数索引, 也是可以的, 但是属于笨方法:

1.对应的SQL也要修改成:select * from t1 where nvl(object_id,-1) = -1;

2.原来object_id字段上的索引还需要保留, 要同时维护两个索引.

补充:

不要见到is null就想套用这种方法,下面这种SQL, 你永远也不要想它走索引:

select * from t1 a

left join t2 b

on a.object_id=b.object_id

where b.object_id is null;

思考题:

select * from t1 where object_id is null and object_type='TABLE';

需要两个字段才能过滤掉大部分记录的情况, 你会创建怎样的索引?

虽然是个小技巧, 还是有很多人不知道, 包括一下优化专家, 补充几个相关案例:

119-从is null能否走索引说起

评论