转自https://www.cnblogs.com/rocker-pg/p/9908506.html
-- 生成test_null_index表
CREATE TABLE `test_null_index` (
`id` int(11) DEFAULT NULL,
`mark` varchar(20) DEFAULT NULL,
`name` varchar(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 生成测试数据
create procedure test_null_index(in num int)
BEGIN
DECLARE i int;
set i=1;
while (i<num)
DO
if 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);
然后执行 explain SELECT * from test_null_index WHERE id is null;
加索引 create index idx_test_null on test_null_index(id);执行 explain SELECT * from test_null_index WHERE id is null;
加索引create index idx_test_null2 on test_null_index(name);执行explain SELECT * from test_null_index WHERE id is null and name is null;
执行 explain SELECT * from test_null_index WHERE id is null or name is null;
结论:对一个字段做null判断,添加索引,索引是起作用的;对两个字段做null判断,and语句索引起作用;or语句索引没有作用,会放弃索引对整张表扫描。