sql tuning--列是否为空is not null与索引index是否可用的关系

最新推荐文章于 2023-08-23 11:32:59 发布
最新推荐文章于 2023-08-23 11:32:59 发布
阅读量101 收藏
点赞数
文章标签： 数据库 后端

附件

前言

    sql是否可以使用索引，与很多因素有关，比如WHERE条件列的选择度，表及列的统计信息，可能还包括列是否为空，本文仅讨论索引是否为空，与索引是否使用的一些关系

测试结论

    
    1,对于select 索引列 from 表名，如果索引列为is null,仍不会使用索引
       即使强制使用index hint,也无法使用索引扫描
    2,原因在于索引的结构为B树索引，而B树索引里面不包括列为NULL值的数据信息，如果强制使用索引，会把NULL值对应的记录遗漏掉
    3,所以如果想在第1涉及的SQL使用索引，索引列必须定义时为IS NOT NULL或者在
       SQL WHERE条件列 IS NOT NULL

测试明细

   

SQL> create table t_null_nonnull(a int,b int);
Table created.
SQL> create table t_null_nonnull(a int,b int);
Table created.
SQL> desc t_null_nonnull;
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER(38)
B NUMBER(38)
SQL> insert into t_null_nonnull select level,level from dual connect by level<=10000;
10000 rows created.
SQL> commit;
Commit complete.
SQL> select a from t_null_nonnull;
10000 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1444422154
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 126K| 7 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T_NULL_NONNULL | 10000 | 126K| 7 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
689 consistent gets
0 physical reads
0 redo size
174298 bytes sent via SQL*Net to client
7845 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed
SQL>
SQL> create index idx_t_nonnull on t_null_nonnull(a);
Index created.
SQL> select a from t_null_nonnull;
10000 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1444422154
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 126K| 7 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T_NULL_NONNULL | 10000 | 126K| 7 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
689  consistent gets
0 physical reads
0 redo size
174298 bytes sent via SQL*Net to client
7845 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed
SQL>
SQL> select /*+ index(t_null_nonnull idx_t_nonnull) */ a from t_null_nonnull;
10000 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1444422154
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 126K| 7 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T_NULL_NONNULL | 10000 | 126K| 7 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
689 consistent gets
0 physical reads
0 redo size
174298 bytes sent via SQL*Net to client
7845 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed
SQL>

SQL> alter table t_null_nonnull modify a not null;
Table altered.
SQL> set linesize 30
SQL> desc t_null_nonnull;
Name Null? Type
----------------- -------- ------------
A NOT NULL NUMBER(38)
B NUMBER(38)
SQL> select a from t_null_nonnull;
10000 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1444422154
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 126K| 7 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T_NULL_NONNULL | 10000 | 126K| 7 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
689 consistent gets
0 physical reads
0 redo size
174298 bytes sent via SQL*Net to client
7845 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed
SQL>
SQL> select /*+ index(t_null_nonnull idx_t_nonnull) */ a from t_null_nonnull;
10000 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 4141558108
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 126K| 27 (0)| 00:00:01 |
| 1 | INDEX FULL SCAN | IDX_T_NONNULL | 10000 | 126K| 27 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
687 consistent gets
0 physical reads
0 redo size
174298 bytes sent via SQL*Net to client
7845 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed
SQL>
SQL> set autot off
SQL> truncate table t_null_nonnull;
Table truncated.
SQL> insert into t_null_nonnull select level,level from dual connect by level<=10000;
10000 rows created.
SQL> commit;
Commit complete.
SQL> select a from t_null_nonnull;
10000 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 315757037
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 126K| 7 (0)| 00:00:01 |
| 1 | INDEX FAST FULL SCAN| IDX_T_NONNULL | 10000 | 126K| 7 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
715 consistent gets
0 physical reads
0 redo size
174298 bytes sent via SQL*Net to client
7845 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed
SQL>
SQL> alter table t_null_nonnull modify a null;
Table altered.
SQL> select a from t_null_nonnull;
10000 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1444422154
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 126K| 7 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T_NULL_NONNULL | 10000 | 126K| 7 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
187 recursive calls
0 db block gets
739 consistent gets
0 physical reads
0 redo size
174298 bytes sent via SQL*Net to client
7845 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
10000 rows processed
SQL>
SQL> select a from t_null_nonnull where a is not null;
10000 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 315757037
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 126K| 7 (0)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| IDX_T_NONNULL | 10000 | 126K| 7 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A" IS NOT NULL)
Note
-----
- dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
715 consistent gets
0 physical reads
0 redo size
174298 bytes sent via SQL*Net to client
7845 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed
SQL>

