我们遇到了一个小故障,它为设备生成了意外的数据量。我们的代码最终沿着 SELEXT * from XXX WHERE yyy IN (xxx,xxx,xxx....,xxxx) 创建了一个选择查询,这应该获取最近 5 天的数据,这通常根本不是问题,因为WHERE 字段被索引。
我们突然开始有大量缓慢的查询并使服务器陷入困境。在我们中止查询之前,查询运行了大约 5000 秒。在弄清楚发生了什么后发现,如果我们在 IN () 子句中有一个最多包含 33594 个参数的查询,则一切正常,查询在 200 毫秒内完成。如果我们添加一个参数,选择似乎永远运行。我们没有让一个人无限期地运行直到它完成所以我不确定它是否会完成但它们似乎至少持续几个小时......
选择中的数据约为 361.04KB。我们使用 MYSQL MYSQL_8_0_31 在 Google 云 SQL 上运行
关于什么可能导致这种行为的任何想法?
原因是因为它是一个过于复杂的谓词。该
IN
子句是一堆子句的语法糖OR
。子句太多OR
或子句中的值太多IN
会导致谓词过于复杂,数据库引擎无法解析并生成有效的查询计划。相反,正在生成的查询计划现在可能会以低效的方式扫描整个表。发生这种情况时没有固定的硬编码限制,并且会因查询而异。
条款中约 33,000 个值
IN
是不合理的数量。哎呀,在我看来,即使是 100 个值也是不合理的,而且在我看来,这样的查询设计是一种反模式。有几种主要方法可以重写可以解决问题的查询:
将查询分解为查询的多个副本,这些副本具有更小的
IN
子句,然后UNION
将它们组合在一起。不过,在您的子句中处理这么多值可能也是不现实的IN
。将值放在某种表中,例如临时表,然后
JOIN
从您要过滤的表中使用它。这通常在关系上更有效。根据选项,在我的公司,自 MySQL 5.7 以来,我们遇到了优化器可以使用的内存上限的问题
range_optimizer_max_mem_size
。如果列表中的项目太多,优化器会放弃,并决定进行表扫描而不是使用索引。请参阅https://dev.mysql.com/doc/refman/5.7/en/range-optimization.html#range-optimization-memory-use
我们的解决方案是在 my.cnf 中设置它:
这意味着优化器没有内存限制,因此开发人员应避免列表过长而导致内存错误。但它更有可能使用索引。
这解决了长列表导致表扫描的每种情况下的问题。