查看: 11668|回复: 15

[笔记] 对于IS NULL条件的优化

[复制链接]
论坛徽章:
9
授权会员
日期:2005-10-30 17:05:33奥运会纪念徽章:自行车
日期:2008-10-24 13:07:492010新春纪念徽章
日期:2010-03-01 11:20:052010数据库技术大会纪念徽章
日期:2010-05-13 09:34:23蜘蛛蛋
日期:2011-12-28 11:33:332012新春纪念徽章
日期:2012-01-04 11:49:54优秀写手
日期:2014-11-22 06:00:132015年新春福章
日期:2015-03-04 14:19:112015年新春福章
日期:2015-03-06 11:57:31
发表于 2011-5-25 10:29 | 显示全部楼层 |阅读模式
单字段索引中,字段的NULL值是不存放与INDEX中的,因此WHERE中的IS NULL条件一般情况下无法使用INDEX来优化,那么,当需要从一个海量数据表中查找某个字段IS NULL的少量记录时如何进行优化呢?
1) 利用复合索引,如果索引中至少有一个字段是非空字段,则即使其他字段是NULL值,整条记录仍然会被索引;
2) 如果没有合适的非空字段可以建立复合索引时,利用固定值复合索引。
下面给出一个例子:
建一个测试表test10,此时object_id和ojbect_name字段都是可以为空的:
create table test10 as select object_id,object_name,object_type from dba_objects;
为object_name字段建立索引:
create index idx_test10_1 on test10(object_name);
检查SQL的执行计划:
--------------------------------------------------------------------
| Id  | Operation            |  Name      | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|  0 | SELECT STATEMENT    |            |    1 |    29 |    5 |
|*  1 |  TABLE ACCESS FULL  | TEST10      |    1 |    29 |    5 |
--------------------------------------------------------------------
将object_id改成非空,再建立复合索引:
alter table test10 modify object_id not null;
create index idx_test10_2 on test10(object_name,object_id);
检查SQL的执行计划:
-----------------------------------------------------------------------------
| Id  | Operation                  |  Name        | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |              |    1 |    29 |    1 |
|  1 |  TABLE ACCESS BY INDEX ROWID| TEST10        |    1 |    29 |    1 |
|*  2 |  INDEX RANGE SCAN          | IDX_TEST10_2  |    1 |      |    1 |
-----------------------------------------------------------------------------
删掉复合索引,建立一个固定值复合索引:
drop index idx_test10_2;
create index idx_test10_2 on test10(object_name,1);
检查SQL的执行计划:
-----------------------------------------------------------------------------
| Id  | Operation                  |  Name        | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |              |    1 |    31 |    1 |
|  1 |  TABLE ACCESS BY INDEX ROWID| TEST10        |    1 |    31 |    1 |
|*  2 |  INDEX RANGE SCAN          | IDX_TEST10_2  |    1 |      |    1 |
论坛徽章:
819
授权会员
日期:2007-08-10 01:06:30会员2007贡献徽章
日期:2007-09-26 18:42:10ITPUB元老
日期:2008-08-24 00:06:57 2014年世界杯参赛球队: 德国
日期:2014-07-09 15:28:06马上加薪
日期:2014-11-23 19:24:42马上有钱
日期:2014-12-21 16:14:33暖羊羊
日期:2015-03-04 14:50:372015年新春福章
日期:2015-03-06 11:57:31
发表于 2011-5-25 10:38 | 显示全部楼层
方法就是设置默认值,用一个特殊字符代替空值,此列禁止null值插入

[ 本帖最后由 tom0732 于 2011-5-25 10:39 编辑 ]

使用道具 举报

回复
论坛徽章:
9
授权会员
日期:2005-10-30 17:05:33奥运会纪念徽章:自行车
日期:2008-10-24 13:07:492010新春纪念徽章
日期:2010-03-01 11:20:052010数据库技术大会纪念徽章
日期:2010-05-13 09:34:23蜘蛛蛋
日期:2011-12-28 11:33:332012新春纪念徽章
日期:2012-01-04 11:49:54优秀写手
日期:2014-11-22 06:00:132015年新春福章
日期:2015-03-04 14:19:112015年新春福章
日期:2015-03-06 11:57:31
 楼主| 发表于 2011-5-25 10:46 | 显示全部楼层
在系统分析、代码、上线之前的阶段可以避免NULL,但很多时候,给我们优化的系统,都是生产系统。

使用道具 举报

回复
论坛徽章:
188
红宝石
日期:2014-05-09 08:24:37萤石
日期:2014-01-03 10:25:39奥运会纪念徽章:羽毛球
日期:2008-07-01 10:46:06奥运会纪念徽章:马术
日期:2008-07-07 17:43:24奥运会纪念徽章:射箭
日期:2008-07-25 18:07:39奥运会纪念徽章:皮划艇激流回旋
日期:2008-07-30 10:02:57奥运会纪念徽章:花样游泳
日期:2008-09-26 13:02:43奥运会纪念徽章:排球
日期:2008-12-03 11:23:272010新春纪念徽章
日期:2010-01-04 08:33:082010年世界杯参赛球队:澳大利亚
日期:2010-02-26 11:08:44
发表于 2011-5-25 11:33 | 显示全部楼层
》删掉复合索引,建立一个固定值复合索引:
》drop index idx_test10_2;
》create index idx_test10_2 on test10(object_name,1);

后面的放在在一些between的情况,可能不不使用。

使用道具 举报

