在关系型数据库中查找和优化N+1查询
At Mixpanel, relational databases play a key role in the architecture of our web application. They store essential data, including user accounts, settings, and reports information. Given the database’s critical function, it is in a constant state of change, with many different teams working around. This interaction is vital for our dynamic development, but it also presents challenges.
在Mixpanel，关系型数据库在我们的Web应用程序的架构中起着关键作用。它们存储着包括用户账户、设置和报告信息在内的重要数据。由于数据库的关键功能，它处于不断变化的状态，有许多不同的团队在进行工作。这种互动对于我们的动态开发至关重要，但也带来了挑战。
One major challenge is the N+1 query problem. Essentially, what looks like a straightforward data fetch can trigger an avalanche of unnecessary queries, creating a domino effect that slows everything down and dampens the user experience. But here’s the good part, our team invested time and work on this and saw some impressive gains, boosting performance up to 10x in certain areas. By pinpointing and beefing up those slower endpoints, we’ve significantly upped our app’s performance and responsiveness.
一个主要的挑战是N+1查询问题。实际上，看起来很简单的数据获取可能会触发一系列不必要的查询，从而创建一个多米诺骨牌效应，减慢一切并降低用户体验。但好消息是，我们的团队投入了时间和工作，并在某些领域取得了令人印象深刻的收益，将性能提升了10倍。通过找出并加强那些较慢的端点，我们显著提高了应用程序的性能和响应能力。
How to find N+1 queries?
如何找到N+1查询？
Most of our API endpoint management and relational database interactions leverage the Django framework. In this section, we’ll use a small Django API specifically created for this blog post with two simple models. You can find the complete code on this github repository.
我们的大多数 API 端点管理和关系数据库交互都利用了 Django 框架。在本节中，我们将使用一个专门为本博客文章创建的小型 Django API，其中包含两个简单的模型。您可以在此 github 存储库上找到完整的代码。
A Board has many Report(s). Our API exposes this data through a /boards endpoint, which returns a JSON object like this.
一个 Board 有多个 Report。我们的 API 通过一个 /boards 端点暴露这些数据，返回一个像这样的 JSON 对象。
{
"<board_id>": {
"id": <board_id>,
"name": <board_name>,
"reports": [
{
"id": <report_id>,
"name": <report_name>,
},
]
}
}
{
"<board_id>": {
"id": <board_id>,
"name": <board_name>,
"reports": [
{
"id": <report_id>,
"name": <report_name>,
},
]
}
}
We populated ...