如何在mysql中优化IN子查询使用索引

这就是陷阱：子查询索引丢失了。

不相信：优化子查询不需要 JOIN。

不要这样做：只使用 IN 子查询并忽略索引。

mysql中in会用索引吗

哎呀，说到MySQL中的IN运算符，这实际上取决于它是否使用索引。
例如，如果你只想检查几件事，MySQL会很乐意使用索引为你做范围查询，这比全表扫描效率要高得多。
如果你查的是一些具体的违章代码，比如2 02 2 年某个城市的交通违章记录，MySQL可以使用该主键索引，速度会快很多。

但是，如果检查的东西很多，比如超过优化器的限制或者超过全表的2 0%，MySQL可能懒得用索引，直接扫描全表，类型字段就会变成ALL。
比如你查2 02 2 年某个城市的几千条房价数据，MySQL可以跳过索引，直接查全表。

再说NOT IN，它不使用索引，因为它要一项一项检查是否不在列表中，这就破坏了索引的可串行性。

另外，复合索引，如果你的IN条件不是第一个字段，是没有用的。
比如索引是(A,B)，而你要检查的是B IN(...)，那么就不需要索引。

数据类型也必须匹配。
不要让IN列表中的值与索引列的类型不兼容，那会很尴尬。

如果需要大规模IN查询，可以考虑使用临时表或者批量查询，以免给索引带来太大的压力。

最后用EXPLAIN分析执行计划，看看Type字段是否为范围，Key字段是否显示索引名称，预期扫描的行数是否为多个，附加字段中是否出现UsingFileSort或UsingTemporary。
如果发现索引无效，就需要想办法调整IN列表的大小或者改变查询逻辑。

mysql in 走索引情况

显然，MySQL的IN运算符能否使用索引取决于IN括号内填充的数据量。
这样做的复杂性在于MySQL会根据值与表中数据的匹配比例来进行判断。

先说最重要的一点：去年我们跑一个电商订单查询项目的时候，我们往IN里插入了几十个ID，速度还蛮快的，因为匹配到的行数还不到1 0%，一直用索引，几秒就出结果了。
还有一点是，当 IN 括号里塞满 3 000 个 ID 并且匹配率超过 3 0% 时，MySQL 优化器就会回头。
直接扫描整个表比使用索引更容易。
还有一个非常重要的细节。
例如，如果使用 SELECT FROM users WHERE id IN (1 ,2 ,3 )，如果 id 是索引，则必须使用该索引。
但如果使用SELECT FROM users WHERE id IN（SELECT id FROMorders WHERE user_id=1 00），外查询中的IN括号实际上匹配了表中7 0%的数据，直接使用索引。

我一开始以为IN括号里的数据越多请求就越好，但是后来发现错了。
这与数据库如何计算成本有关。
等等，还有一件事。
如果IN括号里的值都是连续的数字，比如IN(1 ,2 ,3 ,4 )，MySQL可能会认为你存储数据的方式有问题，不会为你建立索引。

建议将括号IN中的值分成两组或三组，每组不超过1 00，或者干脆用JOIN重写。
但说实话，有点复杂。
有时优化器在推理时会出错，你必须手动 EXPLAIN 才能看到它的想法。

MySQL为什么不推荐使用in

上周一位客户问我为什么他的 MySQL 查询很慢。
我看了一下，发现他们用了很多IN语句。
我告诉他MySQL其实不建议过度依赖IN语句。
主要原因如下：
首先，IN语句会导致索引未被充分利用。
例如，如果你的值列表是动态生成的，例如用于用户输入或程序计算，那么MySQL优化器将很难提前知道要使用哪个索引。
例如，这样的查询： SELECT FROM users WHERE id IN (1 , 3 , 5 , ...);如果值列表太长或者分布不均匀，优化器可能会放弃索引而直接扫描整个表，那么查询速度当然会很慢。

其次，IN语句的内存消耗也很大。
因为MySQL需要将所有值加载到内存中进行比较，如果值列表很长，比如数万个项目，那么内存使用量会很大，可能会导致内存溢出或系统页面更改。

此外，查询优化器对IN语句的优化还不够好。
例如，IN 语句的选择性估计可能不准确，或者优化器在与子查询一起使用时可能无法将 IN 语句转换为更有效的连接操作。

因此，我建议他们考虑以下替代方案：
1 使用JOIN，特别是在查询多个表连接时，因为JOIN可以更稳定地使用索引。
2 、如果IN列表是固定值的，可以拆分成多个OR条件或者使用临时表。
3 .对于复杂的子查询，可以使用EXISTS代替。

最后，我还建议分析执行计划、限制值列表长度、定期更新统计数据以及对非常大的数据集执行迁移或批处理。
不管怎样，你可以看出，这些方法应该可以帮助他们提高查询效率。
我还在思考这个问题，也许还有其他的改进方法。