Skip to main content

使用 Prisma Optimize 进行查询优化

本指南介绍如何识别和优化查询性能、调试性能问题以及解决常见挑战。

¥This guide shows how to identify and optimize query performance, debug performance issues, and address common challenges.

调试性能问题​

¥Debugging performance issues

几种常见做法可能会导致查询速度慢和性能问题，例如：

¥Several common practices can lead to slow queries and performance problems, such as:

  • 过度获取数据

    ¥Over-fetching data

  • 缺少索引

    ¥Missing indexes

  • 不缓存重复查询

    ¥Not caching repeated queries

  • 执行全表扫描

    ¥Performing full table scans

信息

有关性能问题的更多潜在原因，请访问 此页面。

¥For more potential causes of performance issues, visit this page.

Prisma 优化 提供 recommendations 来识别和解决上面列出的低效率问题以及更多问题，从而帮助提高查询性能。

¥Prisma Optimize offers recommendations to identify and address the inefficiencies listed above and more, helping to improve query performance.

要开始，请按照 集成指南 并将 Prisma Optimize 添加到你的项目中以开始诊断慢速查询。

¥To get started, follow the integration guide and add Prisma Optimize to your project to begin diagnosing slow queries.

提示

你还可以使用 在客户端级别记录查询事件 查看生成的查询、它们的参数和执行时间。

¥You can also log query events at the client level to view the generated queries, their parameters, and execution times.

如果你特别关注监控查询持续时间，请考虑使用 日志中间件。

¥If you are particularly focused on monitoring query duration, consider using logging middleware.

使用批量查询​

¥Using bulk queries

批量读写大量数据通常性能更高 - 例如，将 50,000 记录分批插入 1000，而不是作为 50,000 单独插入。PrismaClient 支持以下批量查询：

¥It is generally more performant to read and write large amounts of data in bulk - for example, inserting 50,000 records in batches of 1000 rather than as 50,000 separate inserts. PrismaClient supports the following bulk queries:

重复使用 PrismaClient 或使用连接池以避免数据库连接池耗尽​

¥Reuse PrismaClient or use connection pooling to avoid database connection pool exhaustion

创建 PrismaClient 的多个实例可能会耗尽你的数据库连接池，尤其是在无服务器或边缘环境中，可能会减慢其他查询的速度。在 无服务器挑战 中了解更多信息。

¥Creating multiple instances of PrismaClient can exhaust your database connection pool, especially in serverless or edge environments, potentially slowing down other queries. Learn more in the serverless challenge.

对于具有传统服务器的应用，请实例化 PrismaClient 一次并在整个应用中重复使用它，而不是创建多个实例。例如，而不是：

¥For applications with a traditional server, instantiate PrismaClient once and reuse it throughout your app instead of creating multiple instances. For example, instead of:

query.ts
async function getPosts() {
  const prisma = new PrismaClient()
  await prisma.post.findMany()
}

async function getUsers() {
  const prisma = new PrismaClient()
  await prisma.user.findMany()
}

在专用文件中定义单个 PrismaClient 实例并重新导出以供重用：

¥Define a single PrismaClient instance in a dedicated file and re-export it for reuse:

db.ts
export const prisma = new PrismaClient()

然后导入共享实例：

¥Then import the shared instance:

query.ts
import { prisma } from "db.ts"

async function getPosts() {
  await prisma.post.findMany()
}

async function getUsers() {
  await prisma.user.findMany()
}

对于使用 HMR（热模块替换）框架的无服务器开发环境，请确保正确处理 正在开发中的 Prisma 单个实例。

¥For serverless development environments with frameworks that use HMR (Hot Module Replacement), ensure you properly handle a single instance of Prisma in development.

解决 n+1 问题​

¥Solving the n+1 problem

当你循环查询的结果并对每个结果执行一个额外的查询时，就会出现 n+1 问题，从而导致 n 次查询加上原始的 (n+1) 次查询。这是 ORM 的常见问题，特别是与 GraphQL 结合使用时，因为代码生成低效查询并不总是立即显而易见。

¥The n+1 problem occurs when you loop through the results of a query and perform one additional query per result, resulting in n number of queries plus the original (n+1). This is a common problem with ORMs, particularly in combination with GraphQL, because it is not always immediately obvious that your code is generating inefficient queries.

使用 findUnique() 和 Prisma 客户端的数据加载器在 GraphQL 中求解 n+1​

¥Solving n+1 in GraphQL with findUnique() and Prisma Client's dataloader

如果满足以下条件，Prisma Client 数据加载器会自动批处理在相同 tick 中出现且具有相同 where 和 include 参数的 findUnique() 查询：

¥The Prisma Client dataloader automatically batches findUnique() queries that occur in the same tick and have the same where and include parameters if:

  • where 过滤器的所有条件都位于你正在查询的同一模型的标量字段（唯一或非唯一）上。

    ¥All criteria of the where filter are on scalar fields (unique or non-unique) of the same model you're querying.

  • 所有条件都使用 equal 过滤器，无论是通过简写还是显式语法 (where: { field: <val>, field1: { equals: <val> } })。

    ¥All criteria use the equal filter, whether that's via the shorthand or explicit syntax (where: { field: <val>, field1: { equals: <val> } }).

  • 不存在布尔运算符或关系过滤器。

    ¥No boolean operators or relation filters are present.

