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

九转成圣

发布于 2024-04-10 16:27:51

12600

代码可运行

文章被收录于专栏：csdncsdn

运行总次数：0

代码可运行

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.9967

select (select count(*) from emp where job is not null)/(select count(*) from emp); # 0.0033

select count(*) from emp; # 3001011

select count(*) from emp where job is null; # 2991012

select count(*) from emp where job is not null; # 9999

reset query cache ;

explain select * from emp where job is null;

explain select * from emp where job is not null;

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2023-05-25，如有侵权请联系 cloudcommunity@tencent.com 删除

索引