来自DUAL的SELECT 1：MySQL [英] SELECT 1 from DUAL: MySQL

问题描述

在查看我的查询日志时，我看到一个奇怪的模式，我没有解释。

In looking over my Query log, I see an odd pattern that I don't have an explanation for.

几乎每个查询后，我都有选择1来自DUAL。

After practically every query, I have "select 1 from DUAL".

我不知道它来自何处，我当然没有明确地进行查询。

I have no idea where this is coming from, and I'm certainly not making the query explicitly.

日志基本上如下所示：

    10 Query       SELECT some normal query
    10 Query       select 1 from DUAL
    10 Query       SELECT some normal query
    10 Query       select 1 from DUAL
    10 Query       SELECT some normal query
    10 Query       select 1 from DUAL
    10 Query       SELECT some normal query
    10 Query       select 1 from DUAL
    10 Query       SELECT some normal query
    10 Query       select 1 from DUAL
    ...etc...

以前是否有人遇到此问题？

Has anybody encountered this problem before?

MySQL版本：5.0.51

MySQL Version: 5.0.51

驱动程序：使用JDBC的Java 6应用程序。 mysql-connector-java-5.1.6-bin.jar

Driver: Java 6 app using JDBC. mysql-connector-java-5.1.6-bin.jar

连接池：commons-dbcp 1.2.2

Connection Pool: commons-dbcp 1.2.2

validationQuery设置为从DUAL中选择1（很明显），显然当验证查询非空时，连接池默认testOnBorrow和testOnReturn为true。

The validationQuery was set to "select 1 from DUAL" (obviously) and apparently the connection pool defaults testOnBorrow and testOnReturn to true when a validation query is non-null.

这给我带来的另一个问题是，我是否真的需要来获得验证查询，或者我是否可以通过禁用它或至少降低频率来提高性能它被使用了。不幸的是，编写我们的数据库管理员的开发人员不再和我们在一起了，所以我不能要求他为我辩护。任何输入将不胜感激。我将深入挖掘API并谷歌一段时间，如果我找到任何有价值的东西，我会报告。

One further question that this brings up for me is whether or not I actually need to have a validation query, or if I can maybe get a performance boost by disabling it or at least reducing the frequency with which it is used. Unfortunately, the developer who wrote our "database manager" is no longer with us, so I can't ask him to justify it for me. Any input would be appreciated. I'm gonna dig through the API and google for a while and report back if I find anything worthwhile.

编辑：添加更多信息

EDIT2：为以后发现的人提供了正确答案中要求的信息

Added info that was asked for in the correct answer for anybody who finds this later

推荐答案

它可能来自您的应用程序正在使用的连接池。我们使用一个简单的查询来测试连接。

It could be coming from the connection pool your application is using. We use a simple query to test the connection.

只是快速查看mysql-connector-j的源代码，它不是来自那里。

Just had a quick look in the source to mysql-connector-j and it isn't coming from in there.

最可能的原因是连接池。

The most likely cause is the connection pool.

公共连接池：

commons-dbcp 有一个配置属性 validationQuery ，这与 testOnBorrow 和 testOnReturn 可能会导致您看到的陈述。

commons-dbcp has a configuration property validationQuery, this combined with testOnBorrow and testOnReturn could cause the statements you see.

c3p0 preferredTestQuery ， testConnectionOnCheckin ， testConnectionOnCheckout 和 idleConnectionTestPeriod

对于它的价值，我倾向于配置连接测试和结账/借用，即使它意味着一点额外的网络聊天。

For what's it's worth I tend to configure connection testing and checkout/borrow even if it means a little extra network chatter.

这篇关于来自DUAL的SELECT 1：MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！