--- theme: channing-cyan --- in到底用不用索引感觉像一桩悬疑片！古早时期的面经，统一说不走索引，在一些程序员脑海中从此留下不可磨灭的印记。\ 有些从业时间较长的程序员脑子里的第一反应就是不走索引，上个月我就曾经被同事这样质疑过。但是那是mysql5.5以前的老黄历了，现在都到8.0+了,5.5(甚至更早)以后可以肯定的是它会走索引。\ 但必然走索引吗？不一定。我搜索引擎上搜索关键词 `in/or` 和 `索引`，出来一大片文章，一般都会说，in/or能走索引，但后面跟的条件个数多了就不走索引了。\ 但问题就来了，这个`多了`到底是多少才算多？\ 对于一个动态查询的SQL,我咋知道到底走不走索引？\ 如何量化计算呢？这时候就语焉不详或者直接跳过。大名鼎鼎的《阿里巴巴JAVA开发规范》倒是一刀切。\ 最好不超过1000。 ![](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/4b877bb88e1743829598d2c36173de38~tplv-k3u1fbpfcp-zoom-1.image) 人家这规范只是推荐，也不是强制，是吧，不能吐槽。而且超过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)` 查看执行计划 ![](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/3720049dfc41413a837e0f905a808359~tplv-k3u1fbpfcp-zoom-1.image) 此时表里无数据，显示的是`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)` 查看执行计划 ![](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/9ace84ba24984be3a4da4f9bb3a0b692~tplv-k3u1fbpfcp-zoom-1.image) 使用了索引，还是效率最高的`const`(system生产环境不可能的吧)，此时`id in(1)`相当于 `id = 1`。
在in里增加点条件。 sql变成`EXPLAIN SELECT id, NAME FROM t_person WHERE id IN (1, 2)` 查看执行计划 ![](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/8a9396c808ea46faaa0852c3d11bcde7~tplv-k3u1fbpfcp-zoom-1.image) 使用了索引，但级别下降到了`range`,即范围索引。
继续在in里增加条件。 sql变成`EXPLAIN SELECT id, NAME FROM t_person WHERE id IN (1, 2，3)` 查看执行计划 ![](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/84e77952dadb43e7b2018fec3695596b~tplv-k3u1fbpfcp-zoom-1.image) 索引级别变成了`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)` 查看执行计划 ![](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/21607c1f94214cbaac5602876842de8f~tplv-k3u1fbpfcp-zoom-1.image) 可以看到，随时表数据的增加，同样的sql执行计划从`ALL`变回了`range`,索引又生效了。同样地，再增加一个in条件，`EXPLAIN SELECT id, NAME FROM t_person WHERE id IN (1,2,3,4)`的执行计划又变回了`ALL`,这里就不放图了。

**多点数据**
以上只是小打小闹撒撒水啦，总共几条数据，in的条件都快超过表数据了，执行计算都不用预估就知道全表扫描还好一点啦。我再往表里插入100万条数据。 ![](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/22425b96ca1b4a1895c8faf9839c3ef3~tplv-k3u1fbpfcp-zoom-1.image) 我先按照阿里的开发规范推荐的1000这个值作为临界值，先使用900个条件 ![](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/9d5697bdde884164856c8bc6194a5ff5~tplv-k3u1fbpfcp-zoom-1.image) 再使用1100个条件 ![](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/1f0b7eb693b047bc9139c4925de236ff~tplv-k3u1fbpfcp-zoom-1.image) 上图表明，这两种情况都使用到了range范围索引呢。再加大剂量，直接上10万。 ![](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/15895549a94348fd8376f7eab8da2e71~tplv-k3u1fbpfcp-zoom-1.image) 步子迈大了，咔，这下终于全表扫描了。但是还是没找到临界值。 ## 官网上寻找答案我在这里寻找到了一个参数，描述的倒像是相似的问题。 ![](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/78bf7fb11cff4f029230207357d94a18~tplv-k3u1fbpfcp-zoom-1.image) 这个方法说的是当使用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://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/8d72db9840714839807e17dc528b9401~tplv-k3u1fbpfcp-zoom-1.image)
相似的问题在这里。这位仁兄也在in的使用中也有很多问号，in的条件卡在14000左右，超过就失去了range索引。
下面高赞答案提到了一个参数，`range_optimizer_max_mem_size `，一看就很有搞头啊。 ![](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/4b738c2b2b2d4006a5e76c2ae9bb0549~tplv-k3u1fbpfcp-zoom-1.image)
转到mysql官网，凭我的渣渣英语也能看明白，我知道，大概我找到答案了。 ![企业微信截图_16871466622476.png](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/5d8ee0d1ef5d494bb2854fe45f621325~tplv-k3u1fbpfcp-watermark.image?) 要控制范围优化器可用的内存，使用range\_optimizer\_max\_mem\_size系统变量: * 值为0表示“没有限制”。 * 当值大于0时，优化器将跟踪在考虑范围访问方法时所消耗的内存。如果即将超过指定的限制，则放弃范围访问方法，转而考虑其他方法，包括`全表扫描`。这可能不太理想。如果发生这种情况，会出现以下警告(其中N是当前的range\_optimizer\_max\_mem\_size值)。

现在事情就很简单了。 ![](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/083ee08b8f2a4af3a77d1abb457fb868~tplv-k3u1fbpfcp-zoom-1.image) 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索引。 ![](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/54fd073d530c419a8237a0e0a9377657~tplv-k3u1fbpfcp-zoom-1.image) range\_optimizer\_max\_mem\_size=8时，走ALL全表扫描。 ![](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/958068c2fede47278944eae4c61171d9~tplv-k3u1fbpfcp-zoom-1.image)
破案了！明明官网上就有答案，我却三过家门而不入。 ![](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/d4ba3ea209144b12b043e34d26b290dc~tplv-k3u1fbpfcp-zoom-1.image)
## 结论
in两种情况会走全表扫描。 * in后面条件导致sql(update:消耗内存)大小超过range\_optimizer\_max\_mem\_size。（感谢[MySQL 如何优化慢查询？ - 掘金 (juejin.cn)](https://juejin.cn/post/7174787791291842619)） * in后面条件个数接近或者等于表数量，执行引擎认为此时全表扫描更加合适。推而广之，or也是一样的道理。\ 其它`> >= < <=` `BETWEEN AND`应该也是同样的道理。因为它们归根结底都是范围查询。 * `or` 的情况 ![](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/15de03d6555145efbe1dd77df29fcc1d~tplv-k3u1fbpfcp-zoom-1.image)\ ![](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/e5ba2a5c4fee4b819783a2e07e84441e~tplv-k3u1fbpfcp-zoom-1.image)
* `> <`的情况 ![](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/469b3bf5ef44445283536c77c7803d85~tplv-k3u1fbpfcp-zoom-1.image) ![](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/de990dfe9e1b4ca7abcd764b510e53b9~tplv-k3u1fbpfcp-zoom-1.image) 当然，总体来说，in后面条件越少越好，假设一张表有1000万条数据，in后面的条件有10000个，这时候就算走了range索引，估计效率也好不到哪里。
完