什么是 N+1 查询问题以及如何解决？

zzr-rr

已于 2024-08-06 10:09:40 修改

阅读量933

点赞数 10

文章标签：数据库后端 php

于 2024-08-06 09:40:35 首次发布

本文链接：https://blog.csdn.net/Zzzr_3210/article/details/140945960

版权

原文链接：What is the N+1 Query Problem and How to Solve it? (planetscale.com)

通过一个示例 N+1 查询的例子来学习什么是 N+1 查询问题，如何将其更新为 JOIN 语句，并学习如何在未来识别它们。

什么是 N+1 查询问题？
N+1 查询的示例
什么导致了 N+1 查询问题？
为更复杂的查询创建数据结构
识别 N+1 查询
PlanetScale Insights

你是否曾在开发应用的过程中，盯着屏幕等待加载，想知道到底发生了什么？你可能会遇到性能问题的原因有很多，但在数据库驱动的应用中，一个经典的性能问题源头就是令人头疼的N+1查询问题。

什么是 N+1 查询问题?

这个问题的主要症状是执行了许多许多的查询。通常，这种情况发生在你将代码结构化，首先进行一次查询以获取记录列表，然后对这些记录中的每一条都进行另一次查询。

你可能会期望许多小的查询会很快，而一个大的、复杂的查询会很慢。这种情况很少发生。在实践中，情况恰恰相反。每个查询都必须发送到数据库，数据库必须执行查询，然后将结果发送回你的应用。你执行的查询越多，获取结果的时间就越长，每次到数据库服务器的行程都需要时间和资源。相比之下，即使是复杂的单个查询，也可以由数据库服务器优化，并且只需要一次到数据库的行程，这通常比许多小查询要快得多。

一个 N+1 查询的例子

提示

当你阅读这些例子时，你可以在这里查看一个实时演示，详细说明了结果和查询运行时间。

让我们看一个例子。应用通常会从同一数据库表中查询多个相关记录。让我们以我之前关于连接的文章中的杂货物品和类别为例。在这个示例场景中，我们有一个PlanetScale 数据库，其中有一个 items 表和一个 categories 表。items 表包含了杂货店物品的列表，它们对应的类别在 categories 表中。例子是用 PHP 写的，但同样的原理适用于任何语言。

categories 表：

id	name
1	Produce
2	Deli
3	Dairy

items 表：

id	name	category_id
1	Apples	1
2	Cheese	2
3	Bread	NULL

假设我们希望我们的应用列出所有的物品，包括它们所属的类别的名称。我们可以通过首先查询类别列表，然后遍历每个类别，查询每个类别的物品，这是一种直接的方法。

第一次查询 —— 获取类别：

PHP

<?php

    $dbh = new Dbh();

    $conn = $dbh->connect();

    $sql = "SELECT * FROM categories;";

    $stmt = $conn->prepare($sql);

    $stmt->execute();

第二次查询 —— 遍历每个类别并获取物品：

PHP

<?php

while ($row = $stmt->fetch()) {

    // Show category name

    echo $row['name'];

    // Now query for the items for this category

    $sql = "

        SELECT id, name FROM items

        WHERE category_id = :category_id

        ORDER BY name;

    ";

    $stmt2 = $conn->prepare($sql);

    $stmt2->bindParam(':category_id', $row['id']);

    $stmt2->execute();

    $rowCount += $stmt2->rowCount();

    while ($row2 = $stmt2->fetch()) {

        // Show item ID and name

        echo $row2['id'];

        echo $row2['name'];

    }

}

这种方法的优点是有两个简单的查询和清晰的过程代码。不幸的是，这种方法存在缺陷，你应该避免在循环中执行许多数据库查询的情况。

什么导致了 N+1 查询问题?

这种类型的查询执行通常被称为 "N+1 查询"，因为你没有在一个查询中完成工作，而是运行一个查询来获取类别列表，然后对每个 N 类别运行另一个查询。因此，有了 "N+1 查询"这个术语。

