IS NULL、IS NOT NULL、!=、BETWEEN、IN等等不能用索引吗?必须辟谣!
共 1424字,需浏览 3分钟
·
2022-04-09 16:37
你知道的越多,不知道的就越多,业余的像一棵小草!
你来,我们一起精进!你不来,我和你的竞争对手一起精进!
编辑:业余草
推荐: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
![](https://filescdn.proginn.com/9c9860a7702d73c52b1db39ef4a4f619/d832d23978df5770816d346729358570.webp)
虽然没有key1
为null
的记录,但还是走了索引。
IS NOT NULL 走索引
我们再看一个 IS NOT NULL 的例子。
「IS NOT NULL的例子」
explain select * from demo_info where key1 is not null limit 5;
![](https://filescdn.proginn.com/dcc08f359f8251c5d98b431c91d9bc81/3cec30d211732b08a94ba9f237c40936.webp)
因为这里所有记录的key1
都不为null
,为了避免全表扫描,我这里限制一下返回结果集数量。因为所有的结果都满足is not null
,所有记录都会回表,那么优化器会选择全表扫描,而不是多此一举走非聚集索引+回表的方式。
!= 走索引
我们继续看一个 != 不等于走索引的例子。
「!= 的例子」
explain select * from demo_info where key1 != 'a' limit 5;
![](https://filescdn.proginn.com/edd564445f77382e3d47eb1c6925883d/0586b82af4f2e6151f7211b97fe69f62.webp)
这里也走了索引,限制结果集的理由同上一个例子。
between 走索引
「between的例子」
explain select * from demo_info where key1 between 'a' and 'd' limit 5;
![](https://filescdn.proginn.com/599b067a4a5cf19ec974c779e9a039f7/dc45546aadba6ed7a46c1299eb28788c.webp)
走索引的理由同上。
多的例子就不举了,否则比较冗余,直接上结论。
结论:对于B+
树索引来说,只要索引列使用了=
、<=>
、IN
、NOT IN
、IS NULL
、IS NOT NULL
、>
、<
、>=
、<=
、BETWEEN
、!=
(也就是<>
)或者LIKE
(只能是'a%'
前缀字符形式)操作符连接起来,就可以使用到索引,如果你发现没走索引,请检查自己的结果集是否过多,限制一下结果集数量。