MySQL查询结果非常慢

I am executing a simple query on a small table

我正在一个小表上执行一个简单的查询

SELECT * FROM SYSTEM

System table only has three columns(Id, Name, Progress) and 1300 rows.

系统表只有三列(Id、名称、进度)和1300行。

My code for getting the data is:

我获得数据的代码是:

    try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            conn = (Connection) DriverManager.getConnection(
                    "jdbc:mysql://192.168.0.107:3306/my_database",
                    username.getText(), password.getText());
            String query = "select * from system";
            stmt = (Statement) conn.createStatement();
            rs = (ResultSet) stmt.executeQuery(query);
            while (rs.next()) {
                tableModel.addRow(new Object[] { rs.getInt("Number"),
                        rs.getString("Name"), rs.getFloat("Progress") });
            }
        } catch (Exception e) {
            // some other code
        }`

This code takes around 15 seconds to display the date in my JTable, while if I execute the query in phpmyadmin it takes less than one second.

此代码在我的JTable中显示日期大约需要15秒，而如果我在phpmyadmin中执行查询，则花费不到一秒。

2 个解决方案

#1

I would use a PreparedStatment and I would increase the default ResultSet fetchSize, and I would limit the query to the three columns (note id != Number) -

我将使用一个PreparedStatment并增加默认的ResultSet fetchSize，并将查询限制为三列(注意id != Number) -

String query = "select Number, Name, Progress from system";
stmt = conn.prepareStatement(query);
rs = stmt.executeQuery();
rs.setFetchSize(250);

Finally, establishing a Connection like that is probably the wrong way to go for performance. You should look for a Connection pool like BoneCP or c3p0 or HikariCP.

最后，建立这样的联系可能是追求性能的错误方式。您应该寻找一个连接池，如BoneCP、c3p0或HikariCP。

#2

The issue here was likely the

这里的问题很可能是

while (rs.next()) { tableModel.addRow(...)

而(rs.next()){ tableModel.addRow(…)

Each call to addRow() means a "Notification of the row being added will be generated."

每次对addRow()的调用都意味着“将生成要添加的行的通知”。

This notification of the Listener(s) means quite some overhead. Likely, each call indirectly involves a call to invalidate the GUI's state, and more, because the table will have to be redrawn after it changed. Doing that 1300x in ~15s means 100 of those calls per second, which is quite reasonable. The OP should probably look for a way to first gather all data and then update the table model only once with all the data. This may mean having to implement his own variation of DefaultTableModel or AbstractTableModel.

侦听器的这个通知意味着相当大的开销。很可能，每个调用都间接地包含一个调用，以使GUI的状态无效，甚至更多，因为表在更改之后必须重新绘制。在~15s内进行1300x意味着每秒调用100次，这是很合理的。OP可能应该寻找一种方法，首先收集所有数据，然后使用所有数据更新表模型一次。这可能意味着必须实现他自己对DefaultTableModel或AbstractTableModel的变体。

#1