深⼊了解MySQL的流式查询机制
引⾔
为什么要⽤流式查询？
a) 如果有⼀个很⼤的查询结果需要遍历处理，⼜不想⼀次性将结果集装⼊客户端内存，就可以考虑使⽤流式查询；
b)分库分表场景下，单个表的查询结果集虽然不⼤，但如果某个查询跨了多个库多个表，⼜要做结果集的合并、排序等动作，依然有可能撑爆内存；详细研究了sharding-sphere的代码不难发现，除了group by与order by字段不⼀样之外，其他的场景都⾮常适合使⽤流式查询，可以最⼤限度的降低对客户端内存的消耗。
1、oracle等商业数据库的fetchsize
使⽤过oracle数据库的程序猿都知道，oracle驱动默认设置了fetchsize为10，那什么是fetchsize？
先来简单解释⼀下，当我们执⾏⼀个SQL查询语句的时候，需要在客户端和服务器端都打开⼀个游标，并且分别申请⼀块内存空间，作为存放查询的数据的⼀个缓冲区。这块内存区，存放多少条数据就由fetc
hsize来决定，同时每次⽹络包会传送fetchsize条记录到客户端。应该很容易理解，如果fetchsize设置为20，当我们从服务器端查询数据往客户端传送时，每次可以传送20条数据，但是两端分别需要20条数据的内存空闲来保存这些数据。fetchsize决定了每批次可以传输的记录条数，但同时，也决定了内存的⼤⼩。这块内存，在oracle服务器端是动态分配的。⽽在客户端，PS对象会存在⼀个缓冲中（LRU链表），也就是说，这块内存是事先配好的，应⽤端内存的分配在
conn.prepareStatement(sql)或都conn.CreateStatement(sql)的时候完成。
2、流式查询与MySQL fetchsize的关系
既然fetchsize这么好⽤，那MySQL直接设⼀个值，不就也可以⽤到缓冲区，不必每次都将全量结果集装⼊内存。但是，⾮常遗
憾，MySQL的JDBC驱动本质上并不⽀持设置fetchsize，不管设置多⼤的fetchsize，JDBC驱动依然会将select的全部结果都读取到客户端后再处理, 这样的话当select返回的结果集⾮常⼤时将会撑爆Client端的内存。
但也不是完全没办法，PreparedStatement/Statement的setFetchSize⽅法设置为Integer.MIN_VALUE或者使⽤⽅法
3、MySQL流式查询的坑
带着这个疑问，不妨做⼀次⼩⼩的测试：
使⽤同⼀个MySQL数据库连接，分别执⾏多次查询，在得到多个ResultSet之后，再进⾏结果集的遍历。
public class LoopConnectionTest {
private static Connection conn = getConn();
public static void main(String[] args) {
List<ResultSet> actualResultSets = new ArrayList<>();
for (int i = 0; i < 3; i++) {
actualResultSets.add(getAllCategory(conn));
}
boolean flag = true;
int i = 0;
while (true) {
try {
int index = i++;mysql下载不了什么原因
flag = (index%3), index%3);
} catch (SQLException e) {
e.printStackTrace();
}
if (!flag) {
break;
}
}
}
private static ResultSet getAllCategory(Connection conn) {
String sql = "select * from tb_category";
PreparedStatement pstmt = null;
ResultSet resultSet = null;
try {
pstmt = (PreparedStatement)conn.prepareStatement(sql);
// pstmt.setFetchSize(Integer.MIN_VALUE);
resultSet = uteQuery();
} catch (SQLException e) {
e.printStackTrace();
}
// finally {
// if (null!=pstmt) {
// try {
// pstmt.close();//注释掉close⽅法是因为，⼀旦pstmt关闭，resultSet也会随之关闭// } catch (SQLException e) {
// e.printStackTrace();
// }
// }
// }
return resultSet;
}
private static boolean displayResultSet(ResultSet rs, int index) throws SQLException {
int col = rs.getMetaData().getColumnCount();
System.out.println("index:" + index + "============================");
boolean flag = rs.next();
if (flag) {
System.out.String("name"));
}
return flag;
}
public static Connection getConn() {
String driver = "sql.jdbc.Driver";
String url = "jdbc:mysql://192.168.178.140:3306/jasper";
String username = "root";
String password = "123456";
Connection conn = null;
try {
Class.forName(driver); //classLoader，加载对应驱动
conn = (Connection) Connection(url, username, password);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
}
第⼀次试验，我们将
pstmt.setFetchSize(Integer.MIN_VALUE);
这最关键的⼀⾏注释掉，关闭流式查询，对多个结果集的遍历可以得到正确的结果。
第⼆次试验，开启流式查询，果然问题来了。
index:0============================
⼤家电
java.sql.SQLException: Streaming result sql.jdbc.RowDataDynamic@617f84e0 is still active. No statements may be issued when any streaming result sql.ateSQLException(SQLError.java:935)
sql.ateSQLException(SQLError.java:932)
sql.jdbc.MysqlIO.checkForOutstandingStreamingData(MysqlIO.java:3338)
sql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2504)
sql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2758)
sql.SQL(ConnectionImpl.java:2820)
sql.uteSimpleNonQuery(StatementImpl.java:1657)
sql.uteQuery(PreparedStatement.java:2177)
AllCategory(LoopConnectionTest.java:44)
st.LoopConnectionTest.main(LoopConnectionTest.java:16)
java.sql.SQLException: Streaming result sql.jdbc.RowDataDynamic@617f84e0 is still active. No statements may be issued when any streaming result sql.ateSQLException(SQLError.java:935)
sql.ateSQLException(SQLError.java:932)
sql.jdbc.MysqlIO.checkForOutstandingStreamingData(MysqlIO.java:3338)
sql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2504)
sql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2758)
sql.SQL(ConnectionImpl.java:2820)
sql.uteSimpleNonQuery(StatementImpl.java:1657)
sql.uteQuery(PreparedStatement.java:2177)
AllCategory(LoopConnectionTest.java:44)
st.LoopConnectionTest.main(LoopConnectionTest.java:16)
Exception in thread "main" java.lang.NullPointerException
st.LoopConnectionTest.displayResultSet(LoopConnectionTest.java:61)
st.LoopConnectionTest.main(LoopConnectionTest.java:26)
查了下异常发⽣的原因发现，其实mysql本⾝并没有FetchSize⽅法, 它是通过使⽤CS阻塞⽅式的⽹络流控制实现服务端不会⼀下发送⼤量
数据到客户端撑爆客户端内存，这种实现⽅式⽐起商业数据库Oracle使⽤客户端、服务器端缓冲块暂存查询结果数据来说，简直是弱爆了！
这样带来的问题：如果使⽤了流式查询，⼀个MySQL数据库连接同⼀时间只能为⼀个ResultSet对象服务，并且如果该ResultSet对象没有
关闭，势必会影响其他查询对数据库连接的使⽤！此为⼤坑，难怪sharding-sphere费劲⼼思要提供两种数据库连接模式，如果应⽤对数据
库连接的消耗要求严苛，那么流式查询就不再适合。
贴下MySQL Connector/J 5.1 Developer Guide中原⽂：
There are some caveats with this approach. You must read all of the rows in the result set (or close it) before you can issue
any other queries on the connection, or an exception will be thrown. 也就是说当通过流式查询获取⼀个ResultSet后，在你通过
next迭代出所有元素之前或者调⽤close关闭它之前，你不能使⽤同⼀个数据库连接去发起另外⼀个查询，否者抛出异常（第⼀次调⽤的正
常，第⼆次的抛出异常）。
对⽐测试了Oracle和DB2，设置fetchSize之后，数据库连接依然可以被其他查询共⽤，并没有MySQL的这个坑。再⼀次应证了MySQL相
⽐于⼤型商业数据库来说，还是显得太弱了，这种游标遍历的功能理应提供，但是它偏偏没有。
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论