AI 助理
备案控制台
开发者社区 数据库 文章 正文

NULL 值与索引(二)

2012-04-08 783
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介：     在NULL值与索引(一)中讲述了null值与索引的一些基本情况。其主要的内容为，基于允许存在null值的索引列，其索引值不会被存储；其次是由于这个特性导致了我们在使用is null时索引失效的情形；最后则是描述的通过为null值列添加not null约束来使得is null走索引。
    在NULL值与索引(一)中讲述了null值与索引的一些基本情况。其主要的内容为，基于允许存在null值的索引列，其索引值不会被存储；其次
是由于这个特性导致了我们在使用is null时索引失效的情形；最后则是描述的通过为null值列添加not null约束来使得is null走索引。尽管我
们可以通过添加not null来解决is null走索引，当现实中的情况是仍然很多列根本是无法确定的，而必须保持其null特性。对于此种情形该如
何解决呢？

一、通过基于函数的索引来使得is null使用索引
 
-->演示环境
scott@ORCL> select * from v$version where rownum<2;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

-->创建测试表t2
scott@ORCL> create table t2(obj_id,obj_name) as select object_id,object_name from dba_objects;

Table created.

-->演示表t2上不存在not null约束
scott@ORCL> desc t2
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 OBJ_ID                                 NUMBER
 OBJ_NAME                               VARCHAR2(128)

-->为表t2创建一个普通的B树索引
scott@ORCL> create index i_t2_obj_id on t2(obj_id);

Index created.

-->将表t2列obj_id<=100的obj_id置空
-->注：在Oracle 10g中空字符串等同于null值
scott@ORCL> update t2 set obj_id='' where obj_id<=100;

99 rows updated.

-->下面的查询亦表明在此时空字符串等同于null值
scott@ORCL> set null unknown
scott@ORCL> select * from t2 where obj_id is null and rownum<3;

    OBJ_ID OBJ_NAME
---------- ------------------------------
unknown    ICOL$
unknown    I_USER1

-->收集统计信息
scott@ORCL> exec dbms_stats.gather_table_stats('SCOTT','T2',cascade=>true);

PL/SQL procedure successfully completed.

-->基于null值上使用not null会使用索引扫描，等同于前面 null值与索引(一) 中的描述
scott@ORCL> select count(*) from t2 where obj_id is not null;

Execution Plan
----------------------------------------------------------
Plan hash value: 3840858596

-------------------------------------------------------------------------------------
| Id  | Operation             | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |     1 |     5 |     7   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |             |     1 |     5 |            |          |
|*  2 |   INDEX FAST FULL SCAN| I_T2_OBJ_ID | 11719 | 58595 |     7   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJ_ID" IS NOT NULL)

-->列obj_id is null走全表扫描
scott@ORCL> select count(*) from t2 where obj_id is null;

Execution Plan
----------------------------------------------------------
Plan hash value: 3321871023

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     5 |    13   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     5 |            |          |
|*  2 |   TABLE ACCESS FULL| T2   |     1 |     5 |    13   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJ_ID" IS NULL)

-->创建基于函数的索引来使得is null走索引
-->下面使用了nvl函数来创建函数索引，即当obj_id为null值时，存储-1   
scott@ORCL> create index i_fn_t2_obj_id on t2(nvl(obj_id,-1));

Index created.

-->收集索引信息
scott@ORCL> exec dbms_stats.gather_index_stats('SCOTT','I_FN_T2_OBJ_ID');

PL/SQL procedure successfully completed.

-->可以看到下面的执行计划中刚刚创建的函数索引已经生效I_FN_T2_OBJ_ID
scott@ORCL> select count(*) from t2 where nvl(obj_id,-1) = -1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3983750858

------------------------------------------------------------------------------------
| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |     1 |     5 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |                |     1 |     5 |            |          |
|*  2 |   INDEX RANGE SCAN| I_FN_T2_OBJ_ID |   100 |   500 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(NVL("OBJ_ID",(-1))=(-1))
二、使用伪列创建基于函数的索引来使得is null使用索引 
-->下面通过添加一个值为-1(可取任意值)的伪列来创建索引
scott@ORCL> create index i_new_t2_obj_id on t2(obj_id,-1);

