流式查询：一道解谜的乐趣，带你走向实时数据和智能决策的世界

失乐园
328 阅读7分钟

最近对流式查询有点兴趣，今天趁着有时间来研究下，以前也听过知道学习过流式查询，但是随着时间的推移，忘的都差不多了，今天就借这个机会好好复习下。

流式查询，我学习有一个习惯，那就是见名知其意，意思就是顾名思义，通过一个知识点的名词可以从其字面意思上来通晓它的含义以及作用，流式查询，其实换种说法会更好的理解，那就是游标查询，因为它的原理就是利用了游标的特性来实现的，所以，这也就能解释为什么在springboot中定义了一个接口Cursor，我想这个名字的由来也是源于这里。解释到这里，差不多就懂了流式查询的真正作用了，到时候别人说起流式查询的时候，想到游标自然而然就知晓其意思了。

大概意思既然已经知道了，那接下来就该展现一下自己的动手能力了，正所谓纸上得来终觉浅，绝知此事要躬行。下面就来实现一下流式查询，看看到底是怎么个事。

流式查询一般的作用都是用来查询大数据量的时候才会去用到它，那今天，我准备了一张表，100w的数据量，然后分别从普通查询、流式查询以及自定义游标查询导出excel的三种方式对比的形式来看看效果

前期准备工作，准备一张表，然后插入100w的数据量，表结构随便都可以，这里贴出我试验的表结构以供参考

