MySQL - 持久连接vs连接池 [英] MySQL - Persistent connection vs connection pooling
问题描述
为了避免每次针对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.
使用一个连接（如上所述）等于连接池最大大小为一。这将迟早会成为瓶颈，因为所有线程都必须等待连接。这可以是序列化数据库操作（以一定顺序执行）的选项，尽管有更好的选项来确保序列化。
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.
这篇关于MySQL - 持久连接vs连接池的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
