★ 使用索引的误区之四：空值对索引的影响

最新推荐文章于 2022-10-14 14:12:41 发布
最新推荐文章于 2022-10-14 14:12:41 发布
阅读量1.9k 收藏
点赞数
分类专栏： DB 优化 文章标签： null insert optimization sql oracle table

 

使用索引的误区之四：空值对索引的影响

我们首先做一些测试数据：

SQL> create table t(x int, y int);

 

Table created

 

请注意，这里我对表t做了一个唯一（联合）索引：

SQL> create unique index t_idx on t(x,y);

 

Index created

 

SQL> insert into t values(1,1);

 

1 row inserted

 

SQL> insert into t values(1,NULL);

 

1 row inserted

 

SQL> insert into t values(NULL,1);

 

1 row inserted

 

SQL> insert into t values(NULL,NULL);

 

1 row inserted

 

SQL> commit;

 

Commit complete

 

下面我们分析一下索引：

SQL> analyze index t_idx validate structure;

 

Index analyzed

 

SQL> select name,lf_rows from index_stats;

 

NAME                              LF_ROWS

------------------------------ ----------

T_IDX                                   3

 

SQL>

然后，我们就可以看到，当前的索引中仅仅保存了3行数据。

请注意，上面我们插入并提交了四行数据。

所以，这里就有一个结论：

Oracle的索引不保存全部为空的行。

 

 

我们继续插入数据，现在再插入几行全部为空的行：

SQL> insert into t values(NULL,NULL);

 

1 row inserted

 

SQL> insert into t values(NULL,NULL);

 

1 row inserted

我们看到这样的插入，居然没有违反前面我们设定的唯一约束（unique on t(x,y)），

所以，这里我们又得出一个结论：

Oracle认为 NULL<>NULL ，进而 (NULL,NULL)<>(NULL,NULL)

换句话说，Oracle认为空值（NULL）不等于任何值，包括空值也不等于空值。

 

我们看到下面的插入会违反唯一约束(DEMO.T_IDX)，这个很好理解了，因为它不是全部为空的值，即它不是（NULL,NULL），只有全部为空的行才被认为是不同的行：

SQL> insert into t values(1,null);

 

insert into t values(1,null)

 

ORA-00001: 违反唯一约束条件 (DEMO.T_IDX)

 

SQL> insert into t values(null,1);

 

insert into t values(null,1)

 

ORA-00001: 违反唯一约束条件 (DEMO.T_IDX)

 

SQL>

 

请看下面的例子:

SQL> select x,y,count(*) from t group by x,y;

 

    X        Y   COUNT(*)

----- -------- ----------

                        3

             1          1

    1                   1

    1        1          1

Executed in 0.03 seconds

 

SQL> select x,y,count(*) from t where x is null and y is null group by x,y;

 

   X       Y   COUNT(*)

---- ------- ----------

                      3

 

Executed in 0.01 seconds

 

SQL>

SQL> select x,y,count(*) from t group by x,y having count(*)>1;

 

     X                    Y   COUNT(*)

------ -------------------- ----------

                                     3

 

Executed in 0.02 seconds

SQL>

可以看见，完全为空的行有三行，这里我们又可以得出一个结论：

oracle在group by子句中认为完全为空的行是相同的行

换句话说，在group by子句中，oracle认为(NULL,NULL)=(NULL,NULL)

 

 

 

下面的语句，使用了复合索引（x,y）的前导列，通常这样的查询是会使用索引的，我们看看下面的例子：

select * from t where x is null;

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

--------------------------------------------------------------------

| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |

--------------------------------------------------------------------

|   0 | SELECT STATEMENT     |             |       |       |       |

|*  1 |  TABLE ACCESS FULL   | T           |       |       |       |

--------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("T"."X" IS NULL)

Note: rule based optimization

 

14 rows selected

 

Executed in 0.06 seconds

 

我们看到上面的查询并没有使用索引，那么对比一下不使用控制的情况：

对比一下下面的查询：

select * from t where x=1;

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

--------------------------------------------------------------------

| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |

--------------------------------------------------------------------

|   0 | SELECT STATEMENT     |             |       |       |       |

