java JDBC 和 MySQL 真的很慢，不知道为什么

JDBC with MySQL really slow, don't know why

提问by Brian Agnew

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.

我的 Java 代码和 MySQL 数据库之间的连接速度非常慢。我不知道瓶颈在哪里。

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:

我的 SQL 代码：

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;

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)

（刚刚记住了，如果有助于理解sql代码：
schluessel = key
Schluesselwort = 关键字priitaet
= priority）

My Java Database Code is more or less standard stuff:

我的 Java 数据库代码或多或少是标准的东西：

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();

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

Class.forName（驱动程序）；
Connection con = DriverManager.getConnection(dbase, dbuser, dbpw);
语句 stmt = con.createStatement();

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

rs.close();
stmt.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:

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

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");

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 驱动程序 = new PoolingDriver();
driver.registerPool("example",connectionPool);
Connection conn = DriverManager.getConnection("jdbc:apache:commons:dbcp:example");

回答by Brian Agnew

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.

如果是这样，请查看Apache Commons DBCP，它允许您汇集数据库连接。

回答by user121803

Well I think this warrants a discussion on the design.There are a few things which you can do in order to improve the performance. Since you are not persisting anything here, its better to preload all the data in memory in some custom java object, a map, list or whatever and then do an in-memory lookup for the word and get the results. Another approach could be to use a batch statement so that you dont go ahead and create and release connections for each word. Oh and if using batch statements make sure you set the batch size to an appropriate number, preferably a prime number

好吧，我认为这值得对设计进行讨论。为了提高性能，您可以做一些事情。由于您没有在此处保留任何内容，因此最好将内存中的所有数据预加载到某个自定义 java 对象、地图、列表或其他任何内容中，然后在内存中查找该单词并获得结果。另一种方法可能是使用批处理语句，这样您就不会继续为每个单词创建和释放连接。哦，如果使用批处理语句，请确保将批处理大小设置为适当的数字，最好是质数