MySQL - 持久连接与连接池

MySQL - Persistent connection vs connection pooling

提问by user1259642

In order to avoid the overhead of establishing a new connection each time a query needs fired against MySQL, there are two options available:

为了避免每次需要针对 MySQL 触发查询时建立新连接的开销，有两个选项可用：

1. Persistent connections, whereby a new connection is requested a check is made to see if an 'identical' connection is already open and if so use it.
2. Connection pooling, whereby the client maintains a pool of connections, so that each thread that needs to use a connection will check out one from the pool and return it back to the pool when done.

1. 持久连接，即请求新连接，检查是否已打开“相同”连接，如果已打开，则使用它。
2. 连接池，客户端维护一个连接池，以便每个需要使用连接的线程将从池中检出一个，并在完成后将其返回到池中。

So, if I have a multi-threaded server application expected to handle thousands of requests per second, and each thread needs to fire a query against the database, then what is a better option?

因此，如果我有一个多线程服务器应用程序预计每秒处理数千个请求，并且每个线程都需要对数据库进行查询，那么什么是更好的选择？

From my understanding, With persistent connections, all the threads in my application will try and use the same persistent connection to the database because they all are using identical connections. So it is one connection shared across multiple application threads - as a result the requests will block on the database side soon.

根据我的理解，使用持久连接，我的应用程序中的所有线程都将尝试使用与数据库相同的持久连接，因为它们都使用相同的连接。所以它是一个跨多个应用程序线程共享的连接——因此请求很快就会在数据库端阻塞。

If I use a connection pooling mechanism, I will have all application threads share a pool of connections. So there is less possibility of a blocking request. However, with connection pooling, should an application thread wait to acquire a connection from the pool or should it send a request on the connections in the pool anyway in a round-robin manner, and let the queuing if any, happen on the database?

如果我使用连接池机制，我将使所有应用程序线程共享一个连接池。所以阻塞请求的可能性较小。但是，对于连接池，应用程序线程是否应该等待从池中获取连接，还是应该以循环方式向池中的连接发送请求，并让队列（如果有）发生在数据库上？

回答by mrab

Having persistent connections does not imply that all threads use the same connection. It just "says" that you keep the connection open (in contradiction to open a connection each time you need one). Opening a connection is an expensive operation, so - in general - you try to avoid opening connections more often than necessary.

拥有持久连接并不意味着所有线程都使用相同的连接。它只是“说”您保持连接打开（与每次需要连接时打开连接相矛盾）。打开连接是一项代价高昂的操作，因此 - 通常 - 您尽量避免不必要地频繁打开连接。

This is the reason why multithreaded applications often use connection pools. The pool takes care of opening and closing connections and every thread that needs a connection requests one from the pool. It is important to take care that the thread returns the connection as soon as possible to the pool, so that another thread can use it.

这就是多线程应用程序经常使用连接池的原因。池负责打开和关闭连接，每个需要连接的线程都会从池中请求一个。重要的是要注意线程尽快将连接返回到池中，以便另一个线程可以使用它。

If your application has only a few long running threads that need connections you can also open a connection for each thread and keep this open.

如果您的应用程序只有几个长时间运行的线程需要连接，您还可以为每个线程打开一个连接并保持打开状态。

Using just one connection (as you described it) is equal to a connection pool with the maximum size one. This will be sooner or later your bottleneck as all threads will have to wait for the connection. This could be an option to serialize the database operations (perform them in a certain order), although there are better options to ensure serialisation.

仅使用一个连接（如您所描述的）等于最大大小为 1 的连接池。这迟早会成为您的瓶颈，因为所有线程都必须等待连接。这可能是序列化数据库操作（按特定顺序执行）的一个选项，尽管有更好的选项来确保序列化。

回答by Marcus Adams

Regarding your question about should the application server wait for a connection, the answer is yes.

关于应用服务器是否应该等待连接的问题，答案是肯定的。

MySQL connections are blocking. When you issue a request from MySQL server over a connection, the connection will wait, idle, until a response is received from the server.

MySQL 连接被阻塞。当您通过连接从 MySQL 服务器发出请求时，连接将等待、空闲，直到从服务器收到响应。

There is no way to send two requests on the same connection and see which returns first. You can only send one request at a time.

没有办法在同一个连接上发送两个请求并查看哪个先返回。您一次只能发送一个请求。

So, generally, a single thread in a connection pool consists of one client side connection (in your case, the application server is the client) and one server side connection (database).

因此，通常，连接池中的单个线程由一个客户端连接（在您的情况下，应用程序服务器是客户端）和一个服务器端连接（数据库）组成。

Your application should wait for an available connection thread from the pool, allowing the pool to grow when it's needed, and to shrink back to your default number of threads, when it's less busy.

您的应用程序应该等待池中的可用连接线程，允许池在需要时增长，并在不那么忙时缩回到默认线程数。