回复
论坛徽章:
188
红宝石
日期:2014-05-09 08:24:37萤石
日期:2014-01-03 10:25:39奥运会纪念徽章:羽毛球
日期:2008-07-01 10:46:06奥运会纪念徽章:马术
日期:2008-07-07 17:43:24奥运会纪念徽章:射箭
日期:2008-07-25 18:07:39奥运会纪念徽章:皮划艇激流回旋
日期:2008-07-30 10:02:57奥运会纪念徽章:花样游泳
日期:2008-09-26 13:02:43奥运会纪念徽章:排球
日期:2008-12-03 11:23:272010新春纪念徽章
日期:2010-01-04 08:33:082010年世界杯参赛球队:澳大利亚
日期:2010-02-26 11:08:44
发表于 2011-5-25 11:34 | 显示全部楼层

使用道具 举报

回复
论坛徽章:
9
授权会员
日期:2005-10-30 17:05:33奥运会纪念徽章:自行车
日期:2008-10-24 13:07:492010新春纪念徽章
日期:2010-03-01 11:20:052010数据库技术大会纪念徽章
日期:2010-05-13 09:34:23蜘蛛蛋
日期:2011-12-28 11:33:332012新春纪念徽章
日期:2012-01-04 11:49:54优秀写手
日期:2014-11-22 06:00:132015年新春福章
日期:2015-03-04 14:19:112015年新春福章
日期:2015-03-06 11:57:31
 楼主| 发表于 2011-5-25 12:18 | 显示全部楼层
原帖由 lfree 于 2011-5-25 11:33 发表
》删掉复合索引,建立一个固定值复合索引:
》drop index idx_test10_2;
》create index idx_test10_2 on test10(object_name,1);

后面的放在在一些between的情况,可能不不使用。


多谢指点。

使用道具 举报

回复
论坛徽章:
58
生肖徽章2007版:马
日期:2009-11-06 23:12:33授权会员
日期:2013-01-10 14:38:592013年新春福章
日期:2013-02-25 14:51:24马自达
日期:2013-08-07 10:54:45红旗
日期:2013-08-09 13:48:48劳斯莱斯
日期:2013-09-12 15:56:37萤石
日期:2013-10-31 08:44:19优秀写手
日期:2013-12-18 09:29:13Jeep
日期:2014-01-14 10:53:432014年新春福章
日期:2014-02-18 16:43:09
发表于 2011-5-25 22:31 | 显示全部楼层
固定值复合索引第一次听说

能否解释一下

谢谢

使用道具 举报

回复
论坛徽章:
1088
金色在线徽章
日期:2007-04-25 04:02:08金色在线徽章
日期:2007-06-29 04:02:43金色在线徽章
日期:2007-03-11 04:02:02在线时间
日期:2007-04-11 04:01:02在线时间
日期:2007-04-12 04:01:02在线时间
日期:2007-03-07 04:01:022008版在线时间
日期:2010-05-01 00:01:152008版在线时间
日期:2011-05-01 00:01:342008版在线时间
日期:2008-06-03 11:59:43ITPUB年度最佳技术原创精华奖
日期:2013-03-22 13:18:30
发表于 2011-5-25 23:18 | 显示全部楼层
原帖由 yyp2009 于 2011-5-25 22:31 发表
固定值复合索引第一次听说

能否解释一下

谢谢

比如(name,0)啊,是个FBI索引,这样就保证这个B*索引会全部存储下来了,name没有null约束的时候,走不了索引,要走索引可以这样做

使用道具 举报

回复
论坛徽章:
58
生肖徽章2007版:马
日期:2009-11-06 23:12:33授权会员
日期:2013-01-10 14:38:592013年新春福章
日期:2013-02-25 14:51:24马自达
日期:2013-08-07 10:54:45红旗
日期:2013-08-09 13:48:48劳斯莱斯
日期:2013-09-12 15:56:37萤石
日期:2013-10-31 08:44:19优秀写手
日期:2013-12-18 09:29:13Jeep
日期:2014-01-14 10:53:432014年新春福章
日期:2014-02-18 16:43:09
发表于 2011-5-25 23:40 | 显示全部楼层
原帖由 dingjun123 于 2011-5-25 23:18 发表

比如(name,0)啊,是个FBI索引,这样就保证这个B*索引会全部存储下来了,name没有null约束的时候,走不了索引,要走索引可以这样做


哦!谢谢

不过怕null 妨碍性能

在10性能调整中有这样描述的:

Creating a table specifying NOT NULL for a column will cause NULL
values to be disallowed and eliminate the performance problems
associated with querying NULL values.

使用道具 举报

回复
招聘 : 数据库管理员
论坛徽章:
20
祖国60周年纪念徽章
日期:2009-10-09 08:28:00数据库板块每日发贴之星
日期:2011-02-20 01:01:01ITPUB季度 技术新星
日期:2011-04-02 10:31:09ITPUB十周年纪念徽章
日期:2011-11-01 16:24:042012新春纪念徽章
日期:2012-01-04 11:54:26玉石琵琶
日期:2012-02-21 15:04:38最佳人气徽章
日期:2012-03-13 17:39:18ITPUB 11周年纪念徽章
日期:2012-10-09 18:09:192013年新春福章
日期:2013-02-25 14:51:242011新春纪念徽章
日期:2011-02-18 11:43:33
发表于 2011-5-26 09:13 | 显示全部楼层
啥是FBI  ?  基于函数的index ?

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表