个人简介

8年oracle从业经验，具备丰富的oracle技能，目前在国内北京某专业oracle服务公司从事高级技术顾问。
   
   服务过的客户：
          中国电信
          中国移动
          中国联通
          中国电通
          国家电网
          四川达州商业银行
          湖南老百姓大药房
          山西省公安厅
          中国邮政
          北京302医院     
          河北廊坊新奥集团公司
  
 项目经验:
           中国电信3G项目AAA系统数据库部署及优化
           中国联通CRM数据库性能优化
           中国移动10086电商平台数据库部署及优化
           湖南老百姓大药房ERR数据库sql优化项目
           四川达州商业银行TCBS核心业务系统数据库模型设计和RAC部署及优化
           四川达州商业银行TCBS核心业务系统后端批处理存储过程功能模块编写及优化
           北京高铁信号监控系统RAC数据库部署及优化
           河南宇通客车数据库性能优化
           中国电信电商平台核心采购模块表模型设计及优化
           中国邮政储蓄系统数据库性能优化及sql优化
           北京302医院数据库迁移实施
           河北廊坊新奥data guard部署及优化
           山西公安厅身份证审计数据库系统故障评估
           国家电网上海灾备项目4 node rac+adg 
          贵州移动crm及客服数据库性能优化项目
          贵州移动crm及客服务数据库sql审核项目
          深圳穆迪软件有限公司数据库性能优化项目
 联系方式：
          手机：18201115468
          qq   :   305076427
          qq微博: wisdomone1
          新浪微博：wisdomone9
          qq群：275813900    
          itpub博客名称：wisdomone1     http://blog.itpub.net/9240380/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-1694512/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-1694512/