你可以在这里找到运行结果的演示：演示 —— N+1 查询获取类别和物品

在上面的例子中，我们的数据库包含了大约 800 个物品，分布在 17 个类别中。运行这 18 个简单查询需要超过 1 秒！这相当慢。如果你有更复杂的查询和大量的数据，它将需要更长的时间。

对于这个简单的例子，我们可以通过只使用一个使用 JOIN 子句的查询来完成完全相同的工作，速度可以快 10 倍。我们可以重构上面的代码，使其看起来像这样：

<?php

    $dbh = new Dbh();

    $conn = $dbh->connect();

    // Record the time before the query is executed

    $timeStart = microtime(true);

    $sql = "

        SELECT

            c.id AS category_id,

            c.name AS category_name,

            i.id AS item_id,

            i.name AS item_name

        FROM categories c

        LEFT JOIN items i ON c.id = i.category_id

        ORDER BY c.name, i.name;

    ";

    $stmt = $conn->prepare($sql);

    $stmt->execute();

    $rowCount = $stmt->rowCount();

    $lastCategoryId = null;

    while ($row = $stmt->fetch()) {

        // Render the heading for each category if this category is new

        if ($row['category_id'] != $lastCategoryId) {

            echo $row['category_name'];

        }

        // Display the row for each item

        if (!is_null($row['item_id'])) {

            echo $row['item_id'];

            echo $row['item_name'];

        }

        $lastCategoryId = $row['category_id'];

    }

通过这次更新，我们用一个稍微复杂一点的查询完成了大致相同的工作。再次尝试我们的演示，我们可以观察到原始页面和这个页面之间的显著性能差异！页面在大约 0.16 秒内加载完毕，而不是 1.4 秒。

在这个简单的例子中，对于一个并不非常大的数据库，n+1 的方法大约需要10 倍的时间！

想象一下，如果你有数千甚至数百万的记录。性能差异可能是合理的加载时间和页面加载时间过长导致服务器超时之间的差异。

为更复杂的查询创建数据结构

有时候你可能有一个更复杂的操作在脑海中。比如说你想显示类别以及每个类别中每个物品的数量。你可以使用一个聚合查询（GROUP BY），如下所示：

SELECT c.id, c.name, count(i.id) AS item_count FROM categories c

LEFT JOIN items i ON c.id = i.category_id

GROUP BY c.id, c.name

ORDER BY c.name;

但是，我们如何从这样一个我们正在分组的查询中也获取物品列表呢？

虽然让数据库服务器做大部分繁重的工作而不是我们的服务器端代码通常是最有效的，但对于像简单的物品计数这样的事情，可能不是必要的。如果我们实际上只是查询了物品，让服务器端代码（在我们的例子中是 PHP）为我们做计数是非常容易的！

我们可以重构这个，使我们用一个查询完成工作，然后将这个查询转化为一个干净的数据结构。

<?php

$dbh = new Dbh();

$conn = $dbh->connect();

// Record the time before the query is executed

$timeStart = microtime(true);

$sql = "

    SELECT

        c.id AS category_id,

        c.name AS category_name,

        i.id AS item_id,

        i.name AS item_name

    FROM categories c

    -- Using a normal JOIN would not get the categories with 0 items

    LEFT JOIN items i ON c.id = i.category_id

    ORDER BY c.name, i.name;

";

$stmt = $conn->prepare($sql);

$stmt->execute();

$rowCount = $stmt->rowCount();

$lastCategoryId = null;

$lastCategoryName = null;

// Build a 2D array of categories with their items

$categories = [];

// A categoryItems array will become the value for each category

$categoryItems = [];

// Alternative approach: build a data structure with the data we want as a 2D array.

