网上有些地方说有关非的操作符不能利用索引,这些操作符有 != 、 <> 、 !< 、 !> 、 NOT EXISTS 、 NOT IN 、 NOT LIKE 。 下面来测试一下 != 、 <> 、 !< 、 !> 、 NOT IN ( NOT EXISTS 、 NOT LIKE 会在其他文章中详细说明) 。
测试版本为 DB2 v8.1.0.64 ( 查看 DB2 版本 db2level) 和 DB2 v9.7
1. 测试选择性不强的情况。
先创建测试数据 :
create table student
(
id int primary key not null ,
sid int
)
注 : 定义主键时系统自动创建索引,如果 DROP 表,关于这个表的所有索引也被删除,也包括系统创建的索引。
create procedure insertDate()
BEGIN
DECLARE v_id int;
set v_id = 0;
while v_id < 100000
DO
insert into student values(v_id,v_id );
set v_id = v_id + 1;
end while;
END ;
-- 插入数据
call insertDate ()
-- 创建索引
CREATE INDEX STU_SID ON STUDENT
( SID ASC
)
PCTFREE 10
ALLOW REVERSE SCANS ;
注意 : 在一个表的数据大量修改后,要运行下面的命令:
RUNSTATS ON TABLE NBADV. STUDENT FOR INDEX NBADV. STU_SID SHRLEVEL REFERENCE
runstats on table nbadv . student with distribution and detailed indexes all
注 :NBADV. STUDENT 是 [ 模式名 ].[ 表名 ] NBADV. STU_SID 是 [ 模式名 ].[ 表名 ]
测试语句 :
select sid from student where sid <> 1 (!= 和 <> 一样,就不测试了 )
select sid from student where sid !< 1
select sid from student where sid !> 100001
上面的语句都只能排除 10W 分之一,可以看到都没有利用索引。
2. 测试选择性强的情况。
将上面的测试数据执行下面的修改
update student set sid = 1 where sid > 100 or sid = 0
注意 : 在一个表的数据大量修改后,要运行下面的命令:
RUNSTATS ON TABLE NBADV. STUDENT FOR INDEX NBADV. STU_SID SHRLEVEL REFERENCE
测试语句 :
select sid from student where sid <> 1 (!= 和 <> 一样,就不测试了 )
select sid from student where sid !< 2
select sid from student where sid > 1 or sid < 1
可以发现上面三个语句都利用了索引,但是 <> 利用索引后效率并没有高出多少,而 !< 利用索引后效率很高。将 select sid from student where sid <> 1 转化为 select sid from student where sid > 1 or sid < 1 后效率也是很高。我是这么理解的, <> 时将索引列上所有数据一个个取出来判断,然后取出不等于 1 的数据,这样索引列上每一个数据都要被取出判断,所以,效率很低。但是 !< 时,首先找到所以列上数据 2 的位置,然后将 2 之后的数据都取出来,由于查找 2 的位置很快,而取出数据也只有 100 个,所以,速度会很快。而 <1 and
>1 会查找 1 的开始位置和结束位置,然后取出开始位置之前和结束位置之后的数据,由于查找数据位置很快,而取出数据 也只有 100 行,所以速度很快。
下面测试 not in
当 10 万行数据中只有 100 不等于 1 的数据时:
select sid from student where sid not in (1)
select sid from student where sid > 1
当 10 万行数据中只有 99999 不等于 1 的数据时:
select sid from student where sid not in (1)
和
select sid from student where sid > 1
执行计划一样,如图:
可以看到当条件的筛选性不强时, not in 和其他方式效率差不多,当数据筛选性很强时,就需要将 not in 转换成其他写法了。
总结 :
<> 虽和 not in 虽然能利用索引,但是利用索引后效率并不能改变多少,所以建议在数据筛选性很强时(满足条件的数据比较多),将 <> 转化为 < XX and >XX , not in 也可以转化成别的。而 !< 或者 !> 可以利用索引,且索引利用后效率提升很多,不必转换。