clg10051
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
mysql中IS NULL、IS NOT NULL不能走索引？
luokn1995的博客
08-03 3443
mysql中IS NULL、IS NOT NULL不能走索引？ 不知道是啥原因也不知道啥时候, 江湖上流传着这么一个说法 mysql查询条件包含IS NULL、IS NOT NULL、!=、like %* 、like %*%,不能使用索引查询，只能使用全表扫描。 刚入行时我也是这么认为的，还奉为真理！ 但是时间工作中你会发现还是走索引啊！下面我们来一一探究其中的奥秘。 一、首先验证一下是会走索引的 创建一个表，结构如下： create table u
mysql IS NULL 使用索引
祈雨v的博客
08-28 1万+
简介 mysql的sql查询语句中使用is null、is not null、!=对索引并没有任何影响，并不会因为where条件中使用了is null、is not null、!=这些判断条件导致索引失效而全表扫描。 mysql官方文档也已经明确说明is null并不会影响索引的使用。 MySQL can perform the same optimization on col_name IS ...
数据列not null对索引影响一例
ciqu9915的博客
12-27 107
一些容易忽视的因素，往往是我们进行优化的方向。 Oracle数据列是有not null属性的，标志该列是否允许出现空值。如果插入、修改出现空值的情况，Oracle会拒绝当前的DML操作。 本质上看，数据列的not...
MySQL索引可能失效之or、is null、is not null、不等于(!=,＜＞)、联合索引
孟德尚且敬我，汝意欲何为
08-23 2458
MySQL索引可能失效之or、is null、is not null、联合索引
MySQL到底走不走索引
weixin_43846185的博客
07-28 3828
工作中，常常遇到sql走不走索引的问题，一般只要认为建了索引就会走，其实不然。本文较为深入地讲解了走索引的判断问题
sql性能的调整-总结(转自互联网)
03-01
- **SQL执行计划**：分析SQL的执行计划，查看是否正确使用了索引，以及是否存在全表扫描、排序等开销大的操作。 - **数据库日志分析**：通过分析慢查询日志，找出执行时间长的SQL进行优化。 3. **工具应用**： -...
SQL调优与案例分享
06-24
6. **多余查询条件的消除**：检查查询中是否包含不必要的`NOT NULL`条件等，这些可能会导致额外的开销。 7. **程序中修改动态SQL**：针对动态生成的SQL语句进行优化，确保其遵循最佳实践。 8. **有效利用索引**：...
Oracle+SQL优化
08-22
**2.1 ISNULL与ISNOTNULL** - **问题**：使用`IS NULL`或`IS NOT NULL`会导致Oracle优化器放弃使用索引。 - **建议**：尽可能使用`= 'some value'`或`<> 'some value'`来代替`IS NULL`或`IS NOT NULL`，这样可以...
ORACLE中SQL查询语句的优化.pdf
最新发布
01-04
EMPNO NUMBER(4) NOT NULL, ENAME CHAR(10), JOB CHAR(9), MGRNO NUMBER(4), HIREDATE DATE, SAL NUMBER(7,2), COMM NUMBER(7,2), DEPTNO CHAR(4) ); CREATE UNIQUE INDEX EMP_U1 ON EMP (EMPNO); CREATE ...
mysql索引失效
沐雨橙风ιε的博客
08-10 1513
mysql索引失效
MySQL中IS NULL、IS NOT NULL、|=索引问题
一树一菩提的博客
03-17 2470
看面试题的时候，总能看到MySQL在什么情况下用不上索引，如下： MySQL的WHERE子句中包含 IS NULL、IS NOT NULL、!= 这些条件时便不能使用索引查询，只能使用全表扫描。 不耽误大家时间，告诉大家结论： MySQL中决定使不使用某个索引执行查询的依据就是成本够不够小，如果null值很多，还是会用到索引的。 自己做了个验证： 一个大概3万数据的表，如果只有10多个记录是null值，is null走索引，not null和!=没走索引，如果大部分都...
SQL SERVER 中is null 和 is not null 将会导致索引失效吗？
荷塘月色
06-01 3953
其实本来这个问题没有什么好说的，今天优化的时候遇到一个SQL语句，因为比较有意思，所以我截取、简化了SQL语句，演示给大家看，如下所示
Oracle SQL中IS NOT NULL与!=NULL的区别
热门推荐
freshlover的专栏
05-25 1万+
平时经常会遇到这两种写法：IS NOT NULL与!=NULL。也经常会遇到数据库有符合条件!=NULL的数据，但是返回为空集合。实际上，是由于对二者使用区别理解不透彻。 默认情况下，推荐使用 IS NOT NULL去做条件判断，因为SQL默认情况下对WHERE XX！= Null的判断会永远返回0行，却不会提示语法错误。 这是为什么呢？ SQL Server文档中对Null值的比较运算定义
mysql 给几个主键值 批量校验是否存在_MySQL中IS NULL、IS NOT NULL、!=不能用索引？胡扯！
weixin_39869791的博客
10-21 643
不知道从什么时候开始，网上流传着这么一个说法：MySQL的WHERE子句中包含 IS NULL、IS NOT NULL、!= 这些条件时便不能使用索引查询，只能使用全表扫描。这种说法愈演愈烈，甚至被很多同学奉为真理。咱啥话也不说，举个例子。假如我们有个表s1，结构如下：CREATE 这个表里有10000条记录：mysql下边我们直接贴几个图：上边几个查询语句的WHERE子句中用了IS NULL、I...
Mysql中IS NULL、IS NOT NULL不能走索引？
weixin_46062001的博客
08-16 1460
mysql中IS NULL、IS NOT NULL不能走索引？ 不知道是啥原因也不知道啥时候, 江湖上流传着这么一个说法 mysql查询条件包含IS NULL、IS NOT NULL、!=、like %* 、like %*%,不能使用索引查询，只能使用全表扫描。 刚入行时我也是这么认为的，还奉为真理！ 但是时间工作中你会发现还是走索引啊！下面我们来一一探究其中的奥秘。 一、首先验证一下是会走索引的 创建一个表，结构如下： create table user_info( ...
深度解析Prompt-Tuning：微调新范式与应用探索
在本文中，我们将深度探讨Prompt-Tuning，这是一种全新的微调范式，尤其在深度学习领域，如大型语言模型（LLM）、自动创意生成内容（AIGC）以及如何利用这些模型构建特定领域的应用。作者王嘉宁通过NewBeeNLP公众号...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值