JDBC与MySQL真的很慢，不知道为什么 [英] JDBC with MySQL really slow, don't know why

问题描述

我的Java代码和MySQL数据库之间的连接速度确实很慢.我不知道瓶颈在哪里.

我的程序或多或少是一个聊天机器人.用户输入内容后，我的程序将句子分解为单词，然后将每个单词的单词发送到数据库.如果在该处找到了某些内容，则用户会得到一个输出. 该数据库位于外部服务器上，但我也尝试连接到我旁边的PC.两者都很慢.

在大多数情况下，我在正常工作的另一个地方尝试了一次连接，

我的SQL代码:

从信息信息INFO_SCHLUESSEL sch中选择信息info.INFORMATION
在何处使用LCASE(sch.SCHLUESSELWORT)，例如'"+输入+"％"AND info.ID_INFO = sch.ID_INFO
按信息订购.PRIORITAETDESC LIMIT 1;

(请记住，如果它有助于理解sql代码:
schluessel =键
Schluesselwort =关键字
优先级=优先级)

我的Java数据库代码或多或少是标准内容:

字符串驱动程序="com.mysql.jdbc.Driver";
字符串dbase ="jdbc:mysql://bla";
字符串dbuser ="bla";
字符串dbpw ="bla";

Class.forName(驱动程序);
连接con = DriverManager.getConnection(dbase，dbuser，dbpw);
语句stmt = con.createStatement();

ResultSet rs = stmt.executeQuery(query);
而(rs.next())
{
ergebnis = rs.getString("info.INFORMATION");
}

rs.close();
stmt.close();
con.close();

我已经尝试了一段时间该DBCP，但似乎无法正常工作.它似乎和旧的连接一样慢.这是我使用的网站提供的示例:

GenericObjectPool connectionPool =新的GenericObjectPool(null);
ConnectionFactory connectionFactory =新的DriverManagerConnectionFactory("jdbc:mysql://bla"，"bla"，"bla");
PoolableConnectionFactory poolableConnectionFactory =新的PoolableConnectionFactory(connectionFactory，connectionPool，null，null，false，true);
PoolingDriver驱动程序=新的PoolingDriver();
driver.registerPool("example"，connectionPool);
连接conn = DriverManager.getConnection("jdbc:apache:commons:dbcp:example");

解决方案

我怀疑是导致问题的原因是连接设置.值得花多长时间进行计时:

Connection con = DriverManager.getConnection(dbase, dbuser, dbpw);

，如果是这样，请检出 Apache Commons DBCP ，该库可用于池化数据库连接. /p>

I have a problem with a really slow connection between my Java code and a MySQL Database. I don't know where the bottle neck is.

My program is more or less a chatbot. The user types something in, my program splits the sentence into words and sends it word per word to the database. If it finds something there, the user gets an output. The database is on an external Server, but I also tried to connect to a pc next to me. Both is slow.

I tried the connection once at another place then where I normally work and there it was fast, most of the time.

My SQL Code:

SELECT info.INFORMATION FROM INFORMATION info, INFO_SCHLUESSEL sch
WHERE LCASE(sch.SCHLUESSELWORT) LIKE '" + input + "%' AND info.ID_INFO = sch.ID_INFO
Order BY info.PRIORITAET DESC LIMIT 1;

(just remembered, if it helps to understand the sql code:
schluessel = key
Schluesselwort = key word
prioritaet = priority)

My Java Database Code is more or less standard stuff:

String driver = "com.mysql.jdbc.Driver";
String dbase = "jdbc:mysql://bla";
String dbuser = "bla";
String dbpw = "bla";

Class.forName(driver);
Connection con = DriverManager.getConnection(dbase, dbuser, dbpw);
Statement stmt = con.createStatement();

ResultSet rs = stmt.executeQuery(query);
while (rs.next())
{
ergebnis = rs.getString("info.INFORMATION");
}

rs.close();
stmt.close();
con.close();

edit:

I have tried this DBCP for a while now, and I can't seem to get it to work. It seems to be as slow as the old connection. This is the example provided by the website that I use:

GenericObjectPool connectionPool = new GenericObjectPool(null);
ConnectionFactory connectionFactory = new DriverManagerConnectionFactory("jdbc:mysql://bla", "bla", "bla");
PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(connectionFactory,connectionPool,null,null,false,true);
PoolingDriver driver = new PoolingDriver();
driver.registerPool("example",connectionPool);
Connection conn = DriverManager.getConnection("jdbc:apache:commons:dbcp:example");

解决方案

I suspect that it's the connection setup that is causing the problem. It would be worth timing how long this takes:

Connection con = DriverManager.getConnection(dbase, dbuser, dbpw);

and if so, check out Apache Commons DBCP, which allows you to pool database connections.

这篇关于JDBC与MySQL真的很慢，不知道为什么的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！