Finding optimal join orders is among the most crucial steps to be performed by query optimisers. Though extensively studied in data management research, the problem remains far from solved: While query optimisers rely on exhaustive search methods to determine ideal solutions for small problems, such methods reach their limits once queries grow in size. Yet, large queries become increasingly common in real-world scenarios, and require suitable methods to generate efficient execution plans. While a variety of heuristics have been proposed for large-scale query optimisation, they suffer from degrading solution quality as queries grow in size, or feature highly sub-optimal worst-case behavior, as we will show. We propose a novel method based on the paradigm of mixed integer linear programming (MILP): By deriving a novel MILP model capable of optimising arbitrary bushy tree structures, we address the limitations of existing MILP methods for join ordering, and can rely on highly optimised MILP solvers to derive efficient tree structures that elude competing methods. To ensure optimisation efficiency, we embed our MILP method into a hybrid framework, which applies MILP solvers precisely where they provide the greatest advantage over competitors, while relying on more efficient methods for less complex optimisation steps. Thereby, our approach gracefully scales to extremely large query sizes joining up to 100 relations, and consistently achieves the most robust plan quality among a large variety of competing join ordering methods.

翻译：寻找最优连接顺序是查询优化器需要执行的最关键步骤之一。尽管在数据管理研究中已得到广泛探讨，该问题远未解决：虽然查询优化器依赖穷举搜索方法来确定小型问题的理想解，但一旦查询规模增大，此类方法便达到极限。然而，大型查询在现实场景中日益普遍，需要合适的方法来生成高效的执行计划。尽管已有多种启发式方法被提出用于大规模查询优化，但正如我们将展示的，这些方法存在随着查询规模增大而解质量下降的问题，或表现出高度次优的最坏情况行为。我们提出了一种基于混合整数线性规划（MILP）范式的新方法：通过推导能够优化任意灌木树结构的新型MILP模型，我们解决了现有连接顺序MILP方法的局限性，并可以依赖高度优化的MILP求解器来推导出超越竞争方法的高效树结构。为确保优化效率，我们将MILP方法嵌入混合框架中，该框架在MILP求解器相比竞争方法最具优势的环节精确应用它们，同时对复杂度较低的优化步骤依赖更高效的方法。由此，我们的方法能够优雅地扩展到连接多达100个关系的极大查询规模，并在多种竞争性连接顺序方法中始终实现最稳健的计划质量。