MYSQL系列--IN到底走不走索引

简单的看个例子：

create table test.test_in_for_index(
    id bigint auto_increment,
    c1 int default 0 not null,
    c2 int default 0 not null,
    primary key (id)
)ENGINE INNODB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

# show variables like '%isolation%';

# set global transaction isolation level REPEATABLE READ;

insert into test.test_in_for_index (c1, c2) VALUES (1,2),(3,4),(5,6);
insert into test.test_in_for_index (c1, c2) VALUES (2,3),(4,5),(6,7);
insert into test.test_in_for_index (c1, c2) VALUES (7,8),(8,9),(9,10);

create index idx_c1 on test.test_in_for_index(c1);

# desc test.test_in_for_index;

explain select * from test.test_in_for_index where c1 in (2,3);

很明显，IN是走了索引的。

但是还有一种情况，

explain select * from test.test_in_for_index where c1 in (2,3,4,5,6,7,8);

这时没有走索引，这是因为优化器认为，走索引还不如走全表搜索快。

# case1 0.000326 s
select * from test.test_in_for_index where c1 in (2,3,4,5,6,7,8);

# case2 0.000455 s
select * from test.test_in_for_index force index(idx_c1) where c1 in (2,3,4,5,6,7,8);

show profiles;

由于数据量太小，真实的执行时间可能有所出入，并且走索引还是走全表，实际上都是执行器自己“估算”的，并不是真的最终执行就快。这边仅提供一种思路。