MySQL-'IN'-Operator zur Verarbeitung großer Zahlen

Question

Ich beobachte seltsames Verhalten, das ich zu verstehen versuche.

MySQL-Version: 5.7.33 Ich habe folgende Abfrage:

select * from a_table where time>='2022-05-10' and guid in (102,512,11,35,623,6,21,673);

Index auf

a_table 在 time、guid 上有主键，在 guid

Die Abfrage, die ich oben geschrieben habe, hat eine sehr gute Leistung und laut Erklärungsplan ist sie using index condition;使用地点；使用MRR

Wenn ich die Anzahl der Werte in der in-Klausel erhöhe, wird die Leistung erheblich beeinträchtigt.

Nach einigen Übungen hatte ich eine ungefähre Zahl. Für Werte unter ~14500 ist das Interpretationsschema das gleiche wie oben. Bei Mengen darüber wird nur der Plan erklärt 使用 where und es dauert ewig, meine Abfrage auszuführen.

Mit anderen Worten: Wenn ich beispielsweise 14.000 Werte in die in 子句中放入 14,000 个值，则解释计划将具有预期的 14,000 行。但是，如果我在 in-Klausel einfüge, verfügt der Erklärungsplan über die erwarteten 14.000 Zeilen. Wenn ich jedoch 15.000 Werte in die

-Klausel einfüge, hat die Erklärung 221200324 Zeilen. Ich habe nicht einmal so viele Zeilen in meiner gesamten Tabelle.

Ich versuche dieses Verhalten zu verstehen und herauszufinden, ob es eine Möglichkeit gibt, es zu beheben.

Danke🎜

Answer 1

了解限制内存用于范围优化。

当 IN() 谓词中有大量值时，它会在查询优化步骤中使用更多内存。在某些情况下这被认为是一个问题，因此最近版本的 MySQL 设置了最大内存限制（默认为 8MB）。

如果优化器发现它需要比限制更多的内存，则查询中没有其他条件可以用来优化，它会放弃尝试优化，并诉诸表扫描。我推断您的表统计信息实际上显示该表有约 2.21 亿行（尽管表统计信息是不准确的估计）。

我不能说我知道给定值列表需要多少内存的确切公式，但根据您观察到的行为，我们可以猜测，考虑到 14k 项，每个项平均约为 600 字节有效，但更多则无效。

您可以设置range_optimizer_max_mem_size = 0来禁用内存限制。这会产生过度使用内存的风险，但它避免了优化器“放弃”。我们在上一份工作中在所有 MySQL 实例上设置了这个值，因为我们无法教育开发人员避免在他们的查询中创建巨大的值列表。