Index created.

-->收集索引信息
scott@ORCL> exec dbms_stats.gather_index_stats('SCOTT','I_NEW_T2_OBJ_ID');

PL/SQL procedure successfully completed.   

-->从下面的查询可以看出obj_id is null使用了刚刚创建的索引
scott@ORCL> select count(*) from t2 where obj_id is null;

Execution Plan
----------------------------------------------------------
Plan hash value: 801885198

-------------------------------------------------------------------------------------
| Id  | Operation         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                 |     1 |     5 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |                 |     1 |     5 |            |          |
|*  2 |   INDEX RANGE SCAN| I_NEW_T2_OBJ_ID |    99 |   495 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJ_ID" IS NULL)

-->查看刚刚创建的所有索引的相关统计信息   
scott@ORCL> select index_name,index_type,blevel,leaf_blocks,num_rows,status,distinct_keys
  2  from user_indexes where table_name='T2';

INDEX_NAME      INDEX_TYPE                         BLEVEL LEAF_BLOCKS   NUM_ROWS STATUS   DISTINCT_KEYS
--------------- ------------------------------ ---------- ----------- ---------- -------- -------------
I_FN_T2_OBJ_ID  FUNCTION-BASED NORMAL                   1          26      11719 VALID            11621
I_NEW_T2_OBJ_ID FUNCTION-BASED NORMAL                   1          32      11719 VALID            11621
I_T2_OBJ_ID     NORMAL                                  1          25      11620 VALID            11620

-->从上面的结果可知：
-->普通的B索引(I_T2_OBJ_ID)使用的索引块最小，因为null值没有被存储，NUM_ROWS与DISTINCT_KEYS即是佐证
-->使用NVL函数创建的索引I_FN_T2_OBJ_ID中如实的反应了null值，即11620 + null值 = 11621
-->使用伪列创建的索引依然属于函数索引，其耗用的叶节点块数最多，因为多出了一个值(-1)来存储
-->尽管使用NVL创建的函数占用的磁盘空间小于使用伪列创建的索引，当在书写谓词时需要带上NVL函数，而伪列索引中谓词直接使用is null。
三、NULL值与索引衍生特性 
-->由前面的种种事例再次说明NULL值不会被存储到索引中，因此基于这个特性可以使用decode函数来压缩索引列。
-->在实际应用的多数情形中，如表上有打印状态列is_printed通常为两种情形，已打印或未打印，假定1表示已打印，而0表示未打印。
-->通常情况下90%以上的单据都处于已打印状态，而仅有10%左右的处于未打印。而经常要使用的情形是查询未打印的单据并重新打印。
-->基于上述情况，可以使用位图索引来解决，但此处我们讨论的是B树索引，故不考虑该情形(或者说你使用了非企业版Oracle，不支持位图索引)
-->此处对于这类情形我们可以使用decode函数来解决这个问题

-->更新表上的列，使之obj_id为1的行占绝大多数
scott@ORCL> update t2 set obj_id=1 where obj_id is not null;

11620 rows updated.

-->更新表，使之obj_id为0的行占少部分
scott@ORCL> update t2 set obj_id = 0 where obj_id is null;

99 rows updated.

scott@ORCL> commit;

-->收集统计信息
scott@ORCL> exec dbms_stats.gather_table_stats('SCOTT','T2',cascade=>true);

PL/SQL procedure successfully completed.

-->表t2上obj_id列的最终分布
scott@ORCL> select obj_id,count(*) from t2 group by obj_id;

    OBJ_ID   COUNT(*)
---------- ----------
         1      11620
         0         99   

-->使用decode函数创建索引
-->注意此处decode的使用，当obj_id非0值时，其值被赋予为null值，由于该null值不会存储到索引，因此大部分obj_id列值为1的不会被索引
scott@ORCL> create index i_fn2_t2_obj_id on t2(decode(obj_id,0,0,null));

Index created.

-->收集索引上的统计信息
scott@ORCL> exec dbms_stats.gather_index_stats('SCOTT','I_FN2_T2_OBJ_ID');

