当主键碰到NULL(r6笔记第64天)

主键和Null看似没有多大的关系,因为一般的主键设置都是not null,但是把两者结合起来,会有很多意想不到的情况,说是意想不到是因为结果不在预期范围,但是如果明白了基本的原理,整个过程又在情理之中。

我们先来演示一下问题。

首先创建一个表,创建唯一性索引。

SQL> conn n1/n1
Connected.
SQL>
SQL> select*from cat;
no rows selected
SQL> create table test(x number,y number);
Table created
SQL> create unique index ind_test on test(x,y);
Index created.
SQL> insert into test values(1,2);
1 row created.

再次插入重复的数据,这个肯定会抛错是毫无疑问的。

SQL> insert into test values(1,2);
insert into test values(1,2)
*
ERROR at line 1:
ORA-00001: unique constraint (N1.IND_TEST) violated

然后我们开始测试null相关的场景。

SQL> insert into test values(1,null);
1 row created.
SQL> insert into test values(null,1);
1 row created.

插入两个Null值,也是可以的。

SQL> insert into test values(null,null);
1 row created.

再次插入两个null值,还是可以的。

SQL> insert into test values(null,null);
1 row created.

但是反过来再次插入1,null的时候就抛错了。

SQL> insert into test values(1,null);
insert into test values(1,null)
*
ERROR at line 1:
ORA-00001: unique constraint (N1.IND_TEST) violated

同理,null,1的场景也是如此。

SQL> insert into test values(null,1);
insert into test values(null,1)
*
ERROR at line 1:
ORA-00001: unique constraint (N1.IND_TEST) violated

再次插入两个null值。

SQL> insert into test values(null,null);
1 row created.

查看表test中的数据,如下:

SQL> select *from test;
        X          Y
---------- ----------
        1          2
        1
                   1
6 rows selected.

可以看到有6行。null值列看不到任何显示。

为了标识,我们打印出rownum来。

SQL> select rownum,x,y from test;
   ROWNUM          X          Y
---------- ---------- ----------
        1          1          2
        2          1
        3                     1
        4
        5
        6
6 rows selected.

测试完了null值相关的,我们来看看空串''的情况。

插入''的时候就会抛错。

SQL> insert into test values(1,'');
insert into test values(1,'')
*
ERROR at line 1:
ORA-00001: unique constraint (N1.IND_TEST) violated

插入两个空串,和null的效果是一样的。

SQL> insert into test values('','');
1 row created.

null和空串组合,也没有问题。

SQL> insert into test values(null,'');
1 row created.
SQL> insert into test values('','');
1 row created.

再次查看数据,null值的数据行明显增多。

SQL> select rownum,x,y from test;
   ROWNUM          X          Y
---------- ---------- ----------
        1          1          2
        2          1
        3                     1
        4
        5
        6
        7
        8
        9
9 rows selected.

我们可以再进一步,查看null值的长度,使用length()

SQL> select rownum,x,length(x),y,length(y) from test;
   ROWNUM          X  LENGTH(X)          Y  LENGTH(Y)
---------- ---------- ---------- ---------- ----------
        1          1          1          2          1
        2          1          1
        3                                1          1
        4
        5
        6
        7
        8
        9
9 rows selected.

可以看到null值对应的length没有任何显示。

如果用=来匹配空串,和null的效果一样,匹配不了。

SQL> select *from test where x='';
no rows selected

我们还是来看看dump的信息吧,对于null列dump的结果就是null

 1* select rownum,x,y,dump(x) from test
SQL> /
   ROWNUM          X          Y DUMP(X)
---------- ---------- ---------- ------------------------------
        1          1          2 Typ=2 Len=2: 193,2
        2          1            Typ=2 Len=2: 193,2
        3                     1 NULL
        4                       NULL
        5                       NULL
        6                       NULL
        7                       NULL
        8                       NULL
        9                       NULL
9 rows selected.

感觉null值还是一个很有意思的话题,如果在查询中使用了 where xxx is null的方式,就不会走索引扫描,

而如果表中没有not null的约束,这可能会牵扯到一个全表扫描的案例

我们还是创建一个新表a,然后字段Object_id上没有not null约束

SQL> create table a as select object_id,object_name,object_type from dba_objects;
Table created.
SQL> desc a
Name                                                  Null?    Type
----------------------------------------------------- -------- ------------------------------------
OBJECT_ID                                                      NUMBER
OBJECT_NAME                                               VARCHAR2(128)
OBJECT_TYPE                                               VARCHAR2(19)
SQL> analyze table a compute statistics;
Table analyzed.
Dbms_stats.gather_table_stats;
SQL> create unique index ind_a on a(object_id);
Index created.
SQL> set autot traceonly exp
如果根据object_id来查询,是会走唯一性扫描。

但是如果查看所有object_id的值,就会走全表扫描。如果查看object_id为null的行,发现时0条。

如果加入了not null约束,就会走fast full scan了。

可见null值对于索引扫描的影响确实是非常巨大,需要在写sql语句的时候提前注意到这个问题。

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2015-09-19

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏潘昌伟的专栏

用好 mysql 分区表

大数据时代,数据量趋于海量,mysql单表很难满足大数据场景的一些统计需求,用好分区表可以很好的解决很大一部分的问题。

1.8K1
来自专栏性能与架构

体验 MySQL 8.0 JSON聚合函数

MySQL 最近的动作很快,已经计划推出 8.0 版本,会新增很多新特性 在 5.7 中,JSON 已经被正式支持,但在 SQL 中对 JSON 的处理能力较弱...

57916
来自专栏杨建荣的学习笔记

MySQL replace into的使用细则(r10笔记第48天)

在Oracle中有merge into的语法,可以达到一个语句完成同时修改,添加数据的功能,MySQL里面没有merge into的语法,却有replace i...

3175
来自专栏高性能服务器开发

数据库进阶4 Mysql 性能优化20个原则(2)

如果你的应用程序有很多 JOIN 查询,你应该确认两个表中Join的字段是被建过索引的。这样,MySQL内部会启动为你优化Join的SQL语句的机制。

902
来自专栏撸码那些事

MySQL——索引基础

本篇文章,我们将从索引基础开始,介绍什么是索引以及索引的几种类型,然后学习如何创建索引以及索引设计的基本原则。

993
来自专栏JMCui

MySQL数据库 Event 定时执行任务.

一、背景   由于项目的业务是不断往前跑的,所以难免数据库的表的量会越来越庞大,不断的挤占硬盘空间。即使再大的空间也支撑不起业务的增长,所以定期删除不必要的数据...

3856
来自专栏飞扬的花生

SqlServer批量刷数据执行事务回滚语句备份

      企业进行对数据库执行刷数据工作,一段很长的语句希望同时成功或者失败时用到。 1.建立测试环境 /**************************...

2526
来自专栏林欣哲

MySQL执行计划(explain)分析

这里的索引有auditstatus和productid,可以建立联合索引。但是哪个放左边就要计算区分度。

1404
来自专栏跟着阿笨一起玩NET

SQL Server通过整理索引碎片和重建索引提高速度

本文章转载:http://database.51cto.com/art/201108/282408.htm

3061
来自专栏Java架构师历程

sql必会基础1

ALTER TABLE old_table_name RENAME [TO] new_table_name

1242

扫码关注云+社区

领取腾讯云代金券