关于is null和is not null

   
 网上有些资料说含有 null 的列不能创建索引，还有资料说因为一般的索引是 b_ 树结构，而 b+ 树不能存储 null 值，所以 is null 和 is not null 都不能利用 索引。为了证明以上说法，我做了一下测试。

1. 测试含有 null 的列是否能创建索引

先创建测试数据 :

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

    insert into student ( id ) values ( 100001 )

-- 创建索引

         CREATE INDEX STU_SID ON STUDENT
     ( SID    ASC
     )
    PCTFREE 10
    ALLOW REVERSE SCANS ;

结果: 可以发现在有 null 的列式可以建立索引的。

 

2. 测试 is null 和 is not null 能否利用索引

a. 空值很少时的情况

测试数据利用上面的数据， 特点是一共 100001 万条记录，只有１条数据的 sid 为 null 。

注意 : 在一个表的数据大量修改后，要运行下面的命令：

RUNSTATS ON TABLE NBADV. STUDENT FOR INDEX  NBADV. STU_SID   SHRLEVEL REFERENCE

注:NBADV. STUDENT 是 [模式名].[表名] NBADV.STU_SID 是 [模式名].[表名]

如下查询语句 :

SELECT * FROM STUDENT WHERE SID is null

查询计划如下：

 

       

由查询计划可以看出，该语句使用了索引。

再看如下语句：

SELECT * FROM STUDENT WHERE SID is not null

查询计划如下：

 

        

由查询计划可以看出，该语句没有使用索引。

再看如下语句：

SELECT SID FROM STUDENT WHERE SID is not null

查询计划如下：

 

        

由查询计划可以看出，该语句也没有使用了索引。

 

一般 SQL 语句，比如 SELECT * FROM STUDENT WHERE SID is null 这句，只告诉了数据库要从哪个表里找到怎样的数据。至于数据库怎样找到我们要的数据，是走索引，还是全表扫描，还是索引和扫描混合，都是 DB2 自己决定的事情。就如我们对一个人说，你从上海来北京吧。那么他是坐飞机呢，还是坐火车，或者是坐汽车，我们并没说。他会用一个标准来选择到底怎么走，比如最快的标准，就会选择飞机，比如最便宜的标准，就会选择火车（长途汽车一般比火车贵吧？）。而 DB2 里的优化器的标准就是最快，怎样最快的得到我们想要的结果。怎样最快的得要想要的结果呢，是走索引还是全表扫描， DB2 优化器收集来的统计信息，然后选择最优的方式。

下面来看上面的语句为什么会产生那样的结果：

第一个 SELECT * FROM STUDENT WHERE SID is null , 可以看到它是利用索引了的。可是 B+ 树的索引（关于 B+ 树索引，可以查询一些资料，有时间我会写些东西来分析它）是不存储 null 值的，它为什么还可以利用索引呢？这就提到了上面的优化器路的选择问题。

我们可以看到此表有 100001 数据，其中 sid 列只有一条是 null 值。也就是 sid 列的索引会存储此列的 100000 条记录的信息，只有一条没有存。在选择怎么的时候， DB2 优化器会试着用这样两种方式，第一种是从表中取出每条记录，然后看它的 sid 值是否为空。第二种是，先从索引找到 sid 列所有非空的数据在表中的位置，然后在扫描表时，如碰到这些位置，则不用取出数据判断是否为空，直接跳到下一条记录。可以看到，第一种方式进行了全表扫描（这里所谓的全表扫描，是指找出每条记录的位置，一般所说的全表扫描是指扫描表每条记录的位置并读取出每条记录的数据）和全表读取数据，第二种方式也进行了全表的扫描，但却没进行全表数据的读取，而是利用索引排除了其他数据，只读取了一条数据。所以，这两种方式分别是这样的：

第一种：全表扫描 + 全盘读取数据

第二种：全表扫描 + 索引扫描 + 读取一条数据

那么，这两种方式哪一种更快呢？

由于索引比整个表小很多，很显然，读取索引比读取整个表的数据时间会少很多，所以第二种方式效率更高。正如上面查询计划显示的那样利用索引和全表扫描共同找到记录。

 

再来分析这个语句 : SELECT * FROM STUDENT WHERE SID is not null 。 DB2 优化器会有两种方式选择。：

第一种：索引扫描 + 读取数据

第二种：全表扫描 + 读取数据