|*  1 |  INDEX RANGE SCAN    | T_IDX       |       |       |       |

--------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("T"."X"=1)

Note: rule based optimization

 

14 rows selected

 

Executed in 0.04 seconds

这个查询（where x=1）如我们所希望的那样使用了t_idx(x,y)复合索引，这里我们可以得出一个结论：

在使用IS NULL 和 IS NOT NULL条件的时候，Oracle不使用索引（因为Oracle的索引不存储空值，详细请参见前面的相关内容）

 

那么我们如何使用空值的比较条件呢？

首先，尽量不在前导列上使用空值，请看下面的例子：

select * from t where x=1 and y is null;

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

--------------------------------------------------------------------

| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |

--------------------------------------------------------------------

|   0 | SELECT STATEMENT     |             |       |       |       |

|*  1 |  INDEX RANGE SCAN    | T_IDX       |       |       |       |

--------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("T"."X"=1)

       filter("T"."Y" IS NULL)

Note: rule based optimization

 

15 rows selected

 

select * from t where x is null and y=1;

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

--------------------------------------------------------------------

| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |

--------------------------------------------------------------------

|   0 | SELECT STATEMENT     |             |       |       |       |

|*  1 |  TABLE ACCESS FULL   | T           |       |       |       |

--------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("T"."Y"=1 AND "T"."X" IS NULL)

Note: rule based optimization

 

14 rows selected

 

还有一个可以变通的方法，即我们在创建表的时候，为每个列都指定为非空约束（NOT NULL），并且在必要的列上使用default值，如：

SQL> create table lunar(

  2   c1 varchar2(10) default 'empty'

  3     constraint  c1_notnull not null,

  4   c2 number(10) default 0

  5     constraint c2_notnull not null,

  6   c3 date default to_date('20990101','yyyymmdd')

  7     constraint c3_notnull not null);

 

表已创建。

 

已用时间:  00: 00: 00.00

SQL> insert into lunar(c1) values('first');

 

已创建 1 行。

 

已用时间:  00: 00: 00.00

SQL> insert into lunar(c2) values(99);

 

已创建 1 行。

 

已用时间:  00: 00: 00.00

SQL> insert into lunar(c3) values(sysdate);

 

已创建 1 行。

 

已用时间:  00: 00: 00.00

SQL> insert into lunar(c1,c3) values('ok',sysdate);

 

已创建 1 行。

 

已用时间:  00: 00: 00.00

SQL> insert into lunar(c2,c1) values(999,'hello');

 

已创建 1 行。

 

已用时间:  00: 00: 00.00

SQL> commit;

 

提交完成。

 

已用时间:  00: 00: 00.00

SQL> select * from lunar;

 

C1                 C2 C3

---------- ---------- ----------

first               0 01-1月 -99

empty              99 01-1月 -99

empty               0 19-10月-04

ok                  0 19-10月-04

hello             999 01-1月 -99

 

已用时间:  00: 00: 00.00

SQL> select c1,c2,to_char(c3,'yyyy-mm-yy') from lunar;

 

