优化 Cloudflare D1 数据库查询之旅

背景

最近，我一直在使用 Cloudflare Workers 和 D1 数据库进行服务器端项目开发。在这个过程中，我遇到了一些与数据库相关的挑战。由于数据库对前端开发人员来说相当陌生，我决定记录下我的经验。

下图显示了过去 30 天的请求记录，揭示了数据库查询的剧烈波动。

问题识别

解决问题始于识别问题。以下几种方法可以帮助我发现问题：

1. 监控 D1 dashboard，检测数据库操作中的异常情况
2. 检查查询语句和行读取/写入计数，特别关注具有高计数或行读取/写入的查询
3. 使用 c.env.DB.prepare('<sql>').run()).meta 来检查返回的元数据，这可以揭示每个查询实际读取/写入了多少行

使用 Batch Requests

重要的是要理解，虽然 Workers 和 D1 都是 Cloudflare 的服务，但一起使用它们并不会使 D1 更快。例如，像这样的简单查询的平均响应时间超过 200 毫秒：

await db.select().from(user).limit(1)

当一个 endpoint 包含多个数据库操作时，最好使用 D1 batch 操作，特别是对于写入操作，由于缺少只读副本，写入操作甚至比查询更慢。例如，不要这样做：

await db.insert(user).values({...})
await db.insert(tweet).values({...})

而是使用 batch 操作：

await db.batch([
 db.insert(user).values({...}),
 db.insert(tweet).values({...})
])

这种方法只需要向 D1 发送一个 REST 请求即可完成多个数据库写入操作。

注意 1：Prisma 不支持 D1 batch 操作，这导致我切换到 Drizzle。 注意 2：在对多个查询使用 batch 时要小心，特别是当表具有名称相同的列时。

从 Update 操作中排除 IDs

在更新记录时，重要的是排除 ID 字段（即使它保持不变）。考虑以下代码：

await db.update(user).set(userParam).where(eq(user.id, userParam.id))

实际执行的 SQL：

update "User" set "id" = ?, "screenName" = ?, "updatedAt" = ? where "User"."id" = ?

如果此 ID 被其他表中的外键引用，则可能导致大量行读取。 例如，如果 "tweet" 表有一个 userId 字段引用此 ID 并且包含 1,000 条记录，则更新包含该 ID 的用户将导致读取 2,005 行，而不是仅读取 1 行。 解决方案是从更新中显式排除 ID 字段：

const r = await db
 .update(user)
 .set(omit(userParam, ['id']))
 .where(eq(user.id, userParam.id))

无论存在多少相关记录，这都可以正确地将读取的行数限制为 1。

避免 Count 查询的全表扫描

我注意到 D1 dashboard 上一个 SQL 语句的行读取数很高：

SELECT count(id) as num_rows FROM "User";

这导致读取的行数显着增加，因此我实现了基于游标的分页，而不是基于偏移量的分页，并且从不提供总计数，因为即使使用索引 ID，计算记录也会扫描所有行。 这是 D1 的一个已知问题。

避免多表 Left Joins

我发现一个特定的 SQL 查询导致数十万行的读取：

SELECT "modlist"."id",
    "modlist"."updatedat",
    "modlistsubscription"."action",
    Json_group_array(DISTINCT "modlistuser"."twitteruserid"),
    Json_group_array(DISTINCT "modlistrule"."rule")
FROM  "modlist"
    LEFT JOIN "modlistsubscription"
       ON "modlist"."id" = "modlistsubscription"."modlistid"
    LEFT JOIN "modlistuser"
       ON "modlist"."id" = "modlistuser"."modlistid"
    LEFT JOIN "modlistrule"
       ON "modlist"."id" = "modlistrule"."modlistid"
WHERE "modlist"."id" IN ( ?, ? )
GROUP BY "modlist"."id",
     "modlistsubscription"."action";

此查询连接了四个表，可能会导致“笛卡尔积爆炸”。 如果 modListUser 和 modListRule 表都包含 100 条记录，则简单的连接可能会产生 10,000 个结果，这不是预期的行为。 解决方案是拆分查询并在应用程序逻辑中处理分组和转换：

await db.batch([
 db
  .select({
   modListId: modListUser.modListId,
   twitterUserId: modListUser.twitterUserId,
  })
  .from(modListUser)
  .where(eq(modListUser.modListId, 'modlist-1')),
 db
  .select({
   modListId: modListRule.modListId,
   rule: modListRule.rule,
  })
  .from(modListRule)
  .where(eq(modListRule.modListId, 'modlist-1')),
]) // 读取 200 行而不是 10,101 行

优化多记录插入

对于批量插入，不要使用：

await Promise.all(users.map((it) => db.insert(user).values(it)) as any)

甚至：

await db.batch(users.map((it) => db.insert(user).values(it)) as any)

我发现在一个语句中插入多个记录效率更高：

await db.insert(user).values(users)

但是，SQLite 和 D1 将每个查询的绑定参数数量限制为 100。 如果有 10 列，我们最多可以每个 SQL 语句插入 10 行。 对于较大的批次，我们需要对数据进行分块：

await db.batch(
 safeChunkInsertValues(user, users).map((it) =>
  db.insert(user).values(it),
 ) as any,
)

使用 5,000 条记录测试这种方法显示性能从 78 毫秒提高到 14 毫秒，使这种优化值得。

结论

服务器端问题与客户端问题有很大不同。 客户端错误只会影响用户，而服务器端错误会直接影响每月账单，并且可能需要一段时间才能显现出来。 因此，谨慎和彻底的单元测试至关重要。

在解决数据库查询问题时，我发现遵循以下流程很有帮助：发现、调查、尝试解决、监控、如果需要重试、继续监控，然后完成。 第一次尝试解决可能不会成功——甚至可能会使事情变得更糟——但持续监控对于及时检测和解决问题至关重要。