我们平时所说的利用索引会快，主要是利用索引的有序性可以迅速筛选记录，其实扫描表或者扫描整个表，时间是差不多的。既然索引扫描和全表扫描速度差不多，那么第一种方式和第二种方式速度就一样了？其实第二种方式读取数据会快一些，因为读取数据时，第一种是一条一条的读取，而全表读数据时是一次加载一个 block 来读取 , 所以第二种效率会高一些。

         综合上面的考虑，优化器会选择第二种方式，也就是全表扫描。

        

注: 如果要测试不加条件的索引扫描比全表扫描慢，可以运行这个语句：

alter table transaction_log volatile cardinality

DB2 优化器会强制使用索引

 

b. 空值很多时的情况

-- 插入数据

delete from student
   create procedure insertDate ()
   BEGIN
      DECLARE v_id int ;
      set v_id = 0 ;
      while v_id < 100000
      DO
      if v_id < 9 0000 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 ;

call insertDate ()

RUNSTATS ON TABLE NBADV. STUDENT FOR INDEX NBADV.STU_SID   SHRLEVEL REFERENCE

测试语句 :

SELECT * FROM STUDENT WHERE SID is null

 

 

这里没有使用索引

SELECT * FROM STUDENT WHERE SID is not null

这里没有使用索引

SELECT sid FROM STUDENT WHERE SID is not null

   

 

这里使用了索引。

可以看到，三个同样的语句，前后执行计划却不一样。问题就在于前后的表的数据不一样，第一个只有 1 个 null 值，第二个却有一半的是 null 。下面分析这三个语句。

第一个 SELECT * FROM STUDENT WHERE SID is null

同样有两种方式 :

第一种：全表扫描 + 全盘读取数据

第二种：全表扫描 + 索引扫描 + 读取十分之九数据

这里和第一次测试的区别，就是第二种方式读的数据不是一条而是90%了。从而导致这种利用索引的方式比第一种慢，而优化器选择了第一种。

第二个 SELECT * FROM STUDENT WHERE SID is not null

两种方式 :

第一种：索引扫描 + 读取数据（全表的10%）

第二种：全表扫描 + 读取数据（全表）

由于索引扫描和全表扫描速度差不多，因为读取数据时，第一种是一条一条的读取，而全表读数据时是一次加载一个 block 来读取 , 所以第二种效率会高一些。

 

第三个 SELECT sid FROM STUDENT WHERE SID is not null

两种方式:

第一种：索引扫描+ 读取数据（索引上的数据）

第二种：全表扫描+ 读取数据（全表）

由于索引扫描和全表扫描速度差不多，而第一种方法读取数据时，不用先找到数据在表的位置再去读，而是直接读取索引上的数据（索引上存有该索引列的数据），所以第一种方式快些。优化器选择第一种方式。

另外， SELECT * FROM STUDENT WHERE SID is not null 和 SELECT sid FROM STUDENT WHERE SID is not null 的区别就是一个选择了所有行，而另一个只选择了自己需要的行，而前一个没有利用索引，速度也很慢，后一个利用了索引。所以在写查询时，要保持良好的习惯，尽量不要用 * 号，而是把自己需要的列写出来。

3. 测试 is not null 改写成其他语句效率是否提高

有些地方有这样的说法，is not null 不能利用索引，所以要将其改写成其他语句，以便能够利用索引提高效率。下面是测试情况：

数据: 第一种情况的测试数据。

SQL 语句： SELECT sid   FROM STUDENT WHERE SID is not null

改写后的SQL 语句 ： SELECT sid FROM STUDENT WHERE SID > 0 and sid < 100001

 
 

 

可以看到两种写法的效果完全一样，所以上面的说法是错误的。

4. 总结

我们可以看到，无论是 IS NULL 还是 IS NOT NULL ，并不是如网上所说的 is null 或者 is not null 不能利用索引，而是在不同的表数据结构环境下，有可能会利用索引有可能不利用索引，而决定如何执行查询的标准就是性能。 DB2 查询优化器会评估各种查询方式的开销之后再来做决定。而且网上流传的将 IS NOT NULL 改写成其他语句的写法，也是错的，并不能提高效率。

另外，由上面测试也可以看出，要养成良好的习惯，尽量不要写 select * 而是将需要的列写上。

 

评论

2 楼 liulanghan110 2012-08-20  

这个是DB2的数据库，用的Quest Central。ORACLE的话，要其他的方式看

1 楼 SCOL_007 2012-08-14  

LZ 请问你 你用的什么工具看到查询计划