CREATE TABLE stream_test (
id BIGINT auto_increment NOT NULL,
name varchar(50) NOT NULL,
sex CHAR NOT NULL,
create_date DATETIME NOT NULL,
create_user varchar(50) NOT NULL,
CONSTRAINT NewTable_pk PRIMARY KEY (id)
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_0900_ai_ci;

表和数据准备好了，首先是第一种，也就是普通查询，一次性查出100w的数据量到内存，然后直接写入excel文件，这里我用的easyexcel写入，pom坐标如下

<!--easyExcel-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.1.1</version>
        </dependency>

然后使用代码工具生成下代码，在service层添加一个export方法，这里我使用了ExcelUtil方法导出到HttpServletResponse

public static void export(HttpServletResponse response,
                          List<StreamTest> data,
                          Class cl) {
    try {
        //HttpServletResponse消息头参数设置
        response.setCharacterEncoding("UTF-8");
        response.setHeader("Content-Transfer-Encoding", "binary");
        response.setHeader("Cache-Control", "must-revalidate, post-check=0, pre-check=0");
        response.setHeader("Pragma", "public");
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8");
        String fileName = "导出列表" + ".xlsx";
        fileName = new String(fileName.getBytes(), "ISO-8859-1");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
        EasyExcel.write(response.getOutputStream(), cl)
                .autoCloseStream(Boolean.FALSE)
                .sheet("导出列表")
                .doWrite(data);
    } catch (Exception e) {
        e.printStackTrace();
    }
}

@Override
public void export(HttpServletResponse response) throws UnsupportedEncodingException {
    long startDate = System.currentTimeMillis();
    List<StreamTest> data = streamTestDao.getList();
    long queryDate = System.currentTimeMillis() - startDate;
    System.out.println("查询整100w数据时间为：" + queryDate);
    long startExportDate = System.currentTimeMillis();
    String fileName = "导出列表" + ".xlsx";
    fileName = new String(fileName.getBytes(), "UTF-8");
    WriteSheet sheet = EasyExcel.writerSheet(1, "导出sheet").build();
    ExcelWriter excelWriter = EasyExcel.write(fileName, StreamTest.class).build();
    excelWriter.write(data,sheet);
    excelWriter.close();
    long endExportDate = System.currentTimeMillis() - startExportDate;
    System.out.println("导出100w数据时间为：" + endExportDate);
}

在service层中，这里记录了下查询数据的时间以及导出到excel的时间，用来比对后续两种方案，做完这些，添加controller方法开始测试

@PostMapping("/export")
public void export(HttpServletResponse response) throws UnsupportedEncodingException {
     this.streamTestService.export(response);
}

启动项目，使用postman开始调用，开始调用之前，先记录下内存使用情况，因为，流式查询重要的作用之一就是内存使用率的问题

image.png 初始内存为10.9，然后开始执行postman，看看效果

image.png

image.png

首先是执行时间，然后是内存使用情况，内存差不多用了2个多G，最后看导出的文件是否可以正常打开

image.png

没有任何问题，正常打开，普通查询导出正常，接下来是第二种，流式查询导出，要使用流式查询，首先第一点要修改数据库连接字符串，在连接字符串中加入useCursorFetch=true，然后在mapper层定义接口方法的时候，只需要定义为Cursor<T>即可，最后，service层调用实现

@Transactional
@Override
public void streamExport() throws Exception {
    long startDate = System.currentTimeMillis();
    try {
        Cursor<StreamTest> data = streamTestDao.streamList();
        long queryDate = System.currentTimeMillis() - startDate;
        long startExportDate = System.currentTimeMillis();
        System.out.println("流式查询数据时间为：" + queryDate);
        String fileName = "streamExport导出列表" + ".xlsx";
        fileName = new String(fileName.getBytes(), "UTF-8");
        WriteSheet sheet = EasyExcel.writerSheet(1, "导出sheet").build();
        ExcelWriter excelWriter = EasyExcel.write(fileName, StreamTest.class).build();
        for (StreamTest st : data) {
            excelWriter.write(Arrays.asList(st), sheet);
        }
        excelWriter.close();
        long endExportDate = System.currentTimeMillis() - startExportDate;
        System.out.println("流式查询导出100w数据时间为：" + endExportDate);
    } catch (Exception e) {
        throw new Exception();
    }
}

这里要解释下，看service这里加了@Transactional注解，这个注解不仅仅是为了事务回滚，在这里更重要的作用是保持数据库的连接，因为流式查询当执行完Cursor<StreamTest> data = streamTestDao.streamList();这一行代码后，如果不加@Transactional注解，那么就会立即关闭数据库连接，导致后续我们循环获取数据时报连接错误，所以，这里也就引申出流式查询的一个不好的点，那就是如果数据量太大，它会长时间占用数据库连接，会有一定的风险存在，好了，做完这些，然后就是在controller层添加方法来调用看看效果如何了

@PostMapping("/streamExport")
public void streamExport() throws Exception {
    this.streamTestService.streamExport();
}

同样是启动postman，然后初始内存之前已经有了，然后执行看看流式查询的时间和内存使用情况

image.png

image.png

可以看到在时间上还是有很大区别的，那是因为在第一步查数据的时候流式查询根本没有持有100w的数据，它真正取数的时候是循环遍历写入文件的时候，所以，区别就在这里，但是整体的用时差别不是很大，内存的话区别也差不多有一个G左右的区别，最后，打开导出的文件看看效果

image.png

一样可以打开，完全可以实现，最后一种，就是自己实现的游标查询了，这里使用的是索引的特性，因为主键索引的特性，可以每次查出一定数量的数据，然后使用id>count 和limit count的方式达到游标方式的效果，首先id是有序的，然后limit是取前多少条数据，看到这里可能会有人问为什么不直接用limint呢，这里就不得不说明下limit的原理了，举个例子吧，如果你想查第5000条到10000条数据出来，那么它首先会把1到10000条数据查出来，然后再去筛选你需要的那第5000到10000的数据返回给你，所以，对于大数据量来说越往后面查limit性能会变得很差，所以这里使用了主键有序的特点，提前告知数据库我要的数据是从哪里开始，你不需要把所有数据都捞出来，然后再去筛出我想要的那些数据，而是直接给了一个起点，然后从起点开始拿我们需要的那么多条数就ok了，所以，这种方式和第一种的区别就是sql，然后就是service层的实现了

@Override
public void cursorExport() throws Exception {
    long startDate = System.currentTimeMillis();
    Long id = 0L;
    String fileName = "cursorExport导出列表" + ".xlsx";
    fileName = new String(fileName.getBytes(), "UTF-8");
    WriteSheet sheet = EasyExcel.writerSheet(1, "导出sheet").build();
    ExcelWriter excelWriter = EasyExcel.write(fileName, StreamTest.class).build();
    while (true) {
        List<StreamTest> data = streamTestDao.cursorList(id);
        if (data.size() == 0) {
            break;
        }
        excelWriter.write(data, sheet);
        id += 5000L;
    }
    excelWriter.close();
    long endExportDate = System.currentTimeMillis() - startDate;
    System.out.println("自定义游标查询导出100w数据时间为：" + endExportDate);
}

这里定义的游标跨度为5000条，也就是每次捞出5000条数据，然后写入文件，最后，添加controller层的方法进行调用

@PostMapping("/cursorExport")
public void cursorExport() throws Exception {
    this.streamTestService.cursorExport();
}

看看最后一种方式的效果吧

image.png

image.png

image.png

这种方式用时最长，使用内存和流式查询相差不大，同样是可以实现导出到文件。

三种方式都实践完了，对于流式查询的基本使用和特性也了解的差不多了，在软件工程中，没有所谓的银弹，只是对于业务来说更适用哪种方案才最合适

最后，还是要送上一位名人曾说的一句话：手上没有剑和有剑不用是两回事！

avatar
文章
阅读
粉丝