我正在观察我试图理解的奇怪行为。
MySQL版本：5.7.33 我有以下查询：
select * from a_table where time>='2022-05-10' and guid in (102,512,11,35,623,6,21,673);
a_table 在 time、guid 上有主键，在 guid 上有索引
我上面写的查询具有非常好的性能，根据解释计划是 using index condition;使用地点；使用MRR
当我增加 in 子句中的值数量时，性能会受到显着影响。
经过一些演练，我得到了一个粗略的数字。对于小于 ~14500 的值，解释计划与上面相同。对于高于此值的数量，仅解释计划 使用 where 并且需要永远运行我的查询。
换句话说，例如，如果我在 in 子句中放入 14,000 个值，则解释计划将具有预期的 14,000 行。但是，如果我在 in 子句中放入 15,000 个值，则解释有 221200324 行。我的整个表中甚至没有这么多行。
我正在尝试理解这种行为，并知道是否有任何方法可以解决这个问题。
谢谢
P粉0418569552023-12-21 00:01:22
了解限制内存用于范围优化。
当 IN() 谓词中有大量值时，它会在查询优化步骤中使用更多内存。在某些情况下这被认为是一个问题，因此最近版本的 MySQL 设置了最大内存限制（默认为 8MB）。
如果优化器发现它需要比限制更多的内存，则查询中没有其他条件可以用来优化，它会放弃尝试优化，并诉诸表扫描。我推断您的表统计信息实际上显示该表有约 2.21 亿行（尽管表统计信息是不准确的估计）。
我不能说我知道给定值列表需要多少内存的确切公式，但根据您观察到的行为，我们可以猜测，考虑到 14k 项，每个项平均约为 600 字节有效，但更多则无效。
您可以设置range_optimizer_max_mem_size = 0来禁用内存限制。这会产生过度使用内存的风险，但它避免了优化器“放弃”。我们在上一份工作中在所有 MySQL 实例上设置了这个值，因为我们无法教育开发人员避免在他们的查询中创建巨大的值列表。