具有多个查询的NodeJS mysql连接池 [英] NodeJS mysql connection pool with multiple queries

问题描述

我对NodeJS相当陌生(从PHP背景开始).与PHP的阻塞性质相比，我对NodeJS的异步性质具有基本的了解.我创建了一些连接到mysql的应用程序Node，并使它们正常工作.

I'm fairly new to NodeJS (from a PHP background). I have a basic understanding of the asyncronous nature of NodeJS compared to the blocking nature of PHP. I've created a few apps Node that connect to mysql and got those working.

我目前对在Node中执行查询的最佳方式的理解是:

My current understanding of the best way to execute queries in Node is this:

• 创建连接池
• 当您需要执行查询时:
• 从池中获取getConnection()
• 执行查询
• .release()连接

我处于需要迭代大量项目并将每个项目插入数据库的情况.进行上述操作无效.我的想法是，由于每个查询都是并行执行的，因此列表中的下一项正在尝试获取连接，但是由于先前的查询可能尚未完成，因此没有可用的连接.没有引发任何错误，似乎没有放入任何项目.通过日志记录，我发现它永远不会到达运行pool.getConnection()回调的地步.

I'm in a situation where I need to iterate of a large number of items and insert each to the database. Doing the above didn't work. My thought was that since each query is executed in parallel, the next item in the list was trying to acquire a connection, but none were available since the previous queries may not have completed yet. No errors were thrown, it just seemed like items weren't being put in. Through logging I found that it was never getting to the point of running the pool.getConnection() callback.

所以我认为我可以修改连接流程:

So I thought I could revise the connection flow:

• 创建连接池
• 当您需要执行许多查询时:
• 从池中获取getConnection()
• 重复需要查询的项目
• 执行查询
• .release()连接

与此有关的问题是，我怎么知道对.release()连接的安全性?我不能只在我的.query()回调函数中执行此操作，因为它之后可能会有更多查询.但是我也不能在查询回调中做到这一点，因为我知道我需要等待所有查询完成.我是否要负责创建自己的逻辑，以弄清楚何时完成所有查询(以及所有可能的未来查询，即使尚未开始)?还是这只是未完成的事情?

The issue with this is, how do I know when it's safe to .release() my connection? I can't just do it in my .query() callback because there may be more queries after it. But I also can't not do it in a query callback because I know I need to wait for all queries to finish. Am I responsible for creating my own logic to figure out when all queries (and all possible future queries, even if they aren't started yet) are done? Or is this just something that isn't done?

在PHP中，这是微不足道的，因为它具有阻塞性.我选择将NodeJS用于此应用程序，因为它有时确实需要异步.但这在我的应用不需要它的地方(在某些情况下不应该使用它)迫使我产生异步性.

In PHP this would be trivial because of it's blocking nature. I chose to use NodeJS for this application because it does need to be asynchronous at times. But it forces asynchronicity on me in places my app doesn't need it (an in some cases shouldn't use it).

我很不愿意发布我的代码，因为它有点复杂，所以我不想分散这个特定的主题.

I'm hesitant to post my code because it's a bit complicated so I didn't want to distract from this specific topic.

推荐答案

您的连接流程很好.如果仅执行1个查询，则实际上不需要执行任何步骤，您可以直接在池上调用 .query().(其余的都在引擎盖下).

Your connection flow is fine. If you only do 1 query, you don't actually need to do any of the steps and you can just call .query() on the pool directly. (Which does the rest under the hood).

大多数情况下，您需要在1个事务中执行多个查询，但是我会添加事务支持:

The situation where you need to do multiple queries in 1 transaction is mostly, but I would add transaction support:

• 创建连接池
• 当您需要执行许多查询时:
• 从池中获取getConnection()
• 开始交易
• 重复需要查询的项目
• 执行查询
• 如果有任何失败，请停止并回滚
• 如果全部成功，则提交
• .release()连接

如果所有查询都已完成"，则您知道何时释放连接.您怎么知道它完成了?有不止一种方法.我建议您使用Promise API，所以它看起来可能像这样:

You know when to release the connection if all the queries are 'done'. How do you know it's done? There's more than one way. I would recommend you use the promise API, so it might look like this:

async function doStuff(items) {

  try { 
    const connection = await pool.getConnection();
    await connection.beginTransaction();
    for(const item of items) {
      await connection.query('....');
    }
    await connection.commit();
  } catch (e) {
    await connection.rollback();
    throw e;
  } finally {
    await connection.release();
  }

}

此模式有一些优点:

1. 它将正确报告错误
2. 它将在成功和错误时释放连接.
3. 它要么完全失败，要么完全成功.在这里没有半招半招.

如果您不关心交易，可以将其简化:

If you don't care about transactions, this can be simplified:

async function doStuff(items) {

  try { 
    const connection = await pool.getConnection();
    for(const item of items) {
      await connection.query('....');
    }
  } finally {
    await connection.release();
  }

}

与此相关的问题是，您可以获得部分成功，这通常是不希望的，尤其是对于API而言.但是，如果对您来说足够好，那就足够了.

The issue with this is that you can get partial successes, which is often not desirable, especially for APIs. However, if it's good enough for you, it's good enough.

如果您不愿意进行任何交易，从理论上讲，您可以完全跳过 getConnection 步骤:

And if you are comfortable not having transactions, you can in theory completely skip the getConnection step:

async function doStuff(items) {
  for(const item of items) {
    await pool.query('....');
  }
}

含义是所有查询都可能在不同的连接上执行.这可能会给您带来较差的性能，但会使代码更简单.

The implication is that all your queries might execute on different connections. This may give you worse performance, but makes for simpler code.

如何启用承诺?

这有点争议.为此，您可能需要切换mysql软件包.有一个 mysql2 软件包，它具有一个非常出色的 mysql2/promise 导入(并且实际上与更流行的 mysql 软件包共享代码).超级建议切换到此.

Well this is a bit controversial. To do this, you might need to switch mysql packages. There is a mysql2 package that has a mysql2/promise import that's really excellent (and actually shares code with the more popular mysql package). It's super recommended to switch to this.

如果我不想切换软件包怎么办?

嗯，这个的回调版本要痛苦得多.在这种情况下，我建议您仍然使用Promise，但可以将您的回调转换为Promise模式，也许使用"promisify".

Well, the callback version of this is a lot more painful. I would in this case recommend still using promises but converting your callbacks to a promise pattern, perhaps using 'promisify'.

如果您真的不希望在任何地方做出承诺，则基本上必须将我的示例转换为基于回调的示例，这看起来会更加痛苦.

If you really don't want promises anywhere, you basically have to convert my examples to callback-based ones, which is going to look a lot more painful.

这篇关于具有多个查询的NodeJS mysql连接池的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！