PL/SQL procedure successfully completed.

-->查看新索引的执行计划
scott@ORCL> set autot trace exp;
scott@ORCL> select count(*) from t2 where decode(obj_id,0,0,null) = 0;

Execution Plan
----------------------------------------------------------
Plan hash value: 1461308992

-------------------------------------------------------------------------------------
| Id  | Operation         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                 |     1 |     3 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |                 |     1 |     3 |            |          |
|*  2 |   INDEX RANGE SCAN| I_FN2_T2_OBJ_ID |    98 |   294 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(DECODE("OBJ_ID",0,0,NULL)=0)

-->当直接使用obj_id = 0来查询时使用的是普通的B树索引
scott@ORCL> select count(*) from t2 where obj_id = 0;

Execution Plan
----------------------------------------------------------
Plan hash value: 1804118247

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |     1 |     3 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |             |     1 |     3 |            |          |
|*  2 |   INDEX RANGE SCAN| I_T2_OBJ_ID |    99 |   297 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJ_ID"=0)   

-->当使用obj_id = 1来查询时走全表扫描，因为obj_id = 1占据表90%以上，由CBO特性决定了走全表扫描   
scott@ORCL> select * from t2 where obj_id = 1;

Execution Plan
----------------------------------------------------------
Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 11620 |   249K|    14   (8)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T2   | 11620 |   249K|    14   (8)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJ_ID"=1)
   
-->表t2上所有索引的统计信息
scott@ORCL> select index_name,index_type,blevel,leaf_blocks,num_rows,status,distinct_keys
  2  from user_indexes where table_name='T2';
  
INDEX_NAME      INDEX_TYPE                         BLEVEL LEAF_BLOCKS   NUM_ROWS STATUS   DISTINCT_KEYS
--------------- ------------------------------ ---------- ----------- ---------- -------- -------------
I_FN_T2_OBJ_ID  FUNCTION-BASED NORMAL                   1          40      11719 VALID                2
I_NEW_T2_OBJ_ID FUNCTION-BASED NORMAL                   1          52      11719 VALID                2
I_FN2_T2_OBJ_ID FUNCTION-BASED NORMAL                   0           1         99 VALID                1
I_T2_OBJ_ID     NORMAL                                  1          40      11719 VALID                2

-->从上面的结果可知，索引I_FN2_T2_OBJ_ID仅仅存储了99跳记录，且DISTINCT_KEYS值为1个，因为所有非0值的全部被置NULL。
-->以上方法实现了索引压缩，避免了较大索引维护所需的开销，同时也提高了查询性能。
-->Author : Robinson Cheng
-->Blog :   http://blog.csdn.net/robinson_0612
四、总结
    1、对于用于连接或经常被谓词使用到的列应尽可能避免NULL值属性，因为它容易导致索引失效。
    2、为需要使用NULL值的列添加缺省值(alter table tb modify(col default 'Y'))。
    3、如果NULL值不可避免也不能使用缺省值，应考虑为该常用列使用nvl函数创建索引，或使用伪列来创建索引以提高查询性能。
    4、对于复合索引应保证索引中至少有一列不为NULL值，还是因为全部列为NULL时不被索引存储，以保证使用is null是可以使用索引。
    5、对于复合索引应保证索引列应使用数据类型长度最小的列来添加not null约束应节省磁盘空间。

五、更多参考

NULL 值与索引(一)

SQL tuning 步骤

高效SQL语句必杀技

父游标、子游标及共享游标

绑定变量及其优缺点

dbms_xplan之display_cursor函数的使用

dbms_xplan之display函数的使用

执行计划中各字段各模块描述

使用 EXPLAIN PLAN 获取SQL语句执行计划

启用 AUTOTRACE 功能

函数使得索引列失效

Oracle 绑定变量窥探

