Java 从 DUAL 中选择 1：MySQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/917927/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SELECT 1 from DUAL: MySQL
提问by biggusjimmus
In looking over my Query log, I see an odd pattern that I don't have an explanation for.
在查看我的查询日志时，我看到了一个我无法解释的奇怪模式。
After practically every query, I have "select 1 from DUAL".
在几乎每次查询之后，我都会“从 DUAL 中选择 1”。
I have no idea where this is coming from, and I'm certainly not making the query explicitly.
我不知道这是从哪里来的，而且我当然没有明确提出查询。
The log basically looks like this:
日志基本上是这样的：
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 Version: 5.0.51
MySQL 版本：5.0.51
Driver: Java 6 app using JDBC. mysql-connector-java-5.1.6-bin.jar
驱动程序：使用 JDBC 的 Java 6 应用程序。mysql-connector-java-5.1.6-bin.jar
Connection Pool: commons-dbcp 1.2.2
连接池：commons-dbcp 1.2.2
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.
validationQuery 设置为“从 DUAL 中选择 1”（显然），显然当验证查询为非空时，连接池默认 testOnBorrow 和 testOnReturn 为 true。
One further question that this brings up for me is whether or not I actually needto 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.
这给我带来的另一个问题是我是否真的需要进行验证查询，或者我是否可以通过禁用它或至少降低它的使用频率来提高性能。不幸的是，编写我们“数据库管理器”的开发人员已不在我们身边，所以我不能要求他为我辩解。任何输入将不胜感激。我会在 API 和谷歌上挖掘一段时间，如果我发现任何有价值的东西，就会回来报告。
EDIT: added some more info
编辑：添加了更多信息
EDIT2: Added info that was asked for in the correct answer for anybody who finds this later
EDIT2：添加了正确答案中要求的信息，供以后发现此信息的任何人使用
采纳答案by Gareth Davis
It could be coming from the connection pool your application is using. We use a simple query to test the connection.
它可能来自您的应用程序正在使用的连接池。我们使用一个简单的查询来测试连接。
Just had a quick look in the source to mysql-connector-j and it isn't coming from in there.
刚刚快速查看了 mysql-connector-j 的源代码，它不是来自那里。
The most likely cause is the connection pool.
最可能的原因是连接池。
Common connection pools:
常见的连接池：
commons-dbcphas a configuration property validationQuery, this combined with testOnBorrowand testOnReturncould cause the statements you see.
commons-dbcp有一个配置属性validationQuery，这与testOnBorrow并testOnReturn可能导致您看到的语句。
c3p0has preferredTestQuery, testConnectionOnCheckin, testConnectionOnCheckoutand idleConnectionTestPeriod
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.
对于什么是值得的，我倾向于配置连接测试和结帐/借用，即使这意味着一些额外的网络聊天。
回答by Cody Caughlan
The "dual" table/object name is an Oracle construct, which MySQL supports for compatibility - or to provide a target for queries that dont have a target but people want one to feel all warm and fuzzy. E.g.
“双”表/对象名称是一种 Oracle 构造，MySQL 支持它以实现兼容性 - 或者为没有目标但人们希望感到温暖和模糊的查询提供目标。例如
select curdate()
can be
可
select curdate() from dual
Someone could be sniffing you to see if you're running Oracle.
有人可能会嗅探您以查看您是否在运行 Oracle。
回答by Durga Prasad
I have performed 100 inserts/deltes and tested on both DBCP and C3PO.
我已经执行了 100 次插入/删除并在 DBCP 和 C3PO 上进行了测试。
DBCP :: testOnBorrow=true impacts the response time by more than 4 folds.
DBCP :: testOnBorrow=true 影响响应时间超过 4 倍。
C3P0 :: testConnectionOnCheckout=true impacts the response time by more than 3 folds.
C3P0 :: testConnectionOnCheckout=true 影响响应时间超过 3 倍。
Here are the results : DBCP – BasicDataSource
结果如下：DBCP – BasicDataSource
Average time for 100 transactions ( insert operation ) testOnBorrow=false :: 219.01 ms testOnBorrow=true :: 1071.56 ms
100 个事务（插入操作）的平均时间 testOnBorrow=false :: 219.01 ms testOnBorrow=true :: 1071.56 ms
Average time for 100 transactions ( delete opration ) testOnBorrow=false :: 223.4 ms testOnBorrow=true :: 1067.51 ms
100 个事务的平均时间（删除操作） testOnBorrow=false :: 223.4 ms testOnBorrow=true :: 1067.51 ms
C3PO – ComboPooledDataSource Average time for 100 transactions ( insert operation ) testConnectionOnCheckout=false :: 220.08 ms testConnectionOnCheckout=true :: 661.44 ms
C3PO – ComboPooledDataSource 100 个事务（插入操作）的平均时间 testConnectionOnCheckout=false :: 220.08 ms testConnectionOnCheckout=true :: 661.44 ms
Average time for 100 transactions ( delete opration ) testConnectionOnCheckout=false :: 216.52 ms testConnectionOnCheckout=true :: 648.29 ms
100 个事务的平均时间（删除操作） testConnectionOnCheckout=false :: 216.52 ms testConnectionOnCheckout=true :: 648.29 ms
Conculsion : Setting testOnBorrow=true in DBCP or testConnectionOnCheckout=true in C3PO impacts the performance by 3-4 folds. Is there any other setting that will enhance the performance.
结论：在 DBCP 中设置 testOnBorrow=true 或在 C3PO 中设置 testConnectionOnCheckout=true 会影响性能 3-4 倍。有没有其他设置可以提高性能。
-Durga Prasad
- 杜尔加普拉萨德
