颠覆三观的索引之is null 和 is not null

job is null和job is not null同时走了索引

is null的type为ref也就是is null和数据分布无关

is not null的type为range,走不走索引和数据分布有关(is not null 的数据少,优化器认为走索引效率高)

select (select count(*) from emp where job is null)/(select count(*) from emp); # 0.9967select (select count(*) from emp where job is not null)/(select count(*) from emp); # 0.0033select count(*) from emp; # 3001011select count(*) from emp where job is null; # 2991012select count(*) from emp where job is not null; # 9999reset query cache ;explain select * from emp where job is null;explain select * from emp where job is not null;