Oracle 自适应共享游标

  
文章标签：
索引
存储
SQL
关系型数据库
Oracle
leshami
目录
相关文章
xaubllxwtvaqiu
|
存储 关系型数据库 MySQL
InnoDB索引允许NULL对性能有影响吗(3)
InnoDB索引允许NULL对性能有影响吗
xaubllxwtvaqiu
82 0
xaubllxwtvaqiu
|
存储 SQL 关系型数据库
InnoDB索引允许NULL对性能有影响吗(2)
InnoDB索引允许NULL对性能有影响吗
xaubllxwtvaqiu
119 0
xaubllxwtvaqiu
|
存储 SQL 关系型数据库
InnoDB索引允许NULL对性能有影响吗(1)
InnoDB索引允许NULL对性能有影响吗
xaubllxwtvaqiu
150 0
小目标青年
|
关系型数据库 MySQL 索引
Mysql 唯一索引的字段值 允许多个NULL值存在吗
Mysql 唯一索引的字段值 允许多个NULL值存在吗
小目标青年
686 0
Mysql 唯一索引的字段值 允许多个NULL值存在吗
xaubllxwtvaqiu
|
存储 SQL 关系型数据库
InnoDB索引允许NULL对性能有影响吗(2)
InnoDB索引允许NULL对性能有影响吗
xaubllxwtvaqiu
85 0
xaubllxwtvaqiu
|
存储 SQL 关系型数据库
InnoDB索引允许NULL对性能有影响吗(1)
InnoDB索引允许NULL对性能有影响吗
xaubllxwtvaqiu
104 0
开发者小达人
|
存储 关系型数据库 MySQL
InnoDB索引允许NULL对性能有影响吗(3)
InnoDB索引允许NULL对性能有影响吗
开发者小达人
144 0
开发者小达人
|
存储 SQL 关系型数据库
InnoDB索引允许NULL对性能有影响吗(2)
InnoDB索引允许NULL对性能有影响吗
开发者小达人
132 0
开发者小达人
|
存储 SQL 关系型数据库
InnoDB索引允许NULL对性能有影响吗(1)
InnoDB索引允许NULL对性能有影响吗
开发者小达人
209 0
德哥
|
关系型数据库 数据库 PostgreSQL
PostgreSQL 数据库NULL值的默认排序行为与查询、索引定义规范 - nulls first\last, asc\desc
德哥
5434 0

热门文章

最新文章

  • 1
    【Bug合集】——Java大小写引起传参失败，获取值为null的解决方案
  • 2
    String为null
  • 3
    You have version null and I want version 8
  • 4
    php中empty()，isset()，is_null()，==，===区别
  • 5
    typeof升级版，可以识别出array、object、null、nan、[]、{}
  • 6
    Mysql的NULL的一个注意点
  • 7
    一个坑：java.sql.ResultSet.getInt==》the column value; if the value is SQL NULL, the value returned is 0
  • 8
    深入详解SQL中的Null
  • 9
    解决mybatis-plus日期显示为null的问题
  • 10
    oracle中的空值null
  • 1
    Android 高版本 packageManager.getPackageArchiveInfo 总是返回null
    312
  • 2
    Spring Boot单元测试报错java.lang.IllegalStateException: Could not load TestContextBootstrapper [null]
    196
  • 3
    键值的 key 和 value 允许为null吗
    92
  • 4
    DataWorks操作报错合集之DataWorks提交失败: 提交节点的源码内容到TSP(代码库)失败:"skynet_packageid is null，该怎么解决
    137
  • 5
    java Map删除值为null的元素
    166
  • 6
    【Web 前端】undefined 和 null 区别？
    938
  • 7
    MaxCompute产品使用合集之需要将多个字段concat起来，有时候遇到null值，该怎么做
    162
  • 8
    云原生数据仓库产品使用合集之在云数据仓库ADB中，GROUP BY操作中出现NULL值,如何解决
    965
  • 9
    为什么HashMap的键值可以为null，而ConcurrentHashMap不行？
    99
  • 10
    Trying to access array offset on value of type null
    1897

    • 相关电子书

    更多
  • 低代码开发师（初级）实战教程
  • 冬季实战营第三期：MySQL数据库进阶实战
  • 阿里巴巴DevOps 最佳实践手册

    • 相关实验场景

    更多
  • 常见类型-3：数组元组的方法
  • 常见类型-2：数组和元组
    • 下一篇
    DataWorks智能交互式数据开发与分析之旅