关于is null和is not null不能利用索引的测试
关于is null和is not null不能利用索引的测试
测试is null和is not null能否利用索引
--创建测试数据
create table student
(
id int primary key not null,
sid int
)
--创建索引
CREATE INDEX STU_SID ON STUDENT
(SID ASC
)
PCTFREE 10
ALLOW REVERSE SCANS;
--插入数据
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()
insert into student (id) values(100001)
注意:在一个表的数据大量修改后，要运行下面的命令：
RUNSTATS ON TABLE STUDENT FOR INDEX STU_SID SHRLEVEL REFERENCE;
上面插入的数据特点是一共100001万条记录，只有１条数据的sid为null
如下查询语句:
SELECT * FROM ZHUYUEHUA.STUDENT WHERE SID is null
查询计划如下：
<?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" />
由查询计划可以看出，该语句使用了索引。
再看如下语句：
SELECT * FROM ZHUYUEHUA.STUDENT WHERE SID is not null
查询计划如下：
由查询计划可以看出，该语句没有使用索引。
再看如下语句：
SELECT SID FROM ZHUYUEHUA.STUDENT WHERE SID is not null
查询计划如下：
由查询计划可以看出，该语句也没有使用了索引。
一般SQL语句，比如SELECT * FROM ZHUYUEHUA.STUDENT WHERE SID is null这句，只告诉了数据库要从哪个表里找到怎样的数据。至于数据库怎样找到我们要的数据，是走索引，还是全表扫描，还是索引和扫描混合，都是DB2自己决定的事情。就如我们对一个人说，你从上海来北京吧。那么他是坐飞机呢，还是坐火车，或者是坐汽车，我们并没说。他会用一个标准来选择到底怎么走，比如最快的标准，就会选择飞机，比如最便宜的标准，就会选择火车（长途汽车一般比火车贵吧？）。而DB2里的优化器的标准就是最快，怎样最快的得到我们想要的结果。怎样最快的得要想要的结果呢，是走索引还是全表扫描，DB2优化器自己也不清楚哪种情况速度最快，他会各种情况都是一次，然后选择最优的方式。
下面来看上面的语句为什么会产生那样的结果：
第一个SELECT * FROM ZHUYUEHUA.STUDENT WHERE SID is null ,可以看到它是利用索引了的。可是B+树的索引（关于B+树索引，可以查询一些资料，有时间我会写些东西来分析它）是不存储null值的，它为什么还可以利用索引呢？这就提到了上面的优化器路的选择问题。
我们可以看到此表有100001数据，其中sid列只有一条是null值。也就是sid列的索引会存储此列的100000条记录的信息，只有一条没有存。在选择怎么的时候，DB2优化器会试着用这样两种方式，第一种是从表中取出每条记录，然后看它的sid值是否为空。第二种是，先从索引找到sid列所有非空的数据在表中的位置，然后在扫描表时，如碰到这些位置，则不用取出数据判断是否为空，直接跳到下一条记录。可以看到，第一种方式进行了全表扫描（这里所谓的全表扫描，是指找出每条记录的位置，一般所说的全表扫描是指扫描表每条记录的位置并读取出每条记录的数据）和全表读取数据，第二种方式也进行了全表的扫描，但却没进行全表数据的读取，而是利用索引排除了其他数据，只读取了一条数据。所以，这两种方式分别是这样的：
第一种：全表扫描+全盘读取数据
第二种：全表扫描+索引扫描+读取一条数据
那么，这两种方式哪一种更快呢？
由于索引比整个表小很多，很显然，读取索引比读取整个表的数据时间会少很多，所以第二种方式效率更高。正如上面查询计划显示的那样利用索引和全表扫描共同找到记录。
再来分析这个语句: SELECT * FROM ZHUYUEHUA.STUDENT WHERE SID is not null。DB2优化器会有两种方式选择。：
第一种：索引扫描+读取数据
第二种：全表扫描+读取数据
我们平时所说的利用索引会快，主要是利用索引的有序性可以迅速筛选记录，其实扫描表或者扫描整个表，时间是差不多的。既然索引扫描和全表扫描速度差不多，那么第一种方式和第二种方式速度就一样了？其实第二种方式读取数据会快一些，因为读取数据时，第一种是一条一条的读取，而全表读数据时是一次加载一个block来读取,所以第二种效率会高一些。
综合上面的考虑，优化器会选择第二种方式，也就是全表扫描。
如果要测试不加条件的索引扫描比全表扫描慢，可以运行这个语句：
alter table transaction_log volatile cardinality
DB2优化器会强制使用索引
再看下面的测试：
--插入数据
delete from student
create procedure insertDate()
BEGIN
DECLARE v_id int;
set v_id = 0;
while v_id < 100000
DO
if v_id < 50000 then
insert into student (id)values(v_id );
else
insert into student values(v_id,v_id );
end if;
set v_id = v_id + 1;
end while;
END ;
SELECT * FROM ZHUYUEHUA.STUDENT WHERE SID is null
这里没有使用索引
SELECT * FROM ZHUYUEHUA.STUDENT WHERE SID is not null
这里没有使用索引
SELECT SID FROM ZHUYUEHUA.STUDENT WHERE SID is not null
这里使用了索引。
可以看到，三个同样的语句，前后执行计划却不一样。问题就在于前后的表的数据不一样，第一个只有1个null值，第二个却有一半的是null。下面分析这三个语句。
第一个SELECT * FROM ZHUYUEHUA.STUDENT WHERE SID is null
同样有两种方式:
第一种：全表扫描+全盘读取数据
第二种：全表扫描+索引扫描+读取一半数据
这里和第一次测试的区别，就是第二种方式读的数据不是一条而是一半了。从而导致这种利用索引的方式比第一种慢，而优化器选择了第一种。
第二个SELECT * FROM ZHUYUEHUA.STUDENT WHERE SID is not null
两种方式:
第一种：索引扫描+读取数据（全表的一半）
第二种：全表扫描+读取数据（全表）
由于索引扫描和全表扫描速度差不多，因为读取数据时，第一种是一条一条的读取，而全表读数据时是一次加载一个block来读取,所以第二种效率会高一些。
第三个SELECT sid FROM ZHUYUEHUA.STUDENT WHERE SID is not null
两种方式:
第一种：索引扫描+读取数据（索引上的数据）
第二种：全表扫描+读取数据（全表）
由于索引扫描和全表扫描速度差不多，而第一种方法读取数据时，不用先找到数据在表的位置再去读，而是直接读取索引上的数据（索引上存有该索引列的数据），所以第一种方式快些。优化器选择第一种方式。
另外，SELECT * FROM ZHUYUEHUA.STUDENT WHERE SID is not null和SELECT sid FROM ZHUYUEHUA.STUDENT WHERE SID is not null的区别就是一个选择了所有行，而另一个只选择了自己需要的行，而前一个没有利用索引，速度也很慢，后一个利用了索引。所以在写查询时，要保持良好的习惯，尽量不要用*号，而是把自己需要的列写出来。
总结：我们可以看到，无论是IS NULL还是IS NOT NULL，并不是如网上所说的is null或者is not null不能利用索引，而是在不同的表数据结构环境下，有可能会利用索引有可能不利用索引，而决定如何执行查询的标准就是性能。DB2查询优化器会评估各种查询方式的开销之后再来做决定。
