精华内容
下载资源
问答
  • MySQL查询优化之九-IS NULL优化(IS NULL Optimization) 如需转载请标明出处:http://blog.csdn.net/itas109 QQ技术交流群:12951803 环境: MySQL版本:5.5.15 操作系统:windows 本文讨论IS NULL优化(IS ...

    MySQL查询优化之九-IS NULL优化(IS NULL Optimization)


    如需转载请标明出处:http://blog.csdn.net/itas109
    QQ技术交流群:129518033

    环境:
    MySQL版本:5.5.15
    操作系统:windows

    本文讨论IS NULL优化(IS NULL Optimization)。

    MySQL可以对col_name = constant_value使用col_name IS NULL来执行相同的优化。 例如,MySQL可以使用索引和范围来用IS NULL搜索NULL。

    例如:

    SELECT * FROM tbl_name WHERE key_col IS NULL;
    
    SELECT * FROM tbl_name WHERE key_col <=> NULL;
    
    SELECT * FROM tbl_name
      WHERE key_col=const1 OR key_col=const2 OR key_col IS NULL;

    如果WHERE子句对于声明为NOT NULL的列包含col_name IS NULL条件,则会优化该表达式。 这种优化不会发生在列可能产生NULL的情况下; 例如,如果它来自LEFT JOIN右侧的表格。

    MySQL还可以优化组合col_name = expr或col_name IS NULL,这是解析子查询中常见的一种形式。 当使用此优化时,EXPLAIN显示ref_or_null。

    此优化可以处理任何关键部件的一个IS NULL。

    假设在表t2的列a和b上有一个索引,优化查询的一些示例:

    SELECT * FROM t1 WHERE t1.a=expr OR t1.a IS NULL;
    
    SELECT * FROM t1, t2 WHERE t1.a=t2.a OR t2.a IS NULL;
    
    SELECT * FROM t1, t2
      WHERE (t1.a=t2.a OR t2.a IS NULL) AND t2.b=t1.b;
    
    SELECT * FROM t1, t2
      WHERE t1.a=t2.a AND (t2.b=t1.b OR t2.b IS NULL);
    
    SELECT * FROM t1, t2
      WHERE (t1.a=t2.a AND t2.a IS NULL AND ...)
      OR (t1.a=t2.a AND t2.a IS NULL AND ...);

    ref_or_null首先对引用键进行读取操作,然后单独搜索具有NULL键值的行。

    优化只能处理一个IS NULL级别。 在以下查询中,MySQL仅对表达式(t1.a = t2.a AND t2.a IS NULL)使用键查找,并且无法使用b:

    SELECT * FROM t1, t2
      WHERE (t1.a=t2.a AND t2.a IS NULL)
      OR (t1.b=t2.b AND t2.b IS NULL);

    Reference:
    https://dev.mysql.com/doc/refman/5.5/en/is-null-optimization.html


    觉得文章对你有帮助,可以用微信扫描二维码捐赠给博主,谢谢!
    微信
    如需转载请标明出处:http://blog.csdn.net/itas109
    QQ技术交流群:129518033

    展开全文
  • mysql IS NULL 使用索引

    千次阅读 2019-08-28 18:58:51
    mysql的sql查询语句中使用is nullis not null、!=对索引并没有任何影响,并不会因为where条件中使用了is nullis not null、!=这些判断条件导致索引失效而全表扫描。 mysql官方文档也已经明确说明is null并不会...

    简介

    mysql的sql查询语句中使用is nullis not null!=对索引并没有任何影响,并不会因为where条件中使用了is nullis not null!=这些判断条件导致索引失效而全表扫描。

    mysql官方文档也已经明确说明is null并不会影响索引的使用。

    MySQL can perform the same optimization on col_name IS NULL that it can use for col_name = constant_value. For example, MySQL can use indexes and ranges to search for NULL with IS NULL.

    事实上,导致索引失效而全表扫描的通常是因为一次查询中回表数量太多。mysql计算认为使用索引的时间成本高于全表扫描,于是mysql宁可全表扫描也不愿意使用索引。

    案例

    CREATE TABLE `user_info` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(11) DEFAULT NULL,
      `age` int(4) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `index_name` (`name`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    
    INSERT INTO `user_info` (`id`, `name`, `age`) VALUES ('1', 'tom', '18');
    INSERT INTO `user_info` (`id`, `name`, `age`) VALUES ('2', null, '19');
    INSERT INTO `user_info` (`id`, `name`, `age`) VALUES ('3', 'cat', '20');
    

    执行sql查询时使用is nullis not null,发现依然使用的索引查询,并没有出现索引失效的问题。

    在这里插入图片描述

    在这里插入图片描述

    分析

    分析上述现象,则需要详细了解mysql索引的工作原理以及索引数据结构。下面,分别通过工具解析和直接查看二进制文件两种方式分别分析mysql索引数据结构。

    工具解析

    innodb_ruby是一个非常强大的mysql分析工具,可以用来轻松解析mysql的.ibd文件进而深入理解mysql的数据结构。

    首先安装innodb_ruby工具:

    yum install -y rubygems ruby-deve
    gem install innodb_ruby
    

    innodb_ruby的功能很多,此处我们只需要用来解析mysql的索引结构,因此只需要如下的命令即可。更多的功能和命令详见wiki

    innodb_space -s ibdata1 -T sakila/film -I PRIMARY index-recurse
    

    解析主键索引:

    $ innodb_space -s /usr/soft/mysql-5.6.31/data -T test/user_info -I PRIMARY index-recurse
    ROOT NODE #3: 3 records, 89 bytes
      RECORD: (id=1) → (name="tom", age=18)
      RECORD: (id=2) → (name=:NULL, age=19)
      RECORD: (id=3) → (name="cat", age=20)
    

    解析普通索引index_name

    $ innodb_space -s /usr/soft/mysql-5.6.31/data -T test/user_info -I index_name index-recurse
    ROOT NODE #4: 3 records, 38 bytes
      RECORD: (name=:NULL) → (id=2)
      RECORD: (name="cat") → (id=3)
      RECORD: (name="tom") → (id=1)
    

    通过解析工具数据mysql的索引结构可以发现,null值也被储存到了索引树中,并且null值被处理成最小的值放在index_name索引树的最左侧。

    二进制文件

    找到user_info表对应的物理文件user_info.ibd,通过软件例如UltraEdit打开,直接定位到第5个数据页(mysql默认一个数据页占用16KB)。

    在这里插入图片描述

    如图,这些二进制数据就是index_name索引对应的索引页数据,只挑选其中的索引记录,展开如下:

    最小记录0x00010063

    01 B2 01 00 02 00 29 	记录头信息
    69 6E 66 69 6D 75 6D 	最小记录(固定值infimum)
    

    最大记录0x00010070

    00 04 00 0B 00 00 		记录头信息
    73 75 70 72 65 6D 75 6D 最大记录(固定值supremum)
    

    ID为1的索引0x0001007f

    03 00 00 00 10 FF F1 	记录头信息
    74 6F 6D 				字段name的值:tom
    80 00 00 01 			RowID:主键id的值为1
    

    ID为2的索引0x0001008c

    01 00 00 18 00 0B 		记录头信息
    						字段name的值:null
    80 00 00 02				RowID:主键id的值为2
    

    ID为3的索引0x00010097

    03 00 00 00 20 FF E8 	记录头信息
    63 61 74 				字段name的值:cat
    80 00 00 03 			RowID:主键id的值为3
    

    最小记录的记录头信息最后2字节00 29 -> 0x00010063偏移0x0029 -> 0x0001008C,即ID为2的索引位置;

    ID为2的记录头信息最后2字节00 0B -> 0x0001008C偏移0x000B -> 0x00010097,即ID为3的索引位置;

    ID为3的记录头信息最后2字节FF E8 -> 0x00010097偏移0xFFE8 -> 0x0001007F,即ID为1的索引位置;

    ID为1的记录头信息最后2字节FF F1 -> 0x0001007F偏移0xFFF1 -> 0x00010070,最大记录的记录位置;

    由此可见索引记录是通过单向链表并以索引值排序串联在一起,而null值被处理成最小的值放在了索引链表的最开始位置,也就是索引树的最左侧。与innodb_ruby工具解析出来的结果一致。

    误解原因

    为何大众误解认为is nullis not null!=这些判断条件会导致索引失效而全表扫描呢?

    导致索引失效而全表扫描的通常是因为一次查询中回表数量太多。mysql计算认为使用索引的时间成本高于全表扫描,于是mysql宁可全表扫描也不愿意使用索引。使用索引的时间成本高于全表扫描的临界值可以简单得记忆为20%左右。

    详细的分析过程可以见笔者的另一篇博客:mysql回表致索引失效

    也就是如果一条查询语句导致的回表范围超过全部记录的20%,则会出现索引失效的问题。而is nullis not null!=这些判断条件经常会出现在这些回表范围很大的场景,然后被人误解为是这些判断条件导致的索引失效。

    复现索引失效

    复现索引失效,只需要回表范围超过全部记录的20%,如下插入1000条非null记录。

    delimiter  //
    CREATE PROCEDURE init_user_info() 
    BEGIN 
    	DECLARE indexNo INT;
    	SET indexNo = 0;
    	WHILE indexNo < 1000 DO
    		START TRANSACTION; 
    			insert into user_info(name,age) values (concat(floor(rand()*1000000000)),floor(rand()*100));
    			SET indexNo = indexNo + 1;
    		COMMIT; 
    	END WHILE;
    END //
    delimiter ;
    call init_user_info();
    

    此时user_info表中一共有1003条记录,其中只有1条记录的name值为null。那么is null判断语句导致的回表记录只有1/1003不会超过临界值,而is not null判断语句导致的回表记录有1002/1003远远超过临界值,将出现索引失效的现象。

    由下两图也可以见,is null依然正常使用索引,而is not null如预期由于回表率太高而宁可全表扫描也不使用索引。

    在这里插入图片描述

    在这里插入图片描述

    使用mysql的optimizer tracing(mysql5.6版本开始支持)功能来分析sql的执行计划:

    SET optimizer_trace="enabled=on";
    explain select * from user_info where name is not null;
    SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
    

    optimizer tracing输出的执行计划可见,该查询下,使用全表扫描所需要的时间成本为206.9;而使用索引所需要的时间成本为1203.4,远远高于全表扫描。因此mysql最终选择全表扫描而出现索引失效的现象。

    {
        "rows_estimation": [
            {
                "table": "`user_info`",
                "range_analysis": {
                    "table_scan": {
                        "rows": 1004,   // 全表扫描需要扫描1004条记录
                        "cost": 206.9   // 全表扫描需要的成本为206.9
                    },
                    "potential_range_indices": [
                        {
                            "index": "PRIMARY",
                            "usable": false,
                            "cause": "not_applicable"
                        },
                        {
                            "index": "index_name",
                            "usable": true,
                            "key_parts": [
                                "name",
                                "id"
                            ]
                        }
                    ],
                    "setup_range_conditions": [],
                    "group_index_range": {
                        "chosen": false,
                        "cause": "not_group_by_or_distinct"
                    },
                    "analyzing_range_alternatives": {
                        "range_scan_alternatives": [
                            {
                                "index": "index_name",
                                "ranges": [
                                    "NULL < name"
                                ],
                                "index_dives_for_eq_ranges": true,
                                "rowid_ordered": false,
                                "using_mrr": false,
                                "index_only": false,
                                "rows": 1002,   // 索引需要扫描1002条记录
                                "cost": 1203.4, // 索引需要的成本为1203.4
                                "chosen": false,
                                "cause": "cost"
                            }
                        ],
                        "analyzing_roworder_intersect": {
                            "usable": false,
                            "cause": "too_few_roworder_scans"
                        }
                    }
                }
            }
        ]
    }
    
    展开全文
  • ISNULL的用法

    千次阅读 2019-09-05 09:34:48
    ISNULL(参数1,参数2),判断参数1是否为NULL,如果是,返回参数2,否则返回参数1。 存储过程中使用,看懂下面的例子你就会了 IF ISNULL(@参数,’ ‘)<>’ ’ ...

    ISNULL(参数1,参数2),判断参数1是否为NULL,如果是,返回参数2,否则返回参数1。
    存储过程中使用,看懂下面的例子你就会了
    IF ISNULL(@参数,’ ‘)<>’ ’

    例如

    isnull(SUM(CASE WHEN CostType!=7THEN  FtAmountELSE 0 end) , 0) AS  FtAmount

    展开全文
  • Oracle中模拟SQL中的isnull函数

    千次阅读 2019-10-30 21:37:25
    程序从MSSQL移植到ORACLE,面临大面积的SQL语句修改,其中用的最多的莫非isnull,虽然oracle中有nvl ,nullif, is null等函数,但却没有isnull。自己写一个吧,但是因为类似ISNULL(),NVL()的函数入参和返回值的数据...

    程序从MS SQL移植到ORACLE,面临大面积的SQL语句修改,其中用的最多的莫非isnull,虽然oracle中有nvl ,nullif, is null等函数,但却没有isnull。自己写一个吧,但是因为类似ISNULL(),NVL()的函数入参和返回值的数据类型都并不确定,要如何定义类型?姑且用varchar2吧:请看下面测试代码

    --创建isnull函数
    create or replace function isnull(i_obj in varchar2, i_obj2 in varchar2)
    return varchar2
    is
    begin
      return nvl(i_obj, i_obj2);
    end isnull;
    
    --测试建表语句
    CREATE TABLE tUsers(
         UserName VARCHAR2(10),
              AGE NUMBER,
            stime date
     ); 
     
    --插入测试数据 
     INSERT INTO tUsers(UserName,age) VALUES(null,22);
     INSERT INTO tUsers(UserName,age,stime)VALUES('你好123',18,SYSDATE);
     INSERT INTO tUsers(UserName,age) VALUES(null,26);
     INSERT INTO tUsers(UserName,age,stime)VALUES('好123',38,SYSDATE);
     INSERT INTO tUsers(UserName,age) VALUES(null,24);
     INSERT INTO tUsers(UserName,age,stime)VALUES('你123',28,SYSDATE);
     
     --查看建的表结构
     SELECT table_name, column_name, data_type,DATA_LENGTH,COLUMN_ID FROM USER_TAB_COLUMNS WHERE table_name =upper('tusers')
    --查看测试数据
    select * from tUsers
     
     --测试语句
     select * from tUsers where isnull(age,0)=0  --查询所有age字段为null的记录,成功!
     select username,isnull(age,0),stime from tUsers  --如果age字段为Null则返回number型0,成功!
     select username,isnull(age,'未知年纪'),stime from tUsers  --如果age字段为Null则返回VARCHAR型'未知年纪',成功!
     select username,age,isnull(stime,'未知时间') from tUsers  --如果stime字段为Null则返回VARCHAR型'未知时间',成功!
     --如果stime字段为Null则返回VARCHAR型'未知时间',否则返回格式化后的stime, 成功!
     select username,age,case when isnull(stime,' ')=' ' then '未知时间' else to_char(stime,'yyyy-mm-dd hh24:mi:ss') end as sstime from tUsers 
     --如果stime字段为Null则返回VARCHAR型'未知时间',否则返回stime,不成功!
     select username,age,case when isnull(stime,' ')=' ' then '未知时间' else stime end as sstime from tUsers  
     select username,age,nvl(stime,'未知时间') from tUsers  --NVL判断如果stime字段为Null则返回VARCHAR型'未知时间',不成功!
    
    --删除测试表和数据
    DROP TABLE tusers;

    通过测试数据可以看出,自定义isnull函数入参定义为varchar2类型, 基本常用的数据类型都可以实现隐式转换,其中包括varchar,varchar2,nvarchar2,number,date等。

    展开全文
  • mysql ISNULL()和is null区别

    万次阅读 2019-04-01 15:16:47
    语法: is null select * from 表名 ...select * from 表名 where ISNULL(字段名); 比较区别 我的数据库表的数据量(146671条) 执行sql select * from fts_srv_dtr_f where ISNULL(BRANCH_NAME); select...
  • MySQL 优化 —— IS NULL 优化

    千次阅读 2020-01-23 13:12:44
    本博客翻译自 MySQL 官网:IS NULL Optimization, MySQL版本 5.7。 MySQL 对 IS NULL 的优化 MySQL 可以对 IS NULL 执行和常量等值判断(列名= 常量表达式,如name = 'Tom')相同的优化。MySQL 可以利用索引和...
  • Mysql中is nullis not null的使用

    千次阅读 2020-03-17 16:51:28
    MySQL 使用 SQL SELECT 命令及 WHERE 子句来读取数据表中的数据,...IS NULL: 当列的值是 NULL,此运算符返回 true。 IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。 <=>: 比较操作符(不同于 = 运算符)...
  • connection holder is null

    万次阅读 2018-05-24 17:21:15
    1 连接被回收(1)错误日志Caused by: java.sql.SQLException: connection holder is nullat com.alibaba.druid.pool.DruidPooledConnection.checkState(DruidPooledConnection.java:1085)at ...
  • mysql isnull()用法

    万次阅读 2019-06-24 21:04:54
    1.isnull(exper) 判断exper是否为空,是则返回1,否则返回0 2.ifnull(exper1,exper2)判断exper1是否为空,是则用exper2代替 3.nullif(exper1,exper2)如果expr1=expr2成立,那么返回值为NULL,否则返回值为expr1。 ...
  • NULL值和ISNULL函数

    千次阅读 2019-06-18 22:25:34
    NULL NULL 表示"没有值"。 NULL并不等同于0或者空格。 在查询过程中用两个字段进行比较时,如果出现null值,就不能再用等号进行比较。 原因是:Null 值代表的是一个缺少的值或不可知的值,所以无法判断一个不可知的...
  • python isnull的使用

    千次阅读 2020-06-08 11:06:21
    #用于查看data中呼出时间字段(也可不加呼出时间字段,则是所有字段都是False才成立) data_notnull=data[data.呼出时间.isnull()==False] data.isnull().sum() #用于查看data各列中各有多少缺失值
  • ISNULL函数

    千次阅读 2018-07-31 10:10:21
    ISNULL()函数,不会修改原表里面的数据,只是修改了视图显示 
  • 如果close(),报错:java.sql.SQLException: connection holder is null。 参考代码,如下: package com.xxx.common.utils; @Service("dataSourceUtil") public class DataSourceUtil { protected ...
  • SELECT ISNULL(NULL,0) WHERE ISNULL(NULL,0)=0 SELECT ISNULL(' ',0) WHERE ISNULL(' ',0)=0 SELECT ISNULL(NULL,0) WHERE ISNULL(NULL,0)='0' SELECT ISNULL(' ',0) WHERE ISNULL(' ',0)='0' 上面sql看似没有多...
  • inStream parameter is null问题解决方案

    千次阅读 2020-12-10 15:03:27
    java.lang.NullPointerException: inStream parameter is null 我的错误实例图: 出现原因: 更具代码获取的值进行分析,我没有进入dao 现在没有链接但数据库 但我也肯达没有代码上的任何错误 。。。。。。。。。。...
  • Mysql where条件中IS NULL 是否走索引?

    千次阅读 2020-11-20 18:01:29
    `id` bigint(18) unsigned NOT NULL AUTO_INCREMENT COMMENT '数据库主键', `kid` bigint(18) unsigned NOT NULL COMMENT '业务主键唯一标识', `tenant_id` varchar(32) DEFAULT NULL COMMENT '租户标识', `c...
  • mysql =nullis null

    千次阅读 2019-10-10 15:12:49
    如果这个字段值为空 where a=null 查不到你想要的结果 只能是is null
  • SQL中 isnull()用法总结

    万次阅读 2019-11-14 17:44:48
    SQL Serve中的isnull()函数: isnull(value1,value2) 1、value1与value2的数据类型必须一致。 2、如果value1的值不为null,结果返回value1。 3、如果value1为null,结果返回vaule2的值。vaule2是你设定的值...
  • //在js中if条件为null、undefined、0、NaN、""、false,表达式时,统统被解释为...function isNull(str) { return !str && str!==0 && typeof str!=="boolean"?true:false; } //alert(isNull(nu...
  • 因为这个行为,IS NULLIS NOT NULL 并不总是为行值表达式返回相反的值,也就是, 一个同时包含NULL和non-null值的行值表达式将在两种情况下都返回false。 这个规定符合 SQL 标准,但是与PostgreSQL之前的版本不...
  • Access与SQL中的IsNull(),IS NULL的区别

    千次阅读 2018-06-13 18:00:58
    Access也有IsNull函数,但意义和参数却和T-SQL中的不同。在T-SQL(也就是SQL Server所支持的SQL语言)中,IsNull的作用是把空值替代成指定的值。然而在Access中,IsNull的作用仅仅是判断是否为空值,而且参数的个数...
  • 第一步,is NULL要比ISNULL()的比较 SELECT * from 表名 where 字段名 is NULL 第二步,is NULL和IFNULL()的比较 SELECT * from 表名 where 字段名 is NULL 由上面可以看出,可看出IFNULL()要比is NULL快...
  • predicate = cb.and(predicate,cb.isNull(root.get("accountsEntity"))); predicate = cb.and(predicate,cb.isEmpty(root.get("receiptsCostList"))); query.where(predicate); return query.getRestriction(); ...
  • sql 替换null值之ISNULL()函数的详解

    千次阅读 2018-12-13 14:15:44
     在sql使用中,有时候,我们需要对查询到null值进行替换为其他具体的值时,可以使用isnull()函数进行对null值替换。 1.格式:isnull(expression,replacement_value) 说明: expression:为用于检索是否值为null...
  • PostgreSQL 类似SQLSERVER的isnull函数

    千次阅读 2020-04-03 17:30:32
    类似的可以用coalesce函数。 Pgsql 里面 COALESCE的用法
  • target is null for method size

    千次阅读 2019-07-25 09:50:13
    Cause: java.lang.NullPointerException: target is null for method size org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException: ### Error quer.....
  • TypeError: data is null

    千次阅读 2019-08-01 09:57:59
    前端页面的数据无法显示。...后端url的没有返回JSON而是null。验证了的确是后端错误。跑一下单元测试 原来我修改了数据库的字段,而没有修改SQL语句。修改SQL 再次印证了 :万物终有其根源。 ...
  • is nullis not null运算符

    万次阅读 2018-05-17 19:52:10
    数据库某个字段的值为NULL很诡异,也经常是大家讨论的话题,所以围绕NULL的两个运算符不得不专门提一下。mysql&gt; select * from tmp ; +------+----------+ | id | name | +------+----------+ | 2 | lisi | |...
  • TypeError: t is null

    千次阅读 2019-07-07 19:48:00
    原因1:layui版本太低,因为table是在layui2.0才有的 转载于:https://www.cnblogs.com/HuangJie-sol/p/11147415.html
  • 原文地址:https://stackoverflow.com/questions/4057196/how-do-you-query-this-in-mongo-is-not-null 插入三条数据 db.test.insert({"num":1, "check":"check value"}); db.test.insert({"num":2, "check":...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 2,371,971
精华内容 948,788
关键字:

isnull