MySQL:对分析表的随机影响

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

但是，这不是最佳选择。所以我对所有表运行"分析表"，它给了我：

A -- B -- C

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

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

所以我的问题是：

1. 为什么部署后执行计划会发生变化？
2. 除了固定执行计划（数据更新和快速变化，因此将来最佳计划可能会发生变化）之外，有没有办法保证始终确保最佳计划？

Answer 1

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

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

但是，在某些情况下，内存中的统计信息正好处于使优化器做出不同选择的风口浪尖，因此您会看到这种翻转行为。

通过在查询中指定索引提示，可以覆盖优化程序用于选择索引的默认算法。

您可以通过指定 STRAIGHT_JOIN 来覆盖优化程序对表重新排序的默认算法。 这意味着您希望它按照您在 FROM 子句中给出的顺序读取表，并且不要对它们重新排序。

您可以使用STRAIGHT_JOIN作为查询修饰符（如 DISTINCT）。 把它放在选择之后：

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;

但请注意不要过于随意地使用索引提示或联接提示。 优化器可能会在下周数据的大小和分布发生一点变化后避免触发器行为。 如果代码中有太多覆盖，则可能会阻止优化器做得更好！