测试java程序插入单表2000万条数据（MySQL,PostgreSQL,达梦）

 

1、创建表

1.1、MySQL

CREATE TABLE `tb_person` (`id` bigint NOT NULL,`name` varchar(100) NOT NULL,`gender` varchar(4) NOT NULL,`age` int NOT NULL,`email` varchar(30) DEFAULT NULL,`tel` varchar(20) DEFAULT NULL,`road` varchar(100) DEFAULT NULL,`create_time` datetime NOT NULL,`update_time` datetime DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

 

1.2、PostgreSQL

CREATE TABLE tb_person (id serial PRIMARY KEY,name varchar(100) NOT NULL,gender varchar(4) NOT NULL,age int NOT NULL,email varchar(30) DEFAULT NULL,tel varchar(20) DEFAULT NULL,road varchar(100) DEFAULT NULL,create_time TIMESTAMP  NOT NULL,update_time TIMESTAMP  DEFAULT NULL
);

 

1.3、达梦

 

2、java批量插入程序

参考：mysql-mock: 生成千万条以上mysql模拟数据，适用于性能测试的场景；测试数据贴近业务场景，看着较真实

pom.xml中配置驱动

<!-- 达梦数据库JDBC驱动 -->
<dependency><groupId>com.dameng</groupId><artifactId>DmJdbcDriver18</artifactId><version>8.1.3.140</version>
</dependency><!-- PostgreSQL JDBC驱动 -->
<dependency><groupId>org.postgresql</groupId><artifactId>postgresql</artifactId><scope>runtime</scope>
</dependency><!-- MySQL JDBC驱动 -->
<dependency><groupId>com.mysql</groupId><artifactId>mysql-connector-j</artifactId><scope>runtime</scope>
</dependency>

application.yaml配置

spring:autoconfigure:exclude: org.springframework.boot.autoconfigure.jdbc.JdbcTemplateAutoConfigurationdatasource:
#    driver-class-name: dm.jdbc.driver.DmDriver
#    url: jdbc:dm://localhost:5236
#    username: SYSDBA
#    password: xxx
#    driver-class-name: org.postgresql.Driver
#    url: jdbc:postgresql://localhost:5432/sku?currentSchema=public&useUnicode=true&characterEncoding=UTF-8
#    username: postgres
#    password: xxxdriver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://localhost:3306/sku?useServerPrepStmts=false&rewriteBatchedStatements=true&useUnicode=true&characterEncoding=UTF-8username: rootpassword: xxx

 

3、单表批量插入2000万条数据

3.1、MySQL

3.2、PostgreSQL

3.3、达梦

 

4、查询测试

测试表 tb_person，数量为2000万

select * from tb_person where name like '%hom1e%' limit 10;select id from tb_person where name like '%hom1e%' limit 10;select p.* from tb_person p 
join(select id from tb_person where name like '%hom1e%' limit 10) a
on a.id=p.id;

 

4.1、MySQL

create index idx_name on tb_person(name);

 

4.2、PostgreSQL

create index idx_name on tb_person(name);

 

4.3、达梦

create index idx_name on test.tb_person(name);