数据库优化之null和notnull使用对索引的可能影响（以mysql为例）

大致分为四种情况

一 字段不可以为null

1.首先建一个name字段不可以为null的表，表中插入几条数据

CREATE TABLE staffs (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR (24) NOT NULL DEFAULT ” COMMENT ‘姓名’,
age INT NOT NULL DEFAULT 0 COMMENT ‘年龄’,
pos VARCHAR (20) NOT NULL DEFAULT ” COMMENT ‘职位’,
add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘入职时间’
) CHARSET utf8 COMMENT ‘员工记录表’ ;
INSERT INTO staffs(NAME,age,pos,add_time) VALUES(‘z3’,22,’manager’,NOW());

2.设立索引，一个组合索引，一个单独索引

ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(NAME, age, pos);
ALTER TABLE staffs ADD INDEX idx_staffs_name(NAME);

3.查询语句

EXPLAIN SELECT * FROM staffs WHERE NAME IS NULL；
EXPLAIN SELECT * FROM staffs WHERE NAME IS NOT NULL;

4.结果如下

可以看见无论是null或者notnull索引都失效。

二 字段可以为null

1.首先建一个name字段可以为null的表，表中插入几条数据

CREATE TABLE staffs (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR (24) ” COMMENT ‘姓名’,
age INT NOT NULL DEFAULT 0 COMMENT ‘年龄’,
pos VARCHAR (20) NOT NULL DEFAULT ” COMMENT ‘职位’,
add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘入职时间’
) CHARSET utf8 COMMENT ‘员工记录表’ ;
INSERT INTO staffs(NAME,age,pos,add_time) VALUES(‘z3’,22,’manager’,NOW());

2.设立索引，一个组合索引，一个单独索引

ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(NAME, age, pos);
ALTER TABLE staffs ADD INDEX idx_staffs_name(NAME);

3.查询语句

EXPLAIN SELECT * FROM staffs WHERE NAME IS NULL；
EXPLAIN SELECT * FROM staffs WHERE NAME IS NOT NULL;

4.结果如下

可以看见无论是null或者notnull索引都有效。

由此得出结论，null和notnull失效主要与表中字段的设立有关系，分为相应的四种情况，当对不能是null的字段使用索引时，条件无论是null或者not null 索引都失效，当对能是null的字段使用索引时，条件无论是null或者not null 索引都生效，只不过是范围不一样

，