从很大的表中获取数据

Question

我在MySQL数据库中有一个非常大的表，在table中有2亿条记录Users。我使用JDBC进行查询：public List<Pair<Long, String>> getUsersAll() throws SQLException {        Connection cnn = null;        CallableStatement cs = null;        ResultSet rs = null;        final List<Pair<Long, String>> res = new ArrayList<>();        try {            cnn = dataSource.getConnection();            cs = cnn.prepareCall("select UserPropertyKindId, login from TEST.users;");            rs = cs.executeQuery();            while (rs.next()) {                res.add(new ImmutablePair<>(rs.getLong(1), rs.getString(2)));            }            return res;        } catch (SQLException ex) {            throw ex;        } finally {            DbUtils.closeQuietly(cnn, cs, rs);        }    }接下来，我处理结果：List<Pair<Long, String>> users= dao.getUsersAll();            if (CollectionUtils.isNotEmpty(users)) {                for (List<Pair<Long, String>> partition : Lists.partition(users, 2000)) {                    InconsistsUsers.InconsistsUsersCallable callable = new InconsistsUsers.InconsistsUsersCallable (new ArrayList<>(partition));                    processExecutor.submit(callable);                }            }但是由于该表非常大，并且全部都已卸载到内存中，因此我的应用程序崩溃并出现错误：com.mysql.jdbc.exceptions.jdbc4.CommunicationsException：通信链接失败从服务器成功接收到的最后一个数据包是105619毫秒之前。如何才能部分接收数据并按优先级顺序处理它们，以免一次将所有结果上传到内存中？创建游标并将数据上传到非阻塞队列并在数据到达时对其进行处理是可能的。如何才能做到这一点？

Answer 1

您应该在几个级别上处理此问题：

JDBC驱动程序访存大小

JDBC有一个Statement.setFetchSize()方法，该方法指示从JDBC获取行之前，JDBC驱动程序将预取多少行。请注意，MySQL JDBC驱动程序并未真正正确地实现此功能，但您可以设置setFetchSize(Integer.MIN_VALUE)为阻止它一次性获取所有行。另请参阅此答案。

请注意，您还可以使用以下方法激活连接上的功能 useCursorFetch

你自己的逻辑

您不应将整个用户列表存储在内存中。您现在正在做的是从JDBC收集所有行，然后稍后使用来对列表进行分区Lists.partition(users, 2000)。这是朝着正确的方向发展，但是您做的还不是很正确。相反，请执行以下操作：

try (ResultSet rs = cs.executeQuery()) {

    while (rs.next()) {

        res.add(new ImmutablePair<>(rs.getLong(1), rs.getString(2)));

    }

    // Process a batch of rows:

    if (res.size() >= 2000) {

        process(res);

        res.clear();

    }

}

// Process the remaining rows

process(res);

此处的重要信息是不加载内存中的所有行，然后分批处理它们，而是在从JDBC流传输行时直接处理它们。

Answer 2

而不是Java端的Lists.partition（users，2000），您应该将每个请求的mysql结果集限制为2000。

select UserPropertyKindId, login from TEST.users limit <offset>, 2000;

更新：正如Raymond Nijland在下面的评论中提到的，如果偏移量太大，则查询速度可能会大大降低。

一种解决方法可能是使用偏移量，而不是使用偏移量，引入where语句，例如where id> last_user_id。

由于@All_safe在下面进行了注释，因此不存在自动增量ID，因此另一个针对较大偏移量的解决方法是：仅在子查询中获取主键，然后再联接回主表。这将强制mysql不执行早期行查找，这是大偏移量限制的主要问题。

但是您的原始查询仅获取主键列，我认为早期行查找不适用。

Answer 3

我遇到了类似的情况。我正在从一个MySQL数据库中读取数据，并将其复制到MS SQL Server数据库中。不是2亿，每天只有400万。但是我在通信链接失败时也遇到了同样的错误消息。我可以通过设置PreparedStatement.setFetchSize（Integer.MIN_VALUE）;的fetchsize来解决它。因此，通信链接故障消失了。我知道，这不能解决您的列表问题。