• 问题
• 官网上寻找答案
• stackoverflow
• 结论

in/or到底能不能用索引应该是肯定的，但有时生效有时不生效，这个能不能量化计算？这是本文想讨论和解答的问题.

---

in到底用不用索引感觉像一桩悬疑片！古早时期的面经，统一说不走索引，在一些程序员脑海中从此留下不可磨灭的印记。 有些从业时间较长的程序员脑子里的第一反应就是不走索引，上个月我就曾经被同事这样质疑过.

但是那是mysql5.5以前的老黄历了，现在都到8.0+了,5.5(甚至更早)以后可以肯定的是它会走索引。 但必然走索引吗？不一定.

我搜索引擎上搜索关键词 in/or 和 索引 ，出来一大片文章，一般都会说，in/or能走索引，但后面跟的条件个数多了就不走索引了。 但问题就来了，这个 多了 到底是多少才算多？ 对于一个动态查询的SQL,我咋知道到底走不走索引？ 如何量化计算呢?

这时候就语焉不详或者直接跳过.

大名鼎鼎的《阿里巴巴JAVA开发手册》倒是一刀切。 最好不超过1000.

人家这规范只是推荐，也不是强制，是吧，不能吐槽.

而且超过1000就算用上了range级别的查询，那可能也快不到哪里去啊，对于要求快速响应的互联网需求来说这推荐好像没毛病.

但这不是重点，今天的重点在于，我一定要搞清楚，在保证 explain 的type为 range 而不是 ALL 全表扫描的前提下，到底 select * from table where id in (1,2,3.....x) 这个 x 能到多少.

问题

首先建一张测试表，来一步复现一下，走与不走索引的情况.

mysql 。

版本:5.7.19 引擎:innodb 。

创建一个测试表 。

                        
                          CREATE TABLE `t_person` (
  `id` int(11) NOT NULL,
  `name` varchar(10) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

                        
                      

使用SQL 。

EXPLAIN SELECT id, NAME FROM t_person WHERE id IN (1) 。

查看执行计划 。

此时表里无数据，显示的是 no matching row in const table . 。

少量数据 。

插入一条数据 insert t_person (id,name) values(1,'张三') 。

使用SQL 。

EXPLAIN SELECT id, NAME FROM t_person WHERE id IN (1) 。

查看执行计划 。

使用了索引，还是效率最高的 const (system生产环境不可能的吧)，此时 id in(1) 相当于 id = 1 .

在in里增加点条件.

sql变成 EXPLAIN SELECT id, NAME FROM t_person WHERE id IN (1, 2) 。

查看执行计划 。

使用了索引，但级别下降到了 range ,即范围索引.

继续在in里增加条件.

sql变成 EXPLAIN SELECT id, NAME FROM t_person WHERE id IN (1, 2，3) 。

查看执行计划 。

索引级别变成了 ALL ,即全表扫描，其实是索引失效了.

再往表里插入两条数据。此时总共3条数据.

                        
                          insert t_person (id,name) values(2,'李四')
insert t_person (id,name) values(3,'王五')

                        
                      

再使用sql EXPLAIN SELECT id, NAME FROM t_person WHERE id IN (1, 2，3) 。

查看执行计划 。

可以看到，随时表数据的增加，同样的sql执行计划从 ALL 变回了 range ,索引又生效了.

同样地，再增加一个in条件， EXPLAIN SELECT id, NAME FROM t_person WHERE id IN (1,2,3,4) 的执行计划又变回了 ALL ,这里就不放图了.

多点数据 。

以上只是小打小闹撒撒水啦，总共几条数据，in的条件都快超过表数据了，执行计算都不用预估就知道全表扫描还好一点啦.

我再往表里插入100万条数据.

我先按照阿里的开发规范推荐的1000这个值作为临界值，先使用900个条件 。

再使用1100个条件 。

上图表明，这两种情况都使用到了range范围索引呢.

再加大剂量，直接上10万.

步子迈大了，咔，这下终于全表扫描了.

但是还是没找到临界值.

官网上寻找答案

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html 。

我在这里寻找到了一个参数，描述的倒像是相似的问题.

这个方法说的是当使用in或or查询时，比如where in(1,2,3),执行引擎会先预估表中的数量，表中的数量将决定使用的查询方式，比如，如果表中只有3条数据，那么很明显，这时候直接全表扫描.

而这个预估的方法有2种,一是dive到index中即利用索引完成元组数的估算,简称index dive; 二是使用索引的统计数值,进行估算. 。

相比这2种方式,在效果上

• index dive: 速度慢,但能得到精确的值（MySQL的实现是数索引对应的索引项个数，所以精确） 。

• index statistics: 速度快,但得到的值未必精确. 。

但 eq_range_index_dive_limit 这个参数确实跟今天的主题相关系数不大。很明显，这个值在mysql 5.7是200， 一开始的in后面的条件个数就是900，依然是走了range索引的.

stackoverflow

于是我找到了stackoverflow，在上面把 msyql in count 这些关键词搜了一下，没有找到相关的问题.

然后我把问题详细描述了一下，提了一个新的问题，没想到啊，半个小时不到，人家就直接给我点踩，并给出了相似的已解答问题.

尴尬了。 我超喜欢stackoverflow，这里的人个个都是人才.

相似的问题在这里.

https://stackoverflow.com/questions/72361880/mysql-in-operator-on-large-number-of-values 。

这位仁兄也在in的使用中也有很多问号，in的条件卡在14000左右，超过就失去了range索引.

下面高赞答案提到了一个参数， range_optimizer_max_mem_size ，一看就很有搞头啊.

转到mysql官网，凭我的渣渣英语也能看明白，我知道，大概我找到答案了.

https://dev.mysql.com/doc/refman/5.7/en/range-optimization.html#equality-range-optimization 。

要控制范围优化器可用的内存，使用range_optimizer_max_mem_size系统变量

• 值为0表示“没有限制”.

• 当值大于0时，优化器将跟踪在考虑范围访问方法时所消耗的内存。如果即将超过指定的限制，则放弃范围访问方法，转而考虑其他方法，包括 全表扫描 。这可能不太理想。如果发生这种情况，会出现以下警告(其中N是当前的range_optimizer_max_mem_size值).

现在事情就很简单了.

range_optimizer_max_mem_size默认是8M，使用同样的SQL，in后面同样的条件为固定的19900个， 在 range_optimizer_max_mem_size=8M , range_optimizer_max_mem_size=8 情况下分别执行一下看效果.

range_optimizer_max_mem_size=8M时，走range索引.

range_optimizer_max_mem_size=8时，走ALL全表扫描.

破案了！ 。

明明官网上就有答案，我却三过家门而不入.

结论

in两种情况会走全表扫描.

• in后面条件导致sql大小超过range_optimizer_max_mem_size。
• in后面条件个数接近或者等于表数量，执行引擎认为此时全表扫描更加合适。

推而广之，or也是一样的道理。 归根结底都是范围查询.

当然，总体来说，in后面条件越少越好，假设一张表有1000万条数据，in后面的条件有10000个，这时候就算走了range索引，估计效率也好不到哪里.

完

最后此篇关于in用不用索引，啥时候能用啥时候不能用，一文说清的文章就讲到这里了,如果你想了解更多关于in用不用索引，啥时候能用啥时候不能用，一文说清的内容请搜索CFSDN的文章或继续浏览相关文章，希望大家以后支持我的博客！ 。