MySQL中的流式查询及游标查询方式（总结分享）

MySQL中的流式查询及游标查询方式

随着业务系统中数据量的增加，MySQL数据库查询的效率成为了重要的考虑点。本文将介绍MySQL中的流式查询和游标查询两种方式，并分析它们的优点和应用场景。

1. 业务场景

假设业务系统需要从MySQL数据库里读取500万条数据进行处理、迁移、导出或批量处理。常规的做法是一次性读取所有数据到JVM内存中，或者采用分页的方式进行读取。但是当数据量较大时，这种做法会导致内存占用过高，甚至造成内存溢出的风险。因此，需要考虑一些更加高效的查询方式。

2. 查询方式

2.1 常规查询

默认情况下，MySQL会将完整的结果集存储在内存中，而对于大数据量的查询，可以采用分页的方式进行查询。但是在一些情况下，这种做法仍然会导致内存溢出的风险。以下是一个常规查询的示例：

```
public void generalQuery() throws Exception{
String sql = "select * from wh_b_inventory limit 10000";
PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery(sql);
int count = 0;
while (rs.next()){
count ++;
}
System.out.println(count);
}
```

在这个示例中，查询了wh_b_inventory表中的10000条记录。但是如果要查询更多的数据，就需要考虑将其分成多次查询，或采用其他查询方式。

2.2 流式查询

流式查询是为了解决常规查询中内存占用过高的问题而产生的。流式查询每次只会读取一条数据，并将其加载到JVM内存中进行处理。因此，流式查询不会在JVM中存储所有的数据，有效地解决了内存消耗问题。以下是一个流式查询的示例：

```
public void streamQuery() throws Exception{
String sql = "select * from wh_b_inventory limit 50000";
Statement statement = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
statement.setFetchSize(Integer.MIN_VALUE);
ResultSet rs = statement.executeQuery(sql);
int count = 0;
while (rs.next()){
count ++;
}
System.out.println(count);
}
```

在这个示例中，查询了wh_b_inventory表中的50000条记录。通过对流式查询的测试，我们发现它不能提升查询的速度，但可以有效地解决内存占用过高的问题。

2.3 游标查询

游标查询是在流式查询的基础上进行的一种优化方式。游标查询的原理是：通过fetchSize参数，每次读取一定量的数据，并将其进行缓存，在客户端消费完成之后再请求下一次的缓存数据。以下是一个游标查询的示例：

```
public void cursorQuery() throws Exception{
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://101.34.50.82:3306/mysql-demo?useCursorFetch=true", "root", "123456");
String sql = "select * from wh_b_inventory limit 50000";
((JDBC4Connection)conn).setUseCursorFetch(true);
Statement statement = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
statement.setFetchSize(1000);
ResultSet rs = statement.executeQuery(sql);
int count = 0;
while (rs.next()){
count ++;
}
System.out.println(count);
}
```

在这个示例中，查询了wh_b_inventory表中的50000条记录，并且开启了游标查询。通过对游标查询的测试，我们发现它在一定程度上缩短了查询的时间，且能够规避OOM的风险。

3. RowDataResultSet.next的逻辑

在MySQL中，ResultSet的实现类为ResultSetImpl，每次从RowData获取下一行的数据。RowData有三种不同的实现方式：

3.1 RowDataStatic

默认情况下，ResultSet会使用RowDataStatic实例。在生成RowDataStatic对象时，ResultSet中所有的记录都会被一次性读取到内存中。在JVM内存中存储所有记录的方式，已经不适合大数据量查询的场景。

3.2 RowDataDynamic

当采用流式查询时，ResultSet会使用RowDataDynamic对象，这个对象通过IO调用每次获取一条数据，但是它只读取一个package大小的数据。相对于RowDataStatic，它不需要将全部记录存储到JVM内存中。

3.3 RowDataCursor

RowDataCursor的调用为批处理，它会将fetchSize数量的结果缓存在内存中，然后返回第一行数据。当所有数据读取完成之后，会发起新的请求获取指定的数据量。由于MySQL无法得知客户端何时消费完数据，因此需要建立一个临时空间来存放需要拿走的数据。

4. JDBC通信原理

在JDBC与MySQL服务端的交互中，数据是通过Socket完成的。MySQL可以看作是一个SocketServer，整个请求链路应该是：JDBC客户端 -> 客户端Socket -> MySQL -> 检索数据返回 -> MySQL内核SocketBuffer -> 网络 -> 客户端SocketBuffer -> JDBC客户端。

MySQL会将返回的数据打包为多个package，以减少网络传输的次数。

5. 并发场景

在并发场景下，我们需要对查询方式进行一些调整。以下是对流式查询和游标查询的并发测试结果：

5.1 流式查询

当进行10个线程的并发调用时，我们发现流式查询的内存占用比较稳定，没有出现叠加式的增长。

5.2 游标查询

当进行10个线程的并发调用时，我们发现游标查询的内存占用相对于流式查询有所上升，但仍然控制在一个安全的范围内。

6. 总结

通过对MySQL中流式查询和游标查询的介绍及测试，我们得出以下几个结论：

1. 流式查询和游标查询在单线程下能够规避OOM，游标查询相比于流式查询更快；
2. 在查询速度上，流式查询和普通查询相比并不能缩短查询时间；
3. 在并发场景下，流式查询的内存占用比较稳定，而游标查询占用的内存有所增加。

为提高MySQL查询的效率，我们需要根据不同的场景选择不同的查询方式。

扫码添加UTHEME微信为好友

· 分享WordPress相关技术文章，主题上新与优惠动态早知道。
· 微信端最大WordPress社群，限时免费入群。