findUnique() 的自动批处理在 GraphQL 上下文中特别有用。GraphQL 为每个字段运行单独的解析器函数，这使得优化嵌套查询变得困难。

¥Automatic batching of findUnique() is particularly useful in a GraphQL context. GraphQL runs a separate resolver function for every field, which can make it difficult to optimize a nested query.

例如 - 以下 GraphQL 运行 allUsers 解析器来获取所有用户，并为每个用户运行一次 posts 解析器来获取每个用户的帖子 (n+1)：

¥For example - the following GraphQL runs the allUsers resolver to get all users, and the posts resolver once per user to get each user's posts (n+1):

query {
  allUsers {
    id,
    posts {
      id
    }
  }
}

allUsers 查询使用 user.findMany(..) 返回所有用户：

¥The allUsers query uses user.findMany(..) to return all users:

const Query = objectType({
  name: 'Query',
  definition(t) {
    t.nonNull.list.nonNull.field('allUsers', {
      type: 'User',
      resolve: (_parent, _args, context) => {
        return context.prisma.user.findMany()
      },
    })
  },
})

这会产生一个 SQL 查询：

¥This results in a single SQL query:

{
  timestamp: 2021-02-19T09:43:06.332Z,
  query: 'SELECT `dev`.`User`.`id`, `dev`.`User`.`email`, `dev`.`User`.`name` FROM `dev`.`User` WHERE 1=1 LIMIT ? OFFSET ?',
  params: '[-1,0]',
  duration: 0,
  target: 'quaint::connector::metrics'
}

但是，每个用户都会调用 posts 的解析器函数一次。这会导致每个用户执行 findMany() 查询✘，而不是单个 findMany() 来返回所有用户的所有帖子（展开 CLI 输出以查看查询）。

¥However, the resolver function for posts is then invoked once per user. This results in a findMany() query ✘ per user rather than a single findMany() to return all posts by all users (expand CLI output to see queries).

const User = objectType({
  name: 'User',
  definition(t) {
    t.nonNull.int('id')
    t.string('name')
    t.nonNull.string('email')
    t.nonNull.list.nonNull.field('posts', {
      type: 'Post',
      resolve: (parent, _, context) => {
        return context.prisma.post.findMany({
          where: { authorId: parent.id || undefined },
        })
      },
    })
  },
})
Show CLI results

解决方案 1：使用流畅的 API 批量查询​

¥Solution 1: Batching queries with the fluent API

如图所示，将 findUnique() 与 流畅的 API（.posts()）结合使用以返回用户的帖子。即使每个用户调用一次解析器，Prisma 客户端中的 Prisma 数据加载器也会批量处理 findUnique() 查询。

¥Use findUnique() in combination with the fluent API (.posts()) as shown to return a user's posts. Even though the resolver is called once per user, the Prisma dataloader in Prisma Client ✔ batches the findUnique() queries.

信息

使用 prisma.user.findUnique(...).posts() 查询而不是 prisma.posts.findMany() 返回帖子似乎有悖常理 - 特别是因为前者会导致两个查询而不是一个。

¥It may seem counterintitive to use a prisma.user.findUnique(...).posts() query to return posts instead of prisma.posts.findMany() - particularly as the former results in two queries rather than one.

你需要使用 Fluent API (user.findUnique(...).posts()) 返回帖子的唯一原因是 Prisma 客户端中的数据加载器批量处理 findUnique() 查询，而当前不批量处理 批量 findMany() 查询。

¥The only reason you need to use the fluent API (user.findUnique(...).posts()) to return posts is that the dataloader in Prisma Client batches findUnique() queries and does not currently batch findMany() queries.

当数据加载器批量处理 findMany() 查询或你的查询将 relationStrategy 设置为 join 时，你不再需要以这种方式将 findUnique() 与流畅的 API 一起使用。

¥When the dataloader batches findMany() queries or your query has the relationStrategy set to join, you no longer need to use findUnique() with the fluent API in this way.

const User = objectType({
  name: 'User',
  definition(t) {
    t.nonNull.int('id')
    t.string('name')
    t.nonNull.string('email')
    t.nonNull.list.nonNull.field('posts', {
      type: 'Post',
      resolve: (parent, _, context) => {
        return context.prisma.post.findMany({
          where: { authorId: parent.id || undefined },
        })
        return context.prisma.user
          .findUnique({
            where: { id: parent.id || undefined },
          })
          .posts()
      },
    })
  },
})
Show CLI results

如果每个用户调用 posts 解析器一次，Prisma 客户端中的数据加载器会使用相同的参数和选择集对 findUnique() 查询进行分组。每个组都优化为一个 findMany()。

¥If the posts resolver is invoked once per user, the dataloader in Prisma Client groups findUnique() queries with the same parameters and selection set. Each group is optimized into a single findMany().

