如何将Prisma批量Upsert逻辑转为原生SQL以提升性能？

针对Prisma循环upsert的性能瓶颈（100条数据耗时10+秒），以下是两种核心优化方向：

一、Prisma层面优化方案（无需切换纯原生SQL） #

1. 合并所有操作到单个事务 #

将原来的三个独立事务（transactionOne、transactionTwo、主模型创建）合并为一个大事务，减少数据库连接建立/销毁的开销，同时保证数据一致性。

2. 用原生批量Upsert替代循环单条操作 #

利用Prisma的$executeRawUnsafe执行批量SQL语句，避免循环生成单条upsert的网络往返。

示例代码（MySQL）：

await prisma.$transaction(async (tx) => {
  // 批量Upsert arrayOne数据
  const arrayOneValues = arrayOne.map(d => `('${d.id}', '${d.foo}', '${d.bar}')`).join(',');
  await tx.$executeRawUnsafe(`
    INSERT INTO "MODEL" (id, foo, bar)
    VALUES ${arrayOneValues}
    ON DUPLICATE KEY UPDATE foo = VALUES(foo);
  `);

  // 批量Upsert arrayTwo数据
  const arrayTwoValues = arrayTwo.map(d => `('${d.id}', '${d.foo}', '${d.bar}')`).join(',');
  await tx.$executeRawUnsafe(`
    INSERT INTO "MODEL" (id, foo, bar)
    VALUES ${arrayTwoValues}
    ON DUPLICATE KEY UPDATE foo = VALUES(foo);
  `);

  // 创建主模型并关联子模型
  return tx.MAIN_MODEL.create({
    data: {
      foo: foo,
      bar: bar,
      example: {
        connect: arrayOne.map(d => ({ id: d.id })),
      },
      exampleTwo: {
        connect: arrayTwo.map(d => ({ id: d.id })),
      },
    },
  });
});

注意：若使用PostgreSQL，替换Upsert语法为ON CONFLICT (id) DO UPDATE SET foo = EXCLUDED.foo;；原代码中transactionOne是Promise对象，直接调用map会报错，批量upsert后目标ID已存在于数据库，直接使用原始数组ID即可。

二、纯原生SQL事务方案 #

如果需要完全脱离Prisma ORM操作，直接执行原生SQL事务，以下是完整示例（MySQL）：

START TRANSACTION;

-- 批量Upsert arrayOne数据
INSERT INTO `MODEL` (id, foo, bar)
VALUES 
  ('id1', 'foo1', 'bar1'),
  ('id2', 'foo2', 'bar2'),
  -- 补充其他arrayOne数据
ON DUPLICATE KEY UPDATE foo = VALUES(foo);

-- 批量Upsert arrayTwo数据
INSERT INTO `MODEL` (id, foo, bar)
VALUES 
  ('id3', 'foo3', 'bar3'),
  ('id4', 'foo4', 'bar4'),
  -- 补充其他arrayTwo数据
ON DUPLICATE KEY UPDATE foo = VALUES(foo);

-- 创建主模型并获取ID
INSERT INTO `MAIN_MODEL` (foo, bar)
VALUES ('mainFoo', 'mainBar');

-- 关联example（假设关联表为main_model_example）
INSERT INTO `main_model_example` (main_model_id, model_id)
SELECT 1311904, id FROM `MODEL` WHERE id IN ('id1', 'id2', ...);

-- 关联exampleTwo（假设关联表为main_model_example_two）
INSERT INTO `main_model_example_two` (main_model_id, model_id)
SELECT 1311904, id FROM `MODEL` WHERE id IN ('id3', 'id4', ...);

COMMIT;

PostgreSQL版本需调整关联逻辑：创建主模型时用RETURNING id获取ID，再插入关联表数据。

性能提升说明 #

• 批量SQL操作将原来的N次网络请求减少到3-5次，200条数据量级的耗时可降低至数百毫秒。
• 单个事务避免了多次事务提交的锁开销，进一步提升效率。

内容的提问来源于stack exchange，提问作者frost2709