MySQL - 持久连接与连接池
为了避免每次需要针对 MySQL 触发查询时建立新连接的开销，有两个选项可用:
In order to avoid the overhead of establishing a new connection each time a query needs fired against MySQL, there are two options available:
- 持久连接，即请求新连接，检查是否已打开相同"连接，如果已打开，则使用它.
- 连接池，客户端维护一个连接池，以便每个需要使用连接的线程都会从池中检出一个，并在完成后将其返回到池中.
那么，如果我有一个多线程服务器应用程序预计每秒处理数千个请求，并且每个线程都需要对数据库进行查询，那么什么是更好的选择?
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?
推荐答案
拥有持久连接并不意味着所有线程都使用相同的连接.它只是说"您保持连接打开(与每次需要连接时打开连接相矛盾).打开连接是一项代价高昂的操作，因此 - 一般而言 - 您应尽量避免不必要地频繁打开连接.
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.
仅使用一个连接(如您所描述的那样)等于最大大小为 1 的连接池.这迟早会成为您的瓶颈，因为所有线程都必须等待连接.这可能是序列化数据库操作的一个选项(按特定顺序执行它们)，尽管有更好的选项来确保序列化.
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.
相关文章