解决方案 2：使用 JOIN 执行查询​

¥Solution 2: Using JOINs to perform queries

你可以通过将 relationLoadStrategy 设置为 "join" 来使用 数据库连接 执行查询，确保只对数据库执行一个查询。

¥You can perform the query with a database join by setting relationLoadStrategy to "join", ensuring that only one query is executed against the database.

const User = objectType({
  name: 'User',
  definition(t) {
    t.nonNull.int('id')
    t.string('name')
    t.nonNull.string('email')
    t.nonNull.list.nonNull.field('posts', {
      type: 'Post',
      resolve: (parent, _, context) => {
        return context.prisma.post.findMany({
          relationLoadStrategy: "join",
          where: { authorId: parent.id || undefined },
        })
      },
    })
  },
})

其他情况下的 n+1​

¥n+1 in other contexts

n+1 问题在 GraphQL 上下文中最常见，因为你必须找到一种方法来优化跨多个解析器的单个查询。但是，你可以通过在自己的代码中使用 forEach 循环结果来轻松引入 n+1 问题。

¥The n+1 problem is most commonly seen in a GraphQL context because you have to find a way to optimize a single query across multiple resolvers. However, you can just as easily introduce the n+1 problem by looping through results with forEach in your own code.

以下代码会产生 n+1 次查询 - 一个 findMany() 获取所有用户，每个用户 1 个 findMany() 获取每个用户的帖子：

¥The following code results in n+1 queries - one findMany() to get all users, and one findMany() per user to get each user's posts:

// One query to get all users
const users = await prisma.user.findMany({})

// One query PER USER to get all posts
users.forEach(async (usr) => {
  const posts = await prisma.post.findMany({
    where: {
      authorId: usr.id,
    },
  })

  // Do something with each users' posts
})
Show CLI results
SELECT "public"."User"."id", "public"."User"."email", "public"."User"."name" FROM "public"."User" WHERE 1=1 OFFSET $1
SELECT "public"."Post"."id", "public"."Post"."title" FROM "public"."Post" WHERE "public"."Post"."authorId" = $1 OFFSET $2
SELECT "public"."Post"."id", "public"."Post"."title" FROM "public"."Post" WHERE "public"."Post"."authorId" = $1 OFFSET $2
SELECT "public"."Post"."id", "public"."Post"."title" FROM "public"."Post" WHERE "public"."Post"."authorId" = $1 OFFSET $2
SELECT "public"."Post"."id", "public"."Post"."title" FROM "public"."Post" WHERE "public"."Post"."authorId" = $1 OFFSET $2
/* ..and so on .. */

这不是一种有效的查询方式。相反，你可以：

¥This is not an efficient way to query. Instead, you can:

用 include 求解 n+1​

¥Solving n+1 with include

你可以使用 include 返回每个用户的帖子。这只会产生两个 SQL 查询 - 一种是为了获取用户，一种是为了获取帖子。这被称为 嵌套读取。

¥You can use include to return each user's posts. This only results in two SQL queries - one to get users, and one to get posts. This is known as a nested read.

const usersWithPosts = await prisma.user.findMany({
  include: {
    posts: true,
  },
})
Show CLI results
SELECT "public"."User"."id", "public"."User"."email", "public"."User"."name" FROM "public"."User" WHERE 1=1 OFFSET $1
SELECT "public"."Post"."id", "public"."Post"."title", "public"."Post"."authorId" FROM "public"."Post" WHERE "public"."Post"."authorId" IN ($1,$2,$3,$4) OFFSET $5

用 in 求解 n+1​

¥Solving n+1 with in

如果你有用户 ID 列表，则可以使用 in 过滤器返回 authorId 是 in 该 ID 列表的所有帖子：

¥If you have a list of user IDs, you can use the in filter to return all posts where the authorId is in that list of IDs:

const users = await prisma.user.findMany({})

const userIds = users.map((x) => x.id)

const posts = await prisma.post.findMany({
  where: {
    authorId: {
      in: userIds,
    },
  },
})
Show CLI results
SELECT "public"."User"."id", "public"."User"."email", "public"."User"."name" FROM "public"."User" WHERE 1=1 OFFSET $1
SELECT "public"."Post"."id", "public"."Post"."createdAt", "public"."Post"."updatedAt", "public"."Post"."title", "public"."Post"."content", "public"."Post"."published", "public"."Post"."authorId" FROM "public"."Post" WHERE "public"."Post"."authorId" IN ($1,$2,$3,$4) OFFSET $5

用 relationLoadStrategy: "join" 求解 n+1​

¥Solving n+1 with relationLoadStrategy: "join"

你可以通过将 relationLoadStrategy 设置为 "join" 来使用 数据库连接 执行查询，确保只对数据库执行一个查询。

¥You can perform the query with a database join by setting relationLoadStrategy to "join", ensuring that only one query is executed against the database.

const users = await prisma.user.findMany({})

const userIds = users.map((x) => x.id)

const posts = await prisma.post.findMany({
  relationLoadStrategy: "join",
  where: {
    authorId: {
      in: userIds,
    },
  },
})