空与非空:浅谈非空约束的影响

黄玮(Fuyuncat)

资深Oracle DBA,个人网www.HelloDBA.com,致力于数据库底层技术的研究,其作品获得广大同行的高度评价.

非空约束是字段的一个重要属性。但是,很多时候,数据库表的设计人员似乎并不十分在意这个属性。最常见的现象就是,除了主键字段外,所有字段都不指定该属性。而在Oracle中,默认是允许为空。

而实际上,优化器在选择执行计划时,非空约束是一个重要的影响因素。为了说明问题,我们建立以下测试表,然后分别说明非空约束在各种情况下对执行计划和性能的影响。

谓词评估

在上面表中,字段SUBOBJECT_NAME中不存在空值,但也没有非空约束,再看以下查询,查找该字段的空值记录:

统计信息如下:

我们看到,需要对表进行全表扫描(关于索引,随后再讨论)。而如果我们加上非空约束,可以看到执行计划已经性能的变化:

统计信息如下:

注意到,在全表扫描之前,增加了一个filter,而fileter的表达式是NULL IS NOT NULL,其逻辑结果是FALSE,因此,实际上其子操作(全表扫描)并未执行。相应的,请性能数据里面CR为0。

之所以优化器会为执行计划增加这样一个filter,是因为优化器在做查询转换(Query Transformation)时,会将非空约束作为参照条件之一,对where子句的谓词做逻辑结果评估,如果评估结果为false,则会增加一个这样的filter,以避免执行一些高代价的操作。从10053跟踪文件中,可以看到这对于优化器对执行计划代价估算的影响:

非空约束对索引选择的影响

我们知道,Oracle中B*树索引中不存在空键值,即在表的数据记录中,如果索引中所有字段都为空,则该记录不会被构建到索引树中。也就是说,如果索引字段上没有非空约束,则表记录与索引记录不是完全映射的。

我们先去掉subobject_name上的非空约束,并在上面建立索引:

HELLODBA.COM>alter table t_test1 modify subobject_name null; Table altered. HELLODBA.COM>create index t_test1_subo_idx on t_test1(subobject_name) compute statistics; Index created.

执行以下语句,以获取subobject_name最小的10条记录。为了提高效率,我们希望直接从索引中直接读取前10条ROWID(索引数据已经按照subobject_name排序),然后根据ROWID获取数据记录:

我们来看执行计划

统计信息如下:

但是,查询计划和结果看,语句并没有按照设想的方式执行,得出的数据也不是我们需要的。其原因就在于,由于空值不被索引,优化器无法确认索引数据是否涵盖了所有数据记录,因而它没有选择指定索引。

我们把非空约束加上,执行计划和结果就符合我们的需求了。

我们来看执行计划

统计信息如下

非空约束对连接查询的影响

在进行数据关联时,数据集中关联字段是否存在空值也会影响优化器对执行计划的选择。我们再创建一张测试表。

再将subobject_name的非空约束去掉。

HELLODBA.COM>alter table t_test1 modify subobject_name null; Table altered.

我们通过以下语句查找t_test1中subobject_name不为table_name的数据:

统计信息如下:

可以看到,执行计划通过添加函数LNNVL和NOT EXISTS,对数据进行过滤得到结果,性能相当低。

注意:当逻辑表达是中的操作数可能为空时,LNNVL函数可以判断出该表达式的结果。

我们再把非空约束加上,

统计信息如下

可以看到执行计划通过ANTI-JOIN获取我们需要的数据,性能大为改善。

我们知道,对于空值的逻辑判断,只能通过IS NULL或IS NOT NULL进行判断,除此之外,一旦表达式中含有NULL值,结果始终为空。这也就是空值的存在不会影响JOIN或SEMI-JOIN的原因。

ANTI-JOIN,通过关联方法(NESTED-LOOP、MERGE、HASH)判断记录是否符合条件,一旦发现两边记录可以关联上,则判定不符合要求,可以放弃对一个数据集中的剩余数据的判断,因而能提高性能。而关联是同等价匹配(=)实现的,不适合空值数据。因此,关联字段可能存在空值时,无法采用ANTI-JOIN。

注意:无论是否存在空值,NOT EXISTS都可以采用ANTI-JOIN,但是它的逻辑结果与NOT IN并不等价。在以下例子中,NOT EXISTS和NOT IN的结果不相同:

统计信息如下

另外一种情况

统计信息如下:

事务回滚

HELLODBA.COM>rollback; Rollback complete.

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2016-08-30

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏数据和云

Library Cache优化与SQL游标

? 冷菠 冷菠,网名悠然(个人主页http://www.orasky.net),资深DBA,著有《Oracle高性能自动化运维》,有近10年的数据库运维、团队...

2675
来自专栏林欣哲

MySQL数据库开发规范知识点速查

数据库设计规范 命名规范 基本设计规范 索引设计规范 字段设计规范 SQL开发规范 操作行为规范 命名规范 对象名称使用小写字母并用下划线分割 禁止使用MySQ...

35111
来自专栏程序猿

MySQL优化方案(一)优化SQL脚本与索引

MySQL的优化方案有哪一些? 本文记录MySQL优化方案 ,梗概如下: 优化SQL 优化索引 (一)优化SQL 1、通过MySQL自有的优化语句 优化SQL语...

3937
来自专栏JavaWeb

原 荐 MySQL-性能优化-索引和查询优化

1834
来自专栏Java技术

MySQL存储引擎MyISAM与InnoDB区别总结整理

在MySQL 5.1之前的版本中,默认的搜索引擎是MyISAM,从MySQL 5.5之后的版本中,默认的搜索引擎变更为InnoDB。

782
来自专栏琯琯博客

MySQL 查询优化

15911
来自专栏java一日一条

数据库查询优化

1 使用SET NOCOUNT ON 选项: 缺省地,每次执行SQL语句时,一个消息会从服务端发给客户端以显示SQL语句影响的行数。这些信息对客户端来说...

752
来自专栏性能与架构

Mysql 5.7 的‘虚拟列’是做什么?

Mysql 5.7 中推出了一个非常实用的功能 虚拟列 Generated (Virtual) Columns 对于它的用途,我们通过一个场景来说明 假设有一...

3036
来自专栏维C果糖

史上最简单的 MySQL 教程(十七)「索引」

索引:系统根据某种算法,将已有的数据(未来可能新增的数据),单独建立一个文件,这个文件能够实现快速匹配数据,并且能够快速的找到对应的记录,几乎所有的索引都是建立...

3776
来自专栏xingoo, 一个梦想做发明家的程序员

Java程序员的日常——存储过程知识普及

存储过程是保存可以接受或返回用户提供参数的SQL语句集合。在日常的使用中,经常会遇到复杂的业务逻辑和对数据库的操作,使用存储过程可以进行封装。可以在数据库中定...

1748

扫码关注云+社区