MySQL 百万数据量通过分页查询vs游标查询实现数据导出效率对比

原创 已于 2024-07-14 01:59:10 修改 · 1.6k 阅读 · 16 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#mysql #数据库 #java #excel

于 2024-07-14 01:26:03 首次发布

先说结论：

查询百万数据并导出，游标查询效率 远大于 分页查询，游标查询总耗时5s，分页查询102s，加上实际导出时间，游标查询并导出耗时16s，分页查询导出耗时118s，下方第4点记录了使用时的一些异常

环境说明：

SpringBoot版本： 2.7.10
ORM框架：MyBatis
数据库：mysql 5.7
数据导出：EasyExcel

1. 创建表

CREATE TABLE `t_user_test`  (
    `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
    `user_name` varchar(30) NOT NULL COMMENT '姓名',
    `age` int(3) NULL DEFAULT NULL  COMMENT '年龄',
    `address` varchar(50) NULL DEFAULT NULL  COMMENT '地址',
    PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='用户测试表';

2.造数据，循环插入100w条数据

-- 创建存储过程
DELIMITER //

CREATE PROCEDURE insert_multiple_rows()
BEGIN
    DECLARE i INT DEFAULT 1;

		-- 循环插入100w条数据
    WHILE i <= 1000000 DO
        INSERT INTO t_user_test (user_name, age, address) VALUES (CONCAT('user_name_', i), FLOOR(RAND() * 100), CONCAT('address_', i));
        SET i = i + 1;
    END WHILE;

END //

DELIMITER ;

-- 调用存储过程
CALL insert_multiple_rows();

我本地执行完花了大概800多秒…这800多秒，先执行下面的操作吧！！！

3. 代码实现

3.1 数据库连接配置，必须在jdbc url上设置连接属性参数useCursorFetch=true

jdbc:mysql://localhost:3306/gooluke-admin?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useAffectedRows=true&useCursorFetch=true&serverTimezone=UTC

3.2 dao接口

/**
 * 分页查询
 */
List<TUserTest> selectListByP(@Param("startIndex") int startIndex, @Param("pageSize") int pageSize);

/**
 * 游标查询
 */
@Options(fetchSize = 300)
Cursor<TUserTest> selectListByCursor();

3.3 xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.gooluke.admin.integration.dao.UserTestDAO">

    <resultMap id="BaseResultMap" type="com.gooluke.admin.entity.TUserTest">
        <result column="id" jdbcType="INTEGER" property="id"/>
        <result column="user_name" jdbcType="VARCHAR" property="userName"/>
        <result column="age" jdbcType="VARCHAR" property="age"/>
        <result column="address" jdbcType="VARCHAR" property="address"/>
    </resultMap>

    <select id="selectListByP" resultMap="BaseResultMap">
        select * from t_user_test limit #{startIndex}, #{pageSize}
    </select>

    <select id="selectListByCursor" resultMap="BaseResultMap">
        select * from t_user_test
    </select>
</mapper>

3.4 实体类忽略

3.3 查询接口


package com.gooluke.admin.service.impl;

import com.gooluke.admin.entity.TUserTest;
import com.gooluke.admin.integration.dao.UserTestDAO;
import com.gooluke.admin.service.DataService;
import io.github.hthbryant.gooluke.framework.biz.common.dto.BaseResponseDTO;
import io.github.hthbryant.gooluke.framework.biz.common.enums.ErrorStatus;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.cursor.Cursor;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.io.IOException;
import java.util.Iterator;
import java.util.List;
import java.util.concurrent.atomic.AtomicInteger;

/**
 * @author gooluke
 */
@Service
@Slf4j
public class DataServiceImpl implements DataService {

    @Autowired
    private UserTestDAO userTestDAO;

    @Override
    public BaseResponseDTO exportByPage() {
        AtomicInteger count = new AtomicInteger(0);
        //这里因为知道数据就是要查100w条，所以简单实现下分页，没有查总条数，如果还要查总条数并返回，效率更差了
        int currentPage = 1;
        int pageSize = 1000;
        long startTime = System.currentTimeMillis();
        while (currentPage <= 1000) {
            int startIndex = (currentPage - 1) * pageSize;
            long start = System.currentTimeMillis();
            List<TUserTest> userInfos = userTestDAO.selectListByP(startIndex, pageSize);
            log.info("第{}页查询耗时为：{} ms", currentPage, (System.currentTimeMillis() - start));
            for (TUserTest userInfo : userInfos) {
                count.addAndGet(1);
            }
            //写excel
            currentPage++;
        }
        log.info("处理数据量为：{}", count);
        log.info("分页查询总处理耗时为：{} s", (System.currentTimeMillis() - startTime) / 1000);
        return new BaseResponseDTO();
    }

    @Override
	@Transactional
    public BaseResponseDTO exportByCursor() {
        AtomicInteger count = new AtomicInteger(0);
        long startTime = System.currentTimeMillis();
        Cursor<TUserTest> cursor = userTestDAO.selectListByCursor();
        try {
            Iterator<TUserTest> iterator = cursor.iterator();
            while (iterator.hasNext()) {
                TUserTest userInfo = iterator.next();
                count.addAndGet(1);
                //写excel
            }
        } catch (Exception e) {
            log.error("游标查询异常,", e);
            return new BaseResponseDTO(ErrorStatus.SYSTEM_ERROR);
        } finally {
            if (cursor != null) {
                try {
                    cursor.close();
                } catch (IOException e) {
                    log.error("游标关闭失败");
                }
            }
        }
        log.info("游标查询总处理数据量：{}", count);
        log.info("游标查询总处理耗时：{} ms", (System.currentTimeMillis() - startTime));
        return new BaseResponseDTO();
    }
}

调了接口之后发现光是查询效率就有20倍！！！
分页查询总耗时102s，第一页的时候查询耗时20ms左右，干到第999页的时候已经要202ms了
在这里插入图片描述
游标查询总耗时5s
在这里插入图片描述

3.4 数据导出，EasyExcel

如果没有使用过EasyExcel的可以参考EasyExcel官网：https://easyexcel.opensource.alibaba.com/

定义数据导出对象
package com.gooluke.admin.excel.exportdata;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

@Data
public class UserTestListExcelData {

    @ExcelProperty(value = "id", index = 0)
    private Integer id;
    @ExcelProperty(value = "姓名", index = 1)
    private String userName;
    @ExcelProperty(value = "年龄", index = 2)
    private Integer age;
    @ExcelProperty(value = "电话号码", index = 3)
    private String address;

}
代码调整：因为上述只做了查询，没有导出逻辑

我们定义了一个导出对象，所以我们把查询结果直接用这个对象接收，省去了转换带来的性能影响
dao层：

/**
 * 分页查询
 */
List<UserTestListExcelData> selectListByP(@Param("startIndex") int startIndex, @Param("pageSize") int pageSize);

/**
 * 游标查询
 */
@Options(fetchSize = 300)
Cursor<UserTestListExcelData> selectListByCursor();

xml

<select id="selectListByP" resultType="com.gooluke.admin.excel.exportdata.UserTestListExcelData">
     select
     id,
     user_name as userName,
     age,
     address
     from t_user_test limit #{startIndex}, #{pageSize}
 </select>

 <select id="selectListByCursor" resultType="com.gooluke.admin.excel.exportdata.UserTestListExcelData">
     select
     id,
     user_name as userName,
     age,
     address
     from t_user_test
 </select>

service

@Value("${file.excel.path}")
private String filePath;

@Autowired
private UserTestDAO userTestDAO;

@Override
public BaseResponseDTO exportByPage() {
    String time = new SimpleDateFormat("yyyyMMddHHmmssSSS").format(new Date());
    String fileName = filePath + "分页查询导出百万数据_" + time + ".xlsx";
    ExcelWriter excelWriter = EasyExcel.write(fileName, UserTestListExcelData.class).build();
    WriteSheet writeSheet = EasyExcel.writerSheet("分页查询导出").build();
    AtomicInteger count = new AtomicInteger(0);
    int currentPage = 1;
    int pageSize = 1000;
    long startTime = System.currentTimeMillis();
    while (currentPage <= 1000) {
        int startIndex = (currentPage - 1) * pageSize;
        long start = System.currentTimeMillis();
        List<UserTestListExcelData> userInfos = userTestDAO.selectListByP(startIndex, pageSize);
        log.info("第{}页查询耗时为：{} ms", currentPage, (System.currentTimeMillis() - start));
        count.addAndGet(userInfos.size());
        excelWriter.write(userInfos, writeSheet);
        currentPage++;
    }
    excelWriter.finish();
    log.info("处理数据量为：{}", count);
    log.info("数据写入完成：{}", fileName);
    log.info("分页查询总处理耗时为：{} s", (System.currentTimeMillis() - startTime) / 1000);
    return new BaseResponseDTO();
}

@Override
@Transactional
public BaseResponseDTO exportByCursor() {
    String time = new SimpleDateFormat("yyyyMMddHHmmssSSS").format(new Date());
    String fileName = filePath + "游标查询导出百万数据_" + time + ".xlsx";
    ExcelWriter excelWriter = EasyExcel.write(fileName, UserTestListExcelData.class).build();
    WriteSheet writeSheet = EasyExcel.writerSheet("游标查询导出").build();
    AtomicInteger count = new AtomicInteger(0);
    long startTime = System.currentTimeMillis();
    Cursor<UserTestListExcelData> cursor = userTestDAO.selectListByCursor();
    ArrayList<UserTestListExcelData> writeList = new ArrayList<>();
    try {
        for (UserTestListExcelData userInfo : cursor) {
            writeList.add(userInfo);
            if (writeList.size() == 1000) {
                excelWriter.write(writeList, writeSheet);
                writeList.clear();
            }
            count.addAndGet(1);
        }
        if (!writeList.isEmpty()) {
            excelWriter.write(writeList,writeSheet);
            writeList.clear();
        }
        excelWriter.finish();
        log.info("数据写入完成：{}", fileName);
    } catch (Exception e) {
        log.error("游标查询异常,", e);
        return new BaseResponseDTO(ErrorStatus.SYSTEM_ERROR);
    } finally {
        if (cursor != null) {
            try {
                cursor.close();
            } catch (IOException e) {
                log.error("游标关闭失败");
            }
        }
    }
    log.info("游标查询总处理数据量：{}", count);
    log.info("游标查询总处理耗时：{} ms", (System.currentTimeMillis() - startTime));
    return new BaseResponseDTO();
}
导出耗时结果对比: 游标>>分页查询
分页查询花了118s

在这里插入图片描述
在这里插入图片描述

游标查询花了16s

在这里插入图片描述
在这里插入图片描述

4. 异常记录

4.1 Cause: java.sql.SQLFeatureNotSupportedException

这里我报错的原因是我用的是druid数据源，druid包的版本太低了，之前是’com.alibaba:druid:1.1.12’,后来升到1.2.20就可以了
参考链接：https://blog.csdn.net/m0_56070893/article/details/136257787
在这里插入图片描述

4.2 java.lang.IllegalStateException:A Cursor is already closed.

这里报错的原因是方法上没有加事务注解@Transactional，加上即可
在这里插入图片描述

SQL分页查询优化：百万级数据快速翻页
架构师的AI之路，分享AI应用开发架构的学习与实践。
07-12 589
本文深入剖析了SQL分页查询在百万级数据场景下的性能瓶颈与解决方案。通过从数据库原理层面分析传统LIMIT分页方法的固有缺陷，构建了一套包含索引优化、查询重写、算法改进和架构调整的全方位优化体系。文章系统对比了各类分页技术的适用场景与性能特征，提供了从前端到后端的端到端优化策略，并通过真实案例展示了如何将查询时间从秒级降至毫秒级，为处理大规模数据集的分页查询提供了理论指导和实践路径。
Python处理MySQL大数据量：分页查询与性能优化
AI天才研究院
06-23 1007
本文面向需要处理MySQL大数据量表分页查询的Python开发者，重点解决“百万级数据分页慢”的核心问题。覆盖传统分页的性能瓶颈分析、4种优化方案（索引分页/覆盖索引/游标分页/预计算分页）的原理与实现，以及Python代码实战。本文从“图书馆找书”的生活案例引入分页原理，逐步拆解传统的性能痛点，通过代码+执行计划分析优化方案，最后结合电商商品列表场景给出完整实战案例。传统分页简单但低效，OFFSET越大越慢。索引分页：基于自增主键或有序索引，通过快速定位，时间与页数无关。覆盖索引。
MySQL读取百万数据,使用流式、游标查询实战
hcyxsh的博客
10-20 947
mybatis 实现流式查询、游标查询很简单。
MySQL的两种分页方式：Offset/Limit分页和游标分页
最新发布
程序新视界
09-27 1034
分页方式的选择需依据具体应用场景与性能要求。如果你的应用允许宽松的精确度或需要支持随机页面访问，Offset/Limit分页可能是不错的选择。然而对于深度分页或大数据场景，游标分页表现更为优秀，尤其是在动态数据集上避免了数据漂移问题。两者并无绝对优劣，最重要的是根据业务需求选择最适合的实现方式。
三种SQL分页法效率分析
热门推荐
鱼汤的技术资料库
06-03 1万+
表中主键必须为标识列，[ID] int IDENTITY (1,1)　　1.分页方案一：(利用Not In和SELECT TOP分页)　　语句形式：SELECTTOP10*FROMTestTableWHERE(IDNOTIN　　　　　(SELECTTOP20id　　　　FROMTestTable　　　　ORDERBYid))ORDERBYIDSELECTTOP页大小*FROMTestT
MySQL索引学习（100万数据做对比）
anxiaoxiao61的博客
05-14 1624
创建表 create table account ( id int auto_increment primary key, name varchar(11) not null, money decimal(9, 2) not null ); 一、显示某个表所有索引信息 show index from account; 二、添加一个全文索引 （索引名字）列名 alter table shop.account add fulltext inde
mysql大数据量分页查询
zh的博客
06-18 878
只查id： A:id,title select * from A where id > (select id from A limit 100000,1) limit 100; select * from A where id between (select id from A limit 100000,1) and(select id from A limit 100100,1);...
面试官问：MySQL中百万级数据量，如何分页查询？
Java精选
07-18 2420
方法1: 直接使用数据库提供的SQL语句语句样式:MySQL中,可用如下方法: SELECT * FROM 表名称 LIMIT M,N适应场景:适用于数据量较少的情况(元组百/千级)原因/缺点:全表扫描,速度会很慢 且 有的数据库结果集返回不稳定(如某次返回1,2,3,另外的一次返回2,1,3). Limit限制的是从结果集的M位置处取出N条输出,其余抛弃.方法2:建立主键或唯一索引, 利...
基于POI实现万级数据分页导出Excel并解决内存溢出
本项目标题《POI导出Excel万级数据分页实现 解决内存溢出问题》精准指出了核心痛点与解决方案：通过 **分页查询 + 流式处理** 的方式，结合 Apache POI 提供的 SXSSFWorkbook（Streaming Usermodel API），实现对...
MySQL导出包含MBA字段数据的实现代码
同时，考虑到数据量较大时直接导出可能导致内存溢出或性能下降的问题，脚本中还可能引入分页机制（如使用LIMIT和OFFSET）或者游标遍历的方式逐步导出数据。 数据导出功能是本案例的核心目标之一。MySQL本身支持多种...
JDBC链接数据库查询1000万数据并导出execl表格使用多线程和深度分页
2301_79455003的博客
01-05 859
可以连接本地和服务器，本地更快一些，和电脑的性能也有关系。
shell-mysql:shell脚本分页导出mysql数据
05-10
shell-mysql 使用shell脚本分页读取MySQL数据 脚本背景 由于要在Linux上，远程读取mysql的表的数据，然后做一定清洗后，把数据上传至Hadoop集群中，使用Java写吧，感觉太麻烦了，得在Win上开发好，还得打成jar包， 上传到Linux上，如果那里出了问题，还得重复这样，非常不方便，那就用shell写一个吧，也不需要什么jdbc驱动包，只需要在Linux上装个MySQL的 客户端即可，用一行yum命令即可搞定，所以就花了点时间，封装了一个小脚本 功能介绍 直接在Linux下使用shell脚本远程分页读取MySQL表的数据的一个小脚本，已测过读取600万+的数据 效率与jdbc相差无几 脚本介绍 主要有三个脚本构成1，page.sh 这是一主脚本，里面定义了分页的条件，大家看下便知2，f.sh 一个小包装的脚本吧，里面会用sed去掉表头一些信息3，port.
PHP如何实现百万级数据导出
Black Dragon 的个人博客
08-22 8952
| 前言 公司目前有一个需求，需要对一个日增量在20万+数据量的数据表中的数据进行可自定义条件筛选的导出数据，该功能需要对多个部门进行开发使用，要保证功能可用的前提下，尽量优化体验。 首先介绍一下当前可利用的资源： 1、MySql - 一主库双从库。 2、分布式服务器集群，选择其中一台中型机作为脚本执行载体。 3、文件系统 - 可以支持上传大数据量文件。 4、编程语言PHP，说实话PH...
通过游标实现分页查询
雪辉博客
10-10 1827
该方法不适用MySQL，因为MySQL无法在存储过程外使用游标 – 开启一个事务 BEGIN; – 定义游标 DECLARE t1_cur CURSOR FOR SELECT * FROM t1; – 获取1000条记录 FETCH 1000 FROM t1_cur ; – … – 继续获取1000条记录 FETCH 1000 FROM t1_cur ; –最后结束事务 COMMIT; ...
MongoDB 学习笔记（三）：分页、排序与游标
漂流在深圳
10-09 5148
一、分页 1、limit返回指定条数的数据 2、skip指定跨度 3、limit与skip结合，进行分页 二、排序 1、sort排序：指定排序的key，1为升序，-1为降序。 2、排序的先后：从下图中可以发现，使用sort，始终是先对满足查询条件而查询到的结果集进行排序，然后再进行选取指定的条数或者是跳过指定的条数。 3、排序的优先级：由于MongoDB的key可
mysql分页导出_mysql数据导出并分页
weixin_39596720的博客
02-03 339
6 $sum = mysql_numrows($query); //计算总记录数7 if($sum%$pagesize == 0)8 $total = (int)($sum/$pagesize);9 else10 $total = (int)($sum/$pagesize)+1;1112 if(isset($_GET[‘page‘]))13 {14 $p = (int...
mysql迭代查询并分页_基于游标的分页接口实现
weixin_39636857的博客
02-08 623
分页接口的实现，在偏业务的服务端开发中应该很常见，PC时代的各种表格，移动时代的各种feed流、timeline。出于对流量的控制，或者用户的体验，大批量的数据都不会直接返回给客户端，而是通过分页接口，多次请求返回数据。而最常用的分页接口定义大概是这样的：router.get('/list', async ctx => {const { page, size } = this.query//...
数据库查询之游标分页
weixin_43281875的博客
03-18 698
普通分页：常用的基于偏移量的分页，使用LIMIT和OFFSET来获取分页数据//返回第50~60行数据游标分页：通过唯一的、有序的字段（游标）来标记分页的起始位置，每次分页查询时候使用'>'或‘<’某个特定游标值实现分页。//id为游标如果上面这条sql查出来的最后一条数据id=14。
Mysql模拟100W条数据查询对比索引查询
mrhe_的博客
04-20 420
创建user表 CREATE TABLE `app_user` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT '' COMMENT '用户昵称', `email` varchar(50) NOT NULL COMMENT '用户邮箱', `phone` varchar(20) DEFAULT '' COMMENT '手机号', `gender` tinyint(4) unsigned DEFAUL
Google查询数据导出至Excel与数据库直接导出方法解析
此外，随着数据量的不断增长，传统的单机导出方式可能面临性能瓶颈，因此还可以考虑引入分布式处理框架（如Apache Spark）来处理大规模数据导出任务，或者使用云服务提供的Excel处理能力，进一步提升系统的可扩展性...
评论 7
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值