Reformulator: Automated Refactoring of the N+1 Problem in Database-Backed Applications
Reformulator: Automated Refactoring of the N+1 Problem in Database-Backed Applications
复制标题
Reformulator：数据库支持的应用程序中 N 1 问题的自动重构
DOI：
10.1145/3551349.3556911
复制
发表时间：
2022
期刊：
影响因子：
--
通讯作者：
Tip, Frank
中科院分区：
文献类型：
--
作者：
Turcotte, Alexi;Aldrich, Mark W.;Tip, Frank
对象关系映射（Object-Relational Mapping， ORM）为数据库提供了面向对象的接口，并促进了基于数据库的应用程序的开发。在ORM中，程序员不需要用单独的查询语言（如SQL）编写查询，而是编写由ORM映射到数据库查询的普通方法调用。这个添加的抽象层隐藏了数据库操作的重大性能成本，并且orm的误用可能导致生成的查询远远超过必要的数量。特别值得关注的是臭名昭著的“N+1问题”，其中初始查询产生N个结果，这些结果用于发出N个后续查询。这种反模式在使用orm的应用程序中很普遍，因为在面向对象语言中迭代集合是很自然的。但是，迭代源自数据库的数据并在每次迭代中调用ORM方法可能会导致性能次优。在这种情况下，通常可以通过发出一个更大的查询来一次获取所有所需的结果，从而减少到数据库的往返次数。我们提出了一种自动重构使用ORM的应用程序的方法，以消除“N+1问题”的实例，该问题依赖于静态分析来检测ORM API调用之间的数据流。我们在一个名为reformulator的工具中实现了这种方法，目标是JavaScript中的Sequelize ORM，并在8个JavaScript项目中对其进行了评估。我们在这些项目中发现了44个N+1查询对，reformulator成功地重构了它们，在保留程序行为的同时提高了性能（高达7.67倍）。进一步的实验表明，随着数据库大小的增加（高达38.58倍），相对性能的改进也在增加，并且表明前端页面加载时间也得到了改善。
An Object-Relational Mapping (ORM) provides an object-oriented interface to a database and facilitates the development of database-backed applications. In an ORM, programmers do not need to write queries in a separate query language such as SQL, they instead write ordinary method calls that are mapped by the ORM to database queries. This added layer of abstraction hides the significant performance cost of database operations, and misuse of ORMs can lead to far more queries being generated than necessary. Of particular concern is the infamous “N+1 problem”, where an initial query yields N results that are used to issue N subsequent queries. This anti-pattern is prevalent in applications that use ORMs, as it is natural to iterate over collections in object-oriented languages. However, iterating over data that originates from a database and calling an ORM method in each iteration may result in suboptimal performance. In such cases, it is often possible to reduce the number of round-trips to the database by issuing a single, larger query that fetches all desired results at once.We propose an approach for automatically refactoring applications that use ORMs to eliminate instances of the “N+1 problem”, which relies on static analysis to detect data flow between ORM API calls. We implement this approach in a tool called reformulator, targeting the Sequelize ORM in JavaScript, and evaluate it on 8 JavaScript projects. We found 44 N+1 query pairs in these projects, and reformulator refactored all of them successfully, resulting in improved performance (up to 7.67x) while preserving program behavior. Further experiments demonstrate that the relative performance improvements grew as the database size was increased (up to 38.58x), and show that front-end page load times were also improved.