为什么MySQL不自动为适当的查询创建临时索引？

Question

我意识到这是一个元编程问题，但我假设这里有足够的有经验的人来给出一个像样的答案。

我只是在重新构建一个查询，以便从一个表中检索一些数据。

SELECT pl.field1, pl.field2
FROM table pl
LEFT JOIN table2 dp on pl.field1 = dp.field1
WHERE dp.field1 IS NULL

执行此查询需要很长时间(1800+秒)。

当我厌倦了等待，并努力EXPLAIN查询，结果是一个完整的表扫描已经完成。

我在dp.field1上创建了一个索引，之后查询几乎是即时的，创建该索引所花费的时间还不到一秒钟。

从EXPLAIN来看，这并不是很难确定。为什么MySQL不能或者不会自动完成这个任务呢？只需花一秒时间创建该索引就可以使查询立即进行，因此MySQL理论上可以创建一个临时索引，使用它执行查询，然后再删除它，这仍然比其他方法快几个数量级。

我期待着“确保您设计一个好的模式”或“mysql只是做您要做的事情”的通常答案，但我想知道这是否是一个糟糕的想法的技术原因。

Answer 1

非常简单--因为使用当前RDBMS引擎的设计，这个想法并没有真正的扩展。

对于单个用户来说，这是可以的，但是数据库是为了支持许多并发用户而设计的，让每个用户的查询也运行一个推测的优化步骤(“我可以通过创建一个索引来加速这个查询吗？”)，而创建这个索引在某些情况下是一个非常昂贵的操作，在任何规模上都会变得缓慢。将索引“单一使用”将浪费计算时间和磁盘空间，但拥有大量永久性索引反过来将通过对给定查询的多个索引进行调查来减缓查询优化器的速度。它还将减缓数据修改操作。

诚然，在现代硬件上，这些担忧并不那么重要-- RDBMS引擎的基本设计可以追溯到磁盘空间昂贵、CPU速度慢几个数量级、内存是不可想象的奢侈品的时代。

Answer 2

对于基数较低的列，使用B树索引不是一个好主意。B树因基数低而退化，与全表扫描相比，实际上增加了查询时间。

所以，总是创建一个B树索引不是一个好主意。至少它也必须考虑基数。也许还有其他几件事。

Answer 3

我只代表MySQL发言，因为可能有一个数据库系统可以自动修改数据库设计。

简单的答案是，MySQL只是做你让它做的事情。

MySQL无法预测未来。只有你能。你比MySQL更了解你的数据。MySQL保存了一些统计数据，但在实际尝试之前，它猜测在非常稀疏的信息(有时是过时的)上执行查询的最佳方法。一旦它开始执行，它就不会改变它的计划，不管猜测是多么的错误。

它用来猜测的方法都有很好的文档记录。我们的工作是提供将提供最大效益的索引，有时甚至暗示它应该使用这些索引。

如果您告诉MySQL执行需要表扫描的查询，它假定您知道它将进行表扫描，因为它在其文档中告诉您它会进行表扫描。它只是服从。

不允许DBA作出决策的数据库系统不能很好地扩展。总有一些需要权衡的东西，而你才是做出它们的人。MySQL是锤子，不是木匠。