MySQL:对ANALYZE TABLE的随机影响

Question

我有 3 个 innodb 表，比如 A、B 和 C。有一个查询连接这三个表以生成结果。

SELECT A.a, B.b, C.c
from A 
join B on A.id = B.a_id 
join C on C.id = B.c_id
where A.a = 'example' and B.b < 10;

开始时，当我使用“EXPLAIN”命令测试查询时，它给了我以下顺序:

B -- C -- A

然而，这并不是最优的。所以我对所有表运行“ANALYZE TABLE”，它给了我:

A -- B -- C

，我相信这是正确的顺序。

然后我将 SQL 部署到生产中，1​​ 个月后，执行计划无缘无故地切换回了错误的选项，即 B--C--A。之后，我尝试再次运行 ANALYZE TABLE 几次，不过，这一次，结果让我感到困惑。有时它也给我 B--C--A，有时它给我 A--B--C，有时甚至是其他执行计划。

所以我的问题是:

1. 为什么部署后执行计划会改变？
2. 除了固定执行计划(数据更新和变化很快，所以最优计划可能会在未来改变)，有没有办法保证最优计划始终得到保证？

Answer 1

优化器根据有关表大小、基数、值分布、索引等的内存统计信息来选择重新排序表和使用索引。这些统计信息是估计，并非绝对准确在任何时候。

InnoDB 会不时更新其统计信息，这就是您在运行 ANALZYE TABLE 时可能导致发生的情况。

但是，在某些情况下，内存中的统计数据正处于使优化器做出不同选择的风口浪尖，因此您会看到这种反复无常的行为。

您可以通过指定 index hints 来覆盖优化器选择索引的默认算法。在您的查询中。

您可以通过指定 STRAIGHT_JOIN 来覆盖优化器重新排序表的默认算法。 .这意味着您希望它按照您在 FROM 子句中指定的顺序读取表格，而不是重新排序。

您可以使用 STRAIGHT_JOIN 作为查询修饰符(如 DISTINCT)。放在 SELECT 之后:

SELECT STRAIGHT_JOIN A.a, B.b, C.c
from A 
join B on A.id = B.a_id 
join C on C.id = B.c_id
where A.a = 'example' and B.b < 10;

但是要小心不要过度使用索引提示或连接提示。在数据的大小和分布发生一点点变化之后，优化器可能会在下周避免翻转行为。如果您的代码中有太多覆盖，您可能会阻止优化器做得更好!