Spring Boot 集成 Druid 批量插入数据和效率监控配置

最新推荐文章于 2024-05-07 16:43:37 发布
最新推荐文章于 2024-05-07 16:43:37 发布
阅读量2.5k 收藏 7
点赞数
分类专栏： 高并发多线程 文章标签： mysql 数据库 java druid 批量
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u011001084/article/details/122338537
版权

        最近新的项目写了不少各种 insertBatch 的代码，例如excle导入，批量导入的方式很多，如何选择困扰着大家。下面为大家分析常见的批量插入方法和效率。本文只设计单线程，多线程甚至生产者消费者模式后续补充。

测试环境：

  • SpringBoot 2.5
  • Mysql 8
  • JDK 8
  • Druid

搭建测试环境

Druid是Java语言中最好的数据库连接池，并且能够提供强大的监控和扩展功能。

业界把 Druid 和 HikariCP 做对比后，虽说 HikariCP 的性能比 Druid 高，但是因为 Druid 包括很多维度的统计和分析功能，所以这也是大家都选择使用它的原因。

下面来说明如何在 spring Boot 中配置使用Druid

1、添加Maven依赖 (或jar包)
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.0.15</version>
        </dependency>

2、配置数据源相关信息
#datasource
spring.datasource.url=jdbc:mysql://192.168.10.20:3306/test?useUnicode=true&characterEncoding=utf-8&rewriteBatchedStatements=true
spring.datasource.username = root
spring.datasource.password = password
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

#pool Initialization 
spring.datasource.initialSize=5  
spring.datasource.minIdle=5  
spring.datasource.maxActive=20  
#overtime
spring.datasource.maxWait=60000  
spring.datasource.timeBetweenEvictionRunsMillis=60000  
spring.datasource.minEvictableIdleTimeMillis=300000  
spring.datasource.validationQuery=SELECT 1 FROM DUAL  
spring.datasource.testWhileIdle=true  
spring.datasource.testOnBorrow=false  
spring.datasource.testOnReturn=false  
spring.datasource.poolPreparedStatements=true  
spring.datasource.maxPoolPreparedStatementPerConnectionSize=20  
spring.datasource.filters=stat,wall,log4j  
spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
#spring.datasource.useGlobalDataSourceStat=tru

#server
server.port=8081

sql 文件：

drop database IF EXISTS test;
CREATE DATABASE test;
use test;
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT "",
  `age` int(11) DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

应用的配置文件：

server:
  port: 8081
spring:
  #数据库连接配置
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf-8&useSSL=false&allowPublicKeyRetrieval=true&&serverTimezone=UTC&setUnicode=true&characterEncoding=utf8&&nullCatalogMeansCurrent=true&&autoReconnect=true&&allowMultiQueries=true
    username: root
    password: 123456
#mybatis的相关配置
mybatis:
  #mapper配置文件
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: com.aphysia.spingbootdemo.model
  #开启驼峰命名
  configuration:
    map-underscore-to-camel-case: true
logging:
  level:
    root: error

启动文件，配置了 Mapper 文件扫描的路径：

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan("com.aphysia.springdemo.mapper")
public class SpringdemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(SpringdemoApplication.class, args);
    }

}

Mapper 文件一共准备了几个方法，插入单个对象，删除所有对象，拼接插入多个对象：

import com.aphysia.springdemo.model.User;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface UserMapper {

    int insertUser(User user);

    int deleteAllUsers();


    int insertBatch(@Param("users") List<User>users);
}

