首页 文章详情

IS NULL、IS NOT NULL、!=、BETWEEN、IN等等不能用索引吗?必须辟谣!

业余草 | 187 2022-04-09 16:37 0 0 0
UniSMS (合一短信)

你知道的越多,不知道的就越多,业余的像一棵小草!

你来,我们一起精进!你不来,我和你的竞争对手一起精进!

编辑:业余草

推荐:https://www.xttblog.com/?p=5336

其实,我在两年前就写了 IN 查询是走索引的。当时很多人没有经过验证,就直接得出 in 查询不走索引。MySQL 的 in 查询不走索引?我拿什么拯救你!

这两天,我看到微信群里,还有不少网友在讨论,哪些关键字不走索引。看到不少人还有认知错,我这里做了一个验证,分享给大家!

IS NULL、IS NOT NULL、!=、BETWEEN、IN等等不能用索引吗?必须辟谣!

为了演示效果,我们先创建一张 demo 表。具体创建表的 SQL 语句如下所示:

CREATE TABLE demo_info(
    id INT NOT NULL auto_increment,
    key1 VARCHAR(100),
    key2 INT,
    key3 VARCHAR(100),
    key_part1 VARCHAR(100),
    key_part2 VARCHAR(100),
    key_part3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    KEY idx_key1 (key1),
    UNIQUE KEY uk_key2 (key2),
    KEY  idx_key3 (key3),
    KEY idx_key_part(key_part1, key_part2, key_part3)
)ENGINE = INNODB CHARSET=utf8mb4;

IS NULL 走索引

我们先看一个 IS NULL 的例子。

「IS NULL的例子」

explain select * from demo_info where key1 is null
IS NULL 走索引

虽然没有key1null的记录,但还是走了索引。

IS NOT NULL 走索引

我们再看一个 IS NOT NULL 的例子。

「IS NOT NULL的例子」

explain select * from demo_info where key1 is not null limit 5;
IS NOT NULL 走索引

因为这里所有记录的key1都不为null,为了避免全表扫描,我这里限制一下返回结果集数量。因为所有的结果都满足is not null,所有记录都会回表,那么优化器会选择全表扫描,而不是多此一举走非聚集索引+回表的方式。

!= 走索引

我们继续看一个 != 不等于走索引的例子。

「!= 的例子」

explain select * from demo_info where key1 != 'a' limit 5;
!= 走索引

这里也走了索引,限制结果集的理由同上一个例子。

between 走索引

「between的例子」

explain select * from demo_info where key1 between 'a' and 'd' limit 5;
between 走索引

走索引的理由同上。

多的例子就不举了,否则比较冗余,直接上结论。

结论:对于B+树索引来说,只要索引列使用了=<=>INNOT INIS NULLIS NOT NULL><>=<=BETWEEN!=(也就是<>)或者LIKE(只能是'a%'前缀字符形式)操作符连接起来,就可以使用到索引,如果你发现没走索引,请检查自己的结果集是否过多,限制一下结果集数量。

good-icon 0
favorite-icon 0
收藏
回复数量: 0
    暂无评论~~
    Ctrl+Enter