分页查询的sql语句_MySQL流式查询及游标查询

1. 业务场景

现在业务系统需要从MySQL数据库中读取500w行数据进行处理批量

迁移数据、导出数据、处理数据 2.列出以下三种处理方法

常规查询：一次向JVM内存读取500w的数据，或分页读取

流式查询：一次读取一个，加载到 JVM 内存中进行业务处理

游标查询：与流式处理一样，您可以通过参数控制一次可以读取多少条数据

2.1 一般查询

默认情况下，整套搜索结果将其存储在内存中。在大多数情况下，这是最有效的操作方式，并且更容易实施。

假设单个表包含 500W 的数据，没有人会一次将其加载到内存中，并且通常使用分页。

在这里，测试演示仅用于监视 JVM，因此没有分页，并且数据一次性加载到内存中

@Test
public void generalQuery() throws Exception {
    // 1核2G：查询一百条记录：47ms
    // 1核2G：查询一千条记录：2050 ms
    // 1核2G：查询一万条记录：26589 ms
    // 1核2G：查询五万条记录：135966 ms
    String sql = "select * from wh_b_inventory limit 10000";
    ps = conn.prepareStatement(sql);
    ResultSet rs = ps.executeQuery(sql);
    int count = 0;
    while (rs.next()) {
        count++;
    }
    System.out.println(count);
}

JVM 监控

我们会让内存变小——

在整个查询过程中，堆内存占用量逐渐增加，并最终导致 OOM：

java.lang.:d ed

1. GC频繁触发

2.存在OOM隐患

2.2 流式查询

流式查询的一个警告是，它们必须先读取（或关闭）结果集中的所有行，然后才能向联接发出任何其他查询，否则将引发异常，并且其查询将独占联接。

从测试结果来看，流式查询并没有提高查询的速度

@Test
public void streamQuery() throws Exception {
    // 1核2G：查询一百条记录：138ms
    // 1核2G：查询一千条记录：2304 ms
    // 1核2G：查询一万条记录：26536 ms
    // 1核2G：查询五万条记录：135931 ms
    String sql = "select * from wh_b_inventory limit 50000";
    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);
}

JVM 监控

让我们让堆内存变小——

我们发现，即使堆内存只有 70m，也没有发生 OOM

2.3 光标查询

注意：

1. 您需要在数据库连接信息中拼接参数 = true

2.其次，设置每次读取的数据次数，例如一次读取1000个

根据测试结果，光标查询在一定程度上缩短了查询速度

@Test
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");
    start = System.currentTimeMillis();
     // 1核2G：查询一百条记录：52 ms
     // 1核2G：查询一千条记录：1095 ms
    // 1核2G：查询一万条记录：17432 ms
    // 1核2G：查询五万条记录：90244 ms
    String sql = "select * from wh_b_inventory limit 50000";
    ((JDBC4Connection) conn).setUseCursorFetch(true);
    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);
}

JVM 监控

让我们让堆内存变小——

我们发现，在单线程查询的情况下，游标查询（如流式查询）可以很好地规避OOM，游标查询可以优化查询速度。

第三

.next（） 的逻辑是实现一个类，每次从中获取下一行数据。 是一个接口，实现图如下

3,1

默认情况下，使用实例，在生成对象时将所有记录读入内存，然后通过 next（） 逐个从内存中读取。

3,2

流式传输时，您使用的是一个对象 next（），该对象在每次调用时启动对单行数据的 IO 读取

3,3

调用经过批处理，然后在内部缓存，过程如下：

首先，它会检查其内部缓冲区中是否有任何尚未返回的数据，如果有，则返回下一行，如果已读取，则触发对MySQL的新请求以读取数量结果，并将返回的结果缓冲到内部缓冲区，然后返回第一行数据

总结一下：

默认情况下，所有数据都读入客户端内存，即我们的 JVM;

每个 IO 调用读取一条数据;

读取该行一次，然后在消耗完成后发起请求调用。

四、JDBC通信原理

JDBC 和 MySQL 服务器之间的交互是通过 完成的，对应网络编程，MySQL 可以视为一个 ，所以一个完整的请求链接应该是：

JDBC客户端 ->客户端 -> MySQL -> 检索数据返回 -> MySQL内核 ->网络 ->客户端 ->JDBC客户端

4.1 一般咨询

普通查询在处理之前将查询到的所有数据加载到 JVM。

如果查询数据量过大，会继续经历 GC，然后会溢出

4.2 流式查询

当服务器准备好从第一条数据返回时，它会把数据放到缓冲区里，数据会通过TCP链路放到客户端机器的内核缓冲区里，JDBC的.read（）方法会被唤醒来读取数据，唯一的区别是当读取被打开时， 它每次只会从内核中读取大量数据，并且只返回一行数据，如果无法组合一行数据，它将读取另一行数据。

4.3 光标查询

打开光标时，服务器返回数据时，会根据大小返回数据，客户端收到数据时，每次都会读取缓冲区中的所有数据，如果数据有1亿个数据，则设置为1000，进行10万次往返通信;

由于MySQL不知道客户端何时会消耗数据，并且对应的表可能有DML写入操作，因此MySQL需要建立一个临时空间来存储需要获取的数据。

因此，当您启用读取大型表时，您将在MySQL上看到以下几种现象：

1. IOPS 飙升

2.磁盘空间暴涨

3. 客户端JDBC启动SQL后，等待SQL响应数据的时间较长，这意味着服务器正在准备数据

4、数据准备完成，数据传输开始后，网络响应开始飙升，IOPS由“读写”变为“读”。

IOPS（输入/曾经）：磁盘每秒的读取和写入次数

5.CPU和内存将按一定比例上升

5. 并发场景

并发调用：1 秒内 10 个线程并发调用 Jmete

下表报告了流式查询内存性能

并发调用对于内存使用量也可以，并且没有附加增加

下表描述了游标查询的内存性能报告

6. 总结

1、无论是游标查询还是流式查询，都可以避免单线程下的OOM;

2、在查询速度方面，上游标准查询比流式查询快，流式查询相比普通查询不能缩短查询时间;

3、在并发场景式查询堆内存的趋势更加稳定，没有叠加增加。

参考文章：

MySQL JDBC通信原理分析专栏-CSDN博客