Mapper.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.aphysia.springdemo.mapper.UserMapper">
    <insert id="insertUser" parameterType="com.aphysia.springdemo.model.User">
        insert  into user(id,age) values(#{id},#{age})
    </insert>

    <delete id="deleteAllUsers">
        delete from user where id>0;
    </delete>

    <insert id="insertBatch" parameterType="java.util.List">
        insert into user(id,age) VALUES
        <foreach collection="users" item="model" index="index" separator=",">
            (#{model.id}, #{model.age})
        </foreach>
    </insert>
</mapper>

测试的时候，每次操作我们都删除掉所有的数据，保证测试的客观，不受之前的数据影响。

不同的测试

1. foreach 插入

先获取列表，然后每一条数据都执行一次数据库操作，插入数据：

@SpringBootTest
@MapperScan("com.aphysia.springdemo.mapper")
class SpringdemoApplicationTests {

    @Autowired
    SqlSessionFactory sqlSessionFactory;

    @Resource
    UserMapper userMapper;

    static int num = 100000;

    static int id = 1;

    @Test
    void insertForEachTest() {
        List<User> users = getRandomUsers();
        long start = System.currentTimeMillis();
        for (int i = 0; i < users.size(); i++) {
            userMapper.insertUser(users.get(i));
        }
        long end = System.currentTimeMillis();
        System.out.println("time:" + (end - start));
    }
}

2. 拼接sql插入

其实就是用以下的方式插入数据：

INSERT INTO `user` (`id`, `age`) 
VALUES (1, 11),
(2, 12),
(3, 13),
(4, 14),
(5, 15);
@Test
    void insertSplicingTest() {
        List<User> users = getRandomUsers();
        long start = System.currentTimeMillis();
        userMapper.insertBatch(users);
        long end = System.currentTimeMillis();
        System.out.println("time:" + (end - start));
    }

3. 使用Batch批量插入

将 MyBatis session 的 executor type 设为 Batch ，使用 sqlSessionFactory 将执行方式置为批量，自动提交置为 false ,全部插入之后，再一次性提交：

@Test
    public void insertBatch(){
        SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> users = getRandomUsers();
        long start = System.currentTimeMillis();
        for(int i=0;i<users.size();i++){
            mapper.insertUser(users.get(i));
        }
        sqlSession.commit();
        sqlSession.close();
        long end = System.currentTimeMillis();
        System.out.println("time:" + (end - start));
    }

4. 批量处理+分批提交

在批处理的基础上，每1000条数据，先提交一下，也就是分批提交。

@Test
    public void insertBatchForEachTest(){
        SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> users = getRandomUsers();
        long start = System.currentTimeMillis();
        for(int i=0;i<users.size();i++){
            mapper.insertUser(users.get(i));
            if (i % 1000 == 0 || i == num - 1) {
                sqlSession.commit();
                sqlSession.clearCache();
            }
        }
        sqlSession.close();
        long end = System.currentTimeMillis();
        System.out.println("time:" + (end - start));
    }

初次结果，明显不对？

运行上面的代码，我们可以得到下面的结果， for 循环插入的效率确实很差，拼接的 sql 效率相对高一点，看到有些资料说拼接 sql 可能会被 mysql 限制，但是我执行到 1000w 的时候，才看到堆内存溢出。

下面是不正确的结果！！！

插入方式1010010001w10w100w1000w
for循环插入3871150790770026635984太久了...太久了...
拼接sql插入308320392838315624948OutOfMemoryError: 堆内存溢出
批处理392917544251647470666太久了...太久了...
批处理 + 分批提交359893527550270472462太久了...太久了...

拼接sql并没有超过内存

我们看一下 mysql 的限制:

mysql> show VARIABLES like '%max_allowed_packet%';
+---------------------------+------------+
| Variable_name             | Value      |
+---------------------------+------------+
| max_allowed_packet        | 67108864   |
| mysqlx_max_allowed_packet | 67108864   |
| slave_max_allowed_packet  | 1073741824 |
+---------------------------+------------+
3 rows in set (0.12 sec)

这 67108864 足足 600 多M，太大了，怪不得不会报错，那我们去改改一下它吧，改完重新测试：

  1. 首先在启动 mysql 的情况下，进入容器内，也可以直接在 Docker 桌面版直接点 Cli 图标进入：
docker exec -it mysql bash
  1. 进入 /etc/mysql 目录，去修改 my.cnf 文件：
cd /etc/mysql
  1. 先按照 vim ，要不编辑不了文件：
apt-get update
apt-get install vim
  1. 修改 my.cnf
vim my.cnf
  1. 在最后一行添加 max_allowed_packet=20M (按 i 编辑，编辑完按 esc ，输入 :wq 退出)
[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
 
# Custom config should go here
!includedir /etc/mysql/conf.d/
max_allowed_packet=2M
  1. 退出容器
# exit
  1. 查看 mysql 容器 id
docker ps -a

  1. 重启 mysql
docker restart c178e8998e68

重启成功后查看最大的 max_allowed_pactet ，发现已经修改成功：

mysql> show VARIABLES like '%max_allowed_packet%';
+---------------------------+------------+
| Variable_name             | Value      |
+---------------------------+------------+
| max_allowed_packet        | 2097152    |
| mysqlx_max_allowed_packet | 67108864   |
| slave_max_allowed_packet  | 1073741824 |
+---------------------------+------------+

我们再次执行拼接 sql ，发现 100w 的时候， sql 就达到了 3.6M 左右，超过了我们设置的 2M ，成功的演示抛出了错误：

org.springframework.dao.TransientDataAccessResourceException: 
### Cause: com.mysql.cj.jdbc.exceptions.PacketTooBigException: Packet for query is too large (36,788,583 > 2,097,152). You can change this value on the server by setting the 'max_allowed_packet' variable.
; Packet for query is too large (36,788,583 > 2,097,152). You can change this value on the server by setting the 'max_allowed_packet' variable.; nested exception is com.mysql.cj.jdbc.exceptions.PacketTooBigException: Packet for query is too large (36,788,583 > 2,097,152). You can change this value on the server by setting the 'max_allowed_packet' variable.

批量处理为什么这么慢？

但是，仔细一看就会发现，上面的方式，怎么批处理的时候，并没有展示出优势了，和 for 循环没有什么区别？这是对的么？

这肯定是不对的，从官方文档中，我们可以看到它会批量更新，不会每次去创建预处理语句，理论是更快的。

然后我发现我的一个最重要的问题：数据库连接 URL 地址少了 rewriteBatchedStatements=true

如果我们不写， MySQL JDBC 驱动在默认情况下会忽视 executeBatch() 语句，我们期望批量执行的一组 sql 语句拆散，但是执行的时候是一条一条地发给 MySQL 数据库，实际上是单条插入，直接造成较低的性能。我说怎么性能和循环去插入数据差不多。

只有将 rewriteBatchedStatements 参数置为 true , 数据库驱动才会帮我们批量执行 SQL 。

正确的数据库连接：

jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf-8&useSSL=false&allowPublicKeyRetrieval=true&&serverTimezone=UTC&setUnicode=true&characterEncoding=utf8&&nullCatalogMeansCurrent=true&&autoReconnect=true&&allowMultiQueries=true&&&rewriteBatchedStatements=true

找到问题之后，我们重新测试批量测试,最终的结果如下：

插入方式1010010001w10w100w1000w
for循环插入3871150790770026635984太久了...太久了...
拼接sql插入308320392838315624948（很可能超过sql长度限制）OutOfMemoryError: 堆内存溢出
批处理(重点)33332336263616388978OutOfMemoryError: 堆内存溢出
批处理 + 分批提交359313394630290718631OutOfMemoryError: 堆内存溢出

从上面的结果来看，确实批处理是要快很多的，当数量级太大的时候，其实都会超过内存溢出的，批处理加上分批提交并没有变快，和批处理差不多，反而变慢了，提交太多次了，拼接 sql 的方案在数量比较少的时候其实和批处理相差不大，最差的方案就是 for 循环插入数据，这真的特别的耗时。 100 条的时候就已经需要 1s 了，不能选择这种方案。

一开始发现批处理比较慢的时候，真的挺怀疑自己，后面发现是有一个参数，有一种拨开云雾的感觉，知道得越多，不知道的越多。

ArthurKingYs
关注
  • 0
    点赞
  • 踩
  • 7
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
spring boot基于DRUID实现数据源监控过程解析
08-25
DRUID 是阿里巴巴开源的数据库连接池，提供了数据源监控、SQL 执行分析、Web 应用防火墙等功能，在 Spring Boot 中可以通过依赖的方式引入 DRUID，实现数据源监控和加密。 5. 数据源监控实现过程 数据源监控实现...
Spring boot 集成 Druid 数据源过程详解
08-25
Spring Boot 集成 Druid 数据源过程详解 Spring Boot 是一个基于 Java 的开源框架，用于快速开发 Web 应用程序，而 Druid 是阿里开源的一个 JDBC 应用组件，包括三部分：DruidDriver、DruidDataSource 和 SQLParser...
数据库批量插入这么讲究的么？
秦怀杂货店
01-04 1212
最近新的项目写了不少各种 insertBatch 的代码，一直有人说，批量插入比循环插入效率高很多，那本文就来实验一下,到底是不是真的？ 测试环境： SpringBoot 2.5 Mysql 8 JDK 8 Docker 首先，多条数据的插入，可选的方案： foreach循环插入 拼接sql，一次执行 使用批处理功能插入 搭建测试环境` sql文件： drop database IF EXISTS test; CREATE DATABASE test; use test; DROP TABLE IF
Druid 入门 数据导入
weixin_33712881的博客
06-23 308
2019独角兽企业重金招聘Python工程师标准>>> ...
SpringBoot~使用DruidDataSource实现日志监控
最新发布
2401_84049061的博客
05-07 409
自我介绍一下，小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。深知大多数Java工程师，想要提升技能，往往是自己摸索成长，自己不成体系的自学效果低效漫长且无助。因此收集整理了一份《2024年Java开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。
Druid 分析jpa批量插入
mark_dwj的博客
01-05 852
重代码上看似乎是一个一个的save，其实不然，查看源码(这里不细说了)，jpa每次save都会先添加到action queue，在flush的时候，再通过insert action构造statement的batch操作，然后到达一个批量的时候才perform，达到一个batch的时候会调用executeBatch()。可以监控数据库访问性能，Druid 内置提供了一个功能强大的 StatFilter 插件，能够详细统计 SQL 的执行性能，这对于线上分析数据库访问性能有帮助。
jpa批量插入+druid数据库连接池
HAHA2576的博客
12-07 1757
jpa批量插入，spring.jpa.properties.jdbc.batch_size,druid连接池yml格式配置
MySQL—SQL优化：插入数据（insert、大批量插入）、主键优化（数据组织方式、页分裂、页合并、索引设计）、order by 、group by 、limit 、count 、update 优化
放下华子我只抽RuiKe5的博客
05-04 5389
​ 在之前的测试中，我们发现，如果数据量很大，在执行count操作时，是非常耗时的。MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高；但是如果是带条件的count，MyISAM也慢。nnoDB 引擎就麻烦了，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。
Spring Boot集成Druid实现多数据源的两种方式
04-12
1、集成com.baomidou的方式，引入dynamic-datasource...2、原生的方式，基于AOP手动实现多数据源； 3、项目代码完整，包含注释，能启动测试； 博客地址：https://blog.csdn.net/u011974797/article/details/130109195
Spring Boot使用Druid和监控配置方法
08-30
在Spring Boot中集成和配置Druid作为数据库连接池可以带来许多优势，比如高效的连接管理和强大的监控功能。以下是如何在Spring Boot应用中实现这一配置的详细步骤： 首先，我们需要在项目的`pom.xml`文件中添加...
Spring Boot使用Druid进行维度的统计和监控
08-30
Spring Boot 使用 Druid 进行维度的统计和监控 Spring Boot 是一个基于 Java 的开源框架，旨在简化基于 Spring 框架的开发。Druid 是一款为监控而生的数据库连接池框架，能够提供实时的统计和监控功能。本文将介绍...
Springboot+jpa+druid+hibernate实现批量新增
风间净琉璃的博客
02-21 2545
Springboot+jpa+druid+hibernate实现批量新增，并验证批量功能
Spring Boot操作数据库学习之整合Druid
缘友一世的博客
01-21 634
Java程序很大一部分要操作数据库，为了提高性能操作数据库的时候，又不得不使用数据库连接池。Druid 是阿里巴巴开源平台上一个数据库连接池实现，结合了 C3P0、DBCP 等 DB 池的优点，同时加入了日志监控。Druid 可以很好的监控 DB 池连接和 SQL 的执行情况，天生就是针对监控而生的 DB 连接池。Spring Boot 2.0 以上默认使用 Hikari 数据源，Hikari 与 Driud 都是当前 Java Web 上最优秀的数据源。Druid GitHub地址。
Druid连接TIDB数据库生成表和插入值代码
ahoges的博客
03-24 2271
/** * @author yanchenyang958@hellobike.com * @date 2022/3/15 5:57 下午 */ @EqualsAndHashCode(callSuper = true) @ConfigurationProperties(prefix = "datasource.tidb") @Component @Data @Slf4j public class TidbSource extends JDBCCacheSource { /** * .
Apache druid的ingestion spec数据插入规范
Bulut0907
06-21 486
目录1. 测试数据2. 定义dataSchema2.1 指定数据源名称2.2 指定__time时间戳2.3 定义Rollup相关2.3.1 "rollup" : true2.3.2 "rollup" : false2.4 定义granularitySpec3. 定义task type4. 定义数据输入源5. 其它优化参数6. 提交任务7. 查询结果数据 1. 测试数据 我们要插入的测试是一批网络流数据，如下所示： {"ts":"2018-01-01T01:01:35Z","srcIP":"1.1.1.1",
druid 学习笔记2 - druid数据导入
luckydoit的博客
11-03 633
上一篇我们讲到了druid可以监听kafka的topic实时导入数据，下面我们讲一下如何通过flume实时讲本地的日志文件导入kafka中。 flume是一个日志采集系统，可以通过不同方式收集数据，并做一些预处理，然后传输到下游的数据管道中，比如hdfs或者kafka中。因为druid中的字段最好是简单类型，方便进行分析，而我们现在上报的日志都是通过一个ext_data的json字段将关键字段存...
springboot+druid实现的动态数据源
qq_34093116的博客
03-29 769
最后在方法上或者类上加上注解就可以，自由切换数据源了，需要注意的是两个数据源是不能同时的，那个是多数据源，咱们这个是动态数据源。总的实现思路是 利用spring的aop并且取消springboot的自动配置数据源，自行配置数据源的思路。工作中用到了动态数据源连接多个数据库的场景，记录一下；
druid导入数据的几种方式
weixin_34360651的博客
11-13 2456
2019独角兽企业重金招聘Python工程师标准>>> ...
SpringBoot 高效批量插入万级数据
weixin_44030143的博客
05-23 2673
3、批量插入saveBatch（总耗时：2.7秒）5、ThreadPoolTaskExecutor（总耗时：1.7秒）
spring boot +druid admin
06-01
Spring Boot是一种用于快速开发基于Spring框架的应用程序的框架，而Druid是一个开源的数据库连接池和监控平台。Druid Admin是一种基于Web的管理平台，可以用于监控和管理Druid数据库连接池。 要在Spring Boot中使用Druid Admin，需要遵循以下步骤： 1. 在pom.xml文件中添加Druid和Druid Admin的依赖。 ```xml <dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> <version>1.1.10</version> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-admin-starter</artifactId> <version>1.1.10</version> </dependency> ``` 2. 在application.properties或application.yml文件中添加Druid的配置信息。 ```properties spring.datasource.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8 spring.datasource.username=root spring.datasource.password=root spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver # Druid监控配置 # 访问Druid监控平台的URL为：http://localhost:8080/druid spring.datasource.druid.stat-view-servlet.enabled=true spring.datasource.druid.stat-view-servlet.url-pattern=/druid/* spring.datasource.druid.stat-view-servlet.login-username=admin spring.datasource.druid.stat-view-servlet.login-password=admin spring.datasource.druid.stat-view-servlet.reset-enable=true spring.datasource.druid.stat-view-servlet.allow=127.0.0.1 ``` 3. 在Spring Boot启动类中添加@EnableAdminServer注解，启用Druid Admin监控平台。 ```java import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication; import de.codecentric.boot.admin.server.config.EnableAdminServer; @EnableAdminServer @SpringBootApplication public class Application { public static void main(String[] args) { SpringApplication.run(Application.class, args); } } ``` 4. 启动Spring Boot应用程序，Druid Admin监控平台即可访问。在浏览器中输入"http://localhost:8080"，即可进入Druid Admin监控平台。 希望这些步骤能够帮助你在Spring Boot中成功使用Druid Admin监控平台。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值