while ($row = $stmt->fetch()) {

    // Render the heading for each category if this category is new

    if (!is_null($lastCategoryId) && $row['category_id'] != $lastCategoryId) {

        $categories[$lastCategoryName] = $categoryItems;

        // Reset the categoryItems array

        $categoryItems = array();

    }

    // Create an array of all the non-null items

    if (!is_null($row['item_id'])) $categoryItems[$row['item_id']] = $row['item_name'];

    $lastCategoryId = $row['category_id'];

    $lastCategoryName = $row['category_name'];

}

// Add the last category to the array with its items

$categories[$lastCategoryName] = $categoryItems;

现在，我们有了这个 $categories 数组，其中包含了物品的数组，我们可以做一个嵌套的循环来按我们希望的方式渲染数据。当我们想要物品的数量时，你可以简单地运行 count($items) 来获取数量。

<?php

foreach ($categories as $categoryName => $items) {

    echo $categoryName;

    // Show the count of items in the category

    echo count($items) . ' items';

    if (count($items)) {

        // Loop through all the items in the category and display them

        foreach($items as $itemId => $itemName) {

            echo $itemId;

            echo $itemName;

        }

    }

使用这样的技术，你可以通过有效地使用数据库来保持你的页面加载时间非常快。而不是写你的代码，使你有 1 个查询加上该查询的每条记录的另一个查询，你最好花点努力写你的代码，使你有 1 个查询返回你需要的所有数据。

使用这种方法，你还可以创建对你的应用更有用的数据结构。例如，你可能想要创建一个以类别 ID 为键，然后将物品作为子数组的数据结构。这将允许你通过其 ID 轻松访问特定类别的物品。

识别 N+1 查询

如果你有一个更复杂的应用，你可能有很多的 N+1 查询并且不知道。有几种方法可以识别这些查询并修复它们。

如果你正在开发 Laravel 应用，你可以使用 Laravel Debug Bar。Laravel 还允许你通过在 AppServiceProvider 中的 boot 方法添加以下行来完全禁用 N+1 查询：

Model::preventLazyLoading(!app()->isProduction());

这将导致应用在非生产环境下检测到 N+1 查询时抛出异常，从而让你能够检测并修复这些问题。

PlanetScale Insights

PlanetScale 还提供了一个名为 PlanetScale Insights 的分析和监控解决方案。你可以从你的 PlanetScale 仪表板访问这个，它允许你看到在你的数据库上运行的查询。使用这个，你可以识别你的查询中的许多类型的问题，包括 N+1 查询和长时间运行的查询。下面的截图来自我们在本文中使用的演示数据库。

第一个查询是我们更复杂但更高效的 JOIN 查询，它读取了 834 行，返回了 815 行，总共花费了 14ms。

下面的两个查询是导致 N+1 问题的低效查询。它们一共花费了 42ms 和 13,889 行的读取，给我们提供了与更复杂查询相同的结果。

总的来说，这立即告诉我们我们的 N+1 查询：

运行的次数太多
读取的行数比返回的行数多
性能相对较慢

现在你知道如何识别 N+1 查询，如何修复它们，以及如何使用 PlanetScale Insights 监控你的查询并识别性能问题，所以你可以出去编写一些快速、精简的代码！

zzr-rr

关注

10
点赞
踩
22

收藏

觉得还不错? 一键收藏
打赏
0
评论
什么是 N+1 查询问题以及如何解决？

你可能会期望许多小的查询会很快，而一个大的、复杂的查询会很慢。你可以从你的 PlanetScale 仪表板访问这个，它允许你看到在你的数据库上运行的查询。使用这个，你可以识别你的查询中的许多类型的问题，包括 N+1 查询和长时间运行的查询。虽然让数据库服务器做大部分繁重的工作而不是我们的服务器端代码通常是最有效的，但对于像简单的物品计数这样的事情，可能不是必要的。而不是写你的代码，使你有 1 个查询加上该查询的每条记录的另一个查询，你最好花点努力写你的代码，使你有 1 个查询返回你需要的所有数据。
复制链接

扫一扫