C1                 C2 TO_CHAR(C3

---------- ---------- ----------

first               0 2099-01-99

empty              99 2099-01-99

empty               0 2004-10-04

ok                  0 2004-10-04

hello             999 2099-01-99

 

已用时间:  00: 00: 00.00

SQL>

然后我们再像使用一般的列那样，使用他们，并且合理的为他们建立索引相信就可以很好的提高应用的查询效率。

 

评论

# yangtingkun 发表于2005-06-09 11:28:00  IP: 61.186.252.*
总结的很好。

但是对于“在使用IS NULL 和 IS NOT NULL条件的时候，Oracle不使用索引”这句话，说的有点绝对。

即使不考虑BITMAP索引，复合索引也会存储空值
SQL> create table tt (col1 number, col2 number);

表已创建。

SQL> create index ind_tt_col1_col2 on tt (col1, col2);

索引已创建。

SQL> insert into tt values (1, null);

已创建 1 行。

SQL> commit;

提交完成。

SQL> set autot on
SQL> select * from tt where col1 = 1 and col2 is null;

COL1 COL2
---------- ----------
1


Execution Plan
----------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 INDEX (RANGE SCAN) OF 'IND_TT_COL1_COL2' (NON-UNIQUE)

对于单列索引，如果指定IS NOT NULL也是有可能使用索引的。
# lunar 发表于2005-06-20 09:29:00  IP: 61.186.252.*
符合索引也不会存储完全为空的条目的，呵呵 ：）

jackpk
关注
专栏目录
使用索引的误区之四：空值对索引的影响
lunar的小铺
11-24 4032
使用索引的误区之四：空值对索引的影响我们首先做一些测试数据：SQL> create table t(x int, y int); Table created 请注意，这里我对表t做了一个唯一（联合）索引：SQL> create unique index t_idx on t(x,y); Index created SQL> insert into t v
空值对索引的影响
zuixinnet的专栏
04-08 734
我们首先做一些测试数据： SQL> create table t(x int, y int);   Table created   请注意，这里我对表t做了一个唯一（联合）索引： SQL> create unique index t_idx on t(x,y);   Index created   SQL> insert into t values(1,1);   1 ro
mysql中NULL值对索引的影响
MRZHQ的博客
10-09 2314
mysql中NULL值对索引的影响NULL的三种形式innodb提供了一个系统变量： NULL的三种形式 NULL值代表一个未确定的值，每一个NULL值都是独一无二的，在统计列不重复值的时候应该都当作独立的。 NULL值在业务上就是代表没有，所有的NULL值代表的意义是一样的，所以所有的NULL值都一样，在统计列不重复值的时候应该只算一个。 NULL完全没有意义，在统计列不重复值的时候应该忽略NULL。 innodb提供了一个系统变量： show global variables like '%inno
索引列上有null值，是否会使用索引？
11-10 1357
记得以前就搞过这个问题，可是时间长了就又记得不清楚了，所以决定记录下来，以便以后查看，也和大家分享下。 1.创建表 create table b (id number,name varchar2(6...
MySQL之索引初识篇：索引机制、索引分类、索引使用与管理综述
Huangjiazhen711的博客
09-27 735
对于MySQL索引就是用来帮助表快速检索目标数据的。此时先来简单回顾一下MySQL中索引是如何使用的呢？首先需要创建索引，MySQL可以通过三种方式创建一个索引。
MySQL索引原理篇：深入数据库底层揭开索引机制的神秘面纱
m0_74132984的博客
10-14 602
MySQL的索引机制，自始至终对于我们都是一个黑盒般的存在，我们并不清楚建立索引后MySQL会发生什么，也并不清楚使用索引查询时会如何检索......。对于索引咱们也留下了很多很多的疑惑：​相信大家多多少少都有了解过，毕竟这也是面试过程中出现次数较为频繁的一个技术点。在本文中就来一窥MySQL索引底层的神秘面纱！一、MySQL索引为何使用B+树结构？MySQL的索引机制中，有一点可谓是路人皆知，既默认使用B+Tree作为底层的数据结构，但为什么要选择B+树呢？有人会说树结构是以二分法查找数据，所以会在很大程
MySQL数据库索引设计最佳实践：避免索引陷阱，打造高效查询
![MySQL数据库索引设计最佳实践：避免索引陷阱，打造高效查询]...在MySQL中，索引可以创建在表中的列上，当对表进行查询时，My
Mysql索引失效的常见原因&如何用好索引
H_Jason_的博客
10-11 1470
本篇文章主要介绍了索引失效的常见原因和如何用好索引，并以案例的形式进行了说明
JSON数据库的常见误区与陷阱：避免JSON数据库使用中的常见错误
[JSON数据库的常见误区与陷阱：避免JSON数据库使用中的常见错误](https://ask.qcloudimg.com/http-save/1326493/6bc2b85ec5c44041919842f9b8aadacc.png) # 1. JSON数据库简介 JSON（JavaScript Object Notation）是...
mysql索引支持null_MySQL_列值为null对索引的影响_实践
weixin_29898529的博客
01-18 649
一.首先看一个我在某公众号看到的一个关于数据库优化的举措二.如果where子句中查询的列执行了 “is null” 或者 “is not null” 或者 “<=> null” 会不会使用索引呢？先列出结论：where子句中使用上述对null的判断，如果判断的列设置了索引，那就可以使用到索引三.测试：1.建表1 CREATE TABLE`test_null_index` (2 `i...
telephone 为空 唯一索引_PostgreSQL的空串、空值对唯一性约束的影响
weixin_29103191的博客
01-30 320
一、现象及原因今天在PostgreSQL又遇到一个现象，应用代码报错：“ERROR: duplicate key value violates unique constraint ...”。最后查明，这是由于PostgreSQL把空串('')、空值(NULL)当作不同的值，从而影响了那些没有申明为NOT NULL字段的unique约束。二、问题重现为重现问题，创建测试表如下：create tabl...
mysql null会导致索引失效吗
小英雄
01-18 1万+
网上很多博客中都写到：is null ，is not null 无法使用索引 首先说下，该结论经过验证是错误的。 现在通过实例来验证下： 我的myslq版本是：5.7.28 建表语句 CREATE TABLE `t_union_index` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `a` bigint(255) DEFAULT ...
MySQL唯一索引和NULL空值之间的关系
bisal的专栏
04-29 1283
《Oracle唯一索引和NULL空值之间的关系》提到了当存在唯一索引的时候，不能插入两条(1, 'a', null)，但是有朋友说，MySQL允许，实测一下，root@mysqldb:[test]>createtablett1(c1varchar(1),c2varchar(1),c3varchar(1)); Query OK, 0 rows af...
Mysql联合唯一索引存在空值时唯一约束失效
热门推荐
xiaobao5214的博客
09-17 1万+
问题： 当Mysql中建立的联合索引， 只要索引中的某一列的值为空时(NULL)，即便其他的字段完全相同，也不会引起唯一索引冲突。 原因： Mysql官方文档中有这样的解释 A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if y...
关于Oracle索引中的空值
Z_Y_J_1_2_3的专栏
04-12 2764
当索引列为空（NULL）或者组合组合索引的所有列都为空时，此记录将不会包含在B*Tree索引中。这是一个基本概念也是非常重要的概念，因为它意味着无法使用B*TREE索引来查找空值，只能查找非空值（NOT NULL）。      因此，将可能在WHERE字句中引用的列定义为非空，一使得在这些列上创建 的索引（默认是B*TREE） 变得有效，最佳实践证：这样是聪明的做法。然而，如果符合下面的
page分页竟然这么写，被经理看到了一脸惊讶
【CSDN】
05-31 3828
以下code就不一一解释了，看不懂的伙伴留言咨询，文末给一个总页数计算的页面 VO： public class PageBean<T> { // 1.当前页数 从页面获取 private int currentPage; // 2.每页显示数据个数，赋初值或者setter获取 private int currentCount; ...
oracle索引空字段,一文搞懂Oracle中索引和空值的恩恩怨怨
weixin_30418225的博客
04-15 1187
在日常的工作中，空值总是有特殊的身份，它和索引之间也是“恩怨”颇多。(1)有时候创建索引会因为空值出现一些奇怪的结果。(2)有时候一个简单的查询因为空值却走不了索引。我们来简单地模拟一下这些问题。首先创建一个空表，注意，对于ID列我们是加了NOT NULL约束的。SQL> create table index_test(id number not null,name varchar2(30)...
oracle 空值 走索引吗,Oracle中NULL值与索引
weixin_35971306的博客
04-04 2770
NULL值是关系数据库系统布尔型(true,false,unknown)中比较特殊类型的一种值，通常称为UNKNOWN或空值，即是未知的，不确定的。由于NULL存在着无数的可能，因此NULL值也不等于NULL值，所以与NULL值相关的操作同样都为NULL值。正是基于这样一个特性，对于NULL值列上的B树索引导致了is null/is not null不走索引的情形，下面描述了NULL值与索引以及索...
MySQL性能优化：索引策略与影响
索引是提升数据库查询效率的重要工具，但也会对写入操作产生负面影响。理解不同类型的索引，如聚集索引和非聚集索引，以及如何合理创建和使用索引，是优化数据库性能的关键。" MySQL性能优化主要涉及两方面：SQL...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值