确定 MySQL 连接是否仍然有效的最便宜的方法
我有一个用于基于 Web 的数据服务的 MySQL 连接池。当它开始服务请求时，它会从池中获取一个连接来使用。问题是，如果自使用该特定连接以来出现了明显的暂停，服务器可能会超时并关闭其结束。我希望能够在池管理代码中检测到这一点。
诀窍是这样的：我编码的环境只为我提供了一个非常抽象的连接 API。我基本上只能执行SQL语句。我无法访问实际的套接字或直接访问 MySQL 客户端 API。
所以，问题是：我可以在连接上执行以确定它是否正常工作的最便宜的 MySQL 语句是什么。例如 SELECT 1; 应该可以，但我想知道是否有更便宜的东西？也许有些东西甚至不通过网络，而是在 MySQL 客户端库中处理并有效地回答相同的问题？
澄清：我不关心检查 MySQL 服务器是否正在运行，或者它的数据库配置是否足以回答查询。如果这些事情发生了，那么服务执行的后续 SQL 将获取并处理适当的错误。我只真正关心 TCP 连接是否打开......因为如果服务器关闭它，那么 Web 服务的 SQL 将收到一个错误，这意味着“只需重新连接并重试”，并且一旦关闭就会很不方便服务代码的垃圾。
I have a pool of MySQL connections for a web-based data service. When it starts to service a request, it takes a connection from the pool to use. The problem is that if there has been a significant pause since that particular connection has been used, the server may have timed it out and closed its end. I'd like to be able to detect this in the pool management code.
The trick is this: The environment in which I'm coding gives me only a very abstract API into the connection. I can basically only execute SQL statements. I don't have access to the actual socket or direct access to the MySQL client API.
So, the question is: What is the cheapest MySQL statement I can execute on the connection to determine if it is working. For example SELECT 1; should work, but I'm wondering if there is something even cheaper? Perhaps something that doesn't even go across the wire, but is handled in the MySQL client lib and effectively answers the same question?
Clarification: I'm not concerned about checking if the MySQL server is running, or if it's database configuration is up enough to answer queries. If those things are down, then the subsequent SQL the service executes will get and handle the appropriate error. I'm only really concerned with if the TCP connection is open… since if the server closed it, then the web service's SQL will get an error that means "just reconnect and try again", and that would be inconvenient to do once down in the muck of the service code.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您不通过线路就无法知道连接的真实状态，并且
SELECT 1是一个足够好的候选者（可以说您可以想出一个更短的命令，该命令需要解析时间更少，但与网络甚至环回延迟相比，这些节省的时间是微不足道的。）话虽这么说，我认为在从池中检查连接之前ping连接并不是 很重要最好的方法。
您可能应该简单地让您的连接池管理器强制执行自己的保持活动（超时）策略，以避免被服务器断开连接（没有更严重的干预连接问题，这可能会影响您在无论如何，您的连接池管理器都无法提供帮助），以及为了不不必要地占用数据库（想想文件句柄和内存使用）。
因此，在我看来，在从池中检查连接之前测试连接条件到底有什么价值是值得怀疑的。在将连接签入池之前，可能值得测试连接状态，但这可以通过在出现 SQL 硬错误（或等效异常）时简单地将连接标记为脏来隐式完成（除非您使用的 API 已经向您公开了类似
is-bad的调用。）因此，我建议：
更新
从您的评论中可以看出您确实真的想要 ping 连接（我认为这是因为您没有完全控制或了解 MySQL 服务器或中间网络设备（例如代理等）的超时特性。）
在这种情况下，您可以使用
DO 1作为SELECT 1的替代方案;它稍微更快 - 解析时间更短，并且它不返回实际数据（尽管您将获得 TCPack，所以您仍将进行往返验证连接是否仍已建立。）更新 2
关于 Joshua 的帖子，这里是各种数据包捕获跟踪场景：
如您所见，除了
mysql_ping数据包为 5 个字节而不是DO 1;的 9 个字节之外，往返次数（因此，网络引起的延迟）完全相同。与mysql_ping相比，您使用DO 1支付的唯一额外成本是解析DO 1，这是微不足道的。You will not know the real state of the connection without going over the wire, and
SELECT 1is a good enough candidate (arguably you could come up with a shorter command which takes less time to parse, but compared to network or even loopback latency those savings would be insignificant.)This being said, I would argue that pinging a connection before checking it out from the pool is not the best approach.
You should probably simply have your connection pool manager enforce its own keep-alive (timeout) policy to avoid being disconnected by the server (short of a more serious intervening connectivity issue, which could affect you smack in the middle of regular operations anyway -- and which your connection pool manager would be unable to help with anyway), as well as in order not to hog the database (think filehandles and memory usage) needlessly.
It is therefore questionable, in my opinion, what value testing for connectivity condition before checking out a connection from the pool really has. It may be worth testing connection status before a connection is checked in back into the pool, but that can be done implicitly by simply marking the connection as dirty when an SQL hard error (or equivalent exception) arises (unless the API you are using already exposes a
is-bad-like call for you.)I would therefore recommend:
UPDATE
It would appear from your comments that you really really want to ping the connection (I assume that is because you don't have full control over, or knowledge of, timeout characteristics on the MySQL server or intervening network equipment such as proxies etc.)
In this case you can use
DO 1as an alternative toSELECT 1; it is marginally faster -- shorter to parse, and it does not return actual data (although you will get the TCPacks, so you will still do the roundtrip validating that the connection is still established.)UPDATE 2
Regarding Joshua's post, here's packet capture traces for various scenarios:
As you can see, except for the fact that the
mysql_pingpacket is 5 bytes instead ofDO 1;'s 9 bytes, the number of roundtrips (and consequently, network-induced latency) is exactly the same. The only extra cost you are paying withDO 1as opposed tomysql_pingis the parsing ofDO 1, which is trivial.我不确定您当前使用的是什么 API（或什么语言），但是对于 Java，JDBC 驱动程序可以执行一个特殊的技巧。
标准测试查询是：
正如您所指出的。如果将其修改为：
JDBC 驱动程序将注意到这一点，并且仅向 MySQL 服务器发送单个数据包以获得响应。
我在 Sun 的“Deep Dive”节目中了解了这一点，标题为 Mark Matthews 为 Java 开发人员提供的 MySQL 技巧< /a>.
即使您不使用 Java，也许其他 mysql 驱动程序中也实现了同样的技巧？我假设服务器需要知道这个特殊的数据包，以便它可以发送响应......
I'm not sure what API you are currently using (or what language), but for Java, there is a special trick the JDBC driver can do.
The standard test query is:
as you've indicated. If you modify it to:
the JDBC driver will notice this, and send only a single packet to the MySQL server to get a response.
I learned about this at a Sun 'Deep Dive' episode titled MySQL Tips for Java Developers With Mark Matthews.
Even if you aren't using Java, maybe this same trick has been implemented in other mysql drivers? I assume the server would need to be aware of this special packet so it can send a response...
这里的“连接”有多重含义。 MySQL 监听套接字——这是网络级的“连接”。 MySQL 维护“数据库连接”，其中包括查询执行的上下文和其他开销。
如果您只想知道服务是否正在侦听，您应该能够执行网络级调用来查看端口（不知道默认值是什么）是否正在侦听目标 IP。如果您想让 MySQL 引擎做出响应，我认为您的 SELECT 1 想法很好 - 它实际上并不从数据库中获取任何数据，但确实确认引擎已启动并响应。
"Connection" in this case has multiple meanings. MySQL listens on a socket- that's the network-level "connection." MySQL maintains "database connections," which include a context for query execution and other overhead.
If you just want to know if the service is listening, you should be able to execute a network-level call to see if the port (don't know what the default is) is listening on the target IP. If you want to get the MySQL engine to respond, I think your
SELECT 1idea is good- it doesn't actually fetch any data from the database but does confirm that the engine is spun up and responding.