精华内容
下载资源
问答
  • 一.首先看一个我在某公众号看到的一个关于数据库优化的举措二....先列出结论:where子句中使用上述对null的判断,如果判断的列设置了索引,那就可以使用到索引三.测试:1.建表1 CREATE TABLE`test_null_index` (2 `i...

    一.首先看一个我在某公众号看到的一个关于数据库优化的举措

    20181105132637699321.png

    二.如果where子句中查询的列执行了 “is null” 或者 “is not null” 或者 “<=> null” 会不会使用索引呢?

    先列出结论:where子句中使用上述对null的判断,如果判断的列设置了索引,那就可以使用到索引

    三.测试:

    1.建表

    1 CREATE TABLE`test_null_index` (2 `id` int(11) DEFAULT NULL,3 `mark` varchar(20) DEFAULT NULL,4 `name` varchar(11) DEFAULT NULL

    5 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    2.插数据

    create procedure test_null_index(in num int)BEGIN

    DECLARE i int;set i=1;while (i

    DOif mod(i,10)!=0 then

    insert into test_null_index values (i,concat(‘aaa‘,i),null);else

    insert into test_null_index values (null,concat(‘aaa‘,i),‘bbb‘);end if;set i=i+1;END while;END;

    call test_null_index(10000);

    3.没加索引时,type为All,全表扫描

    mysql> explain SELECT * from test_null_index WHERE id is null;+----+-------------+-----------------+------+---------------+------+---------+------+-------+-------------+

    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

    +----+-------------+-----------------+------+---------------+------+---------+------+-------+-------------+

    | 1 | SIMPLE | test_null_index | ALL | NULL | NULL | NULL | NULL | 10589 | Using where |

    +----+-------------+-----------------+------+---------------+------+---------+------+-------+-------------+

    1 row in set (0.00 sec)

    4.加上索引,可以看到,索引起作用了

    mysql> create index idx_test_null ontest_null_index(id);

    Query OK,0 rows affected (0.12sec)

    Records:0 Duplicates: 0 Warnings: 0mysql> explain SELECT * from test_null_index WHERE id is null;+----+-------------+-----------------+------+---------------+---------------+---------+-------+------+-------------+

    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

    +----+-------------+-----------------+------+---------------+---------------+---------+-------+------+-------------+

    | 1 | SIMPLE | test_null_index | ref | idx_test_null | idx_test_null | 5 | const | 998 | Using where |

    +----+-------------+-----------------+------+---------------+---------------+---------+-------+------+-------------+

    1 row in set (0.00 sec)

    四.注意,只能使用针对一个字段的关于null的判断,如果同时在两个字段对null进行判断,还是会走全表扫描

    1.测试,可以看到,在name字段加上索引,并查询name为空的语句,同样会走索引

    mysql> create index idx_test_null2 ontest_null_index(name);

    Query OK,0 rows affected (0.13sec)

    Records:0 Duplicates: 0 Warnings: 0mysql> explain SELECT * from test_null_index WHERE name is null;+----+-------------+-----------------+------+----------------+----------------+---------+-------+------+-------------+

    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

    +----+-------------+-----------------+------+----------------+----------------+---------+-------+------+-------------+

    | 1 | SIMPLE | test_null_index | ref | idx_test_null2 | idx_test_null2 | 36 | const | 8994 | Using where |

    +----+-------------+-----------------+------+----------------+----------------+---------+-------+------+-------------+

    1 row in set (0.00 sec)

    2.同时针对id和name查询为空的语句,走的是全表扫描

    mysql> explain SELECT * from test_null_index WHERE id is null or name is null;+----+-------------+-----------------+------+------------------------------+------+---------+------+-------+-------------+

    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

    +----+-------------+-----------------+------+------------------------------+------+---------+------+-------+-------------+

    | 1 | SIMPLE | test_null_index | ALL | idx_test_null,idx_test_null2 | NULL | NULL | NULL | 10589 | Using where |

    +----+-------------+-----------------+------+------------------------------+------+---------+------+-------+-------------+

    1 row in set (0.01 sec)

    展开全文
  • # 索引不会包含有NULL值的列只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。在很多库表设计...

    # 索引不会包含有NULL值的列

    只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

    在很多库表设计规范、某某军规的文章中,是不是经常会看到类似这样的内容。小编也经常看到这样的内容,并且在编写规范的时候,准备也把这一条加进去。但在按部就班之余,小编抽空验证了一下,发现事实却并非如此!

    小编使用的MySQL版本是社区版 5.7.21

    新建测试表 t1,插入不含NULL值得100行数据,然后插入1行带NULL的数据 insert into t1(id) values(101); 表中有主键id,索引a

    CREATE TABLE`t1` (

    `id`int(11) NOT NULL,

    `a`int(11) DEFAULT NULL,

    `b`int(11) DEFAULT NULL,PRIMARY KEY(`id`),KEY`a` (`a`)

    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

    测试1,包含NULL单列索引的查询,可以看到即使是查找 IS NULL的行,也是可以用上索引的

    测试1:

    desc select * from t1 where a > 82;+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+

    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

    +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+

    | 1 | SIMPLE | t1 | NULL | range | a | a | 5 | NULL | 18 | 100.00 | Using index condition |

    +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+

    desc select * from t1 where a is NULL;+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+

    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

    +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+

    | 1 | SIMPLE | t1 | NULL | ref | a | a | 5 | const | 1 | 100.00 | Using index condition |

    +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+

    desc select * from t1 where a = 20 or a is null;+----+-------------+-------+------------+-------------+---------------+--------+---------+-------+------+----------+--------------------------+

    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

    +----+-------------+-------+------------+-------------+---------------+--------+---------+-------+------+----------+--------------------------+

    | 1 | SIMPLE | t1 | NULL | ref_or_null | idx_ab | idx_ab | 5 | const | 2 | 100.00 | Using where; Using index |

    +----+-------------+-------+------------+-------------+---------------+--------+---------+-------+------+----------+--------------------------+

    注意对 NULL 值的检索只能使用 is null / is not null / <=>,不能使用=,这样的运算符(mysql中可以用a <=> NULL 表示查找 a is NULL'的行)

    测试2,包含NULL复合索引的查询,首先加一个复合索引 alter table t1 drop index a,add index idx_ab(a,b); 可以看到不管是指定 a is null ,或者指定 b is null ,都可以利用上索引 idx_ab(key_len 可以看出)

    测试2:

    desc select * from t1 where a=50 and b>20;+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+--------------------------+

    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

    +----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+--------------------------+

    | 1 | SIMPLE | t1 | NULL | range | idx_ab | idx_ab | 10 | NULL | 1 | 100.00 | Using where; Using index |

    +----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+--------------------------+

    desc select * from t1 where a=50 and b is null;+----+-------------+-------+------------+------+---------------+--------+---------+-------------+------+----------+--------------------------+

    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

    +----+-------------+-------+------------+------+---------------+--------+---------+-------------+------+----------+--------------------------+

    | 1 | SIMPLE | t1 | NULL | ref | idx_ab | idx_ab | 10 | const,const | 1 | 100.00 | Using where; Using index |

    +----+-------------+-------+------------+------+---------------+--------+---------+-------------+------+----------+--------------------------+

    desc select * from t1 where a is null and b>20;+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+--------------------------+

    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

    +----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+--------------------------+

    | 1 | SIMPLE | t1 | NULL | range | idx_ab | idx_ab | 10 | NULL | 1 | 100.00 | Using where; Using index |

    +----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+--------------------------+

    desc select * from t1 where a is null and b is null;+----+-------------+-------+------------+------+---------------+--------+---------+-------------+------+----------+--------------------------+

    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

    +----+-------------+-------+------------+------+---------------+--------+---------+-------------+------+----------+--------------------------+

    | 1 | SIMPLE | t1 | NULL | ref | idx_ab | idx_ab | 10 | const,const | 1 | 100.00 | Using where; Using index |

    +----+-------------+-------+------------+------+---------------+--------+---------+-------------+------+----------+--------------------------+

    由此,只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。这句的前半句是不对的(可参考官网说明: https://dev.mysql.com/doc/refman/5.7/en/is-null-optimization.html),但是后半句的结论确是可以采纳的。

    虽然MySQL可以在含有null的列上使用索引,但不代表null和其他数据在索引中是一样的。不建议列上允许为空,最好限制 not null ,并设置一个默认值,比如0和''空字符串等,如果是datetime类型,可以设置成'1970-01-01 00:00:00'这样的值。对MySQL来说,null 是一个特殊的值,Conceptually, NULL means “a missing unknown value” and it is treated somewhat differently from other values。 对null做算术运算的结果都是null,count时不会包括null行,null 比空字符串需要更多的存储空间等。

    附:上面说到可用通过 key_len 看出使用了索引列的个数,a,b 都是 int 类型,4 byte,为什么 key_len 是 5 byte 和 10 byte 呢?是因为如果索引列定义时允许NULL,其key_len还需要再加 1 bytes.参考好友王的文章,可以移步我们的站点查看详情: http://www.fordba.com/spend-10-min-to-understand-how-mysql-use-index.html

    展开全文
  • 这种说法是错的:”MySql 中是不能用 null索引的,任何包含null值的列都将不会被包含在索引中“我们做个实验如下,可以看到b字段建有索引,b 字段中有 null,但是select count(*) from a group by b用了b上的覆盖...

    这种说法是错的:”MySql 中是不能用 null 作索引的,任何包含null值的列都将不会被包含在索引中“

    我们做个实验如下,可以看到b字段建有索引,b 字段中有 null,但是

    select count(*) from a group by b

    用了b上的覆盖索引。 如果索引里不包含null的话,只用覆盖索引是 不能满足要求的(无法计算出b中null的个数)。

    mysql> show create table a;

    +-------+-------------------------------------------------------------------------------------------

    ----------------------------------------+

    | Table | Create Table

    |

    +-------+-------------------------------------------------------------------------------------------

    ----------------------------------------+

    | a | CREATE TABLE `a` (

    `a` int(11) DEFAULT NULL,

    `b` int(11) DEFAULT NULL,

    KEY `k` (`b`)

    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

    +-------+-------------------------------------------------------------------------------------------

    ----------------------------------------+

    1 row in set (0.03 sec)

    mysql> select * from a;

    +------+------+

    | a | b |

    +------+------+

    | 1 | NULL |

    | 2 | NULL |

    | 2 | 23 |

    | 2 | 45 |

    | 2 | 4 |

    | 2 | 43 |

    +------+------+

    6 rows in set (0.00 sec)

    mysql> explain select count(*) from a group by b;

    +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+

    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

    +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+

    | 1 | SIMPLE | a | index | NULL | k | 5 | NULL | 6 | Using index |

    +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+

    1 row in set (0.00 sec)

    再来:"任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。"

    mysql> show index from t3;

    +-------+------------+----------+--------------+-------------+-----------+-------------+----------+-

    -------+------+------------+---------+---------------+

    | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part |

    Packed | Null | Index_type | Comment | Index_comment |

    +-------+------------+----------+--------------+-------------+-----------+-------------+----------+-

    -------+------+------------+---------+---------------+

    | t3 | 1 | isd | 1 | uid | A | 200 | NULL |

    NULL | YES | BTREE | | |

    +-------+------------+----------+--------------+-------------+-----------+-------------+----------+-

    -------+------+------------+---------+---------------+

    1 row in set (0.00 sec)

    mysql> select count(*) from t3 where uid is null;

    +----------+

    | count(*) |

    +----------+

    | 3009 |

    +----------+

    1 row in set (0.03 sec)

    mysql> explain select count(*) from t3 where uid is null;

    +----+-------------+-------+------+---------------+------+---------+-------+------+-----------------

    ---------+

    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra

    |

    +----+-------------+-------+------+---------------+------+---------+-------+------+-----------------

    ---------+

    | 1 | SIMPLE | t3 | ref | isd | isd | 5 | const | 3008 | Using where; Usi

    ng index |

    +----+-------------+-------+------+---------------+------+---------+-------+------+-----------------

    ---------+

    1 row in set (0.00 sec)

    展开全文
  • 首先建议无论索引列还是其他列,都设置成非 null,通过默认值解决 null 值问题。 如果把索引列一些值设置为 null,是允许的,但是写sql时候就要用到 is null 和 is not null来进行筛选数据,但这个时候是否也走索引...

    首先建议无论索引列还是其他列,都设置成非 null,通过默认值解决 null 值问题。

    如果把索引列一些值设置为 null,是允许的,但是写sql时候就要用到 is null 和 is not null来进行筛选数据,但这个时候是否也走索引呢? 让我们来验证下

    创建表

    CREATE TABLE `a` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(45) DEFAULT NULL,
      `dd` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `name` (`name`)
    ) 

    插入数据

    insert into a(name) select null
    多次执行下面语句,将数据量达到十几万级别(数据太少不走索引)
    insert into a(name)
    select  uuid_short()  from a

    执行sql1

    SELECT * FROM a
    where name='99293305182879751';

    执行sql2

    SELECT * FROM a
    where name is null;

     

    通过两个执行计划我们看到都是可以正常走索引的,在mysql5.7 innodb引擎下,索引字段有null值也不会影响索引的正常使用

     

     

     

     

     

    展开全文
  • mysql 唯一索引null.md

    2021-02-01 21:16:35
    mysql 的唯一索引要求所有参与的列都不能够为 null 值,如果唯一索引中的任何一个元素含有 null 值,则唯一约束将不起作用。示例代码create table tb (a int,b int,c int,unique index (a,b,c));insert into tb(a,b,...
  • 今天在测试过程中发现有数据重复的现象,想设计一个唯一索引达到幂等的效果,但是发现设置了唯一索引以后还是可以插入相同的数据,经过排查,发现联合唯一索引中含有null值会导致索引失效。 二.问题复现 现在我们来...
  • mysql唯一索引null

    2021-06-09 13:54:30
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `age` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`,`age`) ) ENGINE=InnoDB AUTO_INCREMENT=6 ...
  • 除非真的要保存 NULL,否则就把列定义为 NOT NULLMySQL难以优化引用了可空列的查询,它会使索引索引统计和值更加复杂。可空列需要更多的储存空间,还需要在MySQL内部进行特殊处理。当可空列被索引的时候,每条记录...
  • MySQL 多字段组合唯一索引中,有一列字段可能会出现空值业务场景:**用户行为记录表,**防止用户数据重复插入表中。也防止尴尬。。。直接上SQL,已优化CREATE TABLE `user_behavior` (`behavior_id` bigint(20) NOT ...
  • 此时创建唯一索引时要注意了,此时数据库会把空作为多个重复值,而创建索引失败,示例如下:步骤1:mysql> select phone ,count(1) from User group by phone;+-----------------+----------+| phone | coun...
  • 本文实例讲述了mysql索引对排序的影响。分享给大家供大家参考,具体如下:索引不仅能提高查询速度,还可以添加排序速度,如果order by 后面的语句用到了索引,那么将会提高排序的速度。测试1、创建测试表:t15表...
  • 这篇小短文讲的是啥之前看到有人问到 PHPHub 迁移数据库文件中 nullable 和索引的问题,相信很多用了 MySQL 很久的人(特别是平时过多关注业务开发的人),这两个字段属性的概念还不是很清楚,一般会有以下疑问:我...
  • 关于mysql优化部分,有很多网友说尽量避免使用is null, is not null,select * 等,会导致索引失效,性能降低?那是否一定收到影响呢?真的就不会使用索引了吗?本文的测试数据库版本为5.7.18,不同版本得出的结果...
  • 测试环境:数据库:MySQL5.7.25数据库引擎:InnoDB连接工具:Navicat Premium首先,创建数据库,并创建测试表 test:DROP TABLE IF EXISTS `test`;CREATE TABLE `test` (`id` int(11) NOT NULL COMMENT 'ID',`name` ...
  • MySQL索引失效的几种情况汇总

    千次阅读 2021-01-18 20:19:54
    索引不能存储Null,所以这列采用is null条件时,因为索引上根本没Null值,不能利用到索引,只能全表扫描。为什么索引列不能存Null值?将索引列值进行建树,其中必然涉及到诸多的比较操作。Null值的特殊性就在于...
  • 索引不能存储Null,所以这列采用is null条件时,因为索引上根本没Null值,不能利用到索引,只能全表扫描。为什么索引列不能存Null值?将索引列值进行建树,其中必然涉及到诸多的比较操作。Null值的特殊性就在于...
  • MySQL null值存储,null性能影响

    千次阅读 2021-01-19 01:00:10
    MySQL null值的存储mysql>createtablemytest(t1varchar(10),t2varchar(10),t3varchar(10),t4varchar(10))engine=innodbcharset=latin1row_format=compact;QueryOK,0rowsaffected(0.08sec)mysql>...
  • from:http://ourmysql.com/archives/970遇到了几例 MySQL 没用使用预期索引的问题,读了些文档之后,发现 MySQL 的类型转换对索引选择的影响还真是一个不大不小的坑。比如有这样一张 MySQL 表:CREATE TABLE `...
  • 此时创建唯一索引时要注意了, 此时数据库会把空作为多个重复值,而创建索引失败,示例如下:步骤一:mysql> select phone ,count(1) from User group by phone;+-----------------+----------+| phone...
  • create table TEST(col_1 INT,col_2 INT,col_3 VARCHAR(128) not null,col_4 VARCHAR(128) not null,UNIQUE KEY (col_1, col_2, col_3),CONSTRAINT FOREIGN KEY (col_1) REFERENCES T2(ID) ON DELETE CASCAD...
  • 转:https://mp.weixin.qq.com/s/CEJFsDBizdl0SvugGX7UmQ不知道从什么时候开始,网上流传着这么一个说法:MySQL的WHERE子句中包含 IS NULL、IS NOT NULL、!= 这些条件时便不能使用索引查询,只能使用全表扫描。这种...
  • 解决 在sql server中,唯一索引字段不能出现多个null值 在mysql 的innodb引擎中,是允许在唯一索引的字段中出现多个null值的。 根据NULL的定义,NULL表示的是未知,因此两个NULL比较的结果既不相等,也不不等,结果...
  • 面试:xxx是索引字段,那么mysql 中where xxx is null 会使用到索引吗? 答案:如果索引字段设置的是可以为空的情况下,是可以使用到索引的。 验证: CREATE TABLE `department` ( `id` int(10) NOT NULL COMMENT ...
  • mysql 数据库字段为null时 token_app加了索引 1. SELECT * FROM `user` WHERE token_app='xxx' SELECT * FROM `user` WHERE token_app is null 上面的都会走索引 2. SELECT * FROM `user` WHERE token_app is not ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 217,380
精华内容 86,952
关键字:

mysqlnull对索引的影响

mysql 订阅